Utility object-level history information in SYSOBJEVENTS

FL 504Utility object-level history information is stored in the Db2 catalog table SYSIBM.SYSOBJEVENTS. Each row represents an object that is processed by a utility. Each utility has specific behavior that influences the object-level information collected and the timing of updates in SYSOBJEVENTS.

When utility object-level history is activated, a SYSOBJEVENTS row is inserted for the following objects that are processed by a utility:

Each partition of a partitioned table space and index space
Each nonpartitioned table space and index space

Most of the rows are inserted during utility initialization in the UTILINIT phase, after the utility-in-progress status (UTRW, UTRO, or UTUT) is set on the objects. In the job output, message DSNU3033I confirms that a row or rows were inserted into SYSOBJEVENTS.

Rows are not inserted for objects with the DEFINE NO attribute whose underlying data sets are undefined, except when the utility will define those data sets, such as the LOAD utility.

For a list of all SYSOBJEVENTS columns and their descriptions, see SYSOBJEVENTS catalog table. The following sections include more details about some of the SYSOBJEVENTS columns that can be helpful when interpreting these values.

EVENTTS column

The EVENTTS column contains the timestamp when processing started for the object. When the object is processed immediately, the SYSOBJEVENTS row is inserted with this column value set to the current local timestamp. Otherwise, the EVENTTS column is set to NULL and updated to the current local timestamp when processing of the object begins.

You can use the EVENTTS and COUNT column values to determine whether processing of an object has begun or completed. In cases where the COUNT column is not applicable and is NULL, you can use the RETURNCODE and CONDITION column values in the corresponding SYSUTILITIES row to obtain information about the status of a utility execution and to determine whether objects have completed processing.

ELAPSEDTIME column

The ELAPSEDTIME column applies only to the COPY and RECOVER utilities. For all other utilities, this column remains NULL.

For COPY and RECOVER, this column is set to NULL until processing of the sequential image copy for the object is complete. Specifically, this column value is updated as follows:

For COPY, this column contains the elapsed time in microseconds for the creation of a sequential image copy or copies.
For RECOVER, this column contains the elapsed time in microseconds for the restore of a sequential image copy or copies. The elapsed time for the PRELOGC, PRELOGA, LOGAPPLY, LOGCSR, and LOGUNDO phases is not included, because the objects in the list are processed together in these phases and the time for each object cannot be discerned.

When you run COPY and RECOVER on a list of objects, that list is processed by the utility in one utility execution. The objects are processed concurrently with the PARALLEL option in the COPY and RESTORE phases respectively. Therefore, elapsed time information for each object is useful. Similar information for most of the other utilities is provided by the ELAPSEDTIME column in SYSUTILITIES, because the objects in a list are processed either one at a time or as a related group of partitions (for the same table space or index space) or related group of indexes (associated with the same table) at the same time.

If COPY or RECOVER is restarted, the ELAPSEDTIME value represents processing in the restart execution only.

COUNT column

The SYSOBJEVENTS row is inserted with a COUNT column value of NULL. This value is updated to the number of items processed by the main function of the utility. Additional processing of the object is not recorded. For example, the REORG utility sets COUNT to the number of rows reloaded for the table space or table space partitions, and the number of pages written to the inline image copy is not recorded.

Some utilities, such as COPY and RECOVER, set the COUNT column value as soon as the processing of the object is complete, while other utilities wait until the end of a phase (LOAD is an example.) or the end of the utility (REORG is an example.). If the object is processed in subsequent phases by the utility, the COUNT value might be updated again. For example, the LOAD utility could update the COUNT value again if rows are deleted from the table space for unique index violations or referential integrity constraint violations. If the utility is restarted, the COUNT value might not reflect processing prior to the restart.

The following tables explain the specific meaning of the COUNT column value for each utility for a given object.

Notes:

For utilities that are not listed in these tables, the COUNT column is not applicable and has the default value of NULL. For example, COUNT remains NULL for objects that are processed by the QUIESCE utility.
If an object is not processed by one of the utilities listed in these tables, COUNT remains NULL.
In the following tables, if specific types of table spaces are not listed for a utility, the information for table space or table space partition applies. Similarly, if specific types of indexes are not listed for a utility, the information for index space or index space partition applies. Examples of specific types of table spaces are LOB or XML table spaces. An example of a specific type of index is a XML node ID index space.
In the following tables, N/A means the object type is not relevant for that utility, and a SYSOBJEVENTS row is not inserted.
The information in these tables is subject to change.

Table 1. COUNT column values for CHECK utilities
Object	CHECK DATA	CHECK INDEX	CHECK LOB
Table space or table space partition	Number of rows read to extract keys	Number of rows read to extract keys	N/A
Index space or index space partition	N/A	Number of keys checked	N/A
Index space or index space partition for primary key of parent	Number of keys checked	N/A	N/A
Index space or index space partition for foreign key	Number of keys checked	N/A	N/A
Table space or table space partition for dependent table	NULL	N/A	N/A
Table space or table space partition for exception table	Number of rows inserted for constraint violations. When DELETE YES is specified, this number is the same as the number of rows deleted for check constraint violations or for referential integrity constraint violations from the dependent table.	N/A	N/A
Index space or index space partition for exception table	Number of keys built	N/A	N/A
LOB table space	N/A	N/A	Number of LOBs checked
LOB auxiliary index space	Number of LOB keys checked	N/A	N/A
XML table space partition	NULL	N/A	N/A
XML node ID index space	NULL	N/A	N/A
XML values index space	NULL	N/A	N/A

Table 2. COUNT column values for backup and recover utilities
Object	COPY	COPYTOCOPY	MERGECOPY	RECOVER
Table space or table space partition	Number of pages written to the sequential or FlashCopy® image copy. This value is NULL if the sequential image copy data set was not allocated for incremental processing (no changed pages) or the CHANGELIMIT criteria was not met. This value is also NULL for a concurrent copy.	Number of pages written to the sequential image copy	Number of pages written to the sequential image copy or NULL when no incremental image copies to merge.	Number of pages restored to the object from the sequential or FlashCopy image copy. This value is NULL when the recovery base is a concurrent copy or system-level backup.
Index space or index space partition	Number of pages written to the sequential or FlashCopy image copy.	Number of pages written to the sequential image copy	N/A	Number of pages restored to the object from the sequential or FlashCopy image copy. This value is NULL for recovery by log processing only or when the recovery base is a concurrent copy or system-level backup.

Table 3. COUNT column values for the LOAD and UNLOAD utilities
Object	LOAD	UNLOAD
Table space or table space partition	Number of records loaded. This value is adjusted for discards due to unique index key violations and referential integrity constraint violations.	Number of rows written to the unload data set from the table space, table space partition, or image copy¹
Index space or index space partition	Number of keys built. This value is adjusted for discards due to unique index key violations and referential integrity constraint violations.	N/A
LOB table space	Number of LOBs loaded. This value is adjusted for discards due to unique index key violations and referential integrity constraint violations. This value does not include inline LOBs that are completely contained in the base table space.	Number of LOBs written to the unload data set from the LOB table space. This value does not include inline LOBs that are completely contained in the base table space.
LOB auxiliary index space	Number of keys built. This value is adjusted for discards due to unique index key violations and referential integrity constraint violations.	NULL
XML table space or partition	NULL	NULL
XML node ID index space	NULL	NULL
XML values index space	NULL	NULL

Table note:

If the image copy was created when the attributes of the table space differed from the current attributes, the number of rows written to the unload data set might not be reflected in a SYSOBJEVENTS row or the COUNT value. For example, if the image copy contains data pages for a table space partition that no longer exists, the rows in these data pages will not be recorded because a SYSOBJEVENTS row was not inserted for the partition. If the image copy was created when the table space was not partitioned but the table space is now partitioned, the COUNT value remains NULL.

Table 4. COUNT column values for the REORG TABLESPACE and RUNSTATS utilities
Object	REORG TABLESPACE	RUNSTATS
Table space or table space partition	Number of rows reloaded. This value does not include the number of rows discarded by DISCARD processing. This value is not adjusted for processing in the LOG phase.	Number of rows processed or sampled
Index space or index space partition	Number of keys built. This value is not adjusted for processing in the LOG phase.	Number of keys processed
Mapping index space or index space partition:	Number of keys built. This value might not correspond to the number of rows reloaded for the reorganized table space or partition.	N/A
LOB table space	Number of LOBs reloaded. This value does not include inline LOBs that are completely contained in the base table space.	Number of LOBs processed
LOB auxiliary index space	Number of keys built	N/A

Table 5. COUNT column values for the REBUILD INDEX and REORG INDEX utilities
Object	REBUILD INDEX	REORG INDEX
Table space or table space partition	Number of rows read	N/A
Index space or index space partition	Number of keys built. For SHRLEVEL CHANGE, this value does not include keys processed in the LOG phase.	Number of keys built. For SHRLEVEL CHANGE, this value does not include keys processed in the LOG phase.