Previous Topic Next topic Print topic


DB-PROCESS

Combine record retrieval and looping functions into one call. DB-PROCESS provides a built-in loop structure to process records and rows. The loop structure can include blocks of user-supplied logic and AMB-generated control fields. User logic executes once for each successful iteration of DB-PROCESS, and can include:
Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.
  • Any AMB DB call, except DB-OPEN, DB-DECLARE, or DB-CLOSE
  • Record and row processing code, for example, MOVE DATA TO SCREEN

AMB generates the following fields, enabling you to logically terminate the loop structure.

APS-END-PROCESS

S-COBOL flag initialized FALSE. To end the process loop, set flag to TRUE.

name-END-PROCESS

AMB generates this flag, where name is the PROCESS-ID name. Use this flag when using DB-PROCESS-ID clauses for nested loops.

name-PROCESS-CTR

AMB generates this counter, where name is the PROCESS-ID name. Use this counter when using DB-PROCESS-ID clauses for nested loops.

APS-PROCESS-CTR

AMB increments this counter at each process loop execution. This counter controls looping, serves as a subscript when moving data into a table or screen fields, and counts processed records

The specific purposes of this call vary depending on the target.

Targets:

  • IMS DB
  • SQL
  • VSAM Batch
  • VSAM Online

IMS DB

DB-PROCESS processes:
  • Records that satisfy the key qualification (key qualified)
  • Records sequentially (unqualified)
Syntax: for Format 1

Key-qualified.

DB-PROCESS REC recordname
... [WHERE keyname operator value [SUB[SCRIPT] value] 
... [OF|IN dataarea]]
... [DB-PROCESS-ID name] [INTO dataarea] [HOLD] [RESET]
... [VIEW|PCB pcbname]
  Controlled logic block
Syntax: for Format 2

Unqualified.

DB-PROCESS REC recordname
... [DB-PROCESS-ID name] [INTO dataarea]
... [HOLD] [RESET]
... [VIEW|PCB pcbname]
  Controlled logic block
General Rules:
  1. Processing begins at a previously established position in the database or, if RESET coded, at the beginning of the database.
  2. Use the CCODE keyword to include additional IMS command codes. See the topic IMS command codes for a list of valid CCODEs.
Parameters:

REC recordname

IMS segment to process.

WHERE keyname operator value

Operator can be =, EQ, >, GT, <, LT, >=, GE, <=, LE, <>, NE, ^=. 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.

DB-PROCESS-ID name

Generate an end-process flag named name-END-PROCESS, and a counter named name-PROCESS-CTR.

Name (maximum 12 characters) must be unique, and cannot be the same as the subschema copylib names.

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.

RESET

Reset database or file positioning to the beginning. Code only once per call, at end.

Alternate reset method: prior to retrieving under VSAM Batch or Online, set RESET-OBTAIN flag to TRUE; under IMS DB, set RESET-POSITION flag to TRUE.

VIEW|PCB pcbname

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

Example:

Process CUST-RECORD; execute logic upon each successful read. Note that CUST-PROCESS-CTR serves as a subscript for the screen fields.

DB-PROCESS REC CUST-RECORD
... WHERE CUST-KEY >= SCREEN-KEY
... DB-PROCESS-ID CUST
        IF  CUST-PROCESS-CTR > SCREEN-MAX
            TRUE CUST-END-PROCESS
        ELSE
            SCREEN-CUST    (CUST-PROCESS-CTR) = CUST-NAME
            SCREEN-PHONE   (CUST-PROCESS-CTR) = CUST-PHONE
            SCREEN-ADDRESS (CUST-PROCESS-CTR) = CUST-ADDRESS
IF NTF-ON-REC
    SCREEN-MSG = 'CUSTOMER NOT FOUND'
ELSE-IF END-ON-REC
    SCREEN-MSG = 'END OF CUSTOMER RECORDS'

Sample loop syntax:

/* Begin process loop
DB-PROCESS REC recordname
... WHERE keyname operator value keywords
... [DB-PROCESS-ID name]
    IF  APS-PROCESS-CTR|name-PROCESS-CTR > value
    /* End process loop
        TRUE APS-END-PROCESS|name-END-PROCESS
    ELSE
        /* User-written record processing logic
IF ...
/*Logic executed after process loop termination
/*    Includes file status checking

SQL

DB-PROCESS simplifies cursor row processing by:
  • Declaring a cursor
  • Opening a cursor for processing
  • Defining a loop flag and a loop counter
  • Providing logic for retrieving rows from the results table
  • Executing user-written row processing code
  • Processing closing the cursor set file at the end of processing
  • Processing entire rows throughout a cursor set
  • Processing specific columns throughout a cursor set
  • Processing specific columns and rows throughout a cursor set
  • Processing columns from more than one table, such as a join)
Syntax: for Format 1

Unqualified - select all columns.

DB-PROCESS REC copylibname-REC
... [DB-PROCESS-ID name]
... [DB-LOOP-MAX=number]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING FOR number ROWS]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]
... [INTO dataname]
  Controlled logic block
Syntax: for Format 2

Qualified - select all columns.

DB-PROCESS REC copylibname-REC
... [DB-PROCESS-ID name]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING FOR number ROWS]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... WHERE column operator [[:]]altvalue]|column
... [AND|OR column operator [[:]altvalue]|column]
          .
          .
          .
... [AND|OR column operator [[:]altvalue]|column]
... [DB-LOOP-MAX=number]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]
... [INTO dataname]
  Controlled logic block
Syntax: for Format 3

Select specific columns.

DB-PROCESS REC copylibname-REC
... [DB-PROCESS-ID name]
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING FOR number ROWS]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... column1 [(altvalue)] [AS name] [... columnN [(altvalue)]]
... WHERE column operator [[:]altvalue]|column
... [AND|OR column operator [[:]altvalue]|column]
          .
          .
          .
... [AND|OR column operator [[:]altvalue]|column]
... [DB-LOOP-MAX=number]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]
  Controlled logic block
Syntax: for Format 4

Join columns from two or more tables:

DB-PROCESS REC correlname.copylibname-REC 
... [DB-PROCESS-ID name]
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING FOR number ROWS]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [(altvalue)][AS name][... columnN [(altvalue)]]]
... REC correlname.copylibname-REC
... [column1 [(altvalue)] [... columnN [(altvalue)]]]
          .
          .
          .
... [WHERE correlname.column oper
... [[:]altval]|correlname.column
... [AND|OR correlname.column oper
... [[:]altval]|correlname.col]
          .
          .
          .
... [AND|OR correlname.column oper
... [[:]altval]|correlname.col]
... [DB-LOOP-MAX=number]
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]
  Controlled logic block
Syntax: for Format 5

Specify a UNION.

DB-PROCESS REC copylibname-REC
... [DB-PROCESS-ID name]
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING FOR number ROWS]
... [WITH [CS|RS|RR|UR]]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [(altvalue)] [AS name] [... columnN [(altvalue)]]]
... [WHERE column operator [[:]altvalue]|column
... [AND|OR column operator [[:]altvalue]|column]
          .
          .
          .
... [AND|OR column operator [[:]altvalue]|column]]
... [DB-LOOP-MAX=number]
... UNION [ALL]
DB-OBTAIN REC copylibname-REC
          .
          .
          .
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]
  Controlled logic block
Syntax: for Format 6

Specify a Join using a join operator.

DB-PROCESS REC correlname1.copylibname-REC
... [DB-PROCESS-ID name]
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING FOR number ROWS]
... [WITH [CS|RS|RR|UR]]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [AS name] [... 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]]
... [DB-LOOP-MAX=number]
... [ORDER 
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]
  Controlled logic block
General Rules:
  1. DB-PROCESS generates and performs the DB-DECLARE, DB-OPEN, DB-FETCH, and DB-CLOSE calls within loop structure.
  2. AMB declares the cursor at the first DB-PROCESS. All subsequent calls within the loop reference the cursor. The cursor is named
    • By you, if you code DB-PROCESS-ID.
    • By AMB, which generates a copylib name and a numeric suffix, beginning with one and increasing by one with each subsequent DB-PROCESS for the same DCLGEN or copybook member.
  3. When you code WITH HOLD, a commit operation commits all the changes in the current unit of work, but releases only locks that are not required to maintain the cursor. Afterwards, you must code an initial DB-FETCH before you can execute a positioned update or delete. After the initial DB-FETCH, the cursor is positioned on the row following the one it was positioned on before the commit operation.
  4. When using ROWSET operations or multiple row operations on inserts, set up host-variable array structures to receive multiple values for each column.
  5. AMB creates default array structures for records that specify multiple-row fetches or entire-row, multiple-row inserts. The array size is the maximum row number value found specified in the FOR number ROWS clause for that table. For example:
    01   recordname-ARR.
         05  CobolHostVar1-ARR     picture occurs row-number times.
         05  IND-CobolHostVar1-ARR picture occurs row-number times.
         05  CobolHostVar2-ARR     picture occurs row-number times.
         05  IND-CobolHostVar2-ARR picture occurs row-number times.
         …..
         05  CobolHostVarn-ARR     picture occurs row-number times.
         05  IND-CobolHostVarn-ARR picture occurs row-number times.
    
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.

DB-PROCESS-ID name

Generate a cursor named name (for SQL targets), an end-process flag named name-END-PROCESS, and a counter named name-PROCESS-CTR.

DB-LOOP-MAX=number

Maximum number loops allowed. Overrides loop flags for current structure only; you can define a different limit for each DB-PROCESS.

FETCH ONLY

Specify that the table is read-only and therefore the cursor cannot be referred to in positioned UPDATE and DELETE statements. Do not code in a call that contains an UPDATE clause.

Data moves after the actual SQL call via a MOVE statement. Generated code isIF OK-ON-RECMOVE hostname TO dataname

READ ONLY

Equivalent to FETCH ONLY.

FETCH FIRST numberofrows

Limits the number of rows that can be fetched.

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.

SENSITIVE SCROLL CURSOR

Cursor is scrollable. Changes made to the base table after the result table is materialized are visible to the cursor.

DYNAMIC

The result table of the cursor when it is opened is dynamic, meaning the size of the result table and the order of rows might change when rows are inserted or deleted. This is the default.

STATIC

The size of the result table and the order of the rows do not change after the cursor is opened.

INSENSITIVE SCROLL CURSOR

Cursor is scrollable. Changes made to the base table after the result table is materialized are not visible to the cursor.

WITH ROWSET POSITIONING

Retrieve multiple rows of data from the result table as a rowset into host-variable arrays.

FOR number ROWS

Controls how many rows are returned.

Note: DB-PROCESS defaults to NEXT ROWSET in the generated FETCH logic.

WITH HOLD

Prevent the closing of a cursor as a consequence of a commit operation.

QUERYNO number

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

OPTIMIZE number

Specify estimated maximum number of rows that call will retrieve. If the call retrieves no more than number rows, performance could be improved. Specifying this argument does not prevent all rows from being retrieved.

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.

UPDATE column1columnN

Modify columns during cursor processing. In cursor processing, you cannot modify a column unless you specify UPDATE first. Do not code UPDATE with ORDER, UNION, DISTINCT, GROUP BY, or if the call specifies a join or selects column functions. Column names cannot exceed 18 characters.

ORDER [ASC|DESC] [column11 columnN]

Sort the results table in ascending (default) or descending order, based on the values in the columns specified. Specify the column either by name or by relative position in the column selection list. Specify at least one column. Do not code with UPDATE. Column names cannot exceed 18 characters.

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

WHERE column operator [:]altvalue|column

Column is the column on which to qualify the selection. Operator can be =, ^=, >, <, >=, <=, native SQL predicates (such as LIKE and BETWEEN). See also the information on Alternate values and Host variables in the General Rules section of the Help topic Database Calls.

AND|OR column operator [:]altvalue|column

Altvalue can be a literal or data name. See also the information on Alternate values and Host variables in the General Rules section of the Help topic Database Calls.

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.

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.

UNION

See UNION.

Examples:

Declare, name, and open a cursor; retrieve all rows and columns; process only one row from duplicate rows; close the cursor; move data into an alternate area.

DB-PROCESS REC D2TAB-REC
... DB-PROCESS-ID D2MAST-ID
... DISTINCT
... INTO WS-D2MAST-RECORD

Eliminate duplicate rows from the cursor set (rows with matching data in columns PM_PART_NO, PM_COLOR, IN_PART_NO, and IN_COLOR are considered duplicates). Sort cursor set in ascending order by D2MASTER columns PM_PART_NO and PM_COLOR.

DB-PROCESS REC A.D2TAB-REC
... DB-PROCESS-ID D2MAST-ID
... DISTINCT
... PM_PART_NO PM_COLOR
... REC B.D2INVEN-REC 
... IN_PART_NO IN_COLOR
... WHERE A.PM_PART_NO = B.IN_PART_NO
... AND A.PM_PART_SHORT_DESC =
... :WS-PART-SHORT-DESC 
... AND B.IN_QTY_ONHAND > 100 
... DB-LOOP-MAX=999
... ORDER A.PM_PART_NO ASC
... A.PM_COLOR ASC

Join a table to itself. Retrieve records where the IN_QTY_ONHAND column is greater than 100 and retrieve records whose IN_DATA_LAST_ORDER column matches these records.

DB-PROCESS REC A.D2INVEN-REC
... DB-PROCESS-ID D2MAST-ID
... IN_PART_NO
....IN_QTY_ONHAND
... IN_DATE_LAST_ORDER
... REC B.D2INVEN-REC
... IN_PART_NO       (:WS-PART-NO)
... IN_QTY_ONHAND    (:WS-QTY-ONHAND)
... WHERE A.IN_DATA_LAST_ORDER = B.IN_DATE_LAST_ORDER
... AND A.IN_QTY_ONHAND > 100

Use a LEFT OUTER JOIN.

DB-PROCESS
... REC A.ACCTMAST-REC
... LEFT OUTER JOIN
... ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
... REC B.EMSTOCK-REC
... WHERE DIVIDENDS IS NOT NULL
... DB-PROCESS-ID PX24

Use multiple INNER JOINs:

DB-PROCESS
... REC A.EMSTOCK-REC
...     A.ACCOUNT_NUMBER   (WS-ACCOUNT-NUMBER-1)
...     A.CUSTOMER_NAME
... INNER JOIN
... ON A.ACCOUNT_NUMBER = C.ACCOUNT_NUMBER
... REC B.EMSAVING-REC
...     B.DEPOSITS   (WS-DEPOSITS)
... INNER JOIN
... ON C.ACCOUNT_NUMBER = A.ACCOUNT_NUMBER
... REC C.ACCTMAST-REC
...     C.ACCOUNT_TYPE (WS-ACCOUNT-TYPE)
...     C.CITY
... WHERE C.CITY IS NOT NULL
... DB-PROCESS-ID CSR2

Use both a LEFT OUTER JOIN and a FULL OUTER JOIN.

DB-PROCESS
... REC A.EMSTOCK-REC
...       A.ACCOUNT_NUMBER   (WS-ACCOUNT-NUMBER-1)
...       A.CUSTOMER_NAME
... LEFT OUTER JOIN
... ON A.ACCOUNT_NUMBER = C.ACCOUNT_NUMBER
... REC B.EMSAVING-REC
...       B.DEPOSITS   (WS-DEPOSITS)
... FULL OUTER JOIN
... ON C.ACCOUNT_NUMBER = A.ACCOUNT_NUMBER
... REC C.ACCTMAST-REC
...     C.ACCOUNT_TYPE (WS-ACCOUNT-TYPE)
...     C.CITY
... REC A.EMSTOCK-REC
...       A.ENDING_BALANCE
... WHERE C.CITY IS NOT NULL
... DB-PROCESS-ID CSR7

This example shows a DB-PROCESS with both a LEFT OUTER join and a RIGHT OUTER join, usage of the AS clause, and usage of SQL Functions ABS, and ROUND:

DB-PROCESS
... REC A.ACCTMAST-REC
...     ACCOUNT_NUMBER
... LEFT OUTER JOIN
... ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
... REC B.EMSTOCK-REC
...     DIVIDENDS
...     ABS((BEGIN_BALANCE),WS-ABS,'N') AS ABSX
...     ROUND((CHANGE, 1),WS-CHANGE-ROUNDED,'N') AS ROUNDX
... RIGHT OUTER JOIN
... ON A.ACCOUNT_NUMBER = C.ACCOUNT_NUMBER
... AND A.ACCOUNT_NUMBER = '11111'
... REC C.EMSAVING-REC
...     ABS((DEPOSITS),WS-DEPOSITS,'N') AS DEPOSITX
...     WITHDRAWALS
... WHERE DEPOSITS IS NOT NULL
... ORDER BY ABSX, ROUNDX
... DB-PROCESS-ID PX03

This example shows a DB-PROCESS using the FOR READ ONLY, FETCH FIRST, WITH, and QUERYNO clauses, INSENSITIVE SCROLL CURSOR, and SQL functions LOWER and RIGHT:

DB-PROCESS
... REC A.ACCTMAST-REC
...     CUSTOMER_NUMBER
...     LOWER(CUSTOMER_NAME)
...     ACCOUNT_NUMBER
...     ACCOUNT_TYPE
...     ADDRESS_1
...     ADDRESS_2
...     RIGHT((CITY,5),WS-CITY-SHORT,'Y')
...     STATE
...     ZIP_CODE
... WHERE (
... CUSTOMER_NUMBER = #MASTX-CUSTNO
... ) AND
... ((A.CUSTOMER_NUMBER = #PX-FWDX-ACCTMASTER-0001 AND
... A.ACCOUNT_NUMBER > #PX-FWDX-ACCTMASTER-0002 )
... OR
... (A.CUSTOMER_NUMBER > #PX-FWDX-ACCTMASTER-0001 ))
... ORDER
... 01
... 03
... DB-PROCESS-ID PX01
... READ ONLY
... INSENSITIVE SCROLL CURSOR
... WITH CS
... QUERYNO 2
... FETCH FIRST 40

Sample loop syntax:

/* Begin process loop
DB-PROCESS REC recordname
    /* Custom row processing code
    IF APS-PROCESS-CTR|name-PROCESS-CTR > value
    /* End process loop
        TRUE APS-END-PROCESS|name-END-PROCESS
    ELSE
        /* Custom record processing logic
        /*     Can include file status 
/* Logic executed after process loop termination
/*     Can include file status checking

This example shows use of a sensitive dynamic scroll cursor:

DB-PROCESS 
...  REC A.ACCTMAST-REC
...  CUSTOMER_NUMBER
...  CUSTOMER_NAME
...  ACCOUNT_NUMBER
...  ACCOUNT_TYPE
...  WHERE
...  ACCOUNT_NUMBER = '111111'
...  DB-PROCESS-ID THS-CSR1
...  SENSITIVE DYNAMIC SCROLL CURSOR

This example shows ROWSET positioning:

DB-PROCESS 
...  REC A.ACCTMAST-REC
...  CUSTOMER_NUMBER
...  CUSTOMER_NAME
...  ACCOUNT_NUMBER
...  ACCOUNT_TYPE
...  WHERE
...  ACCOUNT_NUMBER = '111111'
...  DB-PROCESS-ID THS-CSR3
...  SENSITIVE SCROLL CURSOR WITH ROWSET POSITIONING
...  FOR 9 ROWS

This example shows a multiple row insert:

DB-STORE
... REC ACCTMAST-REC
... CUSTOMER_NUMBER (:CUSTOMER-NUMBER-ARR)
... CUSTOMER_NAME (:CUSTOMER-NAME-ARR)
... ACCOUNT_NUMBER (:ACCOUNT-NUMBER-ARR)
... ACCOUNT_TYPE (DEFAULT)
... FOR 5 ROWS 
... NOT ATOMIC

This example shows storing multiple rows:

DB-STORE REC D2MASTER-REC FOR 10 ROWS

VSAM Batch

DB-PROCESS processes records:
  • Sequentially, beginning at the position established by the key qualification (key qualified)
  • Sequentially, beginning at the beginning, end, or a previously defined position in the file (unqualified)
Syntax: for Format 1

Qualified.

DB-PROCESS REC recordname WHERE keyname operator value
... [DB-PROCESS-ID name] [SUB value]
... [OF dataarea] [INTO dataarea]
  Controlled logic block
Syntax: for Format 2

Unqualified.

DB-PROCESS REC recordname [VIEW keyname]
... [DB-PROCESS-ID name] [INTO dataarea] [RESET]
  Controlled logic block
General Rules:
  1. The ENDFILE condition can be determined outside of the process loop, even after DB-PROCESS ends the sequential browse.
  2. You must precede a DB-PROCESS call with a DB-OPEN call, and code a subsequent DB-CLOSE call. AMB does not support automatic file opening and closing.
  3. AMB locates a variable length record description directly under the associated FD and places the fixed length record descriptions in Working-Storage.

    See also the section All Formats - Stand-alone Applications below.

Parameters:

REC recordname

COBOL record to process.

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.

DB-PROCESS-ID name

Generate a cursor named name (for SQL targets), an end-process flag named name-END-PROCESS, and a counter named name-PROCESS-CTR.

Name (maximum 12 characters) must be unique, and cannot be the same as the subschema copylib names.

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.

INTO dataarea

Specify I/O area where the program reads a record.

VIEW keyname

Specify primary or alternate key.

RESET

Reset database or file positioning to the beginning. Code only once per call, at end.

Alternate reset method: prior to retrieving, set RESET-OBTAIN flag to TRUE.

Example:

Process CUST-RECORD; execute logic upon each successful read. Note that CUST-PROCESS-CTR serves as a subscript for the screen fields.

DB-PROCESS REC CUST-RECORD
... WHERE CUST-KEY >= SCREEN-KEY
... DB-PROCESS-ID CUST
        IF  CUST-PROCESS-CTR > SCREEN-MAX
            TRUE CUST-END-PROCESS
        ELSE
            SCREEN-CUST    (CUST-PROCESS-CTR) = CUST-NAME
            SCREEN-PHONE   (CUST-PROCESS-CTR) = CUST-PHONE
            SCREEN-ADDRESS (CUST-PROCESS-CTR) = CUST-ADDRESS
IF NTF-ON-REC
    SCREEN-MSG = 'CUSTOMER NOT FOUND'
ELSE-IF END-ON-REC
    SCREEN-MSG = 'END OF CUSTOMER RECORDS'

Sample loop syntax

/* Begin process loop
DB-PROCESS REC recordname
... WHERE keyname operator value keywords
... [DB-PROCESS-ID name]
    IF  APS-PROCESS-CTR|name-PROCESS-CTR > value
    /* End process loop
        TRUE APS-END-PROCESS|name-END-PROCESS
    ELSE
        /* User-written record processing logic
IF ...
/*Logic executed after process loop termination
/*    Includes file status checking

VSAM Online

DB-PROCESS processes records:
  • Sequentially, beginning at the position established by the key qualification (key qualified)
  • Sequentially, beginning at the beginning, end, or a previously defined position in the file (unqualified)
Syntax: for Format 1

Key qualified.

DB-PROCESS REC recordname WHERE keyname operator value
... [DB-PROCESS-ID name] [SUB value]
... [OF dataarea] [INTO dataarea]
... [KLEN value] [HOLD] [PREV]
... [REQID number] [SYSID systemname]
... [DDN ddname]
  Controlled logic block
Syntax: for Format 2

Unqualified.

DB-PROCESS REC recordname
... [DB-PROCESS-ID name] [INTO dataarea]
... [HOLD] [PREV] [RESET]
... [REQID number] [SYSID systemname]
... [VIEW keyname] [DDN ddname]
  Controlled logic block
General Rules:
  1. Because DB-PROCESS terminates the active browse when the process loop is terminated, it is not necessary to end the browse with the DB-FREE call.
  2. DB-PROCESS unlocks a locked record when the process loop terminates. Unlocking a record via a DB-FREE UNLOCK is unnecessary.
  3. The ENDFILE condition can be determined outside of the process loop, even after DB-PROCESS ends the sequential browse.

    See also All Formats.

Parameters:

REC recordname

COBOL record to process.

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.

DB-PROCESS-ID name

Generate a cursor named name (for SQL targets), an end-process flag named name-END-PROCESS, and a counter named name-PROCESS-CTR.

Name (maximum 12 characters) must be unique, and cannot be the same as the subschema copylib names.

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.

INTO dataarea

Specify I/O area where the program reads a record.

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.

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.

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.

VIEW keyname

Specify key primary or alternate key.

Examples:

Process CUST-RECORD; execute logic upon each successful read. Note that CUST-PROCESS-CTR serves as a subscript for the screen fields.

DB-PROCESS REC CUST-RECORD
... WHERE CUST-KEY >= SCREEN-KEY
... DB-PROCESS-ID CUST
        IF  CUST-PROCESS-CTR > SCREEN-MAX
            TRUE CUST-END-PROCESS
        ELSE
            SCREEN-CUST    (CUST-PROCESS-CTR) = CUST-NAME
            SCREEN-PHONE   (CUST-PROCESS-CTR) = CUST-PHONE
            SCREEN-ADDRESS (CUST-PROCESS-CTR) = CUST-ADDRESS
IF NTF-ON-REC
    SCREEN-MSG = 'CUSTOMER NOT FOUND'
ELSE-IF END-ON-REC
    SCREEN-MSG = 'END OF CUSTOMER RECORDS'

Sample loop syntax:

/* Begin process loop
DB-PROCESS REC recordname
... WHERE keyname operator value keywords
... [DB-PROCESS-ID name]
    IF  APS-PROCESS-CTR|name-PROCESS-CTR > value
    /* End process loop
        TRUE APS-END-PROCESS|name-END-PROCESS
    ELSE
        /* User-written record processing logic
IF ...
/*Logic executed after process loop termination
/*    Includes file status checking

Sample loop syntax:

/* Begin process loop
DB-PROCESS REC recordname
    /* Custom row processing code
    IF APS-PROCESS-CTR|name-PROCESS-CTR > value
    /* End process loop
        TRUE APS-END-PROCESS|name-END-PROCESS
    ELSE
        /* Custom record processing logic
        /*     Can include file status checking
/* Logic executed after process loop termination
/*     Can include file status checking

All Formats - Stand-alone Applications

The following rules apply to all formats for DB-PROCESS used in stand-alone applications:
  1. Under SQL, code file status checking inside a DB-PROCESS loop; under IMS DB, and VSAM Batch or Online, code file status checking outside the process loop.
  2. You can nest DB-PROCESS calls if you supply each call with a unique DB-PROCESS-ID clause, thus providing controlled exits for each.
  3. DB-PROCESS continues looping until the program reaches a TRUE APS-END-PROCESS or TRUE name-END-PROCESS, an invalid or end of file condition, the limit defined by a AMB internal counter.
  4. AMB defines a loop limit as follows:
    • If you do not code DB-PROCESS-ID, the loop limit is 500, set by the AMB counter &APS-DB-PROCESS-GLOBAL-LIMIT. This counter resides in the APSMACS file APSBASE.
    • If you do code DB-PROCESS-ID, adhere to the following loop limit and counter for each target
      Target Limit AMB Counter

      IMS DB

      100
      
      &VS-IMS-LOOP-MAX
      

      SQL

      100
      
      &DB2-LOOP-MAX
      

      VSAM Batch

      999,99
      9
      
      &VS-MVS-LOOP-MAX
      

      VSAM Online

      100
      
      &VS-CICS-LOOP-
      MAX
      
  5. You can change the value of LOOP-MAX in the APSMACS file; this affects all generated applications. Or, change the LOOP-MAX limit at the program level by redefining it at the top of your program with the SYM1 or SYM2 keyword. For example:
    SYM1    % &prefix-LOOP-MAX = 200
    
Previous Topic Next topic Print topic