Previous Topic: Database LoadingNext Topic: Contents of the Input File

Loading an SQL-Defined Database › Loading Considerations

Loading Considerations

Loading Multiple Tables

If you are loading multiple tables, it may be necessary to split the process into separate load operations and process them in a certain order. Use these rules to determine whether this is necessary and the correct sequence in which to perform the load operations.

Using Pre-Sorted Data

Before CA IDMS/DB loads data, it sorts the data using a sort sequence best suited to the table's characteristics. If you have already sorted the input data, you can tell CA IDMS/DB to skip the sort phase.

Providing Sorted Data

To sort the data yourself, follow these recommendations to achieve the most efficient load for your tables:

Table Characteristic

Recommended Sort Sequence

Table has a clustered index

Sort on index key

Table has a clustered referential constraint

Sort on foreign key of the referencing table

Table has a CALC key

Sort on CALC-key target page; to do this, use the IDMSCALC utility program to determine the target page and append the target page to the input record

Database Buffers Used During Load

You must load a database in local mode. The DMCL that you use for the load should specify buffers for the areas being loaded that contain at least 10 pages. The larger the buffer, the more efficient the load.

Reserving Space on the Page

If you want to leave free space on the database pages following the load, add an area override in the DMCL that specifies a page reserve. After the load is complete, remove the area override so that new rows and index entries can use the free space. This technique is especially useful for areas that contain only indexes or that contain tables clustered on an index.

Error Handling

CA IDMS/DB may encounter errors during each phase of the load process. You can instruct CA IDMS/DB what to do in response to these errors, for example, to continue processing or to quit following a specified number of errors. The following table summarizes the types of errors that can occur within each phase:

Phase

Type of Error

Corrective Action

All phases

Table not defined in the catalog

Define the table in the catalog

Load
  • Check constraint violation
  • Invalid data values
  • Unique constraint violation on a CALC key,clustering index, or linked clustered constraint
  • Referential constraint violation on a linked clustered constraint

No corrective action needed; however, row is not inserted and subsequent build and validate phases may fail.

 

 

Ensure that the referenced table has been loaded and any referenced key index built prior to loading the referencing table.

Build
  • Unique constraint violation on non-clustering indexor linked index constraint
  • Referential constraint violation on a linked index constraint

FIX PAGE utility statement or reload data

Validate

Invalid referential constraint
  • INSERT to store missing owner
  • UPDATE to change invalid foreign key
  • DELETE to remove invalid referencing rows

Input Data Used in the Build Phase

You can enter the BUILD phase of the load process using data stored in intermediate work files created by the LOAD phase or by instructing CA IDMS/DB to extract the necessary information as the first step in the build process. Intermediate work files are generally used when you intend to enter the BUILD phase immediately following the LOAD phase; typically, you instruct CA IDMS/DB to extract the information if some time elapses between the two phases.

The following table summarizes how to specify these options:

BUILD Phase Input

Load and Build Option

LOAD Statement

BUILD Statement

Intermediate work file

Phased load and build

LOAD NO VALIDATE

None

 

Stepped load

LOAD STEP1 EXTRACT

Start with BUILD STEP2

Extracted work file

Phased load

LOAD NO BUILD

Start with BUILD STEP1

Enhancing Load Performance

The following list identifies some ways to enhance the performance of your load operations: