SYSCOPY catalog table

The SYSCOPY table contains information needed for recovery. The schema is SYSIBM.

Table 1. SYSIBM.SYSCOPY table column descriptions
Column name	Data type	Description	Use
DBNAME	CHAR(8) NOT NULL	Name of the database.	G
TSNAME	CHAR(8) NOT NULL	Name of the target table space or index space.	G
DSNUM	INTEGER NOT NULL	Data set number within the table space. For partitioned table spaces, this value corresponds to the partition number for a single partition copy, or 0 for a copy of an entire partitioned table space or index space.	G
ICTYPE	CHAR(1) NOT NULL	Type of operation: A ALTER B REBUILD INDEX C CREATE D CHECK DATA LOG(NO) (no log records for the range are available for RECOVER utility) E RECOVER (to current point) F COPY FULL YES I COPY FULL NO J REORG TABLESPACE or LOAD REPLACE compression dictionary write to log L SQL (type of operation) M MODIFY RECOVERY utility P RECOVER TOCOPY, RECOVER TOLOGPOINT, or RECOVER TORBA (to a point in time) Q QUIESCE R LOAD REPLACE LOG(YES) S LOAD REPLACE LOG(NO) T TERM UTILITY command V REPAIR CATALOG utility W REORG LOG(NO) X REORG LOG(YES) Y LOAD LOG(NO) Z LOAD LOG(YES)	G
ICDATE	CHAR(6) NOT NULL	Date of the last image copy	N
START_RBA	CHAR(10) NOT NULL FOR BIT DATA	An 80-bit positive integer that contains the RBA/LRSN of a point in the Db2 recovery log. (The LRSN is the RBA in a data-sharing environment.) For ICTYPE I or F, the starting point for all updates since the image copy was taken For ICTYPE J, the RBA/LRSN of the compression dictionary For ICTYPE M, the RBA of the highest deleted SYSCOPY or SYSLGRNX record For ICTYPE P, the point after the log-apply phase of point-in-time recovery For ICTYPE Q, the point after all data sets have been successfully quiesced For ICTYPE R or S, the end of the log before the start of the LOAD utility and before any data is changed For ICTYPE T, the end of the log when the utility is terminated For other values of ICTYPE, the end of the log before the start of the RELOAD phase of the LOAD or REORG utility.	G
FILESEQNO	INTEGER NOT NULL	Tape file sequence number of the copy.	G
DEVTYPE	CHAR(8) NOT NULL	Device type the copy is on.	G
IBMREQD	CHAR(1) NOT NULL	A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators. The value in this field is not a reliable indicator of release dependencies. RELCREATED should be used instead.	G
DSNAME	CHAR(44) NOT NULL	For ICTYPE='P' (RECOVER TOCOPY only), 'I', or 'F', DSNAME contains the data set name. Otherwise, DSNAME contains the name of the database and table space or index space in the form, `database-name.space-name`, or DSNAME is blank for any row migrated from a release prior to Version 4. For redirected recoveries, DSNAME contains the source table space or index space name, in the form, database-name.space-name. FL 508 A SYSCOPY record with ICTYPE='W' and STYPE='M' for the target table space indicates that a REORG moved a table into the target table space. In this case, the DSNAME column contains the name of the source table space, `database-name.tablespace-name`. The DBNAME column contains the name of the target database, `database-name`, and the TSNAME column contains the name of the target table space, `tablespace-name`.	G
ICTIME	CHAR(6) NOT NULL	Time of the last image copy.	N
SHRLEVEL	CHAR(1) NOT NULL	SHRLEVEL parameter value on COPY (for ICTYPE F or I only): C CHANGE R REFERENCE blank Does not describe an image copy or was migrated from Version 1 Release 1 of Db2.	G
DSVOLSER	VARCHAR(1784) NOT NULL	If the operation is not an image copy operation that creates a FlashCopy® image copy with consistency (an image copy operation with the FLASHCOPY CONSISTENT option), this value is: A comma-separated list of 6-byte volume serial numbers of the data set, if the data set is not cataloged. Blank if the data set is cataloged. If the operation is a FlashCopy image copy with consistency that had uncommitted units of work backed out or a sequential image copy that was created from such a FlashCopy image copy by COPY or COPYTOCOPY, this value is a comma-separated list of values in one of the following forms: `memberID-ckptrba` This format is used for SYSCOPY records that were inserted before SYSCOPY was converted to the Db2 11 enabling-new-function mode format. `ckptrba` is the 12-byte hexadecimal checkpoint RBA for the member. `memberID+ckptrba` This format is used for SYSCOPY records that were inserted after SYSCOPY was converted to the Db2 11 new-function mode format or later. `ckptrba` is the 20-byte hexadecimal checkpoint RBA for the member. `memberID` is a 3-digit ID for a member of a data sharing group. For a non-data sharing environment, `memberID` is `000`.	G
TIMESTAMP	TIMESTAMP NOT NULL WITH DEFAULT	The date and time when the row was inserted. For the COPYTOCOPY utility, this value is the date and time when the row was inserted for the primary local site or primary recovery site copy. For an EXCHANGE DATA statement, this is the time that the statement is run.	G
ICBACKUP	CHAR(2) NOT NULL WITH DEFAULT	Specifies the type of image copy contained in the data set: blank LOCALSITE primary copy (first data set named with COPYDDN) FC FlashCopy copy LB LOCALSITE backup copy (second data set named with COPYDDN) RP RECOVERYSITE primary copy (first data set named with RECOVERYDDN) RB RECOVERYSITE backup copy (second data set named with RECOVERYDDN)	G
ICUNIT	CHAR(1) NOT NULL WITH DEFAULT	Indicates the media that the image copy data set is stored on: D DASD T Tape blank Medium is neither tape nor DASD, the image copy is from a Db2 release prior to Version 2 Release 3, or ICTYPE is not 'I' or 'F'.	G
STYPE	CHAR(1) NOT NULL WITH DEFAULT	When ICTYPE=A, the values are: A A partition was added or inserted to a table. B The MEMBER CLUSTER value was changed. C A column was added to a table and an index in different commit scopes, index compression was activated or deactivated, or a column was dropped from a table. A column was added to a table and an index in different commit scopes, or a column was dropped from a table. D Either the DSSIZE attribute of the table space was altered or the default value of a column of a table was altered. E The data set numbers of a base table and its associated clone table are exchanged. F The page size attribute of the table space or index was altered. G An index was regenerated H The table was altered to hash organization, the size of the hash space was changed, or the hash organization was dropped. The value of the TTYPE column indicates the action taken. I The inline length attribute of the LOB column was altered by REORG. L The logging attribute of the table space was altered to LOGGED. M The MAXPARTITIONS attribute of the table space was altered. N An index was altered to not padded O The logging attribute of the table space was altered to NOT LOGGED. P An index was altered to padded R A table was altered to rotate partitions.	G
STYPE (continued)		When ICTYPE=A, the values are (continued): S The SEGSIZE attribute of the table space was altered. T FL 508 A table space attribute was altered. U An ALTER TABLE ALTER COLUMN DROP DEFAULT statement was executed on a column that was previously added with an ALTER TABLE ADD COLUMN statement. V A column in a table was altered for a numeric data type change and the column is in an index. X A REORG dropped one or more empty partitions from the related table space. Y An index was altered to COPY YES Z A column that is in the key of an index that was versioned prior to DB2® version 8 was altered. When ICTYPE=C, the values are: L The logging attribute of the table space was LOGGED. O The logging attribute of the table space was NOT LOGGED. When ICTYPE=E, the values are: B RECOVER utility with the BACKOUT keyword. blank RECOVER utility without the BACKOUT keyword. When ICTYPE=F, the values are: C DFSMS concurrent copy ("I" instance of the table space) J DFSMS concurrent copy ("J" instance of the table space) N A FlashCopy copy is not consistent. Q Sequential copy is consistent
STYPE (continued)		When ICTYPE=F, the values are (continued): R LOAD REPLACE(YES) S LOAD REPLACE(NO) T FlashCopy copy is consistent. U Sequential copy is not consistent V ALTER INDEX NOT PADDED W REORG LOG(NO) X REORG LOG(YES) Y LOAD RESUME YES LOG(NO) Z LOAD RESUME YES LOG(YES) blank Db2 image copy When ICTYPE=L, the value is: M Mass DELETE, TRUNCATE TABLE, DROP TABLE, or ALTER TABLE ROTATE PARTITION. The LOWDSNUM column contains the table OBID of the affected table. The MERGECOPY utility, when used to merge an embedded copy with subsequent incremental copies, also produces a record that contains ICTYPE=F and the STYPE of the original image copy (R, S, W, or X). When ICTYPE = M and the MODIFY RECOVERY utility was executed to delete SYSCOPY and/or SYSLGRNX records, the value is R. When ICTYPE=O, the values are: B A table space or partition that was in reordered row format was recovered to a point in time when it was in basic row format. R A table space or partition was converted to reordered row format as a result of REORG or LOAD REPLACE.
STYPE (continued)		When ICTYPE=P, the values are: B RECOVER with the BACKOUT YES option. C RECOVER without using LOGONLY with consistency. F Indicates RECOVER using FROM , where the table space was recovered using recovery resources from a different source table space. J RECOVER using LOGONLY without consistency and using the ENFORCE NO option. K RECOVER without using LOGONLY or the BACKOUT YES and the ENFORCE NO options. L RECOVER using LOGONLY without consistency. M RECOVER using LOGONLY with consistency. blank RECOVER without using LOGONLY without consistency. When ICTYPE=Q and option WRITE(YES) is in effect when the quiesce point is taken, the value is W. When ICTYPE=R or S, the values are: A Resetting REORG pending status T First materializing the default value for a row change timestamp column When ICTYPE=T, this field indicates which COPY utility was terminated by the TERM UTILITY command or the START DATABASE command with the ACCESS(FORCE) option. The values are: F COPY FULL YES When ICTYPE=T, the values are (continued): I COPY FULL NO
STYPE (continued)		When ICTYPE=W, the values are: M FL 508 REORG on the source table space materialized an ALTER TABLESPACE MOVE TABLE statement When ICTYPE=W or X, the values are: A Resetting REORG pending status or REBALANCE T First materializing the default value for a row change timestamp column For other values of ICTYPE, the value is blank.
PIT_RBA	CHAR(10) NOT NULL WITH DEFAULT FOR BIT DATA	The meaning of the value depends on the value of the ICTYPE column: ICTYPE='P' The LRSN for the point in the Db2 log. (The LRSN is the RBA in a non-data-sharing environment) The value indicates the stop location of a point-in-time recovery. If a record contains ICTYPE='P' and PIT_RBA=X'000000000000', the copy pending status is active and a full image copy is required. If such a record is encountered during fallback processing of RECOVER, the recover job fails, and a point-in-time recovery is required. PIT_RBA can be zero if the point-in-time recovery is completed by the fall-back processing of RECOVER, or if ICTYPE=P from a prior release of Db2. ICTYPE='F' or 'I' and SHRLEVEL='C' The current RBA or LRSN that corresponds to the point in the Db2 log when the SHRLEVEL CHANGE copy completes. ICTYPE='F', SHRLEVEL='R' or 'C', and ICBACKUP='FC' The RBA or LRSN that corresponds to the point in the Db2 log when the FlashCopy completes. CTYPE='F' and SHRLEVEL 'R' or 'C', STYPE= 'T', 'N', 'Q', or 'U' The RBA or LRSN that corresponds to the point in the Db2 log when the FlashCopy completes. ICTYPE=J The RBA where the compression dictionary is written to the log. In data sharing environments, it is the RBA of the member writing to the log. ICTYPE='M' The RBA/LRSN for the end of the log when the utility completes. For other all other ICTYPE values, this field contains X'00000000000000000000'.	G
GROUP_MEMBER	CHAR(8) NOT NULL WITH DEFAULT	The Db2 data sharing member name of the Db2 subsystem that performed the operation. This column is blank if the Db2 subsystem was not in a Db2 data sharing environment at the time the operation was performed.	G
OTYPE	CHAR(1) NOT NULL WITH DEFAULT 'T'	Type of object that the recovery information is for: I Index space T Table space	G
LOWDSNUM	INTEGER NOT NULL WITH DEFAULT	Partition number of the lowest partition in the range for SYSCOPY records created for REORG and LOAD REPLACE for resetting a REORG pending status. Version number of an index for SYSCOPY records created for a COPY (ICTYPE=F) of an index space (OTYPE=I). (An index is versioned when a VARCHAR column in the index key is lengthened.) When ICTYPE = F or I, DSNUM = 0 and OTYPE is not equal to I, LOWDSNUM = 1. When ICTYPE=A and STYPE=X, this value is the lowest partition number that was dropped by REORG.	G
HIGHDSNUM	INTEGER NOT NULL WITH DEFAULT	Partition number of the highest partition in the range. This column is valid only for SYSCOPY records created for REORG and LOAD REPLACE for resetting REORG pending status. When ICTYPE = F or I, DSNUM = 0 and OTYPE is not equal to I, this value is the number of the highest partition that is copied. When ICTYPE=A and STYPE=X, this value is the highest partition number that was dropped by REORG.	G
COPYPAGESF	FLOAT(8) NOT NULL WITH DEFAULT -1	Number of pages written to the copy data set. For inline copies, this number might include pages appearing more than once in the copy data set.	G
NPAGESF	FLOAT(8) NOT NULL WITH DEFAULT -1	The number of pages in the table space or index at the time of COPY. This number might include pre-formatted pages that are not actually copied. When ICTYPE=A, SYTPE=H, and TTYPE=S or D, this column contains the previous HASHDATAPAGES value. When ICTYPE=A, SYTPE=H, and TTYPE=A this column contains zero.	G
CPAGESF	FLOAT(8) NOT NULL WITH DEFAULT -1	Total number of changed pages since last copy.	G
JOBNAME	CHAR(8) NOT NULL WITH DEFAULT	Job name of the utility. For changes that cause pending definition changes to object, this column might not be accurate.	G
AUTHID	CHAR(8) NOT NULL WITH DEFAULT	Authorization ID of the utility.For changes that cause pending definition changes to object, this column might not be accurate.	G
OLDEST_VERSION	SMALLINT NOT NULL WITH DEFAULT	When ICTYPE= B, F, I, R, S, W, or X: For a single-table table space, this value is the version number of the oldest format of data for an object. For a multiple-table table space: A value of zero indicates that there is a least one table in the table space that has data rows in version 0 format. When ICTYPE=B, F, I, W, or X, a value greater than 0 is the oldest version for tables that are not at version 0. Any tables that are at version 0 have system pages for a version 0 format. When ICTYPE=R or S: A value greater than 0 is the oldest version for tables that are not at version 0. Any tables that are at version 0 have system pages for a version 0 format. A value of -1 means that the oldest version number cannot be determined. For other values of ICTYPE, the value is -1. The default value of this column is 0.	G
LOGICAL_PART	INTEGER NOT NULL WITH DEFAULT	Logical partition number.	G
LOGGED	CHAR(1) NOT NULL WITH DEFAULT	Indicates the logging attribute of the table space at the time the SYSCOPY record is written: Y — indicates that the logging attribute of the table space is LOGGED N — indicates that the logging attribute of the table spaces is NOT LOGGED blank — indicates that the row was inserted prior to DB2 9 or is not specified. For non-LOB table spaces or an index space, blank indicates that the logging attribute is LOGGED.	G
TTYPE	CHAR(8) NOT NULL WITH DEFAULT	The value of TTYPE column indicates different information depending on the values of the ICTYPE, STYPE, and in some cases, OTYPE column. When ICTYPE=A and STYPE=B: Y The previous member cluster attribute of the table space is being used. N The previous member cluster attribute of the table space is not being used. When ICTYPE=A and STYPE=C: blank A column was added to a table. D A column was dropped from a table. CMP=N Index compression was activated with the ALTER INDEX COMPRESS YES statement. CMP=Y Index compression was deactivated with the ALTER INDEX COMPRESS NO statement. When ICTYPE=A and STYPE=D: `x` The previous DSSIZE attribute value for the table space in units of G, M, or K. blank The default value of a column of a table was altered. When ICTYPE=A and STYPE=F: `x` The previous page size attribute value for the table space in units of K. When ICTYPE=A and STYPE=H: A Hash organization was added. The record is written when the hash space is materialized at REORG. D Hash organization was dropped. The record is written immediately when the ALTER statement is issued. S The size of the hash space was changed. The value of the NAPGESF column contains the previous HASHDATAPAGES value. The record is written when the hash space is materialized at REORG.	G
TTYPE (continued)		When ICTYPE=A and STYPE=I: D REORG decremented the inline length of the LOB column I REORG incremented the inline length of the LOB column When ICTYPE=A and STYPE=M: I The table space was converted from a single-table simple table space to a partition-by-growth table space. `n` The previous value of the MAXPARTITIONS attribute for the table space. S The table space was converted from single-table segmented table space to a partition-by-growth table space. When ICTYPE=A and STYPE=P: ABSOLUTE The table space was converted from absolute to relative page numbering. When ICTYPE=A and STYPE=S: `n` The previous value of the SEGSIZE attribute for the table space. P The table space was converted from a partitioned (non-UTS) table space to a partition-by-range table space. FL 508 When ICTYPE=A and STYPE=T: M One or more tables were moved by the ALTER TABLESPACE MOVE TABLE statement When ICTYPE=E: blank The full recovery reset the object N The full recovery did not reset the object
TTYPE (continued)		When ICTYPE=F and OTYPE=I: S indicates that the directory pages for the index image copy are at the front of each partition and are indicated with a 'V' or '8'. When ICTYPE=F and STYPE=N, Q, T, or U, this column indicates the utility that made the FlashCopy: A LOAD RESUME LOG NO B REBUILD C COPY D LOAD RESUME LOG YES E LOAD SHRLEVEL CHANGE L LOAD P REPAIR R LOAD REPLACE LOG YES S LOAD REPLACE LOG NO T COPYTOCOPY W REORG TABLESPACE LOG NO X REORG TABLESPACE LOG YES When ICTYPE=F and STYPE=R, S, Y, or Z: V This inline sequential image copy created by LOAD contains pages updated during the INDEXVAL and ENFORCE phases. Rows that were loaded with unique index violations, referential constraint violations, or index evaluation errors were removed from pages in the image copy.
TTYPE (continued)		When ICTYPE=I: N Indicates that no system pages are in this incremental image copy. blank Indicates that system pages are in this incremental image copy. I Indicates that the incremental copy was created for a table space or partition with the TRACKMOD YES attribute using the RBA or LRSN in each page instead of the modified page indicator bits in the space map pages. When ICTYPE=P, R, S, W, X, this column provides additional diagnostic information: B Indicates that the RBA or LRSN format changed to basic 6-byte format. BRF Indicates that the row format is the basic row format. BRF I Indicates that the row format is the basic row format, and the FORMAT INTERNAL option was specified. E Indicates that the RBA or LRSN format changed to extended 10-byte format. F Indicates that the REORG utility was run with the FASTSWITCH YES option. RRF I Indicates that the row format is the reordered row format, and the FORMAT INTERNAL option was specified. S Indicates that the REORG utility was run with the FASTSWITCH NO option. When ICTYPE=M and STYPE=R: blank MODIFY RECOVERY deleted rows from SYSIBM.SYSLGRNX. N MODIFY RECOVERY did not delete rows from SYSIBM.SYSLGRNX.
TTYPE (continued)		When ICTYPE=T: B Indicates that a broken page was detected during copy. When ICTYPE=W or X and STYPE=H: `n` the previous value of HASHDATAPAGES When ICTYPE=Y or Z: blank Indicates that the FORMAT INTERNAL option was not specified during LOAD. I Indicates that the FORMAT INTERNAL option was specified during LOAD. When ICTYPE=A-A, A-R, B, C, P, R, S, W, or X, indicates that the page format was changed by the ALTER ADD PARTITION, ALTER ROTATE PARTITION, or CREATE statement, or by the LOAD REPLACE, REBUILD, REORG, or RECOVER utilities: B Indicates that the page format was created or converted to basic page format with 6-byte RBA or LRSN values E Indicates that the page format was created or converted to extended page format with 10-byte RBA or LRSN values. When ICTYPE=A and STYPE=A or R: B Indicates that the page format was converted to basic page format with 6-byte RBA or LRSN values. E Indicates that the page format was converted to extended page format with 10-byte RBA or LRSN values.
INSTANCE	SMALLINT NOT NULL WITH DEFAULT 1	When STYPE = E and ICTYPE = A, INSTANCE indicates the data set instance number of a base object after an EXCHANGE statement completes. The value of the INSTANCE column for the last data exchange will match the value of the INSTANCE column for the SYSIBM.SYSTABLESPACE table. For an image copy, INSTANCE indicates the instance number of the current base objects (table and index).	G
RELCREATED	CHAR(1) NOT NULL WITH DEFAULT	The Db2 release that created the object. Blank if created prior to DB2 9. For other values, see "Release dependency indicators" in Db2 catalog tables.	G
MODECREATED	CHAR(2) NOT NULL WITH DEFAULT	The latest mode to which the Db2 subsystem had been migrated when the SYSCOPY record was written: C Conversion mode or CM* E Enabling-new-function mode or ENFM* N New-function mode blank Prior to Db2 11	G