VALIDATE

The VALIDATE utility checks linked and unlinked referential constraints for a referencing table, making sure that referenced tables exist and contain the appropriate column values.
idmscu19
The VALIDATE utility checks linked and unlinked referential constraints for a referencing table, making sure that referenced tables exist and contain the appropriate column values.
Type of VALIDATE
What it does
Complete VALIDATE
Runs both STEP1 and STEP2
Stepped VALIDATE
Runs one step at a time with intermediate file sorting required between STEP1 and STEP2
This article describes the following information:
2
2
Authorization
To
You need this privilege
On
Validate a table
SELECT
The table
Validate the tables in an area
SELECT
All tables in the area
Validate the tables in a segment
SELECT
All tables in the segment
Syntax
Syntax for complete VALIDATE
►►─── VALIDATE ───────────────────────────────────────────────────────────────►               ┌───────────────────── , ───────────────────┐  ►─┬─ TABLE ──▼──┬────────────────┬── table-identifier ───┴──┬────────────────►    │             └─ schema-name. ─┘                          │    ├─ AREA segment-name.area-name ───────────────────────────┤    └─ SEGMENT segment-name ──────────────────────────────────┘  ►─┬──────────────────────────────┬───────────────────────────────────────────►    └─ NOTIFY notify-record-count ─┘  ►─┬────────────────────────────┬─────────────────────────────────────────────►◄    └─ ERRORS ─┬─ error-count ─┬─┘               └─ CONTINUE ◄───┘
Syntax for stepped VALIDATE
►►─── VALIDATE ───────────────────────────────────────────────────────────────►                           ┌─────────────────── , ─────────────────┐  ►─┬─────────┬──┬─ TABLE ─▼─┬────────────────┬─ table-identifier ─┴─┬─┬───────►    ├─ STEP1 ─┘  │           └─ schema-name. ─┘                      │ │    │            ├─ AREA segment-name.area-name ─────────────────────┤ │    │            └─ SEGMENT segment-name ────────────────────────────┘ │    └─ STEP2 ──────────────────────────────────────────────────────────┘  ►─┬──────────────────────────────┬───────────────────────────────────────────►    └─ NOTIFY notify-record-count ─┘  ►─┬────────────────────────────┬─────────────────────────────────────────────►◄    └─ ERRORS ─┬─ error-count ─┬─┘               └─ CONTINUE ◄───┘
Only one LOAD, BUILD, or VALIDATE statement can be performed during one execution of the Batch Command Facility (IDMSBCF).
Parameters
  • TABLE
    Identifies the referencing table to validate.
  • schema-name
    Specifies the name of the schema that defines the table.
  • table-identifier
    Specifies the identifier of the table.
  • AREA
    Identifies the area containing tables to be validated. All referencing tables in the area will be validated.
  • segment-name
    Specifies the name of the segment containing the area.
  • area-name
    Specifies the name of the area.
  • SEGMENT
    Identifies the segment containing tables to be validated. All referencing tables in the segment will be validated.
  • segment-name
    Specifies the name of the segment.
  • NOTIFY
    Directs the VALIDATE utility to send a message to the operator whenever a specified number of rows are processed.
    The message states the phase and step currently being executed and the number of records processed.
  • notify-record-count
    Specifies the number of rows to validate before sending a message.
  • ERRORS
    Directs the VALIDATE utility to either continue the validation when errors are detected or stop after a specified number of errors are detected.
    By default, processing will not stop when errors are detected.
    Detected errors are listed in the report generated by the VALIDATE utility.
  • error-count
    Specifies the number of errors to detect before terminating.
  • CONTINUE
    Directs the VALIDATE utility to continue processing regardless of the number of errors detected.
    CONTINUE is the default.
  • STEP1
    Validates only linked referential constraints and unlinked index-to-index referential constraints.
    If other unlinked referential constraints are detected, VALIDATE STEP1 produces an intermediate work file to be used as input to VALIDATE STEP2. If no such file is produced, you do not need to run VALIDATE STEP2.
    If you do not specify a STEP number, the VALIDATE utility will validate all linked and unlinked referential constraints, and is considered a complete VALIDATE.
  • STEP2
    Validates all unlinked referential constraints except index to index referential constraints.
Usage
How to submit the VALIDATE statement
You submit the VALIDATE statement only through the batch command facility. You must run the batch command facility in local mode.
When to use VALIDATE
If you have loaded a group of tables using a phased or stepped LOAD and built the indexes and relationships of the tables specifying NO VALIDATE in the BUILD statement, use VALIDATE to ensure that referencing tables have valid references.
You can also use the VALIDATE utility at any time to validate the referential constraints of a table.
VALIDATE utility uses intermediate work files
STEP1 of the VALIDATE utility produces an intermediate work file to be used by STEP2. If you run a complete VALIDATE without separating STEP1 from STEP2, data is sorted in the intermediate file between the steps automatically. If you run a stepped VALIDATE, you must run the intermediate sorts.
When running a complete VALIDATE, SYS002 and SYS003must point to the
same
intermediate file. When running a stepped VALIDATE, SYS002 and SYS003 must point to
different
intermediate files. The data that is output in SYS003 by STEP1 is input to STEP2 in SYS002.
When not to use VALIDATE
If you loaded the tables with a complete LOAD, or if you did not specify NO VALIDATE in the BUILD statement, then the validation has already been done. There is no need to run the VALIDATE utility.
If the tables have no referential constraints, there is no need to run the VALIDATE utility.
For more information about referential constraints, see the
CA IDMS Database Administration Section
.
JCL Considerations
When you submit a VALIDATE statement through the batch command facility, the JCL to execute the facility must include statements to define the following:
  • Dictionary containing table definitions
  • Files containing the areas associated with the referencing tables to be processed
  • Intermediate work files
  • Sort work files are needed if doing a complete VALIDATE
Sorting intermediate work files
If you run the validate process in steps or phases, use the sort parameters in the SYSPCH file to sort the intermediate files.
The following table shows the output of the steps of the VALIDATE process:
Step
Output
Size
STEP1
SYS003
For each record:
(MAX SORT CONTROL KEY SIZE) + (MAX FOREIGN KEY SIZE) + 24
 
SYSPCH contains sort parameters
80 bytes
STEP2
SYS003
For each record:
(MAX SORT CONTROL KEY SIZE) + (MAX FOREIGN KEY SIZE) + 24
 
SYSPCH contains sort parameters
80 bytes
For more information about the generic JCL used to execute the batch command facility, see the section specific to your operating system.
Example
The following example instructs the VALIDATE utility to perform a validation check against sample tables M and M2. The validation was not performed when the BUILD utility was run against them.
validate table load.m,                load.m2      errors continue;
Sample Output
The following listing was generated after validating sample tables M and M2 in the previous example.
IDMSBCF                                              IDMS Batch Command Facility *DEBUG IDMS OFF CONNECT TO SYSCAT; Status = 0 SET BATCH     HEADINGS OFF WIDTH PAGE 79 UNDERLINE '-'     SQLCODE ERROR     COMPRESS ON; UNLOCK AREA SYSSQL.DDLCAT; Status = 1        Extended Reason Code = 2367     Messages follow: DB002367 C1M353: Area SYSSQL.DDLCAT was not locked. UNLOCK AREA SYSSQL.DDLCATX; Status = 1        Extended Reason Code = 2367     Messages follow: DB002367 C1M353: Area SYSSQL.DDLCATX was not locked. --   ****  Load data into Tables      **** *DEBUG IDMS ON VALIDATE TABLE LOAD.M,                LOAD.M2      ERRORS CONTINUE; IDMSLOAD  -   volser     VALIDATE INDEXES STEP 1     yy-mm-dd-hh.mm.ss IDMSLOAD  -  0 records processed for table LOAD.M IDMSLOAD  -  0 records processed for table LOAD.M2 IDMSLOAD  -  3 intermediate records were written to SYS003 IDMSLOAD  -       largest SYS003 record size is 56 characters IDMSLOAD  -         VALIDATE INDEXES STEP 1     processing completed AutoCommit will COMMIT transaction Command Facility ended with warnings