Loads data from files, tapes, or named pipes into a DB2 table.
Scope
This command only affects the node on which it is executed.
In a multi-node environment, this command 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:
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--+ +-RESTARTCOUNT--+-B-+-+ +-ROWCOUNT--n--+
+-D-+
+-n-+
>--+------------------+--+-------------------------+------------>
+-WARNINGCOUNT--n--+ +-MESSAGES--message-file--+
>--+---------------------------+--+-INSERT----+----------------->
+-REMOTE FILE--remote-file--+ +-REPLACE---+
+-RESTART---+
+-TERMINATE-+
>-INTO--table-name---+----------------------------+------------->
| +-,--------------+ |
| V | |
+-(----insert-column--+--)---+
>--+----------------------------+------------------------------->
+-FOR EXCEPTION--table-name--+
>--+--------------------------------------------------------------------------------------+->
| +-YES--+-+---------------------------------------------------------+-+-+ |
+-STATISTICS--+-NO-------WITH DISTRIBUTION-------------------------------------------+-+
| +-AND-+----------+---INDEXES ALL--+ |
| +-DETAILED-+ |
+-+-------------------------------------+---------------------+
+--+-AND-++----------+---INDEXES ALL--+
+-FOR-++-DETAILED-+
>--+----------------------------------------------------------------+>
| +-NO----------------------------------------------------+|
+-COPY--+-YES--+-USE ADSM--+---------------------------+-------+++
| | +-OPEN--num-sess--SESSIONS--+ | |
| | +-,-----------------+ | |
| | V | | |
| +-TO----device/directory--+---------------------+ |
| +-LOAD--lib-name--+---------------------------+-+ |
| +-OPEN--num-sess--SESSIONS--+ |
+-NONRECOVERABLE-------------------------------------------------+
>--+------------------------+----------------------------------->
| +-,----------+ |
| V | |
+-USING----directory--+--+
>-+--------------+--+-------------------+----------------------->
+-HOLD QUIESCE-+ +-WITHOUT PROMPTING-+
>--+---------------------------+--+---------------------------+->
+-DATA BUFFER--buffer-size--+ +-SORT BUFFER--buffer-size--+
>--+---------------------+--+----------------------+-----------><
+-CPU_PARALLELISM--n--+ +-DISK_PARALLELISM--n--+
|
Command Parameters
Notes:
For more information about file formats, see Appendix C. "IMPORT/EXPORT/LOAD Utility File Formats".
| 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. |
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 default value is 0, meaning that no consistency points will be established, unless necessary.
This option can be specified with any of the INSERT, REPLACE, or RESTART modes. B or D must not be specified for the INSERT or the REPLACE mode.
If the load is stopped because the threshold of warnings was encountered, another load can be started in RESTART mode by specifying the RESTARTCOUNT option. Alternatively, another load can be initiated in REPLACE mode, starting at the beginning of the input file.
If the complete path to the file is not specified, LOAD uses the current directory and the default drive as the destination.
If the name of a file that already exists is specified, LOAD appends the information.
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 LOAD QUERY to get this information if the database connection was lost during the load.
| Note: | This option is not recommended for general use; it should only be selected if an unrecoverable error has occurred. |
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.
If more than one directory is specified, each directory should be on a different file system, and each file system should be on a different disk, to optimize performance.
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.
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.
| Note: | Sort buffer size has a very large impact on sort performance. Therefore, for very large tables (for example, tables in excess of 100M), this buffer should be set as large as possible. |
If a value is not specified, the utility uses the larger of:
If a value greater than zero, but less than the required minimum is specified, the minimum value for that load is returned.
| 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. |
Example
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 T 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)
| Note: |
The specification of T in the MODIFIED BY parameter forces the truncation of blanks in VARCHAR columns (COL1, for example, which is 11, 17 and 19 bytes long, in rows 1, 2 and 3, respectively). The specification of reclen=40 in the MODIFIED BY parameter indicates that there is no new-line character at the end of each input record, and that each record is 40 bytes long. The last 8 bytes are not used to load the table. Since COL4 is not provided in the input file, it will be inserted into TABLE1 with its default value (it is defined NOT NULL WITH DEFAULT). Positions 23 and 32 are used to indicate whether COL2 and COL3 of TABLE1 will be loaded NULL for a given row. If there is a Y in the column's null indicator position for a given record, the column will be NULL. If there is an N, the data values in the columns data positions of the input record (as defined in L(........)) are used as the source of column data for the row. In this example, neither column in row 1 is NULL; COL2 in row 2 is NULL; and COL3 in row 3 is NULL. In this example, the NULL INDICATORS for COL1 and COL5 are specified as 0 (zero), indicating that the data is not nullable. The NULL INDICATOR for a given column can be anywhere in the input record, but the position must be specified, and the Y or N values must be supplied. |
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
| Note: |
The specification of lobsinfile in the MODIFIED BY parameter tells the loader that all LOB data is to be loaded from files. The specification of reclen=22 in the MODIFIED BY parameter indicates that there is no new-line character at the end of each input record, and that each record is 22 bytes long. LOB data is contained in 6 files, ASCFILE2 through ASCFILE7. Each file contains the data that will be used to load a LOB column for a specific row. The relationship between LOBs and other data is specified in ASCFILE1. The first record of this file tells the loader to place REC1 in COL1 of row 1. The contents of ASCFILE2 will be used to load LOB1 of row 1, and the contents of ASCFILE3 will be used to load LOB2 of row 1. Similarly, ASCFILE4 and ASCFILE5 will be used to load LOB1 and LOB2 of row 2, and ASCFILE6 and ASCFILE7 will be used to load the LOBs of row 3. The LOBS FROM parameter contains 2 paths that will be searched for the named LOB files when those files are required by the loader. |
To load LOBs directly from ASCFILE1 (a non-delimited ASCII file), without the LOBSINFILE option, the following rules must be observed:
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 LOAD is executed.
LOAD builds indexes based on existing definitions. The exception tables are used to handle duplicates on unique keys. LOAD does not perform referential integrity or constraint checking. If these are included in the table definition, the tables are placed in check pending state, and the user must either force the check flag, or execute the SET CONSTRAINTS statement.
| Note: | The load utility does not issue a warning if an attempt is made to use unsupported file types with the MODIFIED BY option. If this is attempted, the load fails, and an error code is returned. |
Table 9. Valid File Type Modifications (LOAD)
| Modification | 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 data 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.
This option does not affect referential integrity checking or constraints checking; it merely reduces syntax checking of the supplied data. For example, if the value 123qwr4 were 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. 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. | ||
| 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. | ||
| nodefaults | If a source column for a target table column is not explicitly specified,
and the table column is not nullable, default values are not loaded. Without
this option, if a source column for one of the target table columns is not
explicitly specified, one of the following occurs:
| ||
| noheader | Skips the header verification code.
db2split - Data Declustering Tool 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 right 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 32K of
data is written per record. For example,
db2 load from data of del
modified by dumpfile = /u/user/filename
insert into table_name
| ||
| 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:
| ||
| nullindchar=x | x is a single character. Changes the character denoting a null value to x. The default value of x is Y.b | ||
| 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 | ||
| 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 | ||
| 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. | ||
| |||
Remote file is a base file name to which DB2 appends different extensions to create files used by other commands (for example, .msg for LOAD QUERY.
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