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.
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 |
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.
FILE "testtbl2.unl" DELIMITER '|' 1;
INSERT INTO testtbl
(testuser, testtime, testfield)
VALUES
('kae', CURRENT, f01);
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.
- 1 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.