Data Movement Utilities Guide and Reference

File Type Modifiers (Export)

Table 2. Valid File Type Modifiers (Export)

Modifier	Description
All File Formats
lobsinfile	lob-path specifies the path to the files containing LOB values.
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.a 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.a In the following example, coldel; causes the export utility to interpret any semicolon (;) it encounters as a column delimiter: db2 "export to temp of del modified by coldel; select * from staff where dept = 20"
datesiso	Date format. Causes all date data values to be exported in ISO format ("YYYY-MM-DD").b
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.a
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. a Note: x must not be the same character specified as the row, column, or character string delimiter.
nodoubledel	Suppresses recognition of double character delimiters. For more information, see Delimiter Restrictions.
WSF File Format
1	Creates a WSF file that is compatible with Lotus 1-2-3 Release 1, or Lotus 1-2-3 Release 1a.c This is the default.
2	Creates a WSF file that is compatible with Lotus Symphony Release 1.0.c
3	Creates a WSF file that is compatible with Lotus 1-2-3 Version 2, or Lotus Symphony Release 1.1.c
4	Creates a WSF file containing DBCS characters.
Notes: The export 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 export operation fails, and an error code is returned. a Delimiter Restrictions lists restrictions that apply to the characters that can be used as delimiter overrides. b The export utility normally writes date data in YYYYMMDD format char(date) data in "YYYY-MM-DD" format time data in "HH.MM.SS" format time stamp data in "YYYY-MM-DD-HH.MM.SS.uuuuuu" format Data contained in any datetime columns specified in the SELECT statement for the export operation will also be in these formats. c These files can also be directed to a specific product by specifying an L for Lotus 1-2-3, or an S for Symphony in the filetype-mod parameter string. Only one value or product designator may be specified.

Delimiter Restrictions

It is the user's responsibility to ensure that the chosen delimiter character is not part of the data to be moved. If it is, unexpected errors may occur. The following restrictions apply to column, string, DATALINK, and decimal point delimiters when moving data:

• Delimiters are mutually exclusive.
• A delimiter cannot be binary zero, a line-feed character, a carriage-return, or a blank space.
• The default decimal point (.) cannot be a string delimiter.
• The following characters are specified differently by an ASCII-family code page and an EBCDIC-family code page:
  • The Shift-In (0x0F) and the Shift-Out (0x0E) character cannot be delimiters for an EBCDIC MBCS data file.
  • Delimiters for MBCS, EUC, or DBCS code pages cannot be greater than 0x40, except the default decimal point for EBCDIC MBCS data, which is 0x4b.
  • Default delimiters for data files in ASCII code pages or EBCDIC MBCS code pages are:

       " (0x22, double quotation mark; string delimiter)
       , (0x2c, comma; column delimiter)
    

  • Default delimiters for data files in EBCDIC SBCS code pages are:

       " (0x7F, double quotation mark; string delimiter)
       , (0x6B, comma; column delimiter)
    

  • The default decimal point for ASCII data files is 0x2e (period).
  • The default decimal point for EBCDIC data files is 0x4B (period).
  • If the code page of the server is different from the code page of the client, it is recommended that the hex representation of non-default delimiters be specified. For example,

       db2 load from ... modified by chardel0x0C coldelX1e ...
    

The following information about support for double character delimiter recognition in DEL files applies to the export, import, and load utilities:

• Character delimiters are permitted within the character-based fields of a DEL file. This applies to fields of type CHAR, VARCHAR, LONG VARCHAR, or CLOB (except when lobsinfile is specified). Any pair of character delimiters found between the enclosing character delimiters is imported or loaded into the database. For example,

     "What a ""nice"" day!"
  

  will be imported as:

     What a "nice" day!
  

  In the case of export, the rule applies in reverse. For example,

     I am 6" tall.
  

  will be exported to a DEL file as:

     "I am 6"" tall."
  

• In a DBCS environment, the pipe (|) character delimiter is not supported.