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
- 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--'
.-COL NORMAL----. .-BLOCKMAP NORMAL----.
>--+---------------+--+--------------------+-------------------->
'-COL--+-NONE-+-' '-BLOCKMAP--+-NONE-+-'
'-LOW--' '-LOW--'
.-INDEX NORMAL----. .-LONG NORMAL----. .-LOB NORMAL----.
>--+-----------------+--+----------------+--+---------------+--->
'-INDEX--+-NONE-+-' '-LONG--+-NONE-+-' '-LOB--+-NONE-+-'
'-LOW--' '-LOW--' '-LOW--'
.-XML NORMAL----.
>--+---------------+--| Cross Object Checking Clause |----------|
'-XML--+-NONE-+-'
'-LOW--'
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-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.