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 ;
Release:
Component: PDT