LOAD

The LOAD utility loads data into an SQL-defined database.

The process of loading a database has three major phases:

Phase	Description
Load	Loads the data.
Build	Builds indexes and linked indexed referential constraints.
Validate	Ensures the validity of referential constraints.

Each phase is composed of multiple steps.

You can run the LOAD utility in several ways:

Type of LOAD	What It Does
Complete LOAD	Runs all three phases.
Phased LOAD	Runs either the LOAD and BUILD phases or just the LOAD phase.
Stepped LOAD	Runs the first or second step of the LOAD phase, with intermediate file sorting required between each step.

Note: For more information about the phases and steps involved in loading a database, and help in deciding what kind of load you should run, see the CA IDMS Database Administration Guide.

Authorization

To	You need this privilege	On
Load into a table	INSERT	The table

Note: Only one LOAD, BUILD, or VALIDATE statement can be performed during one execution of the Batch Command Facility (IDMSBCF).

LOAD Syntax

Complete LOAD:

Syntax for complete or phased LOAD

►►─── LOAD ───────────────────────────────────────────────────────────────────►

   ┌──────────────────────────────┐
 ►─▼─ target-table-specification ─┴───────────────────────────────────────────►

 ►─┬───────────────────┬──────────────────────────────────────────────────────►
   └─ WITHOUT PRESORT ─┘

 ►─┬────────────────────┬─────────────────────────────────────────────────────►
   ├─ NO BUILD ─────────┤
   ├─ BUILD INDEX ONLY ─┤
   └─ NO VALIDATE ──────┘
 ►─┬─────────────────────┬────────────────────────────────────────────────────►
   └─ FROM start-record ─┘

 ►─┬──────────────────────────┬───────────────────────────────────────────────►
   └─ FOR ─┬─ record-count ─┬─┘
           └─ ALL ◄─────────┘

 ►─┬───────────────────────────────┬──────────────────────────────────────────►
   └─ LRECL logical-record-length ─┘
 ►─┬──────────────────────────────┬───────────────────────────────────────────►
   └─ NOTIFY notify-record-count ─┘

 ►─┬───────────────────────────────────────────────┬──────────────────────────►◄
   │ ┌───────────────────────────────────────────┐ │
   └─▼─┬────────────┬─ ERRORS ─┬─ error-count ─┬─┴─┘
       ├─ LOAD ─────┤          └─ CONTINUE ────┘
       ├─ BUILD ────┤
       ├─ VALIDATE ─┤
       └─ ALL ◄─────┘

Expanded syntax for target table specification

►►─── INTO ─┬────────────────┬─ table-id ─────────────────────────────────────►
            └─ schema-name. ─┘

 ►─┬───────────────────────────────────────────────────┬──────────────────────►
   └─ WHERE ─┬─ select-column-name ──────┬─ = literal ─┘
             └─ POSITION start-position ─┘

 ►─┬──────────────────────────────────────────────────────────────────────┬───►◄
   │┌──────────────────────────────── , ───────────────────────────────┐  │
   └▼─target-col-name POSITION start-pos data-type ┬─ NULL IF literal ┬┴)─┘
                                                   └─ SQLIND (length) ┘

Stepped LOAD

Syntax for STEP 1

►►─── LOAD STEP1 ─────────────────────────────────────────────────────────►

   ┌──────────────────────────────┐
 ►─▼─ target-table-specification ─┴───────────────────────────────────────►

 ►─┬───────────────────┬──────────────────────────────────────────────────►
   └─ WITHOUT PRESORT ─┘
 ►─┬─────────────────────────────┬────────────────────────────────────────►
   ├─ EXTRACT ─┬─ INDEXES ─────┬─┤
   │           ├─ CONSTRAINTS ─┤ │
   │           └─ BOTH ◄───────┘ │
   └─ NO EXTRACT ────────────────┘

 ►─┬─────────────────────┬────────────────────────────────────────────────►
   └─ FROM start-record ─┘
 ►─┬──────────────────────────┬───────────────────────────────────────────►
   └─ FOR ─┬─ record-count ─┬─┘
           └─ ALL ◄─────────┘

 ►─┬───────────────────────────────┬──────────────────────────────────────►
   └─ LRECL logical-record-length ─┘
 ►─┬──────────────────────────────┬───────────────────────────────────────►
   └─ NOTIFY notify-record-count ─┘

 ►─┬────────────────────────────┬─────────────────────────────────────────►◄
   └─ ERRORS ─┬─ error-count ─┬─┘
              └─ CONTINUE ────┘

Syntax for STEP 2

►►─── LOAD STEP2 ─────────────────────────────────────────────────────────►

 ►─┬──────────────────────────────┬───────────────────────────────────────►◄
   └─ NOTIFY notify-record-count ─┘

LOAD Parameter

Parameters for complete or phased LOAD

target-table-specification

Specifies the tables and columns to be loaded.

See parameter descriptions under "Parameters for Target Table Specification".

WITHOUT PRESORT

Directs the LOAD utility not to sort the input data in SYS001 before starting to load.

By default, if you do not specify WITHOUT PRESORT, the LOAD utility will sort the input data into target page sequence before beginning to load.

Note: For more information and help in deciding whether to suppress the presort, see the CA IDMS Database Administration Guide.

NO BUILD

Directs the LOAD utility to perform neither the BUILD nor the VALIDATE phase.

If you specify NO BUILD, you must run the BUILD utility before you can use the table(s).

BUILD INDEX ONLY

Directs the LOAD utility to build indexes but not referential constraints.

If you specify BUILD INDEX ONLY, you might have to run the BUILD utility before you can use the table(s).

NO VALIDATE

Directs the LOAD utility to stop before validating referential constraints.

If you specify NO VALIDATE, you will have to run the VALIDATE utility before you can use the table(s).

FROM

Directs the LOAD utility to begin processing input data from a specified record in the input file.

By default, if you do not specify a start-record, the LOAD utility will begin with the first record of the input file.

start-record

Specifies the number of the first record to process.

FOR

Directs the LOAD utility to stop after processing a specified number of records from the input file.

By default, if you do not specify a FOR option, the LOAD utility will continue until it has processed the last input record.

record-count

Specifies the number of records to process before stopping.

ALL

Directs the LOAD utility to continue processing until it has loaded the last record in the input file.

All is the default.

LRECL

Specifies that the SYS001 input records are fixed length records.

By default, if you do not specify LRECL, the LOAD utility assumes that the SYS001 records are variable length.

logical-record-length

Specifies the length, in bytes, of the fixed length input records.

NOTIFY

Directs the LOAD 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 processed.

By default, the LOAD utility will not notify you of its progress until it is finished.

notify-record-count

Specifies the number of records to process before sending a message.

ALL ERRORS

Directs the LOAD utility either to continue when any errors are detected or stop after a specified number of errors are detected.

By default, the LOAD utility will stop after the first error is detected.

Detected errors are listed in the report generated by LOAD and sent to the SYSLST file.

LOAD ERRORS

Directs the LOAD utility either to continue when errors are detected in the LOAD process or stop after a specified number of errors are detected.

By default, the LOAD utility will stop after the first error is detected.

Detected errors are listed in the report generated by the LOAD utility and sent to the SYSLST file.

BUILD ERRORS

Directs the LOAD utility either to continue when errors are detected while indexes and referential constraints are being built or stop after a specified number of errors are detected.

By default, the LOAD utility will stop after the first error is detected.

Detected errors are listed in the report generated by the LOAD utility and sent to the SYSLST file.

VALIDATE ERRORS

Directs the LOAD utility either to continue when errors are detected in the validation or stop after a specified number of errors are detected.

By default, the LOAD utility will stop after the first error is detected.

Detected errors are listed in the report generated by the LOAD utility and sent to the SYSLST file.

error-count

Specifies the number of errors to detect before stopping.

CONTINUE

Directs the LOAD utility to continue regardless of the number of errors detected.

Parameters for Target Table Specification

INTO

Specifies a table in which to load data.

schema-name

Specifies the name of the schema that contains the table.

table-id

Specifies the identifier of the table.

WHERE

Directs the LOAD utility to insert a row into the table from an input record only if a field beginning at a specified position in the input record, or the contents of an input column, equals a given literal value.

select-column-name

Specifies the name of the column whose contents must equal a literal value.

The column name you specify must also be a column name of the table and must be named in the column list of the target table specification if target column names are used.

literal

Specifies the value the column must contain.

The data type of the literal must be comparable to that of the column.

Note: For more information about comparable data types, see the CA IDMS SQL Reference Guide.

POSITION start-pos

Specifies the beginning position of the field whose value is to be tested.

literal

Specifies the value the field must contain. The literal must be a character or hexadecimal literal value.

target-column-name

Specifies the columns of the table for which input values are present in the input file.

If no column names are specified, the input file must contain values for all columns of the table and the order, data type, and null indications must exactly match those of the table as defined in the dictionary. VARCHAR and VARGRAPHIC input values must be preceded by a 2-byte binary length of the value.

If you specify more than one target-column-name, specify them in increasing order; the position of each must be greater than the sum of:

The position of the one listed before
plus
The size of the data type for the one listed before
- The size of VARCHAR is the maximum length, plus 2.
- The size of VARGRAPHIC is the maximum length times the number of bytes for one character, plus 2.

POSITION

Specifies the position of the column value in the input record.

start-pos

Specifies the position of the first byte of the value relative to one.

data-type

Specifies the SQL data type of the column value in the input record.

Note: For a list of SQL data types and for the SQL standards of data-type specification, see the CA IDMS SQL Reference Guide.

NULL IF

Directs the LOAD utility to substitute a null value if it encounters a specified input value.

literal

Specifies the value for which a null will be substituted. The data type of the literal must be comparable to the data type of the column.

SQLIND

Indicates that a null indicator immediately follows the data value on the input file.

The possible values of the null indicator and their meanings are as follows:

Value	Meaning
Binary zeros	The column value is not null
Binary X'FF's	The column value is null

(length): Specifies the length of the indicator. The length must be 1, 2, or 4.

Parameters for STEP 1

LOAD STEP1

Directs the LOAD utility to perform only the first step of the load process.

target-table-specification

Specifies the tables and columns to be loaded.

For the syntax expansion and parameter descriptions of target-table-specification, see "Complete LOAD" earlier in this chapter.

WITHOUT PRESORT

Directs the LOAD utility not to sort the input data before starting to load.

By default, if you do not specify WITHOUT PRESORT, the LOAD utility will sort the input data before beginning to load. The records are sorted so that target pages will be loaded in order.

Note: For more information and help in deciding whether to suppress the presort, see the CA IDMS Database Administration Guide.

If you specify WITHOUT PRESORT, then do not run LOAD STEP2. After LOAD STEP1 is completed, and you have sorted the output of LOAD STEP1, run the BUILD utility.

EXTRACT

Directs LOAD to extract information needed for building indexes or referential constraints.

By default, if you do not specify otherwise, LOAD STEP1 will extract the information needed for building both indexes and indexed referential constraints. In this case, you can begin BUILD processing with BUILD STEP2.

INDEXES

Directs the LOAD utility to extract only the information needed for building indexes. Therefore, the LOAD utility will not extract the information needed for building indexed referential constraints.

CONSTRAINTS

Directs the LOAD utility to extract only the information needed for building linked indexed referential constraints. Therefore, the LOAD utility will not extract the information needed for building indexes.

BOTH

Directs the LOAD utility to extract the information needed for building both indexes and referential constraints. In this case, when you run the BUILD utility, you can begin with BUILD STEP2.

BOTH is the default.

NO EXTRACT

Directs the LOAD utility not to extract information needed for building either indexes or referential constraints. In this case, run STEP1 of the BUILD utility to do the extraction.

Note: The remaining parameters are identical to the like-named parameters for a complete LOAD presented earlier in this chapter. Refer to this section for parameter descriptions.

Parameters for STEP 2

LOAD STEP2

Directs the LOAD utility to perform only the second step of the load process.

Execute this only after executing LOAD STEP1 without specifying WITHOUT PRESORT.

Note: The remaining parameters are identical to the like-named parameters for a complete LOAD presented earlier in this chapter. Refer to this section for parameter descriptions.