How to backup DDL / View a table definition from a specific point in time using the Database Management Solutions for DB2 for z/OS products
Article ID: 26279
Updated On:
Products
Issue/Introduction
In case of accidental UPDATE (or DROP) of a table, recreation / restore of the object requires the original DDL which may not always be available.
Db2 users may also have a requirement to view a table definition from a specific point in time in the past.
This can be due to regulatory demands or other business issues like using DSN1COPY or unload using an old image copy in order to view the content of a specific row.
Resolution
The solution is to use ACM (Alternate Catalog Mapping) to make a shadow copy of the Db2 catalog.
While ACM is mostly used for performance reasons it can also serve as a backup of DDL.
If you regularly update an ACM of the catalog, you can use the template function in RC/Update for Db2 for z/OS (RCU) to generate DDL from the shadow copy.
ACM is a part of the Value Pack delivered free with any Database Management Solutions for Db2 for z/OS product.
There are different ways to use ACM with different products. This document describes two different methods:
- Use RC/Query for Db2 for z/OS (RCQ) Table-Column list to view a previous table definition.
- Use RC/Update for Db2 for z/OS (RCU) or RC/Migrator for Db2 for z/OS (RCM) to template previous DDL.
The following scenario describes how RCQ can be used to accomplish these requirements using ACM.
The following steps should be executed once a day after all Db2 schema change management tasks are completed.
Using ACM:
- Chose option M from the Db2 Products Main Menu.
- Specify a new ACMID (e.g. J2008101 for Julian date 2008 day 101) and a description (e.g. April 10 2008).
- In the field TO: All --> specify SYSIBM . = and hit ENTER. (see the following example):
FROM: All: Define ID. tablename TO: All --> SYSIBM . = - For SYSTABLES and SYSCOLUMNS overtype SYSIBM creator with J2008101 (or whatever the ACMID was specified as).
Outside of ACM: - Create two tables : J2008101.SYSTABLES and J2008101.SYSCOLUMNS which are templates of SYSIBM.SYSTABLES and SYSIBM.SYSCOLUMNS.
Hint: Issue the CREATE command to jump to RCU and use the template option. - Execute two INSERT statements:
- INSERT INTO J2008101.SYSTABLES SELECT * FROM SYSIBM.SYSTABLES;
- INSERT INTO J2008101.SYSCOLUMNS SELECT * FROM SYSIBM.SYSCOLUMNS;
Hint: Issue the ISQL command to get a screen where SQL can be entered.
- INSERT INTO J2008101.SYSTABLES SELECT * FROM SYSIBM.SYSTABLES;
Of course the ACMID used as the ACM entry and the tables created must reflect the current date since they need to be unique.
It is important to understand it is not necessary to have shadow tables for every catalog table. RCQ will automatically switch to the appropriate tables when creating reports.
Once there is a need to view the definition of a specific table at a given day in the past, all that is necessary is to enter the appropriate ACMID on the Main Menu and specify ON for ACM. Requesting the T-C report (Table-Column) from RCQ, the shadow tables created earlier will be used to create this report.
The process of creating these ACMID shadow versions on a daily basis can easily be automated. The ACMID shadow versions reside in a DB2 table, so the insertion of a new ACMID together with the CREATE of the two tables and the population of the shadow versions of SYSTABLES and SYSCOLUMNS can be implemented in a REXX or another program, which then is executed on a daily basis.
You can also use the template function in RCU to directly generate DDL based on ACM tables. Note that if you use all catalogs table in you ACMID you are not restricted to tables only. Indexes, tablespaces etc. can be template based on ACM tables. In RCM you can define an ALTER strategy and from here template DB2 objects the same way.
If you want to utilize all catalog tables in your ACM you would probably want to use the MAKETAB command to create ACM tables and indexes.
Feedback
