Stepped Load Procedure

Loading an SQL-Defined Database › Loading Procedures › Stepped Load Procedure

Stepped Load Procedure

Steps

Follow the steps listed next, to perform a stepped load:

Note: If you want to be able to recover the database in the event of a failed job step, back up the database areas between each job step.

Action	Statement
1. In local mode, load one or more database tables choosing one of the following options. If you intend to build indexes and relationships for the tables immediately following the load step, choose one of the options that creates an intermediate work file:
1.1Load, creating intermediate extract files for the build phase	LOAD STEP1 EXTRACT BOTH (the default)
1.2 Load, creating an intermediate extract file for building indexes	LOAD STEP1 EXTRACT INDEXES
1.3 Load, creating an intermediate extract file for building relationships	LOAD STEP1 EXTRACT RELATIONSHIPS
1.4 Load, creating no intermediate extract file	LOAD STEP1 NO EXTRACT
2. If you specified WITHOUT PRESORT, skip this step. Otherwise, sort the data using an external sort program and the sort cards supplied by CA IDMS/DB. Then continue the load phase of the stepped load procedure.	LOAD STEP2
3. If you specified LOAD STEP1 NO EXTRACT, perform this step to collect the data necessary to build the table indexes and indexed constraints	BUILD STEP1
4. Sort the data using an external sort program and the sort cards supplied by CA IDMS/DB
5. After all of the tables have been loaded or after completingthe previous step, determine the db-keys of rows in any tables that participate as the referenced table in a linked index referential constraint	BUILD STEP2
6. Sort the data using an external sort program and the sort cards supplied by CA IDMS/DB
7. Build unclustered indexes and linked index referentialconstraints	BUILD STEP3
8. Sort the database using an external sort program and the sort cards supplied by CA IDMS/DB
9. Update the prefixes of any tables that participate as thereferencing table in a linked index referential constraint	BUILD STEP4
10. Perform the first pass at validating the relationships between tables that participate in referential constraints	VALIDATE STEP1
11. Sort the database using an external sort program and the sort cards supplied by CA IDMS/DB
12. Perform the second pass of validatingreferential constraints; generally, a second pass is required for unlinked relationships if either the referenced table or referencing table contains a CALC key.	VALIDATE STEP2

Example

In the next example, the DBA loads an SQL-defined database in the following steps:

Loads tables CUSTOMER and INVENTORY using pre-sorted data
Loads tables ORDERS and PARTS using pre-sorted data
Using an area sweep, extracts information for building indexes and indexed constraints
Builds the indexes and constraints for the table using separate steps and external sorts

Validates referential constraints

load step1
  into custschm/customer
   where position 300 = '435'
  into custschm.inventory
   where position 300 = '457'
  without presort
  no extract;


load step1
  into custschm.orders
   where position 200 = '335'
  into custschm.parts
   where position 200 = '345'
  without presort
  no extract;


build step1
   for custschm.customer
       custschm.inventory
       custschm.orders
       custschm.parts;

Sort the data:

build step2;

Sort the data:

build step3;

Sort the data:

build step4;

validate step1;

Sort the data:

validate step2;