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.
• Description of the Output Files including the Generated Objects.
• Detailed Processing including the Command-line Syntax.
• For messages, see DB2-to-Oracle Converter Messages.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.Listing 4‑1 Schema Name Example
Table 4‑1 DB2 Objects to Convert 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> 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>
Table 4‑2 DB2 to Oracle Data Type Conversion
Table 4‑3 DB2 Column Property Conversion <value> depends on DB2 z/OS data type. 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.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.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.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.In addition to the parameters concerning schema, one other parameter should be set in the System Description File.Listing 4‑2 db-param.cfg Template# the file param/rdbms/rename-objects-<schema>.txt is automatically loaded by the tool if it exists.
Table 4‑4 db-param-cfg 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.Listing 4‑3 SQL CREATE INDEX Script Using Index Parameternlssort(MYCOL11, 'nls_sort=FRENCH') ASC
• By default, the tools generated by the DB2 to Oracle migration expect NLS_SORT in binary.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 file should be placed in the directory indicated by the $PARAM/rdbms variable.
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.Listing 4‑4 Renaming File ExampleListing 4‑5 rdbms-template.txtWhen 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 FileThis 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 unloading and loading components generated with the -i $HOME/trf option are placed in the following locations:
Table 4‑5 Component Locations <table name_8>.jclunload Location by <schema name> of the SQL scripts used to create the Oracle objects. Location by <schema name> of the COBOL transcoding programs. Location by <schema name> of the CTL files used by SQL*LOADER. Location by <schema name> of the reloading Korn shell scripts.
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.
Table 4‑6 Datamap File Name Parameters Listing 4‑8 Datamap File
Table 4‑7 Mapper file Name Parameters Listing 4‑9 Mapper File ExampleListing 4‑10 Mapper FileEach 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
Table 4‑9 Copy File Name Parameters Listing 4‑11 Copy File: SQL CodeListing 4‑12 Copy File: Generation SampleListing 4‑13 Copy File
Table 4‑10 Copy File Parameters 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:
The JCLs are named: <table name>.jclunload
Note: The .jclunload extension should be deleted for execution under z/OS.
• JOB cards: <crdjob>,
• library access paths: <db2_runlib_load_librairy>,
• Listing 4‑14 Unload JCL ExampleThe COBOL transcoding programs are generated using the -g option of the rdbms.sh command. They are then (using the -i option) installed in:The programs are named: MOD_<table name>.cblThe 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 ProgramsThe Reloading Korn shell scripts are generated using the -g option of the rdbms.sh command. They are then (using the -i option) installed in:The scripts are named: loadrdbms-<table name>.kshThe execution of the scripts produces an execution log in $MT_LOG/<nom de table>.logListing 4‑16 Reloading Script VariablesVarious messages may be generated during the three execution phases of the scripts, these messages are listed in Oracle Tuxedo Application Rehosting Workbench Messages.This step loads the Oracle table using the SQL*LOADER utility: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:
Note: To execute this step, it is necessary to transfer the DSNTIAUL log file to the target environment.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:Listing 4‑17 Oracle Table and Column GenerationListing 4‑18 Oracle Index GenerationThe 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.The following samples show an index creation with rdbms:indexsort=french parameter.Listing 4‑21 Oracle Constraint GenerationListing 4‑22 Oracle Comment GenerationListing 4‑23 Oracle View GenerationListing 4‑24 Oracle Sequence GenerationListing 4‑25 Oracle Synonym Generation
• Trigger associated with table <target_table_name>.Listing 4‑26 Oracle Sequence and Trigger GenerationThis file is generated using the -c or -C option of the rdbms.sh command. It is then (using the -i option) installed in:It is named <schema name>.lst.Listing 4‑27 Sample COBOL Conversion GuideListing 4‑28 CTL File ExampleListing 4‑29 DDL Translator Log File — headerListing 4‑30 DDL Translator Log File — ParametersListing 4‑31 DDL Translator Log File — Database ParametersListing 4‑32 DDL Translator Log File — Schema TranslationListing 4‑33 DDL Translator Log File — DDL AnalysisListing 4‑34 DDL Translator Log File — Output FilesListing 4‑35 DDL Translator Log File — Translation IssuesListing 4‑36 DDL Translator Log File — Trailerrdbms.sh creates different execution reports depending on the options chosen. In the following examples the following command is used:Listing 4‑40 Messages Produced by The -r Options of rdbms.shListing 4‑41 Messages Produced by the-i Option of rdbms.shThis section describes the Command-line Syntax used by the DB2-to-Oracle Convertor, the Process Steps summary and the Conversion of DB2 Data Types.rdbms.sh - generate DB2 migration components.rdbms.sh generates the Rehosting Workbench components used to migrate z/OS DB2 databases to UNIX/Linux Oracle databases.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).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.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.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.All these files are created in $PARAM/dynamic-configIf the rdbms.sh options are used one at a time, they should be used in the following order:
1.
2.
3.
4.
5. The components used for the reloading (generated in $HOME/trf/reload/rdbms) should be installed on the target platform.
Table 4‑11 Target Platform Environment Variables 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). 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.
1. Copy MWDB2ORA.plb onto your target UNIX/Linux platform.
2. Install the package under SQLPLUS:These unloading JCLs are named <table name>.jclunloadThe files transferred to the target UNIX/Linux platform should be stored in the $DATA_SOURCE directory.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.loadrdbms – transcode and reload data.This check uses the following option of the loadrdbms-<table name>.kshThis 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.