Introduction
Throughout the history of DB2 on z/OS, DB2 development has continuously improved the product so that larger quantities of data can be maintained in a single table. Nonetheless, there is certainly value in separating data that is still subject to change or referenced very frequently from historical (archive) data that is no longer subject to change and accessed very rarely.
Separating operational from the archive data in separate tables provides increased efficiency for applications. The operational table and indexes for the application process is to a much smaller extent burdened by non-operational data. This also applies to housekeeping processes. In addition, housekeeping processes can run at a much lower frequency for non-operational data, creating another opportunity to reduce data management cost and elapsed time.
Before DB2 11, it was up to the user to organize not only the archiving process, but also ensure proper coding of SQL to include or exclude the archive data. Splitting an existing table into an operational table and an archive table thus meant reviewing and recoding of all SQL statements against the original table.
So what is Transparent Archiving?
DB2 11 Transparent Archiving is built on the infrastructure that was introduced in DB2 V10 for (bi) temporal support. Transparent archiving allows you to define a table, the archive table, and associate that with an existing table. The latter table is then referred to as the archive enabled table.
When an SQL statement deletes a row from the archive enable table, DB2 will automatically insert it into the archive table, provided the global system variable SYSIBMADM.MOVE_TO_ARCHIVE has been set to 'Y'.
When selecting from the archive enabled table, the user can control whether or not the data contained in the archive table is automatically included. If the global system variable SYSIBMADM.GET_ARCHIVE has been set to 'N', only the table specified in the SQL statement will be examined. If this variable is set to 'Y', DB2 will automatically transform the SQL statement to use a union of the archive enabled table and the archive table.
Note: The package must have been bound with ARCHIVESENSITIVE(YES) in order for DB2 to take the value of GET_ARCHIVE into consideration.
As a result, DB2 is able to shield the user from having to recode SQL statements to distinguish between queries that access the operational data only or a combination of the operational data and the archived data.
Enabling Transparent Archiving
Let's follow steps that need to be taken to split an existing table into an archive enabled table (the original) and an archive table.
The existing table looks like this:
CREATE TABLE
T412.TR400002
(
PGMID CHAR(8) NOT NULL
,VERARB CHAR(1) NOT NULL
,BUCHEN CHAR(1) NOT NULL
,KKONL CHAR(1) NOT NULL
)
The first step to take is to define an archive table that will be associated with (what will become) the archive enabled table. Important is that the columns are identical. For instance:
CREATE TABLE
T412.TR400002_ARCH LIKE T412.TR400002
The next step is to associate the archive table with the existing table. This is done by executing:
ALTER TABLE T412.TR400002
ENABLE ARCHIVE USE T412.TR400002_ARCH
At this point we have an archive enable table and an associated archive table that DB2 is aware of. This can be verified by examining SYSIBM.SYSTABLES (ARCH_SCHEMA, ARCH_TABLE)
SELECT SUBSTR(CREATOR,1,8) AS CREATOR
,SUBSTR(NAME,1,15) AS NAME
,SUBSTR(ARCHIVING_SCHEMA,1,8) AS ARCH_SCHEMA
,SUBSTR(ARCHIVING_TABLE,1,15) AS ARCH_TABLE
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'T412'
AND NAME LIKE 'TR40%'
---------+---------+---------+---------+---------+---------
CREATOR NAME ARCH_SCHEMA ARCH_TABLE
---------+---------+---------+---------+---------+---------
T412 TR400002 T412 TR400002_ARCH
T412 TR400002_ARCH T412 TR400002
Using the tables
To illustrate how archiving and selecting works, I inserted three rows in the archive enabled table. The T412.TR400002_ARCH table is empty. The contents of the archive enabled table are now as follows:
SELECT * FROM T412.TR400002;
PGMID VERARB BUCHEN KKONL
TEST04 2 2 2
TEST05 2 2 2
TEST06 2 2 2
Subsequently the following two SQL statements were executed:
SET SYSIBMADM.MOVE_TO_ARCHIVE = 'Y';
DELETE FROM T412.TR400002
WHERE PGMID = 'TEST04';
Now the tables look like this:
SELECT * FROM T412.TR400002;
PGMID VERARB BUCHEN KKONL
TEST05 2 2 2
TEST06 2 2 2
SELECT * FROM T412.TR400002_ARCH;
PGMID VERARB BUCHEN KKONL
TEST04 2 2 2
After ensuring the package (SPUFI) was bound with ARCHIVESENSITIVE(YES), and instructing DB2 to include the archive data using the global system variable, the result set is as follows.
SET SYSIBMADM.GET_ARCHIVE = 'Y';
SELECT * FROM T412.TR400002;
PGMID VERARB BUCHEN KKONL
TEST05 2 2 2
TEST06 2 2 2
TEST04 2 2 2