Wednesday, April 22, 2015

A DB2 11 for z/OS Temporal Data Enhancement You Might Have Missed

When DB2 10 for z/OS introduced temporal data functionality about five years ago, one of the first use cases that jumped to mind for many people was data-change auditing: a table could be created with (or an existing table altered to add) the "system time" characteristic, and thereafter one would have, in the history table associated with the base table, a record of row changes resulting from UPDATE and DELETE statements targeting the base table.

That's nice, but suppose you want to see more than WHAT a row looked like before it was changed. Suppose you also want to see WHO changed a row, and by what means (i.e., INSERT, UPDATE, or DELETE). I was recently contacted by a programmer, working on a new application in a DB2 10 for z/OS environment, who wanted to do just that. He and his team had created a table with the system time property, and in this table they had two columns to capture the identity of a data-changer and the nature of the change: one column to record the ID of the user who added a row to the table, and another column to record the ID of any user who subsequently updated the row. The rub, as this programmer saw it, concerned delete activity. How could he capture the ID of the user who deleted a row in the table? The delete operation would cause DB2 (by way of its system time temporal capability) to move a copy of the deleted row to the base table's history table, but that "pre-delete" image of the row would contain no information about the ID of the user associated with the delete operation. The programmer thought about updating a row before deleting it, just to capture (via the UPDATE) the ID of the user that would subsequently drive the row-delete action. That didn't seem like a desirable solution to the developer, but what else could he do? On top of this problem, there was the matter of not being able to easily determine whether a DELETE or an UPDATE caused a "before" image of a row to be placed in the history table. Not a good situation.

I'll tell you, I like to give people good news, and I had good news for this guy. The good news, I told him, was that his organization was about to migrate their DB2 for z/OS subsystems to DB2 11, and new functionality in that release would address his "who did what?" requirements while also allowing his team to simplify their application code.

I'm actually talking here about capabilities added to DB2 11 after its general availability, by way of several APARs and their respective PTFs. Key among these APARs is PM99683 (the text of this APAR references the related APARs that, together with PM99683, provide the new functionality I'm about to describe). The first goody here is a new type of generated column specification, GENERATED ALWAYS AS (CURRENT SQLID). That enables code simplification: there's no need to programmatically place the ID of a data-changer in a column of a row -- DB2 11 will do it for you (and note that CURRENT SQLID is one of several special registers that can now be used with GENERATED ALWAYS -- you can read more about this in the section of the DB2 11 SQL Reference that covers CREATE TABLE).

There's more: you can also have in a table a column that is GENERATED ALWAYS AS (DATA CHANGE OPERATION). What's that? It's just a 1-character indication of the nature of a data change operation: I for INSERT, U for UPDATE, D for DELETE. Isn't that cool?

I'm still not done. In addition to the new GENERATED ALWAYS AS (CURRENT SQLID) and GENERATED ALWAYS AS (DATA CHANGE OPERATION) options of CREATE TABLE (and ALTER TABLE), there is a very handy clause that can now be added to the ALTER TABLE statement used to "turn on" versioning (i.e., system time) for a table: ON DELETE ADD EXTRA ROW. When system time activation for a table includes this clause, DB2 will add an extra row to the base table's history table when a row is deleted. That is to say, you'll get (as usual) the "pre-delete" image of the row (with the "row end" timestamp showing when the row was made non-current by the DELETE), and you'll ALSO get ANOTHER version of the row added to the history table -- this one with a 'D' in your GENERATED ALWAYS AS (DATA CHANGE OPERATION) column, and the ID of the deleting user in your GENERATED ALWAYS AS (CURRENT SQLID) column.

A little more information about this "extra row" that's added to the history table for a base table DELETE when ON DELETE ADD EXTRA ROW is in effect: first, the "row begin" and "row end" timestamps in the extra row are the same, and are equal to the "row end" value in the "as usual" row placed in the history table as a result of the DELETE (by "as usual" I mean the "before-change" row image that's always been placed in a history table when a base table row is deleted). Second, "extra rows" in the history table resulting from base table DELETEs with ON DELETE ADD EXTRA ROW in effect are NOT part of a base table query result set when that query has a FOR SYSTEM_TIME period specification, no matter what that specification is. If you want to see the extra rows added to a history table by way of ON DELETE ADD EXTRA ROW functionality, you'll need to query the history table explicitly.

The text of APAR PM99683, which you can access via the hyperlink I included a few paragraphs up from here, provides a set of SQL DDL and DML statements that very effectively illustrate the use and effects of the enhancements about which I've written in this blog entry. I encourage you to try these statements (or variations of them) on a DB2 11 test or development system at your site, to see for yourself what the new capabilities can do for you.

Temporal data support was a gem when it was introduced with DB2 10. That gem just got shinier.

6 comments:

  1. I am not convinced that GENERATED ALWAYS AS (CURRENT SQLID) is useful. Many of our operations use Websphere Data Sources which connect to DB2 z/OS with a generic application SQLID (we use the phrase "proxy ID"). A possible better approach could be a user modifiable special register that defaults to CURRENT SQLID which would allow the application code to set the register value to something appropriate such as a value from one of the workstation registers.

    Michael Harper/TD Bank

    ReplyDelete
  2. Sorry about the long-delayed response, Michael.

    Even when a network-attached application connects to DB2 for z/OS with an auth ID that is tied to the application, versus being tied to a user, you should be able to get an individual RACF ID to be associated with an individual user of the application (and have that individual RACF ID be that user's current SQL ID) by way of enterprise identity mapping, which effectively became available with DB2 10 for z/OS (it was do-able with DB2 9, but became much easier to implement with DB2 10). I blogged about enterprise identity mapping in a DB2 context a couple of years ago. That blog entry is at http://robertsdb2blog.blogspot.ca/2013/06/db2-10-for-zos-and-enterprise-identity.html.

    Robert

    ReplyDelete
  3. This seems to be specific to RACF and we are not a RACF shop.

    Michael Harper/TD Bank

    ReplyDelete
  4. P.S. no worries, you're on a road trip, you were in my home town Toronto on 1 June I believe. Sorry I missed you.

    Michael Harper/TD Bank

    ReplyDelete
  5. Where in the Db2 catalog is the information stored for "ON DELETE ADD EXTRA ROW"

    Hans-Henrik Gudmann/Tommerup Denmark

    ReplyDelete
    Replies
    1. In the SYSIBM.SYSTABLES catalog table, the value in the CLUSTERRID column is 1 for a table if "The table is a system-period temporal table with versioning, and the table is defined with the ON DELETE ADD EXTRA ROW clause;' otherwise the value for a table in this column is 0 (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=tables-systables).

      Robert

      Delete