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.
• Overview of the Migration Process.
• Description of the output files including the Generated objects.
• Detailed Processing including the Command-line syntax.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 3‑1 Schema name example
Table 3‑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 3‑2 DB2 to Oracle data type conversion
Table 3‑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, two other parameters should be set in the system description file.Listing 3‑2 db-param.cfg template# the file param/rdbms/rename-objects-<schema>.txt is automatically loaded by the tool if it exists.
Table 3‑4 db-param-cfg parameters Listing 3‑3 SQL CREATE INDEX script using index parameternlssort(MYCOL11, 'nls_sort=FRENCH') ASC
• 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 3‑4 Renaming file example:Listing 3‑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 3‑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 3‑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 3‑6 Datamap file name parameters Listing 3‑8 Datamap file
<revision>
Table 3‑7 Mapper file name parameters Listing 3‑9 Mapper file exampleListing 3‑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).
<revision>
Table 3‑8 Copy file name parameters Listing 3‑11 Copy file: SQL codeListing 3‑12 Copy file: Generation sampleListing 3‑13 Copy file
Table 3‑9 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 3‑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 compilation options documented in Compiler options.Listing 3‑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 3‑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.
Note: 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 3‑17 Oracle table and column generationListing 3‑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.Listing 3‑21 Oracle constraint generationListing 3‑22 Oracle comment generationListing 3‑23 Oracle view generationListing 3‑24 Oracle sequence generationListing 3‑25 Oracle synonym generationListing 3‑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 3‑27 Sample COBOL conversion guideListing 3‑28 CTL file exampleListing 3‑29 DDL Translator Log File — headerListing 3‑30 DDL Translator Log File — parametersListing 3‑31 DDL Translator Log File — database parametersListing 3‑32 DDL Translator Log File — schema translationListing 3‑33 DDL Translator Log File — DDL AnalysisListing 3‑34 DDL Translator Log File — output filesListing 3‑35 DDL Translator Log File — translation issuesListing 3‑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 3‑40 Messages produced by the -r options of rdbms.shListing 3‑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.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.If 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 3‑10 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). 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>.ksh