Loads data from files, tapes, or named pipes into a DB2 table. Tape is not supported on OS/2. The load utility does not support loading data at the hierarchy level.
Scope
This command affects only the partition to which a direct connection exists; the load utility operates on a single database partition only.
Authorization
One of the following:
| 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 invokes the command. |
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.
Version
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
The information provided in this structure depends on the value of the media_type field. Valid values (defined in sqlutil) are:
Wherever a file name is provided, it should be fully qualified. For more information, see SQLU-MEDIA-LIST.
The information provided in this structure depends on the value of the media_type field. Valid values (defined in sqlutil) are:
For more information, see SQLU-MEDIA-LIST.
If the pFileType 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.
For more information, see SQLDCOL.
The character array is of the form:
"INSERT|REPLACE|RESTART|TERMINATE INTO tbname [(column_list)] [DATALINK SPECIFICATION datalink-spec] [FOR EXCEPTION e_tbname]"
The load terminate option will not remove a backup pending state from table spaces.
For more information about file formats, see the "Export/Import/Load Utility File Formats" appendix in the Data Movement Utilities Guide and Reference.
Not all options can be used with all of the supported file types.
For more information, see SQLCHAR, and the Command Reference.
If the initial call or any subsequent call returns and requires the calling application to perform some action prior to completing the requested load operation, the caller action must be set to one of the following:
The values provided in this structure depend on the value of the media_type field. Valid values for this field (defined in sqlutil) are:
For more information, see SQLU-MEDIA-LIST.
REXX API Syntax
This API can be called from REXX through the SQLDB2 interface. See How the API Descriptions are Organized, or the Application Development Guide. For a description of the syntax, see the Command Reference.
Sample Programs
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 that include referential or check constraints are placed in check pending state. Summary tables that are defined with REFRESH IMMEDIATE, and that are dependent on tables being loaded, are also placed in check pending state. Issue the SET INTEGRITY 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.
DB2 Data Links Manager Considerations
For each DATALINK column, there can be one column specification within parentheses. Each column specification consists of one or more of DL_LINKTYPE, prefix and a DL_URL_SUFFIX specification. The prefix information can be either DL_URL_REPLACE_PREFIX, or the DL_URL_DEFAULT_PREFIX specification.
There can be as many DATALINK column specifications as the number of DATALINK columns defined in the table. The order of specifications follows the order of DATALINK columns as found within the insert-column list (if specified by INSERT INTO (insert-column, ...)), or within the table definition (if insert-column is not specified).
For example, if a table has columns C1, C2, C3, C4, and C5, and among them only columns C2 and C5 are of type DATALINK, and the insert-column list is (C1, C5, C3, C2), there should be two DATALINK column specifications. The first column specification will be for C5, and the second column specification will be for C2. If an insert-column list is not specified, the first column specification will be for C2, and the second column specification will be for C5.
If there are multiple DATALINK columns, and some columns do not need any particular specification, the column specification should have at least the parentheses to unambiguously identify the order of specifications. If there are no specifications for any of the columns, the entire list of empty parentheses can be dropped. Thus, in cases where the defaults are satisfactory, there need not be any DATALINK specification.
If data is being loaded into a table with a DATALINK column that is defined with FILE LINK CONTROL, perform the following steps before invoking the load utility. (If all the DATALINK columns are defined with NO LINK CONTROL, these steps are not necessary).
The connection between DB2 and the Data Links server may fail while running the load utility, causing the load operation to fail. If this occurs:
Links that fail during the load operation are considered to be data integrity violations, and are handled in much the same way as unique index violations. Consequently, a special exception has been defined for loading tables that have one or more DATALINK columns. For additional information, refer to the description of exceptions in the SQL Reference.
Representation of DATALINK Information in an Input File
The LINKTYPE (currently only URL is supported) is not specified as part of DATALINK information. The LINKTYPE is specified in the LOAD or the IMPORT command, and for input files of type PC/IXF, in the appropriate column descriptor records as described in
The syntax of DATALINK information for a URL LINKTYPE is as follows:
>>-+----------+---+------------------------+------------------->< '-urlname--' '-dl_delimiter--comment--'
Note that both urlname and comment are optional. If neither is provided, the NULL value is assigned.
Notes:
If no comment is specified, the comment defaults to a string of length zero.
Following are DATALINK data examples for the delimited ASCII (DEL) file format:
This is stored with the following parts:
This is stored with the following parts:
This is stored with the following parts:
Following are DATALINK data examples for the non-delimited ASCII (ASC) file format:
This is stored with the following parts:
This is stored with the following parts:
This is stored with the following parts:
Following are DATALINK data examples in which the load or import specification for the column is assumed to be DL_URL_DEFAULT_PREFIX ("http://qso"):
This is stored with the following parts:
This is stored with the following parts:
Table 9. Valid File Type Modifiers (LOAD)
| Modifier | Description | ||
|---|---|---|---|
| All File Formats | |||
| anyorder | This modifier is used in conjunction with the cpu_parallelism parameter. Specifies that the preservation of source data order is not required, yielding significant additional performance benefit on SMP systems. If the value of cpu_parallelism is 1, this option is ignored. This option is not supported if SAVECOUNT > 0, since crash recovery after a consistency point requires that data be loaded in sequence. | ||
| fastparse | Reduced syntax checking is done on user-supplied column values, and
performance is enhanced. Tables loaded under this option are guaranteed
to be architecturally correct, and the utility is guaranteed to perform
sufficient data checking to prevent a segmentation violation or trap.
Data that is in correct form will be loaded correctly.
For example, if a value of 123qwr4 were to be encountered as a field entry for an integer column in an ASC file, the load utility would ordinarily flag a syntax error, since the value does not represent a valid number. With fastparse, a syntax error is not detected, and an arbitrary number is loaded into the integer field. Care must be taken to use this modifier with clean data only. Performance improvements using this option with ASCII data can be quite substantial, but fastparse does not significantly enhance performance with PC/IXF data, since IXF is a binary format, and fastparse affects parsing and conversion from ASCII to internal forms. | ||
| indexfreespace=x | x is an integer between 0 and 99 inclusive. The value
is interpreted as the percentage of each index page that is to be left as free
space when loading the index. The first entry in a page is added
without restriction; subsequent entries are added if the percent free space
threshold can be maintained. The default value is the one used at
CREATE INDEX time.
This value takes precedence over the PCTFREE value specified in the CREATE INDEX statement, and affects index leaf pages only. | ||
| lobsinfile | lob-path specifies the path to the files containing LOB values. The ASC, DEL, or IXF load input files contain the names of the files having LOB data in the LOB column. | ||
| noheader | Skips the header verification code.
The AutoLoader utility (see writes a header to each file contributing data to a table in a multi-node nodegroup. The header includes the node number, the partitioning map, and the partitioning key specification. The load utility requires this information to verify that the data is being loaded at the correct node. When loading files into a table that exists on a single-node nodegroup, the headers do not exist, and this option causes the load utility to skip the header verification code. | ||
| norowwarnings | Suppresses all warnings about rejected rows. | ||
| pagefreespace=x | x is an integer between 0 and 100 inclusive. The value
is interpreted as the percentage of each data page that is to be left as free
space.
If the specified value is invalid because of the minimum row size, (for example, a row that is at least 3 000 bytes long, and an x value of 50), the row will be placed on a new page. If a value of 100 is specified, each row will reside on a new page.
| ||
| totalfreespace=x | x is an integer between 0 and 100 inclusive. The value is interpreted as the percentage of the total pages in the table that is to be appended to the end of the table as free space. For example, if x is 20, and the table has 100 data pages, 20 additional empty pages will be appended. The total number of data pages for the table will be 120. | ||
| usedefaults | If a source column for a target table column has been specified, but it
contains no data for one or more row instances, default values are
loaded. Examples of missing data are:
| ||
| ASCII File Formats (ASC/DEL) | |||
| codepage=x | x is an ASCII character string. The value is
interpreted as the code page of the data in the input data set.
Converts character data (and numeric data specified in characters) from this
code page to the database code page during the load operation.
The following rules apply:
| ||
| dumpfile = x | x is the fully qualified (according to the server node) name
of an exception file to which rejected rows are written. A maximum of
32KB of data is written per record. Following is an example that shows
how to specify a dump file:
db2 load from data of del
modified by dumpfile = /u/user/filename
insert into table_name
Notes:
| ||
| implieddecimal | The location of an implied decimal point is determined by the column definition; it is no longer assumed to be at the end of the value. For example, the value 12345 is loaded into a DECIMAL(8,2) column as 123.45, not 12345.00. | ||
| noeofchar | The optional end-of-file character x'1A' is not recognized as the end of file. Processing continues as if it were a normal character. | ||
| ASC (Non-delimited ASCII) File Format | |||
| binarynumerics | Numeric (but not DECIMAL) data must be in binary form, not the character
representation. This avoids costly conversions.
This option is supported only with positional ASC, using fixed length records specified by the reclen option. The noeofchar option is assumed. The following rules apply:
| ||
| nochecklengths | If nochecklengths is specified, an attempt is made to load each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows can be successfully loaded if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions. | ||
| nullindchar=x | x is a single character. Changes the character denoting
a NULL value to x. The default value of x is
Y.b
This modifier is case sensitive for EBCDIC data files, except when the character is an English letter. For example, if the NULL indicator character is specified to be the letter N, then n is also recognized as a NULL indicator. | ||
| packeddecimal | Loads packed-decimal data directly, since the binarynumerics
modifier does not include the DECIMAL field type.
This option is supported only with positional ASC, using fixed length records specified by the reclen option. The noeofchar option is assumed. Supported values for the sign nibble are: + = 0xC 0xA 0xE 0xF - = 0xD 0xB
| ||
| reclen=x | x is an integer with a maximum value of 32 767. x characters are read for each row, and a new-line character is not used to indicate the end of the row. | ||
| striptblanks | Truncates any trailing blank spaces when loading data into a
variable-length field. If this option is not specified, blank spaces
are kept.
This option cannot be specified together with striptnulls. These are mutually exclusive options.
| ||
| striptnulls | Truncates any trailing NULLs (0x00 characters) when loading data into a
variable-length field. If this option is not specified, NULLs are
kept.
This option cannot be specified together with striptblanks. These are mutually exclusive options.
| ||
| DEL (Delimited ASCII) File Format | |||
| chardelx | x is a single character string delimiter. The default
value is a double quotation mark ("). The specified character is
used in place of double quotation marks to enclose a character
string.ab
The single quotation mark (') can also be specified as a character string delimiter as follows: modified by chardel'' | ||
| coldelx | x is a single character column delimiter. The default value is a comma (,). The specified character is used in place of a comma to signal the end of a column.ab | ||
| datesiso | Date format. Causes all date data values to be loaded in ISO format. | ||
| decplusblank | Plus sign character. Causes positive decimal values to be prefixed with a blank space instead of a plus sign (+). The default action is to prefix positive decimal values with a plus sign. | ||
| decptx | x is a single character substitute for the period as a decimal point character. The default value is a period (.). The specified character is used in place of a period as a decimal point character.ab | ||
| delprioritychar | The current default priority for delimiters is: record delimiter,
character delimiter, column delimiter. This modifier protects existing
applications that depend on the older priority by reverting the delimiter
priorities to: character delimiter, record delimiter, column
delimiter. Syntax:
db2 load ... modified by delprioritychar ... For example, given the following DEL data file: "Smith, Joshua",4000,34.98<row delimiter> "Vincent,<row delimiter>, is a manager", ... ... 4005,44.37<row delimiter> With the delprioritychar modifier specified, there will be only two rows in this data file. The second <row delimiter> will be interpreted as part of the first data column of the second row, while the first and the third <row delimiter> are interpreted as actual record delimiters. If this modifier is not specified, there will be three rows in this data file, each delimited by a <row delimiter>. | ||
| dldelx | x is a single character DATALINK delimiter. The default
value is a semicolon (;). The specified character is used in place
of a semicolon as the inter-field separator for a DATALINK value. It is
needed because a DATALINK value may have more than one sub-value.
abc
| ||
| nodoubledel | Suppresses recognition of double character delimiters. | ||
| IXF File Format | |||
| forcein | Directs the utility to accept data despite code page mismatches, and to
suppress translation between code pages.
Fixed length target fields are checked to verify that they are large enough for the data. If nochecklengths is specified, no checking is done, and an attempt is made to load each row. | ||
| nochecklengths | If nochecklengths is specified, an attempt is made to load each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows can be successfully loaded if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions. | ||
|
Notes:
| |||
See Also
sqluvqdp - Quiesce Tablespaces for Table.