Data Migration to DB2

Data migration from an XPS database to a DB2 database currently requires XPS users to perform two tasks before a DB2 database server can load the data:

• insert the data into an external file (called an external table)
• reformat the unloaded data into the LOAD format of DB2.

For supported data types, this feature eliminates the second task by adding the 'DB2' keyword as a FORMAT option in the Table Options clause of the CREATE EXTERNAL TABLE statement, so that data values from an XPS database can be loaded directly from an external table into a DB2 database.

A new format called DB2 is now available to format the external file to match the default export format of DB2 for a file of type del. The environment variables DBDATE (or GL_DATE) and DBMONEY can be used in conjunction with this new format to match the external table format to that required for loading into the DB2 database server. In addition, file type modifiers in DB2 can be used to allow DB2 to understand data layouts. This is particularly the case when the XPS data type DATETIME is converted to the DB2 data type TIMESTAMP, because DBTIME (or GL_DATETIME) cannot be used to influence the data format in an external table. The timestamp modifier is only available in DB2 Version 8.1.2 or later, so it should be noted that full compatibility with DB2 is only available for DB2 versions no earlier than 8.1.2.

The Table Options clause of CREATE EXTERNAL TABLE now accepts the 'DB2' keyword, enclosed within quotation marks, after the FORMAT keyword. Use this option when you prepare data for a DB2 database. See the Guide to SQL: Syntax for the other syntax options of the CREATE EXTERNAL TABLE statement.

For a typical external table in DB2 format, the Table Options clause of the CREATE EXTERNAL TABLE statement should include the following specifications:

• the FORMAT specification should be set to 'db2'
• the DELIMITER specification should be set to "," to match the DB2 default delimiter.

In addition, the following data type considerations apply to an external table of XPS whose records will be loaded into a DB2 database:

• Values in any character column (CHAR, NCHAR, VARCHAR, NVARCHAR and TEXT) must be enclosed between double quotation ( " ) marks, and any literal double quotation mark in the data must be escaped with a double quotation mark. TEXT data values are limited to 32,700 bytes.
• INTEGER, DECIMAL and FLOAT formats are unchanged.
• DATE should match the default DB2 format, by setting the DBDATE environment variable to "Y4MD0".
• DATETIME is unchanged. DATETIME HOUR TO SECOND loads into TIME columns of DB2, and DATETIME YEAR to FRACTION loads into TIMESTAMP columns. An important point to note is that the file type modifiers timeformat and timestampformat need to be specified in the LOAD statement of DB2. The timestampformat modifier is only available in DB2 Version 8.1.2 or later.
• MONEY requires DBMONEY to be set to "''." to remove the currency symbol, because there is no monetary data type in DB2. The DECIMAL datatype in DB2 needs to be used.
• BYTE must be in raw format, limited to 32,700 bytes and enclosed between quotation marks.

XPS does not warn of truncation, if the original data length of a BYTE or TEXT value is longer than 32,700 bytes.