Thursday, April 19, 2007

SELECT from DELETE, UPDATE, and MERGE [DB2 9 for z/OS]

Another nice new SQL feature provides the ability to SELECT from DELETE, UPDATE, and MERGE statements.

This capability is similar to the SELECT from INSERT feature that was introduced with DB2 V8. So, before looking at the new V9 feature, let’s review the V8 feature.

The ability to SELECT from an INSERT statement is an intriguing feature. To understand why this is so, we need to review some background details. In some cases, it is possible to perform actions on an inserted row before it gets saved to disk. For example, a BEFORE TRIGGER might change data before it is even recorded to disk. But the application program will not have any knowledge of this change that is made in the trigger. Identity columns and columns with defaults (especially user-defined defaults) have similar effects. What if the program needs to know the final column values? Prior to V8 this was difficult and inefficient to implement.

Well, the SELECT FROM INSERT feature introduced in DB2 V8 solves this problem. It allows you to both insert the row and retrieve the values of the columns with a single SQL statement. It performs very well because it performs both the INSERT and the SELECT as a single operation. Consider the following example:


SELECT COL5 INTO :C5-HV
FROM FINAL TABLE
(INSERT (COL1, COL2, COL5, COL7) INTO SAMPLE_TABLE
VALUES('JONES', 'CHARLES', CURRENT DATE, 'HOURLY')
);

The data is inserted as specified in the VALUES clause, and retrieved as specified in the SELECT. Without the ability to select COL5, the program would have no knowledge of the value supplied to COL5, because it was assigned using CURRENT DATE. With this new syntax the program can retrieve the CURRENT DATE value that was just inserted into COL5 without incurring additional overhead.

OK, on to V9. In this new version you can retrieve columns from rows that are modified via DELETE, UPDATE, and MERGE statements, thereby replacing multiple SQL calls with one. DB2 V9 allows a searched UPDATE or a searched DELETE statement in the FROM clause of a SELECT statement that is a subselect, or in the SELECT INTO statement. This capability allows a user or program to know which values were updated or deleted.

And, if you recall from a few blog entries ago, DB2 9 for z/OS also adds the MERGE statement. Well, it also adds the ability to SELECT from the MERGE. This allows you to return all the rows that were either inserted or updated as a result of the MERGE.

Here is an example of a SELECT from an UPDATE:


SELECT SUM(salary) INTO :SAL-HV
FROM FINAL_TABLE
(UPDATE EMP
SET SALARY = SALARY * 1.02
WHERE WORKDEPT = ‘A01’);


Prior to the capability you would have had to run the UPDATE statement, and then only after it finishes, you would run the SELECT to add up the new salary values. Now, instead of multiple statements requiring multiple passes through the data, you can consolidate it into one.

Nice, huh?

9 comments:

Татьяна said...

Hello Craig,
we have 2 select from:

select * from old table
(update ...) - this select old value before update and execute update

select * from final table
(update ...) - this select new value after update and of course execute update

Could these 2 results be union into one, like

Old-value + New_value in one single SQL statement.

Many thanks!!!

Anonymous said...

Hi Craig, I'm Kim. My question below is related to REORG. Hope you can help shed some lights :)

I started using DB2 REORG SHRLEVEL(CHANGE) since version 5. At my customer site, they installed a new REORG tool. We are in DB2v9 now. Last year, my customer 'accidentally' REORG a partition TS using both IBM REORG and the third party REORG tool on the same partition TS (this will caused conflict among the 2 tools). At the end, DB2 REORG Utility abended, and SYSUTILX corrupted, as we couldn't issue TERM UTIL(*) command. We recovered SYSIBM.SYSUTILX (redefined). New utilities are running just great. However, the mapping table and indexes that were hanging, still registered in DB2. Now, when I want to drop and recreate the mapping table or index, DB2 returned error 'onject is used in another utility'. Dropping of mapping objects are not possible at all. No doubt I can bypass not using the 'corrupted mapping table and index', but prefer a proper cleanup not leaving invalid objects in the system. Do you have any tips how I can clean up these?
Many THANKS ...I can be contacted at kimchin@gmail.com

Anonymous said...

Hi Craig,

I am trying to resolve a timeout issue by thinking to run one statement which will do bot the select and update. But I can't get the following SQL to run:

SELECT VALUE+1 AS NEWVAL
FROM VALUE
(UPDATE VALUE
SET VALUE=NEWVAL
WHERE VALUE_ID='1');

Any suggestions? thanks!

Craig S Mullins said...

You seem to be trying to SELECT the next value and then update it, but SELECT FROM UPDATE works by updating the value and then selecting from the table.

Anonymous said...

I need to update one field 28000 record in table b by checking selecting the table a field

Linda said...
This comment has been removed by a blog administrator.
Anonymous said...

I'm trying to retrieve non-updated column values from FINAL TABLE after I update some other column. I'm using REXX to do this. Would you know how to retrieve the row from the final table. The update is of only one row so it is being done via an EXECUTE (dynamic) statement. I'm just not sure where/if to code the INTO clause at. Thanks Mike

Anonymous said...

hi craig - any thoughts on the -104?

SELECT SEQUENCE_NO FROM TVIX.AUTO_SEQUENCE
WHERE SEQUENCE_CATEGORY = 01
(UPDATE TVIX.AUTO_SEQUENCE
SET SEQUENCE_NO = SEQUENCE_NO + 1
WHERE SEQUENCE_CATEGORY = 01)

receive SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(".

Anonymous said...

this works.

SELECT SEQUENCE_NO FROM FINAL TABLE
(UPDATE TVIX.AUTO_SEQUENCE
SET SEQUENCE_NO = SEQUENCE_NO + 1
WHERE SEQUENCE_CATEGORY = 01)