Section 12.2. Data Movement File Formats

12.2. Data Movement File Formats

Before learning about moving data between DB2 databases and/or other data sources, it is important to first understand the file formats that the data movement utilities use. You can choose from five different file formats:

• Delimited ASCII (DEL)

• Non-delimited ASCII (ASC)

• PC version of Integrated Exchange Format (PC/IXF)

• Worksheet format (WSF)

• Cursor

12.2.1. Delimited ASCII (DEL) Format

As the name implies, this file format contains a stream of ASCII characters that are separated by row and column delimiters. Comma (,) is the default column delimiter and the carriage return is the default row delimiter. For character strings, DB2 uses double quotes (" ") as the string delimiter. For example, a DEL file will look similar to Figure 12.2. Note that all the string data is surrounded by a pair of double quotes and each column value is separated by a comma.

Figure 12.2. Sample DEL file

12.2.2. Non-Delimited ASCII (ASC) Format

The ASC file format is also known as fixed length ASCII file format because each column length in the file has the same length as defined for the corresponding column definition in the table. For example, variable-length character column definitions in a table are padded with blanks in an ASC file and represented using their maximum length. Figure 12.3 shows the same data as in Figure 12.2 but in ASC format.

Figure 12.3. Sample ASC file

12.2.3. PC Version of IXF (PC/IXF) Format

PC/IXF (or simply IXF) files cannot be edited with a normal text editor. It uses the IXF data interchange architecture, which is a generic relational database exchange format that lets you move data among DB2 databases. PC/IXF can only be used for moving data between DB2 databases because it is an IBM proprietary format. In addition to data, the file also contains the data types and structure of the table. Therefore it can be used to first create the table in the target database and then import data.

12.2.4. WSF Format

WSF files are Lotus 1-2-3 and Symphony worksheets that the database manager supports. Any file names with these extensions are accepted: WKS, WK1, WRK, WR1, and WJ2. WSF files are mainly used for moving data between DB2 and these worksheets.

12.2.5. Cursor

The cursor format lets you load data into a table using a cursor. The cursor must be declared against an SQL query first before it can be referenced in the load command. You can only use the cursor file format with the load utility. Section 12.5.2.2, Loading from a CURSOR, shows how to use this format.