Found 150 Articles for DB2

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

The “FOR UPDATE OF” clause is given in the cursor declaration and it is used when we want to update the table. All the columns which need to be updated should be given in the cursor declaration.The “FOR UPDATE OF” clause will place the exclusive lock on all the qualifying rows once the cursor is open. We can also update the table without using “FOR UPDATE CLAUSE” but in that case the exclusive lock will be placed on the row only when the UPDATE query will be executed.

The cursor can be used to fetch multiple rows from a DB2 table. However, we have to fetch this cursor in a loop so that values corresponding to a single row are assigned to host variables at a time. Based on this logic we have to process our loop till the cursor reaches the last row result.The SQLCODE field takes the value as 100 when there are no more rows left in the cursor to be fetched. Practically, we can implement this in the following way.SET WF-END-CURSOR-N TO TRUE PERFORM UNTIL WF-END-CURSOR-Y    EXEC SQL       FETCH ORDER_CUR ... Read More

The CURSOR is used when we have to fetch multiple rows from a table. There are 4 steps involved in order to use a cursor in a COBOL-DB2 program.DECLARE cursor− In this step we will define the layout of the cursor. We will give the query we want to use. For example−EXEC SQL DECLARE ORDER_CUR CURSOR FOR SELECT ORDER_ID FROM ORDERS WHERE ORDER_DATE = ‘2020-07-28’ END-EXECOPEN cursor− Next we will open our cursor. This statement readies the cursor for data retrieval. For example−EXEC SQL OPEN ORDER_CUR END-EXECFETCH cursor− In this statement, we start fetching the data from DB2 and the ... Read More

The standalone SELECT statement is generally used when we use a primary or an alternate key in the WHERE clause. Therefore, In this case we are certain that the standalone select statement will return only one row since the primary key cannot have a duplicate value (multiple rows).If we want to query the database using a non-unique key which could return multiple rows from a DB2 table, we have to use a cursor to handle the multiple rows returned. We can access the cursor in a loop to read each row data one by one.For example, if we want to ... Read More