DB2 migration

 Top  Previous  Next

Data coming from IBM DB2 for mainframe systems, must be converted into ASCII and loaded into the target database. XCONV contains utilities to speed-up data movement from mainframe IBM DB2 to Oracle Database, IBM UDB and Microsoft SQL Server.

The pre-requisites for the migration are the mainframe DDL scripts (CREATE/ALTER TABLE, INDEX, etc.) and EBCDIC data files.

The migration process is composed by 2 macro-activities:

•data downloading from mainframe
•data conversion and uploading on the target database

The first step is performed on the mainframe, and must be run according to the procedure described in the following pages.

The second step varies depending on the target database, and therefore it is separately documented for each target database supported by XCONV.

The DB2 migration relies basically on the XCONV Data Migration Toolkit, and in particular on xddlconv.

Do not forget that once data as been migrated into the target database, you also have to adapt the E/SQL syntax of SQL commands embedded in source programs. For this purpose please refer to theCOBOL programs migration guide and to esqlconv.

Data types correspondency

Some of the DB2 data types are not supported or have different behaviour on the target databases. These are the correspondency/transformation tables for critical data types used by the XFRAME migration tools.

DB2 to Oracle

TIME

DATE or CHAR(8)*

TIMESTAMP

TIMESTAMP

SMALLINT

INTEGER

*depending on xddlconv conversion options (-t)

DB2 to Microsoft SQL/Server

DATE

DATETIME

TIME

DATETIME or CHAR(8)

TIMESTAMP

CHAR(26)

Note: For SQL Server, TIMESTAMP is converted into CHAR(26) (and not in DATETIME) because datetime values are rounded to increments of .000, .003, or .007 seconds, and therefore inappropriate for DB2 conversion, in many cases. For more information check the Microsoft web site at http://msdn2.microsoft.com/en-us/library/ms187819.aspx.

TIME type conversion

As described above, the XFRAME database migration tools may convert the TIME types into target native DATE/DATETIME or CHAR(8).  At a first sight, converting TIME into DATE/DATETIME looks like the most obvious choice, but you must consider that this strategy will lead to large code changes. This is because both DATE or TIME columns, after the migration, are stored into a DATE/DATETIME columns. In order to be correctly managed by COBOL programs, this columns must be properly converted in the EXEC SQL statements, by means of the functions TO_DATE/TO_CHAR (oracle) and CONVERT/CAST (SQL Server). This means that a large set of code changes must be applied to the COBOL code, making programs harder to maintain.

On the other side, converting TIME types into CHAR(8) has many adavatages:

•the code can take easily advantage of the default DATE format settable through the XCICS/TS and XEBE runtimes
•no code changes or conversion function required to manage TIME columns
•largely reduced usage of DATE conversion functions
•better code maintainability
•no behaviour/performance impact on the SQL statement execution

Therefore,  we suggest to convert the TIME types into CHAR(8).