Sample CHECK DATA control statements

Use sample control statements as models for developing your own CHECK DATA control statements.

Example 1: Copying violations into exception tables

The control statement specifies that the CHECK DATA utility is to check for and delete any rows that violate referential and table check constraints in table spaces DSN8D10A.DSN8S10D and DSN8D10A.DSN8S10E. CHECK DATA copies any rows that violate these constraints into the exception tables that are specified in the FOR EXCEPTION clause. For example, CHECK DATA is to copy the violations in table DSN8810.DEPT into table DSN8810.EDEPT.

//STEP1    EXEC DSNUPROC,UID='IUIQU1UQ.CHK1',
//         UTPROC='',
//         SYSTEM='DSN'
//SYSUT1   DD DSN=IUIQU1UQ.CHK3.STEP1.SYSUT1,DISP=(MOD,DELETE,CATLG),
//         UNIT=SYSDA,SPACE=(8000,(200,20),,,ROUND)
//SYSERR   DD DSN=IUIQU1UQ.CHK3.SYSERR,DISP=(MOD,DELETE,CATLG),
//         UNIT=SYSDA,SPACE=(6000,(20,20),,,ROUND)
//SORTOUT  DD DSN=IUIQU1UQ.CHK3.STEP1.SORTOUT,DISP=(MOD,DELETE,CATLG),
//         UNIT=SYSDA,SPACE=(6000,(20,20),,,ROUND)
//SYSIN    DD *
CHECK DATA TABLESPACE DSN8D10A.DSN8S10D
           TABLESPACE DSN8D10A.DSN8S10E
     FOR EXCEPTION IN DSN8A10.DEPT        USE DSN8A10.EDEPT
                   IN DSN8A10.EMP         USE DSN8A10.EEMP
                   IN DSN8A10.PROJ        USE DSN8A10.EPROJ
                   IN DSN8A10.PROJACT     USE DSN8A10.EPROJACT
                   IN DSN8A10.EMPPROJACT  USE DSN8A10.EEPA
     DELETE YES
//*

Example 2: Creating an exception table for the project activity table

You can create an exception table for the project activity table by using the following SQL statements:

Begin general-use programming interface information.

EXEC SQL
CREATE TABLE EPROJACT
   LIKE DSN8A10.PROJACT
   IN DATABASE DSN8D10A 
ENDEXEC
 
EXEC SQL
ALTER TABLE EPROJACT
   ADD RID CHAR(4)
ENDEXEC
 
EXEC SQL
ALTER TABLE EPROJACT
   ADD TIME TIMESTAMP NOT NULL WITH DEFAULT
ENDEXEC

End general-use programming interface information.

The first statement requires the SELECT privilege on table DSN8A10.PROJACT and the privileges that are usually required to create a table.

Table EPROJACT has the same structure as table DSN8A10.PROJACT, but it can have two extra columns. The columns in EPROJACT are:

Its first five columns mimic the columns of the project activity table; they have exactly the same names and descriptions. Although the column names are the same, they do not need to be. However, the rest of the column attributes for the initial columns must be same as those of the table that is being checked.
The next column, which is added by ALTER TABLE, is optional; CHECK DATA uses it as an identifier. The name "RID" is an arbitrary choice; if the table already has a column with that name, use a different name. The column description, CHAR(4), is required.
The final timestamp column is also optional. If you define the timestamp column, a row identifier (RID) column must precede this column. You might define a permanent exception table for each table that is subject to referential or table check constraints. You can define it once and use it to hold invalid rows that CHECK DATA detects. The TIME column allows you to identify rows that were added by the most recent run of the utility.

Eventually, you correct the data in the exception tables, perhaps with an SQL UPDATE statement, and transfer the corrections to the original tables by using statements that are similar to those in the following example:

INSERT INTO DSN8A10.PROJACT
   SELECT PROJNO, ACTNO, ACSTAFF, ACSTDATE, ACENDATE
     FROM EPROJACT
     WHERE TIME > CURRENT TIMESTAMP - 1 DAY;

Example 3: Running CHECK DATA on a table space with LOBs

Assume that table space DBIQUQ01.TPIQU01 contains LOB columns. In the following control statement, the SCOPE ALL option indicates that CHECK DATA is to check all rows in all dependent tables in table space DBIQUQ01.TPIQU01 for the following violations:

Violations of referential constraints
Violations of table check constraints
Inconsistencies between the base table space and the corresponding LOB table space.

The AUXERROR INVALIDATE option indicates that if the CHECK DATA utility finds a LOB column error in this table space, it is to perform the following actions:

Issues a warning message
Sets the base table LOB column to an invalid status
Sets the base table to auxiliary warning (AUXW) status

//STEP11   EXEC DSNUPROC,UID='IUIQU1UQ.CHK2',                           
//         UTPROC='',                                                   
//         SYSTEM='SSTR'                                                
//SYSUT1   DD DSN=IUIQU1UQ.CHK2.STEP5.SYSUT1,DISP=(MOD,DELETE,CATLG),   
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                      
//SORTOUT  DD DSN=IUIQU1UQ.CHK2.STEP5.SORTOUT,DISP=(MOD,DELETE,CATLG),  
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                      
//SYSERR   DD DSN=IUIQU1UQ.CHK2.SYSERR,DISP=(MOD,DELETE,CATLG),         
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                      
//SYSIN    DD *                                                         
   CHECK DATA TABLESPACE DBIQUQ01.TPIQUQ01 SCOPE ALL                    
         AUXERROR INVALIDATE                                            
/*

Example 4: Specifying the maximum number of exceptions

The control statement specifies that the CHECK DATA utility is to check all rows in partition number 254 in table space DBNC0216.TPNC0216. The EXCEPTIONS 1 option indicates that the utility is to terminate when it finds one exception. Any exceptions are to be reported by messages only.

//CKDATA   EXEC DSNUPROC,UID='L450TST3.CHECK',                           
//         UTPROC='',                                                    
//         SYSTEM='SSTR'                                                 
//SYSERR   DD DSN=L450TST3.CHECK.STEP1.SYSERR,DISP=(MOD,DELETE,CATLG),   
//         UNIT=SYSDA,SPACE=(2000,(20,20),,,ROUND)                       
//SYSUT1   DD DSN=L450TST3.CHECK.STEP1.SYSUT1,DISP=(MOD,DELETE,CATLG),   
//         UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)                       
//SORTOUT  DD DSN=L450TST3.CHECK.STEP1.SORTOUT,                          
//         DISP=(MOD,DELETE,CATLG),UNIT=SYSDA,                           
//         SPACE=(4000,(20,20),,,ROUND)                                  
//SYSIN    DD *                                                          
    CHECK DATA TABLESPACE DBNC0216.TPNC0216 PART 254                     
             SCOPE ALL EXCEPTIONS 1                                      
/*

Example 5: Running CHECK DATA SHRLEVEL CHANGE

The control statement specifies that the CHECK DATA utility is to specifies that applications can read from and write to the table space that is to be checked.

CHECK DATA TABLESPACE DBNI0101.TSNI010P SHRLEVEL CHANGE

Example 6: Checking several table spaces

To check several table spaces, you can specify more than one table space in a CHECK DATA control statement. This technique is useful for checking a complete set of referentially related table spaces. The following example shows a CHECK DATA control statement that lists more than one table space.

CHECK DATA
     TABLESPACE DBJM1203.TLJM1203
      TABLESPACE DBJM1203.TPJM1204
        FOR EXCEPTION IN TLJM1203.TBJM1203 USE ADMF001.EXCPT3 
           IN TPJM1204.TMBJM1204 USE ADMF001.EXCPT4
    DELETE YES

Example 7: Checking XML columns

The control statement specifies how to include consistency checking of XML columns in a base table with the associated node ID indexes. Specify XMLSCHEMAONLY with INCLUDE XML TABLESPACES to limit the CHECK DATA scope to only XML schema validation for the XML columns.

CHECK DATA TABLESPACE DBNI0101.TSNI010P INCLUDE XML TABLESPACES
					SCOPE XMLSCHEMAONLY AUXONLY

Example 8: Running CHECK DATA on a clone table

The control statement specifies that the CHECK DATA utility is to check the clone table in the specified table space.

CHECK DATA TABLESPACE DBNI0101.TSNI010P CLONE SCOPE ALL  
        ERRDDN SYSERR