Syntax for the delimiter form

The syntax for the delimiter form specifies the field delimiter, the input file, and the number of fields in each row of data.

The following diagram shows the syntax of the delimiter FILE statement.


>>-FILE--filename--DELIMITER--'c'--nfields---------------------><

Element	Purpose	Key Considerations
`c`	Specifies the character as the field delimiter for the specific input file	If the delimiter specified by `c` appears as a literal character anywhere in the input file, the character must be preceded with a backslash (\) in the input file. For example, if the value of `c` is specified as a square bracket ([) , you must place a backslash before any literal square bracket that appears in the input file. Similarly, you must precede any backslash that appears in the input file with an additional backslash. You can specify any printable character, as defined by current locale, the tab character `TAB` (`CTRL-I`), or a blank space (ASCII 32) as the delimiter symbol. You cannot specify non-printable character, a hexadecimal character, or a backslash character.
`filename`	Specifies the input file	None.
`nfields`	Indicates the number of fields in each data row	None.

The dbload utility assigns the sequential names f01, f02, f03, and so on to fields in the input file. You cannot see these names, but if you refer to these fields to specify a value list in an associated INSERT statement, you must use the f01, f02, f03 format. For details, refer to How to write a dbload command file in delimiter form.

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 whenever the last field of a data row is not empty.

Inserted data types correspond to the explicit or default column list. If the data field width is different from its corresponding character column width, the data is made to fit. That is, inserted values are padded with blanks if the data is not wide enough for the column or truncated if the data is too wide for the column.

If the number of columns named is fewer than the number of columns in the table, dbload inserts the default value that was specified when the table was created for the unnamed columns. If no default value is specified, dbload attempts to insert a null value. If the attempt violates a not null restriction or a unique constraint, the insert fails, and an error message is returned.

If the INSERT statement omits the column names, the default INSERT specification is every column in the named table. If the INSERT statement omits the VALUES clause, the default INSERT specification is every field of the previous FILE statement.

An error results if the number of column names listed (or implied by default) does not match the number of values listed (or implied by default).

The syntax of dbload INSERT statements resembles INSERT statements in SQL, except that in dbload, INSERT statements cannot incorporate SELECT statements.

Do not use the CURRENT, TODAY, and USER keywords of the INSERT INTO statement in a dbload command file; they are not supported in the dbload command file. These keywords are supported in SQL only.

For example, the following dbload command is not supported:

FILE "testtbl2.unl" DELIMITER '|' 1; 
            INSERT INTO testtbl 
                 (testuser, testtime, testfield) 
              VALUES 
                 ('kae', CURRENT, f01);

Load the existing data first and then write an SQL query to insert or update the data with the current time, date, or user login. You could write the following SQL statement:

INSERT INTO testtbl  
               (testuser, testtime, testfield) 
            VALUES  
               ('kae', CURRENT, f01);

The CURRENT keyword returns the system date and time. The TODAY keyword returns the system date. The USER keyword returns the user login name.

The following diagram shows the syntax of the dbload INSERT statement for delimiter form.


>>-INSERT INTO--+----------+--table--+------------------+------->
                '-owner--.-'         |    .-,------.    |   
                                     |    V        |    |   
                                     '-(----column-+--)-'   

>--+-----------------------+--;--------------------------------><
   |                   (1) |      
   '-| VALUES clause |-----'

Notes:

See VALUES Clause.

Element	Purpose	Key Considerations
`column`	Specifies the column that receives the new data	None.
`owner.`	Specifies the user name of the table owner	None.
`table`	Specifies the table that receives the new data	None.

Users who run dbload with this command file must have the Insert privilege on the named table.