Loads data into a DB2® table. Although faster than the import utility,
the load utility does not support loading data at the hierarchy level
or loading into a nickname.
Data residing on the server
may be in the form of a file, cursor, tape, or named pipe. Data residing
on a remotely connected client may be in the form of a fully qualified
file, a cursor, or named pipe.
Authorization
One of the following authorities:
- DATAACCESS
- load authority on the database and:
- INSERT privilege on the table when the load utility is invoked
in INSERT mode, TERMINATE mode (to terminate a previous load insert
operation), or RESTART mode (to restart a previous load insert operation)
- INSERT and DELETE privilege on the table when the load utility
is invoked in REPLACE mode, TERMINATE mode (to terminate a previous
load replace operation), or RESTART mode (to restart a previous load
replace operation)
- INSERT privilege on the exception table, if such a table is used
as part of the load operation.
If the FORCE option is specified, SYSADM authority is required.
Note: In
general, all load processes and all DB2 server
processes are owned by the instance owner. All of these processes
use the identification of the instance owner to access needed files.
Therefore, the instance owner must have read access to the input files,
regardless of who invokes the command.
Required connection
Database. If implicit
connect is enabled, a connection to the default database is established.
Utility access to Linux, UNIX, or Windows database servers from Linux, UNIX,
or Windows clients must be
a direct connection through the engine and not through a DB2 Connect™ gateway or loop back environment.
Instance.
An explicit attachment is not required. If a connection to the database
has been established, an implicit attachment to the local instance
is attempted.
API include file
db2ApiDf.h
API and data structure syntax
SQL_API_RC SQL_API_FN
db2Load (
db2Uint32 versionNumber,
void * pParmStruct,
struct sqlca * pSqlca);
typedef SQL_STRUCTURE db2LoadStruct
{
struct sqlu_media_list *piSourceList;
struct sqlu_media_list *piLobPathList;
struct sqldcol *piDataDescriptor;
struct sqlchar *piActionString;
char *piFileType;
struct sqlchar *piFileTypeMod;
char *piLocalMsgFileName;
char *piTempFilesPath;
struct sqlu_media_list *piVendorSortWorkPaths;
struct sqlu_media_list *piCopyTargetList;
db2int32 *piNullIndicators;
struct db2LoadIn *piLoadInfoIn;
struct db2LoadOut *poLoadInfoOut;
struct db2PartLoadIn *piPartLoadInfoIn;
struct db2PartLoadOut *poPartLoadInfoOut;
db2int16 iCallerAction;
struct sqlu_media_list *piXmlPathList;
struct sqllob *piLongActionString;
} db2LoadStruct;
typedef SQL_STRUCTURE db2LoadUserExit
{
db2Char iSourceUserExitCmd;
struct db2Char *piInputStream;
struct db2Char *piInputFileName;
struct db2Char *piOutputFileName;
db2Uint16 *piEnableParallelism;
} db2LoadUserExit;
typedef SQL_STRUCTURE db2LoadIn
{
db2Uint64 iRowcount;
db2Uint64 iRestartcount;
char *piUseTablespace;
db2Uint32 iSavecount;
db2Uint32 iDataBufferSize;
db2Uint32 iSortBufferSize;
db2Uint32 iWarningcount;
db2Uint16 iHoldQuiesce;
db2Uint16 iCpuParallelism;
db2Uint16 iDiskParallelism;
db2Uint16 iNonrecoverable;
db2Uint16 iIndexingMode;
db2Uint16 iAccessLevel;
db2Uint16 iLockWithForce;
db2Uint16 iCheckPending;
char iRestartphase;
char iStatsOpt;
db2Uint16 *piXmlParse;
db2DMUXmlValidate *piXmlValidate;
db2Uint16 iSetIntegrityPending;
struct db2LoadUserExit *piSourceUserExit;
} db2LoadIn;
typedef SQL_STRUCTURE db2LoadOut
{
db2Uint64 oRowsRead;
db2Uint64 oRowsSkipped;
db2Uint64 oRowsLoaded;
db2Uint64 oRowsRejected;
db2Uint64 oRowsDeleted;
db2Uint64 oRowsCommitted;
} db2LoadOut;
typedef SQL_STRUCTURE db2PartLoadIn
{
char *piHostname;
char *piFileTransferCmd;
char *piPartFileLocation;
struct db2LoadNodeList *piOutputNodes;
struct db2LoadNodeList *piPartitioningNodes;
db2Uint16 *piMode;
db2Uint16 *piMaxNumPartAgents;
db2Uint16 *piIsolatePartErrs;
db2Uint16 *piStatusInterval;
struct db2LoadPortRange *piPortRange;
db2Uint16 *piCheckTruncation;
char *piMapFileInput;
char *piMapFileOutput;
db2Uint16 *piTrace;
db2Uint16 *piNewline;
char *piDistfile;
db2Uint16 *piOmitHeader;
SQL_PDB_NODE_TYPE *piRunStatDBPartNum;
} db2PartLoadIn;
typedef SQL_STRUCTURE db2LoadNodeList
{
SQL_PDB_NODE_TYPE *piNodeList;
db2Uint16 iNumNodes;
} db2LoadNodeList;
typedef SQL_STRUCTURE db2LoadPortRange
{
db2Uint16 iPortMin;
db2Uint16 iPortMax;
} db2LoadPortRange;
typedef SQL_STRUCTURE db2PartLoadOut
{
db2Uint64 oRowsRdPartAgents;
db2Uint64 oRowsRejPartAgents;
db2Uint64 oRowsPartitioned;
struct db2LoadAgentInfo *poAgentInfoList;
db2Uint32 iMaxAgentInfoEntries;
db2Uint32 oNumAgentInfoEntries;
} db2PartLoadOut;
typedef SQL_STRUCTURE db2LoadAgentInfo
{
db2int32 oSqlcode;
db2Uint32 oTableState;
SQL_PDB_NODE_TYPE oNodeNum;
db2Uint16 oAgentType;
} db2LoadAgentInfo;
SQL_API_RC SQL_API_FN
db2gLoad (
db2Uint32 versionNumber,
void * pParmStruct,
struct sqlca * pSqlca);
typedef SQL_STRUCTURE db2gLoadStruct
{
struct sqlu_media_list *piSourceList;
struct sqlu_media_list *piLobPathList;
struct sqldcol *piDataDescriptor;
struct sqlchar *piActionString;
char *piFileType;
struct sqlchar *piFileTypeMod;
char *piLocalMsgFileName;
char *piTempFilesPath;
struct sqlu_media_list *piVendorSortWorkPaths;
struct sqlu_media_list *piCopyTargetList;
db2int32 *piNullIndicators;
struct db2gLoadIn *piLoadInfoIn;
struct db2LoadOut *poLoadInfoOut;
struct db2gPartLoadIn *piPartLoadInfoIn;
struct db2PartLoadOut *poPartLoadInfoOut;
db2int16 iCallerAction;
db2Uint16 iFileTypeLen;
db2Uint16 iLocalMsgFileLen;
db2Uint16 iTempFilesPathLen;
struct sqlu_media_list *piXmlPathList;
struct sqllob *piLongActionString;
} db2gLoadStruct;
typedef SQL_STRUCTURE db2gLoadIn
{
db2Uint64 iRowcount;
db2Uint64 iRestartcount;
char *piUseTablespace;
db2Uint32 iSavecount;
db2Uint32 iDataBufferSize;
db2Uint32 iSortBufferSize;
db2Uint32 iWarningcount;
db2Uint16 iHoldQuiesce;
db2Uint16 iCpuParallelism;
db2Uint16 iDiskParallelism;
db2Uint16 iNonrecoverable;
db2Uint16 iIndexingMode;
db2Uint16 iAccessLevel;
db2Uint16 iLockWithForce;
db2Uint16 iCheckPending;
char iRestartphase;
char iStatsOpt;
db2Uint16 iUseTablespaceLen;
db2Uint16 iSetIntegrityPending;
db2Uint16 *piXmlParse;
db2DMUXmlValidate *piXmlValidate;
struct db2LoadUserExit *piSourceUserExit;
} db2gLoadIn;
typedef SQL_STRUCTURE db2gPartLoadIn
{
char *piHostname;
char *piFileTransferCmd;
char *piPartFileLocation;
struct db2LoadNodeList *piOutputNodes;
struct db2LoadNodeList *piPartitioningNodes;
db2Uint16 *piMode;
db2Uint16 *piMaxNumPartAgents;
db2Uint16 *piIsolatePartErrs;
db2Uint16 *piStatusInterval;
struct db2LoadPortRange *piPortRange;
db2Uint16 *piCheckTruncation;
char *piMapFileInput;
char *piMapFileOutput;
db2Uint16 *piTrace;
db2Uint16 *piNewline;
char *piDistfile;
db2Uint16 *piOmitHeader;
void *piReserved1;
db2Uint16 iHostnameLen;
db2Uint16 iFileTransferLen;
db2Uint16 iPartFileLocLen;
db2Uint16 iMapFileInputLen;
db2Uint16 iMapFileOutputLen;
db2Uint16 iDistfileLen;
} db2gPartLoadIn;
/* Definitions for iUsing value of db2DMUXmlValidate structure */
#define DB2DMU_XMLVAL_XDS 1 /* Use XDS */
#define DB2DMU_XMLVAL_SCHEMA 2 /* Use a specified schema */
#define DB2DMU_XMLVAL_SCHEMALOC_HINTS 3 /* Use schemaLocation hints */
#define DB2DMU_XMLVAL_ORIGSCHEMA 4 /* Use schema that document was
originally validated against
(load from cursor only) */
db2Load API parameters
- versionNumber
- Input. Specifies the version and release level of the structure
passed as the second parameter pParmStruct.
- pParmStruct
- Input. A pointer to the db2LoadStruct structure.
- pSqlca
- Output. A pointer to the sqlca structure.
db2LoadStruct data structure parameters
- piSourceList
- Input. A pointer to an sqlu_media_list structure
used to provide a list of source files, devices, vendors, pipes, or
SQL statements.
The information provided in this structure depends
on the value of the
media_type field. Valid values
(defined in
sqlutil header file, located in the
include directory)
are:
- SQLU_SQL_STMT
- If the media_type field is set to this value,
the caller provides an SQL query through the pStatement field
of the target field. The pStatement field is
of type sqlu_statement_entry. The sessions field
must be set to the value of 1, since the load utility
only accepts a single SQL query per load.
- SQLU_SERVER_LOCATION
- If the media_type field is set to this value,
the caller provides information through sqlu_location_entry structures.
The sessions field indicates the number of sqlu_location_entry structures
provided. This is used for files, devices, and named pipes.
- SQLU_CLIENT_LOCATION
- If the media_type field is set to this value,
the caller provides information through sqlu_location_entry structures.
The sessions field indicates the number of sqlu_location_entry structures
provided. This is used for fully qualified files and named pipes.
Note that this media_type is only valid if the
API is being called via a remotely connected client.
- SQLU_TSM_MEDIA
- If the media_type field is set to this value,
the sqlu_vendor structure is used, where filename
is the unique identifier for the data to be loaded. There should only
be one sqlu_vendor entry, regardless of the value
of sessions. The sessions field
indicates the number of TSM sessions to initiate. The load utility
will start the sessions with different sequence numbers, but with
the same data in the one sqlu_vendor entry.
- SQLU_OTHER_MEDIA
- If the media_type field is set to this value,
the sqlu_vendor structure is used, where shr_lib is
the shared library name, and filename is the
unique identifier for the data to be loaded. There should only be
one sqlu_vendor entry, regardless of the value
of sessions. The sessions field indicates the number of other vendor
sessions to initiate. The load utility will start the sessions with
different sequence numbers, but with the same data in the one sqlu_vendor entry.
- SQLU_REMOTEFETCH
- If the media_type field is set to this value,
the caller provides information through an sqlu_remotefetch_entry structure.
The sessions field must be set to the value of 1.
- piLobPathList
- Input. A pointer to an sqlu_media_list structure.
For IXF, ASC, and DEL file types, a list of fully qualified paths
or devices to identify the location of the individual LOB files to
be loaded. The file names are found in the IXF, ASC, or DEL files,
and are appended to the paths provided.
The information provided
in this structure depends on the value of the
media_type field.
Valid values (defined in
sqlutil header file,
located in the
include directory) are:
- SQLU_LOCAL_MEDIA
- If set to this value, the caller provides information through sqlu_media_entry structures.
The sessions field indicates the number of sqlu_media_entry structures
provided.
- SQLU_TSM_MEDIA
- If set to this value, the sqlu_vendor structure
is used, where filename is the unique identifier
for the data to be loaded. There should only be one sqlu_vendor entry,
regardless of the value of sessions. The sessions field
indicates the number of TSM sessions to initiate. The load utility
will start the sessions with different sequence numbers, but with
the same data in the one sqlu_vendor entry.
- SQLU_OTHER_MEDIA
- If set to this value, the sqlu_vendor structure
is used, where shr_lib is the shared library
name, and filename is the unique identifier for
the data to be loaded. There should only be one sqlu_vendor entry,
regardless of the value of sessions. The sessions field
indicates the number of other vendor sessions to initiate. The load
utility will start the sessions with different sequence numbers, but
with the same data in the one sqlu_vendor entry.
- piDataDescriptor
- Input. Pointer to an sqldcol structure containing
information about the columns being selected for loading from the
external file.
If the piFileType parameter
is set to SQL_ASC, the dcolmeth field
of this structure must be set to SQL_METH_L. The
user specifies the start and end locations for each column to be loaded.
If
the file type is SQL_DEL, dcolmeth can
be either SQL_METH_P or SQL_METH_D.
If it is SQL_METH_P, the user must provide the source
column position. If it is SQL_METH_D, the first column
in the file is loaded into the first column of the table, and so on.
If
the file type is SQL_IXF, dcolmeth can
be one of SQL_METH_P, SQL_METH_D,
or SQL_METH_N. The rules for DEL files apply here,
except that SQL_METH_N indicates that file column
names are to be provided in the sqldcol structure.
- piActionString
- Deprecated, replaced by piLongActionString.
- piLongActionString
- Input. Pointer to an sqllob structure containing
a 4-byte long field, followed by an array of characters specifying
an action that affects the table.
The character array is of the
form:
"INSERT|REPLACE KEEPDICTIONARY|REPLACE RESETDICTIONARY|RESTART|TERMINATE
INTO tbname [(column_list)]
[FOR EXCEPTION e_tbname]"
- INSERT
- Adds the loaded data to the table without changing the existing
table data.
- REPLACE
- Deletes all existing data from the table, and inserts the loaded
data. The table definition and the index definitions are not changed.
- RESTART
- Restarts a previously interrupted load operation. The load operation
will automatically continue from the last consistency point in the
load, build, or delete phase.
- TERMINATE
- Terminates a previously interrupted load operation, and rolls
back the operation to the point in time at which it started, even
if consistency points were passed. The states of any table spaces
involved in the operation return to normal, and all table objects
are made consistent (index objects may be marked as invalid, in which
case index rebuild will automatically take place at next access).
If the table spaces in which the table resides are not in load pending
state, this option does not affect the state of the table spaces.
The load terminate option will not remove a backup pending state
from table spaces.
- tbname
- The name of the table into which the data is
to be loaded. The table cannot be a system table, a declared temporary
table, or a created temporary table. An alias, or the fully qualified
or unqualified table name can be specified. A qualified table name
is in the form schema.tablename. If an unqualified table name is specified,
the table will be qualified with the CURRENT SCHEMA.
- (column_list)
- A list of table column names into which the data is to be inserted.
The column names must be separated by commas. If a name contains spaces
or lowercase characters, it must be enclosed by quotation marks.
- FOR EXCEPTION e_tbname
- Specifies the exception table into which rows in error will be
copied. The exception table is used to store copies of rows that violate
unique index rules, range constraints and security policies.
- NORANGEEXC
- Indicates that if a row is rejected because of a range violation
it will not be inserted into the exception table.
- NOUNIQUEEXC
- Indicates that if a row is rejected because it violates a unique
constraint it will not be inserted into the exception table.
- piFileType
- Input. A string that indicates the format of the input data source.
Supported external formats (defined in sqlutil)
are:
- SQL_ASC
- Non-delimited ASCII.
- 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_IXF
- PC version of the Integration Exchange Format, the preferred method
for exporting data from a table so that it can be loaded later into
the same table or into another database manager table.
- SQL_CURSOR
- An SQL query. The sqlu_media_list structure
passed in through the piSourceList parameter
is either of type SQLU_SQL_STMT or SQLU_REMOTEFETCH,
and refers to an SQL query or a table name.
- piFileTypeMod
- Input. A pointer to the sqlchar structure,
followed by an array of characters that specify one or more processing
options. If this pointer is NULL, or the structure pointed to has
zero characters, this action is interpreted as selection of a default
specification.
Not all options can be used with all of the supported
file types. See related link "File type modifiers for the load utility."
- piLocalMsgFileName
- Input. A string containing the name of a local file to which output
messages are to be written.
- piTempFilesPath
- Input. A string containing the path name to be used on the server
for temporary files. Temporary files are created to store messages,
consistency points, and delete phase information.
- piVendorSortWorkPaths
- Input. A pointer to the sqlu_media_list structure
which specifies the Vendor Sort work directories.
- piCopyTargetList
- Input. A pointer to an sqlu_media_list structure
used (if a copy image is to be created) to provide a list of target
paths, devices, or a shared library to which the copy image is to
be written.
The values provided in this structure depend on the
value of the
media_type field. Valid values for
this parameter (defined in
sqlutil header file,
located in the
include directory) are:
- SQLU_LOCAL_MEDIA
- If the copy is to be written to local media, set the media_type to
this value and provide information about the targets in sqlu_media_entry structures.
The sessions field specifies the number of sqlu_media_entry structures
provided.
- SQLU_TSM_MEDIA
- If the copy is to be written to TSM, use this value. No further
information is required.
- SQLU_OTHER_MEDIA
- If a vendor product is to be used, use this value and provide
further information via an sqlu_vendor structure.
Set the shr_lib field of this structure to the
shared library name of the vendor product. Provide only one sqlu_vendor entry,
regardless of the value of sessions. The sessions field
specifies the number of sqlu_media_entry structures
provided. The load utility will start the sessions with different
sequence numbers, but with the same data provided in the one sqlu_vendor entry.
- piNullIndicators
- Input. For ASC files only. An array of integers that indicate
whether or not the column data is nullable. There is a one-to-one
ordered correspondence between the elements of this array and the
columns being loaded from the data file. That is, the number of elements
must equal the dcolnum field of the piDataDescriptor parameter.
Each element of the array contains a number identifying a location
in the data file that is to be used as a NULL indicator field, or
a zero indicating that the table column is not nullable. If the element
is not zero, the identified location in the data file must contain
a Y or an N. A Y indicates that the table column data is NULL, and
N indicates that the table column data is not NULL.
- piLoadInfoIn
- Input. A pointer to the db2LoadIn structure.
- poLoadInfoOut
- Output. A pointer to the db2LoadOut structure.
- piPartLoadInfoIn
- Input. A pointer to the db2PartLoadIn structure.
- poPartLoadInfoOut
- Output. A pointer to the db2PartLoadOut structure.
- iCallerAction
- Input. An action requested by the caller. Valid values (defined
in sqlutil header file, located in the include directory)
are:
- SQLU_INITIAL
- Initial call. This value (or SQLU_NOINTERRUPT)
must be used on the first call to the API.
- SQLU_NOINTERRUPT
- Initial call. Do not suspend processing. This value (or SQLU_INITIAL)
must be used on the first call to the API.
If the initial call
or any subsequent call returns and requires the calling application
to perform some action before completing the requested load operation,
the caller action must be set to one of the following value:
- SQLU_CONTINUE
- Continue processing. This value can only be used on subsequent
calls to the API, after the initial call has returned with the utility
requesting user input (for example, to respond to an end of tape condition).
It specifies that the user action requested by the utility has completed,
and the utility can continue processing the initial request.
- SQLU_TERMINATE
- Terminate processing. Causes the load utility to exit prematurely,
leaving the table spaces being loaded in LOAD_PENDING state. This
option should be specified if further processing of the data is not
to be done.
- SQLU_ABORT
- Terminate processing. Causes the load utility to exit prematurely,
leaving the table spaces being loaded in LOAD_PENDING state. This
option should be specified if further processing of the data is not
to be done.
- SQLU_RESTART
- Restart processing.
- SQLU_DEVICE_TERMINATE
- Terminate a single device. This option should be specified if
the utility is to stop reading data from the device, but further processing
of the data is to be done.
- piXmlPathList
- Input. Pointer to an sqlu_media_list with its media_type field
set to SQLU_LOCAL_MEDIA, and its sqlu_media_entry structure
listing paths on the client where the xml files can be found.
db2LoadUserExit data structure parameters
- iSourceUserExitCmd
- Input. The fully qualified name of an executable that will be
used to feed data to the utility. For security reasons, the executable
must be placed within the sqllib/bin directory
on the server. This parameter is mandatory if the piSourceUserExit structure
is not NULL.
The piInputStream, piInputFileName, piOutputFileName and piEnableParallelism fields
are optional.
- piInputStream
- Input. A generic byte-stream that will be passed directly to the
user-exit application via STDIN. You have complete control over what
data is contained in this byte-stream and in what format. The load
utility will simply carry this byte-stream over to the server and
pass it into the user-exit application by feeding the process' STDIN
(it will not codepage convert or modify the byte-stream). Your user-exit
application would read the arguments from STDIN and use the data however
intended.
One important attribute of this feature is the ability
to hide sensitive information (such as userid/passwords).
- piInputFileName
- Input. Contains the name of a fully qualified client-side file,
whose contents will be passed into the user-exit application by feeding
the process' STDIN.
- piOutputFileName
- Input. The fully qualified name of a server-side file. The STDOUT
and STDERR streams of the process which is executing the user-exit
application will be streamed into this file. When piEnableParallelism is TRUE,
multiple files will be created (one per user-exit instance), and each
file name will be appended with a 3 digit numeric node-number value,
such as filename.000).
- piEnableParallelism
- Input. A flag indicating that the utility should attempt to parallelize
the invocation of the user-exit application.
db2LoadIn data structure parameters
- iRowcount
- Input. The number of physical records to be loaded. Allows a user
to load only the first rowcnt rows in a file.
- iRestartcount
- Input. Reserved for future use.
- piUseTablespace
- Input. If the indexes are being rebuilt, a shadow copy of the
index is built in table space iUseTablespaceName and
copied over to the original table space at the end of the load. Only
system temporary table spaces can be used with this option. If not
specified then the shadow index will be created in the same table
space as the index object.
If the shadow copy is created in the
same table space as the index object, the copy of the shadow index
object over the old index object is instantaneous. If the shadow copy
is in a different table space from the index object a physical copy
is performed. This could involve considerable I/O and time. The copy
happens while the table is offline at the end of a load.
This
field is ignored if iAccessLevel is SQLU_ALLOW_NO_ACCESS.
This
option is ignored if the user does not specify INDEXING
MODE REBUILD or INDEXING MODE AUTOSELECT.
This option will also be ignored if INDEXING MODE AUTOSELECT is
chosen and load chooses to incrementally update the index.
- iSavecount
- The number of records to load before establishing a consistency
point. This value is converted to a page count, and rounded up to
intervals of the extent size. Since a message is issued at each consistency
point, this option should be selected if the load operation will be
monitored using db2LoadQuery - Load Query. If the
value of savecount is not sufficiently high,
the synchronization of activities performed at each consistency point
will impact performance.
The default value is 0,
meaning that no consistency points will be established, unless necessary.
- iDataBufferSize
- The number of 4KB pages (regardless of the degree of parallelism)
to use as buffered space for transferring data within the utility.
If the value specified is less than the algorithmic minimum, the required
minimum is used, and no warning is returned.
This memory is allocated
directly from the utility heap, whose size can be modified through
the util_heap_sz database configuration parameter.
If
a value is not specified, an intelligent default is calculated by
the utility at run time. The default is based on a percentage of the
free space available in the utility heap at the instantiation time
of the loader, as well as some characteristics of the table.
- iSortBufferSize
- Input. This option specifies a value that overrides the sortheap database
configuration parameter during a load operation. It is relevant only
when loading tables with indexes and only when the iIndexingMode parameter
is not specified as SQLU_INX_DEFERRED. The value
that is specified cannot exceed the value of sortheap.
This parameter is useful for throttling the sort memory used by LOAD without
changing the value of sortheap, which would also
affect general query processing.
- iWarningcount
- Input. Stops the load operation after warningcnt warnings.
Set this parameter if no warnings are expected, but verification that
the correct file and table are being used is required. If the load
file or the target table is specified incorrectly, the load utility
will generate a warning for each row that it attempts to load, which
will cause the load to fail. If warningcnt is 0,
or this option is not specified, the load operation will continue
regardless of the number of warnings issued.
If the load operation
is stopped because the threshold of warnings was exceeded, another
load operation can be started in RESTART mode. The load operation
will automatically continue from the last consistency point. Alternatively,
another load operation can be initiated in REPLACE mode, starting
at the beginning of the input file.
- iHoldQuiesce
- Input. A flag whose value is set to TRUE if the
utility is to leave the table in quiesced exclusive state after the
load, and to FALSE if it is not.
- iCpuParallelism
- Input. The number of processes or threads that the load utility
will create for parsing, converting and formatting records when building
table objects. This parameter is designed to exploit intrapartition
parallelism. It is particularly useful when loading presorted data,
because record order in the source data is preserved. If the value
of this parameter is zero, the load utility uses an intelligent default
value at run time. Note: If this parameter is used with tables containing
either LOB or LONG VARCHAR fields, its value becomes one, regardless
of the number of system CPUs, or the value specified by the user.
- iDiskParallelism
- Input. The number of processes or threads that the load utility
will create for writing data to the table space containers. If a value
is not specified, the utility selects an intelligent default based
on the number of table space containers and the characteristics of
the table.
- iNonrecoverable
- Input. Set to SQLU_NON_RECOVERABLE_LOAD if the
load transaction is to be marked as non-recoverable, and it will not
be possible to recover it by a subsequent roll forward action. The
rollforward utility will skip the transaction, and will mark the table
into which data was being loaded as "invalid". The utility will also
ignore any subsequent transactions against that table. After the roll
forward is completed, such a table can only be dropped. With this
option, table spaces are not put in backup pending state following
the load operation, and a copy of the loaded data does not have to
be made during the load operation. Set to SQLU_RECOVERABLE_LOAD if
the load transaction is to be marked as recoverable.
- iIndexingMode
- Input. Specifies the indexing mode. Valid values (defined in sqlutil header
file, located in the include directory) are:
- SQLU_INX_AUTOSELECT
- LOAD chooses between REBUILD and INCREMENTAL
indexing modes.
- SQLU_INX_REBUILD
- Rebuild table indexes.
- SQLU_INX_INCREMENTAL
- Extend existing indexes.
- SQLU_INX_DEFERRED
- Do not update table indexes.
- iAccessLevel
- Input. Specifies the access level. Valid values are:
- SQLU_ALLOW_NO_ACCESS
- Specifies that the load locks the table exclusively.
- SQLU_ALLOW_READ_ACCESS
- Specifies that the original data in the table (the non-delta portion)
should still be visible to readers while the load is in progress.
This option is only valid for load appends, such as a load insert,
and will be ignored for load replace.
- iLockWithForce
- Input. A boolean flag. If set to TRUE load will
force other applications as necessary to ensure that it obtains table
locks immediately. This option requires the same authority as the FORCE
APPLICATIONS command (SYSADM or SYSCTRL).
SQLU_ALLOW_NO_ACCESS
loads may force conflicting applications at the start of the load
operation. At the start of the load, the utility may force applications
that are attempting to either query or modify the table.
SQLU_ALLOW_READ_ACCESS
loads may force conflicting applications at the start or end of the
load operation. At the start of the load, the load utility may force
applications that are attempting to modify the table. At the end of
the load, the load utility may force applications that are attempting
to either query or modify the table.
- iCheckPending
- This parameter is being deprecated as of Version 9.1. Use the iSetIntegrityPending parameter
instead.
- iRestartphase
- Input. Reserved. Valid value is a single space character ' '.
- iStatsOpt
- Input. Granularity of statistics to collect. Valid values are:
- SQLU_STATS_NONE
- No statistics to be gathered.
- SQLU_STATS_USE_PROFILE
- Statistics are collected based on the profile defined for the
current table. This profile must be created using the RUNSTATS command.
If no profile exists for the current table, a warning is returned
and no statistics are collected.
During load, distribution statistics are not collected
for columns of type XML.
- iSetIntegrityPending
- Input. Specifies to put the table into set integrity pending state.
If the value SQLU_SI_PENDING_CASCADE_IMMEDIATE is
specified, set integrity pending state will be immediately cascaded
to all dependent and descendent tables. If the value SQLU_SI_PENDING_CASCADE_DEFERRED is
specified, the cascade of set integrity pending state to dependent
tables will be deferred until the target table is checked for integrity
violations. SQLU_SI_PENDING_CASCADE_DEFERRED is the
default value if the option is not specified.
- piSourceUserExit
- Input. A pointer to the db2LoadUserExit structure.
- piXmlParse
- Input. Type of parsing that should occur for XML documents. Valid
values found in the db2ApiDf header file in the include directory
are:
- DB2DMU_XMLPARSE_PRESERVE_WS
- Whitespace should be preserved.
- DB2DMU_XMLPARSE_STRIP_WS
- Whitespace should be stripped.
- piXmlValidate
- Input. Pointer to the db2DMUXmlValidate structure.
Indicates that XML schema validation should occur for XML documents.
/* XML Validate structure */
typedef SQL_STRUCTURE db2DMUXmlValidate
{
db2Uint16 iUsing; /* What to use to perform */
/* validation */
struct db2DMUXmlValidateXds *piXdsArgs; /* Arguments for */
/* XMLVALIDATE USING XDS */
struct db2DMUXmlValidateSchema *piSchemaArgs; /* Arguments for */
/* XMLVALIDATE USING SCHEMA */
} db2DMUXmlValidate;
db2LoadOut data structure parameters
- oRowsRead
- Output. Number of records read during the load operation.
- oRowsSkipped
- Output. Number of records skipped before the load operation begins.
- oRowsLoaded
- Output. Number of rows loaded into the target table.
- oRowsRejected
- Output. Number of records that could not be loaded.
- oRowsDeleted
- Output. Number of duplicate rows deleted.
- oRowsCommitted
- Output. The total number of processed records: the number of records
loaded successfully and committed to the database, plus the number
of skipped and rejected records.
db2PartLoadIn data structure parameters
- piHostname
- Input. The hostname for the iFileTransferCmd parameter.
If NULL, the hostname will default to "nohost". This
parameter is deprecated.
- piFileTransferCmd
- Input. File transfer command parameter. If not required, it must
be set to NULL. This parameter is deprecated. Use
the piSourceUserExit parameter instead.
- piPartFileLocation
- Input. In PARTITION_ONLY, LOAD_ONLY,
and LOAD_ONLY_VERIFY_PART modes, this parameter can
be used to specify the location of the partitioned files. This location
must exist on each database partition specified by the piOutputNodes option.
For the SQL_CURSOR file type, this parameter
cannot be NULL and the location does not refer to
a path, but to a fully qualified file name. This will be the fully
qualified base file name of the partitioned files that are created
on each output database partition for PARTITION_ONLY mode, or the
location of the files to be read from each database partition for
LOAD_ONLY mode. For PARTITION_ONLY mode, multiple files may be created
with the specified base name if there are LOB columns in the target
table. For file types other than SQL_CURSOR, if the
value of this parameter is NULL, it will default
to the current directory.
- piOutputNodes
- Input. The list of load output database partitions. A NULL indicates
that all nodes on which the target table is defined.
- piPartitioningNodes
- Input. The list of partitioning nodes. A NULL indicates
the default.
- piMode
- Input. Specifies the load mode for partitioned databases. Valid
values (defined in db2ApiDf header file, located
in the include directory) are:
- - DB2LOAD_PARTITION_AND_LOAD
- Data is distributed (perhaps in parallel) and loaded simultaneously
on the corresponding database partitions.
- - DB2LOAD_PARTITION_ONLY
- Data is distributed (perhaps in parallel) and the output is written
to files in a specified location on each loading database partition.
For file types other than SQL_CURSOR, the name of
the output file on each database partition will have the form filename.xxx,
where filename is the name of the first input file
specified by piSourceList and xxx is
the database partition number. For the SQL_CURSOR file
type, the name of the output file on each database partition will
be determined by the piPartFileLocation parameter.
Refer to the piPartFileLocation parameter for
information about how to specify the location of the database partition
file on each database partition.
Note: This mode cannot be used for
a CLI LOAD.
- DB2LOAD_LOAD_ONLY
- Data is assumed to be already distributed; the distribution process
is skipped, and the data is loaded simultaneously on the corresponding
database partitions. For file types other than SQL_CURSOR,
the input file name on each database partition is expected to be of
the form filename.xxx,
where filename is the name of the first file specified
by piSourceList and xxx is
the 13-digit database partition number. For the SQL_CURSOR file
type, the name of the input file on each database partition will be
determined by the piPartFileLocation parameter.
Refer to the piPartFileLocation parameter for
information about how to specify the location of the database partition
file on each database partition.
Note: This mode cannot be used when
loading a data file located on a remote client, nor can it be used
for a CLI LOAD.
- DB2LOAD_LOAD_ONLY_VERIFY_PART
- Data is assumed to be already distributed, but the data file does
not contain a database partition header. The distribution process
is skipped, and the data is loaded simultaneously on the corresponding
database partitions. During the load operation, each row is checked
to verify that it is on the correct database partition. Rows containing
database partition violations are placed in a dumpfile if the dumpfile
file type modifier is specified. Otherwise, the rows are discarded.
If database partition violations exist on a particular loading database
partition, a single warning will be written to the load message file
for that database partition. The input file name on each database
partition is expected to be of the form filename.xxx,
where filename is the name of the first file specified
by piSourceList and xxx is
the 13-digit database partition number.
Note: This mode cannot be
used when loading a data file located on a remote client, nor can
it be used for a CLI LOAD.
- DB2LOAD_ANALYZE
- An optimal distribution map with even distribution across all
database partitions is generated.
- piMaxNumPartAgents
- Input. The maximum number of partitioning agents. A NULL value
indicates the default, which is 25.
- piIsolatePartErrs
- Input. Indicates how the load operation will react to errors that
occur on individual database partitions. Valid values (defined in db2ApiDf header
file, located in the include directory) are:
- DB2LOAD_SETUP_ERRS_ONLY
- In
this mode, errors that occur on a database partition during setup,
such as problems accessing a database partition or problems accessing
a table space or table on a database partition, will cause the load
operation to stop on the failing database partitions but to continue
on the remaining database partitions. Errors that occur on a database
partition while data is being loaded will cause the entire operation
to fail and roll back to the last point of consistency on each database
partition.
- DB2LOAD_LOAD_ERRS_ONLY
- In this mode, errors that occur on a database partition during
setup will cause the entire load operation to fail. When an error
occurs while data is being loaded, the database partitions with errors
will be rolled back to their last point of consistency. The load operation
will continue on the remaining database partitions until a failure
occurs or until all the data is loaded. On the database partitions
where all of the data was loaded, the data will not be visible following
the load operation. Because of the errors in the other database partitions
the transaction will be aborted. Data on all of the database partitions
will remain invisible until a load restart operation is performed.
This will make the newly loaded data visible on the database partitions
where the load operation completed and resume the load operation on
database partitions that experienced an error.
Note: This mode cannot
be used when iAccessLevel is set to SQLU_ALLOW_READ_ACCESS and
a copy target is also specified.
- DB2LOAD_SETUP_AND_LOAD_ERRS
- In this mode, database partition-level errors during setup or
loading data cause processing to stop only on the affected database
partitions. As with the DB2LOAD_LOAD_ERRS_ONLY mode, when database
partition errors do occur while data is being loaded, the data on
all database partitions will remain invisible until a load restart
operation is performed.
Note: This mode cannot be used when iAccessLevel is
set to SQLU_ALLOW_READ_ACCESS and a copy target is
also specified.
- DB2LOAD_NO_ISOLATION
- Any error during the Load operation causes the transaction to
be aborted. If this parameter is NULL, it will default to DB2LOAD_LOAD_ERRS_ONLY,
unless iAccessLevel is set to SQLU_ALLOW_READ_ACCESS and
a copy target is also specified, in which case the default is DB2LOAD_NO_ISOLATION.
- piStatusInterval
- Input. Specifies the number of megabytes (MB) of data to load
before generating a progress message. Valid values are whole numbers
in the range of 1 to 4000. If NULL
is specified, a default value of 100 will be used.
- piPortRange
- Input. The TCP port range for internal communication. If NULL,
the port range used will be 6000-6063.
- piCheckTruncation
- Input. Causes Load to check for record truncation at Input/Output.
Valid values are TRUE and FALSE.
If NULL, the default is FALSE.
- piMapFileInput
- Input. Distribution map input filename. If the mode is not ANALYZE,
this parameter should be set to NULL. If the mode is ANALYZE, this
parameter must be specified.
- piMapFileOutput
- Input. Distribution map output filename. The rules for piMapFileInput apply
here as well.
- piTrace
- Input. Specifies the number of records to trace when you need
to review a dump of all the data conversion process and the output
of hashing values. If NULL, the number of records defaults to 0.
- piNewline
- Input. Forces Load to check for newline characters at end of ASC
data records if RECLEN file type modifier is also specified. Possible
values are TRUE and FALSE. If NULL,
the value defaults to FALSE.
- piDistfile
- Input. Name of the database partition distribution file. If a
NULL is specified, the value defaults to "DISTFILE".
- piOmitHeader
- Input. Indicates that a distribution map header should not be
included in the database partition file when using DB2LOAD_PARTITION_ONLY
mode. Possible values are TRUE and FALSE.
If NULL, the default is FALSE.
- piRunStatDBPartNum
- Specifies the database partition on which to collect statistics.
The default value is the first database partition in the output database
partition list.
db2LoadNodeList data structure parameters
- piNodeList
- Input. An array of node numbers.
- iNumNodes
- Input. The number of nodes in the piNodeList array.
A 0 indicates the default, which is all nodes on
which the target table is defined.
db2LoadPortRange data structure parameters
- iPortMin
- Input. Lower port number.
- iPortMax
- Input. Higher port number.
db2PartLoadOut data structure parameters
- oRowsRdPartAgents
- Output. Total number of rows read by all partitioning agents.
- oRowsRejPartAgents
- Output. Total number of rows rejected by all partitioning agents.
- oRowsPartitioned
- Output. Total number of rows partitioned by all partitioning agents.
- poAgentInfoList
- Output. During a load operation into a partitioned database, the
following load processing entities may be involved: load agents, partitioning
agents, pre-partitioning agents, file transfer command agents and
load-to-file agents (these are described in the Data Movement Guide).
The purpose of the poAgentInfoList output parameter
is to return to the caller information about each load agent that
participated in a load operation. Each entry in the list contains
the following information:
- oAgentType
- A tag indicating what kind of load agent the entry describes.
- oNodeNum
- The number of the database partition on which the agent executed.
- oSqlcode
- The final sqlcode resulting from the agent's processing.
- oTableState
- The final status of the table on the database partition on which
the agent executed (relevant only for load agents).
It is up to the caller of the API to allocate memory
for this list before calling the API. The caller should also indicate
the number of entries for which they allocated memory in the iMaxAgentInfoEntries parameter.
If the caller sets poAgentInfoList to NULL or
sets iMaxAgentInfoEntries to 0,
then no information will be returned about the load agents.
- iMaxAgentInfoEntries
- Input. The maximum number of agent information entries allocated
by the user for poAgentInfoList. In general,
setting this parameter to 3 times the number of database partitions
involved in the load operation should be sufficient.
- oNumAgentInfoEntries
- Output. The actual number of agent information entries produced
by the load operation. This number of entries will be returned to
the user in the poAgentInfoList parameter as
long as iMaxAgentInfoEntries is greater than
or equal to oNumAgentInfoEntries. If iMaxAgentInfoEntries is
less than oNumAgentInfoEntries, then the number
of entries returned in poAgentInfoList is equal
to iMaxAgentInfoEntries.
db2LoadAgentInfo data structure parameters
- oSqlcode
- Output. The final sqlcode resulting from the agent's processing.
- oTableState
- Output. The purpose of this output parameter is not to report
every possible state of the table after the load operation. Rather,
its purpose is to report only a small subset of possible table states
in order to give the caller a general idea of what happened to the
table during load processing. This value is relevant for load agents
only. The possible values are:
- DB2LOADQUERY_NORMAL
- Indicates that the load completed successfully on the database
partition and the table was taken out of the LOAD IN PROGRESS (or
LOAD PENDING) state. In this case, the table still could be in SET
INTEGRITY PENDING state due to the need for further constraints processing,
but this will not reported as this is normal.
- DB2LOADQUERY_UNCHANGED
- Indicates that the load job aborted processing due to an error
but did not yet change the state of the table on the database partition
from whatever state it was in before calling db2Load.
It is not necessary to perform a load restart or terminate operation
on such database partitions.
- DB2LOADQUERY_LOADPENDING
- Indicates that the load job aborted during processing but left
the table on the database partition in the LOAD PENDING state, indicating
that the load job on that database partition must be either terminated
or restarted.
- oNodeNum
- Output. The number of the database partition on which the agent
executed.
- oAgentType
- Output. The agent type. Valid values (defined in db2ApiDf header
file, located in the include directory) are :
- DB2LOAD_LOAD_AGENT
- DB2LOAD_PARTITIONING_AGENT
- DB2LOAD_PRE_PARTITIONING_AGENT
- DB2LOAD_FILE_TRANSFER_AGENT
- DB2LOAD_LOAD_TO_FILE_AGENT
db2gLoadStruct data structure specific parameters
- iFileTypeLen
- Input. Specifies the length in bytes of iFileType parameter.
- iLocalMsgFileLen
- Input. Specifies the length in bytes of iLocalMsgFileName parameter.
- iTempFilesPathLen
- Input. Specifies the length in bytes of iTempFilesPath parameter.
- piXmlPathList
- Input. Pointer to an sqlu_media_list with its media_type field
set to SQLU_LOCAL_MEDIA, and its sqlu_media_entry structure
listing paths on the client where the xml files can be found.
db2gLoadIn data structure specific parameters
- iUseTablespaceLen
- Input. The length in bytes of piUseTablespace parameter.
- piXmlParse
- Input. Type of parsing that should occur for XML documents. Valid
values found in the db2ApiDf header file in the include directory
are:
- DB2DMU_XMLPARSE_PRESERVE_WS
- Whitespace should be preserved.
- DB2DMU_XMLPARSE_STRIP_WS
- Whitespace should be stripped.
- piXmlValidate
- Input. Pointer to the db2DMUXmlValidate structure.
Indicates that XML schema validation should occur for XML documents.
/* XML Validate structure */
typedef SQL_STRUCTURE db2DMUXmlValidate
{
db2Uint16 iUsing; /* What to use to perform */
/* validation */
struct db2DMUXmlValidateXds *piXdsArgs; /* Arguments for */
/* XMLVALIDATE USING XDS */
struct db2DMUXmlValidateSchema *piSchemaArgs; /* Arguments for */
/* XMLVALIDATE USING SCHEMA */
} db2DMUXmlValidate;
db2gPartLoadIn data structure specific parameters
- piReserved1
- Reserved for future use.
- iHostnameLen
- Input. The length in bytes of piHostname parameter.
- iFileTransferLen
- Input. The length in bytes of piFileTransferCmd parameter.
- iPartFileLocLen
- Input. The length in bytes of piPartFileLocation parameter.
- iMapFileInputLen
- Input. The length in bytes of piMapFileInput parameter.
- iMapFileOutputLen
- Input. The length in bytes of piMapFileOutput parameter.
- iDistfileLen
- Input. The length in bytes of piDistfile parameter.
Usage notes
Data is loaded in the sequence
that appears in the input file. If a particular sequence is required,
the data should be sorted before a load is attempted.
The load
utility builds indexes based on existing definitions. The exception
tables are used to handle duplicates on unique keys. The utility does
not enforce referential integrity, perform constraints checking, or
update summary tables that are dependent on the tables being loaded.
Tables that include referential or check constraints are placed in
set integrity pending state. Summary tables that are defined with
REFRESH IMMEDIATE, and that are dependent on tables being loaded,
are also placed in set integrity pending state. Issue the SET INTEGRITY
statement to take the tables out of set integrity pending state. Load
operations cannot be carried out on replicated summary tables.
For
clustering indexes, the data should be sorted on the clustering index
before loading. The data need not be sorted when loading into an multi-dimensionally
clustered (MDC) table.