ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Prevent duplicate rows from being loaded into the Detector unload/archive tables.

book

Article ID: 50026

calendar_today

Updated On:

Products

RC Compare for DB2 for z/OS Bind Analyzer for DB2 for z/OS SQL-Ease for DB2 for z/OS SYSVIEW Performance Management Option for DB2 for z/OS Plan Analyzer for DB2 for z/OS Subsystem Analyzer for DB2 for z/OS Database Analyzer for DB2 for z/OS Fast Unload for DB2 for z/OS Fast Check for DB2 for z/OS Fast Index for DB2 for z/OS Fast Load for DB2 for z/OS Rapid Reorg for DB2 for z/OS

Issue/Introduction

Description:

When loading data into some of the tables, it would be nice to have a unique index to prevent loading duplicates. Is this possible?

Solution:

Yes, we have created a unique index for three of the nine archive tables.

The Tables and Indexes are:

  1. PTI.PDT_STANDARD_150 - INDEX PTI.PDT_STANDARD_150NQ

  2. PTI.PDT_OBJECT_150 - INDEX PTI.PDT_OBJECT_150NQ

  3. PTI.PDT_STANTEXT_150 - INDEX PTI.PDT_STANTEXT_150NQ
--  *---------------------------------------------------------------
--  * PTI.PDT_STANDARD_150NQ index will be created.
--  *---------------------------------------------------------------
 
    CREATE UNIQUE INDEX PTI.PDT_STANDARD_150NQ
           ON PTI.PDT_STANDARD_150
           ( SSID             ASC
/*  Either the SSID or DSGROUP will contain data and this           */
/*  corresponds to the unload control cards.                        */
           , DSGROUP          ASC
/*  Only one interval column is needed for uniqueness, but both are */
/*  included to allow for better searching                          */
           , INTERVAL_START   ASC
           , INTERVAL_END     ASC
           , RECTYPE          ASC
           , PLANNAME         ASC
           , PROGRAM          ASC
           , PGMTYPE          ASC
           , COLLID           ASC
/*  Is both CONTOKEN and VERSION needed?  Yes, it is possible for   */
/*  them to be different based on precompile options.               */
           , CONTOKEN         ASC
           , VERSION          ASC
           , SECT#            ASC
           , STMT#            ASC
           , SQL_CALL         ASC
           , DYN_TEXT_TOKEN   ASC
/*  The final 8 columns are needed when the "VIEW BY" keys          */
/*  collection option is used.  If this option is never used, then  */
/*  these columns can be removed, however, if this option is ever   */
/*  turned on, those keys enabled need the respective column added  */
/*  to the unique index.                                            */
           , CONN_TYPE        ASC
           , CONN_NAME        ASC
           , CORRID           ASC
           , LOCATION         ASC
           , USERID           ASC
           , END_USER_ID      ASC
           , TRANSACTION_ID   ASC
           , WORKSTATION_ID   ASC)
           BUFFERPOOL BP0
           USING STOGROUP PTSG
                 SECQTY   -1
                 ERASE    NO
           FREEPAGE 0
           PCTFREE 0 ;
 
--  *---------------------------------------------------------------
--  * PTI.PDT_OBJECT_150NQ index will be created.
--  *---------------------------------------------------------------
 
    CREATE UNIQUE INDEX PTI.PDT_OBJECT_150NQ
           ON PTI.PDT_OBJECT_150
           ( SSID             ASC
/*  Either the SSID or DSGROUP will contain data and this           */
/*  corresponds to the unload control cards.                        */
           , DSGROUP          ASC
/*  Only one interval column is needed for uniqueness, but both are */
/*  included to allow for better searching                          */
           , INTERVAL_START   ASC
           , INTERVAL_END     ASC
           , RECTYPE          ASC
           , PLANNAME         ASC
           , PROGRAM          ASC
           , PGMTYPE          ASC
/*  Is both CONTOKEN and VERSION needed?  Yes, it is possible for   */
/*  them to be different based on precompile options.               */
           , CONTOKEN         ASC
           , VERSION          ASC
           , COLLID           ASC
           , SECT#            ASC
           , STMT#            ASC
           , SQL_CALL         ASC
           , DYN_TEXT_TOKEN   ASC
/*  The next 4 columns are unique to this table data                */
           , DBID             ASC
           , TSID             ASC
           , TBID             ASC
           , ISID             ASC
/*  The final 8 columns are needed when the "VIEW BY" keys          */
/*  collection option is used.  If this option is never used, then  */
/*  these columns can be removed, however, if this option is ever     */
/*  turned on, those keys enabled need the respective column added  */
/*  to the unique index.                                            */
           , CONN_TYPE        ASC
           , CONN_NAME        ASC
           , CORRID           ASC
           , LOCATION         ASC
           , USERID           ASC
           , END_USER_ID      ASC
           , TRANSACTION_ID   ASC
           , WORKSTATION_ID   ASC
           )
           BUFFERPOOL BP0
           USING STOGROUP PTSG
                 SECQTY   -1
                 ERASE    NO
           FREEPAGE 0
           PCTFREE 0 ;
 
--  *---------------------------------------------------------------
--  * PTI.PDT_STANTEXT_150NQ index will be created.
--  *---------------------------------------------------------------
 
    CREATE UNIQUE INDEX PTI.PDT_STANTEXT_150NQ
           ON PTI.PDT_STANTEXT_150
           ( SSID             ASC
/*  Either the SSID or DSGROUP will contain data and this           */
/*  corresponds to the unload control cards.                        */
           , DSGROUP          ASC
/*  Only one interval column is needed for uniqueness, but both are */
/*  included to allow for better searching                          */
           , INTERVAL_START   ASC
           , INTERVAL_END     ASC
           , RECTYPE          ASC
           , PLANNAME         ASC
           , PROGRAM          ASC
           , COLLID           ASC
/*  Is both CONTOKEN and VERSION needed?  Yes, it is possible for   */
/*  them to be different based on precompile options.               */
           , CONTOKEN         ASC
           , VERSION          ASC
           , SECT#            ASC
           , STMT#            ASC
           , SQL_CALL         ASC
           , DYN_TEXT_TOKEN   ASC
/*  the next 1 column  is  unique to this table data                */
           , SEQNO            ASC
/*  The final 8 columns are needed when the "VIEW BY" keys          */
/*  collection option is used.  If this option is never used, then  */
/*  these columns can be removed, however, if this option is ever     */
/*  turned on, those keys enabled need the respective column added  */
/*  to the unique index.                                            */
           , CONN_TYPE        ASC
           , CONN_NAME        ASC
           , CORRID           ASC
           , LOCATION         ASC
           , USERID           ASC
           , END_USER_ID      ASC
           , TRANSACTION_ID   ASC
           , WORKSTATION_ID   ASC)
           BUFFERPOOL BP0
           USING STOGROUP PTSG
                 SECQTY   -1
                 ERASE    NO
           FREEPAGE 0
           PCTFREE 0 ;

Environment

Release:
Component: PDT
Powered by Wolken Software