Saving and purging configuration and security data

The runtime database contains both current and historical configuration data. Occasionally, a Db2® administrator must move the historical data from the runtime database to a history database, for example, to:
  • Run complex queries against the historical data without affecting the performance of the production system
  • Use standard procedures to archive the configuration data that is stored in the history tables
This is done using a save and purge function that:
  • Creates a history database.
  • Copies each of the following tables into a corresponding history table:
    • DNIvSN.DNI_COS
    • DNIvSN.DNI_CT
    • DNIvSN.DNI_OU
    • DNIvSN.DNI_ROLE
    • DNIvSN.DNI_ROLEGROUP
    • DNIvSN.DNI_USER
    • DNIvSN.DNI_COS_CT_CON_REL
    • DNIvSN.DNI_CT_ATTR
    • DNIvSN.DNI_CT_ATTR_VALUE
    • DNIvSN.DNI_RO_CT_ATTR_REL
    • DNIvSN.DNI_RG_ROLE_REL
    • DNIvSN.DNI_STATUS_COMMENT
    • DNIvSN.DNI_USR_ROLE_REL
    • DNIvSN.DNI_USR_RG_REL
    • DNIvSN.DNI_DBSTATUS
    • DNIvSN.DNI_DBHISTORY
    These history tables are created in a history database.
  • Creates a new set of tables each time you run the save and purge function. A set of history tables covers the configuration data that was valid between:
    • The time FTM SWIFT was installed, or the time of the last successful execution of the save and purge function
    • The time the save and purge function was run to generate the set of history tables under construction
  • Purges historical levels of data from the source tables in the runtime database.
  • Inserts a row into the DNI_DBHISTORY table in the runtime database. The table indicates where the saved configuration data is stored and the time interval covered by the stored data.
Note: Run the save and purge function at a time when there is a reduced workload on the FTM SWIFT system. While the save and purge function is running, you cannot issue system configuration or security administration commands.

Saving and purging configuration data uses the following SQL modules:

/var/ftmswift_v300/cus/depdata/instance/admin/dnicdhid.ddl
/var/ftmswift_v300/cus/depdata/instance/admin/dnicdhit.ddl
dnicdhid.ddl
Contains SQL statements to generate a history database and create the default storage group of this database.
dnicdhit.ddl
Contains SQL statements to generate a full copy of the system configuration and security administration tables, and purges historical levels of data from the runtime tables. The tables are generated in the specified history database.

To save and purge configuration data:

  1. Log on to z/OS® UNIX as Db2 administrator, for example, UDB2ADM1.
    The access rights required by this user are described in Planning users.
  2. Create a copy of the SQL modules in your home directory, for example: 
    cp /var/ftmswift_v300/cus/depdata/instance/admin/dnicdhid.ddl /u/udb2adm1
cp /var/ftmswift_v300/cus/depdata/instance/admin/dnicdhit.ddl /u/udb2adm1
  3. In the copies, replace the following parameters
    DNIxUSER
    User name used to connect.
    DNIxPWD
    Password of the user used to connect.
    DNIxDBHI
    Name of the history database. This is the target database where the historical configuration data is moved. More than one set of history tables can be moved into the same history database. You do not need to specify a new history database each time you run the save and purge function.
    DNIxTSHI
    Name of the segmented table space for the configuration data in the history database.
    DNIxSUFF
    Suffix of up to 8 characters that is appended to the names of the system configuration and security administration tables.
    DNIxSUF
    Short suffix of exactly 3 characters.
    DNIxSGHI
    Name of the storage group created for data in the history database.
    DNIxVOLH
    Volume specified in the CREATE STOGROUP statement for DNIxSGHI.
    DNIxCATH
    Catalog specified in the CREATE STOGROUP statement for DNIxSGHI.
  4. If you activated the data integrity framework modify the copy of dnicdhit.ddl:
    1. Locate the text 'DNI_DI_OPEN' and uncomment this line.
    2. Locate the text 'DNI_DI_CLOSE' and uncomment this line.
    3. Save your changes.
  5. Specify the buffer pools for table spaces and indexes in the history database in the CREATE DATABASE statement in dnicdhid.ddl. By default BP1 is the buffer pool for table spaces and BP2 the buffer pool for indexes. The buffer pool names that you specify must identify activated buffer pools with a page size of 4K. Do not specify buffer pools that are shared by critical applications.
  6. Adjust the space allocation parameters (PRIQTY and SECQTY) for the CREATE TABLESPACE statement in dnicdhit.ddl. Accept the current values or check the size of the following table spaces in the runtime database.
    • DNICOS
    • DNIOU
    • DNICT
    • DNIROLE
    • DNIRG
    • DNIUSER
    • DNISCOM
    • DNICTA
    • DNICTAV
    • DNICOSCC
    • DNICOSRE
    • DNIROCTA
    • DNIRGR
    • DNIUSRRO
    • DNIURG
    Tip: To check the size of the table spaces:
    1. Execute RUNSTATS for the table spaces.
    2. Check the SPACEF column of the table SYSIBM.SYSTABLES in the Db2 catalog.
    3. Total the values that you find for the table spaces in that column.
    4. Use the total from step 7.c in integer format for PRIQTY.
    5. Use 10% of the value of PRIQTY for SECQTY.
  7. To create the storage group and the database where the historical data is to be saved, run the SQL statements in dnicdhid.ddl using the Db2 command line processor: 
    java com.ibm.db2.clp.db2 +c -z dnicdhid.log -stvf /u/udb2adm1/dnicdhid.ddl
  8. Execute the SQL statements in dnicdhit.ddl. This creates the history tables, copies the historical data from the runtime tables to the history tables, and purges that data from the runtime tables.

    To ensure that no data is lost, the SQL statements in dnicdhit.ddl must span a single unit of work.

    Use the Db2 command line processor:
    java com.ibm.db2.clp.db2 +c -z dnicdhit.log -stvf /u/udb2adm1/dnicdhit.ddl
Table 1 lists the names of the resulting source and target tables.
Table 1. Names of the source and target tables
Source table (in runtime database) Target table (in history database)
DNI_COS DNI_CS__<long suffix>
DNI_COS_CT_CON_REL DNI_CSCT__<long suffix>
DNI_CT DNI_CT__<long suffix>
DNI_CT_ATTR DNI_CTA__<long suffix>
DNI_CT_ATTR_VALUE DNI_CTAV__<long suffix>
DNI_DBSTATUS DNI_DBST__<long suffix>
DNI_DBHISTORY DNI_DBHI__<long suffix>
DNI_OU DNI_OU__<long suffix>
DNI_RG_ROLE_REL DNI_RGR__<long suffix>
DNI_RO_CT_ATTR_REL DNI_ROCT__<long suffix>
DNI_ROLE DNI_RO__<long suffix>
DNI_ROLEGROUP DNI_RG__<long suffix>
DNI_STATUS_COMMENT DNI_SCOM__<long suffix>
DNI_USER DNI_US__<long suffix>
DNI_USR_RG_REL DNI_URG__<long suffix>
DNI_USR_ROLE_REL DNI_USRO__<long suffix>
The variable <long suffix> represents the long suffix that you specified for the history tables. The table names are all qualified with the name of the schema name of the runtime database, which is set by the DNIvSN placeholder.