CHECK DATA options
This topic describes the CHECK DATA command options.
CHECK DATA syntax diagrams
CHECK DATA command syntax illustrates the syntax of the CHECK DATA command.
The following diagram provides syntax diagram details. Each detail diagram is numbered to correspond to a preceding section of the CHECK DATA syntax diagram or to another section of the detail.
CHECK DATA syntax diagram detail
CHECK DATA syntax diagram detail
CHECK DATA option descriptions
This section describes each CHECK DATA command option. The options are described in the order in which they appear in the CHECK DATA syntax diagram.
CHECK DATA
CHECK DATA checks the integrity of referential constraints and column conditions that are defined for tables in one or more table spaces.
Value | Description | ||
|---|---|---|---|
TABLESPACE | CHECK DATA TABLESPACE allows you to specify one or more table spaces that you want to check. For any dependent table in the table space or spaces, CHECK PLUS checks the parent table to ensure that a primary key is present for every foreign key in the dependent table. | ||
TABLESPACESET | CHECK DATA TABLESPACESET allows you to check all referential constraint relationships and table check constraints in a relational set. Instead of specifying a list of table spaces, specify only one table space, and CHECK PLUS will identify all other table spaces in the set that contain related tables that need to be checked. You must specify the fully qualified name of the table space. For any table in the named table space that has either foreign keys or primary keys defined, CHECK PLUS checks the parent or dependent tables to ensure that a primary key is present for every foreign key. In addition, if primary or foreign keys are defined for any other table referenced by tables in the named table space, CHECK PLUS performs RI checking throughout the entire relational set.
|
PART
The PART command option is applicable only if you do not specify TABLESPACESET.
The PART option tells CHECK PLUS the partition numbers of the partitioned table spaces to check. You can specify one, some, or all of the partitions either individually or by specifying a range of partitions. If you do not specify PART, CHECK PLUS checks all partitions of the table space.
Specifying partition numbers
Note the information about how to specify partition numbers with the PART option:
- Individual partitions in a list can be in any order (but partitions within a range must be in ascending order).
- You can specify a mixture of individual partitions and ranges of partitions.
- If you specify a partition number more than once, CHECK PLUS ignores any occurrence after the first.
- You can specify integer values from 1 through 4096.
The following example illustrates a valid PART specification for parts 1 through 5, 7, and 10:
PART 10,1:5,7 |
SHRLEVEL
The SHRLEVEL option specifies the level of access that concurrently operating Db2 applications and utilities have to the target spaces.
The following table describes the valid values for the SHRLEVEL option.
Value | Description |
|---|---|
REFERENCE | SHRLEVEL REFERENCE is the default and allows read-only access to the target spaces during check processing. |
CHANGE | SHRLEVEL CHANGE enables read/write access to the table space. CHECK PLUS uses the snapshot-processing features of XBM or SUF to create a consistent image of the data, which CHECK PLUS uses for check processing. You must specify an appropriate XBM subsystem ID with the XBMID installation (XBMID=) or command option . For more information about using the snapshot-processing features of XBM or SUF with CHECK PLUS, see SHRLEVEL CHANGE. |
ZIIP
The ZIIP option tells CHECK PLUS whether to attempt to use IBM System z Integrated Information Processors (zIIPs). CHECK PLUS can use enclave service request blocks (SRBs) to enable zIIP processing automatically while running jobs. Using zIIP processing can reduce the overall CPU time for CHECK PLUS jobs.
Specifying the default
You can specify the default for the ZIIP command option in your installation options module by using the ZIIP installation option (XBMID=). CHECK PLUS was shipped with a default value of ENABLED for this option. The ZIIP command option overrides the default that is in the installation options module.
The following table describes the valid values for the ZIIP option.
Value | Description |
|---|---|
ENABLED | ZIIP ENABLED tells CHECK PLUS to attempt to offload eligible processing to an available zIIP. If the zIIP is busy or not available, normal processing continues on a general-purpose processor. To enable and use zIIP processing with CHECK PLUS, you must:
|
DISABLED | ZIIP DISABLED tells CHECK PLUS to not attempt to use zIIP processing. |
Using XBM or SUF
You can specify a particular XBM subsystem to use for zIIP processing by specifying a value for the XBMID installation or command option. For more information, see XBMID=.
XBM and SUF are licensed, installed, and maintained separately from CHECK PLUS. You can use either XBM or SUF, depending on the license that you have obtained:
- License for the full version of the XBM product authorizes you to use all features of XBM.
- License for SUF authorizes you to use only the snapshot and zIIP processing features of XBM.
For more information about XBM and SUF, see the SNAPSHOT UPGRADE FEATURE for DB2 documentation.
XBMID
Specify XBMID to identify the appropriate active XBM subsystem when you use either XBM or SUF with CHECK PLUS. You must specify an XBM subsystem in either of the following cases:
- You are specifying SHRLEVEL CHANGE (which uses the snapshot-processing functionality of XBM or SUF).
- You want to use a specific XBM subsystem for zIIP processing.
The variable ssid (subsystem ID) is the unique identifier that you specified when you installed XBM or SUF. If you are using XBM or SUF in a Db2 data sharing environment, you can use the value of the XBMGROUP parameter in place of the ssid. The XBMGROUP is the name of the cross-system coupling facility (XCF) group that is defined to the XBM subsystem, and its default value is XBMGROUP.
For more information about using the snapshot-processing feature of XBM or SUF with CHECK PLUS, see SHRLEVEL CHANGE. For detailed information about XBM and SUF, see the SNAPSHOT UPGRADE FEATURE for DB2 documentation.
zIIP processing
If you specify an XBM subsystem and ZIIP ENABLED is in effect, CHECK PLUS attempts to use that subsystem to enable zIIP processing. If that subsystem is not available or not at the correct maintenance level, zIIP processing is not enabled.
If you do not specify an XBM subsystem (either here or with the XBMID installation option), CHECK PLUS searches for an XBM subsystem at the appropriate maintenance level to enable zIIP processing.
Specifying the default
You can specify the default for the XBMID command option in your installation options module by using the XBMID installation option (XBMID=). If the XBMID is not assigned in the installation option, you must specify this command option if you specify SHRLEVEL CHANGE.
WTOMSG
The WTOMSG command option is applicable only when executing SHRLEVEL CHANGE.
Specify WTOMSG SUFSTART to write message BMC50008I to the MVS system log to indicate that snapshot-processing initialization for CHECK PLUS has successfully completed. You can use the text of this message to trigger the submission of jobs that you want to run concurrently with the CHECK PLUS job.
Specify a text string of up to 50 characters enclosed in single quotes to indicate the message that you want CHECK PLUS to print in the MVS system log. CHECK PLUS truncates strings that are greater than 50 characters. Quotes cannot appear within the text string.
CONSTRAINT
The CONSTRAINT option tells CHECK PLUS the name of the referential constraint or table check constraint to check.
You can specify one or more of the constraints for a table space or table space set. If you do not specify CONSTRAINT, CHECK PLUS checks all constraints.
SCOPE
The SCOPE option allows you to limit the scope of rows to be checked within a table or table space.
The following table describes the valid values for the SCOPE option.
Value | Description |
|---|---|
PENDING | PENDING is the default. This option tells CHECK PLUS to check only those tables, partitions, or table spaces that are marked in CHKP status. CHECK PLUS starts checking rows as indicated by the CHECKRID value in the SYSIBM.SYSTABLES and SYSIBM.SYSTABLEPART tables in the Db2 catalog. Restrictions
|
ALL | ALL tells CHECK PLUS to check all rows of all specified tables. Specify SCOPE ALL to perform complete checking for user-defined RI constraints. See the note in the previous discussion of SCOPE PENDING for more information. |
RULES
The RULES option allows you to define RI and column condition rules for CHECK PLUS to verify. If you do not specify the RULES option (and have not previously saved any user-defined rules with the SAVERULES option), CHECK PLUS checks only for RI constraints and table check constraints that are defined within Db2.
If you have RI defined within Db2 and you also specify the RULES option (or have saved user-defined RI rules previously), CHECK PLUS checks for Db2-defined RI constraints and user-defined RI rules simultaneously.
If no Db2-defined RI or table check constraints exist and no user-defined RI rules have been saved previously, CHECK PLUS checks only the RI constraints specified in the RULES option. You must specify SCOPE ALL to have CHECK PLUS check user-defined RI.
FOR TABLE
FOR TABLE indicates the table to which the user-defined rules apply.
Restriction
CHECK PLUS does not support Unicode table names.
Value | Description |
|---|---|
primary key block | Specify the primary key by using the PRIMARY KEY syntax of the CREATE TABLE SQL statement. Refer to the IBM Db2 SQL Reference manual for details about how to use this statement. |
referential constraint block | Specify a foreign key and the table that it references by using the referential constraint syntax of the CREATE TABLE SQL statement. You cannot specify the ON DELETE option. CHECK PLUS supports all other options and parameters (constraint name, column name, REFERENCES table name). Refer to the IBM Db2 SQL Reference manual for specification details. |
VERIFY | This description of VERIFY applies to the CHECK TABLESPACE and CHECK INDEX options as well as to the CHECK DATA option. VERIFY allows you to specify the condition that must be true for a column value to be valid. A condition can be a simple predicate or a combination of predicates and conditions. (See predicate block for details.) CHECK PLUS verifies that values in specified columns are within the domain established by the condition criteria. The VERIFY option is similar to table check constraints, with the following exceptions:
The VERIFY option does not allow column names on the right side of an expression because column verifications are specified at run time of the CHECK command and are not known to Db2. In contrast, table check constraints are specified when the table space is created or altered; because they are known to Db2, they can have a column specified on the right side of the expression. The following additional restrictions apply:
|
SAVERULES
The SAVERULES option allows you to save or not save the RI rules that you defined in the primary key and referential constraint specifications.
The following table describes the valid values for the SAVERULES option.
Value | Description |
|---|---|
NO | (Default) The RI rules that you specified are not saved in the CHECK PLUS database. |
YES | The specified RI rules are saved in the CHECK PLUS database. CHECK PLUS automatically uses these user-defined RI rules in subsequent executions. For more information about the rules tables in the CHECK PLUS database, see CHECK PLUS RI rules tables. This option saves user-defined RI rules only. CHECK PLUS does not save column condition rules that are specified in the VERIFY option. |
VERIFYONLY
The VERIFYONLY command option is applicable only if you specify the VERIFY condition or if table check constraints exist.
This option allows you to check column-level conditions (table check constraints or rules specified with the RULES option) without page checking. See VERIFY for a detailed description of the VERIFY option.
EXCEPTIONS
The EXCEPTIONS option tells CHECK PLUS to terminate processing when the specified number of constraint violations is exceeded. The default value of 0 indicates that there is no report limit on the number of errors. CHECK PLUS applies duplicate errors to this count.
DISCARDDN
The DISCARDDN option allows you to override the default ddname (SYSDISC) of the discard data set that contains records of RI violations. See Building and executing CHECK PLUS jobs for specification guidelines and detailed information about the use of the SYSDISC data set.
If you use the default name SYSDISC in your JCL, you do not need to specify the DISCARDDN option. However, if you want to use a ddname other than SYSDISC, you must specify it both in this option and in the JCL.
DISCARDFMT
The DISCARDFMT option allows you to specify the format of the foreign keys in RI violation that CHECK PLUS writes to the discard data set.
If you plan to use the ACKESQL program to execute your DELETE statements, we recommend that you specify DISCARDFMT SQLDEL. To use the CHKP status reset and table lock capabilities of the ACKESQL program, you must specify DISCARDFMT SQLDEL. For more information about the ACKESQL program, see SQL-batch-execution-program
The following table describes the valid values for the DISCARDFMT option.
Value | Description |
|---|---|
SQLDEL | (Default) DISCARDFMT SQLDEL produces one SQL DELETE statement per distinct foreign key in violation. For rows in violation that have the same foreign key value, CHECK PLUS creates only one DELETE statement. The record length is dictated by the length of the longest possible DELETE statement. You can later issue the DELETE statements in the SYSDISC discard data set to delete the rows that violate referential constraints. For information about using the CHECK PLUS provided program (ACKESQL) to execute the SQL DELETE statements in this data set, see SQL-batch-execution-program |
SPUFI | DISCARDFMT SPUFI produces one SQL DELETE statement per distinct foreign key in violation. For rows in violation that have the same foreign key value, CHECK PLUS creates only one DELETE statement. The record length is always 80 bytes and only the first 72 bytes are used. This fixed record length allows you to later use SPUFI, QMF, DSNTEP2, or DSNTIAD to execute the DELETE statements in the SYSDISC discard data set to delete the rows that violate referential constraints. For information about using the CHECK PLUS-provided program (ACKESQL) to execute the SQL DELETE statements in this data set, see SQL-batch-execution-program |
KEY | DISCARDFMT KEY produces the key for each row with an RI violation, including duplicate violations. You can later use this information as input into user-written programs to make other appropriate corrections. |
DISCARDS
DISCARDS instructs CHECK PLUS to stop writing to the discard data set when a specified number of distinct foreign keys in RI violation are written to the data set.
The default value of 0 indicates that there is no limit on the number of records that can be written to the discard data set. The record count includes only duplicate errors in a KEY-formatted data set.
DISCARDSERIALIZE
DISCARDSERIALIZE tells CHECK PLUS whether to create serialization header records in the discard data set during check processing. These header records are used by the ACKESQL program, which is described in SQL-batch-execution-program.
Specifying the default
You can specify the default for the DISCARDSERIALIZE command option in your installation options module by using the DSER installation option (DSER=NO). CHECK PLUS was shipped with a value of NO; however, the value might have been changed during installation. The command option overrides the default that is in the installation options module.
The following table describes the valid values for the DISCARDSERIALIZE option.
Value | Description |
|---|---|
NO | NO tells CHECK PLUS not to create the serialization header records. |
YES | YES tells CHECK PLUS to create the serialization header records. Specify YES if you plan to use the ACKESQL program to reset CHKP status, lock affected tables, or do both during ACKESQL execution. For more information, see SQL-batch-execution-program |
Restriction
This option is valid only when you specify DISCARDFMT SQLDEL.
CHECKTSLEVEL
CHECKTSLEVEL specifies which table space checks, if any, that you want CHECK PLUS to perform in addition to the RI and table check constraint checking. For detailed information about specific fields and values checked, see Fields-and-values-checked-in-tables-and-indexes.
The following table describes the valid values for the DRAIN_WAIT option.
Value | Description |
|---|---|
CHECKTSLEVEL 1 | (Default) Performs the checks as described in the CHECKTSLEVEL 1 option of the CHECK TABLESPACE command on CHECKTSLEVEL. However, CHECK PLUS performs these checks only on the table space pages that it reads to perform RI or table check constraint checking. Specifying this option does not ensure the integrity of entire table spaces. If you need to check entire table space structures, run CHECK PLUS with the CHECK TABLESPACE command. |
CHECKTSLEVEL 2 | Performs all CHECKTSLEVEL 1 checks plus the checks as described in the CHECKTSLEVEL 2 option of the CHECK TABLESPACE command on CHECKTSLEVEL. However, CHECK PLUS performs these checks only on the table space pages that it reads to perform RI or table check constraint checking. Specifying this option does not ensure the integrity of entire table spaces. If you need to check entire table space structures, run CHECK PLUS with the CHECK TABLESPACE command. |
CHECKTSLEVEL 0 | Performs no checks on table space pages other than those normally performed when CHECK PLUS reads rows from the table space. |
CHECKIXLEVEL
CHECKIXLEVEL specifies which index checks, if any, that you want CHECK PLUS to perform in addition to the RI or table check constraint checking. For detailed information about specific fields and values checked, see Fields-and-values-checked-in-tables-and-indexes.
You cannot perform CHECKIXLEVEL 2 checking while running the CHECK DATA command.
- CHECKIXLEVEL 1 is the default and performs the checks as described in the CHECKIXLEVEL 1 option of the CHECK INDEX command on CHECKIXLEVEL. However, CHECK PLUS performs these checks only on the index pages that CHECK PLUS reads to perform the RI and table check constraints checking.
Specifying this option does not ensure the integrity of entire index structures. If you need to check entire index structures, run CHECK PLUS with the CHECK INDEX command. - CHECKIXLEVEL 0 performs no checks on index pages other than those normally performed when CHECK PLUS reads index entries from the index.
ERRDDN
This option allows you to override the default ddname (SYSERR) for the error data set used for error processing. See Building-CHECK-PLUS-jobs for specification guidelines and detailed information about the use of the ERRDDN data set.
If you use the default name SYSERR in your JCL, you do not need to specify the ERRDDN option. However, if you want to use a ddname other than SYSERR, you must specify it both in this option and in the JCL.
SORTDEVT
Use this option to specify the device type for the sort work files that BMCSORT will allocate dynamically. This option overrides the value specified in the BMCSORT DYNALOC installation option.
If the value of the third parameter in the BMCSORT DYNALOC installation option is OFF, specifying the SORTDEVT option turns BMCSORT dynamic allocation on.
SORTNUM
The SORTNUM option affects the allocation of sort work files when BMCSORT is allocating your sort work files dynamically. You can specify an integer value of 0 through 255.
The following table describes the action that BMCSORT takes for each value that you can specify for this option. The table also provides any additional considerations for these values.
Value | Description | Additional considerations |
|---|---|---|
0 | BMCSORT honors the value of the third parameter of the BMCSORT DYNALOC installation option. (This parameter tells BMCSORT whether to dynamically allocate sort work files.) | For more information about this parameter, see DYNALOC-installation-option. |
1–32 | BMCSORT dynamically allocates the number of sort work files that it needs, up to 32 minus any sort work files that are allocated in your JCL. This number is per sort task. | None |
33–255 | BMCSORT dynamically allocates the number of sort work files that it needs, up to the number that you specify minus any sort work files that are allocated in your JCL. This number is per sort task. | None |
Specifying a value greater than 0 for the SORTNUM option also produces the following results:
- If the value of the third parameter in the BMCSORT DYNALOC installation option is OFF, specifying a value greater than 0 for the SORTNUM option turns BMCSORT dynamic allocation on, and BMCSORT allocates sort work files as needed.
- This option provides the information that lets CHECK PLUS run multiple concurrent sort tasks when applicable. If you do not specify a value greater than 0 for this option and BMCSORT is allocating your sort work files, CHECK PLUS uses a single sort task.
The sort work files that BMCSORT allocates dynamically might be used in one of the following circumstances:
- When you do not specify any SORTWK DD statements in your JCL.
- When BMCSORT determines that there is a need for additional data sets (in addition to any sort work data sets that you specify in your JCL).
Specifying the default
You can specify the default for the SORTNUM command option in your installation options module by using the SORTNUM installation option (SORTNUM=32). CHECK PLUS was shipped with a value of 32; however, the value might have been changed during installation. The command option overrides the default installation options module.
MAXTASKS
The MAXTASKS option specifies the maximum number of sort tasks that CHECK PLUS can run concurrently. Like other BMC utilities for Db2, CHECK PLUS optimizes elapsed time by using all available resources. However, if you need to provide greater control over allocated resources, the MAXTASKS option allows you to restrict the number of concurrent tasks.
Restricting the number of concurrent sorting tasks is important because sorting can consume a significant amount of system resources. Sorting is performed for all CHECK DATA utility executions.
Specifying the default
You can specify the default for the MAXTASKS command option in your installation options module by using the SMAX installation option (SMAX=16). CHECK PLUS was shipped with a default value of 16 for this option; however, the value might have been changed during installation. The command option overrides the default that is in the installation options module.
DRAIN_WAIT
The DRAIN_WAIT option specifies the drain timeout value that you want CHECK PLUS to use.
Specifying the default
You can specify the default for the DRAIN_WAIT command option in your installation options module by using the DRNWAIT installation option. CHECK PLUS was shipped with a default value of NONE for this option; however, the value might have been changed during installation. The command option overrides the default that is in the installation options module.
The following table describes the valid values for the DRAIN_WAIT option.
Value | Description |
|---|---|
NONE | If you specify NONE, the drain request issued by CHECK PLUS times out immediately if the drain cannot acquire the lock. NONE prevents any application transactions from being queued during the drain process. We recommend that you specify NONE in high-transaction environments, such as SAP. |
UTIL | UTIL tells CHECK PLUS to use the standard DB2 utility timeout value defined in DSNZPARMS for your site (IRLMRWT multiplied by UTIMOUT). The wait time applies to each object involved in the check. |
SQL | If you specify SQL, CHECK PLUS uses the standard SQL timeout value (IRLMRWT) as the drain timeout value. The wait time applies to each object involved in the check. If it cannot drain all of the objects within the time period specified by DRAIN_WAIT, CHECK PLUS releases the drains it has obtained so far, waits the time that you specified in RETRY_DELAY, and tries to drain the objects again for the number of times that you specified in RETRY. |
RETRY
The RETRY option specifies the maximum number of times that CHECK PLUS attempts to obtain a drain before the job terminates. The number of attempts can range from 0 through 255.
Specifying the default
You can specify the default for the RETRY command option in your installation options module by using the DRNRETRY installation option. CHECK PLUS was shipped with a default value of 255 for this option; however, the value might have been changed during installation. The command option overrides the default that is in the installation options module.
RETRY_DELAY
After a drain times out, the RETRY_DELAY option specifies the minimum number of seconds that CHECK PLUS waits before it tries again to obtain the drain. The number of seconds can range from 1 through 1800.
Specifying the default
You can specify the default for the RETRY_DELAY command option in your installation options module by using the DRNDELAY installation option. CHECK PLUS was shipped with a default value of 1 for this option; however, the value might have been changed during installation. The command option overrides the default that is in the installation options module.
DSPLOCKS
The DSPLOCKS option tells CHECK PLUS what action to take regarding displaying claims and locks if a drain attempt times out.
Specifying the default
You can specify the default for the DSPLOCKS command option in your installation options module by using the DSPLOCKS installation option (DSPLOCKS=DRNFAIL). CHECK PLUS was shipped with a default value of DRNFAIL for this option; however, the value might have been changed during installation. The command option overrides the default that is in the installation options module.
The following table describes the valid values for the DSPLOCKS option.
Value | Description |
|---|---|
DRNFAIL | If you specify DRNFAIL, CHECK PLUS displays the claims and locks once, after the final attempt to obtain the drain times out. |
NONE | If you specify NONE, CHECK PLUS does not display any claims or locks. |
RETRY | If you specify RETRY, CHECK PLUS displays claims and locks after each drain timeout. |
INFORI
INFORI tells CHECK PLUS whether to enforce informational referential integrity (RI).
The following table describes the valid values for the INFORI option.
Value | Description |
|---|---|
NO | (Default) NO tells CHECK PLUS to ignore all informational RI. |
YES | YES tells CHECK PLUS to check data for violations, validate informational RI and other constraints, and generate DELETE statements for violations of informational RI. If violations are found, CHECK PLUS does not set CHECK pending status. |