DB2 Version 9.7 for Linux, UNIX, and Windows

db2Inspect API - Inspect database for architectural integrity

Inspects the database for architectural integrity and checks the pages of the database for page consistency.

Scope

In a single partition database environment, the scope is the single database partition only. In a partitioned database environment it is the collection of all logical database partitions defined in db2nodes.cfg. For partitioned tables, the scope for database and table space level inspection includes individual data partitions and non-partitioned indexes. Table level inspection for a partitioned table checks all the data partitions and indexes in a table, rather than checking a single data partition or index.

Authorization

One of the following:
  • sysadm
  • sysctrl
  • sysmaint
  • dbadm
  • CONTROL privilege on the table

Required connection

Database

API include file

db2ApiDf.h

API and data structure syntax

SQL_API_RC SQL_API_FN
  db2Inspect (
   db2Uint32 versionNumber,
   void * pParmStruct,
   struct sqlca * pSqlca);

typedef SQL_STRUCTURE db2InspectStruct
{
   char *piTablespaceName;
   char *piTableName;
   char *piSchemaName;
   char *piResultsName;
   char *piDataFileName;
   SQL_PDB_NODE_TYPE *piNodeList;
   db2Uint32 iAction;
   db2int32 iTablespaceID;
   db2int32 iObjectID;
   db2Uint32 iFirstPage;
   db2Uint32 iNumberOfPages;
   db2Uint32 iFormatType;
   db2Uint32 iOptions;
   db2Uint32 iBeginCheckOption;
   db2int32 iLimitErrorReported;
   db2Uint16 iObjectErrorState;
   db2Uint16 iCatalogToTablespace;
   db2Uint16 iKeepResultfile;
   db2Uint16 iAllNodeFlag;
   db2Uint16 iNumNodes;
   db2Uint16 iLevelObjectData;
   db2Uint16 iLevelObjectIndex;
   db2Uint16 iLevelObjectLong;
   db2Uint16 iLevelObjectLOB;
   db2Uint16 iLevelObjectBlkMap;
   db2Uint16 iLevelExtentMap;
   db2Uint16 iLevelObjectXML;
   db2Uint32 iLevelCrossObject;
} db2InspectStruct;

SQL_API_RC SQL_API_FN
  db2gInspect (
   db2Uint32 versionNumber,
   void * pParmStruct,
   struct sqlca * pSqlca);

typedef SQL_STRUCTURE db2gInspectStruct
{
   char *piTablespaceName;
   char *piTableName;
   char *piSchemaName;
   char *piResultsName;
   char *piDataFileName;
   SQL_PDB_NODE_TYPE *piNodeList;
   db2Uint32 iResultsNameLength;
   db2Uint32 iDataFileNameLength;
   db2Uint32 iTablespaceNameLength;
   db2Uint32 iTableNameLength;
   db2Uint32 iSchemaNameLength;
   db2Uint32 iAction;
   db2int32 iTablespaceID;
   db2int32 iObjectID;
   db2Uint32 iFirstPage;
   db2Uint32 iNumberOfPages;
   db2Uint32 iFormatType;
   db2Uint32 iOptions;
   db2Uint32 iBeginCheckOption;
   db2int32 iLimitErrorReported;
   db2Uint16 iObjectErrorState;
   db2Uint16 iCatalogToTablespace;
   db2Uint16 iKeepResultfile;
   db2Uint16 iAllNodeFlag;
   db2Uint16 iNumNodes;
   db2Uint16 iLevelObjectData;
   db2Uint16 iLevelObjectIndex;
   db2Uint16 iLevelObjectLong;
   db2Uint16 iLevelObjectLOB;
   db2Uint16 iLevelObjectBlkMap;
   db2Uint16 iLevelExtentMap;
   db2Uint16 iLevelObjectXML;
   db2Uint32 iLevelCrossObject;
} db2gInspectStruct;

db2Inspect API parameters

versionNumber
Input. Specifies the version and release level of the structure passed as the second parameter pParmStruct.
pParmStruct
Input. A pointer to the db2InspectStruct structure.
pSqlca
Output. A pointer to the sqlca structure.

db2InspectStruct data structure parameters

piTablespaceName
Input. A string containing the table space name. The table space must be identified for operations on a table space. If the pointer is NULL, the table space ID value is used as input.
piTableName
Input. A string containing the table name. The table must be identified for operations on a table or a table object. If the pointer is NULL, the table space ID and table object ID values are used as input.
piSchemaName
Input. A string containing the schema name.
piResultsName
Input. A string containing the name for results output file. This input must be provided. The file will be written out to the diagnostic data directory path.
piDataFileName
Input. Reserved for future use. Must be set to NULL.
piNodeList
Input. A pointer to an array of database partition numbers on which to perform the operation.
iAction
Input. Specifies the inspect action. Valid values (defined in the db2ApiDf header file, which is located in the include directory) are:
DB2INSPECT_ACT_CHECK_DB
Inspect the entire database.
DB2INSPECT_ACT_CHECK_TABSPACE
Inspect a table space.
DB2INSPECT_ACT_CHECK_TABLE
Inspect a table.
DB2INSPECT_ACT_FORMAT_XML
Format an XML object page.
DB2INSPECT_ACT_ROWCMPEST_TBL
Estimate row compression effectiveness on a table.
iTablespaceID
Input. Specifies the table space ID. If the table space must be identified, the table space ID value is used as input if the pointer to table space name is NULL.
iObjectID
Input. Specifies the object ID. If the table must be identified, the object ID value is used as input if the pointer to table name is NULL.
iBeginCheckOption
Input. Option for check database or check table space operation to indicate where operation should begin. It must be set to zero to begin from the normal start. Values are:
DB2INSPECT_BEGIN_TSPID
Use this value for check database to begin with the table space specified by the table space ID field, the table space ID must be set.
DB2INSPECT_BEGIN_TSPID_OBJID
Use this value for check database to begin with the table specified by the table space ID and object ID field. To use this option, the table space ID and object ID must be set.
DB2INSPECT_BEGIN_OBJID
Use this value for check table space to begin with the table specified by the object ID field, the object ID must be set.
iLimitErrorReported
Input. Specifies the reporting limit of the number of pages in error for an object. Specify the number you want to use as the limit value or specify one the following values:
DB2INSPECT_LIMIT_ERROR_DEFAULT
Use this value to specify that the maximum number of pages in error to be reported is the extent size of the object.
DB2INSPECT_LIMIT_ERROR_ALL
Use this value to report all pages in error.

When DB2INSPECT_LVL_XOBJ_INXDAT_RID is used in the iLevelCrossObject field, the limit value specified, or the above DEFAULT or ALL values, represent a limit in the number of errors, instead of number of pages in error, to be reported during the online index to data consistency checking.

iObjectErrorState
Input. Specifies whether to scan objects in error state. Valid values are:
DB2INSPECT_ERROR_STATE_NORMAL
Process object only in normal state.
DB2INSPECT_ERROR_STATE_ALL
Process all objects, including objects in error state.

When DB2INSPECT_LVL_XOBJ_INXDAT_RID is used in the iLevelCrossObject field, as long as the index or data object is in an error state, DB2INSPECT_ERROR_STATE_ALL will be ignored if specified in this field, and the online index to data consistency checking will not be performed.

iKeepResultfile
Input. Specifies result file retention. Valid values are:
DB2INSPECT_RESFILE_CLEANUP
If errors are reported, the result output file will be retained. Otherwise, the result file will be removed at the end of the operation.
DB2INSPECT_RESFILE_KEEP_ALWAYS
The result output file will be retained.
iAllNodeFlag
Input. Indicates whether the operation is to be applied to all nodes defined in db2nodes.cfg. Valid values are:
DB2_NODE_LIST
Apply to all nodes in a node list that is passed in pNodeList.
DB2_ALL_NODES
Apply to all nodes. pNodeList should be NULL. This is the default value.
DB2_ALL_EXCEPT
Apply to all nodes except those in a node list that is passed in pNodeList.
iNumNodes
Input. Specifies the number of nodes in the pNodeList array.
iLevelObjectData
Input. Specifies processing level for data object. Valid values are:
DB2INSPECT_LEVEL_NORMAL
Level is normal.
DB2INSPECT_LEVEL_LOW
Level is low.
DB2INSPECT_LEVEL_NONE
Level is none.
iLevelObjectIndex
Input. Specifies processing level for index object. Valid values are:
DB2INSPECT_LEVEL_NORMAL
Level is normal.
DB2INSPECT_LEVEL_LOW
Level is low.
DB2INSPECT_LEVEL_NONE
Level is none.
iLevelObjectLong
Input. Specifies processing level for long object. Valid values are:
DB2INSPECT_LEVEL_NORMAL
Level is normal.
DB2INSPECT_LEVEL_LOW
Level is low.
DB2INSPECT_LEVEL_NONE
Level is none.
iLevelObjectLOB
Input. Specifies processing level for LOB object. Valid values are:
DB2INSPECT_LEVEL_NORMAL
Level is normal.
DB2INSPECT_LEVEL_LOW
Level is low.
DB2INSPECT_LEVEL_NONE
Level is none.
iLevelObjectBlkMap
Input. Specifies processing level for block map object. Valid values are:
DB2INSPECT_LEVEL_NORMAL
Level is normal.
DB2INSPECT_LEVEL_LOW
Level is low.
DB2INSPECT_LEVEL_NONE
Level is none.
iLevelExtentMap
Input. Specifies processing level for extent map. Valid values (defined in the db2ApiDf header file, which is located in the include directory) are:
DB2INSPECT_LEVEL_NORMAL
Level is normal.
DB2INSPECT_LEVEL_LOW
Level is low.
DB2INSPECT_LEVEL_NONE
Level is none.
iLevelObjectXML
Input. Specifies processing level for XML object. Valid values (defined in the db2ApiDf header file, which is located in the include directory) are:
DB2INSPECT_LEVEL_NORMAL
Level is normal.
DB2INSPECT_LEVEL_LOW
Level is low.
DB2INSPECT_LEVEL_NONE
Level is none.
iLevelCrossObject
A bit-based field used for any cross object consistency checking. Valid values are:
DB2INSPECT_LVL_XOBJ_NONE
Online index data consistency checking will not be performed (0x00000000).
DB2INSPECT_LVL_XOBJ_INXDAT_RID
INDEXDATA checking is enabled on RID index (0x00000001) and will be performed with IS table lock to allow for both readers and writers.

db2gInspectStruct data structure specific parameters

iResultsNameLength
Input. The string length of the results file name.
iDataFileNameLength
Input. The string length of the data output file name.
iTablespaceNameLength
Input. The string length of the table space name.
iTableNameLength
Input. The string length of the table name.
iSchemaNameLength
Input. The string length of the schema name.

Usage notes

The online inspect processing will access database objects using isolation level uncommitted read. Commit processing will be done during the inspect processing. It is advisable to end the unit of work by committing or rolling back changes, by executing a COMMIT or ROLLBACK statement respectively, before starting the inspect operation.

The inspect check processing will write out unformatted inspection data results to the result file. The file will be written out to the diagnostic data directory path. If there are no errors found by the check processing, the result output file will be erased at the end of the inspect operation. If there are errors found by the check processing, the result output file will not be erased at the end of the inspect operation. To see the inspection details, format the inspection result output file with the db2inspf utility.

In a partitioned database environment, the extension of the result output file will correspond to the database partition number. The file is located in the database manager diagnostic data directory path.

A unique results output file name must be specified. If the result output file already exists, the operation will not be processed.

When you call the db2Inspect API, you need to specify iLevelCrossObject in the db2InspectStruct with a proper value. When DB2INSPECT_LVL_XOBJ_NONE is used, online index data consistency checking will not be performed. To enable online index data consistency checking, DB2INSPECT_LVL_XOBJ_INXDAT_RID needs to be specified in the iLevelCrossObject field.

The processing of table spaces will process only the objects that reside in that table space. The exception is during an index data consistency check, when data objects can reside in other table spaces and still benefit from the checking, as long as the index objects are in the table space to be inspected. 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.

Parent topic: DB2 APIs
Related reference:
INSPECT command
SQLCA data structure