DB2 Version 10.1 for Linux, UNIX, and Windows

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) or SYSIBMADM.APPLICATIONS (V9.5)

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.