DB-ERASE

Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Targets:

  • IMS DB
  • SQL
  • VSAM Batch
  • VSAM Online

IMS DB

DB-ERASE deletes a record and all its dependent segments.

Syntax: for Format 1
DB-ERASE REC segment [FROM dataarea]
... [VIEW pcbname|PCB pcbname]
Syntax: for Format 2, records obtained by path calls
DB-ERASE REC|REF segment1 [FROM dataarea]
... [VIEW pcbname|PCB pcbname]
... REC|REF segment2 [FROM dataarea]
... [VIEW pcbname|PCB pcbname]
        .
        .
        .
... REC segmentN [FROM dataarea]
... [VIEW pcbname|PCB pcbname]
Syntax Rules: for Formats 1 and 2
  1. DB-ERASE assumes record retrieval from the default I/O area. If this is not true, you must code FROM dataarea.
  2. When a record is obtained from a PCB other than the default PCB, such as by DB-OBTAIN with VIEW or PCB, you must code VIEW pcbname.
Syntax Rules: for Format 2
  1. Use REF only when DB-ERASE is preceeded by a DB-OBTAIN path call, meaning more than one record was obtained by the call and placed into the I/O area.
  2. Use REF with DB-ERASE to specify higher-level segments that you do not want to delete.
General Rules: for Formats 1 and 2
  1. Specify every segment down to the first segment being deleted for records obtained by path calls. That is, every record named in a DB-OBTAIN preceding a DB-ERASE must also be named in the DB-ERASE.
General Rules: for Format 2
  1. If a preceding DB-OBTAIN specifies the REF parameter, meaning that the record is referenced but not placed into the I/O area, DB-ERASE ignores the record.
Parameters:

REC|REF segment

Specify that the segment name (maximum 8 characters) is in a Working-Storage variable for the program to delete.

FROM dataarea

Alternate I/O area where program deletesa record. Required for a record obtained from an I/O area other than the default I/O area, such as by DB-OBTAIN INTO.

PCB|VIEW pcbname

Specify PCB used when PSB contains multiple PCBs for the same database.

Examples:
Format 1
Delete only RECORD-B and RECORD-C. First, issue a DB-OBTAIN call.
 DB-OBTAIN REC RECORD-A WHERE KEY-A = VALUE-A
 ... REC RECORD-B WHERE KEY-B = VALUE-B
 ... REC RECORD-C WHERE KEY-C = VALUE-C HOLD
Then, name all segments accessed by the prior path call DB-OBTAIN. Because the children of a deleted segment are also deleted, there is no need to code beyond the highest level segment being deleted (RECORD-B).
DB-ERASE REF RECORD-A
... REC RECORD-B REC RECORD-C
Format 2
The following examples assume that the following DB-OBTAIN path call precedes the DB-ERASE:
$DB-OBTAIN ("REC SEG1, REC SEG2, REC SEG3")

To delete the entire path:

$DB-ERASE ("REC SEG1")
Note: Dependent segments are automatically deleted.

To delete SEG2 and its dependents:

$DB-ERASE ("REF SEG1, REC SEG2")

To delete just SEG3:

$DB-ERASE (REF SEG1, REF SEG2, REC SEG3")

The following example assumes that the following DB-OBTAIN call precedes the DB-ERASE. This DB-OBTAIN is not a path call and places only SEG3 into the I/O area; therefore, DB-ERASE ignores SEG1 and SEG2:

$DB-OBTAIN ("REF SEG1, REF SEG2, REC SEG3")

To delete SEG3:

$DB-ERASE ("REC SEG3")

The following examples assume that the following DB-OBTAIN path call precedes the DB-ERASE. This DB-OBTAIN places only SEG2 and SEG3 into the I/O area; therefore, DB-ERASE ignores SEG1:

$DB-OBTAIN ("REF SEG1, REC SEG2, REC SEG3")

To delete only SEG3:

$DB-ERASE ("REF SEG2, REC SEG3")

To delete SEG2 and SEG3, which is a dependent of SEG2:

$DB-ERASE ("REC SEG2")

SQL

DB-ERASE deletes:
  • All rows in a table
  • Specific row in a table
  • Cursor set rows
Syntax: for Format 1
DB-ERASE REC copylibname-REC
... [WHERE column operator [:]altvalue
... [AND|OR column operator [:]altvalue
        .
        .
        .
... AND|OR column operator [:]altvalue]]
... [QUERYNO number]
... [WITH [CS|RS|RR]]
Syntax: for Format 2
DB-ERASE REC copylibname-REC
... [WHERE CURRENT [OF] cursorname]
... [QUERYNO number]
... [WITH [CS|RS|RR]]
Parameters:

AND|OR column operator [:]altvalue

Altvalue can be a literal or data name.

REC copylib-REC

Specify the 01-level name of the COBOL row layout in the DCLGEN or copybook information. Cannot be the same as any cursor names or DB-PROCESS-ID names.

WHERE CURRENT [OF] cursorname

Valid for cursor processing only. Act upon the row retrieved from cursor.

QUERYNO number

Specifies the number to be used for this SQL statement in EXPLAIN output and trace records.

WITH

Specifies the isolation level at which the statement is executed.

CS

Cursor stability

RS

Read stability

RR

Repeatable read

Example:
Delete any row in table D2MASTER where PM_PART_NO equals 123 or 567.
DB-ERASE REC D2TAB-REC
... WHERE PM_PART_NO = '123' OR PM_PART_NO = '567'

VSAM Batch

DB-ERASE deletes a record:
  • Specified by key qualification via DB-OBTAIN (key qualified)
  • Retrieved by DB-OBTAIN or DB-PROCESS (unqualified)
Syntax: for Format 1, key-qualified
DB-ERASE REC recordname
 ... WHERE primarykey = value
 ... [SUB value] [OF|IN dataarea]
Syntax: for Format 2, unqualified
DB-ERASE REC recordname
Parameters:

WHERE primarykey = value

Value can be literal, data name, or an asterisk (*). An asterisk indicates the segment record description contains the key value.

SUB[SCRIPT] (value)

Move the subscripted field value to a specified field. Value can be a data name, literal, or an integer.

OF|IN dataarea

Qualify the I/O area moving to the value field, when more than one structure in the Data Division contains the field.

Example:

Delete a record where ORDR-NUMBER equals the value in the Working-Storage variable CUST-ORDR-NUMBER.

DB-ERASE REC ORDR-RECORD
 ... WHERE ORDR-NUMBER = CUST-ORDR-NUMBER

Read a CUST-RECORD for deletion.

DB-OBTAIN REC CUST-RECORD
... WHERE CUST-KEY = SCREEN-CUST-KEY
IF OK-ON-REC
    DB-ERASE REC CUST-RECORD

VSAM Online

DB-ERASE deletes a record:
  • Specified by key qualification via DB-OBTAIN (key qualified).
  • Retrieved by DB-OBTAIN or DB-PROCESS (unqualified).
Syntax: for Format 1, key-qualified
DB-ERASE REC recordname
... WHERE primarykeyname = value
... [SUB[SCRIPT] value] [OF|IN dataarea] 
... [KEYLENGTH|KLEN value]
... [SYSID systemname] [DDN ddname]
Syntax: for Format 2, unqualified
DB-ERASE REC recordname
Parameters:

WHERE pri-marykey = value

Value can be literal, data name, or an asterisk (*). An asterisk indicates the segment record description contains the key value.

SUB[SCRIPT] (value)

Move the subscripted field value to a specified field. Value can be a data name, literal, or, under VSAM Batch or Online, an integer.

OF|IN dataarea

Qualify the I/O area moving to the value field, when more than one structure in the Data Division contains the field.

KEYLENGTH| KLEN value

Specify number or characters in key length; full or partial length is valid. Value can be a number or a data name defined as PIC S9(4) COMP. AMB generates the CICS GENERIC option for a partial key length.

SYSID systemname

Process records stored on remote systems. Name a file residing in a remote data set. Systemname can be a 4-character literal region name or a Working-Storage field containing a 4-character region name.

DDN ddname

File data description name; supply a value to the name option of CICS DATASET option. Ddname can be a literal or data name defined as PIC X(8).

Examples:

Delete a group of ORDR-RECORD records; use partial key length. For successful deletes, store the number of actual records in the AMB data field APS-VSAM-NUMREC.

DB-ERASE REC ORDR-RECORD
... WHERE ORDR-NUMBER = SCREEN-PARTIAL-ORDR-NUMBER KLEN 6
IF  OK-ON-REC
    SCREEN-MSG = 'ORDER RECORDS DELETED, NUMBER ORDERS = '
    SCREEN-NBR-RECS = APS-VSAM-NUMREC

Hold a CUST-RECORD for deletion.

DB-OBTAIN REC CUST-RECORD
... WHERE CUST-KEY = SCREEN-CUST-KEY HOLD
IF OK-ON-REC
    DB-ERASE REC CUST-RECORD