db2Ingest API- Ingest data from an input file or pipe into a Db2 table.
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:
- At least one of the following items:
- DATAACCESS authority
- CONTROL privilege on the target table
- SELECT and INSERT privileges on the target table if the INGEST command specifies the INSERT statement, including as part of a MERGE statement
- SELECT and UPDATE privileges on the target table if the INGEST command specifies the UPDATE statement, including as part of a MERGE statement
- SELECT and DELETE privilege on the target table if the INGEST command specifies the DELETE statement (including as part of a MERGE statement)
- INSERT, SELECT, and DELETE privilege on the target table if the INGEST command specifies the REPLACE statement
- SELECT privilege on the following catalog views:
- SYSCAT.COLUMNS
- SYSCAT.DATATYPES
- SYSCAT.INDEXES
- SYSCAT.INDEXCOLUSE
- SYSCAT.SECURITYPOLICIES (if the target table has a security label column)
- SYSCAT.TABDEP
- SYSCAT.TABLES
- SYSCAT.VIEWS
Note: Users have these privileges by default unless the database was created with the RESTRICTIVE clause. - EXECUTE privilege on the following procedures:
- SYSPROC.DB_PARTITIONS (V9.7 or earlier) or SYSPROC.DB_MEMBERS (V9.8 or later)
- SYSPROC.MON_GET_CONNECTION (V9.7 and later)
- 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
- 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.
- 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).
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.