How to write a dbload command file in character-position form

Command files must define data fields and use character positions to define the length of each field.

The FILE statement in the following example defines six data fields from the cust_loc_data table data rows.

FILE cust_loc_data
   (city 1-15,
    state 16-17,
    area_cd 23-25 NULL = 'xxx',
    phone 23-34 NULL = 'xxx-xxx-xxxx',
    zip 18-22,
    state_area 16-17 : 23-25);
INSERT INTO cust_address (col1, col3, col4)
   VALUES (city, state, zip);

The statement names the fields and uses character positions to define the length of each field. Compare the FILE statement in the preceding example with the data rows in the following figure.

This figure shows two data rows containing city, state, zip code, and phone information. — Figure 1: A Sample Data File

The FILE statement defines the following data fields, which are derived from the data rows in the sample data file.


Column	Values from Data Row 1	Values from Data Row 2
city	Sunnyvale`++++++`	Tempe`++++++++++`
state	CA	AZ
area_cd	408	null
phone	408-789-8075	null
zip	94086	85253
state_area	CA408	AZxxx

The null strings that are defined for the phone and area_cd fields generate the null values in those columns, but they do not affect the values that are stored in the state_area column.

The INSERT statement uses the field names and values that are derived from the FILE statement as the value-list input. Consider the following INSERT statement:

INSERT INTO cust_address (col1, col3, col4)
   VALUES (city, state, zip);

The INSERT statement uses the data in the sample data file and the FILE statement to put the following information into the cust_address table.


Column	Values from Data Row 1	Values from Data Row 2
col1	Sunnyvale`++++++`	Tempe`++++++++++`
col2	null	null
col3	CA	AZ
col4	94086	85253

Because the second column (col2) in cust_address is not named, the new data row contains a null (assuming that the column permits nulls).

Consider the following INSERT statement:

INSERT INTO cust_sort 
   VALUES (area_cd, zip);

This INSERT statement inserts the following data rows into the cust_sort table.


Column	Values from Data Row 1	Values from Data Row 2
col1	408	null
col2	94086	85253

Because no column list is provided, dbload reads the names of all the columns in cust_sort from the system catalog. (You cannot insert data into a temporary table because temporary tables are not entered into the system catalog.) Field names from the previous FILE statement specify the values to load into each column. You do not need one FILE statement for each INSERT statement.