Data movement file formats

 

The Db2 data movement utilities recognize and support four different file formats; they are the following:

 Delimited ASCII (DEL): This file format is used in several relational database management systems. In this format, each row is extracted to a single line. Within each line, data values are separated by a delimiter, a unique character other than data values themselves. By default, the column delimiter is a comma, the row delimiter is a new line character (0x0A), and the character delimiter is a double quotation mark (").

For example, if you export a table data in DEL format using default delimiters, the content could look similar to the following:

"000030","SALLY","C01","MANAGER "

"000050","JOHN","E01","MANAGER "

"000110","VINCENZO","A00","SALESREP"

"000120","SEAN","A00","CLERK   "

"000150","BRUCE","D11","DESIGNER"

"000160","ELIZABETH","D11","DESIGNER"

 

Non-delimited or fixed-length ASCII (ASC): The non-delimited ASCII file format is also used by multiple software and database applications. In this format, each row is also extracted to a single line. Instead of using a delimiter, the data values have a fixed length and the position of each value in the file determines the column associated with the data. This file format is also known as the fixed-length ASCII file format.

For example, data could be extracted from a table called EMPLOYEE with the following definition:

 Select * from EMPLOYEE;

This would produce a file that looks like the following:

000030SALLY       C01MANAGER

000050JOHN        E01MANAGER

PC Integration Exchange Format (PC/IXF): The PC/IXF file format is a special file format used exclusively to move data between different Db2 databases. In this file format, table definitions and associated index definitions are stored in the export file, along with data.

Unlike DEL and ASC formats, PC/IXF is not stored in a native format readable by the user. This option is only for copying data between Db2 databases. It cannot be used to move data between Db2 and other vendor databases.

 Cursor: This is not exactly a file format type, but a way to move data between Db2 databases using the LOAD command.

For example, if you want to load a table data from one database to another within the same instance or across the instances, you can execute the following statements:

DECLARE c1 CURSOR FOR SELECT * FROM employee;

LOAD FROM c1 OF CURSOR MESSAGES /ds/exp/load.msg INSERT INTO employee;