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

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

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 ;