INGEST command

Authorization

• If the target table has any triggers, the authorization ID must have sufficient privileges to execute the operations that the triggers specify.
• To insert into or update a table that has protected columns, the authorization ID must have LBAC credentials that allow write access to all protected columns in the table. Otherwise, the command fails and an error is returned.
•  If an UPDATE or MERGE statement requires reading a protected column, the authorization ID must have LBAC credentials that allow read access to the column. Otherwise, the command fails and an error is returned.
• To insert into or update a table that has protected rows, the authorization ID must hold an LBAC credential that meets these criteria:
  • The LBAC credential is part of the security policy protecting the table.
  • If the security policy was defined as RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL, then the LBAC credential must granted to the authorization ID for write access
  The security label on the row to be inserted, the authorization ID's LBAC credentials, the security policy definition, and the LBAC rules determine whether insert or update can be performed on the table with protected rows.
• If the INGEST command specifies the RESTART NEW (the default) or RESTART CONTINUE option, then
  • DATAACCESS authority on the schema of the restart table
  • SELECTIN, INSERTIN, UPDATEIN, and DELETEIN privileges on the schema of the restart table
  • SELECT, INSERT, UPDATE, and DELETE privileges on the restart table
• If the INGEST command specifies the RESTART TERMINATE option, then
  • DATAACCESS authority on the schema of the restart table
  • SELECTIN and DELETEIN privileges on the schema of the restart table
  • SELECT and DELETE privileges on the restart table
• If the INGEST command specifies the EXCEPTION TABLE option, then
  • DATAACCESS authority on the schema of the exception table
  • INSERTIN privilege on the schema of the exception table
  • INSERT privilege on the exception table

In addition, the SQL statement on the INGEST command is subject to the same fine grained access controls (FGAC) that it would be if the user running the ingest utility accessed the table outside of the ingest utility.

Required connection

Database

Command syntax

from-clause

format-clause

options

error-handling options

SQL-statement

format-definition

field-list

field-definition

field-type

DELETE-statement

period-clause

INSERT-statement

MERGE-statement

matching-condition

modification-operation

period-clause

assignment-clause

insert-operation

REPLACE-statement

UPDATE-statement

assignment-clause

expression

operator

function-invocation

argument

case-expression

searched-when-clause

simple-when-clause

cast-specification

search-condition

predicate

basic-predicate

NULL-predicate

Command parameters

FROM

Specifies the source of the input data. The ingest utility always reads the input data from the client.

FILE file-name

Specifies that data is to be read from the specified files. For the syntax of file names, see File and pipe names.

FILE remote-file-name

Specifies that data is to be read from remote storage, such as IBM® Cloud Object Storage or Amazon Simple Storage Service (S3), and is accessed by using a storage access alias. Local staging space is required to temporarily store the file that is transferred from the remote storage server; refer to Remote storage requirements. The syntax of remote file names is:

DB2REMOTE://<alias>/<container>/<object>

PIPE pipe-name

Specifies that data is to be read from the specified pipes. For the syntax of pipe names, see File and pipe names.

FORMAT format-definition

The data is in the format given by the specified format definition. The syntax of a format definition is given in the "format-definition" section described previously.

DUMPFILE or BADFILE file-name

Specifies that rows rejected by the formatters are to be written to the specified file. The formatters reject rows due to the following types of errors:

• numbers that are invalid or out of range (based on the field type)
• dates, times, and timestamps that do not fit the specified format
• Any other errors detected by the formatter

If a relative path is specified, it is relative to the current directory and the default drive. On Linux® and UNIX platforms, if the file does not exist, the ingest utility creates the file with the permission flags specified by the user's umask setting. The ingest utility uses the umask setting that was in effect at the time the Db2 CLP backend process (db2bp) started, which is usually at the first connection. If the file already exists, the ingest utility appends the records to the end of the file.

The ingest utility writes each record in the same format as it appeared in the input. This means that after correcting the errors, you can re-run the INGEST command using the dump file as input. However, the ingest utility might not write records in the same order that they appeared across multiple input files or within each input file. If you want the dump file to contain records in the same order that they appeared within each input file, set the num_formatters configuration parameter to 1. In this case:

• If the operation is INSERT or REPLACE, records from different input files might still appear out of order but all the records from a given input file appears in the same order that they were within that file.
• If the operation is UPDATE, DELETE, or MERGE, all records are in the same order that they were across all input files and within each input file

If an error occurs opening or writing to the file, the ingest utility issues an error message, including the input source and line number, and ends the command. In this case, the record is not saved anywhere. (If the input is from a file, the user can get the original input record from the file.)

The dump file is guaranteed to be complete only if the INGEST command completes normally in a single run. If the INGEST command fails and the restarted command succeeds, the combination of dump files from the two commands might be missing records or might contain duplicate records.

If this parameter is not specified, the ingest utility issues an error message for each rejected row, but does not save the row anywhere.

Note: If you specify this parameter and any rejected input records contain sensitive data, these records appear in the dump file. You must protect the file from unauthorized access as needed.

EXCEPTION TABLE table-name

Specifies that rows inserted by the ingest utility and rejected by Db2 with certain SQLSTATEs are to be written to the specified table. Db2 could reject rows due to the following types of errors. Note that each of these errors indicates bad data in the input file:

• For character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small.
• For binary data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small.
• A null value, or the absence of an indicator parameter was detected; for example, the null value cannot be assigned to a host variable, because no indicator variable is specified.
• A numeric value is out of range.
• An invalid datetime format was detected; that is, an invalid string representation or value was specified.
• The character value for a CAST specification or cast scalar function is invalid.
• A character is not in the coded character set.
• The data partitioning key value is not valid.
• A resulting row did not satisfy row permissions.
• An insert or update value is null, but the column cannot contain null values.
• The insert or update value of the FOREIGN KEY is not equal to any value of the parent key of the parent table.
• A violation of the constraint imposed by a unique index or a unique constraint occurred.
• The resulting row of the INSERT or UPDATE does not conform to the check constraint definition.
• The value cannot be converted to a valid security label for the security policy protecting the table.
• This authorization ID is not allowed to perform the operation on the protected table.
• The component element is not defined in security label component.
• The specified security label name cannot be found for the specified security policy.
• The data type, length, or value of the argument to routine is incorrect.

The specified table must be defined as described in the Exception tables topic in the related links section. The restrictions listed in that section and in message SQL3604N also apply, with the following exception:

• If the target table contains a DB2SECURITYLABEL column, the exception table column corresponding to the DB2SECURITYLABEL column must be of type VARCHAR with a length of at least 128. (Type LONG VARCHAR is also permissible but has been deprecated.)

The ingest utility uses the exception table only when the operation is INSERT or REPLACE. If the operation is UPDATE, MERGE, or DELETE, and the EXCEPTION TABLE parameter is specified, the utility issues an error.

If an error occurs inserting into the table, the ingest utility issues an error message, including the input source and line number, and continues. In this case, the record is not saved anywhere. (If the input is from a file, the user can get the original input record from the file.)

If the table name is not fully qualified, the ingest utility uses the value of CURRENT SCHEMA for the schema name. If the EXCEPTION TABLE parameter is not specified, the ingest utility issues an error message for each rejected row but does not save the row anywhere.

WARNINGCOUNT n

Specifies that the INGEST command is to stop after n warning and error messages have been issued. This parameter covers only certain types of errors. For more information, see Categories of errors. The number is the total for all input sources. If this limit is reached in the middle of a transaction, the transaction is rolled back. However, rows that were committed to the target table before this limit was reached remain in the table. If the number is 0, the INGEST command continues regardless of the number of warnings and errors. The range is 0 - 2,147,483,647 (max 32-bit signed integer). The default is 0.

MESSAGES file-name

Specifies the file to receive informational, warning, and error messages.

If a relative path is specified, it is relative to the current directory and the default drive. On Linux and UNIX platforms, if the file does not exist, the ingest utility creates the file with the permission flags specified by the user's umask setting. The ingest utility uses the umask setting that was in effect at the time the Db2 CLP backend process (db2bp) started, which is usually at the first connection. If the file already exists, the ingest utility appends to the end of the file. If this parameter is not specified, the ingest utility writes messages to standard output.

Even when this parameter is specified, the ingest utility writes messages to standard output in the following cases:

• syntax errors
• an input file is not found or not readable
• target or exception table is not found
• the dump file or messages file cannot be opened
• other errors detected at start-up

Also, the summary messages issued at the end (showing number of rows read, inserted, and so on, and the total number of warnings and errors) are always written to standard output.

If an error occurs opening or writing to the messages file, the ingest utility issues an error message and ends the command.

For examples of messages from the INGEST command, see Messages from the INGEST command.

RESTART NEW job-ID

Specifies that if the INGEST command fails before completing, it can be restarted from the point of the last commit by specifying the RESTART CONTINUE option on a later INGEST command. The job-ID is a string of up to 128 bytes that uniquely identifies the INGEST command. This job-ID must be unique across all INGEST commands in the current database that specified the RESTART option and are not yet complete. (These could be commands that are still running or that failed before completing.) Once the INGEST command completes, you can reuse the job-ID on the RESTART parameter of a later INGEST command. If the job-ID is omitted, the ingest utility generates one. This option is the default.

Before using this option, you must have created the restart log table. For more information, see "Restarting a failed INGEST command" in the following related links section.

RESTART OFF

Specifies that no restart information is to be saved. If the INGEST command fails before completing, it cannot be restarted using the RESTART CONTINUE option. If you want to rerun the command to completion, you need to restore the target table to its state before running the INGEST command, and rerun the INGEST command with the same input data.

RESTART CONTINUE job-ID

Specifies that the ingest utility is to restart a previous INGEST command that specified the RESTART NEW option and failed before completing. The job-ID specified on this option must match the job-ID specified on the previous INGEST command. This restarted command is also restartable. Once the restarted command completes, you can reuse the job-ID on the RESTART NEW parameter of a later INGEST command. For more information, see Restarting a failed INGEST command in the following related links section.

RESTART TERMINATE job-ID

Specifies that the ingest utility is to clean up the restart information for a previous INGEST command that specified the RESTART NEW option and failed before completing. The string specified on this option must match the string specified on the previous INGEST command.

This option is intended for when a previous restartable INGEST command failed, and you plan to never resume the job. Once you specify this option, the INGEST command that failed earlier can no longer be restarted. You can, however, reuse the string on the RESTART NEW parameter of a later INGEST command.

Note: Unlike with the TERMINATE parameter of the LOAD command, data that the original INGEST command committed before its failure is still in the target table. For more information, see Terminating a failed INGEST command in the following related links section.

DELIMITED BY char

Specifies that each input record is a sequence of text fields delimited by the specified character. The delimiter can be any single-byte character except the following values:

• the null character (X'00')
• blank (X'20')
• carriage return or line feed (X'0D' or X'0A')
• dot (X'2E'), regardless of what is specified on the RADIX POINT clause
• the decimal point (specified by the RADIX POINT clause; the default is the dot .)
• the string delimiter (specified by the OPTIONALLY ENCLOSED BY clause; the default is the double quote ")

If the input data code page is a DBCS or EUC code page, the character must be in the range X'01' to X'3F', inclusive. Note that this excludes the vertical bar (| or X'7C'). If the input data code page is not a single-byte code page, the character must be in the range X'01' to X'7F', inclusive. If the input code page is a single-byte code page, the character can be in the range X'01' to X'FF'.

The default is a comma ,.

string

The metavariable string specifies any character string constant, including:

• A sequence of characters that starts and ends with a string delimiter which is an apostrophe (').
• A hexadecimal constant: X followed by a sequence of hex characters that starts and ends with an apostrophe.
• A Unicode string constant: U& followed by a sequence of characters that starts and ends with an apostrophe and that is optionally followed by the UESCAPE clause.
• A graphic string constant: G or N followed by a sequence of double-byte characters that starts and ends with an apostrophe.
• A hexadecimal graphic string constant: GX followed by a sequence of hex characters that starts and ends with an apostrophe.
• A UCS-2 graphic string constant: UX followed by a sequence of hex characters that starts and ends with an apostrophe.
• A binary constant: BX followed by a sequence of hex characters that starts and ends with an apostrophe.

If the string contains the enclosing character, it must be doubled inside the string. For example, if the string is "don't know" and the enclosing character is a single quote, specify 'don''t know'. Examples of this are 'This is a string', X'303132', and U&'Unicode chars'.

char

The metavariable char specifies a character string constant that is 1 byte in length. Examples of this are 'A' and X'30'.

POSITIONAL

Specifies that each input record is a sequence of text and/or binary fields identified by their position in the input record. The position of each field is given by the POSITION clause in the field definition.

RECORDLEN length

Specifies that the input is fixed-length records and each record has the specified length in bytes. Any carriage return or line feed characters (X'0D' or X'0A') in the record are assumed to be part of the data. Note that the record length that some editors display does not include these characters, so you might need to add 1 or 2 to the record length that the editor displays. If the input contains binary data, this parameter is required.

The range is 1 - 32,767. If RECORDLEN specifies a length less than the sum of the field lengths specified on each field definition, the ingest utility issues an error message and exits. If RECORDLEN specifies a length greater than the maximum field end position, the ingest utility ignores the bytes past the end of the field with the maximum end position. If the last record is too short, the ingest utility issues an error message and rejects the record.

If this parameter is omitted, the ingest utility recognizes both of the following as the record (row) delimiter, regardless of platform:

• line feed (X'0A')
• one or more carriage returns followed by a line feed (X'0D0A')

INPUT CODEPAGE integer

Specifies the code page of the input data. The ingest utility supports the code pages listed in the Supported territory codes and code pages topic in the Related links section, except the following values :

• 1200 (UTF-16)
• any code pages listed as "host" code pages

The default is the Db2 application code page.

If the input data is not in the code page specified by this parameter, the ingest utility does not issue an error. However, if the ingest utility or Db2 needs to convert the input data into another code page, the utility might convert some characters to the substitute character (Unicode U+001A) or other characters that you did not intend. For more information, see the topic on Code page considerations for the ingest utility.

IMPLICITLYHIDDENINCLUDE

Specifies that implicitly hidden columns are included in the default column list. If the DB2_DMU_DEFAULT registry variable on the client-side is also specified, then this keyword takes precedence. This keyword is only allowed for INSERT statements where there is not a column list following the table name. If the SQL statement is not an INSERT or there is a column list after the table name, then an error is returned (SQLCODE -2918). This option does not apply to the hidden RANDOM_DISTRIBUTION_KEY column of a random distribution table using the random by generation method.

IMPLICITLYHIDDENMISSING

Specifies that implicitly hidden columns are omitted from the default column list. If the DB2_DMU_DEFAULTregistry variable on the client-side is also specified, then this keyword takes precedence. This keyword is only allowed for INSERT statements where there is not a column list following the table name. If the SQL statement is not an INSERT or there is a column list after the table name, then an error is returned (SQLCODE -2918). This option does not apply to the hidden RANDOM_DISTRIBUTION_KEY column of a random distribution table using the random by generation method

RADIX POINT IMPLIED

The location of an implied decimal point is determined by the column definition; it is no longer assumed to be at the end of the value. For example, the value 12345 is ingested into a DECIMAL(8,2) column as 123.45, not 12345.00. The PACKED decimal option and the RADIX POINT option are not compatible (SQLCODE -3525).

This parameter is equivalent to the implieddecimal modifier on the LOAD and IMPORT commands.

When the field represents a DECIMAL but is not defined in the field-list, then the parameter RADIX POINT IMPLIED does not behave as desired. See field-type DECIMAL below for the default precision and scale.

DELIMITER PRIORITY CHAR

The current default priority for delimiters is record delimiter, character delimiter, column delimiter. This modifier protects existing applications that depend on the older priority by reverting the delimiter priorities to character delimiter, record delimiter, column delimiter.

For example, assume the following delimited data file:

"Smith, Joshua",4000,34.98<row delimiter>
"Vincent,<row delimiter>, is a manager", ...
... 4005,44.37<row delimiter>

With DELIMITER PRIORITY CHAR specified, this data file has only two rows. The second <row delimiter> is interpreted as part of the first data column of the second row. The first and the third <row delimiter> are interpreted as actual record delimiters. If DELIMITER PRIORITY CHAR specified is not specified, this data file has three rows; each delimited by a <row delimiter>.

This parameter is equivalent to the delprioritychar modifier on the LOAD and IMPORT commands.

POSITIONAL input is not compatible (SQLCODE -3525).

NO CHAR DELIMITER

When you use the INGEST utility, all bytes that are found between the column delimiters are considered to be part of the column's data. Character delimiters are parsed as part of column data. Do not specify this option if the data was exported by using a Db2 database system (unless the nochardel modifier was specified during export). The nochardel modifier is provided to support vendor data files that do not have character delimiters. Improper usage might result in data loss or corruption. The DELIMITER PRIORITY CHAR and OPTIONALLY ENCLOSED BY parameters are not compatible (SQLCODE -3525).

This parameter is equivalent to the nochardel modifier on the LOAD and IMPORT commands.

field-list

Specifies the list of field definitions. If the list is omitted, it defaults as described in Default field definition list.

$field-name

Field names can be from 2 to 129 bytes in length (including the dollar sign) and follow the same rules as SQL identifiers. You can also specify delimited field names by specifying a dollar sign followed by a delimited name, for example, $"My Field Name".

If the SQL statement specifies a table name column name that starts with a dollar sign, specify the table name or column name as a delimited identifier. For example, the ingest utility interprets the following identifiers as indicated:

Table 1. The meaning of field name identifiers

Identifier	Meaning
$name	field "$NAME"
$NAME	field "$NAME"
$"My Name"	field "$My Name"
"$name"	column "$name" (in lowercase)
"$NAME"	column "$NAME" (in upper case)
"$My Name"	column "$My Name"

If you specify the INGEST command on the system command line in UNIX, in order to avoid the field names being seen as environment variables, you need to put the command in single quotation marks. For more information, see Using INGEST on the system command line (UNIX).

POSITION (start:end)

This clause is valid only when the format is POSITIONAL. It specifies the starting position and optionally the ending position of the field within the record. The units are bytes. The first byte in the record is at position 1. If the ending position is specified, it must be greater than or equal to the starting position.

Positions specified on different fields can overlap. For example, if the full name of an American state is in positions 1 - 10, you could define the following two fields:

• $state_full_name POSITION(1:10)
• $state_abbreviation POSITION(1:2)

If there is data outside the specified field positions, the ingest utility ignores it. For example, if the field definition list specifies two fields at POSITION(1:10) and POSITION(21:30), the ingest utility ignores data in positions 11:20 and any data past position 30 to the end of the record.

If the POSITION clause is omitted, the starting position is assumed to be the next byte following the previous field, or position 1 if this is the first field. If the ending position is omitted, the utility uses a default or issues an error, as described in Rules and defaults for field lengths.

When the field definition does not specify a length, the ingest utility uses a length of end_position - start_position + 1. This length is subject to the following restrictions:

• Fields of type SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, FLOAT, and DECFLOAT with the EXTERNAL modifier have a maximum length of 50.
• If a format string is specified for fields of type DATE, TIME, and TIMESTAMP(p), the field length must be greater than or equal to length of the shortest value that matches the format string
• If a format string is not specified:
  • For DATE, the field length must be between 8 and 10, inclusive.
  • For TIME, the field length must be between 4 and 8, inclusive.
  • For TIMESTAMP, the field length must be between 19 and 32, inclusive.

field-type

Specifies the data type of the field.

CHARACTER (length) / BINARY (length)

Character data or binary data.

The code page is specified by the INPUT CODEPAGE parameter. The default is the application code page.

For information about the default length, see Rules and defaults for field lengths.

NOTRIM, LTRIM, RTRIM, TRIM

Specifies that leading blanks (LTRIM), trailing blanks (RTRIM), or both leading and trailing blanks (TRIM) in the field and not enclosed by the string delimiter are not part of the string. In the case of binary strings, LTRIM, RTRIM, and TRIM specify hexadecimal zeros are not part of the string. When the format is DELIMITED, the default is TRIM. When the format is POSITIONAL, the default is NOTRIM. If this parameter is specified on any character field, it must have the same value on all other character fields.

OPTIONALLY ENCLOSED BY char

This clause is valid only when the format is DELIMITED.

The string can be enclosed by the specified character. The restrictions are the same as for the field delimiter. In addition, the character cannot be the same as the field delimiter or the decimal point. If this parameter is specified on any character, binary, date, time, or timestamp field, it must have the same value on all other character, binary, date, time, and timestamp fields.

The utility uses the following algorithm to determine whether the value starts with the delimiter:

• If the TRIM or LTRIM option is specified (or defaults), the utility tests the first character in the field after trimming leading blanks (in the case of character strings) or hexadecimal zeros (in the case of binary strings).
• If the RTRIM or NOTRIM option is specified, the utility tests the first character in the field. (In other words, when RTRIM or NOTRIM is specified, the utility does not skip over leading blanks to locate the string delimiter.)

Except for record delimiters, the utility considers all characters between the enclosing characters to be part of the string. If the string contains the enclosing character, it must be doubled inside the string. For example, if the string is don't know and the enclosing character is a single quote, specify 'don''t know'. If the string does not end with the enclosing character, the utility considers the opening delimiter to be part of the string.

The utility issues warning SQL3114W under the following conditions:

• TRIM or RTRIM is specified (or defaults) and there are extra non-blank characters between the closing delimiter and the next field delimiter (or end of record).
• NOTRIM or LTRIM is specified and there are extra characters (including blanks) between the closing delimiter and the next field delimiter (or end of record).

When this parameter is not specified, the default is OPTIONALLY ENCLOSED BY '"'. In that case, if the value does not start with a double quote, the utility considers any character (other than the field delimiter) around the string to be part of the string.

FOR BIT DATA

Note: This option is applicable only for CHARACTER data type.

Specifies that the field contains bit data and that the ingest utility is not to convert it from the input data code page to the ingest utility application code page. If this field is used to assign to a column that specifies FOR BIT DATA, it is strongly recommended that the field definition also specify FOR BIT DATA. Otherwise, the value assigned to the column is unpredictable, because of how the ingest utility sometimes optimizes code page conversion.

INTEGER/SMALLINT/BIGINT EXTERNAL (length)

A character string of up to length bytes representing a number. The string can be an integer, decimal, or floating point constant. If the string specifies a decimal or floating point value, the ingest utility converts it to the specified integer type by truncating the fractional part. If the string is not a valid number, or the number is out of range, the ingest utility issues an error message and rejects the record. For more information, see Handling of invalid numeric data.

Use EXTERNAL with numeric field types to indicate that the field value is specified as ASCII characters rather than in binary. If EXTERNAL is specified on any integer field, it must be specified on all other integer, decfloat, and float fields.

For information about default length, see Rules and defaults for field lengths.

DECIMAL (precision, scale) EXTERNAL (length)

A character string of up to length bytes representing a decimal number. The string can be an integer, decimal, or floating point constant. The ingest utility converts it to a decimal value with the specified precision and scale. If the string specifies a decimal value with a scale larger than the scale specified on the field type, the ingest utility truncates the fractional part of the value with no warning or error. If the string specifies a decimal value with a scale smaller than the scale specified on the field type, the ingest utility pads the fractional part of the value with zeros. If the string is not a valid number, or the number is out of range (other than the scale), the ingest utility issues an error message and rejects the record. For more information, see Handling of invalid numeric data.

The precision can range from 1 to 31. The scale can range from 0 to the precision. The default is (5,0). For more information aboutdefault length, see Rules and defaults for field lengths.

If EXTERNAL is specified on any decimal field, it must be specified on all other decimal fields.

DECFLOAT (16|34) EXTERNAL (length)

A character string of up to length bytes representing a decimal floating point number. The string can be an integer, decimal, or floating point constant, or a decimal floating point special value. The ingest utility converts it to a decimal floating point value of the specified precision. If the string is not a valid number or decimal floating point special value, or the number is out of range, the ingest utility issues an error message and rejects the record. For more information, see Handling of invalid numeric data. The precision can be either 16 or 34 (the default).

For more information about the default length, see Rules and defaults for field lengths.

If EXTERNAL is specified on any decfloat field, it must be specified on all other integer, decfloat, and float fields.

FLOAT(integer) EXTERNAL(length)

A character string of up to length bytes representing a floating point number. The string can be an integer, decimal, or floating point constant. If the string is not a valid number, or the number is out of range, the ingest utility issues an error message and rejects the record. For more information, see Handling of invalid numeric data.

The value of the integer must be in the range 1 through 53. Values 1 through 24 indicate single precision, and values 25 through 53 indicate double precision. The default is 53. You can also specify:

REAL

For single-precision floating-point.

DOUBLE, DOUBLE PRECISION, or FLOAT

For double-precision floating-point.

If EXTERNAL is specified on any float field, it must be specified on all other integer, decfloat, and float fields.

For information about the default length, see Rules and defaults for field lengths.

RADIX POINT char

The character to use as the decimal point. The restrictions are the same as those for the field delimiter. In addition, the character cannot be the same as the field delimiter or the string delimiter. If this parameter is specified on any integer, decimal, decfloat, or float field, it must have the same value on all other integer, decimal, decfloat, and float fields. The default is a dot ..

INTEGER, SMALLINT, or BIGINT

An integer of the specified type in binary. These types can be specified only when the format is POSITIONAL and the RECORDLEN parameter specified. The following can also be specified:

BIG | LITTLE ENDIAN

Specifies whether the integer is in big endian format (most significant byte at low address) or little endian format (least significant byte at low address). This option applies to integer, float, and decfloat types, but not decimal types. The default is the default format on the current hardware platform where the ingest utility is running.

DECIMAL (precision, scale) PACKED | ZONED

A decimal number of the specified precision and scale in packed or zoned format. These types can be specified only when the format is POSITIONAL and the RECORDLEN parameter is specified. The precision can range from 1 to 31. The scale can range from 0 to the precision. The default is (5,0). The PACKED/ZONED setting must be the same on all other decimal fields. The default is PACKED.

DECFLOAT(16|34)

A decimal floating point number of the specified precision in binary. The precision can be either 16 or 34 (the default). This type can be specified only when the format is POSITIONAL and the RECORDLEN parameter is specified.

FLOAT(integer)

A single- or double-precision floating point number of the specified size in binary (IEEE floating point format). The value of the integer must be in the range 1 through 53. Values 1 through 24 indicate single precision and values 25 through 53 indicate double precision. The default is 53. These types can be specified only when the format is POSITIONAL and the RECORDLEN parameter is specified. You can also specify:

REAL

For single-precision floating-point.

DOUBLE, DOUBLE PRECISION, or FLOAT

For double-precision floating-point.

DATE, TIME, TIMESTAMP

A date, time, or timestamp specified as a string. If the database has thedate_compat database configuration parameter set to ON, DATE is equivalent to TIMESTAMP(0).

For information about the default length, see Rules and defaults for field lengths.

precision

Specifies the precision of a timestamp. The range is 0 - 12. The default is 6. If the input data specifies a timestamp with a precision larger than the precision specified on the field type, the ingest utility truncates the fractional part of the timestamp with no warning or error (provided that the timestamp has the format specified on the format string). If the input data specifies a timestamp with a precision smaller than the precision specified on the field type, the ingest utility pads the fractional part of the timestamp with zeros.

string

Specifies the format of the date, time, or timestamp value. The elements of the format string are those currently supported by the IMPORT and LOAD commands. For more information, see the descriptions of the dateformat, timeformat, and timestampformat file type modifiers in the IMPORT command topic.

If the format is POSITIONAL and the string is longer than the length specified by the POSITION parameter, the ingest utility issues an error. For example, if the string is 'yyyy-mm-dd' (10 bytes), but the position is 1:6, the ingest utility issues an error.

The date, time, or timestamp value in the input data must match the format, except that you can omit sub-fields on the right. For example, if the timestamp format is "yyyy/mm/dd hh:mm:ss.uuu", the following timestamps are valid:

2010/06/03 12:34  
2010/06/03 12:34:56  
2010/06/03 12:34:56.1  
2010/06/03 12:34:56.12  
2010/06/03 12:34:56.123 

However, "2010/06/03 12:34:56.1234" is not valid. When you omit sub-fields, they default as described in the descriptions of the dateformat, timeformat, and timestampformat file type modifiers in the IMPORT command topic.

There is one exception to the previous description of omitting sub-fields: When the format is POSITIONAL and the field is the last field in the record and the record ends before the field's end position, the utility issues a warning and uses NULL for the value.

If the date, time, or timestamp value is not in the specified format (other than sub-fields missing from the right), the ingest utility issues an error message and rejects the row.

The following rules also apply

• If this parameter is specified on any DATE field, it must also be specified and have the same value on all other DATE fields. If the database has thedate_compat database configuration parameter on (in which case, field type DATE is equivalent to TIMESTAMP(0)), it must also be specified and have the same value on all other TIMESTAMP fields.
• If it is specified on any TIME field, it must also be specified and have the same value on all other TIME fields.
• If it is specified on any TIMESTAMP field, it must also be specified and have the same value on all other TIMESTAMP fields. If the database has thedate_compat database configuration parameter on (in which case, field type DATE is equivalent to TIMESTAMP(0)), it must also be specified and have the same value on all other DATE fields.

If the format string is not specified, the ingest utility recognizes various formats, as shown in Table 2.

Table 2. Default values for DATE, TIME, and TIMESTAMP

Field type	Default format
DATE	If thedate_compat database configuration parameter is set to OFF, then: 'yyyymmdd' (delimited format only) 'yyyy-mm-dd' 'mm/dd/yyyy' 'dd.mm.yyyy' Note: In all but the first format, the month and day can also be specified as a single digit, for example, 'yyyy-m-d'. If thedate_compat database configuration parameter is set to ON, then the same defaults as TIMESTAMP(0).
TIME	'hh.mm.ss' 'hh:mm tt' 'hh:mm:ss'
TIMESTAMP	'yyyy-mm-dd-hh.mm.ss.uuuuuuuuuuuu' or 'yyyy-mm-dd hh:mm:ss.uuuuuuuuuuuu' 'yyyy-mm-dd-hh.mm.ss.uuuuuuuuuuuu' 'yyyy-mm-dd hh:mm:ss.uuuuuuuuuuuu' 'yyyy-mm-dd' 'mm/dd/yyyy' 'dd.mm.yyyy' 'yyymmdd' (delimited format only) Note: In all but the first format, the month and day can also be specified as a single digit, for example, 'yyyy-m-d-hh.mm.ss.uuuuuuuuuuuu''.

OPTIONALLY ENCLOSED BY char

This clause is valid only when the format is DELIMITED.

The string can be enclosed by the specified character. The restrictions are the same as for the field delimiter. In addition, the character cannot be the same as the field delimiter or the decimal point. If this parameter is specified on any character, binary, date, time, or timestamp field, it must have the same value on all other character, binary, date, time, and timestamp fields.

The utility uses the following algorithm to determine whether the value starts with the delimiter:

• If the TRIM or LTRIM option is specified (or defaults), the utility tests the first character in the field after trimming leading blanks (in the case of character strings) or hexadecimal zeros (in the case of binary strings).
• If the RTRIM or NOTRIM option is specified, the utility tests the first character in the field. (In other words, when RTRIM or NOTRIM is specified, the utility does not skip over leading blanks to locate the string delimiter.)

Except for record delimiters, the utility considers all characters between the enclosing characters to be part of the string. If the string contains the enclosing character, it must be doubled inside the string. For example, if the string is don't know and the enclosing character is a single quote, specify 'don''t know'. If the string does not end with the enclosing character, the utility considers the opening delimiter to be part of the string.

The utility issues warning SQL3114W under the following conditions:

• TRIM or RTRIM is specified (or defaults) and there are extra non-blank characters between the closing delimiter and the next field delimiter (or end of record).
• NOTRIM or LTRIM is specified and there are extra characters (including blanks) between the closing delimiter and the next field delimiter (or end of record).

When this parameter is not specified, the default is OPTIONALLY ENCLOSED BY '"'. In that case, if the value does not start with a double quote, the utility considers any character (other than the field delimiter) around the string to be part of the string.

BOOLEAN

A Boolean value.

For Boolean columns, the supported values during data ingest are value 1 or value 0 only.

DB2SECURITYLABEL (length)

Specifies a Db2 security label. If neither NAME nor STRING is specified and the format is POSITIONAL, the default format is encoded numeric format; otherwise an error is returned.

For information about the default length, see Rules and defaults for field lengths.

NAME

The Db2 security label is specified by its name. If the format is DELIMITED, either NAME or STRING must be specified. If this parameter is specified on any DB2SECURITYLABEL field, it must have the same value on all other DB2SECURITYLABEL fields. If no security label exists with the indicated name for the security policy protecting the table, the row is not loaded and a warning is returned.

STRING

The Db2 security label is specified in string format. If the format is DELIMITED, either NAME or STRING must be specified. If this parameter is specified on any DB2SECURITYLABEL field, it must have the same value on all other DB2SECURITYLABEL fields. If a string is not in the proper format, the row is not loaded and a warning is returned. If the string does not represent a valid security label that is part of the security policy protecting the table, the row is not loaded and a warning is returned.

The default is encoded numeric format and is allowed only when the format is POSITIONAL.

DEFAULTIF = NULL

Specifies that if the field is empty, then the utility considers the field value to be the default value of the table column to which the field is assigned. There must be at least one space between the DEFAULTIF keyword and the equal sign. A field is considered empty if any of the following conditions are true:

• When the input format is DELIMITED, and any of the following conditions are true:
  • There are two adjacent delimiters.
  • There are two delimiters with only blanks between them and the NOTRIM option is not specified.
  • The record ends at the end of a previous field.
• When the input format is POSITIONAL and the record ends at the end of a previous field.

This parameter is equivalent to the usedefaults modifier on the LOAD and IMPORT commands.

DEFAULTIF (position) = char

Specifies a input value for a column in the input file to convert to the database table column's default value. The position is optional and can be specified only if the format is POSITIONAL. If the position is specified, the utility reads the character from the input record before applying any specified or default trim option. The position does not have to be in the field. If the position is omitted, the utility first applies any specified or default trim option and then reads the first character of the field. In either case, if the character read from the input record matches the one specified, the utility considers the field value to be the default value of the table column to which the field is assigned.

The DEFAULTIF parameter has the following restrictions:

• When the position is omitted, there must be at least one space between DEFAULTIF and the equal sign. Otherwise a syntax error occurs.
• The DEFAULTIF character can be any single-byte character except the following values:
  • the null character (X'00')
  • carriage return or line feed (X'0D' or X'0A')
  If the input data code page is a DBCS or EUC code page, the character must be in the range X'01' to X'3F', inclusive. If the input data code page is not a single-byte code page, the character must be in the range X'01' to X'7F', inclusive. If the input code page is a single-byte code page, the character can be in the range X'01' to X'FF'.
• The field must be assigned to only one column and cannot be part of an expression that is assigned to that column. For example, consider the following INGEST command:

  INGEST FROM FILE ...     
      UPDATE my_table  
          SET (c1, c2, c3, c4, c5) =           
              ($field1, $field2, $field2, $field3, $field4+$field5)
          WHERE $field3 = 1;

  Only $field1 can specify DEFAULTIF. $field2 cannot because it is assigned to multiple columns. $field3 cannot because it is also used in a predicate. $field4 and $field5 cannot because they are used in an expression.
• The default value of the table column must be a constant or NULL. It cannot be a special register.
  Note: If a column is nullable (that is, NOT NULL is not specified on the column definition) and the WITH DEFAULT clause is not specified on the column definition, the column still has a default value of NULL.
• If the table column type differs from the field type, the ingest utility converts the default value to the field type, applying the same rules used to convert input data to the field type. For example, if the table column is defined as FLOAT WITH DEFAULT 3.7 and the field is defined as INTEGER DEFAULTIF=..., the ingest utility uses a default value of 3. Similarly, if the length of the table column's default value is greater than the field length, the utility truncates the default value to the field length.

If these restrictions are violated, the ingest utility issues an error and the INGEST command ends.

Examples

Usage notes

• INGEST operations with a remote storage object specified, when performed on a remote client host (different from the database server host) do not support the COS SDK, and will silently use the legacy libcurl method (subject to its known limitations). See DB2_ENABLE_COS_SDK.

Using INGEST on the system command line (UNIX)

If you specify the INGEST command on the system command line in a UNIX command shell, you must specify field names in single quotation marks or put an escape character (backslash) before the $ that starts the field name. Otherwise, the command shell interprets the field names as environment variables and replace them with the value of the corresponding environment variable.

db2 INGEST ... FORMAT DELIMITED ($field1 INTEGER, $field2 CHAR) ...

INGEST ... FORMAT DELIMITED (abc INTEGER, CHAR) ...

db2 'INGEST ... FORMAT DELIMITED ($field1 INTEGER, $field2 CHAR) ...'

db2 "INGEST ... FORMAT DELIMITED BY X'7C' (\$field1 ...) ..."

The simplest way to avoid all the previously mentioned problems is to put the command in a file and specify the -f option on the db2 command.

Commit frequency

Categories of error

Messages from the INGEST command

INGEST FROM FILE my_table.del FORMAT DELIMITED
   ($field1 INTEGER EXTERNAL, $field2 CHAR(32))
   INSERT INTO my_table VALUES($field1, $field2)
Number of rows read         = 6
Number of rows inserted     = 6
Number of rows rejected     = 0
SQL2980I The ingest utility completed successfully at timestamp "11/02/2011 12:34:56.123456".

INGEST FROM FILE bad_file.del FORMAT DELIMITED     
    ($field1 INTEGER EXTERNAL, $field2 CHAR)     
    INSERT INTO my_table VALUES($field1, $field2)  

SQL2036N  The path for the file or device "bad_file.del" is not valid. 

INGEST FROM FILE my_table1.del, my_table2.del FORMAT DELIMITED
   ( $field1 integer external, $field2 char(32) )
   INSERT INTO my_table VALUES($field1, $field2)

SQL2905I  The following error occurred issuing the SQL "INSERT" 
statement on table "ALVEYWD.MY_TABLE" using data from line "6" of 
input file "my_table1.del".

SQL0803N  One or more values in the INSERT statement, UPDATE statement, 
or foreign key update caused by a DELETE statement are not valid 
because the primary key, unique constraint or unique index identified 
by "1" constrains table "ALVEYWD.MY_TABLE" from having duplicate values 
for the index key.  SQLSTATE=23505


SQL2905I  The following error occurred issuing the SQL "INSERT" statement 
on table "ALVEYWD.MY_TABLE" using data from line "9" of input file "my_table2.del".

SQL0803N  One or more values in the INSERT statement, UPDATE statement, 
or foreign key update caused by a DELETE statement are not valid because 
the primary key, unique constraint or unique index identified by "1" 
constrains table "ALVEYWD.MY_TABLE" from having duplicate values 
for the index key.  SQLSTATE=23505

Number of rows read         = 6
Number of rows inserted     = 4
Number of rows rejected     = 2
Number of rows committed    = 4

SQL2902I The ingest utility completed at timestamp "11/02/2011 12:34:56.123456". 
Number of errors: 2. Number of warnings: 0.  

If the MESSAGES parameter is specified, most messages are written to that file, except as specified in the description of that parameter.

File and pipe names

'dir1/dir2/my=file'

dir1/dir2/'my=file'

Default field definition list

Rules and defaults for field lengths

Field lengths specified on field types are in bytes and must be 1 - 32 767, inclusive.

How the ingest utility determines field lengths

Handling of invalid numeric data

Notes regarding the FORMAT clause

SQL statements on the INGEST command

• Restrictions that apply to the use of the INGEST command with column-organized tables are the same restrictions that apply to the use of INSERT, UPDATE, or DELETE statements with column-organized tables.
  • You cannot specify RR or RS for the WITH isolation-level clause, which you can specify for the UPDATE and DELETE statements that are part of the INGEST command.
• The ingest utility supports the following values in the PERIOD clause:
  • constants
  • special registers
    Note: Only the following special registers affect the execution of the SQL statement on the INGEST command:

    • CURRENT SCHEMA
    • CURRENT TEMPORAL SYSTEM_TIME
    • CURRENT TEMPORAL BUSINESS_TIME
  • scalar functions whose arguments are supported operands (although nested function invocations and user-defined functions cannot be used)
  • CAST specifications where the cast operand is a supported operand expression using arithmetic operators and operands
  • expressions using arithmetic operators and operands
  You can also specify a field name, which the utility converts to a parameter marker.
• There are several other considerations for ingesting into a temporal table:
  • When special register CURRENT TEMPORAL SYSTEM_TIME is set, you cannot ingest into a system-period temporal table or a bi-temporal table.
  • When special register CURRENT TEMPORAL BUSINESS_TIME is set, you can still ingest into a business-time temporal table or a bi-temporal table (subject to the other restrictions on system-time temporal tables).
  • You cannot use the REPLACE or DELETE operation on a system-period temporal table or bi-temporal table.
  • The ingest utility cannot insert values for the SYSTEM_TIME period in a system-period temporal table.
• Any strings in the SQL statements are assumed to be in the application code page.
• Any column names that start with a dollar sign ($) must be specified as SQL delimited identifiers (enclosed in double quotation marks), in order to distinguish them from field names.
•  In most cases, after converting field names to parameter markers and binding in values for the fields, the ingest utility passes these statements as is to Db2. They are therefore subject to the same restrictions. For example:
  •  Fields are bound as their field type. Therefore they can be used only where a value of the specified type is allowed. For example, fields of type DB2SECURITYLABEL can be used only where a security label is allowed.
  • If the INSERT statement omits the column list but specifies the VALUES list, the VALUES list must contain an item for each column. If the table contains implicitly hidden columns, then you must explicitly specify whether or not the implicitly hidden columns are included. Use the DB2_DMU_DEFAULT registry variable, or the IMPLICITLYHIDDENINCLUDE or IMPLICITLYHIDDENMISSING keywords to specify if implicitly hidden columns are included.
• The ingest utility issues an error and ends the INGEST command if any of the following conditions is true:
  • If the SQL statement specified on the command does not reference any fields
  • The SQL statement is DELETE or UPDATE and it has no WHERE clause or the WHERE clause does not reference any fields.
  • The SQL statement is MERGE and the ON clause does not reference any fields.
• The default isolation level is cursor stability (CS). This can be overridden by specifying the WITH clause on the SQL statement (except INSERT), or by setting the CLI IsolationLevel keyword in the file specified by the DB2CLIINIPATH environment variable (the default is sqllib/cfg/db2cli.ini). Setting the IsolationLevel keyword, however, affects all CLI applications.

The MERGE statement

The INGEST command issues the MERGE statement once for each input data record, treating the record as a one-row table (equivalent to the USING table-reference parameter on the SQL MERGE statement). This can occasionally produce different results than the equivalent SQL MERGE statement. Consider the following example:

• Table SOURCE_TABLE contains two rows:

  (1, 'source data 1')  
  (2, 'source data 2') 

• Input file source_table.del contains the same two records as in SOURCE_TABLE.
• Table TARGET_TABLE contains one row:

  (NULL, 'target data 1')

The SQL MERGE statement is:

MERGE INTO target_table
   USING (SELECT * FROM source_table) source_table
   ON target_table.c1 <> source_table.c1
   WHEN NOT MATCHED THEN
      INSERT VALUES(source_table.c1, source_table.c2);

The INGEST command (using the equivalent MERGE statement) is:

INGEST FROM FILE source_table.del
     FORMAT DELIMITED
     (
        $c1 INTEGER EXTERNAL,
        $c2 CHAR(32)
     )
     MERGE INTO target_table
        ON c1 <> $c1
        WHEN NOT MATCHED THEN
           INSERT VALUES($c1, $c2); 

Following the SQL MERGE statement, TARGET_TABLE contains the following rows:

(NULL, 'target data 1')
(1, 'source data 1')
(2, 'source data 2')

Following the INGEST command, TARGET_TABLE contains the following rows:

(NULL, 'target data 1')
(1, 'source data 1')

The reason the INGEST command results in fewer rows is that after the INGEST command processes the first input data record, the target table contains the two rows shown previously. From that point onwards, the ON clause has a matching row, which means the INSERT statement on the WHEN NOT MATCHED clause is not processed again.

Note that this example is not a common use of the MERGE statement. When the MERGE statement has the more common format of updating or inserting rows based on matching keys, the SQL MERGE statement and the INGEST command produce the same results.

The REPLACE statement

REPLACE is basically the same as INSERT except that the ingest utility issues the DELETE statement to delete all the table rows before beginning the inserts. If the table is large, the DELETE could take some time and use significant log space.

Data type conversion

For each field the utility ingests, there are two places that data conversion can occur:

• When the input data is in character format (including numbers specified in ASCII), the utility converts the data from character to the field type.
• If a field's type differs from its corresponding column type, Db2 converts the field value from the field type to the column type.

In both cases, the ingest utility and Db2 use the rules described in the topic Assignments and comparisons. This can occasionally produce different results than converting directly from character to the column type. Consider the following example:

• The input file specifies string '9.99E6143', which is a valid DECFLOAT(34) number but outside the range of DECFLOAT(16).
• The field type is DECFLOAT(34).
• The column type is DECFLOAT(16).

In this example, the ingest utility converts the field value from character to DECFLOAT(34). Then Db2 converts from DECFLOAT(34) to DECFLOAT(16). That results in a warning and a value of DECFLOAT infinity in the DECFLOAT(16) column.

Casting a field to an SQL data type

Before issuing the SQL statement that you specify on the INGEST command, the ingest utility converts field names to parameter markers and then issues the SQL statement dynamically. In order to determine the data types of the parameter markers, Db2 uses the rules described in the topic Determining data types of untyped expressions. Just as with dynamic SQL, if the data type of a field is not obvious from the context where it is used, you might get an error, in which case you need to do perform one of the following actions:

• Set the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to YES.
• Use a CAST specification to cast the field name to an SQL data type.

For example, consider the following INGEST command:

INGEST FROM FILE my_file.txt  
   FORMAT DELIMITED ( $date_fld DATE )
   INSERT INTO my_table(int_col) VALUES(day($date_fld));

If you have the DB2_DEFERRED_PREPARE_SEMANTICS registry variable set to NO (the default), when the ingest utility tries to issue the statement, it might return an error because Db2 cannot determine which version of the DAY function to use. (This is true even though you defined the field as a DATE and the ingest utility binds it as a DATE.)

You can fix the problem by casting field names to their corresponding field types, for example:

INSERT INTO my_table(int_col) VALUES( day(CAST($date_fld AS DATE)) );

In some cases, you can also fix the problem by setting registry variable DB2_DEFERRED_PREPARE_SEMANTICS=YES. However, this does not always work.

As an additional example, consider the following INGEST command:

INGEST FROM FILE my_file.txt
   FORMAT DELIMITED ( $field1 DECIMAL(5,2) )
   INSERT INTO my_table(dec_col) VALUES($field1 + 1);

Because $field1 is added to an integer, Db2 assigns type INTEGER to the field. In order to have Db2 assign type DECIMAL(5,2), you need to change the SQL statement to:

INSERT INTO my_table(dec_col) VALUES(CAST($field1 AS DECIMAL(5,2)) + 1);

or

INSERT INTO my_table(dec_col) VALUES($field1 + 001.00);