How to backup DDL / view a table definition from a specific point in time using the CA Database Management Solutions for DB2 for z/OS products.

Document ID : KB000026279
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary:

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.

 

Instructions:

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 CA RC/Update for DB2 for z/OS (RCU) to generated DDL from the shadow copy.
ACM is a part of the Value Pack delivered free with any CA 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:

  1. Use CA RC/Query for DB2 for z/OS (RCQ) Table-Column list to view a previous table definition.

  2. Use CA RC/Update for DB2 for z/OS (RCU) or CA 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:

  1. Chose option M from the DB2 Products Main Menu.

  2. Specify a new ACMID (e.g. J2008101 for Julian date 2008 day 101) and a description (e.g. April 10 2008).

  3. In the field TO: All --> specify SYSIBM . = and hit ENTER. (see the following example):
    FROM: All: Define ID. tablename TO: All --> SYSIBM . =

  4. For SYSTABLES and SYSCOLUMNS overtype SYSIBM creator with J2008101 (or whatever the ACMID was specified as).

    Outside of ACM:

  5. 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.

  6. Execute two INSERT statements:

    1. INSERT INTO J2008101.SYSTABLES SELECT * FROM SYSIBM.SYSTABLES;

    2. INSERT INTO J2008101.SYSCOLUMNS SELECT * FROM SYSIBM.SYSCOLUMNS;

      Hint: Issue the ISQL command to get a screen where SQL can be entered.

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.
Please refer to the 'Value Pack Reference Guide' (r18 and previous) or the online documentation in 'CA DB2 DB Management Common Functions' more information about ACM.