Data Versioning in DB2 10
In last week's DB2utor blog entry, I listed the top 10 reasons to upgrade to DB2 10. Now comes the fun part of getting into the details of the upcoming release.
Certainly one good reason to upgrade to DB2 10 is its support of versioned, or temporal, data. So what is versioned data? This is IBM's response to the common challenges that customers face in maintaining different versions of application data, specifically:
IBM’s solution is this: When rows are updated or deleted from the base table, DB2 10 inserts the updated or deleted row(s)--along with a system timestamp--into a history table. The history table is implicitly created with the same table creator as the base table, but placed into a separate partition by growth table space. The history table contains all the columns of the base table, along with columns that support auditing and managing of the rows. When the table is used for auditing purposes, the table creator is SYSAUDIT. The SECADM and ACCESSCTRL commands can grant privileges on a history table.
New DDL syntax is also provided to create or alter a table to support versioning. Once versioning is activated, you'll need to use the new SQL FROM clause extension to specify what data should be processed. The most common form of the extension is SYSTEM VERSION AS OF, but you'll have other options--including BEFORE, AFTER, BETWEEN--to qualify the version of data you want to process.
Here's a quick look at the extended FROM clause. This example displays the average salary for employees working in department SW1 as of June 13, 2009:
SELECT AVG(SALARY)
FROM EMP SYSTEM VERSIONS AS OF '2009-06-13-00:00:00:000000000000'
WHERE deptno = 'SW1'
Certainly one good reason to upgrade to DB2 10 is its support of versioned, or temporal, data. So what is versioned data? This is IBM's response to the common challenges that customers face in maintaining different versions of application data, specifically:
- Maintaining different versions of application data can be a cumbersome and difficult task.
- Even though keeping a history of data changes is a challenge, it's also absolutely necessary, due to the implementation of compliance-related laws and regulations in recent years.
IBM’s solution is this: When rows are updated or deleted from the base table, DB2 10 inserts the updated or deleted row(s)--along with a system timestamp--into a history table. The history table is implicitly created with the same table creator as the base table, but placed into a separate partition by growth table space. The history table contains all the columns of the base table, along with columns that support auditing and managing of the rows. When the table is used for auditing purposes, the table creator is SYSAUDIT. The SECADM and ACCESSCTRL commands can grant privileges on a history table.
New DDL syntax is also provided to create or alter a table to support versioning. Once versioning is activated, you'll need to use the new SQL FROM clause extension to specify what data should be processed. The most common form of the extension is SYSTEM VERSION AS OF, but you'll have other options--including BEFORE, AFTER, BETWEEN--to qualify the version of data you want to process.
Here's a quick look at the extended FROM clause. This example displays the average salary for employees working in department SW1 as of June 13, 2009:
SELECT AVG(SALARY)
FROM EMP SYSTEM VERSIONS AS OF '2009-06-13-00:00:00:000000000000'
WHERE deptno = 'SW1'
Connect With Us: