DB2-to-Oracle Convertor

This chapter describes the DB2 objects that are migrated from the source platform (z/OS) and the migration tools that are generated. The conversion is performed in the context of other components translated or generated by the other Oracle Tuxedo Application Rehosting Workbench tools.

The Oracle Tuxedo Application Rehosting Workbench DB2-to-Oracle Convertor uses as a starting-point a coherent set of DB2 objects called a schema, see Oracle Tuxedo Application Rehosting Workbench Schema. Several configuration files need to be set, see Description of the Configuration Files, before launching the conversion process.

The different objects generated are described in Description of the Output Files.

Overview of the DB2-to-Oracle Convertor

Purpose

The purpose of this section is to describe precisely all the features of the Rehosting Workbench DB2-to-Oracle Convertor tools including:

•	Inventory of DB2 objects migrated.

•	Detailed description of converted Oracle objects on the target platform for each DB2 object.

•	Description of the different commands to be used with the DB2-to-Oracle Convertor.

•	Description of the data unloading options on the source platform.

•	Description of the data loading options on the target platform.

Structure

•	Description of the Input Components including Description of the Configuration Files.

•	Description of the Output Files including the Generated Objects.

•	Detailed Processing including the Command-line Syntax.

•	For messages, see DB2-to-Oracle Converter Messages.

See Also

The conversion of data is closely linked to the conversion of COBOL programs, see:

•	COBOL Converter

 

Oracle Tuxedo Application Rehosting Workbench Schema

For the Rehosting Workbench, 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 Description File.

For example:

Listing 4‑1 Schema Name Example

system STDB2ORA root ".."

global-options

catalog="..",

sql-schema=<schema name>.

 

Another possibility is to implement this option for each directory where it is necessary, an option that is useful when several schemas are used.

Example:

directory "BATCH" type Cobol-Batch files "*.cbl" libraries "COPY". %, "INCLUDE" options sql-schema=<"schema name">.

Environment Variables

Before starting the process of migrating data two environment variables should be set:

•	export TMPPROJECT=/$HOME/tmp

Indicates the location to store temporary objects generated by the process.

•	export PARAM=/$HOME/param

Indicates the location where the configuration files required by the process are stored.

Description of the Input Components

File Locations

Location of rdbms.sh

The rdbms.sh tool is located in the directory:

$REFINEDIR/convert-data/

Location of db-param.cfg File

The db-param.cfg configuration file is located in the directory given in the variable:

$PARAM

DB2 DDL Converted

The following DB2 objects are migrated to Oracle:

 

Table 4‑1 DB2 Objects to Convert

Object Type	File name	Remark
TABLE	TABLE- <target_table_name>.sql	One file per Table. The file contains table construction, with column name, 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 indexes associated with the table <target_table_name>. This file will not be generated if there were 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 were 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-<shema_name>.sql	This file contains all Views created in the source database/schema. In this release, the Select statements are not automatically converted into the target database language.
SEQUENCE	SEQUENCE-<shema_name>.sql	For sequence already created on the source database
SYNONYM	SYNONYMS-<shema_name>.sql
IDENTITY	IDENTITY- <target_table_name>.sql	In case of IDENTITY, when migrating from DB2 to ORACLE the Rehosting Workbench creates a Sequence and Trigger objects.

Conversion of DB2 Data Types

The following table shows all DB2 data types and their conversion to the Oracle database target

 

Table 4‑2 DB2 to Oracle Data Type Conversion

DB2 z/OS Data type	Oracle Format	Notes
CHAR CHAR(length)	CHAR	CHAR without length becomes CHAR(1)
VARCHAR(length)	VARCHAR2 (length)
DECIMAL(…)	NUMBER(…)	If no precision, DECIMAL becomes NUMBER(5)
NUMERIC(…)	NUMBER(…)	If no precision, NUMERIC becomes NUMBER(5)
DEC(…)	NUMBER(…)	If no precision, DEC becomes NUMBER(5)
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 Column Property Conversion

The following table shows all DB2 column properties and their conversion to the Oracle database target.

 

Table 4‑3 DB2 Column Property Conversion

DB2 Column Property	Oracle Format	Notes
WITH DEFAULT	DEFAULT <value>	<value> depends on DB2 z/OS data type.
WITH DEFAULT’’ (with nothing between quotes)	CHAR:… DEFAULT ' ' VARCHAR2 … DEFAULT ' '	A zero byte length in DB2 becomes NULL flag on Oracle
WITH DEFAULT '<value>’	DEFAULT '<value>'
NOT NULL	NOT NULL
IDENTITY	Create a Sequence Create a Trigger	Because the IDENTITY attribute does not exist on Oracle, the Rehosting Workbench replaces the attribute with Sequence and Trigger objects.
FOR SBCS …	Attribute ignored

Description of the Configuration Files

This section lists the files and their parameters that can be used to control the migration of a DB2 database to an Oracle database.

POB Files

These files are created during cataloging, for further information see POB Files for ASTs.

DB2 DDL POB File

A POB file is created for each DB2 DDL source file.

For example the SQODCSF0.ddl file contains the source of different DB2 objects. A SQODCSF0.ddl.pob file is created in the $SOURCE/DDL/pobest directory.

Symtab File

symtab-<schema name>.pob

This file is created during cataloging, it must be up-to-date and present so that DB2-to-Oracle Convertor can migrate DB2 objects to Oracle. See The Cataloger Symtab and Other Miscellaneous Files.

sql-system File

sql-system-<project name>.pob

This file is created during cataloging, it must be up-to-date and present so that DB2-to-Oracle Convertor can migrate DB2 objects to Oracle. See The Cataloger Symtab and Other Miscellaneous Files.

sql-system-<project name>-Statements.pob

This file is created during cataloging, it must be up-to-date and present so that DB2-to-Oracle Convertor can migrate DB2 objects to Oracle. See The Cataloger Symtab and Other Miscellaneous Files.

system.desc

In addition to the parameters concerning schema, one other parameter should be set in the System Description File.

DBMS-VERSION="8".

Indicates the version of the RDBMS to migrate.

db-param.cfg

This file should be created in the directory indicated by the $PARAM variable:

$PARAM/db-param.cfg

Listing 4‑2 db-param.cfg Template

#

# 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 (DBMS=, DBMS-VERSION=)

target_rdbms_name:<target_rdbms_name>

target_rdbms_version:<target_rdbms_version>

target_os:<target_os>

#

# specific parameters for RDBMS conversion

rdbms:date_format:<date_format>

rdbms:timestamp_format:<timestamp_format>

rdbms:time_format:<time_format>

rdbms:indexsort:<index_sort_option>

rdbms:indexlang:<index_lang_option>

# rename object files

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

 

Parameters and Syntaxes

 

Table 4‑4 db-param-cfg Parameters

Parameter	Description	Value
General Parameters
<target_rdbms_name>	Name of target RDBMS	oracle
<target_rdbms_version>	Version of target RDBMS	11
<target_os>	Name of target operating system	unix or linux
Parameters concerning the migration of dates, timestamps and times
<date_format>	Date (in the format expected by Oracle)
<time_stamp_format>	Timestamp (in the format expected by Oracle)
<time_format>	Time (in the format expected by Oracle)
Index and Sort parameters
rdbms:indexsort:<index_sort_option>
rdbms:indexlang:<index_lang_option>

Date, Time Parameters

These three 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.

These parameters are optional and only necessary if the DB2 database contains the DATE, TIME or TIMESTAMP fields. These parameters should be supplied respecting the Oracle formats.

If these parameters are supplied, the UNIX/Linux variables:

•	NLS_DATE_FORMAT,

•	NLS_TIMESTAMP_FORMAT,

•	NLS_TIME_FORMAT,

should be set according to the instructions in the ORACLE documentation.

WARNING:

A correct setting of these parameters is essential.

Index, Sort Parameters

These parameters are optional and are only necessary if the sort order in certain columns must be kept and is maintained in applications by use of an index. They are therefore to be implemented only to maintain the ISO functionality of applications between the source and target platforms. For these parameters the values can be:

<index_sort_option> EBCDIC, FRENCH, …

<index_lang_option> FR, UK, …

When these parameters are used, the SQL CREATE INDEX scripts are generated as shown in the example below where the value of the index parameter is French:

Listing 4‑3 SQL CREATE INDEX Script Using Index Parameter

WHENEVER SQLERROR CONTINUE;

DROP INDEX MYDB.TAB1_IDX;

WHENEVER SQLERROR EXIT 3;

CREATE INDEX MYDB.TAB1_IDX ON MYDB.TAB1

(

nlssort(MYCOL11, 'nls_sort=FRENCH') ASC

);

 

The variables:

•

NLS_SORT,

•

NLS_COMP,

•

NLS_LANG

should be harmonized with the values implemented for these two parameters. See the Oracle documentation: Oracle Database Globalization Support Guide.

By default, the tools generated by the DB2 to Oracle migration expect NLS_SORT in binary.

WARNING:

A correct setting of these parameters is essential.

File 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.

Renaming File

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 variable.

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>

Note:

To apply the modifications to all schema names and/or all table names, place a * in the position of <schema name> and/or <DB2 table name>. See the second example below.

Comments can be added in the form of: % Text.

Listing 4‑4 Renaming File Example

% Modification applied to the AUALPH0T table

column;AUANPR0U;AUALPH0T;NUM_ALPHA;MW_NUM_ALPHA

column;AUANPR0U;*;ASC;ASC-1

 

rdbms-template.txt

This file is placed during the installation of the Rehosting Workbench, it contains the templates that perform the generation of the different migration tools. The file is located in:

$REFINEDIR/convert-data/default/rdbms/rdbms-templates.txt

Listing 4‑5 rdbms-template.txt

% Unloading all TABLE *******************

#VAR:TEMPLATES#/unloading/jcl-unload-DB2-table-SQL.pgm

%

% Loading TABLE *************************

%

#VAR:TEMPLATES#/loading/convert-ebcdic-for-oracle.pgm

#VAR:TEMPLATES#/loading/reload-tables-ksh.pgm

#VAR:TEMPLATES#/loading/rdbms-reload-tables-txt.pgm

%

% included file to include into modified-components

#VAR:TEMPLATES#/include-modified-components.pgm

%

% ***************************************

% MANDATORY: used when using -r argument

#VAR:TEMPLATES#/remove-schema-name-ksh.pgm

% MANDATORY: used when using -i argument

#VAR:DEFAULT#/rdbms-move-assignation.pgm

%

 

When required, another version of the rdbms-template.txt file can be placed in the $PARAM/rdbms directory. The use of an alternative file is signaled during the execution of rdbms.sh by the message:

Listing 4‑6 Execution Log with Alternative Template File

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

Control of templates

OK: Use Templates list file from current project:

File name is /home2/wkb9/param/rdbms/rdbms-templates.txt

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

 

rdbms_move_assignation.txt

This file is placed during the installation of the Rehosting Workbench, it controls the transfer of components generated in the different installation directories. This file indicates the location of each component to copy during the installation phase of rdbms.sh, when launched using rdbms.sh -i.

The file is located in:

$REFINEDIR/convert-data/default/rdbms/rdbms-move-assignation.pgm

This file can be modified following the instructions found at the beginning of the file:

Listing 4‑7 rdbms_move_assignation.txt Modification Instructions

[…]

*@ (c) Metaware:file-move-assignation.pgm. $Revision: 1.2 $

*release_format=2.3

*

* format is:

* <typ>:<source_directory>:<file_name>:<target_directory>

*

* typ:

* O: optional copy: if the <file_name> is missing, it is ignored

* M: Mandatory copy: abort if <file_name> is missing.

* E: Execution: execute the mandatory script <file_name>.

* Parameters for script to be executed are:

* basedir: directory of REFINEDIR/convert-data

* targetoutputdir: value of "-i <targetdir>"

* schema: schema name

* target_dir: value written as 4th parameter in this file.

* d: use this tag to display the word which follows

*

* source_directory:

* T: generated components written in <targetdir>/Templates/<schema>

* O: components written in <targetdir>/outputs/<schema>

* S: SQL requests (DDL) generated into <targetdir>/SQL/<schema> directory

* F: fixed components present in REFINEDIR

* s: used with -s arguments: indicates the target directory for DML utilities

* (in REFINEDIR/modified-components/) which manipulate all schemas.

*

* file_name: (except for typ:d)

* name of the file in <source_directory>

*

* target_directory: (except for typ:d, given at 4th argument for typ:E)

* name of the target directory

* if the 1st character is "/", component is copied using static directory and not in <td> directory

*

[…]

 

Description of the Output Files

File Locations

Location of Temporary Files

The temporary objects generated by the Rehosting Workbench DB2-to-Oracle Convertor are stored in:

$TMPPROJECT

$TMPPROJECT/Template/<schema name>

$TMPPROJECT/outputs/<schema name>

Locations of Log Files

The execution log files are stored in:

•	Log generated by the option -c or -C:

$TMPPROJECT/outputs/<schema name> rdbms-converter-<schema name>.log

•	Log generated by the option -g:

$TMPPROJECT/outputs mapper-log-<schema name>

The $TMPPROJECT variable is set in $HOME/tmp.

Location of Generated Files

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

 

Table 4‑5 Component Locations

Location	Contents
$HOME/trf/unload/rdbms/<schema name>	The JCL used for each unloading table are generated for each <schema name>. These JCL are named: <table name_8>.jclunload
$HOME/trf/SQL/rdbms/<schema name>	Location by <schema name> of the SQL scripts used to create the Oracle objects. The names of these scripts are described in Table 4‑1.
$HOME/trf/reload/rdbms/<schema name>/src	Location by <schema name> of the COBOL transcoding programs. These programs are named: MOD_<target table name>.cbl
$HOME/trf/reload/rdbms/<schema name>/ctl	Location by <schema name> of the CTL files used by SQL*LOADER. These files are named: <target table name>.ctl
$HOME/trf/reload/rdbms/<schema name>/ksh	Location by <schema name> of the reloading Korn shell scripts. These scripts are named: loadrdbms-<target table name>.ksh

Notes:

<table name_8> If the table name is shorter or longer than eight characters, the Rehosting Workbench DB2-to-Oracle Convertor 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.

<target table name> Is the table name in the Oracle database.

Generated Objects

The following sections describe the objects generated during the migration from a DB2 to Oracle database and the directories in which they are placed.

Temporary Files

These files are automatically generated during the first step of the Rehosting Workbench DB2-to-Oracle Convertor for use in the second step.

Datamap File

This is a configuration file generated and used by the Rehosting Workbench containing the list of tables to be migrated. If a table name is missing in the Datamap file, but present in the mapper file, it will be ignored during the execution of the Rehosting Workbench.

This file is generated using the -c or -C options of the rdbms.sh command.

File Name

The Datamap file is created with this complete name:

<-target-directory parameter>/outputs/<schema name>/Datamap-<schema name>.re

Parameters and syntaxes used:

 

Table 4‑6 Datamap File Name Parameters

Parameter	Value
<-target-directory parameter>	Value of the parameter -target-directory: $HOME/tmp
<schema name>	Name of the current schema.

Syntax and Parameters

Listing 4‑8 Datamap File

%% Rdbms-converter. Rev: <revision>. <compilationDate>. <BeginGeneratedDate>

%% tables generation only

data map <schema name>-map system cat::<PROJECT_NAME>

file <schema name>.<source_table_name>

organization sequential

 

Parameter	Value
%%	Comment ignored by the Rehosting Workbench.
<revision>	Oracle Tuxedo Application Rehosting Workbench revision number.
<compilation_date>	Oracle Tuxedo Application Rehosting Workbench compilation date.
<BeginGeneratedDate>	Date and time of the execution.
<schema name>	Name of the current schema
<PROJECT_NAME>	Project name as described into System Description File.
<source_table_name>	Table name on the source database.

Mapper File

This is a configuration file used by the Rehosting Workbench DB2-to-Oracle Convertor in the second step.

It contains all information about tables and re-engineering processes like object renaming.

The file is generated by rdbms.sh using the -c or -C options.

File Name

The Mapper file is created with this complete name:

$TMPPROJECT/outputs/<schema>/mapper-<schema name>.re

 

Table 4‑7 Mapper file Name Parameters

Parameter	Value
<schema name>	Name of the current schema.

Generation Sample

For the following DB2 DDL:

CREATE TABLE ART1 (

REF_ART CHAR(19) NOT NULL,

REF_ART_REMPL VARCHAR(400),

REGR_PEREMT CHAR(1),

DUREE_VALID DATE NOT NULL,

LONG_ART INTEGER

) […]

 

The Mapper file generated by DB2-to-Oracle Convertor is:

Listing 4‑9 Mapper File Example

%% Rdbms-converter. Rev: 1.4. 20090101 13:25. 20091010 11:25

%% tables generation only

ufas mapper GM

file GM.ART1 transferred converted

table name GM-ART1

map record TAB-ART1 defined in "#VAR:RECS_SOURCE#/GM/ART1.cpy"

source record TAB-ART1 in "#VAR:RECS_SOURCE#/GM/ART1.cpy"

logical name ART1

include "#VAR:RECS_SOURCE#/GM/ART1.cpy"

strategies

field REF-ART attributes NULL_DISALLOWED DATATYPE_CHAR

,field REF-ART-REMP attributes NULL_ALLOWED DATATYPE_VARCHAR

,field REF-ART-REMP-LEN attributes TECHNICAL_FIELD_VARCHAR

,field REF-ART-REMP-IND attributes TECHNICAL_FIELD_NULL

,field REGR-PEREMT attributes NULL_ALLOWED DATATYPE_CHAR

,field REGR-PEREMT-IND attributes TECHNICAL_FIELD_NULL

,field DUREE-VALID attributes NULL_DISALLOWED DATATYPE_DATE

,field LONG-ART attributes NULL_DISALLOWED DATATYPE_NUMERIC

 

In this example #VAR:RECS:SOURCE# indicates $TMPPROJECT/outputs/schema.

Syntax and Parameters

Listing 4‑10 Mapper File

%% Rdbms-converter. Rev: <revision>. <compilationDate>. <BeginGeneratedDate>

%% tables generation only

ufas mapper <schema name>

## For each table …

file <schema name>.<source_table_name> transferred converted

table name <target_table_name>

include "#VAR:RECS_SOURCE#/<schema>/<source_table_name>.cpy"

map record TAB-<source_table_name>

defined in "#VAR:RECS_SOURCE#/<schema>/<source_table_name>.cpy"

source record TAB-<source_table_name>.

in "#VAR:RECS_SOURCE#/<schema>/<source_table_name>..cpy"

logical name <table_name_8>

## For each column of the table

[ |,]field <target_column_name>

attributes [NULL_ALLOWED|NULL_DISALLOWED]

[DATATYPE_VARCHAR|DATATYPE_CHAR|

DATATYPE_NUMERIC|

DATATYPE_DATE|DATATYPE_TIME|DATATYPE_TIMESTAMP

]

## optional: for each technical field written in the copy file

[,field <target_column_name>-IND attributes TECHNICAL_FIELD_NULL ]

[,field <target_column_name>-LEN attributes TECHNICAL_FIELD_VARCHAR ]

 

Each file directive is used to describe a table, each field directive describes a column of the table (the field directives <target_column_name>-IND and field <target_column_name>-LEN are optionally used to modify certain options in the technical fields added to the unloaded file by DSNTIAUL).

Table 4‑8 lists the field directives.

 

Table 4‑8 Field Directive Parameters

Parameter	Value
%%	Comment ignored by the Rehosting Workbench.
<revision>	Oracle Tuxedo Application Rehosting Workbench revision number.
<compilation_date>	Oracle Tuxedo Application Rehosting Workbench compilation date.
<BeginGeneratedDate>	Date and time of the execution.
<schema name>	Name of the current schema
<source_table_name>	Table name on source database
<table_name_8>	Logical name of the table limited to 8 characters. Oracle Tuxedo Application Rehosting Workbench creates a unique name only within a schema.
<target_table_name>	Table name on target platform.
<source_column_name>	Column name from source database.
<target_column_name>	Column name on target database.

Links to COBOL Copy

As seen in the file clause of the example, the Mapper file is linked to a COBOL copy file. This COBOL copy describes the unloaded data file: it contains columns descriptions and also technical fields. This unloaded file is created by DSNTIAUL utility. It contains column data and null indicator values

For each column, a field name and two values for the attributes are generated:

•	The NULL information:

•	NULL_ALLOWED

•	NULL_DISALLOWED

Used when the column accepts the NULL flag or has the NOT NULL attribute respectively.

•	Datatype information. It indicates the column data type

For each technical field, DB2-to-Oracle Convertor generates:

•	For a NULL field (it means the column has NULL attribute), the COBOL copy contains an additional field. The name of this field is the target column name with an -IND suffix.

•	For a VARCHAR field, the COBOL copy contains an additional field placed before the data field. The name of this field is the target column name with -LEN suffix.

COBOL Description

Oracle Tuxedo Application Rehosting Workbench DB2-to-Oracle Convertor needs a description associated with each table, so a first step generates a COBOL copy description.

This copy contains:

•	Field descriptions, one field per column.

•	Technical fields:

•	For a VARCHAR column, a length field is added.

•	For a column without a NOT NULL attribute, an indicator field.

This description is equal to the downloaded file.

Copy File Name

The copy file is created with this complete name:

<-target-directory parameter>/outputs/<schema name>/<source_table_name>.cpy

 

Table 4‑9 Copy File Name Parameters

Parameter	Value
<-target-directory parameter>	Value of the parameter -target-directory
<schema name>	Name of the current schema.
<source_table_name>	Name of the source table name

Listing 4‑11 Copy File: SQL Code

CREATE TABLE ART1 (

REF_ART CHAR(19) NOT NULL,

REF_ART_REMPL VARCHAR(400),

REGR_PEREMT CHAR(1),

DUREE_VALID DATE NOT NULL,

LONG_ART INTEGER

) […]

 

For this example of DB2 DDL, the copy file generated by the Rehosting Workbench is:

Listing 4‑12 Copy File: Generation Sample

* Rdbms-converter. Revision: Rev: 1.4. 20090101 13:25. 20091010 11:25

01 TAB-ART1.

03 REF-ART PIC X(19).

03 REF-ART-REMPL-LEN PIC S9(4) COMP-5.

03 REF-ART-REMPL PIC X(400).

03 REF-ART-REMPL-IND PIC X.

03 REGR-PEREMT PIC X(1).

03 REGR-PEREMT-IND PIC X(1).

03 DUREE-VALID PIC X(10).

03 LONG-ART PIC S9(9) COMP-5.

03 LONG-ART-IND PIC X(1).

Copy File Syntax and Parameters

The generated copy files have the following format:

Listing 4‑13 Copy File

* Rdbms-converter. Revision: <revision> <compilationDate> <BeginGeneratedDate>

01 TAB-<source_table_name>.

03 <target_column_name>-LEN PIC S9(4) COMP-5.

03 <target_column_name> PIC <field_Cobol_format>.

## Is the column has NULL attribute

03 <target_column_name>-IND PIC X.

 

Table 4‑10 Copy File Parameters

Parameter	Description
<revision>	Oracle Tuxedo Application Rehosting Workbench revision number.
<compilationDate>	Oracle Tuxedo Application Rehosting Workbench compilation date and time.
<BeginGeneratedDate>	Date and time at the beginning of the process.
<source_table_name>	Table name on source database
<target_column_name>	Column name on target database
<target_column_name>-LEN	If the column has VARCHAR datatype
<target_column_name>-IND	If the column has NULL attribute
<field_Cobol_format>	Picture of the field in COBOL.

Unloading JCL

The JCL used to unload the DB2 tables are generated using the -g option of the rdbms.sh command. They are then (using the -i option) installed in:

$HOME/trf/unload/rdbms/<schema name>

Each JCL contains two steps and unloads one DB2 table using the z/OS DSNTIAUL utility. The JCL return code is equal to 0 or 4 for a normal termination.

 

Step 1	DEL	IDCAMS DELETE files (deletion of log, data, syspunch files)
Step 2	UNLOAD	DSNTIAUL of the indicated table

The 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 within a schema.

Note:

The .jclunload extension should be deleted for execution under z/OS.

The generated JCL may need adapting to specific site constraints including:

•	JOB cards: <crdjob>,

•	library access paths: <db2_runlib_load_librairy>,

•	access paths to input and output files: <data>.

Listing 4‑14 Unload JCL Example

//<crdjob> <cardjob_parameter_1>,'DB2 ODCSF0X1',

// <cardjob_parameter_2>

// <cardjob_parameter_3>

// <cardjob_parameter_4>

//*@ (C) Metaware:jcl-unload-DB2-table-SQL.pgm. $Revision: 1.7.2.1 $

//********************************************************

//* UNLOAD THE RDBMS TABLE:

//* PJ01DB2.ODCSF0

//* INTO <data>.PJ01DB2.ODCSF0X1.DATA

//********************************************************

//*------------------------------------------*

//* DELETE LOG, DATA AND SYSPUNCH FILES

//*------------------------------------------*

//DEL EXEC PGM=IDCAMS

//SYSPRINT DD SYSOUT=*

//SYSOUT DD SYSOUT=*

//SYSIN DD *

DELETE <data>.PJ01DB2.ODCSF0X1.LOG

DELETE <data>.PJ01DB2.ODCSF0X1.DATA

DELETE <data>.PJ01DB2.ODCSF0X1.SYSPUNCH

SET MAXCC=0

//*------------------------------------------*

//* LAUNCH DSNTIAUL UTILITY

//*------------------------------------------*

//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20

//SYSTSPRT DD SYSOUT=*

//SYSTSIN DD *

DSN SYS(DSNM)

RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -

LIBRARY ('<db2_runlib_load_librairy>')

END

//SYSPRINT DD SPACE=(CYL,(150,50),RLSE),

// DISP=(,CATLG),

// UNIT=SYSDA,

// DSN=<data>.PJ01DB2.ODCSF0X1.LOG

//SYSUDUMP DD SYSOUT=*

//*

//SYSREC00 DD SPACE=(CYL,(150,50),RLSE),

// DISP=(,CATLG),

// UNIT=SYSDA,

// DSN=<data>.PJ01DB2.ODCSF0X1.DATA

//SYSPUNCH DD SPACE=(TRK,(15,15),RLSE),

// DISP=(NEW,CATLG),DCB=(LRECL=80,RECFM=FB),

// UNIT=SYSDA,

// DSN=<data>.PJ01DB2.ODCSF0X1.SYSPUNCH

//SYSIN DD *

SELECT

CUSTIDENT

,CUSTLNAME

,CUSTFNAME

,CUSTADDRS

,CUSTCITY

,CUSTSTATE

,CUSTBDATE

,CUSTEMAIL

,CUSTPHONE

FROM PJ01DB2.ODCSF0;

/*

 

COBOL Transcoding Programs

The COBOL transcoding programs are generated using the -g option of the rdbms.sh command. They are then (using the -i option) installed in:

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

The programs are named: MOD_<table name>.cbl

The programs should be compiled using the Microfocus COBOL or COBOL-ITcompilation options documented in Compiler Options.

The compilation of these programs requires the presence of a CONVERTMW.cpy copy file adapted to the project.

These files read a file on input and write a sequential file on output with fixed length records. The output file will be read by the SQL*LOADER utility.

Listing 4‑15 FILE CONTROL Section - for Transcoding Programs

 

SELECT MW-ENTREE

ASSIGN TO "ENTREE"

ORGANIZATION IS SEQUENTIAL

ACCESS IS SEQUENTIAL

FILE STATUS IS IO-STATUS.

 

SELECT MW-SORTIE

ASSIGN TO "SORTIE"

ORGANIZATION IS RECORD SEQUENTIAL

ACCESS IS SEQUENTIAL

FILE STATUS IS IO-STATUS.

 

A record count is written to the output file and is displayed at the end of processing via:

DISPLAY "CONVERTING TERMINATED OK".

DISPLAY "Nb rows reloaded: " D-NB-RECS.

DISPLAY " ".

The validity of the numeric fields is tested to prevent any formatting problems of the z/OS produced file (discrepancy between the DB2 DDL and the unloaded table). If a field expected to be numeric is not, an ABORT is triggered.

The same checks are made on the technical fields, (NULL indicator fields).

Reloading Korn Shell Scripts

The Reloading Korn shell scripts are generated using the -g option of the rdbms.sh command. They are then (using the -i option) installed in:

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

The scripts are named: loadrdbms-<table name>.ksh

They contain three phases:

•	t – transcodephase

•	l – loadingphase

•	c – checkphase

The execution of the scripts produces an execution log in $MT_LOG/<nom de table>.log

The following variables are set at the beginning of each script:

Listing 4‑16 Reloading Script Variables

f="@ (c) Metaware:reload-tables-ksh.pgm. $Revision: 1.14 $"

export DD_ENTREE=${DD_ENTREE:-${DATA_SOURCE}/PJ01DB2.ODCSF0X1.DATA}

export DD_SORTIE=${DD_SORTIE:-${DATA_TRANSCODE}/ODCSF0.ascii}

export DD_MVSLOG=${DD_MVSLOG:-${DATA_SOURCE}/PJ01DB2.ODCSF0X1.LOG}

table_name="ODCSF0"

logtab=$MT_LOG/ODCSF0.log

reportfile=${DATA_TRANSCODE}/${table_name}.rpt

[…]

 

To change the file names, set the DD_* variables before calling the script.

Various messages may be generated during the three execution phases of the scripts, these messages are listed in Oracle Tuxedo Application Rehosting Workbench Messages.

On normal end, a return code of 0 is returned.

Transcoding Phase

This step launches the execution of the COBOL transcoding program associated with the Oracle table processed:

cobrun MOD_ODCSF0 >>$logtab 2>&1

On normal termination the following message is displayed:

echo "file ${DD_SORTIE} transcoded"

Loading Phase

This step loads the Oracle table using the SQL*LOADER utility:

${BIN}/RunSqlLoader.sh $CTL/ODCSF0.ctl $opt >>$logtab 2>&1

On normal termination the following message is displayed:

echo "Table ${table_name} successfully loaded."

Check Phase

This step 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:

if [ "$qteFile" -ne "$qteTranscode" ]

If the number of records is equal, this message is produced:

echo "Number of rows written in output file is equal to number written in the report file: OK"

Note:

To execute this step, it is necessary to transfer the DSNTIAUL log file to the target environment.

Target DDL

The ORACLE DDL is generated using the -c or -C option of the rdbms.sh command. They are then (using the -i option) installed in:

$HOME/trf/SQL/rdbms/<schema name>

The script naming rules are described in Table 4‑1.

TABLE and COLUMNS

Listing 4‑17 Oracle Table and Column Generation

WHENEVER SQLERROR CONTINUE;

DROP TABLE <schema>.<target_table_name> CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE <schema>.<target_table_name>

(

<target_column_name> <column_data_type> <attribute(s)…>[,]

);

 

INDEX

Listing 4‑18 Oracle Index Generation

WHENEVER SQLERROR CONTINUE;

DROP INDEX <schema>.<target_index_name>;

WHENEVER SQLERROR EXIT 3;

CREATE [UNIQUE] INDEX <schema>.<target_index_name> ON <schema>.<target_table_name>

(

[<nls_function>(]<target_column_name> [, '<nls_attributes>')] [ASC|DESC][,]

);

 

The parameters <nls_function> and <nls_attributes> are optional. If the db-param parameters rdbms:indexsort and rdbms:indexlang are set in the db-param .cfg file, then the Rehosting Workbench generates these options in the CREATE INDEX command.

Listing 4‑19 Oracle Index Generation Without rdbms:indexsort and rdbms:indexlang Parameters

WHENEVER SQLERROR CONTINUE;

DROP INDEX MYDB.TAB2_IDX;

WHENEVER SQLERROR EXIT 3;

CREATE INDEX MYDB.TAB2_IDX ON MYDB.TAB2

(

MYCOL1 ASC,

MYCOL2 DESC

);

 

The following samples show an index creation with rdbms:indexsort=french parameter.

Listing 4‑20 Oracle Index Generation With rdbms:indexsort=french Parameter

WHENEVER SQLERROR CONTINUE;

DROP INDEX MYDB.TAB1_IDX;

WHENEVER SQLERROR EXIT 3;

CREATE INDEX MYDB.TAB1_IDX ON MYDB.TAB1

(

nlssort(MYCOL11, 'nls_sort=FRENCH') ASC

);

 

CONSTRAINT

Listing 4‑21 Oracle Constraint Generation

WHENEVER SQLERROR CONTINUE;

ALTER TABLE <schema name>.<target_table_name> DROP CONSTRAINT <target_constraint_name>;

WHENEVER SQLERROR EXIT 3;

ALTER TABLE <schema name>.<target_table_name> ADD CONSTRAINT <target_constraint_name>

< the syntax according to the constraint creation is inserted here>

;

 

COMMENT

Listing 4‑22 Oracle Comment Generation

CREATE COMMENT ON TABLE <schema name>.<target_table_name>

IS '<comment_table>'

;

CREATE COMMENT ON COLUMN<schema name> .<target_table_name>.<target_column_name>

IS '<comment_column>'

;

 

Note:

The tool accepts only COMMENT on TABLE and COLUMN.

VIEW

Listing 4‑23 Oracle View Generation

WHENEVER SQLERROR CONTINUE;

DROP VIEW <target_view_name>;

WHENEVER SQLERROR EXIT 3;

CREATE VIEW <target_view_name>

AS <the syntax according to the existing view is inserted here>

;

 

In this version the DB2 SELECT statement must be adapted to the Oracle SQL syntax.

SEQUENCE

For a sequence already present in the DB2 database.

Listing 4‑24 Oracle Sequence Generation

WHENEVER SQLERROR CONTINUE;

DROP SEQUENCE <schema name>.<target_sequence_name>;

WHENEVER SQLERROR EXIT 3;

CREATE SEQUENCE <schema name> .<target_sequence_name>

<the syntax according to the existent sequence is inserted here>

;

 

SYNONYM

For the synonym of a table, or a synonym of a view:

Listing 4‑25 Oracle Synonym Generation

WHENEVER SQLERROR CONTINUE;

DROP SYNONYM <schema name>.<target_synonym_name>;

WHENEVER SQLERROR EXIT 3;

CREATE SYNONYM <schema name>.<target_synonym_name>

FOR <schema name>.[<target_table_name> | <target_synonym_name>| <target_view_name>]

;

 

Identity Engineering

The DB2 column identity is replaced by two Oracle objects:

•	Sequence associated with table <target_table_name> .

•	Trigger associated with table <target_table_name>.

Listing 4‑26 Oracle Sequence and Trigger Generation

-- Sequence associated with table <target_table_name>

-- for identity column <target_column_name>

WHENEVER SQLERROR CONTINUE;

DROP SEQUENCE <schema name>.<table_name_26>_SEQ;

WHENEVER SQLERROR EXIT 3;

CREATE SEQUENCE <schema name>.<table_name_26>_SEQ

START WITH 1 INCREMENT BY 1

;

-- Trigger associated with table <target_table_name> for

-- identity column <target_column_name>

 

CREATE OR REPLACE TRIGGER <schema name>.<table_name_26>_IDY

BEFORE INSERT ON <schema name>.<table_name>

REFERENCING NEW AS NEW FOR EACH ROW

BEGIN

SELECT <schema name>.<target_table_name>_SEQ.nextval INTO :NEW.<target_column_name>

FROM dual;

END;

/

 

After reloading the ORACLE table, the script must be modified to adapt the CREATE SEQUENCE with the MAX value of the column concerned.

Ordered List of Tables File

This file is generated using the -c or -C option of the rdbms.sh command. It is then (using the -i option) installed in:

$Home/trf/SQL/rdbms/<schema name>

It is named <schema name>.lst.

This file contains the names of all of the tables in hierarchical sequence (parent table then child tables).

COBOL Conversion Guide File

This file is generated using the -s option of the rdbms.sh command.

This file is used by the Rehosting Workbench DB2-to-Oracle Convertor to rename object names and to modify options in SQL functions. These objects and functions are stored in SQL applicative requests, inside EXEC SQL and END-EXEC verbs.

File Name

The language conversion file is created with this complete name:

$PARAM/dynamic-config/rdbms-conv-<schema name>.xml

$PARAM/dynamic-config/rdbms-conv.txt

Where:

<schema name>

Name of the current schema.

Generated Sample

Listing 4‑27 Sample COBOL Conversion Guide

<?xml version="1.0" encoding="ISO-8859-1" ?>

<!-- DOCTYPE RDBMS CONVERSION "metaware_rdbms.dtd" -->

<rdbms revision="1.4" compilationdate="20090101 10:00"

Generateddate="20090608 08:01">

<comment>

</comment>

<sourceformat>

<date>YYYYMMDD</date>

</sourceformat>

<tablelist>

<table source_name="DESC">

<target_name>DESC1</target_name>

</table>

<table source_name="USER">

<target_name>USER1</target_name>

</table>

<table source_name="MYTB">

<column source_name="USER">

<target_name>USER1</target_name>

</column>

</table>

</tablelist>

 

SQL*LOADER Control Files

This file is generated using the -i option of the rdbms.sh command in:

$HOME/trf/reload/rdbms/<schema name>/ctl/<target_table_name>.ctl

These files are generated for the Sql*Loader Oracle utility. They contain the description of the data file created by the transcoding programs. Data files are read by Sql*Loader and fully loaded into Oracle Tables.

File Name

$HOME/trf/reload/rdbms/<schema name>/ctl/<target_table_name>.ctl

Where

<schema name>

Name of the current schema.

<target_table_name>

Name of the target table name.

Generated Sample

The following sample is the content of a CTL file (Control file) used by Sql*Loader.

It shows all the Sql*Loader data types and the file structures managed by this Oracle Loader utility:

Listing 4‑28 CTL File Example

LOAD DATA

INFILE 'PHAM.ascii' "fix 512"

BADFILE 'PHAM.bad'

APPEND

PRESERVE BLANKS

INTO TABLE PH0.PHAM

REENABLE

(

MOUV_REF POSITION(1:19) CHAR(19),

C01 POSITION(21:30) CHAR(10) NULLIF (31:31) = "N",

DATE_FAB POSITION(32:41) DATE(10) "DD.MM.YYYY",

M_COEFF_TR POSITION(43:60) DECIMAL EXTERNAL,

PHDESC POSITION(62:483) VARCHAR(420)

DATMOD POSITION(485:510) TIMESTAMP "YYYY.MM.DD.HH24.MI.SS.FF6" ,

)

 

DATE and TIMESTAMP formats are replaced by values described in the db-param.cfg file.

DDL Translator Log File

The DDL translator outputs a log file for each schema that it translates. This file contains information about the translation process and describes the major translation actions that were preformed. It also contains any error or translation issue messages.

Below is an annotated example log file.

The header shows translator version information and starting time.

Listing 4‑29 DDL Translator Log File — header

Rdbms-converter. Rev: 0.0.0.beta. <revision date/time> <build date/time> .

BeginTime: <starting date/time>

 

The RDBMS Parameters section lists the input parameters for the translation run.

Listing 4‑30 DDL Translator Log File — Parameters

==========================

RDBMS Parameters

==========================

System description file (-system-description): .../samples/param/system.desc

Schemas (-ddl): TEST,OTHER

Schema names file (-ddls-file): None

Target OS (-target-os): UNIX

Target RDBMS (-target-rdbms): ORACLE

Target RDBMS version (-target-rdbms-version): 11g

Target directory (-target-directory): .../samples/latest/

Rename objects file (-rename-objects): .../samples/param/rename-objects.conf

REBMS parameters file (-rdbms-parameters): .../samples/param/rdbms-parameters.conf

Recatalog allowed? (-with-ddl-changes): true

 

The Database Parameters section lists important properties of the source database.

Listing 4‑31 DDL Translator Log File — Database Parameters

=========================

Database Parameters

=========================

DATE_FORMAT: DD.MM.YYYY

TIME_FORMAT: HH24.MI.SSXFF

TIMESTAMP_FORMAT: YYYY.MM.DD.HH24.MI.SS.FF6

INDEXSORT: FRENCH

 

The Schema Translation section is a transcript of the translation process.

Listing 4‑32 DDL Translator Log File — Schema Translation

=========================

Schema Translation

=========================

----------------------------------------------------------------------

Beginning translation

Mode: MVS DB2 8 to UNIX ORACLE 11g

Schemas to translate: TEST, OTHER

Schemas to skip:

Schemas to not translate:

----------------------------------------

Translating schema TEST (1/2) ..

setting up pre-translation .. 3 issues

inspecting the original schema .. 0 issues

preparing the schema for translation .. 0 issues

canonicalizing the original schema .. 0 issues

analyzing the original schema .. 0 issues

Alter-Table-Stmt: 1

Base-Table-Def: 2

Comment-On-Def: 3

Index-Def: 1

Sequence-Def: 1

Synonym-Def: 1

View-Def: 2

translating the schema .. 1 issue

outputting the translated schema files .. 0 issues

outputting the language conversion file .. 0 issues

outputting the data mapper file .. 0 issues

outputting the mapper file .. 0 issues

outputting the COBOL copy book files .. 0 issues

outputting the data loader files .. 0 issues

inspecting the final translation .. 0 issues

The DDL Analysis section describes the major translation actions that were performed to change the input schema into the output schema.

Listing 4‑33 DDL Translator Log File — DDL Analysis

============================

DDL Analysis

============================

table TABLE2

copy name is TABLE2.cpy

logical name is TABLE2X2

table TABLE1

copy name is TABLE1.cpy

logical name is TABLE1X1

table TABLE1 has a new target name NEW_TABLE1

column COL_CHAR, child of table TABLE1, has a new target name NEW_COL_CHAR

 

The Output Files section lists all files that where created during the translation of the schema.

Listing 4‑34 DDL Translator Log File — Output Files

============================

Output Files

============================

Target directory: ".../samples/latest/"

Output files:

LOG file:

outputs/TEST/rdbms-converter-TEST.log

DDL files:

SQL/TEST/COMMENT-NEW_TABLE1.sql

SQL/TEST/CONSTRAINT-NEW_TABLE1.sql

SQL/TEST/IDENTITY-NEW_TABLE1.sql

SQL/TEST/INDEX-NEW_TABLE1.sql

SQL/TEST/SEQUENCE-SEQUENCE1.sql

SQL/TEST/SYNONYM-TAB1.sql

SQL/TEST/TABLE-NEW_TABLE1.sql

SQL/TEST/TABLE-TABLE2.sql

SQL/TEST/TEST.lst

SQL/TEST/VIEW-VIEW1.sql

SQL/TEST/VIEW-VIEW2.sql

COPYBOOK files:

outputs/TEST/TABLE1.cpy

outputs/TEST/TABLE2.cpy

LANGUAGE-CONVERSION file:

outputs/TEST/rdbms-conv-TEST.xml

DATA-MAP file:

outputs/TEST/Datamap-TEST.re

MAPPER file:

outputs/TEST/mapper-TEST.re

SQL*LOADER files:

outputs/TEST/NEW_TABLE1.ctl

outputs/TEST/TABLE2.ctl

 

The Translation Issues section summarizes any translation issues that were detected during the translation.

Listing 4‑35 DDL Translator Log File — Translation Issues

=========================

Translation Issues

=========================

Showing 3 translation issues.

Setup 2 issues

Next-Schema 0 issues

Inspect-Original 0 issues

Setup-Schema-Root 0 issues

Canonicalize-Original 0 issues

Analyze-Original 0 issues

Translate-Ddl 0 issues

Inspect-Translated 0 issues

Output-Ddl 0 issues

Output-Lang-Conv 0 issues

Output-Data-Map 0 issues

Output-Mapper 0 issues

Output-Copy-Books 0 issues

Output-Data-Loaders 0 issues

Inspect-Final 0 issues

Shutdown 0 issues

Total 2 issues

==================================================

Translation Phase: :SETUP (2 issues).

--------------------------------------------------

Original File: unknown

Original Line: unknown

Phase: Setup

RDBMS-0024: Incomplete source information for the system (OS: MVS, DB: None, Version: "None"). Defaulting to (OS: MVS, DB: DB2, Version: "8").

--------------------------------------------------

Original File: unknown

Original Line: unknown

Phase: Setup

RDBMS-0083: Rename pattern "COLUMN; X; TABLE1; COL_CHAR; NEW_COL_CHAR" is pre-empted by an earlier pattern and will never be applied.

--------------------------------------------------

 

The trailer shows the ending date and time. It also gives a success/failure indication.

Listing 4‑36 DDL Translator Log File — Trailer

=============================================================

EndTime: <ending date/time>

Status: Failure

 

Execution Reports

rdbms.sh creates different execution reports depending on the options chosen. In the following examples the following command is used:

rdbms.sh -Cgrmi $HOME/trf PJ01DB2

Listing 4‑37 Messages Produced When Using the Options -c or -C With rdbms.sh

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

CONVERSION OF DDLs and CTL files and GENERATION of directive files

CMD : /Qarefine/release/M2_L3_4/scripts/launch rdbms-converter -s /home2/wkb9/param/system.desc -td /home2/wkb9/tmp -rdbms-parameters /home2/wkb9/tmp/config-rdbms-PJ01DB2-param.tmp -ddl PJ01DB2 -target-rdbms oracle -target-rdbms-version 11 -target-os unix

MetaWorld starter

Loading lib: /Qarefine/release/M2_L3_4/Linux64/lib64/localext.so

(funcall BATCH-TRANSLATE-SQL-DDL)

 

Starting translation at 2010/01/15 11:45:16

----------------------------------------------------------------------

Preparing for translation

Loading system description: /home2/wkb9/param/system.desc

Warning! OS clause is absent, assuming OS is IBM

Current OS is IBM-MF

Loading the SQL System:

... Building or Loading SQL-System...

... Loading SQL-System...

Loading /home2/wkb9/source/sql-system-STDB2ORA.pob at 11:45:16... done at 11:45:17

... Loading SQL-System-Statements...

Loading /home2/wkb9/source/sql-system-STDB2ORA-Statements.pob at 11:45:17... done at 11:45:17

... Loading SQL-System-Statements...done: #1<a SOURCE-FILE>

... Building or Loading SQL-System...done: #2<a SQL-SYSTEM>

... 6 elements in 1 schema.

Warning! OS clause is absent, assuming OS is IBM

----------------------------------------------------------------------

Beginning translation

Mode: MVS DB2 8 to UNIX ORACLE 11g

Schemas to translate: PJ01DB2

Schemas to skip:

Schemas to not translate:

----------------------------------------

{…]

Ending translation at 2010/01/15 11:45:17

WARNING: errors still exist but are ignored (Total=5>S+I=5).

Check /home2/wkb9/tmp/outputs/PJ01DB2/rdbms-converter-PJ01DB2.log log file

Process can continue

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

Converted DDLS are in /home2/wkb9/tmp/SQL/PJ01DB2 directory

Generated directives files are in /home2/wkb9/tmp/outputs/PJ01DB2 directory

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

 

Listing 4‑38 Messages Produced When Using the Options -g With rdbms.sh

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

Control of schema PJ01DB2

 

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

Control of templates

Project Templates list file is missing /home2/wkb9/param/rdbms/rdbms-templates.txt

OK: Use Default Templates list file

File name is /Qarefine/release/M2_L3_4/convert-data/default/rdbms/rdbms-templates.txt

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

Control of Mapper

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

GENERATION OF PROGRAMS

CMD : /Qarefine/release/M2_L3_4/scripts/launch file-converter -s /home2/wkb9/param/system.desc -mf /home2/wkb9/tmp/mapper-PJ01DB2.re.tmp -dmf /home2/wkb9/tmp/outputs/PJ01DB2/Datamap-PJ01DB2.re -td /home2/wkb9/tmp -tmps /home2/wkb9/tmp/rdbms-templates-PJ01DB2.tmp -target-sgbd oracle11 -target-os unix -varchar2 29 -abort

MetaWorld starter

Loading lib: /Qarefine/release/M2_L3_4/Linux64/lib64/localext.so

(funcall LOAD-THE-SYS-AND-APPLY-DMAP-AND-MAPPER)

*File-Converter*: We are in BATCH mode

Comand line arguments: begining of analyze

recognized argument -s value: /home2/wkb9/param/system.desc

recognized argument -mf value: /home2/wkb9/tmp/mapper-PJ01DB2.re.tmp

recognized argument -dmf value: /home2/wkb9/tmp/outputs/PJ01DB2/Datamap-PJ01DB2.re

recognized argument -td value: /home2/wkb9/tmp

recognized argument -tmps value: /home2/wkb9/tmp/rdbms-templates-PJ01DB2.tmp

recognized argument -target-sgbd value: oracle11

recognized argument -target-os value: unix

recognized argument -varchar2 value: 29

recognized argument -abort

End of Analyze

Parsing mapper file /home2/wkb9/tmp/mapper-PJ01DB2.re.tmp ...

Parsing data-map file /home2/wkb9/tmp/outputs/PJ01DB2/Datamap-PJ01DB2.re ...

Parsing system description file /home2/wkb9/param/system.desc ...

Warning! OS clause is absent, assuming OS is IBM

Current OS is IBM-MF

Loading /home2/wkb9/source/symtab-STDB2ORA.pob at 11:45:18... done at 11:45:18

... Loading SQL System from POB...

Loading /home2/wkb9/source/sql-system-STDB2ORA.pob at 11:45:18... done at 11:45:18

Build-Symtab-DL1 #1<a SYMTAB-DL1>

... Postanalyze-System-RPL...

sym=#2<a SYMTAB>

PostAnalyze-Common #2<a SYMTAB>

0 classes

0 classes

0 classes

0 classes

0 classes

13 classes

Loading /home2/wkb9/source/BATCH/pob/RSSBBB01.cbl.shrec...

Loading /home2/wkb9/source/COPY/pob/ODCSF0.cpy.cdm...

Loading /home2/wkb9/source/COPY/pob/ODCSFU.cpy.cdm...

Point 1 !!

Point 2 !!

Parsing file /home2/wkb9/tmp/outputs/PJ01DB2/ODCSF0.cpy ...

*Parsed 12 lines*

Point 3 !!

Point 4 !!

Point 5 !!

loading pob file /Qarefine/release/M2_L3_4/convert-data/templates/rdbms/unloading/jcl-unload-DB2-table-SQL.pgm.pob

Expanding /Qarefine/release/M2_L3_4/convert-data/templates/rdbms/unloading/jcl-unload-DB2-table-SQL.pgm ...

Writing ODCSF0X1.jclunload

[..}

Parsing template file /Qarefine/release/M2_L3_4/convert-data/default/rdbms/rdbms-move-assignation.pgm

Expanding /Qarefine/release/M2_L3_4/convert-data/default/rdbms/rdbms-move-assignation.pgm ...

Writing rdbms-move-assignation.lst

Rest in peace, Refine...

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

Generated components are in /home2/wkb9/tmp/Template/PJ01DB2

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

 

Listing 4‑39 Messages Produced When Using the Options -m with rdbms.sh

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

FORMATTING COBOL LINES

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

CHANGE ATTRIBUTE TO KSH or SH scripts

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

Components are modified into /home2/wkb9/tmp directory

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

 

Listing 4‑40 Messages Produced by The -r Options of rdbms.sh

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

REMOVE SCHEMA INFORMATION IN SPECIFIC SCRIPTS

Physical_File_is_PJ01DB2.ODCSF0=====

Modified <Templates>:loadrdbms-ODCSF0.ksh

Modified <outputs>:ODCSF0.ctl

Modified <SQL>:TABLE-ODCSF0.sql

Modified <SQL>:INDEX-ODCSF0.sql

Modified <SQL>:CONSTRAINT-ODCSF0.sql

IGNORED <SQL>:COMMENT-ODCSF0.sql is missing but is optional

==_all_schema_==

IGNORED <SQL>:VIEW-*.sql is missing but is optional

IGNORED <SQL>:SEQUENCE-*.sql is missing but is optional

IGNORED <SQL>:SYNONYM-*.sql is missing but is optional

IGNORED <SQL>:IDENTITY-*.sql is missing but is optional

=====

Number of modified files: 5

Number of ignored files: 5

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

Components are modified: remove schema_name string

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

 

Listing 4‑41 Messages Produced by the-i Option of rdbms.sh

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

INSTALL COMPONENTS INTO SOURCES USING modif-source-rdbms.sh.sh

===================================================

==_PJ01DB2.ODCSF0_==

Copied <Templates>:ODCSF0X1.jclunload to <td>/unload/rdbms/PJ01DB2/ODCSF0X1.jclunload

Copied <Templates>:loadrdbms-ODCSF0.ksh to <td>/reload/rdbms/PJ01DB2/ksh/loadrdbms-ODCSF0.ksh

[…}

Copied <SQL>:CONSTRAINT-ODCSF0.sql to <td>/SQL/rdbms/PJ01DB2/CONSTRAINT-ODCSF0.sql

IGNORED <SQL>:COMMENT-ODCSF0.sql is missing but is optional

===================================================

IGNORED <SQL>:VIEW-*.sql is missing but is optional

(…)

Copied <fixed-components>:CreateReportFromMVS.sh to <td>/reload/bin/CreateReportFromMVS.sh

===================================================

Dynamic_configuration

Copied <outputs>:rdbms-conv-PJ01DB2.xml to /home2/wkb9/param/dynamic-config/rdbms-conv-PJ01DB2.xml

=====

Number of copied files: 12

Number of executed scripts: 0

Number of ignored files: 5

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

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

Components are copied into /home2/wkb9/trf directory

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

 

Detailed Processing

This section describes the Command-line Syntax used by the DB2-to-Oracle Convertor, the Process Steps summary and the Conversion of DB2 Data Types.

The processes required on the source and target platforms concern:

•	Configuring the Environments and Installing the Components,

•	Unloading Data,

•	Transferring the Data,

•	Reloading the Data,

•	Checking the Transfers,

Command-line Syntax

rdbms.sh

Name

rdbms.sh - generate DB2 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/Linux Oracle databases.

Options

Generation Options

-C <schema name>

Triggers the generation in $TMPPROJECT, for the schema indicated, of the following components: ORACLE DDL, CTL files of SQL*LOADER, XML file used by the COBOL Converter, configuration file(mapper file and Datamap file).

If an error occurs, the process is aborted.

-c <schema name>

This option has the same result as the -C option, except the process will abort for any error or warning.

-g <schema name>

Triggers the generation in $TMPPROJECT, for the schema indicated, of the unloading and loading components. This generation depends on the information found in the configuration files.

Modification Options

-m <schema name>

Makes the generated SHELL scripts executable. COBOL programs are adapted to Micro Focus COBOL fixed format. When present, the shell script belonging to a project, (see File Modifying Generated Components), that modifies the generated sources is executed.

-r <schema name>

Removes the schema name from generated objects (ORACLE DDL, CTL file, KSH). When this option is used, the name of the schema can also be removed from COBOL components by using the option: remove-sql-qualifier Clause located in the COBOL conversion configuration file used when converting 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.

Final Option

-s <installation directory> (<schema name 1>, <schema name 2>, …)

Enables the generation of the COBOL converter 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.sh -Cgrmi $HOME/trf PJ01DB2

rdbms.sh -s $HOME/trf PJ01DB2

Unitary Usage Sequence

If the rdbms.sh options are used one at a time, they should be used in the following order:

1.	=> -c or -C

2.

=> -g

3.

=> -m

4.

=> -r

5.

=> -i

6.	=> -s (should be executed once steps 1 to 5 have been executed for all schemas).

Process Steps

Configuring the Environments and Installing the Components

This section describes the preparation work on the source and target platforms.

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).

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.

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

 

Table 4‑11 Target Platform Environment Variables

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: Oracle Database Globalization Support Guide
NLS_SORT	Set according to the instructions in the Oracle documentation: Oracle Database Globalization Support Guide
NLS_COMP	Set according to the instructions in the Oracle documentation: Oracle Database Globalization Support Guide

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

•	MT_DB_LOGIN.

Installing the MWDB2ORA Package Component on the Target Platform

Oracle Tuxedo Application Rehosting Workbench has to reproduce the same behavior found on the DB2/zOS platform on the Oracle database. In order to handle certain DB2/zOS specific cases, the DB2-to-Oracle Convertor includes an Oracle package named MWDB2ORA which contains Oracle functions.

Those functions are used for managing all DATE, TIME and TIMESTAMPS features. They are added to the COBOL programs by the COBOL Converter SQL Rules.

The package enabling the DB2 behavior is located in the directory:

REFINEDIR/convert-data/fixed-components/

The package is named:

MWDB2ORA.plb

To activate the package, install it on the target Oracle database:

1.	Copy MWDB2ORA.plb onto your target UNIX/Linux platform.

2.	Install the package under SQLPLUS:

sqlplus $MT_DB_LOGIN <<EOF

start REFINEDIR/convert-data/fixed-components/MWDB2ORA.plb

quit

EOF

Listing 4‑42 Messages Produced when Installing the MWDB2ORA Package Under SQLPLUS

SQL*Plus: Release 11.1.0.6.0 - Production on ...

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Package created.

Package body created.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Unloading Data

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

•	a data file,

•	a log file,

•	a SYSPUNCH file.

These unloading JCLs are named <table name>.jclunload

A return code of 0 is sent on normal job end.

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.

Transferring the Data

The unloaded data files should be transferred between the source z/OS platform and the target UNIX/Linux 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/Linux platform should be stored in the $DATA_SOURCE directory.

Reloading the Data

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

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 reloading script uses the SQL*LDR Oracle utility. Because this utility can access to ORACLE server only, this variable should not contain @<oracle_sid> string especially for this reloading step.

Transcoding and Reloading Command

Name

loadrdbms – transcode and reload data.

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).

Checking the Transfers

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

-c dsntiaul

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.