How to correct duplicate row problems when loading Sysview for Db2 Application detail archive tables

Products

• SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

The Db2 load utility encounters duplicate rows loading the Sysview for Db2 for z/OS (IDB2) application detail archive tables when the rows are not really duplicates.
The tables that can be affected by this problem are: APPLICATION_DETAIL, APPL_BP_DETAIL, APPL_DDF_DETAIL, APPL_GBP_DETAIL and APPL_PGM_DETAIL.

Cause

The thread end time store clock values for the two threads are different but when converted result in the same timestamp value. This timestamp value is used for the table's END_DATE_TIME TIMESTAMP column which is included as a key column in the table's UNIQUE INDEX. For example, the following two STCK values convert to the same timestamp value:

Accounting record QWACESC STCK          Converted to timestamp
-------------------------------------------------------------------
C7FCB78E315665C9                        2011-06-27-20.59.40.764582
C7FCB78E31566A86                        2011-06-27-20.59.40.764582

Resolution

There are two possible solutions that will resolve this problem for future archival executions.

1. The simplest solution would be to Drop the UNIQUE INDEX and recreate it as non-unique. This opens up the possibility that truly duplicate data could be accidentally loaded to the DB2 table. Having in place a pretty rigid archival process is needed to avoid this possibility.
   
   
2. A slightly more involved solution would be to add a new column to the UNIQUE INDEX key that is based on the value of Insight field IFCID-SEQ-NUM. The following will need to be done:
   
   
   1. Modify the associated archive IQL member and add field IFCID-SEQ-NUM as the last field with formatting as a fifteen digit decimal number. Insert this line prior to the OUTFILE statement. For example:
      
      IFCID-SEQ-NUM (OF=P15.0)
      
      
   2. Run the following ALTER TABLE DDL statement for the associated archive table.
      
      ALTER TABLE <creator>.<archive_table_name>
      ADD COLUMN IFCID_SEQ_NUM DECIMAL(15, 0) NOT NULL DEFAULT;
      
      
3. Run a DROP INDEX DDL statement to drop the associated UNIQUE INDEX.
   
   
4. Modify the CREATE INDEX DDL for the associated INDEX and add the IFCID_SEQ_NUM to the index column list. Run the CREATE UNIQUE INDEX DDL statement.
   
   
5. Modify the associated load utility control statements. Change the closing parenthesis in the field definition list to a comma and insert the following as the next line:
   
   IFCID_SEQ_NUM DECIMAL)
   
   
6. For future archival executions run the Insight report and DB2 load utility job using the modified IQL and DB2 load utility control statements.

Note: Adding IFCID-SEQ-NUM as the last field in the IQL and DB2 Load utility control statements saves having to update all the POSITION parameters in the DB2 load utility control statements.

Please be aware that if you chose to add the new column to the TABLE and UNIQUE INDEX you will need to modify the migration procedure(s) that will be supplied with a future release.

The solution to add the column to the table and unique index will be permanently implemented in a future release when Db2 10 support is added to the archive tables.