BUILD

The BUILD utility statement builds indexes and referential constraints linked through an index on tables that are being loaded with a phased or stepped LOAD.
idmscu19
The BUILD utility statement builds indexes and referential constraints linked through an index on tables that are being loaded with a phased or stepped LOAD.
The BUILD utility can also be used to reorganize existing indexes.
The BUILD utility works only on tables in an SQL-defined database.
Type of BUILD
What it does
Complete BUILD
Runs all four steps
Stepped BUILD
Runs one step at a time with intermediate file sorting required between each step
This article describes the following information:
2
2
Authorization
To
You need this privilege
On
Build indexes and/or referential constraints on a table
INSERT
The indexed or referencing table
For more information about designing indexes and referential constraints in SQL-defined databases, see the
Database Design Section
.
For more information about defining indexes and referential constraints in SQL-defined databases, see the
Database Administration Section
.
Syntax
Syntax for complete BUILD
                                            ┌────────────── , ────────────────┐ ►►── BUILD ─┬─────────────────────┬─ FOR ─▼─┬─────────────────┬─ table-id ──┴─►             ├─ INDEXES ─┬───────┬─┤         └── schema-name. ─┘             │           └─ ALL ─┘ │             ├─ CONSTRAINTS ───────┤             └─ BOTH ◄─────────────┘  ►─┬───────────────┬──────────────────────────────────────────────────────────►    └─ NO VALIDATE ─┘  ►─┬──────────────────────────────┬───────────────────────────────────────────►    └─ NOTIFY notify-record-count ─┘  ►─┬────────────────────────────────────────────────┬─────────────────────────►◄    │ ┌────────────────────────────────────────────┐ │    └─▼─┬────────────┬─  ERRORS ─┬───────────────┬─┴─┘        ├─ BUILD ────┤           ├─ error-count ─┤        ├─ VALIDATE ─┤           └─ CONTINUE ────┘        └─ ALL ◄─────┘  
Syntax for stepped BUILD
  ►►─── BUILD ──────────────────────────────────────────────────────────────────►                                        ┌──────────── , ────────────────┐  ►─┬─ STEP1 ───┬───────────────┬─ FOR ─▼─┬────────────────┬─ table-id ─┴─┬────►    │           ├─ INDEXES ─────┤         └─ schema-name. ─┘              │    │           ├─ CONSTRAINTS ─┤                                         │    │           └─ BOTH ◄───────┘                                         │    ├─ STEP2 ─────────────────────────────────────────────────────────────┤    ├─ STEP3 ─────────────────────────────────────────────────────────────┤    └─ STEP4 ─────────────────────────────────────────────────────────────┘  ►─┬──────────────────────────────┬───────────────────────────────────────────►    └─ NOTIFY notify-record-count ─┘  ►─┬─────────────────────────────────────────┬────────────────────────────────►◄    └─┬─────────┬─  ERRORS ─┬───────────────┬─┘      ├─ BUILD ─┤           ├─ error-count ─┤      └─ ALL   ─┘           └─ CONTINUE ────┘  
Only one LOAD, BUILD, or VALIDATE statement can be performed during one execution of the Batch Command Facility (IDMSBCF).
Parameters
  • INDEXES
    Directs the BUILD utility to build the indexes only.
    By default, if you do not specify what is to be built, both indexes and constraints are built.
  • ALL
    Directs the BUILD utility to build all indexes (clustered and non-clustered). If ALL is not specified, only non-clustered indexes will be built.
  • CONSTRAINTS
    Directs the BUILD utility to build referential constraints only.
    By default, if you do not specify what is to be built, both indexes and constraints are built.
  • BOTH
    Directs the BUILD utility to build both indexes and relationships. BOTH is the default.
  • FOR
    Specifies the table for which indexes and/or constraints are to be built.
  • schema-name.
    The name of the schema that defines the table.
  • table-id
    The identifier of the table.
  • NO VALIDATE
    Directs the BUILD utility not to validate referential constraints.
    If you specify NO VALIDATE, you will have to execute the VALIDATE utility before you can use the table(s).
    By default, the validation is performed.
    You can specify NO VALIDATE only for a complete BUILD.
  • NOTIFY
    Directs the BUILD utility to send a message to the operator whenever a specified number of records are processed.
    The message states the phase and step currently being executed and the number of records that have been processed.
  • notify-record-count
    The number of records to process before sending a message.
  • BUILD ERRORS
    When errors are detected, directs the BUILD utility to either continue processing or stop after a specified number of errors are detected.
    By default, processing is stopped after the first error is detected.
    Detected errors are listed in the report generated by the BUILD utility.
  • VALIDATE ERRORS
    When errors are detected in the validation process, directs the BUILD utility to either continue processing or stop processing after a specified number of errors are encountered.
    By default, processing is stopped after the first error is detected.
    Detected errors are listed in the report generated by the BUILD utility.
    You can specify VALIDATE ERRORS only for a complete BUILD.
  • ALL ERRORS
    Directs the BUILD utility to either continue when any errors are detected, or stop after a specified number of errors are detected.
    By default, processing is stopped after the first error is detected.
    Detected errors are listed in the report generated by the BUILD utility.
  • error-count
    The number of errors to detect before stopping.
    If you are doing a complete BUILD, you can specify different values for
    error-count
    for different kinds of errors.
  • CONTINUE
    Indicates that processing should continue regardless of the number of errors detected.
  • STEP
    n
    Directs the BUILD utility to perform only the
    n
    th step of the index or constraint building process.
    By default, if you do not specify a step, all four steps are performed as a single operation, and this is considered a complete BUILD.
  • STEP1
    Directs the BUILD utility to perform only STEP1 of the BUILD process. STEP1 sweeps the area containing the specified table(s), creating an intermediate work file. The file contains the information needed later to build the index structures.
    If you specified the EXTRACT option in STEP1 of the LOAD utility, you do not need to run BUILD STEP1.
    In this case, the intermediate work file (SYS003) that is output from the LOAD utility can be used as the input file (SYS002) to STEP2 of the BUILD utility.
  • STEP2
    Directs the BUILD utility to perform only STEP2 of the BUILD process. STEP2 determines the database key of the referenced table rows.
  • STEP3
    Directs the BUILD utility to perform only STEP3 of the BUILD process. STEP3 creates the index structures needed for both indexes and constraints.
  • STEP4
    Directs the BUILD utility to perform only STEP4 of the BUILD process. STEP4 updates the prefix(es) of the affected referencing table rows.
Usage
How to submit the BUILD statement
You submit the BUILD statement only through the batch command facility. You must run the batch command facility in local mode.
When to use BUILD
Use the BUILD utility after loading one or more tables using a phased or stepped LOAD.
You can also use the BUILD utility at any time to reorganize existing indexes on tables in an SQL-defined database.
When not to use BUILD
There is no need to run the BUILD utility if you loaded the table(s) with a complete LOAD. The indexes and constraints have already been built.
If the table is not part of an SQL-defined database, you cannot use the BUILD utility.
When to specify NO VALIDATE
The BUILD utility validates all referential constraints on the tables being worked on, not just the constraints currently being built. If all tables referenced by those specified by
table-id
have not yet been loaded, defer validation by specifying NO VALIDATE.
For more information and help in deciding which options to specify, see the
Database Administration Section
.
Sorting intermediate work files
If you run the load process in steps or phases, use the sort parameters in the SYSPCH file to sort the intermediate files.
JCL Considerations
When you submit a BUILD statement through the batch command facility, the JCL to execute the facility must include statements to define:
  • The dictionary containing table definitions
  • The files containing the tables and indexes to be processed
  • Intermediate work files to be used by BUILD
  • Sort work files, if doing a complete BUILD
BUILD utility uses intermediate work files
Each step of the build process, except BUILD STEP4, produces intermediate work files to be used by the next step. If you run a complete BUILD without separating steps, data is sorted in the intermediate files between the steps automatically. If you run a stepped BUILD, you must run the intermediate sorts.
 
When running a complete BUILD,SYS002 and SYS003 must point to the same intermediate file. If the database being processed is so large that the intermediate file must be a multi-volume file, it is required that all extents of the file be physically allocated prior to the initiation of the BUILD utility. If this cannot be done, then run a stepped BUILD. When running a stepped BUILD, SYS002 and SYS003 must point to different intermediate files. The data that is output in SYS003 by each step is input to the next step in SYS002.
The following table shows the output of the steps of the BUILD process:
Step
Output
Size
Step 1
SYS003
For each record:
(MAX SORT CONTROL KEY SIZE) + (MAX FOREIGN KEY SIZE) + 24
 
SYSPCH contains sort parameters
80 bytes
Step 2
SYS003
For each record:
(MAX SORT CONTROL KEY SIZE) + (MAX FOREIGN KEY SIZE) + 24
 
SYSPCH contains sort parameters
80 bytes
Step 3
SYS003
For each prefix:
56 bytes
 
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.
Examples
The following example instructs the BUILD utility to perform a complete BUILD for the LOAD.M and LOAD.M2 sample tables. The NO VALIDATE option specifies that a VALIDATE should not be performed and ERRORS CONTINUE indicates that processing should continue regardless of the number of errors.
build for load.m,           load.m2      errors continue      no validate;
Sample Output
The following report was generated after executing the BUILD statement 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 BUILD FOR LOAD.M,           LOAD.M2      ERRORS CONTINUE      NO VALIDATE; IDMSLOAD  -   volser     SWEEP DATABASE     yy-mm-dd-hh.mm.ss IDMSLOAD  -  3 records processed for table LOAD.M IDMSLOAD  -  3 intermediate records for index LOAD.IX_M IDMSLOAD  -  3 records processed for table LOAD.M2 IDMSLOAD  -  3 intermediate records for index LOAD.IX1_M2 IDMSLOAD  -  3 intermediate records for index LOAD.IX2_M2 IDMSLOAD  -  15 intermediate records were written to SYS003 IDMSLOAD  -       largest SYS003 record size is 56 characters IDMSLOAD  -         SWEEP DATABASE     processing completed IDMSLOAD  -   volser     CONNECT UP INDEXES     yy-mm-dd-hh.mm.ss IDMSLOAD  -  6 intermediate records were written to SYS003 IDMSLOAD  -       largest SYS003 record size is 56 characters IDMSLOAD  -         CONNECT UP INDEXES     processing completed AutoCommit will COMMIT transaction Command Facility ended with warnings