DB-DECLARE

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

All rows and columns in a table
All columns, from specific rows, in a table
Specific columns in a table
Specific columns, from specific rows, in a table

Note the following when coding DB-DECLARE:

Declare a cursor before coding a DB-OPEN or DB-FETCH call.
Because a declared cursor name is referenced by all subsequent calls for that cursor, code UPDATE to specify which columns can be modified; otherwise columns cannot be modified in subsequent cursor processing.
When specifying columns for sorting, identify them either by name or position in the selection list; do not mix references.

Target:

SQL

Syntax: for Format 1

Unqualified, select all columns.

DB-DECLARE cursorname copylibname-REC
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 2

Qualified, select all columns.

DB-DECLARE cursorname copylibname-REC
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... WHERE column operator [[:]altvalue]|column
... [AND|OR column operator [[:]altvalue]|column]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 3

Select specific columns.

DB-DECLARE cursorname copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... column1 [(altvalue)] [... columnN [(altvalue)]]
... [WHERE column operator [[:]altvalue]|column
... [AND|OR correlname.]column operator 
... [[:]altvalue]|column]
        .
        .
        .
... [AND|OR correlname.]column operator
... [[:]altvalue]|column]]
... [UPDATE|ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 4

Implicit Join selecting columns from two or more tables.

DB-DECLARE cursorname correlname.copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [(altvalue)] [... columnN [(altvalue)]]]
... [correlname.copylibname-REC
... [column1 [(altvalue)] [... columnN [(altvalue)]]]
        .
        .
        .
... [WHERE correlname.column oper
... [:]altvalue|correlname.column
... [AND|OR correlname.column oper
... [:]altval|correlname.column]
        .
        .
        .
... [AND|OR correlname.column oper
... [:]altvalue|correlname.col]]
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 5

Specify a UNION.

DB-DECLARE cursorname copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [(altvalue)] [... columnN [(altvalue)]]]
... [WHERE column operator [[:]altvalue]|column
... [AND|OR column operator [[:]altvalue]|column]
        .
        .
        .
... [AND|OR column operator [[:]altvalue|]column]]
... UNION [ALL]
DB-OBTAIN REC copylibname-REC
        .
        .
        .
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]

Syntax: for Format 6

Specify a Join using a join-operator.

DB-DECLARE cursorname correlname1.copylibname-REC
... [DISTINCT]
... [FETCH ONLY|READ ONLY]
... [QUERYNO number]
... [WITH HOLD]
... [OPTIMIZE number]
... [column1 [... columnN]]|[NONE]
           .
           .
           .
... [[INNER JOIN|RIGHT OUTER JOIN|LEFT OUTER JOIN|FULL OUTER JOIN] 
... ON joincondition REC]
... correlnameN.copylibname-REC
... [column1 [... columnN]]|[NONE]
           .
           .
           .
... [WHERE correlname.column1 oper
... [:]value|correlname.column2
... [AND|OR correlname.column3 oper
... [:]value|correlname.column4
           .
           .
           .
... AND|OR correlname.columnN operator
... [:]value|correlnameN]]
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC]]]
... [FETCH FIRST numberofrows]
... [WITH [CS|RS|RR|UR]]
... [SENSITIVE [DYNAMIC | STATIC] SCROLL CURSOR|INSENSITIVE SCROLL CURSOR]
... [WITH ROWSET POSITIONING]

General Rules:

Declare a cursor before coding a DB-OPEN or DB-FETCH call.
Because a declared cursor name is referenced by all subsequent calls for that cursor, code UPDATE to specify which columns can be modified; otherwise columns cannot be modified in subsequent cursor processing.
When specifying columns for sorting, identify them either by name or position in the selection list; do not mix references.
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.
The WITH HOLD clause is ignored in CICS and IMS DC.
When using ROWSET operations, set up host-variable array structures to receive multiple values for each column.

Parameters:

`cursorname`	Cursor name (maximum 12 characters) must be unique, and cannot be the same as the subschema copylib names.
`copylibname`-REC	Copybook library name of source data.
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.
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.
READ ONLY	Equivalent to FETCH ONLY.
QUERYNO `number`	Specifies the number to be used for this SQL statement in EXPLAIN output and trace records.
WITH HOLD	Prevent the closing of a cursor as a consequence of a commit operation.
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 keyword 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	Modify columns during cursor processing. In cursor processing, you cannot modify a column unless you code UPDATE first. Do not code UPDATE with UNION, DISTINCT, GROUP BY, or if call specifies a join or selects column functions.
ORDER [ASC\|DESC] [column1 ...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.
WHERE column operator [:]`altvalue`	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. Column names cannot exceed 18 characters.
AND\|OR column operator column\|[:]`altvalue`	`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.
UNION	See UNION.
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.

Examples:

Declare cursor set D2MAST-CURSOR; define to include all rows and columns in D2MASTER table; allow updating for PM_COLOR and PM_NEW_PART_NO columns.

DB-DECLARE D2MAST-CURSOR D2TAB-REC
... UPDATE PM_COLOR PM_NEW_PART_NO

Declare cursor set consisting of entire rows selected by evaluating two columns; if duplicate rows, select only one row where PM_PART_SHORT_DESC equals Working-Storage variable WS_PART_SHORT_DESC and PM_UNIT_BASE_PRICE is greater than 10 and less than 50.

DB-DECLARE D2MAST-CURSOR D2TAB-REC
... DISTINCT
... WHERE PM_PART_SHORT_DESC = 
... :WS-PART-SHORT-DESC
... AND PM_UNIT_BASE_PRICE BETWEEN 10 AND 50

Declare cursor and define its set to include two columns; select only rows that meet selection criteria; for column PM_PART_NO, move data to default destination; for column PM_COLOR, move data to alternate host-variable WS-COLOR.

DB-DECLARE D2MAST-CURSOR D2TAB-REC
... PM_PART_NO
... PM_COLOR (WS-COLOR)
... WHERE PM_PART_SHORT_DESC = 
... :WS-PART-SHORT-DESC
... AND PM_UNIT_BASE_PRICE BETWEEN 10 AND 50

Declare cursor and define its set to include two columns; select only one row that meets selection criteria. Sort columns by position within selection list; sort cursor first by PM_COLOR (second column in selection list), then within PM_COLOR by PM_PART_NO (first column).

DB-DECLARE D2MAST-CURSOR D2TAB-REC
... DISTINCT
... PM_PART_NO PM_COLOR
... WHERE PM_PART_SHORT_DESC = 
... :WS-PART-SHORT-DESC
... ORDER 2, 1

Declare a cursor set to include three columns drawn from two separate select statements, UNIONed together. Select two columns from D2TAB-REC and one column from D2INVEN-REC in the first UNION. Base selection criteria on PM_PART_NO matching IN_PART_NO, with PM_COLOR equal to the Working-Storage field WS-COLOR and IN-COLOR equal to 'BLUE'.

Select three columns from D2TAB-REC in the second UNION. Base selection criteria on PM_COLOR not equal to 'BLUE' and PM_UNITS less than 50. Sort the result table (consisting of rows returned from both select statements) by PM_PART_NO within PM_COLOR.

DB-DECLARE JOIN-CUR A.D2TAB-REC
... DISTINCT
... PM_PART_NO PM_COLOR
... B.D2INVEN_REC IN_QTY_ONHAND
... WHERE A.PM_PART_NO = B.IN_PART_NO
... AND A.PM_COLOR = :WS-COLOR
... AND B.IN_COLOR = 'BLUE'
... UNION
DB-OBTAIN REC D2TAB-REC
... PM_PART_NO PM_COLOR PM_UNITS
... WHERE PM_COLOR ^= 'BLUE'
... AND PM_UNITS < 50
... ORDER 1 ASC, 2 ASC

This example shows use of a sensitive cursor:

DB-DECLARE ORDERSCROLL
... ACCTMAST-REC
... SENSITIVE DYNAMIC SCROLL CURSOR
... WHERE ACCOUNT_NUMBER > 1000
... UPDATE CUSTOMER_NUMBER    

DB-OPEN CURSOR ORDERSCROLL

DB-FETCH CURSOR ORDERSCROLL
... SENSITIVE RELATIVE +3

DB-CLOSE CURSOR ORDERSCROLL

This example shows ROWSET positioning:

DB-DECLARE C1 
... A.ACCTMAST-REC
... WITH ROWSET POSITIONING               

DB-OPEN CURSOR C1
      
DB-FETCH CURSOR C1
... NEXT ROWSET
... FOR 9 ROWS
... INTO ACCTMAST-REC  

DB-CLOSE CURSOR C1