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 module:

/var/ftmswift_v300/cus/depdata/instance/admin/dnicdhit.ddl

To save and purge configuration data:

Log on to the runtime system as a Db2 administrator, for example, udb2adm1. The access rights required by this user are described in Planning users.
Copy the customized FTM SWIFT SQL module dnicdhit.ddl from your deployment data directory to another directory. For example, to copy the SQL module to your home directory, enter:
```
cp /var/ftmswift_v300/cus/depdata/instance/admin/dnicdhit.ddl /home/udb2adm1
```
In the copied version of dnicdhit.ddl, replace the following placeholders:

DNIxSUFF

This is the long table name suffix. This suffix can be at most eight characters long and is appended to the names of the system configuration and security administration history tables.

DNIxSUF

This is the short table name suffix. This suffix must be exactly three characters long.
If you activated the data integrity framework edit the copied version 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 and close the file.
Set the appropriate environment by executing the dnicus profile located in your deployment data directory.
To create the history tables, copy the historical data into the history tables, and purge that data from the runtime tables, enter:
```
db2 +c -o -l dnicdhit.trc -z dnicdhit.log CONNECT TO <runtime_db_name>
db2 +c -l dnicdhit.trc -z dnicdhit.log -stnovf /home/udb2adm1/dnicdhit.ddl
```
The variable <runtime_db_name> represents the name of the runtime database, for example, DNIDBRUN.
To ensure that no data is lost, the SQL statements in dnicdhit.ddl span a single unit of work.
The parameter +c turns off autocommit, and thereby ensures that data can be rolled back in case of error. However, it also turns off auto-rollback, so if you encounter an error while executing a script, you must roll back the transaction manually, for example, by entering:
```
db2 rollback
```
In addition, if you activated the DNI_DI_CLOSE statement in dnicdhit.ddl run:
```
db2 "CALL DNIvSN.DNI_DI_CLOSE('DNIvSN',?)"
```

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.