INSPECT command

The INSPECT command inspects database for architectural integrity, checking the pages of the database for page consistency. The INSPECT command checks that the structures of table objects and structures of table spaces are valid. Cross object validation conducts an online index to data consistency check.

Scope

In a single partition database environment, the scope is that single partition only. In a partitioned database environment, it is the collection of all logical partitions defined in db2nodes.cfg. For partitioned tables, the CHECK DATABASE and CHECK TABLESPACE options include individual data partitions and non-partitioned indexes. The CHECK TABLE option is also available for a partitioned table, however it will check all data partitions and indexes in a table, rather than checking a single data partition or index.

Authorization

For INSPECT CHECK, one of the following authorities:

SYSADM
DBADM
SYSCTRL
SYSMAINT
SCHEMAADM authority on the schema of the table, if single table
CONTROL privilege if single table.

Required Connection

Database

Command Syntax

Check Clause

Row Compression Estimate Clause

Level Clause

Cross Object Checking Clause

On Database Partition Clause

Database Partition List Clause

Command Parameters

CHECK

Specifies check processing.

DATABASE

Specifies whole database.

BEGIN TBSPACEID n

Specifies processing to begin from table space with given table space ID number.

OBJECTID n: Specifies processing to begin from table with given table space ID number and object ID number.

TABLESPACE

NAME tablespace-name: Specifies single table space with given table space name.
TBSPACEID n: Specifies single table space with given table space ID number.
BEGIN OBJECTID n: Specifies processing to begin from table with given object ID number.

TABLE

NAME table-name: Specifies table with given table name.
SCHEMA schema-name: Specifies schema name for specified table name for single table operation.
TBSPACEID n OBJECTID n: Specifies table with given table space ID number and object ID number.

ROWCOMPESTIMATE-TABLE

Estimates the effectiveness of row compression for a table. You can also specify which database partitions this operation is to be done on.

This operation will keep the RESULTS output file regardless if the KEEP option is specified.

This tool is capable of taking a sample of the table data, and building a dictionary from it. This dictionary can then be used to test compression against the records contained in the sample. From this test compression, data is be gathered from which the following estimates are made:

Percentage of bytes saved from compression
Percentage of pages saved from compression
Compression dictionary size
Expansion dictionary size

INSPECT will insert the dictionary built for gathering these compression estimates if the COMPRESS YES attribute is set for this table, and a dictionary does not already exist for this table. INSPECT will attempt to insert the dictionary concurrent to other applications accessing the table. Dictionary insert requires an Exclusive Table Alter lock and an Intent on Exclusive Table lock. INSPECT will only insert a dictionary into tables that support data row compression. For partitioned tables, a separate dictionary is built and inserted on each partition.

When sampling table row data and building a compression dictionary for a table, the INSPECT command supports only the table row data in the table object. If the table contains XML columns, data is not sampled and a compression dictionary is not built for the XML data in the XML storage object of the table. Use the table function instead.

The ROWCOMPESTIMATE option does not provide an index compression estimate. Use the table function instead.

This parameter does not support column-organized tables.

RESULTS

Specifies the result output file. The file will be written out to the diagnostic data directory path. If there is no error found by the check processing, this result output file will be erased at the end of the INSPECT operation. If there are errors found by the check processing, this result output file will not be erased at the end of the INSPECT operation.

KEEP: Specifies to always keep the result output file.
file-name: Specifies the name for the result output file. The file has to be created in the diagnostic data directory path.

ALL DBPARTITIONNUMS

Specifies that operation is to be done on all database partitions specified in the db2nodes.cfg file. This is the default if a database partition clause is not specified.

EXCEPT

Specifies that operation is to be done on all database partitions specified in the db2nodes.cfg file, except those specified in the database partition list.

ON DBPARTITIONNUM | ON DBPARTITIONNUMS

Perform operation on a set of database partitions.

db-partition-number1: Specifies a database partition number in the database partition list.
db-partition-number2: Specifies the second database partition number, so that all database partitions from db-partition-number1 up to and including db-partition-number2 are included in the database partition list.

FOR ERROR STATE ALL

For table object with internal state already indicating error state, the check will just report this status and not scan through the object. Specifying this option will have the processing scan through the object even if internal state already lists error state.

When used with the INDEXDATA option, as long as the index or data object is in an error state, the online index to data consistency checking will not be performed.

LIMIT ERROR TO n

Number of pages in error for an object to which reporting is limited. When this limit of the number of pages in error for an object is reached, the processing will discontinue the check on the rest of the object.

When used with the INDEXDATA option, n represents the number of errors to which reporting is limited during the online index to data consistency checking.

LIMIT ERROR TO DEFAULT

Default number of pages to limit error reporting for an object. This value is the extent size of the object. This parameter is the default.

When used with the INDEXDATA option, DEFAULT represents the default number of errors to which reporting is limited during the online index to data consistency checking.

LIMIT ERROR TO ALL

No limit on number of pages in error reported.

When used with the INDEXDATA option, ALL represents no limit on the number of errors reported during the online index to data consistency checking.

EXTENTMAP

Specifies the processing level for an extent map.

NORMAL: Specifies a normal processing level. This option is the default.
NONE: Specifies no processing.
LOW: Specifies a low processing level.

DATA

Specifies the processing level for a data object.

NORMAL: Specifies a normal processing level. This option is the default.
NONE: Specifies no processing.
LOW: Specifies a low processing level.

COL

Specifies the processing level for a column-organized data object.

NORMAL: Specifies a normal processing level. This option is the default.
NONE: Specifies no processing.
LOW: Specifies a low processing level.

BLOCKMAP

Specifies the processing level for a block map object.

NORMAL: Specifies a normal processing level. This option is the default.
NONE: Specifies no processing.
LOW: Specifies a low processing level.

This parameter does not support column-organized tables.

INDEX

Specifies the processing level for an index object.

NORMAL: Specifies a normal processing level. This option is the default.
NONE: Specifies no processing.
LOW: Specifies a low processing level.

LONG

Specifies the processing level for a long object.

NORMAL: Specifies a normal processing level. This option is the default.
NONE: Specifies no processing.
LOW: Specifies a low processing level.

LOB

Specifies the processing level for a LOB.

NORMAL: Specifies a normal processing level. This option is the default.
NONE: Specifies no processing.
LOW: Specifies a low processing level.

XML

Specifies the processing level for an XML column object.

NORMAL: Specifies a normal processing level. This option is the default. Pages of the XML object are checked for most inconsistencies. Actual XML data is not inspected.
NONE: Specifies no processing.
LOW: Specifies a low processing level. Pages of the XML object are checked for some inconsistencies. Actual XML data is not inspected.

INDEXDATA

Specified in order to perform an index to data consistency check. INDEXDATA checking is not performed by default.

This parameter does not support column-organized tables.

Examples

To perform an index to data consistency check that allows read/write access to all objects, even the object inspected at the moment, issue the following command:
```
inspect check table name fea3 indexdata results keep fea3high.out
```
To perform an index to data consistency check that allows read or write access to all objects, including the object that is being currently inspected, issue:
```
 INSPECT CHECK TABLE NAME car SCHEMA vps INDEXDATA RESULTS KEEP table1.out
```
To estimate how much storage space will be saved if the data in a table named EMPLOYEE is compressed, issue:
```
 INSPECT ROWCOMPESTIMATE TABLE NAME car SCHEMA vps RESULTS table2.out
```

Usage Notes

For CHECK operations on table objects, the level of processing can be specified for the objects. The default is NORMAL level, specifying NONE for an object excludes it. Specifying LOW will do subset of checks that are done for NORMAL.
The CHECK DATABASE option can be specified to start from a specific table space or from a specific table by specifying the ID value to identify the table space or the table.
The CHECK TABLESPACE option can be specified to start from a specific table by specifying the ID value to identify the table.
The processing of table spaces will affect only the objects that reside in the table space. The exception is when the INDEXDATA option is used. INDEXDATA will check index to data consistency as long as the index object resides in the table space. This means:
- If the data object resides in a different table space than the specified table space to be inspected where the index object resides, it can still benefit from the INDEXDATA checking.
- For a partitioned table, each index can reside in a different table space. Only those indexes that reside in the specified table space will benefit from the index to data checking. If you want to inspect all the indexes against one table, use the CHECK TABLE option or the CHECK DATABASE option.
The online inspect processing will access database objects using isolation level uncommitted read. COMMIT processing will be done during INSPECT processing. It is advisable to end the unit of work by issuing a COMMIT or ROLLBACK before invoking INSPECT.
The online inspect check processing will write out unformatted inspection data results to the results file specified. The file will be written out to the diagnostic data directory path. If there is no error found by the check processing, this result output file will be erased at the end of INSPECT operation. If there are errors found by the check processing, this result output file will not be erased at the end of INSPECT operation. After check processing completes, to see inspection details, the inspection result data will require to be formatted out with the utility db2inspf. The results file will have file extension of the database partition number.
In a partitioned database environment, each database partition will generate its own results output file with extension corresponding to its database partition number. The output location for the results output file will be the database manager diagnostic data directory path. If the name of a file that already exists is specified, the operation will not be processed, the file will have to be removed before that file name can be specified.
Normal online inspect processing will access database objects using isolation level uncommitted read. Inserting a compression dictionary into the table will attempt to acquire write locks. Please refer to the ROWCOMPESTIMATE option for details on dictionary insert locking. Commit processing will be done during the inspect processing. It is advisable to end the unit of work by issuing a COMMIT or ROLLBACK before starting the inspect operation.
The INDEXDATA option only examines the logical inconsistency between index and data. Therefore, it is recommended that you first run INDEX and DATA checking separately, to rule out any physical corruption, before running INDEXDATA checking.
The INSPECT command, specified with the INDEXDATA parameter, performs an index to data consistency check while allowing read/write access to all objects/tables, even the one being inspected at the moment. The INSPECT INDEXDATA option includes the following inspections:
- the existence of the data row for a given index entry.
- a key to data value verification.
When the INDEXDATA option is specified:
- By default, only the values of explicitly specified level clause options will be used. For any level clause options which are not explicitly specified, the default levels will be overwritten from NORMAL to NONE. For instance, when INDEXDATA is the only level clause option specified, by default, only index to data checking will be performed.
The BLOCKMAP option returns information that includes whether a block has been reclaimed for use by the table space following a reorganization to reclaim multidimensional clustering (MDC) or insert time clustering (ITC) table blocks that were empty.
If the INSPECT command completes with resource errors or limitations, E.g. system is out of memory, you should try to run it again after the resource errors are fixed or limitations lifted.
The INSPECT command is designed to run simultaneously with other utilities such as REORG. Concurrency is managed through acquisition of various locks such as table space locks, table locks, row locks, etc. Deadlocks are generally not expected to occur; however, lock time-outs may be encountered depending on the workload and the database configuration. Also note that the lock manager's deadlock detection will handle deadlocks in the event they occur and will choose a deadlock victim arbitrarily.