How to write a dbload command file in delimiter form

Command files must contain required elements, including delimiters.

The FILE statement in the following example describes the stock.unl data rows as composed of six fields, each separated by a vertical bar (|) as the delimiter.

FILE stock.unl DELIMITER '|' 6;
INSERT INTO stock;

Two consecutive delimiters define a null field. As a precaution, you can place a delimiter immediately before the new-line character that marks the end of each data row. If the last field of a data row has data, you must use a delimiter. If you omit this delimiter, an error results.

1|SMT|baseball gloves|450.00|case|10 gloves/case
2|HRO|baseball|126.00|case|24/case
3|SHK|baseball bat|240.00|case|12/case

The example INSERT statement contains only the required elements. Because the column list is omitted, the INSERT statement implies that values are to be inserted into every field in the stock table. Because the VALUES clause is omitted, the INSERT statement implies that the input values for every field are defined in the most recent FILE statement. This INSERT statement is valid because the stock table contains six fields, which correspond to the number of values that the FILE statement defines.

FILE stock.unl DELIMITER '|' 6;
INSERT INTO new_stock (col1, col2, col3, col5, col6)
   VALUES (f01, f03, f02, f05, 'autographed');

The user changed the column names, the order of the data, and the meaning of col6 in the new stock table. Because the fourth column in new_stock (col4) is not named in the column list, the new data row contains a null value in the col4 position (assuming that the column permits null values). If no default is specified for col4, the inserted value is null.

The following table shows the first data row that is inserted into new_stock from this INSERT statement.