CONFIRM (Verify Constraint Integrity)

Constraint integrity can be verified by using the CONFIRM function of the ddb Utility (DBUTLTY).
datacom
Constraint integrity can be verified by using the CONFIRM function of the 
Datacom/DB
 Utility (DBUTLTY).
The MUF must be initiated to perform this function.
This function communicates with one MUF. Be aware of the following:
  • Verify that DBUTLTY is communicating with the desired MUF by executing DBUTLTY with the same System Identifier module (DBSIDPR) that is used by the MUF.
  • Verify proper load library concatenation by keeping the System Identifier modules (DBSIDPR) in separate load libraries.
If the z/OS Cross-System Coupling Facility (XCF) is being used, verify that the TOGROUP DBSIDPR parameter is correctly defined. 
The following topics are discussed on this page:
 
 
When to Use CONFIRM
Use the CONFIRM function when one or both of the following conditions indicate that the integrity of the data in a table must be verified:
  • An application receives a return code indicating the table has a CHECK PENDING status.
  • A Directory (CXX) report shows the tables to have a CHECK PENDING status. See Directory (CXX) Reports for an example.
The DBUTLTY functions which can result in a CHECK PENDING status for a table are the LOAD function (for details, see LOAD (Build a Data Area)), the LOAD portion of the REORG function, (for details, see REORG (Parallel BACKUP/LOAD)), and the RECOVERY function (for details, see RECOVERY (Rebuild a Database)).
 You must CONFIRM tables in the following order, from referenced-to-referencing, or parent-to-child, that is to say, the referenced table cannot be in check pending status. For example, if there is a foreign key from a REMARKS table to a LINE_ITEM table on PO_Nbr and Line_Item_Nbr, and the LINE_ITEM table has a foreign key that references the PO table on PO_Nbr, the PO table must be loaded first, then the LINE_ITEM table can be confirmed, then the REMARKS table can be confirmed. Therefore, the confirm order would be: from PO table to LINE_ITEM table to REMARKS table.
Determining Constraints In Effect
How to Use CONFIRM
The MUF must be active when you execute this command. To confirm a table's constraints, execute DBUTLTY with the command:
►►─ CONFIRM DBID=
n
,TABLE=
ttt
─┬───────────────────────┬───────────────────────► └─ ,DELETE= ─┬─ NO ◄ ─┬─┘ └─ YES ──┘ ►─┬──────────────────────┬─┬────────────────────────────┬────────────────────► └─ ,FORCE= ─┬─ NO ◄ ─┬─┘ └─ ,EXCEPT=
authid.tablename
─┘ └─ YES ──┘ ►─┬───────────────────────┬──────────────────────────────────────────────────►◄ └─ ,VERIFY= ─┬─ NO ◄ ─┬─┘ └─ YES ──┘
 
Command
 
  •  
    CONFIRM
    Invokes the function to verify the integrity constraints for a table.
 
Required Keywords
 
  •  
    DBID=
    Identifies the database containing the table.
    •  
      Valid Entries:
      DATACOM-ID of the database containing the table
    •  
      Default Value:
      (No default)
  •  
    ,TABLE=
    Identifies the table to be verified.
    •  
      Valid Entries:
      DATACOM-NAME of the table
    •  
      Default Value:
      (No default)
 
Optional Keywords
 
  •  
    ,DELETE=
    Specifies whether invalid records (rows) in the table are to be deleted.
    •  
      Valid Entries:
      NO or YES
    •  
      Default Value:
      NO
  •  
    ,EXCEPT=
    Specifies an SQL table into which the rows found to violate constraints are inserted. If DELETE=YES is also specified, the records (rows) are saved into this table before they are deleted from the table being confirmed.
    •  
      Valid Entries:
      The name of an existing SQL table whose definition matches that of the table being confirmed. You must specify an AUTHID.
    •  
      Default Value:
      (No default)
  •  
    ,FORCE=
    Allows you to bypass constraint verification and move the table from CHECK PENDING to usable status regardless of the underlying data. Use of this option is recorded in the FORCED OFF field of the HISTORY CHECK PENDING section of the CXX Report. Use of this option is valid only when the user is certain that the data contained in the table conforms to all constraints.
    •  
      Valid Entries:
      NO or YES
    •  
      Default Value:
      NO
  •  
    ,VERIFY=
    Indicates that you want the data to be checked, even if the table is not in CHECK PENDING status.
    For example, you may have executed the CONFIRM function, specifying FORCE=YES which does not check the data, but indicates that you have forced the CONFIRM. When you use the VERIFY option, the system confirms the data, checking for integrity constraints.
    •  
      Valid Entries:
      NO or YES
    •  
      Default Value:
      NO
Example JCL (CONFIRM)
 Use the following as a guide to prepare your JCL. The JCL statements are for example only. Lowercase letters in a statement indicate a value you must supply. Code all statements to your site and installation standards.
//jobname See the previous note and
JCL Requirements
.
// EXEC PGM=DBUTLTY,REGION=2M //STEPLIB See the previous note and
JCL Requirements
.
//SYSIN DD * Command input CONFIRM DBID=30,TABLE=DAT,VERIFY=YES,DELETE=YES,EXCEPT=DB.OPEN /*
Sample Report
Following is a sample report page. For an example report header, see Sample Report Headers.
CONTROL CARD(S) .........1.........2.........3.........4.........5.........6.........7.........8 CONFIRM DBID=30,TABLE=DAT,VERIFY=YES,DELETE=YES,EXCEPT=DB.OPEN FUNCTION=CONFIRM DBID=30 DELETE=YES EXCEPT=DB.OPEN TABLE=DAT VERIFY=YES
This page of the report shows the following:
  • The command exactly as entered.
  • An analysis of keywords encountered and expected. Any errors found are flagged with a note in the left margin.
  • Any messages related to syntax processing.
  • This area can also return an error code related to processing the CONFIRM request. If an error occurs during CONFIRM processing, interpret the error code and the internal return code of 
    Datacom/DB
     error code 94. For more information about the error code, access Messages.