What is update operation on the cursor having JOIN between 2 tables?

DB2DatabaseBig Data Analytics

Problem: Is it possible to update a CURSOR in which we have used JOIN on 2 tables ORDERS and TRANSACTIONS? Why or why not? How can we proceed to UPDATE any of these tables?

Solution

Whenever we use JOIN in a cursor on two or more tables (ORDERS and TRANSACTIONS in this case) a temporary table is generated in the virtual memory. However, since this is a temporary table we can fetch data from this table but it is not possible to update this table.

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. For example, if we want to create an UPDATE cursor to update the ORDERS table then we will declare the cursor as below.

Example

EXEC SQL
DECLARE ORDER_CUR CURSOR WITH HOLD FOR
   SELECT * FROM ORDERS
      FOR UPDATE OF ORDER_TOTAL, ORDER_STATUS
END-EXEC

However, if we wish to update all the columns of this table then we should not specify any column name in the “FOR UPDATE” OF clause. Similarly, if we want to update the TRANSACTIONS table then we need a separate update cursor as above.

raja
Published on 01-Dec-2020 09:34:01
Advertisements