Prevent duplicate rows from being loaded into the Detector unload/archive tables.
Article ID: 50026
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
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:
- PTI.PDT_STANDARD_150 - INDEX PTI.PDT_STANDARD_150NQ
- PTI.PDT_OBJECT_150 - INDEX PTI.PDT_OBJECT_150NQ
- 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
Component: PDT
Feedback
Yes
No
Powered by
