Embedded SQL/COBOL Support for MRI and MRF
The Db2® Precompiler supports the SQL array in INSERT, UPDATE, and DELETE operations. It allows the application to insert rows, update rows, or delete rows at the target database from a COBOL application. The FETCH statement is also supported for fetching multiple rows from a specified cursor of the server.
- Supporting Array INSERT/UPDATE/DELETE
The Db2 ESQL enables arrays to be passed as a host variable, while ESQL calls the INSERT, UPDATE, and DELETE statements as their input arguments from the COBOL application. Also, Db2 embedded runtime supports the bulk insert against Db2 except LOB columns.
To pass a cardinality for the array insert, the Db2 supports the “For n ROWS” clause in INSERT, UPDATE, and DELETE SQL’s. The variable "n" within the "For n ROWS" clause can be an integer in the range 2 - 32767, a host variable declared as integer, or short data type.
If the “For n ROWS” clause is not specified, the Db2 precompiler for COBOL takes the cardinality of the array size based on the declared size of host variables that are used in the SQL. If the host variables used are of different sizes, the minimum size of all the host variables are used as the cardinality of the bulk/array INSERT.
Users can check sqlca.sqlerrd(2) when an error occurs during an array operation. Processing stops at the row that caused the error. Thus, sqlerrd[2] gives the row number on which error occurred. Users can check sqlca.sqlerrd(3), which indicates the number of impacted records as a result of the array INSERT/UDPATE/DELETE operation.
The following example demonstrates the array INSERT through COBOL.Identification Division. Program-ID. "arrayfetch". Data Division. Working-Storage Section. copy "sqlca.cbl". EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 cnt pic s9(4) comp-5. 01 insert-rec. 03 c1 pic x(18) OCCURS 5 TIMES. 03 c2 pic s9(9) comp-5 OCCURS 5 TIMES. EXEC SQL END DECLARE SECTION END-EXEC. 77 errloc pic x(80). Procedure Division. Main Section. display "Sample COBOL program: ARRAY INSERT". EXEC SQL CONNECT TO sample END-EXEC MOVE 5 to cnt. MOVE "Row1" to c1(1). MOVE "Row2" to c1(2). MOVE "Row3" to c1(3). MOVE "Row4" to c1(4). MOVE "Row5" to c1(5). MOVE 1 to c2(1). move 10 to c2(2). MOVE 50 to c2(3). MOVE 100 to c2(4). MOVE 500 to c2(5). EXEC SQL INSERT INTO test VALUES (:c2, :c1 ) FOR :cnt ROWS END-EXEC. EXEC SQL CONNECT RESET END-EXEC. move "CONNECT RESET" to errloc. call "checkerr" using SQLCA errloc. End-Main. go to End-Prog. End-Prog. stop run.
- Support of Multi-row Fetch
Db2 Precompiler and embedded Runtime support the fetch of multiple rows by using a single FETCH statement. Db2 ESQL enables arrays to be passed as a host variable (and as their indicators) while ESQL calls the FETCH statements as their output arguments (bind-outs) from the COBOL application.
To pass the cardinality for the array FETCH statement, the Db2 Precompiler supports the “For n ROWS” clause in FETCH statement. The variable "n" in the "For n ROWS" clause can be an integer in the range 2 - 32767, a host variable declared as integer, or a short data type.
If the “For n ROWS” clause is not specified, the Db2 precompiler for COBOL takes the cardinality of the array size based on the declared size of host variables that are used in the SQL. If the host variables used are of different sizes, the minimum size of all the host variables are used as the cardinality of the bulk/array.
Users can check sqlca.sqlerrd(3), which indicates the number of records fetched so far on this cursor.
The following example demonstrates the array FETCH through COBOL.Identification Division. Program-ID. "openftch". Data Division. Working-Storage Section. copy "sqlca.cbl". EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 dept-rec. 03 pname pic x(10) OCCURS 5 TIMES. 03 dept pic s9(9) comp-5 OCCURS 5 TIMES. 03 cnt pic s9(9) comp-5. 01 userid pic x(8). 01 passwd. 49 passwd-length pic s9(4) comp-5 value 0. 49 passwd-name pic x(18). EXEC SQL END DECLARE SECTION END-EXEC. 77 errloc pic x(80). Procedure Division. Main Section. display "Sample COBOL program: OPENFTCH". EXEC SQL CONNECT TO sample END-EXEC MOVE 5 TO cnt. EXEC SQL DECLARE c1 CURSOR FOR SELECT name, dept 1 FROM staff WHERE job='Mgr' END-EXEC. EXEC SQL OPEN c1 END-EXEC. 2 move "OPEN" to errloc. call "checkerr" using SQLCA errloc. *call the FETCH and UPDATE/DELETE loop. perform Fetch-Loop thru End-Fetch-Loop until SQLCODE not equal 0. EXEC SQL CLOSE c1 END-EXEC. 5 move "CLOSE" to errloc. call "checkerr" using SQLCA errloc. EXEC SQL ROLLBACK END-EXEC. move "ROLLBACK" to errloc. call "checkerr" using SQLCA errloc. display "On second thought -- changes rolled back.". EXEC SQL CONNECT RESET END-EXEC. move "CONNECT RESET" to errloc. call "checkerr" using SQLCA errloc. End-Main. go to End-Prog. Fetch-Loop Section. EXEC SQL FETCH c1 FOR :cnt ROWS INTO :pname, :dept END-EXEC. display pname(1), " in dept", dept(1), "will be fetched". display pname(2), " in dept", dept(2), "will be fetched". display pname(3), " in dept", dept(3), "will be fetched". display pname(4), " in dept", dept(4), "will be fetched". display pname(5), " in dept", dept(5), "will be fetched". display "blank line........................,,,,,,,,,,,". End-Fetch-Loop. exit. End-Prog. stop run.
- Making MRF behave like the Db2 for z/OS feature
- In Db2 11.5.8 and later, when setting the DB2_CURSOR_MRF_ZOS registry variable to FALSE, the final fetch returns 0 rows and a sqlcode of 100. When set to TRUE, it behaves like Db2 for z/OS when running a multi-row fetch: The final fetch returns a sqlcode of 100 and the number of rows fetched. The exception to this result is when there is an edge case, where the number of rows is equal to the array size.
- Array declaration by using the OCCURS clause
- COBOL supports the declaration of array's by using the OCCURS clause. Multi-row INSERT and FETCH
is supported for array when it is declared as the following
The previous declartion specifices 12 fields, all of which have the same PIC. The individual fields are referenced by using subscripts such as MONTHLY-SALES(1).01 Monthly-sales-rec. 03 Monthly-sales pic s9(9) comp-5 OCCURS 12 TIMES.
- Restrictions
-
- The Multi-row fetch and array INSERT do not support the LOB array's. The Db2 precompiler throws SQL1727N error, if the application uses the LOB array’s in INSERT/UPDATE/DELETE/FETCH statements.
- The Multi-row fetch and array insert do not support table of records (array of structure).
- The Db2 precompiler throws SQL0104N error if the application uses the NON-ATOMIC keyword in the INSERT statement because the Db2 server does not support this keyword.
- The Db2 embedded SQL supports running the array insert/update/delete operations in atomic mode only.
- The Db2 Precompiler does not consider the declaration of array of structure/record.
- The Db2 server and client does not support ROWSET cursors. Hence the Db2 precompiler strips off the WITH ROWSET POSITIONING keyword from the DECLARE CURSOR statement, and the NEXT ROWSET keyword from the FETCH statement.