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_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 ;