Inserts data from an input file or pipe into a DB2 table.
Authorization
The authorization
ID that is used to connect to the database must hold the following
authorities and privileges:
- 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 SELECT, INSERT, UPDATE, and DELETE privileges are required on
the restart table.
- If the INGEST command specifies the RESTART
TERMINATE option, then SELECT and DELETE privileges are required
on the restart table.
- If the INGEST command specifies the EXCEPTION
TABLE option, then INSERT privileges are required on the
exception table.
In addition, the SQL statement on the INGEST command
is subject to the same row and column access control (RCAC) that it
would be if you accessed the table without using the ingest utility.
Required connection
Database
API include file
db2ApiDf.h
API and data structure syntax
SQL_API_RC SQL_API_FN db2Ingest (
db2Uint32 versionNumber,
void *pIngestStruct,
struct sqlca *pSqlca);
typedef SQL_STRUCTURE db2IngestCfgParam
{
db2Uint32 iCfgParam;
db2Uint32 iCfgParamValue;
} db2IngestCfgParam;
typedef SQL_STRUCTURE db2IngestCfgList
{
db2IngestCfgParam *piCfgParam;
db2Uint32 iNumCfgParams;
} db2IngestCfgList;
typedef SQL_STRUCTURE db2IngestFieldDefn
{
db2Char *piDatetimeFormat;
db2Char *piName;
db2Uint32 iStartPos;
db2Uint32 iEndPos;
db2Uint32 iLength;
db2Uint32 iExternal;
db2Uint32 iDecimalFormat;
db2Uint32 iScale;
db2Uint32 iPrecision;
db2Uint32 iSecLabelFormat;
db2Uint32 iEndian;
db2Uint32 iForBitData;
db2Uint32 iTrim;
db2Uint32 iDefaultIfPos;
db2Uint16 iType;
char iDefaultIfChar;
char iEnclosedBy;
char iRadixPoint;
} db2IngestFieldDefn;
typedef SQL_STRUCTURE db2IngestFormat
{
struct db2IngestFieldDefn *piFieldDefn;
db2Char *piFileType;
db2Uint32 iRecordLength;
db2Uint32 iNumFields;
db2Uint16 iCodepage;
db2Uint16 iImplicitlyHidden;
char iDelimiter;
} db2IngestFormat;
typedef SQL_STRUCTURE db2IngestOut
{
db2Uint64 oRowsRead;
db2Uint64 oRowsSkipped;
db2Uint64 oRowsInserted;
db2Uint64 oRowsUpdated;
db2Uint64 oRowsDeleted;
db2Uint64 oRowsMerged;
db2Uint64 oRowsRejected;
db2Uint64 oNumErrors;
db2Uint64 oNumWarnings;
db2Uint64 oMaxMsgSeverity;
} db2IngestOut;
typedef SQL_STRUCTURE db2IngestStruct
{
struct db2IngestCfgList *piCfgList;
struct sqlu_media_list *piSourceList;
struct db2IngestFormat *piFormat;
struct db2IngestOut *poIngestInfoOut;
db2Char *piDumpFile;
db2Char *piExceptTableName;
db2Char *piMsgFileName;
db2Char *piJobId;
db2Char *piSqlStatement;
db2Uint32 iWarningcount;
db2Uint32 iRestartMode;
} db2IngestStruct;
/* Possible values for field "iCfgParam". */
#define DB2INGEST_CFG_COMMIT_COUNT 1
#define DB2INGEST_CFG_COMMIT_PERIOD 2
#define DB2INGEST_CFG_NUM_FLUSHERS_PER 3
#define DB2INGEST_CFG_NUM_FORMATTERS 4
#define DB2INGEST_CFG_PIPE_TIMEOUT 5
#define DB2INGEST_CFG_RETRY_COUNT 6
#define DB2INGEST_CFG_RETRY_PERIOD 7
#define DB2INGEST_CFG_SHM_MAX_SIZE 8
/* Possible values for field "iSecLabelFormat". */
#define DB2INGEST_SEC_FORMAT_NOT_SEC 0
#define DB2INGEST_SEC_FORMAT_ENCODED 1
#define DB2INGEST_SEC_FORMAT_NAME 2
#define DB2INGEST_SEC_FORMAT_STRING 3
/* Possible values for field "iDecimalFormat". */
#define DB2INGEST_DEC_FORMAT_DEFAULT 0
#define DB2INGEST_DEC_FORMAT_PACKED 1
#define DB2INGEST_DEC_FORMAT_ZONED 2
/* Possible values for field "iEndian". */
#define DB2INGEST_ENDIAN_DEFAULT 0
#define DB2INGEST_ENDIAN_LITTLE 1
#define DB2INGEST_ENDIAN_BIG 2
/* Possible values for field "iTrim". */
#define DB2INGEST_TRIM_DEFAULT 0
#define DB2INGEST_TRIM_NO 1
#define DB2INGEST_TRIM_LEFT 2
#define DB2INGEST_TRIM_RIGHT 3
#define DB2INGEST_TRIM 4
/* Possible values for field "iImplicitlyHidden". */
#define DB2INGEST_IMPLICIT_HID_DEFAULT 0
#define DB2INGEST_IMPLICIT_HID_MISSING 1
#define DB2INGEST_IMPLICIT_HID_INCLUDE 2
/* Possible values for field "oMaxMsgSeverity". */
#define DB2INGEST_MSGSEV_UNDEFINED 0
#define DB2INGEST_MSGSEV_INFO 1
#define DB2INGEST_MSGSEV_NO_DATA 2
#define DB2INGEST_MSGSEV_WARNING 3
#define DB2INGEST_MSGSEV_ERROR 4
#define DB2INGEST_MSGSEV_SEVERE 5
/* Possible values for field "iRestartMode". */
#define DB2INGEST_RESTART_DEFAULT 0
#define DB2INGEST_RESTART_OFF 1
#define DB2INGEST_RESTART_NEW 2
#define DB2INGEST_RESTART_CONTINUE 3
#define DB2INGEST_RESTART_TERMINATE 4
db2Ingest API Parameters
- versionNumber
- Input. Specifies the version and release level of the structure
passed in as the second parameter pIngestStruct.
- pIngestStruct
- Input. A pointer to the db2IngestStruct structure.
- pSqlca
- Output. A pointer to the sqlca structure.
db2IngestCfgParam data structure parameters
- iCfgParam
- Input. Specifies the configuration parameter. The same configuration
parameter cannot be set more than once per invocation. The possible
values are one of the following constants:
- DB2INGEST_CFG_COMMIT_COUNT
- specifies the number of rows each flusher writes in a single transaction
before issuing a commit.
- DB2INGEST_CFG_COMMIT_PERIOD
- Specifies the number of seconds between committed transactions.
- DB2INGEST_CFG_NUM_FLUSHERS_PER
- Specifies the number of flushers to allocate for each database
partition.
- DB2INGEST_CFG_NUM_FORMATTERS
- Specifies the number of formatters to allocate.
- DB2INGEST_CFG_PIPE_TIMEOUT
- specifies the maximum number of seconds to wait for data when
the input source is a pipe.
- DB2INGEST_CFG_RETRY_COUNT
- Specifies the number of times to retry a failed, but recoverable,
transaction.
- DB2INGEST_CFG_RETRY_PERIOD
- Specifies the number of seconds to wait before retrying a failed,
but recoverable, transaction.
- DB2INGEST_CFG_SHM_MAX_SIZE
- Specifies the maximum size of Inter Process Communication (IPC)
shared memory in bytes on the client machine.
- iCfgParamValue
- Input. Specifies the value of the configuration parameter. The
possible values must be between 1 and 8, inclusive.
db2IngestCfgList data structure parameters
- piCfgParam
- Input. Pointer to an array of configuration parameter settings.
If iNumCfgParams is 0, this
field must be NULL.
- iNumCfgParams
- Input. The number of elements in the array that piCfgParam points
to.
db2IngestFieldDefn data structure parameters
- piDatetimeFormat
- Input. Specifies the format string when the field type is date,
time or timestamp. If piDatetimeFormat is NULL
then the format will be set to the default format string. This parameter
must be set to NULL for all other field types.
- piName
- Input. Specifies the field name. The field names must start with
a dollar sign and can be 2 to 129 bytes in length (including the dollar
sign) and follow the same rules as SQL identifiers. Multiplication,
addition, subtraction, division, logical operator functions and user
functions that can be specified in piSqlStatement are
permitted. You can also specify delimited field names by specifying
a dollar sign followed by a delimited name, for example, $"My Field
Name". This parameter cannot be NULL.
- iStartPos
- Input. Specifies the field starting position. If the file type
is not POSITIONAL, this parameter must be set to 0 .
- iEndPos
- Input. Specifies the field ending position. If the file type is
not POSITIONAL, this parameter must be set to 0.
- iLength
- Input. Specifies the field length. This length is same as the
value of the SQLLEN field in the SQLDA for DATE, TIME, INTEGER, BIGINT,
SMALLINT, DOUBLE. To specify the default length, set this parameter
to 0. If you specify this field, it must be valid
for the data type.
- iExternal
- Input. Specifies the length after which the field will be truncated.
Valid values are TRUE and FALSE.
- iDecimalFormat
- Input. Specifies the format of the DECIMAL field. The possible
values are:
- DB2INGEST_DEC_FORMAT_DEFAULT
- Allowed only when the field type is DECIMAL. If iExternal is
TRUE, this value must be specified.
- DB2INGEST_DEC_FORMAT_PACKED
- Allowed only when the field type is DECIMAL.
- DB2INGEST_DEC_FORMAT_ZONED
- Allowed only when the field type is DECIMAL.
- iScale
- Input. Specifies the scale of a DECIMAL field. The valid values
must be between 1 and SQL_MAXDECIMAL inclusive.
- iPrecision
- Input. Specifies the precision of DECIMAL, DECFLOAT, FLOAT or
TIMESTAMP in digits.
- The valid values for a DECFLOAT field are 0, SQL_DECFLOAT16_PRECISION or SQL_DECFLOAT34_PRECISION
- The possible values for a TIMESTAMP field are SQL_STAMP_MIN_PREC to
SQL_STAMP_MAX_PREC.
- The possible values for a FLOAT field are SQL_MINSFLOATPREC to
SQL_MAXFLOATPREC.
- The possible values for a 4-byte FLOAT field are SQL_MINSFLOATPREC to
SQL_MAXSFLOATPREC.
- iSecLabelFormat
- Input. Specifies the format of a DB2SECURITYLABEL field. For more
information on DB2SECURITYLABEL, refer to the INGEST Command documentation.
The iType for this field is SQL_TYP_CHAR and
the possible values are:
- DB2INGEST_SEC_FORMAT_ENCODED
- Allowed when the field type is DB2SECURITYLABEL. If neither DB2INGEST_SEC_FORMAT_NAME nor DB2INGEST_SEC_FORMAT_STRING is
specified, the default format is encoded numeric format. This is allowed
only when the format is POSITIONAL.
- DB2INGEST_SEC_FORMAT_NAME
- Allowed when the field type is DB2SECURITYLABEL. The DB2 security
label is specified by its name. If the format is DELIMITED, either DB2INGEST_SEC_FORMAT_NAME or DB2INGEST_SEC_FORMAT_STRING must
be specified. If this parameter is specified on any DB2SECURITYLABEL
field, it must have the same value on all other DB2SECURITYLABEL fields.
- DB2INGEST_SEC_FORMAT_STRING
- Allowed when the field type is DB2SECURITYLABEL. The DB2 security
label is specified in string format. If the format is DELIMITED, either DB2INGEST_SEC_FORMAT_NAME or DB2INGEST_SEC_FORMAT_STRING must
be specified. If this parameter is specified on any DB2SECURITYLABEL
field, it must have the same value on all other DB2SECURITYLABEL fields.
- DB2INGEST_SEC_FORMAT_NOT_SEC
- Specified when the field is not a DB2SECURITYLABEL.
- iEndian
- Input. Specifies the endian format when the field type is binary
numeric but not decimal. If you use a different field type or if the
field format is EXTERNAL, you must set this parameter to 0.
The possible values are:
- DB2INGEST_ENDIAN_DEFAULT
- The default format on the current hardware platform where the
ingest utility is running.
- DB2INGEST_ENDIAN_LITTLE
- Specifies the field in little endian format (least significant
byte at low address).
- DB2INGEST_ENDIAN_BIG
- Specifies the field in big endian format (most significant byte
at low address).
- iForBitData
- Input. Specifies if codepage conversion should be executed or
not. Valid values are TRUE or FALSE.
If the field type is CHAR, this parameter must be set to the value
of TRUE to skip codepage conversion.
- iTrim
- Specifies how the leading blanks, trailing blanks or both leading
and trailing blanks are handled in a string. When the field type is
CHAR the possible values are:
- DB2INGEST_TRIM_DEFAULT
- The default format when the format is DELIMITED. Specifies that
both leading and trailing blanks in the field that are not enclosed
by the string delimiter are not part of the string.
- DB2INGEST_TRIM_NO
- Specifies that the leading and trailing blanks are ignored. This
is the default value when the format is POSITIONAL.
- DB2INGEST_TRIM_LEFT
- Specifies that leading blanks in the field that are not enclosed
by the string delimiter are not part of the string.
- DB2INGEST_TRIM_RIGHT
- Specifies that trailing blanks in the field that are not enclosed
by the string delimiter are not part of the string.
- DB2INGEST_TRIM
- Specifies that both leading and trailing blanks in the field that
are not enclosed by the string delimiter are not part of the string.
For other field types, the value of this parameter
must be set to 0.
- iDefaultIfPos
- Specifies the position in the input record when a DEFAULTIF character
is specified. If the DEFAULTIF character is not specified or if the
format is DELIMITED this parameter must be set to 0.
- iType
- Specifies the field type. The defined types are in the sql.h include
file, located in the include subdirectory of
the sqllib directory.
- iDefaultIfChar
- Specifies the DEFAULTIF character. If you do not specify a DEFAULTIF
character, this parameter must be set to 0 or '\0' (null
character).
- iEnclosedBy
- Specifies the character that encloses the data in the input source
when the field type is CHAR, DATE, TIME, or TIMESTAMP. If there is
no enclosing character, set this parameter to 0 or '\0' (null
character). For all other field types, set this parameter to 0 or '\0' (null
character).
- iRadixPoint
- Input. Specifies the radix point when the field type is numeric.
To apply the default radix point, this parameter should be set to 0 or '\0' (null
character). For all other field types, this parameter should be set
to 0.
db2IngestFormat data structure parameters
- piFieldDefn
- Input. Pointer to an array of field definition structures. If
you set the iNumFields parameter to 0,
you should set the piFieldDefn parameter to NULL.
Set this parameter to 0 to allow the user to avoid
manually entering the field definition list and have the utility infer
the columns.
- piFileType
- Input. Specifies the format of the input source. This parameter
cannot be NULL. Valid values are :
- SQL_DEL
- Delimited ASCII, for exchange with dBase, BASIC, and the IBM Personal
Decision Series programs, and many other database managers and file
managers.
- SQL_ASC
- Non-delimited ASCII.
- iRecordLength
- Specifies the record length when the file type is positional.
If the file type is not positional this parameter must be set to 0.
- iNumFields
- Specifies the number of elements in the array that piFieldDefn points
to. This number must be less than or equal to the maximum number of
fields (1012).
- iCodepage
- Specifies the codepage of the input file. To apply the default
code page (DB2® Application code
page), you must set this parameter to 0.
- iImplicitlyHidden
- Specifies whether the input records contain implicitly hidden
columns when the SQL statement is INSERT, REPLACE or MERGE. Possible
values for this parameter are as follows:
- DB2INGEST_IMPLICIT_HID_DEFAULT
- Specifies that the value of the DB2_DMU_DEFAULT DB2
registry variable is used. If you set the iImplicitlyHidden parameter
to DB2INGEST_IMPLICIT_HID_DEFAULT but do not set
the registry variable and the target table contains hidden columns,
the utility issues an error. If the SQL statement is not an INSERT,
REPLACE, or MERGE statement or if the table columns are specified,
you must set the parameter to DB2INGEST_IMPLICIT_HID_DEFAULT.
- DB2INGEST_IMPLICIT_HID_MISSING
- Specifies that implicitly hidden columns are omitted from the
default column list.
- DB2INGEST_IMPLICIT_HID_INCLUDE
- Specifies that implicitly hidden columns are included in the default
column list.
- iDelimiter
- Specifies the column delimiter. To set the default delimiter,
this parameter must be set to 0. If the file type
is POSITIONAL, this parameter must be set to 0.
db2IngestOut data structure parameters
- oRowsRead
- Output. The number of rows the utility read from all input sources.
- oRowsSkipped
- Output. This parameter is reserved for future use and is always
set to 0.
- oRowsInserted
- Output. The number of rows the utility inserted into the table
for an SQL INSERT statement.
- oRowsUpdated
- Output. The number of rows the utility updated in the table for
an SQL UPDATE statement.
- oRowsDeleted
- Output. The number of rows the utility deleted from the table
for a SQL DELETE statement.
- oRowsMerged
- Output. The number of rows the utility merged (inserted or updated)
into the table for a SQL MERGE statement.
- oRowsRejected
- Output. The number of rows that were rejected.
- oNumErrors
- Output. The number of error messages that the utility issued.
If you specify the retry_count or reconnect_count ingest
configuration parameter, the value of the oNumErrors parameter
does not include errors from which the utility recovered. The utility
prints such errors to the message file along with a warning message
to state that the utility recovered. The warning messages are counted
in the oNumWarnings output parameter.
- oNumWarnings
- Output. The number of warning messages the utility issued.
- oMaxMsgSeverity
- Output. The maximum severity of all the messages the utility issued.
Possible values are:
- DB2INGEST_MSGSEV_UNDEFINED
- No message returned.
- DB2INGEST_MSGSEV_INFO
- Informational ( SQL messages with message ID SQLnnnn I).
- DB2INGEST_MSGSEV_NO_DATA
- No data was retrieved, updated, or deleted (Message SQL0100W ).
- DB2INGEST_MSGSEV_WARNING
- A warning message other than SQL0100W.
- DB2INGEST_MSGSEV_ERROR
- Error messsages (SQL messages with message ID SQLnnnn N).
- DB2INGEST_MSGSEV_SEVERE
- Severe error messages (SQL messages with message ID SQLnnnn C).
Note: If the warning message is the most severe message
that the API issued, the API then sets this parameter to DB2INGEST_MSGSEV_WARNING.
db2IngestStruct data structure parameters
- piCfgList
- Input. Pointer to the structure containing the ingest configuration
parameters.
- piSourceList
- Input. Specifies the input sources. For this utility, set sqlu_media_list media
type member to SQLU_CLIENT_LOCATION.
- piFormat
- Input. Pointer to the structure containing information about the
input format. This parameter cannot be NULL.
- poIngestInfoOut
- Pointer to the ingest output structure. If this parameter is set
to NULL, the API does not return the information in the db2IngestInfoOut structure.
- piDumpFile
- Input. Pointer to the dump file name. If there is no dump file,
set this parameter to NULL.
- piExceptTableName
- Input. Pointer to the name of the exception table. If there is
no exception table, specify NULL or the empty string (""). If you
do not specify a schema name, the value of the CURRENT SCHEMA special
register is used. The API parses the table name the same way the INGEST command
parses it. That is, if you do not delimit the schema name or table
name, the API converts it to uppercase. If the schema name or table
name contains characters that are not valid in a non-delimited SQL
identifier, you must enclose the name in delimiters ("").
- piMsgFileName
- Input. Pointer to the messages file name. A message file name
must be specified.
- piJobId
- Input. Pointer to the job ID. For the default job ID, this parameter
should be set to NULL. If the restart option is OFF, this parameter
must be set to NULL. If the restart option is CONTINUE or TERMINATE,
this parameter cannot be NULL.
- piSqlStatement
- Input. Pointer to the SQL statement. This parameter cannot be
NULL. For information about valid SQL statements, see the information
about SQL statements in the INGEST command documentation.
- iWarningcount
- Specifies that the Ingest utility is to stop after the warning
count number has reached.
- iRestartMode
- Specifies the Restart option if the Ingest utility fails before
completing. The valid values are:
- DB2INGEST_RESTART_DEFAULT
- The default option. SELECT, INSERT, UPDATE, and DELETE privileges
must exist on the restart table. Specifies that if the INGEST command
fails before completing, it can be restarted from the point of the
last commit by specifying the DB2INGEST_RESTART_CONTINUE option
on a later INGEST command. The parameter piJobId is
a string of up to 128 bytes that uniquely identifies the INGEST command.
The string pointed to by piJobId 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 piJobId parameter
with a later INGEST command. If piJobId is not
specified, the ingest utility generates one. Before using this option,
the restart log table must have been created.
- DB2INGEST_RESTART_OFF
- No Restart. Specifies that no restart information is to be saved.
If the INGEST command fails before completing, it cannot be restarted
using the DB2INGEST_RESTART_CONTINUE option. If you
want to rerun the command to completion, the target table must be
restored to its state and the INGEST command must be rerun with the
same input data.
- DB2INGEST_RESTART_NEW
- SELECT, INSERT, UPDATE, and DELETE privileges must exist on the
restart table. Specifies that if the INGEST command fails before completing,
it can be restarted from the point of the last commit by specifying
the DB2INGEST_RESTART_CONTINUE option on a later
INGEST command. The parameter piJobId is a string
of up to 128 bytes that uniquely identifies the INGEST command. The
string pointed to by piJobId 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 piJobId parameter
with a later INGEST command. If piJobId is not
specified, the ingest utility generates one. Before using this option,
the restart log table must have been created.
- DB2INGEST_RESTART_CONTINUE
- SELECT, INSERT, UPDATE, and DELETE privileges must exist on the
restart table. Specifies that the ingest utility is to restart a previous
INGEST command that specified DB2INGEST_RESTART_NEW option
and failed before completing. The piJobId string
specified with DB2INGEST_RESTART_CONTINUE option
must match the piJobId string specified on the
previous INGEST command. This restarted command is also restartable.
Once the restarted command completes, you can reuse the piJobId string
on a later INGEST command.
- DB2INGEST_RESTART_TERMINATE
- SELECT and DELETE privileges must exist on the restart table.
Specifies that the ingest utility is to clean up the restart information
for a previous INGEST command that specified the DB2INGEST_RESTART_NEW option
and failed before completing. The string specified on this option
must match the piJobId string specified on the
previous INGEST command. This option is specified when a previous
restartable INGEST command fails and you plan to never resume the
job. Once this option is specified, the INGEST command that failed
earlier can no longer be restarted. You can, however, reuse the piJobId string
of a later INGEST command. Note that the data committed by the original
INGEST command, before its failure will still be in the target table,
unlike the TERMINATE option of the LOAD command, that rolls back the
operation to the point in time at which it started.