Data Movement Utilities Guide and Reference

File Type Modifiers (Load)

Table 8. 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). Note: The load utility will not attempt to validate generated column values when this option is used. 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. Note: The load utility will not attempt to maintain or verify the uniqueness of values in the table's identity column when this option is used.
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 Chapter 4, AutoLoader) 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. Note: The PCTFREE value of a table determines the amount of free space designated per page. If a pagefreespace value on the load operation or a PCTFREE value on a table have not been set, the utility will fill up as much space as possible on each page. The value set by pagefreespace overrides the PCTFREE value specified for the table.
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: For DEL files: ",," is specified for the column For DEL/ASC/WSF files: A row that does not have enough columns, or is not long enough for the original specification. Without this option, if a source column contains no data for a row instance, one of the following occurs: If the column is nullable, a NULL is loaded If the column is not nullable, the utility rejects the row.
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: For pure DBCS (graphic), mixed DBCS, and EUC, delimiters are restricted to the range of x00 to x3F, inclusive. For DEL data specified in an EBCDIC code page, the delimiters may not coincide with the shift-in and shift-out DBCS characters. nullindchar must specify symbols included in the standard ASCII set between code points x20 and x7F, inclusive. This refers to ASCII symbols and code points. EBCDIC data can use the corresponding symbols, even though the code points will be different.
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: In a partitioned database environment, the path should be local to the loading node, so that concurrently running load operations do not attempt to write to the same file. The contents of the file are written to disk in an asynchronous buffered mode. In the event of a failed or an interrupted load operation, the number of records committed to disk cannot be known with certainty, and consistency cannot be guaranteed after a LOAD RESTART. The file can only be assumed to be complete for a load operation that starts and completes in a single pass. This modifier does not support file names with multiple file extensions. For example, dumpfile = /home/svtdbm6/DUMP.FILE is acceptable to the load utility, but dumpfile = /home/svtdbm6/DUMP.LOAD.FILE is not.
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: No conversion between data types is performed, with the exception of BIGINT, INTEGER, and SMALLINT. Data lengths must match their target column definitions. FLOATs must be in IEEE Floating Point format. Binary data in the load source file is assumed to be big-endian, regardless of the platform on which the load operation is running. Note: NULLs cannot be present in the data for columns affected by this modifier. Blanks (normally interpreted as NULL) are interpreted as a binary value when this modifier is used.
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 Note: NULLs cannot be present in the data for columns affected by this modifier. Blanks (normally interpreted as NULL) are interpreted as a binary value when this modifier is used. Regardless of the server platform, the byte order of binary data in the load source file is assumed to be big-endian; that is, when using this modifier on OS/2 or on the Windows operating system, the byte order must not be reversed.
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. Note: This option replaces the obsolete t option, which is supported for back-level compatibility only.
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. Note: This option replaces the obsolete padwithzero option, which is supported for back-level compatibility only.
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 Note: x must not be the same character specified as the row, column, or character string delimiter.
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: 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 operation fails, and an error code is returned. a Double quotation marks around the date format string are mandatory. Field separators cannot contain any of the following: a-z, A-Z, and 0-9. The field separator should not be the same as the character delimiter or field delimiter in the DEL file format. A field separator is optional if the start and end positions of an element are unambiguous. Ambiguity can exist if (depending on the modifier) elements such as D, H, M, or S are used, because of the variable length of the entries. For time stamp formats, care must be taken to avoid ambiguity between the month and the minute descriptors, since they both use the letter M. A month field must be adjacent to other date fields. A minute field must be adjacent to other time fields. Following are some ambiguous time stamp formats: "M" (could be a month, or a minute) "M:M" (Which is which?) "M:YYYY:M" (Both are interpreted as month.) "S:M:YYYY" (adjacent to both a time value and a date value) In ambiguous cases, the utility will report an error message, and the operation will fail. Following are some unambiguous time stamp formats: "M:YYYY" (Month) "S:M" (Minute) "M:YYYY:S:M" (Month....Minute) "M:H:YYYY:M:D" (Minute....Month) Note: Some characters, such as double quotation marks and back slashes, must be preceded by an escape character (for example, \). b The character must be specified in the code page of the source data. The character code point (instead of the character symbol), can be specified using the syntax xJJ or 0xJJ, where JJ is the hexadecimal representation of the code point. For example, to specify the # character as a column delimiter, use one of the following: ... modified by coldel# ... ... modified by coldel0x23 ... ... modified by coldelX23 ... c Delimiter Restrictions lists restrictions that apply to the characters that can be used as delimiter overrides. d Even if the DATALINK delimiter character is a valid character within the URL syntax, it will lose its special meaning within the scope of the load operation.