Reference Guide

     Previous  Next    Open TOC in new window    View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

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:

Structure

See Also

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

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:

 


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:

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:

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:

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:

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.
Note: <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:

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

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:

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:

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:

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:

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:

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:

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:
  3. 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:

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.


  Back to Top       Previous  Next