DB2 data movement utilities

DB2 data movement utilities. The utilities provide a way to move data from one database to another. The source and target databases can be the same instance, in different instances on the same server, on different servers on the same platform, or on different platforms entirely.






To extract data from a table in a database, you use the export utility. The import and load utilities insert data from the input files into a specified table. db2move is a batch version of the data movement utilities; it can export, import, or load multiple tables with just one command.

Input/Output File Types:

1.  Delimited ASCII (DEL)
2.  Non-delimited ASCII (ASC)
3.  PC version of Integrated Exchange Format (PC/IXF)
4.  Worksheet format (WSF)
5.  Cursor

Delimited ASCII (DEL) Format: 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.

 

Non-delimited ASCII (ASC) : 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.

PC version of Integrated Exchange Format (PC/IXF) : 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.

 

Worksheet format (WSF) : 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.

 

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 cursorfile format with the load utility.