Table 5. Valid File Type Modifiers (Import)
| Modifier | Description | ||
|---|---|---|---|
| All File Formats | |||
| compound=x | x is a number between 1 and 100 inclusive (7 on DOS/Windows). Uses nonatomic compound SQL to insert the data, and x statements will be attempted each time. | ||
| lobsinfile | lob-path specifies the path to the files containing LOB values. | ||
| no_type_id | Valid only when importing into a single sub-table. Typical usage is to export data from a regular table, and then to invoke an import operation (using this modifier) to convert the data into a single sub-table. | ||
| 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:
| ||
| 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) | |||
| 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 | |||
| nochecklengths | If nochecklengths is specified, an attempt is made to import 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 imported 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. | ||
| 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.
In the following example, striptblanks causes the import utility to truncate trailing blank spaces: db2 import from myfile.asc of asc
modified by striptblanks
method l (1 10, 12 15) messages msgs.txt
insert into staff
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. In the following example, chardel'' causes the import utility to interpret any single quotation mark (') it encounters as a character string delimiter: db2 "import from myfile.del of del
modified by chardel''
method p (1, 4) insert into staff (id, years)"
| ||
| 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
In the following example, coldel; causes the import utility to interpret any semicolon (;) it encounters as a column delimiter:
db2 import from myfile.del of del
modified by coldel;
messages msgs.txt insert into staff
| ||
| datesiso | Date format. Causes all date data values to be imported 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
In the following example, decpt; causes the import utility to interpret any semicolon (;) it encounters as a decimal point: db2 "import from myfile.del of del
modified by chardel'
decpt; messages msgs.txt insert into staff"
| ||
| 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 import ... 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.
ab
| ||
| 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 import each row. | ||
| indexixf | Directs the utility to drop all indexes currently defined on the existing table, and to create new ones from the index definitions in the PC/IXF file. This option can only be used when the contents of a table are being replaced. It cannot be used with a view, or when a insert-column is specified. | ||
| indexschema=schema | Uses the specified schema for the index name during index creation. If schema is not specified (but the keyword indexschema is specified), uses the connection user ID. If the keyword is not specified, uses the schema in the IXF file. | ||
| nochecklengths | If nochecklengths is specified, an attempt is made to import 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 imported 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:
| |||