This section describes the reengineering rules applied by the Rehosting Workbench when migrating data from a DB2 database to an Oracle database.The list of DB2 objects that are included in the migration towards Oracle are described in Creating the Oracle objects.Migrated DB2 objects keep their names when migrated to Oracle except for the application of the Rehosting Workbench renaming rules (see Preparing and implementing renaming rules).
Table 4‑1 DB2 to Oracle data types
Table 4‑2 DB2 to Oracle column properties Renaming rules should be placed in a file named rename-objects-<schema name>.txt. This file should be placed in the directory indicated by the $PARAM/rdbms parameter.
•
• Listing 4‑1 DDL example before migrationListing 4‑2 Oracle table example after migrationListing 4‑3 Oracle index example after migrationListing 4‑4 Oracle constraint example after migrationFor a DB2-To-Oracle migration, two parameters must be set in the system.desc System description file that is used by all of the Rehosting Workbench tools:By default, if the SQL commands of the DB2 DDL are prefixed by a qualifier or an authorization ID, the prefix is used by the Rehosting Workbench as the name of the schema—for example, CREATE TABLE <qualifier or authorization ID>.table name.The schema name can also be determined by the Rehosting Workbench using the global-options clause of the system.desc file.
• Listing 4‑5 Example db-param.cfg file
• target_rdbms_name: oracle
• The three rdbms parameters indicate the date, timestamp and time formats used by z/OS DB2 and stored in DSNZPARM. These parameters impact the reloading operations and the COBOL date and time manipulations.
• rdbms:date_format: YYYY/MM/DD
• rdbms:timestamp_format:YYYY/MM/DD HH24 MI SS
• rdbms:time_format:HH24 MI SSTo generate the components used to migrate data from DB2 databases to Oracle databases, the Rehosting Workbench uses the rdbms.sh command. This section describes the command.rdbms.sh generates the Rehosting Workbench components used to migrate z/OS DB2 databases to UNIX Oracle databases.The following components are generated in $TMPPROJECT: DDL Oracle, CTL files of the SQL*LOADER, XML file used by the COBOL converter, configuration files (mapper.re and Datamap.re). If an error or warning is encountered, the process will not abort.See Executing the transcoding and reloading scripts for information about the SQL scripts created during the generation operation.Removes the schema name from the generated objects (create table, table name, CTL file for SQL*LOADER, KSH). When this option is used, the name of the schema can also be removed from the COBOL components by using the option: sql-remove-schema-qualifier located in the config-cobol file (COBOL conversion configuration file) used when converting the COBOL components.Places the components in the installation directory. This operation uses the information located in the rdbms-move-assignation.pgm file.Enables the generation of the COBOL convertor configuration file. This file takes all of the unitary XML files of the project. All these files are created in $PARAM/dynamic-config.Example: rdbms-conv.txt rdbms-conv-PJ01DB2.xmlThe version.mk configuration file in $PARAM is used to set the variables and parameters required by the make utility.In version.mk specify where each type of component is installed and their extensions, as well as the versions of the different tools to be used. This file also describes how the log files are organized.The following general variables should be set at the beginning of migration process in the version.mk file:
• In addition, the RDBMS_SCHEMAS variable is specific to DB2 migration, it indicates the different schemas to process.The make RdbmsConvert command can be used to launch the Rehosting Workbench DB2-To-Oracle Converter. It enables the generation of the components required to migrate a DB2 database to Oracle.The make file launches the rdbms.sh tool with the -C, -g, -r, -m and -i options, for all schemas contained in the RDBMS_SCHEMAS variable.The unloading and loading components generated with the -i $HOME/trf option are placed in the following locations:
Table 4‑3 Location of components The generation log files produced when using the -c or -C options. rdbms-converter-<schema name> can be used to resolve problems.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.This section describes the tasks of unloading, transfer and reloading using the components generated using the Rehosting Workbench (see Generating the components).The components used for the unloading (generated in $HOME/trf/unload/rdbms) should be installed on the source z/OS platform. The generated JCL may need adapting to specific site constraints including JOB cards, library access paths and access paths to input and output files (Data Set Name – DSN).The components used for the reloading (generated in $HOME/trf/reload/rdbms) should be installed on the target platform (runtime).
The location of the generic reload and control scripts ($HOME/trf/reload/bin) Directory containing the <table name>.ctl files used by the SQL*LOADER ($HOME/trf/reload/rdbms/<schema name>/ctl). The reloading script loadrdbms-<table name>.ksh uses the SQL*LDR Oracle utility. Because this utility can access to ORACLE servers only, this script should be used in ORACLE servers and not with client connection. This variable should not contain an @<oracle_sid> string, especially for this reloading step.
• Example:ODCSF0X1.jclunloadThe scripts creating Oracle objects (tables, index, constraints, …) are created in the $HOME/trf/SQL/rdbms/<schema name> directory. They should be executed in the target Oracle instance.The <schema name>.lst file contains the names of all of the tables in hierarchical sequence (parent table then child tables).
This file contains all the CREATE INDEXes associated with the table <target_table_name>. This file will not be generated if there are no indexes defined on the table <target_table_name> Listing 4‑6 FILE CONTROL example – extract from program: MOD_ODCSF0.cblloadrdbms-<table name>.ksh<table name>.ctl.For the example provided in Example of a migration of DB2 objects, the generated script is:
• If the rdbms-converter-<schema name>.log file contains any errors (see Common problems and solutions).When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/trf PJ01DB2 STFILEORA the following message appears:When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/trf SCHEMA the following message appears:When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/trf PJ01DB2 the following message appears:When executing $REFINEDIR/$VERS/rdbms.sh -Cgrmi $HOME/bad-directory PJ01DB2 the following message appears: