DB-OBTAIN

The purpose of DB-OBTAIN varies depending on the target.

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-OBTAIN performs:

Qualified and unqualified record retrievals
Retrieval of records qualified on multiple fields (Boolean qualification)
Retrieval of records qualified on secondary indices composed of more than one field
Compound calls
Path calls
Hold segments for modification or deletion
Current positioning
Reset positioning
First/last qualification
Naming of an alternate I/O area that a record is read into or stored from
Retrieval of segments using concatenated key qualification

Syntax: for Format 1

Unqualified.

DB-OBTAIN REC recordname [HOLD] [RESET]

Syntax: for Format 2

Qualified.

DB-OBTAIN REC recordname
... WHERE fieldname1 operator value
... [AND|OR fieldname2 operator value [FIRST|LAST]
... [INTO dataarea]
        .
        .
... [AND|OR fieldnameN operator value] [FIRST|LAST]
... [INTO dataarea] [HOLD]]] [RESET]

Syntax: for Format 3

Qualified on secondary index values.

DB-OBTAIN REC recordname
... WHERE fieldname operator (value1 [ ... valueN])
... [INTO dataarea]
... [FIRST|LAST] [HOLD] [RESET]

Syntax: for Format 4

Qualified compound retrieval.

DB-OBTAIN REF segmentname1 WHERE fieldname1 operator value
... REC|REF segmentname2 WHERE fieldname2 operator value
        .
        .
        .
... [REC segmentnameN WHERE fieldnameN operator value
... [FIRST|LAST] [HOLD]]
... [RESET]

Syntax: for Format 5

Retrieve next segment.

DB-OBTAIN NEXT[REC] INTO dataarea 
...VIEW|PCB pcbname [HOLD] [RESET]

Syntax: for Format 6

Retrieve segment specified in program at run time.

MOVE 'segmentname' TO segmentname
DB-OBTAIN IMSREC segmentname FROM dataarea
... VIEW pcbname|PCB pcbname

Syntax: for Format 7

Retrieve dependent of current record.

DB-OBTAIN REF recordname1 CURRENT
... REC recordname2 [WHERE fieldname operator value]

Syntax: for Format 8

Retrieve segment from PSB with multiple PCBs.

DB-OBTAIN REC recordname
... [WHERE fieldname operator value]
... VIEW|PCB pcbname

Syntax: for Format 9

Retrieve qualified or subscripted field.

DB-OBTAIN REC recordname
... WHERE keyname operator fieldname
... OF dataarea|SUB (number)

Syntax: for Format 10

Retrieve dependent record via concatenated key.

DB-OBTAIN REC recordname
... CKEYED dataname

Syntax Rules: for Format 2

Fieldname1 can be a primary key, secondary key, segment sequence field, or segment search field. If it is a secondary key made up of more than one field, the data name supplied as value must be a concatenation of all fields composing the index.

Syntax Rules: for Format 3

The number of values listed within parentheses must equal the number of source fields making up the index, and be listed in the same order. Values can be qualified or subscripted.

Syntax Rules: for Format 4

Ensure that all segments lie along a single imaginary path that runs from the root down to the dependent segment, and are in the same logical database.
Specify segments in hierarchical order.
With a compound DB-OBTAIN, code REC for the lowest level segment.

Syntax Rules: for Format 5

Do not use with IMS call codes.
NEXTREC generates IMS retrieval call without segment search arguments (SSAs).
Do not use field qualifications with NEXTREC and IMSREC.

Syntax Rules: for Format 6

Code only one segment per call. Move the correct IMS segment name into Working-Storage before issuing the call.
Do not use field qualifications with NEXTREC and IMSREC.

Syntax Rules: for Format 7

Code CURRENT only with REF.
Code CURRENT for the first level specified.
Do not qualify a CURRENT segment in any other way; you can qualify segments below as usual.

General Rules:

Use the CCODE keyword to include additional IMS command codes. See the topic IMS command codes for a list of valid CCODEs.

Parameters:

[:](`altvalue`)	See the information on Alternate values and Host variables in the General Rules section of the Help topic Database Calls.
REC `recordname`	COBOL record or IMS segment to process.
HOLD	Hold a record for modification or deletion. Code only once, at end of call.
RESET	Reset database or file positioning to the beginning. Code only once, at end of call.
WHERE fieldname operator value	`Operator` can be =, ^=, >, <, >=, <=, native SQL predicates (such as LIKE and BETWEEN).
FIRST	Establish positioning at the first occurrence of the specified segment; generate IMS code F.
LAST	Establish positioning at the last occurrence of the specified segment; generate IMS code L.
INTO `dataarea`	Specify I/O area where the program reads a record.
REF `recordname`	Specify a COBOL record to reference. Under IMS, the program uses the referenced segment for navigating the database.
REC`segmentname`
VIEW\|PCB `pcbname`	Specify the PCB used when the PSB contains multiple PCBs for the same database. Must be last keyword in call.
IMSREC `segmentname`	Specify that the segment name (maximum 8 characters) is in a Working-Storage variable for the program to read, modify, add, or delete.
CKEYED `dataname`	Single data area containing concatenated key information.

Examples:

Do a compound DB-OBTAIN to obtain SEGMENT-C.

DB-OBTAIN REF SEGMENT-A WHERE KEY-A = VALUE-A
... REF SEGMENT-B WHERE KEY-B = VALUE-B
... REC SEGMENT-C WHERE KEY-C = VALUE-C HOLD

Do a path call to retrieve three segments. Use REC at each level to indicate that each record is retrieved.

DB-OBTAIN REC SEGMENT-A WHERE KEY-A = VALUE-A
... REC SEGMENT-B WHERE KEY-B = VALUE-B
... REC SEGMENT-C HOLD

Do a path call to retrieve specific records (SEGMENT-A, SEGMENT-C) at certain levels. Specify that SEGMENT-B is used for qualification only and is not retrieved.

DB-OBTAIN REC SEGMENT-A WHERE KEY-A = VALUE-A
... REF SEGMENT-B WHERE KEY-B = VALUE-B
... REC SEGMENT-C HOLD

Using position, qualify database access. First, retrieve RECORD-A. Then, retrieve RECORD-B; restrict the retrieval of RECORD-B to children of the previously retrieved RECORD-A.

DB-OBTAIN REC RECORD-A WHERE FIELD-1 = VALUE-1
DB-OBTAIN REF RECORD-A CURRENT
... REC RECORD-B WHERE FIELD-2 = VALUE-2

Retrieve the last occurrence of RECORD-B under a specific RECORD-A.

DB-OBTAIN REF RECORD-A WHERE FIELD-1 = VALUE-1
... REC RECORD-B LAST

Reference the next occurrence of RECORD-A; retrieve the first occurrence of RECORD-B under RECORD-A and read it into DATAAREA-B. Also, retrieve the first occurrence of RECORD-C under RECORD-B and read it into DATAAREA-C.

DB-OBTAIN REF RECORD-A
... REC RECORD-B INTO DATAAREA-B
... REC RECORD-C INTO DATAAREA-C

Retrieve RECORD-A based on the value of the data name FIELD-X, which is found inDATAAREA-Z.

DB-OBTAIN REC RECORD-A
... WHERE KEY-A = FIELD-X OF DATAAREA-Z

Assume FIELD-X is an array as shown. First, retrieve RECORD-A based on the value of the seventh occurrence of FIELD-X. Then, obtain a dependent record by specifying its concatenated key.

01  FIELD-X OCCURS 10 TIMES PIC X(10).
        .
        .
DB-OBTAIN REC RECORD-A
... WHERE KEY-A = FIELD-X SUB(7)
        .
        .
DB-OBTAIN REC RECORD-C
... CKEYED WS-FIELD

SQL

DB-OBTAIN:

Selects an entire row qualified on one column
Selects an entire row qualified on multiple columns
Selects specific columns from a row
Names an alternate data name into which the host-variable structure is moved
Joins columns from more than one table
Specifies a union

Syntax: for Format 1

Qualified; select all columns.

DB-OBTAIN REC copylibname-REC
... WHERE column operator [:]altvalue|column
... [AND|OR column operator [:]altvalue|column]
        .
        .
        .
... [AND|OR column operator [:]altvalue|column]
... [INTO dataname]
... [FETCH FIRST 1]
... [QUERYNO number]
... [WITH [CS|RS|RR|UR]]

Syntax: for Format 2

Unqualified; select all columns.

DB-OBTAIN REC copylibname-REC
... [INTO dataname]
... [FETCH FIRST 1]
... [QUERYNO number]
... [WITH [CS|RS|RR|UR]]

Syntax: for Format 3

Implicit Join retrieving all columns from two tables.

DB-OBTAIN REC correlname1.copylibname-REC
... REC correlnameN.copylibname-REC
... [WHERE correlname.column oper
... [:]altvalue|correlname.column
... [AND|OR correlname.column oper
... [:]altval|correlname.column]
        .
        .
        .
... [AND|OR correlname.column oper
... [:]altvalue|correlname.col]]
... [FETCH FIRST 1]
... [QUERYNO number]
... [WITH [CS|RS|RR|UR]]

Syntax: for Format 4

Select specific columns.

DB-OBTAIN REC copylibname-REC
... [DISTINCT]
... column1 [(altvalue)] [AS name] [... columnN
... [(altvalue)]]
... [WHERE [correlname.]column operator
... [:]altvalue|column]
... [FETCH FIRST 1]
... [QUERYNO number]
... [WITH [CS|RS|RR|UR]]

Syntax: for Format 5

Specify an explicit Join.

DB-OBTAIN REC correlname1.copylibname-REC
... [DISTINCT]
... [column1 [... columnN]]|[NONE]
           .
           .
           .
... [[INNER JOIN|RIGHT OUTER JOIN|LEFT OUTER JOIN|FULL OUTER JOIN] 
... ON joincondition]
... REC correlnameN.copylibname-REC
... [column1 [AS name] [... columnN]]|[NONE]
... [WHERE correlname.column1 oper
... [:]value|correlname.column2
... [AND|OR correlname.column3 oper
... [:]value|correlname.column4
           .
           .
           .
... AND|OR correlname.columnN op
... [:]value|correlname.columnN]]
... [FETCH FIRST 1]
... [QUERYNO number]
... [WITH [CS|RS|RR|UR]]

Parameters:

REC `copylibname`-REC	Specify the 01-level name of the COBOL row layout in the DCLGEN or copybook information. Copybook library name of source data. Cannot be the same as any cursor names or DB-PROCESS-ID names.
WHERE column operator	`Column` is the column on which to qualify the selection. `Operator` can be =, ^=, >, <, >=, <=, native SQL predicates (such as LIKE and BETWEEN).
`altvalue`	`Altvalue` can be a literal or data name. See also the information on Alternate values in the General Rules section of the Help topic Database Calls.
FETCH FIRST 1	Limits the number of rows that can be fetched. Only use on DB-OBTAIN if the query can result in more than a single row. “Fetch First 1” indicates that only one row should be retrieved.
INTO `dataname`	Move host variable structure into the alternate data structure dataname. Can code before or after WHERE. Data moves after the actual SQL call via a MOVE statement. Generated code isIF OK-ON-RECMOVE `hostname` TO `dataname`
`correlname`.	Correlation name (maximum 18 characters); end with a period. Required if `columnname` is in a select list or if the WHERE clause appears in multiple joined tables.
WITH	Specifies the isolation level at which the statement is executed.
	CS	Cursor stability
	RS	Read stability
	RR	Repeatable read
	UR	Uncommited read - can only be specified if the result table is read-only.
DISTINCT	Eliminate all but one row from each set of duplicate rows. Duplicate rows have identical selected columns from the results table. You can use one or more DISTINCT keywords on selects.
`column1` [(`altvalue`)]	`Column1` can be a column name or one of the following expressions: NEXT VALUE FOR `sequencename` PREVIOUS VALUE FOR `sequencename`
INNER JOIN ON `joincondition`	Combines each row of the left table with every row of the right table keeping only the rows where the join-condition is true. If no join operator is specified, INNER is implicit
RIGHT OUTER JOIN ON `joincondition`	Includes the rows from the right table that were missing from the inner join.
LEFT OUTER JOIN ON `joincondition`	Includes the rows from the left table that were missing from the inner join.
FULL OUTER JOIN ON `joincondition`	Includes rows from both tables that were missing from the inner join.

Examples:

Select the rows where PM_PART_NO equals 123; move the data to an alternate area, WS-D2MAST-RECORD in Working-Storage.

DB-OBTAIN REC D2TAB-REC
... WHERE PM_PART_NO = '123'
... INTO WS-D2MAST-RECORD

Select only one row (if duplicates exist) based on multiple selection criteria.

DB-OBTAIN REC D2TAB-REC
... DISTINCT
... WHERE PM_PART_SHORT_DESC = 'WIDGET'
... AND PM_COLOR = 'RED'

Select only columns PM_PART_NO and PM_COLOR, from rows of table D2MASTER based on multiple selection criteria; eliminate duplicate rows. For column PM_PART_NO, move the data to the default destination, the COBOL host-variable of the same name; for column PM_COLOR, name an alternate destination, Working-Storage field WS-COLOR.

DB-OBTAIN REC D2TAB-REC
... DISTINCT
... PM_PART_NO PM_COLOR (WS-COLOR)
... WHERE PM_PART_SHORT_DESC = 'WIDGET'
... AND PM_COLOR = 'RED'

This example shows a DB-OBTAIN that uses SQL functions UPPER, LEFT, ROUND, CEILING, SIGN, COS, LOG, and DAYOFYEAR. It also shows usage of a FULL OUTER JOIN and WITH CLAUSE.

DB-OBTAIN
... REC A.EMSAVING-REC
...     ACCOUNT_NUMBER (ACCOUNT-NUMBER)
...     UPPER((CUSTOMER_NAME),CUSTOMER-NAME)
...     LEFT((ADDRESS_1,10),WS-ADDRESS1)
...     ADDRESS_2 (ADDRESS-2)
...     CITY (CITY)
...     STATE (STATE)
...     ZIP_CODE (ZIP-CODE)
...     ROUND((CURRENT_BALANCE,1),CURRENT-BALANCE)
... FULL OUTER JOIN
... ON
... A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
... REC B.EMSTOCK-REC
...    ENDING_BALANCE (ENDING-BALANCE)
...    CEILING((BEGIN_BALANCE),BEGIN-BALANCE)
...    SIGN((CHANGE),CHANGE)
...    CURRENT_BALANCE (CURRENT-BALANCE)
...    STOCK_NAME (STOCK-NAME)
...    STOCK_SYMBOL (STOCK-SYMBOL)
...    GAINS_CODE (GAINS-CODE)
...    COS((SHARES),WS-FLOAT2)
...    LOG((DIVIDENDS),WS-FLOAT1)
...    MARKET_VALUE (MARKET-VALUE)
...    DATE_PURCHASED (DATE-PURCHASED)
...    DAYOFYEAR((DATE_SOLD),WS-INTEGER)
...    STOCK_TYPE (STOCK-TYPE)
... WHERE
... A.STATE = 'NY'
... WITH UR

This example shows a DB-OBTAIN using the AS clause, FETCH FIRST clause, and the SQL Function LEFT:

DB-OBTAIN
... REC CUSTMAST-REC
...    CUSTOMER_NAME
...    LEFT((ADDRESS_1,10),WS-ADDRESS,'Y') AS ADDRESSX
...    CITY
...    STATE
... WHERE
... CUSTOMER_NUMBER > #MASTX-CUSTNO
... FETCH FIRST 1

VSAM Batch

DB-OBTAIN:

Reads records from a file.
Provides a starting point in a file that meets specified key criteria in the call.
Reads calls sequentially, in ascending key order (sequential DB-OBTAIN).
Reads a record based upon specified key criteria (direct DB-OBTAIN).
Positions a file for a sequential DB-OBTAIN, based upon key criteria (positional DB-OBTAIN).

Syntax: for Format 1

Sequential.

DB-OBTAIN REC recordname 
... [VIEW keyname] [INTO dataarea] [RESET]

Syntax: for Format 2

Direct.

DB-OBTAIN REC recordname
... WHERE keyname operator value [SUB value]
... [OF dataarea] [INTO dataarea]

Syntax: for Format 3

Positional.

DB-OBTAIN REF recordname
... WHERE keyname operator value [SUB[SCRIPT] value] 
... [OF dataarea]

General Rule:

AMB locates a variable length record description directly under the associated FD and places the fixed length record descriptions in Working-Storage.

Parameters:

REC `recordname`	COBOL record to process.
VIEW `keyname`	Specify key primary or alternate key.
INTO `dataarea`	Specify I/O area where the program reads a record.
RESET	Reset database or file positioning to the beginning. Code only once, at end of call. Code with PREV or KLEN, to reset file position to end. Alternate reset method: prior to retrieving under VSAM Batch, set RESET-OBTAIN flag to TRUE
WHERE keyname operator value	`Operator` can be =, EQ, >=, GTEQ. `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 `dataarea`	Qualify the I/O area moving to the `value` field, when more than one structure in the Data Division contains the field. Optionally code IN instead of OF.
REF `recordname`	Specify a COBOL record to reference. Under IMS, the program uses the referenced segment for navigating the database.
QUERYNO `number`	Specifies the number to be used for this SQL statement in EXPLAIN output and trace records.

Examples:

Read records sequentially by the CUST-NUMBER key.

DB-OBTAIN REC CUST-RECORD VIEW CUST-NUMBER

Read records sequentially; store record in an alternate storage area.

DB-OBTAIN REC CUST-RECORD
... INTO CUST-RECORD-SAVE-AREA

Read records by CUST-NUMBER key; identify the subscripted SCREEN-CUST-NUMBER that is used as the key search value. After successful execution, establish file position so that a sequential DB-OBTAIN can read the next record.

DB-OBTAIN REC CUST-RECORD
... WHERE CUST-NUMBER = SCREEN-CUST-NUMBER
... SUB (ROW-CTR)

Verify the existence of a CUST-NUMBER and (if successful) provide a starting point in the file for a sequential DB-OBTAIN to execute.

DB-OBTAIN REF CUST-RECORD
... WHERE CUST-NUMBER = SCREEN-CUST-NUMBER
IF  OK-ON-REC
    DB-OBTAIN REC CUST-RECORD
    ... VIEW CUST-NUMBER

VSAM Online

DB-OBTAIN:

Reads records from a file.
Provides a starting point in a file that meets specified key criteria in the call.
Reads calls sequentially, in ascending key order (sequential DB-OBTAIN).
Reads a record based upon specified key criteria (direct DB-OBTAIN).
Positions a file for a sequential DB-OBTAIN, based upon key criteria (positional DB-OBTAIN).

Syntax: for Format 1

Sequential.

DB-OBTAIN REC recordname 
... [VIEW keyname] [INTO dataarea]
... [HOLD] [PREV[IOUS]]
... [REQID number] [RESET]
... [SYSID systemname] [DDN ddname]

Syntax: for Format 2

Direct.

DB-OBTAIN REC recordname
... WHERE keyname operator value [SUB value]
... [OF dataarea] [INTO dataarea]
... [KLEN|KEYLENGTH value]
... [HOLD] [REQID number]
... [SYSID systemname] [DDN ddname]

Syntax: for Format 3

Positional.

DB-OBTAIN REF recordname
... WHERE keyname operator value [SUB value] [OF dataarea]
... [KLEN value] [RESETBR] [REQID number]
... [SYSID systemname] [DDN ddname]

General Rule:

Always follow a DB-OBTAIN HOLD with a DB-MODIFY or DB-ERASE.

Parameters:

REC `recordname`	COBOL record to process.
VIEW `keyname`	Specify key primary or alternate key.
INTO `dataarea`	Specify I/O area where the program reads a record.
HOLD	Hold a record for modification or deletion. Code only once, at end of call. Do not code with PREV or KLEN.
PREV[IOUS]	Perform a reverse sequential browse starting at the last record in file. Do not code with HOLD or KLEN.
REQID `number`	Unique browse identifier for performing a simultaneous browse on the same key; is a single integer (0 - 9). Assign &VS-ENDBR-CONTROL = "USER" in the AMB CNTL file APVSAMIN.
RESET	Reset database or file positioning to the beginning. Code only once, at end of call. Code with PREV or KLEN, to reset file position to end. Alternate reset method: prior to retrieving under VSAM Online, set RESET-OBTAIN flag to TRUE
SYSID `systemname`	Remote system name (maximum 4 characters); can be a literal region name or a Working-Storage field.
DDN `ddname`	Specify file `ddname`; can be a literal or data name defined as PIC X(8). Supply a value to the name option of CICS DATASET.
WHERE keyname operator value	`Operator` can be =, EQ, >=, GTEQ. `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 `dataarea`	Qualify the I/O area moving to the `value` field, when more than one structure in the Data Division contains the field. Optionally code IN instead of OF.
KLEN\|KEYLENGTH `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. Do not use with HOLD.
REF `recordname`	Specify a COBOL record to reference. Under IMS, the program uses the referenced segment for navigating the database.
RESETBR	Reset active browse on key name; generate CICS RESETBR.

Examples:

Read records sequentially by the CUST-NUMBER key.

DB-OBTAIN REC CUST-RECORD VIEW CUST-NUMBER

Sequentially read records into an alternate storage area and hold a record for updating. The VSAM Generator ends the sequential read and rereads the file via the primary key for updating. The sequential read is then resumed on the next execution of the DB-OBTAIN call.

DB-OBTAIN REC CUST-RECORD
... INTO CUST-RECORD-SAVE-AREA
... HOLD
      .
      .
      .
DB-MODIFY REC CUST-REC

Read records by CUST-NUMBER key. Identify the subscripted SCREEN-CUST-NUMBER that is used as the key search value. Establish file position for a sequential DB-OBTAIN.

DB-OBTAIN REC CUST-RECORD
... WHERE CUST-NUMBER = SCREEN-CUST-NUMBER SUB 
(ROW-CTR)

Read by ORDR-NUMBER; hold the record for updating. Note that if ORDR-NUMBER was an alternate key, the file would be reread via the primary key for updating.

DB-OBTAIN REC ORDR-RECORD
... WHERE ORDR-NUMBER = CUST-ORDR-NUMBER HOLD

Verify the existence of CUST-RECORD with the value specified in CUST-NUMBER and (if successful) provide a starting point in the file for a sequential DB-OBTAIN.

DB-OBTAIN REF CUST-RECORD
... WHERE CUST-NAME = SCREEN-CUST-NAME
IF  OK-ON-REC
    DB-OBTAIN REC CUST-RECORD... VIEW CUST-NAME