DB2 Version 9.7 for Linux, UNIX, and Windows

INSPECT command

Inspect 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:

sysadm
dbadm
sysctrl
sysmaint
CONTROL privilege if single table.

Required Connection

Database

Command Syntax


>>-INSPECT--+-| Check Clause |--------------------+------------->
            '-| Row Compression Estimate Clause |-'   

                            .-LIMIT ERROR TO DEFAULT--.   
>--+---------------------+--+-------------------------+--------->
   '-FOR ERROR STATE ALL-'  '-LIMIT ERROR TO--+-n---+-'   
                                              '-ALL-'     

>--+------------------+--RESULTS--+------+--filename------------>
   '-| Level Clause |-'           '-KEEP-'             

>--+----------------------------------+------------------------><
   '-| On Database Partition Clause |-'   

Check Clause

|--CHECK-------------------------------------------------------->

>----+-+-DATABASE--+-------------------------------------+------------+-+----|
     | |           '-BEGIN TBSPACEID--n--+-------------+-'            | |     
     | |                                 '-OBJECTID--n-'              | |     
     | '-TABLESPACE--+-NAME--tablespace-name-+--+-------------------+-' |     
     |               '-TBSPACEID--n----------'  '-BEGIN OBJECTID--n-'   |     
     '-TABLE--+-NAME--table-name--+---------------------+-+-------------'     
              |                   '-SCHEMA--schema-name-' |                   
              '-TBSPACEID--n--OBJECTID--n-----------------'                   

Row Compression Estimate Clause

|--ROWCOMPESTIMATE-TABLE---------------------------------------->

>--+-NAME--table-name--+---------------------+-+----------------|
   |                   '-SCHEMA--schema-name-' |   
   '-TBSPACEID--n--OBJECTID--n-----------------'   

Level Clause

   .-EXTENTMAP NORMAL----.  .-DATA NORMAL----.   
|--+---------------------+--+----------------+------------------>
   '-EXTENTMAP--+-NONE-+-'  '-DATA--+-NONE-+-'   
                '-LOW--'            '-LOW--'     

   .-BLOCKMAP NORMAL----.  .-INDEX NORMAL----.   
>--+--------------------+--+-----------------+------------------>
   '-BLOCKMAP--+-NONE-+-'  '-INDEX--+-NONE-+-'   
               '-LOW--'             '-LOW--'     

   .-LONG NORMAL----.  .-LOB NORMAL----.  .-XML NORMAL----.   
>--+----------------+--+---------------+--+---------------+----->
   '-LONG--+-NONE-+-'  '-LOB--+-NONE-+-'  '-XML--+-NONE-+-'   
           '-LOW--'           '-LOW--'           '-LOW--'     

>--| Cross Object Checking Clause |-----------------------------|

Cross Object Checking Clause

|--+-----------+------------------------------------------------|
   '-INDEXDATA-'   

On Database Partition Clause

|--ON----------------------------------------------------------->

>--+-| Database Partition List Clause |----------------------------------+--|
   '-ALL DBPARTITIONNUMS--+--------------------------------------------+-'   
                          '-EXCEPT--| Database Partition List Clause |-'     

Database Partition List Clause

|--+-DBPARTITIONNUM--+------------------------------------------>
   '-DBPARTITIONNUMS-'   

      .-,--------------------------------------------------.      
      V                                                    |      
>--(----db-partition-number1--+--------------------------+-+--)--|
                              '-TO--db-partition-number2-'

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

Estimates the effectiveness of row compression for a table. You can also specify which database partition(s) 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.

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 node 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 node 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

NORMAL: Specifies processing level is normal for extent map. Default.
NONE: Specifies processing level is none for extent map.
LOW: Specifies processing level is low for extent map.

DATA

NORMAL: Specifies processing level is normal for data object. Default.
NONE: Specifies processing level is none for data object.
LOW: Specifies processing level is low for data object.

BLOCKMAP

NORMAL: Specifies processing level is normal for block map object. Default.
NONE: Specifies processing level is none for block map object.
LOW: Specifies processing level is low for block map object.

INDEX

NORMAL: Specifies processing level is normal for index object. Default.
NONE: Specifies processing level is none for index object.
LOW: Specifies processing level is low for index object.

LONG

NORMAL: Specifies processing level is normal for long object. Default.
NONE: Specifies processing level is none for long object.
LOW: Specifies processing level is low for long object.

LOB

NORMAL: Specifies processing level is normal for LOB object. Default.
NONE: Specifies processing level is none for LOB object.
LOW: Specifies processing level is low for LOB object.

XML

NORMAL: Specifies processing level is normal for XML column object. Default. Pages of XML object will be checked for most inconsistencies. Actual XML data will not be inspected.
NONE: Specifies processing level is none for XML column object. XML object will not be inspected at all.
LOW: Specifies processing level is low for XML column object. Pages of XML object will be checked for some inconsistencies. Actual XML data will not be inspected.

INDEXDATA

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

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, please 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) table blocks that were empty.