IBM Books

API Reference

sqluload - Load

Loads data from files, tapes, or named pipes into a DB2 table. The load utility does not support loading data at the hierarchy level.

Scope

This API only affects the node on which it is executed.

In a multi-node environment, this API can be used only with ASC or DEL files. IXF files can be loaded only if the table exists on a single node nodegroup.

Authorization

One of the following:

sysadm
dbadm
Note:Since all load processes (and all DB2 server processes, in general), are owned by the instance owner, and all of these processes use the identification of the instance owner to access needed files, the instance owner must have read access to input data files. These input data files must be readable by the instance owner, regardless of who calls the API.

Required Connection

Database. If implicit connect is enabled, a connection to the default database is established.

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

sqlutil.h

C API Syntax 



/* File: sqlutil.h */
/* API: Load */
/* ... */
SQL_API_RC SQL_API_FN
  sqluload (
    sqlu_media_list * pDataFileList,
    sqlu_media_list * pLobPathList,
    struct sqldcol * pDataDescriptor,
    struct sqlchar * pActionString,
    char * pFileType,
    struct sqlchar * pFileTypeMod,
    char * pLocalMsgFileName,
    char * pRemoteMsgFileName,
    short CallerAction,
    struct sqluload_in * pLoadInfoIn,
    struct sqluload_out * pLoadInfoOut,
    sqlu_media_list * pWorkDirectoryList,
    sqlu_media_list * pCopyTargetList,
    long * pNullIndicators,
    void * pReserved,
    struct sqlca * pSqlca);
/* ... */

Generic API Syntax 



/* File: sqlutil.h */
/* API: Load */
/* ... */
SQL_API_RC SQL_API_FN
  sqlgload (
    unsigned short FileTypeLen,
    unsigned short LocalMsgFileNameLen,
    unsigned short RemoteMsgFileNameLen,
    sqlu_media_list * pDataFileList,
    sqlu_media_list * pLobPathList,
    struct sqldcol * pDataDescriptor,
    struct sqlchar * pActionString,
    char * pFileType,
    struct sqlchar * pFileTypeMod,
    char * pLocalMsgFileName,
    char * pRemoteMsgFileName,
    short CallerAction,
    struct sqluload_in * pLoadInfoIn,
    struct sqluload_out * pLoadInfoOut,
    sqlu_media_list * pWorkDirectoryList,
    sqlu_media_list * pCopyTargetList,
    long * pNullIndicators,
    void * pReserved,
    struct sqlca * pSqlca);
/* ... */

API Parameters

FileTypeLen
Input. A 2-byte unsigned integer representing the length in bytes of the file type parameter.
LocalMsgFileNameLen
Input. A 2-byte unsigned integer representing the length in bytes of the local message file name parameter.
RemoteMsgFileNameLen
Input. A 2-byte unsigned integer representing the length in bytes of the remote message file name parameter.
pDataFileList
Input. A pointer to an sqlu_media_list structure used to provide a list of source files, devices, vendors or pipes.

The information provided in this structure depends on the value of the media_type field. Valid values (defined in sqlutil) are:

SQLU_SERVER_LOCATION
If the media_type field is set to this value, the caller provides information via 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_ADSM_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 ADSM sessions to initiate. LOAD 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. LOAD will start the sessions with different sequence numbers, but with the same data in the one sqlu_vendor entry.

Wherever a file name is provided, it should be fully qualified.

pLobPathList
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/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) are:

SQLU_LOCAL_MEDIA
If set to this value, the caller provides information via sqlu_media_entry structures. The sessions field indicates the number of sqlu_media_entry structures provided.
SQLU_ADSM_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 ADSM sessions to initiate. LOAD 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. LOAD will start the sessions with different sequence numbers, but with the same data in the one sqlu_vendor entry.
pDataDescriptor
Input. Pointer to an sqldcol structure containing information about the columns being selected for loading from the external file.

If the pFileType parameter is set to SQL_ASC, the dcolmeth field of this structure must be SQL_METH_L. The user indicates 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 column position from which the data comes. If it is SQL_METH_D, the first column in the file will be 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.

For more information, see SQLDCOL.

pActionString
Input. Specifies an action that affects the table. Pointer to an sqlchar structure that contains the following string: 
   "INSERT|REPLACE|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 index definitions are not changed.
RESTART
Restarts LOAD after a previous load was interrupted.

It is important to keep track of the last commit point. This information is stored in the message file and is passed to LOAD. Use sqluqry - Load Query to get this information if the database connection was lost during the load.

TERMINATE
Terminates a previously interrupted load and moves the table spaces in which the table resides from load pending state to recovery pending state. The table spaces cannot be used until a backup has been restored and the table spaces have been rolled forward. A restart should be issued before attempting to complete an interrupted load.
Note:This option is not recommended for general use; it should only be selected if an unrecoverable error has occurred.
into tbname
Specifies the database table into which the data is to be loaded. The table cannot be a system 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 authorization ID.
(column_list)
Specifies the table columns 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.
Note:An additional DB2 File Manager specification, using the same syntax as in the LOAD command (see the Command Reference), can be specified after column_list and before the FOR EXCEPTION clause.
FOR EXCEPTION e_tbname
Specifies the exception table into which rows in error will be copied. Any row that is in violation of a unique index or a primary key index is copied.
pFileType
Input. A string that indicates the format of the data within the external file. Supported external file formats (defined in sqlutil) are:
SQL_ASC
Non-delimited ASCII.
SQL_DEL
Delimited ASCII.
SQL_IXF
IXF (integrated exchange format, PC version) exported from the same or from another DB2 table.

For more information about file formats, see the Command Reference.

pFileTypeMod
Input. A pointer to a structure containing a 2-byte long field, 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.

For more information, see the Command Reference.

pLocalMsgFileName
Input. A string containing the local file name to be used for output messages.
pRemoteMsgFileName
Input. A string containing the base name to be used on the server for temporary files. Temporary files are created to store messages, consistency points, and to delete phase information. Different extensions will be appended to this name for the various files. For more information about remote files, see ***).
CallerAction
Input. Specifies an action that affects the utility. Valid values (defined in sqlutil) are:
SQLU_INITIAL
Initial call. Must be set to this value or to SQLU_NOINTERRUPT for the first call.
SQLU_CONTINUE
Continue processing. The action requested by the utility has completed, so the system can continue processing the request. This option could be specified, for example, after a tape has been changed.
SQLU_TERMINATE
Terminate processing. Causes the load utility to exit prematurely, leaving the table spaces being loaded in RECOVER_PENDING and QUIESCE_EXCLUSIVE state.
SQLU_NOINTERRUPT
Initial call. Do not suspend processing. Must be set to this value or to SQLU_INITIAL for the first call.
SQLU_ABORT
Abort 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.
pLoadInfoIn
Input. Optional pointer to the sqluload_in structure containing additional input parameters. See SQLULOAD-IN.
pLoadInfoOut
Output. Optional pointer to the sqluload_out structure containing additional output parameters. See SQLULOAD-OUT.
pWorkDirectoryList
Input. Optional work directories used for sorting index keys. If not provided, the sqllib/tmp directory is used.
pCopyTargetList
Input. If a copy image is to be created, this parameter contains 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 field (defined in sqlutil) 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_ADSM_MEDIA
If the copy is to be written to ADSM, 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. LOAD will start the sessions with different sequence numbers, but with the same data provided in the one sqlu_vendor entry.
pNullIndicators
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 pDataDescriptor 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.
pReserved
Reserved for future use.
pSqlca
Output. A pointer to the sqlca structure. For more information about this structure, see SQLCA.

REXX API Syntax

This API can be called from REXX through the SQLDB2 interface. See How the API Descriptions are Organized, or the Embedded SQL Programming Guide. For a description of the syntax, see the Command Reference.

Sample Programs

C
\sqllib\samples\c\tload.sqc

COBOL
\sqllib\samples\cobol\tload.sqb

FORTRAN
\sqllib\samples\fortran\tload.sqf

Usage Notes

Data is loaded in the sequence that appears in the input file. If a particular sequence is desired, 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 being loaded that include referential or check constraints are placed in check pending state. Summary tables dependent on tables being loaded are also placed in check pending state. Issue the SET CONSTRAINTS statement to take the tables out of check pending state. Load operations cannot be carried out on replicated summary tables.

If clustering is required, the data should be sorted on the clustering index prior to loading.

Remote Files

Remote file is a base file name to which DB2 appends different extensions to create files used by other functions (for example, .msg for sqluqry).

The remote file resides on the server machine and is accessed by the DB2 instance exclusively. Therefore, it is imperative that any file name qualification given to this parameter reflects the directory structure of the server, not the client, and that the DB2 instance owner has read and write permission on this file. In addition, the user must ensure that two loads are not issued that have the same fully-qualified remote file name.

There are several ways that the remote file name can be selected and qualified when the user has just given a partially qualified name, or no name at all:

Note:In an MPP system, the remote file must reside on a local disk, not on an NFS mount. If the file is on an NFS mount, there will be a significant performance decrement during the load operation.

See Also

sqluqry - Load Query

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]