Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
A SCROLLABLE CURSOR can move in both forward and backward direction. In other words, it can fetch next as well as previous rows. A SCROLLABLE CURSOR is declared using the “SCROLL” clause in the DECLARE CURSOR.For example, if we want to declare a SCROLLABLE CURSOR on the ORDERS table then we have to declare the cursor like below.EXEC SQL DECLARE ORDER_CURR SCROLL CURSOR FOR SELECT ORDER_ID, ORDER_DATE FROM ORDERS WHERE ORDER_DATE = ‘2020-07-29’ END-SQL
A cursor can move only in forward direction, which means that it can extract the next row after every fetch. It is not possible to extract the previous row using a cursor.For example, if our resultant cursor contains following rows−ORDER_IDORDER_DATEA223672020-07-28A667562020-07-28A778902020-07-29A968322020-07-29If our cursor is currently pointing to 3rd row i.e. order id A77890 then the next fetch will point the cursor to the next row i.e. order id A96832. It is not possible to point the cursor to the previous order id i.e. A66756.In order to achieve this, we use the concept of SCROLLABLE CURSOR. The SCROLLABLE CURSOR can move both ... Read More
The ATOMIC and NON ATOMIC clauses are used with the multi-row insert. ATOMIC is always processed by default if any of the options is not given. The ATOMIC clause states that if there is a failure while inserting any one row during multi-row insertion then the entire query will be failed and all the inserts will be rolled back.The NON ATOMIC clause is just the opposite of the ATOMIC clause. It is used when we have to insert and process all the rows individually in a multi-row insert. For example, this option can be used like below.MOVE 50 TO MAX-ROWS ... Read More
If we want to insert a multiple rows in a DB2 table using a single INSERT command then we have to define the host variable array and move the row data we want to insert in that array. We need to define another variable in the working storage section with configuration S9(4) COMP which will hold the number of rows to be inserted. We can insert the multiple rows as below−MOVE 50 TO MAX-ROWS EXEC SQL INSERT INTO ORDERS (ORDER_ID, ORDER_DATE) VALUES(:ORDER-ID, :ORDER-DATE) FOR :MAX-ROWS ROWS END-EXECThe ORDER-ID and ORDER-DATE are host variables which should be ... Read More
The host variable needs to be declared as an array for the multi-row fetch. Also, we need to define another variable in the working storage section with the configuration S9(4) COMP, which will store the value of the number of rows to be fetched in a single fetch call.We can give any name to this variable, here we have used MAX-ROW. Below is an example of declaring a host variable array and MAX-ROW.01 ORDER-ID PIC X(25) OCCURS 25 TIMES. 01 MAX-ROW PIC S9(4) COMP VALUE 25.We will fetch the cursor like below.EXEC SQL FETCH NEXT ROWSET FROM ORDER_CUR FOR ... Read More
We can extract multiple rows from a DB2 table in a single FETCH call using a concept of multi row fetch.In a multi row fetch, we have to declare the cursor with the clause “WITH ROWSET POSITIONING”. The host variable in this case should be declared as an array.Therefore, in a single fetch statement the host variable array will be populated with the multiple row data. We can traverse in the host variable array in order to access this row data.For example, we can declare a multi fetch cursor like below−EXEC SQL DECLARE ORDER_CUR WITH ROWSET POSITIONING FOR ... Read More
When we try to fetch the data from the CURSOR which is not open the query will fail. In this case the SQLCODE field of SQLCA will be populated with the DB2 error code - 501. As per the IBM documentation -501 error code states that−“THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN”When we try to OPEN the cursor which is already open then the query will fail and we will get the error code as -502 in the SQLCODE field of SQLCA. As per the IBM documentation -502 error code states that−“THE CURSOR IDENTIFIED IN ... Read More
When we use JOIN on one (self join) or more table inside a cursor declaration then a read-only cursor will be created. We cannot update the read-only cursor.If we want to update any of the tables used in the JOIN then we have to declare a separate cursor for all the tables and an individual logic has to be built in order to update each DB2 table.
Whenever we issue a COMMIT statement, all the open cursors will get closed. This is a very common case when we have to frequently use the commit statement after a UPDATE while working with a cursor. In this case we can use the “WITH HOLD” clause during the cursor declaration.The “WITH HOLD” clause will keep the cursor open even after firing the COMMIT statement. We can give the “WITH HOLD” clause in the following way.EXEC SQL DECLARE ORDER_CUR CURSOR WITH HOLD FOR SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS WHERE ORDER_DATE = ‘2020-07-28’ END-EXEC
The “WHERE CURRENT OF” clause will place the exclusive lock on the row once the UPDATE statement is executed. The “WHERE CURRENT OF” clause will point to the most recently fetched row of the cursor.We can update the rows in cursor using “WHERE CURRENT OF” in the following way.CURSOR definition.EXEC SQL DECLARE ORDER_CUR CURSOR FOR SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS WHERE ORDER_DATE = ‘2020-07-28’ END-EXECOPEN cursorEXEC SQL OPEN ORDER_CUR END-EXECFETCH cursor and Update rowSET WF-END-CURSOR-N TO TRUE PERFORM UNTIL WF-END-CURSOR-Y EXEC SQL FETCH ORDER_CUR INTO :ORDER-ID, :TRANSACTION-ID END-EXEC IF TRANSACTION-ID NOT = SPACES EXEC SQL ... Read More