SELECT FROM data change table on DB2 for z/OS
I am a big fan of minimizing the calls to DB2 and run as few SQL as possible. That is one of the reason I like the MERGE statement although it is a bit limited in DB2 for z/OS. In this short blog I would like to mention another of my favorite SQL tricks - SELECT FROM data-change-table-reference.
Imagine you want to query the data you are inserting, deleting, updating, or even merging. Would you do that using two operations? I.e. INSERT/DELETE/UPDATE/MERGE the data and then executing a SELECT? Forget about it. DB2 allows you to perform these two actions in one call!
The feature that allows this is called data change table reference and it can be used to query the intermediate result table, which is the set of rows that are affected by the data change statement. Might seem complicated, but examples will hopefully make this clear.
Let’s start with a DELETE. Assume you have an employee table (I am using the IBM EMP sample table) and your boss decided to fire the employees from one specific department (let’s say D11), but keeping the department. Not a very happy day; what you can do is (while having autocommit off):
DELETE FROM EMP WHERE DEPT=’D11’;
But, what if you wanted to see the names of potentially fired employees before pressing the COMMIT key? Update the previous statement to:
SELECT LASTNAME FROM OLD TABLE (DELETE FROM EMP WHERE WORKDEPT=’D11’);
The OLD TABLE clause allows you referencing the affected rows before the deletion. Now, before committing the change, your boss is looking over your shoulder and he recognizes all the friends from the department. “Press ROLLBACK!”, he says and the lives have been saved. What a nice feature, so please remember that SELECT FROM OLD TABLE can save lives as it references the rows that were or would be deleted.
A new plan. Now you want to increase the salary for each employee in the department D11 (as a compensation for the stress they had) and you want to see the new salaries for each of them. What you can do is this:
SELECT LASTNAME, SALARY FROM FINAL TABLE (UPDATE EMP SET SALARY = SALARY * 1.1 WHERE WORKDEPT = 'D11');
Nice. The FINAL TABLE clause allows you selecting the rows that were updated as they appear after the update. What if you changed the FROM NEW TABLE to OLD TABLE? The output would then show the salaries before the increase, i.e. before the update operation. All good, but what if you wanted to see both - old and new salaries? Well, what about this:
SELECT LASTNAME, SALARY, OLD_SALARY FROM FINAL TABLE (UPDATE EMP INCLUDE (OLD_SALARY DECIMAL(9,2)) SET SALARY = SALARY * 1.1, OLD_SALARY = SALARY WHERE WORKDEPT = 'D11') ORDER BY LASTNAME;
The output can be like this:
LASTNAME SALARY OLD_SALARY
--------- -------- ----------
ADAMSON 40713.68 37012.44
BROWN 44675.54 40614.13
JOHN 48057.61 43688.74
JONES 29424.01 26749.10
LUTZ 48057.61 43688.74
PIANKA 35833.84 32576.22
SCOUTTEN 34368.27 31243.89
STERN 51938.94 47217.22
WALKER 32934.92 29940.84
YAMAMOTO 39747.37 36133.98
YOSHIMURA 39747.37 36133.98
Did you notice the INCLUDE clause? It is a very powerful option as it allows introducing new columns, which you can reference in the outer SELECT. You can use it in the INSERT/DELETE/UPDATE/MERGE statements as can be seen in the SQL reference, search for “include column”. In our simple example, I used it pass the old salary to the outer SELECT.
Your company is growing and hiring new employees, so you are inserting new employees. You wouldn’t be surprised you can query the new rows while inserting them. But why would you do that when you know the new rows already? Maybe, you have some automatically generated values (like sequences or identity columns), or you are using some default values, or special registers. You don’t need to query these values separately, but do it in one call using the SELECT FROM FINAL TABLE. In my example, I am using the IBM EMP sample table and I will use the CURRENT DATE register in the INSERT:
SELECT LASTNAME, HIREDATE FROM FINAL TABLE(INSERT INTO EMP(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES('300000', 'John', '', 'Smith', 'D11', 2518, CURRENT DATE, 'DESIGNER', 20, 'M', '1971-11-12', 30000, 0, 0));
the output is
You can do even more! If you just acquired another company, you can insert and select all the new employees as easy as:
SELECT LASTNAME FROM FINAL TABLE (INSERT INTO YOUR.EMP SELECT * FROM NEW.EMP) ORDER BY LASTNAME;
I would point out the ordering here. In the previous query, I’ve used the ORDER BY in the SELECT statement. However, what if I wanted to keep the ordering of the inserted rows? ORDER OF clause of the ORDER BY can help here. For example:
SELECT LASTNAME FROM FINAL TABLE (INSERT INTO YOUR.EMP SELECT * FROM NEW.EMP ORDER BY LASTNAME DESC) AS NEWCOMERS ORDER BY ORDER OF NEWCOMERS;
In this case we are inserting the data ordered descending by last name and the outer SELECT keeps the ordering as defined by ORDER OF clause, this might be useful in some case.
Maybe there are some employees in the acquired company that already work for you. If so, you would just assign them to another department. Otherwise, if he/she does not work for you yet, you would add him/her to your employee table. Does it look familiar? Yes, I would use merge (update or insert a new employee) and select from the final table:
SELECT LASTNAME, WORKDEPT FROM FINAL TABLE (
MERGE INTO EMP01 AS TARGET
USING (values('300000', 'John', '', 'Smith', 'D11', 2518, CURRENT DATE, 'DESIGNER', 20, 'M', '1971-11-12', 30000, 0, 0)) AS SOURCE(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM)
ON TARGET.LASTNAME = SOURCE.LASTNAME AND TARGET.BIRTHDATE = SOURCE.BIRTHDATE
WHEN MATCHED THEN UPDATE SET WORKDEPT = 'A00'
WHEN NOT MATCHED THEN INSERT (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM)
VALUES (SOURCE.EMPNO, SOURCE.FIRSTNME, SOURCE.MIDINIT, SOURCE.LASTNAME, SOURCE.WORKDEPT, SOURCE.PHONENO, SOURCE.HIREDATE, SOURCE.JOB, SOURCE.EDLEVEL, SOURCE.SEX, SOURCE.BIRTHDATE, SOURCE.SALARY, SOURCE.BONUS, SOURCE.COMM));
The output reveals the updated row:
I admit, these were silly examples, but I wanted to show what you can do with the SELECT FROM OLD/FINAL table. The key point is that the data change table references the rows that were affected by the change operation or affected rows that existed before the operation. The data change reference does not reference the entire table. You can use OLD TABLE reference for searched UPDATE and DELETE statements, and FINAL TABLE for INSERT, UPDATE, and MERGE statements.
When this can be really useful? Well, in any case you are inserting or modifying the data and you want to know the data that were inserted/modified. For example this might be helpful for obtaining the data from automatically generated columns, like default values, timestamps, sequences, or identity columns.
If I still haven’t convinced you about the advantages of this feature, please have a look to the reference section where I provide the links to IBM documentation. I hope you will find the data change table reference useful.
- Selecting values while inserting data: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_selectvalueinsert.dita?lang=en
- Preserving the order of a derived table: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_preserveorderderivedtable.dita?lang=en
- Selecting values while updating data: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_selectvaluesupdate.dita?lang=en
- Selecting data while deleting data: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_selectvaluesdelete.dita?lang=en
- Selecting values while merging data: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_selectvaluesmerge.dita?lang=en