Loading an SQL-Defined Database › Contents of the Input File

Contents of the Input File

Mixed Input Records

The input file to the load process can contain different types of input records. For example, the input file might contain an EMPLOYEE record, followed by a DEPARTMENT record, followed by an OFFICE record and so on; to distinguish the different types of records, you must include a record identifier (in this example, at the end of the record):

0574SMITH    JOHN   254 WILLOW ST   NEEDHAM  MA   4035 415
4001PERSONNEL          MASON      PAULA    5538   0020 410
0020CHICAGO  3 CORPORATE PLACE                         450

Note: By including record identifiers at the end of the input records, you may be able to avoid listing individual column definitions in the LOAD statement.

Loading Multiple Tables

You can load more than one table in the same load operation by using one of the following techniques:

• By specifying selection criteria applied against records in the input file. For example, to load the EMPLOYEE table, using the example above, you could select all input records with value '415' as a record identifier.
• By selecting specific fields from one input record that contains data pertinent to multiple tables. For example, the input record may contain values to be stored in table EMPLOYEE and values to be stored in table DEPARTMENT.

Identifying Columns Implicitly

If, in the LOAD statement, you do not explicitly list the columns in the table to be loaded, CA IDMS/DB assumes that values are supplied for all columns in the table. It starts with position 1 of the input record and extracts input values for each column of the table. To be successful, the input data must match the order, data type, length, and null criteria specified in the table definition. Columns that allow null values must be represented by a data field and an indicator field, which is described under "Null values".

Identifying Columns Explicitly

If you supply values for only some of the columns within the table or if the order or data types of the values in the input file do not match that of the columns in the table, you must tell CA IDMS/DB:

• Where to find the column values in the input record by specifying their start position relative to the beginning of the input record
• The data type of the input record value
• The null value criteria for input values, if applicable

If you omit a column name, the column must either:

• Allow null values
• Allow a default value

Data Types

If you explicitly list the columns to be loaded, the data type of the value to be stored can be different from the data type defined for the column provided the data types are compatible. For example, a column defined as CHARACTER is compatible with data types VARCHAR, DATE, TIME, and TIMESTAMP.

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

Null Values

Null values in an input file can be represented as either:

• A specific data value, designated by you in the NULL IF clause of the LOAD statement.
• An indicator field, immediately following the data field. This field is either a 1, 2, or 4 byte binary field and must contain a value of:
  • 0, to indicate a non-null data value
  • -1, to indicate a null value

  If you do not explicitly list the columns to be loaded, then all columns that permit null values must be followed by a 4-byte indicator field.