Loads data into a DB2 table. Data residing on the server may be in the form of a file, tape, or named pipe. Data residing on a remotely connected client may be in the form of a fully qualified file or named pipe. 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.
Loading data that resides on a remotely connected client is not supported under the following conditions:
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-+--+--------------->
'-CLIENT--' +-pipename-+
'-device---'
>----OF--filetype----+-----------------------------+------------>
| .-,-----------. |
| V | |
'-LOBS FROM-----lob-path---+--'
>-----+-----------------------------------+--------------------->
| .-----------------. |
| V | |
'-MODIFIED BY-----filetype-mod---+--'
>-----+-----------------------------------------------------------------------------------------------------------------+>
| .-,---------------------------. |
| V | |
'-METHOD--+-L--(-----column-start--column-end---+---)--+-----------------------------------------------------+-+--'
| | .-,----------------------. | |
| | V | | |
| '-NULL INDICATORS--(-----null-indicator-list---+---)--' |
| .-,--------------. |
| 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 TSM--+---------------------------+--------+-+-+
| | '-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:
In the following example, a data file (/u/user/data.del) residing on a remotely connected client is to be loaded into MYTABLE on the server database:
db2 load client from /u/user/data.del of del
modified by codepage=850 insert into mytable
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" "dfs://.../cellname/fs"
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 are 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.
| Note: | Deferred indexing is not supported for tables that have DATALINK columns. |
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)
The message file is usually populated with messages at the end of the load operation and, as such, is not suitable for monitoring the progress of the operation. For real-time monitoring of a load operation, use the LOAD QUERY.
| 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.
The load terminate option will not remove a backup pending state from table spaces.
| Note: | This option is not supported for tables with DATALINK columns. |
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)
Example 5 (Loading a Table with an Identity Column)
TABLE1 has 4 columns:
TABLE2 is the same as TABLE1, except that C2 is a GENERATED ALWAYS identity column.
Data records in DATAFILE1 (DEL format):
"Liszt" "Hummel",,187.43, H "Grieg",100, 66.34, G "Satie",101, 818.23, I
Data records in DATAFILE2 (DEL format):
"Liszt", 74.49, A "Hummel", 0.01, H "Grieg", 66.34, G "Satie", 818.23, I
Notes:
db2 load from datafile1.del of del replace into table1
db2 load from datafile1.del of del method P(1, 3, 4) replace into table1 (c1, c3, c4) db2load from datafile1.del of del modified by identityignore replace into table1
db2 load from datafile2.del of del replace into table1 (c1, c3, c4) db2 load from datafile2.del of del modified by identitymissing replace into table1
db2 load from datafile1.del of del modified by identityoverride replace into table2In this case, rows 1 and 2 will be rejected, because the utility has been instructed to override system-generated identity values in favor of user-supplied values. If user-supplied values are not present, however, the row must be rejected, because identity columns are implicitly not NULL.
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. | ||
| generatedignore | This modifier informs the load utility that data for all generated columns is present in the data file but should be ignored. For nullable generated columns, this results in NULLs being loaded into the column; for non-nullable generated columns, this results in the default value for the generated column's data type being loaded. At the end of the load operation, the SET INTEGRITY statement can be invoked to force the replacement of loaded values with values computed according to the generated column definition. This modifier cannot be used with either the generatedmissing or the generatedoverride modifier. | ||
| generatedmissing | If this modifier is specified, the utility assumes that the input data file contains no data for the generated column (not even NULLs), and will therefore load NULLs into the column. At the end of the load operation, the SET INTEGRITY statement can be used to replace the NULLs with values computed according to the generated column definition. This modifier cannot be used with either the generatedignore or the generatedoverride modifier. | ||
| generatedoverride | This modifier instructs the load utility to accept explicit, non-NULL
data for all generated columns in the table (contrary to the normal rules for
these types of columns). This is useful when migrating data from
another database system, or when loading a table from data that was recovered
using the DROPPED TABLE RECOVERY option on the ROLLFORWARD DATABASE
command. When this modifier is used, any rows with no data or NULL data
for a non-nullable generated column will be rejected (SQL3116W).
This modifier cannot be used with either the generatedmissing or the generatedignore modifier. | ||
| identityignore | This modifier informs the load utility that data for the identity column is present in the data file but should be ignored. This results in all identity values being generated by the utility. The behavior will be the same for both GENERATED ALWAYS and GENERATED BY DEFAULT identity columns. This means that for GENERATED ALWAYS columns, no rows will be rejected. This modifier cannot be used with either the identitymissing or the identityoverride modifier. | ||
| identitymissing | If this modifier is specified, the utility assumes that the input data file contains no data for the identity column (not even NULLs), and will therefore generate a value for each row. The behavior will be the same for both GENERATED ALWAYS and GENERATED BY DEFAULT identity columns. This modifier cannot be used with either the identityignore or the identityoverride modifier. | ||
| identityoverride | This modifier should be used only when an identity column defined as
GENERATED ALWAYS is present in the table to be loaded. It instructs the
utility to accept explicit, non-NULL data for such a column (contrary to the
normal rules for these types of identity columns). This is useful when
migrating data from another database system when the table must be defined as
GENERATED ALWAYS, or when loading a table from data that was recovered using
the DROPPED TABLE RECOVERY option on the ROLLFORWARD DATABASE command.
When this modifier is used, any rows with no data or NULL data for the
identity column will be rejected (SQL3116W). This modifier cannot be
used with either the identitymissing or the
identityignore modifier.
| ||
| 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 (applicable only to load operations
into tables that reside in a single-node nodegroup).
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:
| ||
| dateformat="x" | x is the format of the date in the source
file.a Valid date elements are:
YYYY - Year (four digits ranging from 0000 - 9999)
M - Month (one or two digits ranging from 1 - 12)
MM - Month (two digits ranging from 1 - 12;
mutually exclusive with M)
D - Day (one or two digits ranging from 1 - 31)
DD - Day (two digits ranging from 1 - 31;
mutually exclusive with D)
DDD - Day of the year (three digits ranging
from 001 - 366; mutually exclusive
with other day or month elements)
A default value of 1 is assigned for each element that is not specified. Some examples of date formats are: "D-M-YYYY" "MM.DD.YYYY" "YYYYDDD" | ||
| 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. | ||
| timeformat="x" | x is the format of the time in the source
file.a Valid time elements are:
H - Hour (one or two digits ranging from 0 - 12
for a 12 hour system, and 0 - 24
for a 24 hour system)
HH - Hour (two digits ranging from 0 - 12
for a 12 hour system, and 0 - 24
for a 24 hour system; mutually exclusive
with H)
M - Minute (one or two digits ranging
from 0 - 59)
MM - Minute (two digits ranging from 0 - 59;
mutually exclusive with M)
S - Second (one or two digits ranging
from 0 - 59)
SS - Second (two digits ranging from 0 - 59;
mutually exclusive with S)
SSSSS - Second of the day after midnight (5 digits
ranging from 00000 - 86399; mutually
exclusive with other time elements)
TT - Meridian indicator (AM or PM)
A default value of 0 is assigned for each element that is not specified. Some examples of time formats are: "HH:MM:SS" "HH.MM TT" "SSSSS" | ||
| timestampformat="x" | x is the format of the time stamp in the source
file.a Valid time stamp elements are:
YYYY - Year (four digits ranging from 0000 - 9999)
M - Month (one or two digits ranging
from 1 - 12)
MM - Month (two digits ranging from 1 - 12;
mutually exclusive with M, month)
D - Day (one or two digits ranging from 1 - 31)
DD - Day (two digits ranging from 1 - 31;
mutually exclusive with D)
DDD - Day of the year (three digits ranging
from 001 - 366; mutually exclusive with
other day or month elements)
H - Hour (one or two digits ranging from 0 - 12
for a 12 hour system, and 0 - 24
for a 24 hour system)
HH - Hour (two digits ranging from 0 - 12
for a 12 hour system, and 0 - 24
for a 24 hour system; mutually exclusive
with H)
M - Minute (one or two digits ranging
from 0 - 59)
MM - Minute (two digits ranging from 0 - 59;
mutually exclusive with M, minute)
S - Second (one or two digits ranging
from 0 - 59)
SS - Second (two digits ranging from 0 - 59;
mutually exclusive with S)
SSSSS - Second of the day after midnight (5 digits
ranging from 00000 - 86399; mutually
exclusive with other time elements)
UUUUUU - Microsecond (6 digits ranging
from 000000 - 999999)
TT - Meridian indicator (AM or PM)
A default value of 1 is assigned for unspecified YYYY, M, MM, D, DD, or DDD elements. A default value of 0 is assigned for all other unspecified elements. Following is an example of a time stamp format: "YYYY/MM/DD HH:MM:SS.UUUUUU" The following example illustrates how to import data containing user defined date and time formats into a table called schedule: db2 import from delfile2 of del
modified by timestampformat="yyyy.mm.dd hh:mm tt"
insert into schedule
| ||
| 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.
| ||
| zoneddecimal | Loads zoned decimal data, 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.
Half-byte sign values can be one of the following: + = 0xC 0xA 0xE 0xF - = 0xD 0xB Supported values for digits are 0x0 to 0x9. Supported values for zones are 0x3 and 0xF. | ||
| 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.bc
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.bc | ||
| 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.bc | ||
| 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.
bcd
| ||
| keepblanks | Preserves the leading and trailing blanks in each field of type CHAR,
VARCHAR, LONG VARCHAR, or CLOB. Without this option, all leading and
tailing blanks that are not inside character delimiters are removed, and a
NULL is inserted into the table for all blank fields.
The following example illustrates how to load data into a table called TABLE1, while preserving all leading and trailing spaces in the data file: db2 load from delfile3 of del
modified by keepblanks
insert into table1
| ||
| nodoubledel | Suppresses recognition of double character delimiters. For more information, see Delimiter Restrictions. | ||
| 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.