File type modifiers for export

Table 20. Valid file type modifiers for export: All file formats

Modifier

Description

lobsinfile

lob-path specifies the path to the files containing LOB data. Each path contains at least one file that contains at least one LOB pointed to by a Lob Location Specifier (LLS) in the data file. The LLS is a string representation of the location of a LOB in a file stored in the LOB file path. The format of an LLS isfilename.ext.nnn.mmm/, where filename.ext is the name of the file that contains the LOB, nnn is the offset in bytes of the LOB within the file, and mmm is the length of the LOB in bytes. For example, if the string db2exp.001.123.456/ is stored in the data file, the LOB is located at offset 123 in the file db2exp.001, and is 456 bytes long. |If you specify the "lobsinfile" modifier when using EXPORT, the LOB data |is placed in the locations specified by the LOBS TO clause. Otherwise the |LOB data is sent to the current working directory. The LOBS TO clause specifies one |or more paths to directories in which the LOB files are to be stored. There |will be at least one file per LOB path, and each file will contain at least|one LOB. To indicate a null LOB , enter the size as -1. If the size is specified as 0, it is treated as a 0 length LOB. For null LOBS with length of -1, the offset and the file name are ignored. For example, the LLS of a null LOB might be db2exp.001.7.-1/.

Table 21. Valid file type modifiers for export: DEL (delimited ASCII) file format

Modifier	Description
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.2 If you want to explicitly specify the double quotation mark as the character string delimiter, it should be specified as follows: modified by chardel"" The single quotation mark (') can also be specified as a character string delimiter as follows: modified by chardel''
codepage=x	x is an ASCII character string. The value is interpreted as the code page of the data in the output data set. Converts character data to this code page from the application code page during the export operation. For pure DBCS (graphic), mixed DBCS, and EUC, delimiters are restricted to the range of x00 to x3F, inclusive. Note: The   codepage  modifier cannot be used with the   lobsinfile  modifier.
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.2 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").3
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.2
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 can have more than one sub-value. 2 Note: x  must not be the same character specified as the row, column, or character string delimiter.
2nochardel	2Column data will not be surrounded by character delimiters. This option should 2not be specified if the data is intended to be imported or loaded using DB2. 2It is provided to support vendor data files that do not have character delimiters. 2Improper usage might result in data loss or corruption.22 This option cannot be specified with chardelx or nodoubledel. 2These are mutually exclusive options.
nodoubledel	Suppresses recognition of double character delimiters.2
3striplzeros	3Removes the leading zeros from all exported decimal columns.33 Consider the following example:3 3 db2 create table decimalTable ( c1 decimal( 31, 2 ) ) 3 db2 insert into decimalTable values ( 1.1 ) 3 3 db2 export to data of del select * from decimalTable 3 3 db2 export to data of del modified by STRIPLZEROS 3 select * from decimalTable 3In the first export operation, the content of the exported file data will be 3+00000000000000000000000000001.10. In the second operation, which is identical to the 3first except for the striplzeros modifier, the content of the exported file data will be +1.10.
2timestampformat="x"	2x is the format of the time stamp in the source 2file.4 Valid time stamp elements are:22 YYYY - Year (four digits ranging from 0000 - 9999) 2 M - Month (one or two digits ranging from 1 - 12) 2 MM - Month (two digits ranging from 01 - 12; 2 mutually exclusive with M and MMM) 2 MMM - Month (three-letter case-insensitive abbreviation for 2 the month name; mutually exclusive with M and MM) 2 D - Day (one or two digits ranging from 1 - 31) 2 DD - Day (two digits ranging from 1 - 31; mutually exclusive with D) 2 DDD - Day of the year (three digits ranging from 001 - 366; 2 mutually exclusive with other day or month elements) 2 H - Hour (one or two digits ranging from 0 - 12 2 for a 12 hour system, and 0 - 24 for a 24 hour system) 2 HH - Hour (two digits ranging from 0 - 12 2 for a 12 hour system, and 0 - 24 for a 24 hour system; 2 mutually exclusive with H) 2 M - Minute (one or two digits ranging from 0 - 59) 2 MM - Minute (two digits ranging from 0 - 59; 2 mutually exclusive with M, minute) 2 S - Second (one or two digits ranging from 0 - 59) 2 SS - Second (two digits ranging from 0 - 59; 2 mutually exclusive with S) 2 SSSSS - Second of the day after midnight (5 digits 2 ranging from 00000 - 86399; mutually 2 exclusive with other time elements) 2 UUUUUU - Microsecond (6 digits ranging from 000000 - 999999; 2 mutually exclusive with all other microsecond elements) 2 UUUUU - Microsecond (5 digits ranging from 00000 - 99999, 2 maps to range from 000000 - 999990; 2 mutually exclusive with all other microseond elements) 2 UUUU - Microsecond (4 digits ranging from 0000 - 9999, 2 maps to range from 000000 - 999900; 2 mutually exclusive with all other microseond elements) 2 UUU - Microsecond (3 digits ranging from 000 - 999, 2 maps to range from 000000 - 999000; 2 mutually exclusive with all other microseond elements) 2 UU - Microsecond (2 digits ranging from 00 - 99, 2 maps to range from 000000 - 990000; 2 mutually exclusive with all other microseond elements) 2 U - Microsecond (1 digit ranging from 0 - 9, 2 maps to range from 000000 - 900000; 2 mutually exclusive with all other microseond elements) 2 TT - Meridian indicator (AM or PM) 22 Following is an example of a time stamp format:2 2 "YYYY/MM/DD HH:MM:SS.UUUUUU" 22 The MMM element will produce the following values: 2'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 2and 'Dec'. 'Jan' is equal to month 1, and 'Dec' is equal to month 12. 22 The following example illustrates how to export data containing user-defined time stamp 2formats from a table called 'schedule':2 2 db2 export to delfile2 of del 2 modified by timestampformat="yyyy.mm.dd hh:mm tt" 2 select * from schedule

Table 22. Valid file type modifiers for export: WSF file format

Modifier	Description
1	Creates a WSF file that is compatible with Lotus 1-2-3 Release 1, or Lotus 1-2-3 Release 1a.5 This is the default.
2	Creates a WSF file that is compatible with Lotus Symphony Release 1.0.5
3	Creates a WSF file that is compatible with Lotus 1-2-3 Version 2, or Lotus Symphony Release 1.1.5
4	Creates a WSF file containing DBCS characters.

Notes:

1. 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.
2. Delimiter restrictions for moving data lists restrictions that apply to the characters that can be used as delimiter overrides.
3. 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.
4. 2For time stamp formats, care must be taken to avoid ambiguity 2between the month and the minute descriptors, since they both use the letter 2M. A month field must be adjacent to other date fields. A minute field must 2be adjacent to other time fields. Following are some ambiguous time stamp 2formats:22

      "M" (could be a month, or a minute)
   2    "M:M" (Which is which?)
   2    "M:YYYY:M" (Both are interpreted as month.)
   2    "S:M:YYYY" (adjacent to both a time value and a date value) 

   In ambiguous 2cases, the utility will report an error message, and the operation will fail.22

   Following are some unambiguous time stamp formats:2

   2

      "M:YYYY" (Month)
   2    "S:M" (Minute)
   2    "M:YYYY:S:M" (Month....Minute)
   2    "M:H:YYYY:M:D" (Minute....Month) 

5. 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 can be specified.

Related reference

• db2Export - Export
• EXPORT Command
• Delimiter restrictions for moving data

This topic can be found in: Command Reference.

 

 

原文：http://www-01.ibm.com/support/knowledgecenter/SSEPGG_8.2.0/com.ibm.db2.udb.doc/core/r0011046.htm