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.
Command Syntax
.-,-------------.
V |
>>-LOAD FROM------+-filename-+--+--OF--filetype----------------->
+-pipename-+
'-device---'
>-----+-----------------------------+--------------------------->
| .-,-----------. |
| V | |
'-LOBS FROM-----lob-path---+--'
>-----+-----------------------------------+--------------------->
| .-----------------. |
| V | |
'-MODIFIED BY-----filetype-mod---+--'
>-----+-----------------------------------------------------------------------------------------------+>
| .-,---------------------------. |
| V | |
'-METHOD--+-L--(-----column-start--column-end---+---)--+-----------------------------------+-+--'
| | .-,----. | |
| | V | | |
| '-NULL INDICATORS--(-----n---+---)--' |
| .-,--------------. |
| V | |
+-N--(-----column-name---+---)-----------------------------------------------------+
| .-,------------------. |
| V | |
'-P--(-----column-position---+---)-------------------------------------------------'
>-----+---------------+---+--------------+---------------------->
'-SAVECOUNT--n--' '-ROWCOUNT--n--'
>-----+------------------+---+-------------------------+-------->
'-WARNINGCOUNT--n--' '-MESSAGES--message-file--'
>-----+--------------------------------+---+-INSERT----+-------->
'-TEMPFILES PATH--temp-pathname--' +-REPLACE---+
+-RESTART---+
'-TERMINATE-'
>----INTO--table-name----+------------------------------+------->
| .-,----------------. |
| V | |
'-(-----insert-column---+---)--'
>-----+--------------------------------------------+------------>
'-DATALINK SPECIFICATION--| datalink-spec |--'
>-----+----------------------------+---------------------------->
'-FOR EXCEPTION--table-name--'
>-----+-------------------------------------------------------------------------------------------+>
| .-YES-. |
'-STATISTICS--+-+-----+--+-+---------------------------------------------------------+-+-+--'
| | '-WITH DISTRIBUTION--+---------------------------------+--' | |
| | '-AND--+----------+--INDEXES ALL--' | |
| | '-DETAILED-' | |
| '-+---------------------------------------+-------------------' |
| '--+-AND-+---+----------+--INDEXES ALL--' |
| '-FOR-' '-DETAILED-' |
'-NO-----------------------------------------------------------------------'
>-----+------------------------------------------------------------------+>
| .-NO-----------------------------------------------------. |
+-COPY--+-YES--+-USE ADSM--+---------------------------+-------+-+-+
| | '-OPEN--num-sess--SESSIONS--' | |
| | .-,-------------------. | |
| | V | | |
| +-TO-----device/directory---+-------------------+ |
| '-LOAD--lib-name--+---------------------------+-' |
| '-OPEN--num-sess--SESSIONS--' |
'-NONRECOVERABLE---------------------------------------------------'
>-----+---------------+---+--------------------+---------------->
'-HOLD QUIESCE--' '-WITHOUT PROMPTING--'
>-----+---------------------------+---+---------------------+--->
'-DATA BUFFER--buffer-size--' '-CPU_PARALLELISM--n--'
>-----+----------------------+---------------------------------->
'-DISK_PARALLELISM--n--'
>-----+---------------------------------+----------------------><
'-INDEXING MODE--+-AUTOSELECT--+--'
+-REBUILD-----+
+-INCREMENTAL-+
'-DEFERRED----'
datalink-spec
.-,--------------------------------------------------------------------------------------------------.
V |
|------(--+-----------------+--+----------------------------------+---+--------------------------+---)---+->
'-DL_LINKTYPE URL-' +-DL_URL_REPLACE_PREFIX--"prefix"--+ '-DL_URL_SUFFIX--"suffix"--'
'-DL_URL_DEFAULT_PREFIX--"prefix"--'
>---------------------------------------------------------------|
Command Parameters
Notes:
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.
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 found within the insert-column list, or within the table definition (if an insert-column list is not specified).
Examples of prefix are:
"http://server" "file://server" "file:" "http://server:80"
If no prefix is found in the column data, and a default prefix is specified with DL_URL_DEFAULT_PREFIX, the default prefix is prefixed to the column value (if not NULL).
For example, if DL_URL_DEFAULT_PREFIX specifies the default prefix "http://toronto":
For example, if DL_URL_REPLACE_PREFIX specifies the prefix "http://toronto":
Information that is written to the exception table is not written to the dump file (for a description of the dumpfile modifier, see Table 7). In a partitioned database environment, an exception table must be defined for those nodes on which the loading table is defined. The dump file, on the other hand, contains rows that cannot be loaded because they are invalid or have syntax errors. For more information, see the Data Movement Utilities Guide and Reference.
Notes:
db2 quiesce tablespaces for table <tablename> reset
| Note: | Ensure that no phantom quiesces are created (see QUIESCE TABLESPACES FOR TABLE). |
Incremental indexing is not supported when all of the following conditions is true:
To bypass this restriction, it is recommended that indexes be placed in a separate table space.
Deferred indexing is only supported for tables with non-unique indexes, so that duplicate keys inserted during the load phase are not persistent after the load operation.
The load utility cannot parse columns whose names contain one or more spaces. For example,
db2 load from delfile1 of del modified by noeofchar noheader
method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
insert into table1 (BLOB1, S2, I3, Int 4, I5, I6, DT7, I8, TM9)
will fail because of the Int 4 column. The solution is to enclose such column names with double quotation marks:
db2 load from delfile1 of del modified by noeofchar noheader
method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
insert into table1 (BLOB1, S2, I3, "Int 4", I5, I6, DT7, I8, TM9)
| Note: | This method can only be used with ASC files, and is the only valid option for that file type. |
| Note: | This method can only be used with IXF files. |
| Note: | This method can only be used with IXF or DEL files, and is the only valid option for the DEL file type. |
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.
This option should not be used when DATALINK columns with the FILE LINK CONTROL attribute are present in, or being added to, the table.
A value of Y in the NULL indicator column specifies that the column data is NULL. Any character other than Y in the NULL indicator column specifies that the column data is not NULL, and that column data specified by the METHOD L option will be loaded.
The NULL indicator character can be changed using the MODIFIED BY option (see the description of the nullindchar modifier in Table 7).
For more information about file formats, see the "Export/Import/Load Utility File Formats" appendix in the Data Movement Utilities Guide and Reference.
This option is not supported for tables with DATALINK columns.
The default value is zero, meaning that no consistency points will be established, unless necessary.
Temporary files take up file system space. Sometimes, this space requirement is quite substantial. Following is an estimate of how much file system space should be allocated for all temporary files:
For more information about temporary files, see the Data Movement Utilities Guide and Reference.
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.
If this option is not specified, and the tape device encounters an end of tape for the copy image, or the last item listed is a tape device, the user is prompted for a new tape on that device. Tape is not supported on OS/2.
Examples
Example 1
TABLE1 has 5 columns:
ASCFILE1 has 6 elements:
Data Records:
1...5....10...15...20...25...30...35...40 Test data 1 XXN 123abcdN Test data 2 and 3 QQY wxyzN Test data 4,5 and 6 WWN6789 Y
The following command loads the table from the file:
db2 load from ascfile1 of asc modified by striptblanks reclen=40
method L (1 20, 21 22, 24 27, 28 31)
null indicators (0,0,23,32)
insert into table1 (col1, col5, col2, col3)
Notes:
Example 2 (Loading LOBs from Files)
TABLE1 has 3 columns:
ASCFILE1 has 3 elements:
The following files reside in either /u/user1 or /u/user1/bin:
Data Records in ASCFILE1:
1...5....10...15...20...25...30. REC1 ASCFILE2 ASCFILE3 REC2 ASCFILE4 ASCFILE5 REC3 ASCFILE6 ASCFILE7
The following command loads the table from the file:
db2 load from ascfile1 of asc
lobs from /u/user1, /u/user1/bin
modified by lobsinfile reclen=22
method L (1 4, 6 13, 15 22)
insert into table1
Notes:
Example 3 (Using Dump Files)
Table FRIENDS is defined as:
table friends "( c1 INT NOT NULL, c2 INT, c3 CHAR(8) )"
If an attempt is made to load the following data records into this table,
23, 24, bobby , 45, john 4,, mary
the second row is rejected because the first INT is NULL, and the column definition specifies NOT NULL. Columns which contain initial characters that are not consistent with the DEL format will generate an error, and the record will be rejected. Such records can be written to a dump file (see Table 7).
DEL data appearing in a column outside of character delimiters is ignored, but does generate a warning. For example:
22,34,"bob" 24,55,"sam" sdf
The utility will load "sam" in the third column of the table, and the characters "sdf" will be flagged in a warning. The record is not rejected. Another example:
22 3, 34,"bob"
The utility will load 22,34,"bob", and generate a warning that some data in column one following the 22 was ignored. The record is not rejected.
Example 4 (Loading DATALINK Data)
The following command loads the table MOVIETABLE from the input file delfile1, which has data in the DEL format:
db2 load from delfile1 of del
modified by dldel|
insert into movietable (actorname, description, url_making_of, url_movie)
datalink specification (dl_url_default_prefix "http://narang"),
(dl_url_replace_prefix "http://bomdel" dl_url_suffix ".mpeg")
for exception excptab
Notes:
actorname VARCHAR(n) description VARCHAR(m) url_making_of DATALINK (with LINKTYPE URL) url_movie DATALINK (with LINKTYPE URL)
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 "Export/Import/Load Utility File Formats" appendix in the Data Movement Utilities Guide and Reference.
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 7. 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 "AutoLoader" in the Data Movement Utilities Guide and Reference) 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
QUIESCE TABLESPACES FOR TABLE.