Oracle Tuxedo Application Rehosting Workbench DB2 to Oracle Converter

Overview

Purpose

This chapter describes how to install, implement, and configure the Rehosting Workbench DB2- to-Oracle converter in order to migrate files from a source DB2 database to a target Oracle database.

Skills

When migrating DB2, a good knowledge of COBOL, JCL, z/OS utilities, DB2 and Oracle databases as well as UNIX/Linux Korn Shell is required.

See also

For a comprehensive view of the migration process, see the Oracle Tuxedo Application Rehosting Workbench Reference Guide for the chapters Data Conversion and Cobol Conversion as well as the COBOL Converter chapter of this guide.

Organization

Migrating data files is described in the following sections:

•	The DB2- to-Oracle Migration Process

•	Reengineering rules to implement

•	Preparing the environment

•	Generating the components

•	Performing the migration

•	Troubleshooting

The DB2- to-Oracle Migration Process

File Organizations Processed

When migrating from a z/OS DB2 source platform to an Oracle UNIX target platform, the first question to ask is, which tables should be migrated?. When not all DB2 tables are to be migrated, a DB2 DDL representing the sub-set of objects to be migrated should be built.

Migration Process Steps

The principle steps in the DB2- to-Oracle migration process, explained in detail in the rest of this chapter, are:

1.	Create an overall list of the tables to be migrated. When not all of the DB2 tables are to be migrated, build a DB2 DDL for those tables to be migrated; otherwise take the entire DB2 DDL used by the site.

2.	Prepare the environment to be used to generate the components.

3.	Using the Rehosting Workbench generate the components used in the following steps.

4.	Unload the tables from the source platform.

5.	Transfer the data to the target platform.

6.	Transcode and reload the data.

7.	Check the results.

8.	Build the Oracle database.

Interaction with other Oracle Tuxedo Application Rehosting Workbench tools

The DB2-to-Oracle migration is dependent on the results of the Cataloger; the DB2-to-Oracle migration impacts the COBOL conversion and should be completed before beginning the program conversion work.

Reengineering rules to implement

This section describes the reengineering rules applied by the Rehosting Workbench when migrating data from a DB2 database to an Oracle database.

Migration rules applied

The list of DB2 objects that are included in the migration towards Oracle are described in Creating the Oracle objects.

Migrated DB2 objects keep their names when migrated to Oracle except for the application of the Rehosting Workbench renaming rules (see Preparing and implementing renaming rules).

DB2-to-Oracle data type migration rules

Table 4‑1 DB2 to Oracle data types

DB2 z/OS data type	Oracle format
CHAR(length)	Becomes CHAR if length <= 2000 bytes Becomes VARCHAR2 if length > 2000 bytes
VARCHAR(length)	VARCHAR2 (length)
DECIMAL(…)	NUMBER(…)
NUMERIC(…)	NUMBER(…)
DEC(…)	NUMBER(…)
SMALLINT	NUMBER(6)
INTEGER	NUMBER(11)
TIMESTAMP	TIMESTAMP
TIMESTMP	TIMESTAMP
DATE	DATE
TIME	DATE
DOUBLE	FLOAT(53)
FLOAT(prec)	FLOAT(53)
REAL	FLOAT(24)

DB2-to-Oracle column property migration rules

A column property can change the behavior of an application program.

The following table shows all of the DB2 column properties and how they are converted for the target Oracle database.

Table 4‑2 DB2 to Oracle column properties

DB2 column properties	Oracle format	Notes
WITH DEFAULT	DEFAULT <value>	<value> depends on DB2/z/OS data type.
WITH DEFAULT ''	CHAR: DEFAULT ‘ ‘ VARCHAR2: DEFAULT ‘ ‘	A zero byte length in DB2 becomes a NULL flag on Oracle.
WITH DEFAULT '<value>'	DEFAULT '<value>'
NOT NULL	NOT NULL
IDENTITY	Create a Sequence Create a Trigger	As the IDENTITY attribute does not exist on Oracle, the Rehosting Workbench replaces the attribute by Sequence and Trigger objects.
FOR SBCS …	Attribute ignored

Preparing and implementing renaming rules

Oracle Tuxedo Application Rehosting Workbench permits the modification of the different names in the DDL source file (table name, column name).

Renaming rules can be implemented for the following cases:

•	When Oracle reserved words are found in the DB2 DDL source file.

•	When there is a desire to perform a reengineering of the DDL source file.

Note:

If, when executing the Rehosting Workbench, an Oracle reserved word is found in the DDL source, an error is reported and the Rehosting Workbench continues the analysis of the DDL.

Renaming rules should be placed in a file named rename-objects-<schema name>.txt. This file should be placed in the directory indicated by the $PARAM/rdbms parameter.

Renaming rules have the following format:

•

table:

table;<schema name>;<DB2 table name>;<Oracle table name>

•

column:

Column;<schema name>;<DB2 table name>;<DB2 column name>;<Oracle column name>

Comments can be added as following: % Text.

Example:

% Modification applied to the AUALPH0T table

column;AUANPR0U;AUALPH0T;NUM_ALPHA;MW_NUM_ALPHA

Example of a migration of DB2 objects

In this example, the DB2 DDL contains a table named ODCSF0 with a primary key and a unique index named XCUSTIDEN:

Listing 4‑1 DDL example before migration

DROP TABLE ODCSF0;

COMMIT;

CREATE TABLE ODCSF0

(CUSTIDENT DECIMAL(6, 0) NOT NULL,

CUSTLNAME CHAR(030) NOT NULL,

CUSTFNAME CHAR(020) NOT NULL,

CUSTADDRS CHAR(030) NOT NULL,

CUSTCITY CHAR(020) NOT NULL,

CUSTSTATE CHAR(002) NOT NULL,

CUSTBDATE DATE NOT NULL,

CUSTEMAIL CHAR(040) NOT NULL,

CUSTPHONE CHAR(010) NOT NULL,

PRIMARY KEY(CUSTIDENT))

IN DBPJ01A.TSPJ01A

CCSID EBCDIC;

COMMIT;

CREATE UNIQUE INDEX XCUSTIDEN

ON ODCSF0

(CUSTIDENT ASC) USING STOGROUP SGPJ01A;

COMMIT;

 

After applying the migration rules, and without implementing any renaming rules the following Oracle objects are obtained:

Listing 4‑2 Oracle table example after migration

WHENEVER SQLERROR CONTINUE;

DROP TABLE ODCSF0 CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE ODCSF0 (

CUSTIDENT NUMBER(6) NOT NULL,

CUSTLNAME CHAR(30) NOT NULL,

CUSTFNAME CHAR(20) NOT NULL,

CUSTADDRS CHAR(30) NOT NULL,

CUSTCITY CHAR(20) NOT NULL,

CUSTSTATE CHAR(2) NOT NULL,

CUSTBDATE DATE NOT NULL,

CUSTEMAIL CHAR(40) NOT NULL,

CUSTPHONE CHAR(10) NOT NULL);

 

Listing 4‑3 Oracle index example after migration

WHENEVER SQLERROR CONTINUE;

DROP INDEX XCUSTIDEN;

WHENEVER SQLERROR EXIT 3;

CREATE UNIQUE INDEX XCUSTIDEN ON ODCSF0

(

CUSTIDENT ASC

);

 

Listing 4‑4 Oracle constraint example after migration

WHENEVER SQLERROR CONTINUE;

ALTER TABLE ODCSF0 DROP CONSTRAINT CONSTRAINT_01;

WHENEVER SQLERROR EXIT 3;

ALTER TABLE ODCSF0 ADD

CONSTRAINT CONSTRAINT_01 PRIMARY KEY (CUSTIDENT);

 

Preparing the environment

This section describes the tasks to perform before generating the components to be used to migrate the DB2 data to Oracle.

Implementing the cataloging of the DB2 DDL source files

The DB2 DDL source files to be migrated are located when preparing for the catalog operations. During the migration process, all valid DB2 syntaxes are accepted, although only the SQL CREATE command is handled and migrated to Oracle.

system.desc file parameters

For a DB2-To-Oracle migration, two parameters must be set in the system.desc System description file that is used by all of the Rehosting Workbench tools:

•

DBMS=DB2.

Indicates the type of RDBMS to migrate.

•	DBMS-VERSION="8".

Indicates the version of the RDBMS to migrate.

Schemas

A schema should consist of a coherent set of objects (for example there should be no CREATE INDEX for a table that does not exist in the schema).

By default, if the SQL commands of the DB2 DDL are prefixed by a qualifier or an authorization ID, the prefix is used by the Rehosting Workbench as the name of the schema—for example, CREATE TABLE <qualifier or authorization ID>.table name.

The schema name can also be determined by the Rehosting Workbench using the global-options clause of the system.desc file.

For example:

system STDB2ORA root ".."

global-options

catalog="..",

sql-schema=<schema name>.

Implementing the configuration files

Only one file needs to be placed in the Rehosting Workbench file structure as described by $PARAM:

•	db-param.cfg,

Two other configuration files:

•	rdbms-templates.txt

•	rdbms-move-assignation.pgm

are automatically generated in the file structure during the installation of the Rehosting Workbench. If specific versions of these files are required, they will be placed in the $PARAM/rdbms file structure.

Initializing environment variables

Before executing the Rehosting Workbench set the following environment variables:

•	export TMPPROJECT=/$home/tmp

— the location for storing temporary objects generated by the process.

•	export PARAM=$HOME/param

— the location of the configuration files.

Generation parameters

Listing 4‑5 Example db-param.cfg file

#

# This configuration file is used by FILE & RDBMS converter

# Lines beginning by "#" are ignored

# write information in lower case

#

# common parameters for FILE and RDBMS

# source information is written into system descriptor file (OS, DBMS=, # DBMS-VERSION=)target_rdbms_name:oracle

target_rdbms_version:11

target_os:unix

#

# specific parameters for FILE to RDBMS conversion

file:char_limit_until_varchar:29

# specific parameters for RDBMS conversion

rdbms:date_format:YYYY/MM/DD

rdbms:timestamp_format:YYYY/MM/DD HH24 MI SS

rdbms:time_format:HH24 MI SS

# rename object files

# the file param/rdbms/rename-objects-<schema>.txt is automatically loaded by # the tool if it exists.

 

Only the parameters target_<xxxxx> and rdbms:<xxxxx> need to be adapted.

•	target_rdbms_name: oracle

name of the target RDBMS.

•	target_rdbms_version:11

version of the target RDBMS.

•	target_os:unix

Name of the target operating system.

The three rdbms parameters indicate the date, timestamp and time formats used by z/OS DB2 and stored in DSNZPARM. These parameters impact the reloading operations and the COBOL date and time manipulations.

WARNING:

A correct setting of these parameters is essential.

•	rdbms:date_format: YYYY/MM/DD

•	rdbms:timestamp_format:YYYY/MM/DD HH24 MI SS

•	rdbms:time_format:HH24 MI SS

Generating the components

To generate the components used to migrate data from DB2 databases to Oracle databases, the Rehosting Workbench uses the rdbms.sh command. This section describes the command.

rdbms.sh

Name

rdbms.sh — Generate DB2 to Oracle database migration components.

Synopsis

rdbms.sh [ [-c|-C] [-g] [-m] [-r] [-i <installation directory>] <schema name> ] -s <installation directory> (<schema name>,...) ]

Description

rdbms.sh generates the Rehosting Workbench components used to migrate z/OS DB2 databases to UNIX Oracle databases.

Options

Generation options

-g <schema name>

The unloading and loading components are generated in $TMPPROJECT using the information provided by the configuration files.

-C <schema name>

The following components are generated in $TMPPROJECT: DDL Oracle, CTL files of the SQL*LOADER, XML file used by the COBOL converter, configuration files (mapper.re and Datamap.re). If an error or warning is encountered, the process will not abort.

See Executing the transcoding and reloading scripts for information about the SQL scripts created during the generation operation.

-c <schema name>

This option has the same result as the -C option except the process will abort if an error or warning is generated.

Modification options

-m <schema name>

Makes the generated shell scripts executable. The COBOL programs are adapted to MICROFOCUS COBOL fixed format. When present, the shell script that modifies the generated source is executed.

-r <schema name>

Removes the schema name from the generated objects (create table, table name, CTL file for SQL*LOADER, KSH). When this option is used, the name of the schema can also be removed from the COBOL components by using the option: sql-remove-schema-qualifier located in the config-cobol file (COBOL conversion configuration file) used when converting the COBOL components.

Installation Option

-i <installation directory> <schema name>

Places the components in the installation directory. This operation uses the information located in the rdbms-move-assignation.pgm file.

Generate configuration files for COBOL conversion

-s <installation directory> <schema name>,...)

Enables the generation of the COBOL convertor configuration file. This file takes all of the unitary XML files of the project. All these files are created in $PARAM/dynamic-config.

Example: rdbms-conv.txt rdbms-conv-PJ01DB2.xml

Example

rdbms.sh -Cgrmi $HOME/trf PJ01DB2

Using the make utility

Make is a UNIX utility intended to automate and optimize the construction of targets (files or actions).

You should have a descriptor file named makefile in the source directory in which all operations are implemented (a makefile is prepared in the source directory during the initialization of a project).

The next two sections describe configuring a make file and how to use the Rehosting Workbench DB2-To-Oracle Converter functions with a make file.

Configuring a make file

Version.mk

The version.mk configuration file in $PARAM is used to set the variables and parameters required by the make utility.

In version.mk specify where each type of component is installed and their extensions, as well as the versions of the different tools to be used. This file also describes how the log files are organized.

The following general variables should be set at the beginning of migration process in the version.mk file:

•	ROOT_PROJECT

•	DIR_TRADDATA

•	TOOLS_DATA

•

LOGDIR

In addition, the RDBMS_SCHEMAS variable is specific to DB2 migration, it indicates the different schemas to process.

This configuration should be complete before using the make file.

make file contents

The contents of the makefile summarize the tasks to be performed:

•	Including vesion.mk

•	Controlling if the main variables are set

•	Collecting the value of variables and building the required lists to process.

•	Implementing the targets.

A makefile and a version.mk file are provided with the Rehosting Workbench Simple Application.

Using a makefile with the Rehosting Workbench DB2-To-Oracle Converter

The make RdbmsConvert command can be used to launch the Rehosting Workbench DB2-To-Oracle Converter. It enables the generation of the components required to migrate a DB2 database to Oracle.

The make file launches the rdbms.sh tool with the -C, -g, -r, -m and -i options, for all schemas contained in the RDBMS_SCHEMAS variable.

Locations of generated files

The unloading and loading components generated with the -i $HOME/trf option are placed in the following locations:

Table 4‑3 Location of components

Location	Contents
$HOME/trf/unload/rdbms/<schema name>	The JCL used for each unloading are generated for each <schema name>.
$HOME/trf/SQL/rdbms/<schema name>	Location by <schema name> of the SQL scripts used to create the Oracle objects. For the example used in this chapter there are three scripts: • INDEX-ODCSF0.sql • TABLE-ODCSF0.sql • CONSTRAINT-ODCSF0.sql
$HOME/trf/reload/rdbms/<schema name>/src	Location by <schema name> of the COBOL transcoding programs. For the example in this chapter, there is one program: • MOD_ODCSF0.cbl
$HOME/trf/reload/rdbms/<schema name>/ctl	Location by <schema name> of the CTL files used by SQL*LOADER. For the example in this chapter, there is one CTL: • ODCSF0.ctl
$HOME/trf/reload/rdbms/<schema name>/ksh	Location by <schema name> of the reloading Korn shell scripts. For the example in this chapter, there is one script: • loadrdbms-ODCSF0.ksh
$TMPPROJECT/outputs/<schema name>	The generation log files produced when using the -c or -C options. rdbms-converter-<schema name> can be used to resolve problems.

Modifying generated components

The generated components may be modified using a project’s own scripts. these scripts (sed, awk, perl,…) should be placed in:

$PARAM/rdbms/rdbms-modif-source.sh

When present, this file will be automatically executed at the end of the generation process. It will be called using the <schema name> as an argument.

Performing the migration

This section describes the tasks of unloading, transfer and reloading using the components generated using the Rehosting Workbench (see Generating the components).

Preparation

Configuring the environments and installing the components

Installing the unloading components under z/OS

The components used for the unloading (generated in $HOME/trf/unload/rdbms) should be installed on the source z/OS platform. The generated JCL may need adapting to specific site constraints including JOB cards, library access paths and access paths to input and output files (Data Set Name – DSN).

Installing the reloading components on the target platform

The components used for the reloading (generated in $HOME/trf/reload/rdbms) should be installed on the target platform (runtime).

The following environment variables should be set on the target platform:

Table 4‑4  

Variable	Value
DATA_SOURCE	The name of the directory containing the unloaded DB2 tables transferred from z/OS to be reloaded into Oracle tables.
BIN	The location of the generic reload and control scripts ($HOME/trf/reload/bin)
TMPPROJECT	The temporary directory.
MT_LOG	Directory to contain execution logs.
CTL	Directory containing the <table name>.ctl files used by the SQL*LOADER ($HOME/trf/reload/rdbms/<schema name>/ctl).
DATA_TRANSCODE	Temporary directory used by the DB2 binary data transcoding script (contains temporary files in ASCII format).
NLS_LANG	Set according to the instructions in the Oracle documentation.

The following variable should be set according to the information in the Oracle Tuxedo Application Rehosting Workbench Installation Guide:

•	MT_DB_LOGIN.

The reloading script loadrdbms-<table name>.ksh uses the SQL*LDR Oracle utility. Because this utility can access to ORACLE servers only, this script should be used in ORACLE servers and not with client connection. This variable should not contain an @<oracle_sid> string, especially for this reloading step.

Unloading JCL

To unload each DB2 table, a JCL using the IBM DSNTIAUL utility is executed. The DSNTIAUL utility creates three files for each table:

•	a data file,

•	a log file,

•	a SYSPUNCH file.

These unloading JCLs are named <table name>.jclunload

If the table name is shorter or longer than eight characters, the Rehosting Workbench attributes an eight-character name to the z/OS JCL as close as possible to the original. The renaming process maintains the uniqueness of each table name.

•	Example:ODCSF0X1.jclunload

In the example used in this chapter, the table named ODCSF0 is lengthened to ODCSF0X1 when naming the z/OS JCL.

Transferring the files

The unloaded data files should be transferred between the source z/OS platform and the target UNIX platform in binary format using the file transfer tools available at the site (CFT, FTP, …).

The LOG and SYSPUNCH files should be transferred in text mode.

The files transferred to the target UNIX platform should be stored in the $DATA_SOURCE directory.

Creating the Oracle objects

The scripts creating Oracle objects (tables, index, constraints, …) are created in the $HOME/trf/SQL/rdbms/<schema name> directory. They should be executed in the target Oracle instance.

The <schema name>.lst file contains the names of all of the tables in hierarchical sequence (parent table then child tables).

The following table lists the DB2 objects managed by the Rehosting Workbench and the name of the script used to create them:

Table 4‑5  

Object Type	File name	Notes
TABLE	TABLE- <target_table_name>.sql	One file per Table. The file contains the table construction, with column names, data type and attribute(s). Constraints, except NULL/NOT NULL attributes, are not written in this file
INDEX	INDEX- <target_table_name>.sql	This file contains all the CREATE INDEXes associated with the table <target_table_name>. This file will not be generated if there are no indexes defined on the table <target_table_name> Indexes are: unique or not Unique constraint
CONSTRAINT	CONSTRAINT- <target_table_name>.sql	This file contains all constraints associated with the table <target_table_name>. This file will not be generated if there are no constraints defined on the table <target_table_name> Constraints are: Primary Key, Unique, Check and Foreign key
COMMENT	COMMENT- <target_table_name>.sql	Contains all comments for table and columns. One file per table
VIEW	VIEW-<schema_name>.sql	This file contains all the Views created in the source database/schema. In this release, the Select statements are not automatically converted into the target database language.
SEQUENCE	SEQUENCE-<schema_name>.sql	This file contains all the CREATE SEQUENCEs already created on the source Database.
SYNONYM	SYNONYMS-<schema_name>.sql	This file contains all the CREATE SYNONYMs already created on the source Database.
IDENTITY	IDENTITY- <target_table_name>.sql	In case of IDENTITY when migrating from DB2 to ORACLE, the Rehosting Workbench creates a Sequence and a Trigger object.

Compiling the transcoding programs

The generated COBOL programs used for transcoding are named:

MOD_<table name>.cbl

For the example used in this chapter the generated program is:

•	MOD_ODCSF0.cbl

The programs should be compiled using Microfocus COBOL and the options documented in the Oracle Tuxedo Application Rehosting Workbench Reference Guide.

The programs produce RECORD SEQUENTIAL files on output that will then be read by the SQL*LOADER utility.

Listing 4‑6 FILE CONTROL example – extract from program: MOD_ODCSF0.cbl

SELECT MW-SORTIE

ASSIGN TO "SORTIE"

ORGANIZATION IS RECORD SEQUENTIAL

ACCESS IS SEQUENTIAL

FILE STATUS IS IO-STATUS.

 

Executing the transcoding and reloading scripts

The scripts enabling the transcoding and reloading of data are generated in the directory:

$HOME/trf/reload/rdbms/<schema name>/ksh

The format of the script names is:

loadrdbms-<table name>.ksh

In the example used in this chapter, the script is named:

loadrdbms-ODCSF0.ksh

Each script launches the COBOL program that performs the transcoding and then the SQL*LOADER utility. The CTL files used by SQL*LOADER are named:

<table name>.ctl.

The CTL file used for the example in this chapter is named:

ODCSF0.ctl

Transcoding and reloading command

The transcoding and reloading scripts have the following parameters:

Synopsis

loadrdbms-<table name>.ksh [-t] [-l] [-c: <method>]

Options

-t

Transcode the file.

-l

Reload the data into Oracle table.

-c dsntiaul:

Implement the verification of the transfer (see Checking the transfers).

Examples

For the example provided in Example of a migration of DB2 objects, the generated script is:

•	loadrdbms-ODCSF0.ksh

Checking the transfers

This check uses the following option of the loadrdbms-<table name>.ksh

-c dsntiaul

Note:

This option verifies after the reloading that the reloaded Oracle table contains the same number of records as the equivalent table unloaded from z/OS by the DSNTIAUL utility. If the number of records is different, an error message is produced.

Troubleshooting

This section describes problems resulting from usage errors that have been encountered when migrating data from a source DB2 database to a target Oracle database.

Overview

When executing any of the Rehosting Workbench tools, users should check:

•	If any error messages are displayed on the screen.

•	If the rdbms-converter-<schema name>.log file contains any errors (see Common problems and solutions).

Error messages and associated explanations are listed in the appendix of the Oracle Tuxedo Application Rehosting Workbench Reference Guide.

Common problems and solutions

Error: RDBMS-0105

When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/trf PJ01DB2 STFILEORA the following message appears:

Fatal RDBMS error.

Error: RDBMS-0105: Catalog for /home2/wkb9/param/system.desc is out of date

and needs to be updated externally.

Refine error...

Explanation

Changes have been made to the DDL, re-perform the cataloging operation.

Error: conversion aborted. Can not read

When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/trf SCHEMA the following message appears:

Refine error...

/tmp/refine-exit-status.MOaZwgTphIN14075

ERROR : conversion aborted . Can not read /home2/wkb9/tmp/outputs/SCHEMA/rdbms-converter-SCHEMA.log log file

abort

Explanation

The schema name is not known.

Error: Configuration file /db-param.cfg is missing!

When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/trf PJ01DB2 the following message appears:

*=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=--=-

#########################################################################

CONVERSION OF DDLs and CTL files and GENERATION of directive files

ERROR : Configuration file /db-param.cfg is missing !

ERROR : Error in reading configuration file

Abort

Explanation

The external variable PARAM is not set.

Error: Target output directory... is missing

When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/bad-directory PJ01DB2 the following message appears:

*=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-

Target output directory /home2/wkb9/bad-directory is missing

Check parameters: -i <output_directory> <schema>

ERROR : usage : rdbms.sh [ [-c|-C] [-g] [-m] [-r] [-i <output_directory>] <schema_name> ] -s <output_directory> (<schema>,...) ]

abort

Explanation

The target directory does not exist.