File-to-Oracle Converter

This chapter describes Oracle Tuxedo Application Rehosting Workbench (Tuxedo ART Workbench) File-to-Oracle Converter used to migrate files from the source platform (z/OS) to Oracle tables, and describes 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.

This chapter contains the following topics:

•	Overview of the File-to-Oracle Converter

•	Description of the Input Components

•	Description of the Output Files

•	Detailed Processing

Overview of the File-to-Oracle Converter

Purpose

The purpose of this section is to describe precisely all the features of Tuxedo ART Workbench File-to-Oracle Converter tools including:

•	Inventory of files to migrate.

•	Detailed description of Oracle tables on the target platform for each file.

•	Description of the different commands to be used with the File-to-Oracle Converter.

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

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

Structure

•	Overview of the File-to-Oracle Converter.

•	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 File Convertor Messages.

See Also

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

•	COBOL Converter

The previous chapter explains all common usages:

•	File Convertor: Introduction

File Organizations Processed

When migrating files from a z/OS source platform to a target platform, the first question to ask, when VSAM is concerned, is whether to keep a file or migrate the data to an Oracle table.

Migrating to Oracle Table on the Target Platform

KSDS, RRDS and ESDS VSAM files can be migrated into Oracle tables.

To make this work, the first task is to list all of the VSAM files to be migrated, and then identify those files that should be converted to Oracle tables. For example, permanent files to be later used via Oracle or files that needs locking at the record level.

Oracle Tuxedo Application Rehosting Workbench Configuration Name

A configuration name is related to a set of files to be converted. Each set of files can be freely assembled. Each configuration could be related to a different application for example, or a set of files required for Tuxedo ART Workbench. The set of files can contain both files or Oracle tables targets.

VSAM Files Becoming Oracle Table

Specific Migration Rules Applied

•	Each table name is stipulated in the mapper-<configuration name>.re file using the table name clause.

•	Each elementary field name contained in a copy description of the file becomes a column in an Oracle table. Hyphens (-) are replaced by underscore (_) characters.

•	For sequential VSAM files (VSAM ESDS): Tuxedo ART Workbench adds a technical column: *_SEQ_NUM NUMBER.

This column is incremented each time a new line is added to the table and becomes the primary key of the table.

•	For relative VSAM files (VSAM RRDS): Tuxedo ART Workbench adds a technical column: *_RELATIVE_NUM.

The size of the column is deduced from the information supplied in the Datamap parameter file; the column becomes the primary key of the table.

The column:

•	is incremented when a sequential write is made to the table, and the relative key is zero.

•	contains a relative key when the relative key is not zero.

•	For indexed VSAM files (VSAM KSDS): Tuxedo ART Workbench does not add a technical column unless duplicate keys are accepted; the primary key of the VSAM file becomes the primary key of the table.

Rules Applied to Picture Clauses

The following rules are applied to COBOL Picture clauses when migrating data from VSAM files to Oracle tables.

 

Table 7‑1 Picture Clause Re-engineering

COBOL Picture	Oracle format
PIC 9(length) PIC S9(length) PIC 9(length) COMP-3 PIC S9(length) COMP-3	NUMBER(length)
PIC 9(prec,scale) PIC S9(prec,scale) PIC 9(prec,scale) COMP-3 PIC S9(prec,scale) COMP-3	NUMBER(prec+scale, scale)
PIC S9(length) BINARY PIC S9(length) COMP PIC S9(length) COMP-4	NUMBER(real_binary_length) Sample: PIC S9(4) BINARY is migrated as NUMBER(5)
COMP-1	DATATYPE_DOUBLE
COMP-2	DATATYPE_DOUBLE
PIC X(…)	Becomes CHAR if length <= 2000 Becomes VARCHAR2 if length > 2000 and <= 4000 If the parameter file:char_limit_until_varchar is set in the db-param.cfg file, it takes precedence over the above rule.

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 file.sh

The file.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

Description of the Configuration Files

This section lists the files and their parameters that can be used to control the migration of z/OS files to Oracle table.

db-param.cfg

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

$PARAM/db-param.cfg

Listing 7‑1 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>

# optional parameter

target_cobol:<target_cobol>

hexa-map-file:<hexa-map-file>

#

# specific parameters for FILE to RDBMS conversion

file:char_limit_until_varchar:<char_limit>

 

 

Parameters and Syntaxes

 

Table 7‑2 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
Optional Parameter
<target_cobol>	Name of COBOL language. Default value is “cobol_mf” for Micro Focus COBOL. Choice to this COBOL language impacts the generation of access functions.	cobol_mf cobol_it
<hexa-map-file>	Specifies a mapping table file between EBCDIC (z/OS code set) and ASCII (Linux/UNIX code set) hexadecimal values; if hexa-map-file is not specified, a warning will be logged.
Specific file-to-oracle conversion parameters
<char_limit>	• For a field size <= 2000, a COBOL alphanumeric field is migrated on ORACLE in CHAR • For a field size > 2000 it is migrated in VARCHAR2, except if the parameter file:char_limit_until_varchar is used. This parameter indicates the maximum length of a COBOL alphanumeric (PIC X) field before the field will be transformed into an ORACLE VARCHAR2 data type. If the parameter contains: file:char_limit_until_varchar:29 Then, fields longer than 29 characters will become VARCHAR2, fields shorter than 30 characters will become CHAR fields.

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/file/file-modif-source.sh

When presented, this file will be automatically executed at the end of the generation process. It will be called using the <configuration name> as an argument.

file-template.txt

This file is put in place during the installation of Tuxedo ART Workbench, it contains the templates that perform the generation of the different migration tools. The file is located in:

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

Listing 7‑2 file-template.txt

% Unloading all File ********************

% All SAM file were transfered using FTP/Binary

% VSAM unloaded step:

#VAR:TEMPLATES#/unloading/jcl-unload-MVS-REPRO.pgm

%

% To create a specific template, copy this template into :

% -- #VAR:PARAM#/file/specific-templates/unloading/jcl-unload-customer.pgm

%

% Loading **********************************************

#VAR:TEMPLATES#/loading/file-reload-files-txt.pgm

% Loading File to File ***************************

#VAR:TEMPLATES#/loading/unix-file/reload-files-ksh.pgm

#VAR:TEMPLATES#/loading/unix-file/reload-mono-rec.pgm

% Loading File to Oracle *************************

#VAR:TEMPLATES#/loading/unix-oracle/load-tables-ksh.pgm

#VAR:TEMPLATES#/loading/unix-oracle/rel-mono-rec.pgm

#VAR:TEMPLATES#/dml/clean-tables-ksh.pgm

#VAR:TEMPLATES#/dml/drop-tables-ksh.pgm

#VAR:TEMPLATES#/dml/create-tables-ksh.pgm

#VAR:TEMPLATES#/dml/ifempty-tables-ksh.pgm

#VAR:TEMPLATES#/dml/ifexist-tables-ksh.pgm

%

% Generate Logical & Relational Module *****************

#VAR:TEMPLATES#/dml/module/open-multi-assign-free.pgm

#VAR:TEMPLATES#/dml/module/open-mono-rec-idx-perf.pgm

#VAR:TEMPLATES#/dml/module/open-mono-rec-sequential.pgm

#VAR:TEMPLATES#/dml/module/open-mono-rec-relative.pgm

%

% and utilities ****************************************

#VAR:TEMPLATES#/dml/module/decharge-mono-rec.pgm

#VAR:TEMPLATES#/dml/module/recharge-table.pgm

#VAR:TEMPLATES#/dml/module/close-all-files.pgm

#VAR:TEMPLATES#/dml/module/init-all-files.pgm

%

% configuration file for translation and runtime *******

#VAR:TEMPLATES#/dml/generate-config-wb-translator-jcl.pgm

#VAR:TEMPLATES#/dml/generate-rdb-txt.pgm

%

% included file to include into modified-components

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

%

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

% MANDATORY

% : used just after the generation

#VAR:TEMPLATES#/dml/generate-post-process.pgm

% : used when using -i arguments

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

 

Note:

This file contains both File-to-File and File-to-Oracle migration parameters.

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

Listing 7‑3 Execution Log with Alternative Template File

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

Control of templates

OK: Use Templates list file from current project:

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

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

 

file-move-assignation.pgm

This file is placed during the installation of Tuxedo ART 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 file.sh, when launched using file.sh -i.

The file is located in:

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

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

Listing 7‑4 file-move-assignation.pgm Modification Instructions

[…]

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

*release_format=2.4

*

* 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

* If the 1st character is "!", target directory contains both directory and

* target file name.

*

[…]

 

Note:

This file contains both File-to-File and File-to-Oracle migration parameters.

Datamap File

This is a configuration file used by Tuxedo ART Workbench file converter to add or modify information on the physical files of a system.

See File Convertor: Introduction – Datamap File .

Mapper File

This is a configuration file used by Tuxedo ART Workbench File-to-Oracle Converter to associate each file to migrate

See File Convertor: Introduction – Mapper File.

Note:

In the mapper file, the converted clause has to be used for RDBMS Table target.

 

Table 7‑3 Mapper File Specific Parameters Used with the File-to-Oracle Converter

Parameter	Value
file <physical filename>	ZOS physical file name, Name used in the Datamap file.
converted	Indicates file is to be converted to Oracle table (converted clause can be combined with transferred clause)
transferred	Indicates that the file is to be loaded and reloaded (can be combined with converted clause).
include “<path/COPY name>”	Access path and name of the descriptive copy of the file to migrate.
map record <record name> defined in <“path/COPY name”>	• record name: corresponds to the level 01 field name of the copy description. • path/COPY name: corresponds to the access path and name of the descriptive copy of the file to migrate. Note: “map record” and “source record” parameters must use the same “record name” and “descriptive copy”. They are used for forward compatibility.
source record <record names> defined in <"path/COPY name">	• record name: corresponds to the level 01 field name of the copy description of the file to migrate. • path/COPY name: corresponds to the access path and name of the descriptive copy of the file to migrate. Note: “map record” and “source record” parameters must use the same “record name” and “descriptive copy”. They are used for forward compatibility.
Logical name <logical file name>	The Logical file name is chosen by the user, maximum eight characters. This name is used for naming the objects (COBOL, JCL) created by the different tools in Tuxedo ART Workbench.
Converter name <program name>	Same name and use as logical file name.
table name	Oracle table name.
attributes <attribute clause>	This optional clause has two attributes that can be used: • LOGICAL_MODULE_IN_ADDITION • LOGICAL_MODULE_ONLY Their action is described in the next table.

Table 7‑4 Mapper File Attributes

attributes <attribute clause>	Role
Attribute clause absent	In this case the following access functions are generated: • RM_<logical file name>, • UL_<logical file name>, • DL_<logical file name> and the Korn shell utilities. See Access Functions and Utility Programs.
LOGICAL_MODULE_IN_ADDITION	In this case the following access functions are generated: • ASG_<logical file name> • RM_<logical file name>, • UL_<logical file name>, • DL_<logical file name> and the Korn shell utilities. See Access Functions and Utility Programs.
LOGICAL_MODULE_ONLY	In this case only the ASG_<logical file name> access function is generated.

Listing 7‑5 Mapper File Example

ufas mapper STFILEORA

file PJ01AAA.SS.VSAM.CUSTOMER converted transferred

table name CUSTOMER

include "COPY/ODCSF0B.cpy"

map record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

source record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

logical name ODCSF0B

converter name ODCSF0B

attributes LOGICAL_MODULE_IN_ADDITION

 

In this example the mapper file is named STFILEORA. The file processes only one file named PJ01AAA.SS.VSAM.CUSTOMER that is migrated to an Oracle table using the convert option. The ODCSF0B.cpy copy file used to describe the file is one of the source copy files.

Mapping Strategy Clauses

[ field <field_name>

[ use detail table ]

[ use opaque field <field name> ]

[ table name <target table name> ]

[ mapped type <target data type> ]

[ discard field <field name> ]

[ discard subfields <field name> ]

[ discrimination rule ] ]

Mapping Strategy Clause Syntax and Parameters

For OCCURS and REDEFINES clauses, using discrimination rules, three reengineering possibilities are proposed:

•	Creation of sub-tables (use detail table)

•	Redefinitions: each description is associated with a sub-table (one sub-table for each description).

•	Occurs: one sub-table is created containing a technical column that references the original table to which the data corresponds.

•	Creation of an opaque field (use opaque field).

•	Redefinitions: all the descriptions are stored in an opaque field type CHAR or VARCHAR2.

•	Occurs: all the occurrences are stored in an opaque field type CHAR or VARCHAR2.

•	Extended description (default)

•	Redefinitions: all the fields described in the copy file are created as columns in the Oracle table.

•	Occurs: each occurrence of a field in a redefined area is created as a column in the Oracle table, one column for each occurrence in the OCCURS clause.

 

Table 7‑5 Mapping Strategies

Strategy	Description
table name < table name >	Name of sub-table in case of mapping 'use detail table'.
mapped type <target data type>	Enables the modification of the column type chosen by default. Two possibilities are proposed: CHAR or VARCHAR2.
discard field	Enables the deletion of a non-useful redefined field.
discard subfields	When a field has several levels of description, this option allows to keep only the higher level.

Mapping Strategy Examples

Discard Subfield Example

…

05 NIV1.

10 NIV2A PIC 99.

10 NIV2B PIC 999.

…

When discarding subfields at the level NIV1, Tuxedo ART Workbench File-to-Oracle Converter only processes the field NIV1 PIC 9(5). When not discarding subfields, the NIV1 field is ignored and the two fields NIV2A and NIV2B are processed.

Redefines With Default Option Example

This redefines example is without any specific options:

Listing 7‑6 Descriptive Copy of the File: PJ01AAA.SS.VSAM.CUSTOMER

01 VS-ODCSF0-RECORD.

05 VS-CUSTIDENT PIC 9(006).

05 VS-CUSTLNAME PIC X(030).

05 VS-CUSTFNAME PIC X(020).

05 VS-CUSTADDRS PIC X(030).

05 VS-CUSTCITY PIC X(020).

05 VS-CUSTSTATE PIC X(002).

05 VS-CUSTBDATE PIC 9(008).

05 VS-CUSTBDATE-G REDEFINES VS-CUSTBDATE.

10 VS-CUSTBDATE-CC PIC 9(002).

10 VS-CUSTBDATE-YY PIC 9(002).

10 VS-CUSTBDATE-MM PIC 9(002).

10 VS-CUSTBDATE-DD PIC 9(002).

05 VS-CUSTEMAIL PIC X(040).

05 VS-CUSTPHONE PIC 9(010).

05 VS-FILLER PIC X(100).

 

The mapper file implemented is:

Listing 7‑7 Mapper File for the File: PJ01AAA.SS.VSAM.CUSTOMER

ufas mapper STFILEORA

file PJ01AAA.SS.VSAM.CUSTOMER converted transferred

table name CUSTOMER

include "COPY/ODCSF0B.cpy"

map record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

source record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

logical name ODCSF0B

converter name ODCSF0B

attributes LOGICAL_MODULE_IN_ADDITION

field VS-CUSTBDATE

rule if VS-CUSTSTATE = "02" then VS-CUSTBDATE

else VS-CUSTBDATE-G

 

The table is generated as follows (all the unitary fields of the REDEFINES are handled).

Listing 7‑8 Table Generation for the File: PJ01AAA.SS.VSAM.CUSTOMER

WHENEVER SQLERROR CONTINUE;

DROP TABLE CUSTOMER CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE CUSTOMER (

VS_CUSTIDENT NUMBER(6) NOT NULL,

VS_CUSTLNAME VARCHAR2(30),

VS_CUSTFNAME CHAR (20),

VS_CUSTADDRS VARCHAR2(30),

VS_CUSTCITY CHAR (20),

VS_CUSTSTATE CHAR (2),

VS_CUSTBDATE NUMBER(8),

VS_CUSTBDATE_CC NUMBER(2),

VS_CUSTBDATE_YY NUMBER(2),

VS_CUSTBDATE_MM NUMBER(2),

VS_CUSTBDATE_DD NUMBER(2),

VS_CUSTEMAIL VARCHAR2(40),

VS_CUSTPHONE NUMBER(10),

VS_FILLER VARCHAR2(100),

CONSTRAINT PK_CUSTOMER PRIMARY KEY (

VS_CUSTIDENT)

);

 

REDEFINES With OPAQUE FIELD Option Example

Listing 7‑9 Descriptive Copy of the File: PJ01AAA.SS.VSAM.CUSTOMER

01 VS-ODCSF0-RECORD.

05 VS-CUSTIDENT PIC 9(006).

05 VS-CUSTLNAME PIC X(030).

05 VS-CUSTFNAME PIC X(020).

05 VS-CUSTADDRS PIC X(030).

05 VS-CUSTCITY PIC X(020).

05 VS-CUSTSTATE PIC X(002).

05 VS-CUSTBDATE PIC 9(008).

05 VS-CUSTBDATE-G REDEFINES VS-CUSTBDATE.

10 VS-CUSTBDATE-CC PIC 9(002).

10 VS-CUSTBDATE-YY PIC 9(002).

10 VS-CUSTBDATE-MM PIC 9(002).

10 VS-CUSTBDATE-DD PIC 9(002).

05 VS-CUSTEMAIL PIC X(040).

05 VS-CUSTPHONE PIC 9(010).

05 VS-FILLER PIC X(100).

 

The mapper file implemented is:

Listing 7‑10 Mapper File for the File: PJ01AAA.SS.VSAM.CUSTOMER

ufas mapper STFILEORA

file PJ01AAA.SS.VSAM.CUSTOMER converted transferred

table name CUSTOMER

include "COPY/ODCSF0B.cpy"

map record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

source record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

logical name ODCSF0B

converter name ODCSF0B

attributes LOGICAL_MODULE_IN_ADDITION

field VS-CUSTBDATE

use opaque field

rule if VS-CUSTSTATE = "02" then VS-CUSTBDATE

else VS-CUSTBDATE-G

 

The table is generated as follows (only the VS_CUSTBDATE field is kept).

Listing 7‑11 Table Generation for the File: PJ01AAA.SS.VSAM.CUSTOMER

WHENEVER SQLERROR CONTINUE;

DROP TABLE CUSTOMER CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE CUSTOMER (

VS_CUSTIDENT NUMBER(6) NOT NULL,

VS_CUSTLNAME VARCHAR2(30),

VS_CUSTFNAME CHAR (20),

VS_CUSTADDRS VARCHAR2(30),

VS_CUSTCITY CHAR (20),

VS_CUSTSTATE CHAR (2),

VS_CUSTBDATE RAW (8),

VS_CUSTEMAIL VARCHAR2(40),

VS_CUSTPHONE NUMBER(10),

VS_FILLER VARCHAR2(100),

CONSTRAINT PK_CUSTOMER PRIMARY KEY (

VS_CUSTIDENT)

);

REDEFINES With DETAIL TABLE Option Example

Listing 7‑12 Descriptive Copy of the File: PJ01AAA.SS.VSAM.CUSTOMER

01 VS-ODCSF0-RECORD.

05 VS-CUSTIDENT PIC 9(006).

05 VS-CUSTLNAME PIC X(030).

05 VS-CUSTFNAME PIC X(020).

05 VS-CUSTADDRS PIC X(030).

05 VS-CUSTCITY PIC X(020).

05 VS-CUSTSTATE PIC X(002).

05 VS-CUSTBDATE PIC 9(008).

05 VS-CUSTBDATE-G REDEFINES VS-CUSTBDATE.

10 VS-CUSTBDATE-CC PIC 9(002).

10 VS-CUSTBDATE-YY PIC 9(002).

10 VS-CUSTBDATE-MM PIC 9(002).

10 VS-CUSTBDATE-DD PIC 9(002).

05 VS-CUSTEMAIL PIC X(040).

05 VS-CUSTPHONE PIC 9(010).

05 VS-FILLER PIC X(100).

 

The mapper file implemented is:

Listing 7‑13 Mapper File for the File: PJ01AAA.SS.VSAM.CUSTOMER

ufas mapper STFILEORA

file PJ01AAA.SS.VSAM.CUSTOMER converted transferred

table name CUSTOMER

include "COPY/ODCSF0B.cpy"

map record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

source record VS-ODCSF0-RECORD defined in "COPY/ODCSF0B.cpy"

logical name ODCSF0B

converter name ODCSF0B

attributes LOGICAL_MODULE_IN_ADDITION

field VS-CUSTBDATE

use detail table

rule if VS-CUSTSTATE = "02" then VS-CUSTBDATE

else VS-CUSTBDATE-G

 

The tables are generated as follows (a parent table is generated using the fields not part of the REDEFINES, and two child tables are generated, one for each REDEFINES description).

Listing 7‑14 Table Generation for the File: PJ01AAA.SS.VSAM.CUSTOMER

WHENEVER SQLERROR CONTINUE;

DROP TABLE CUSTOMER CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE CUSTOMER (

VS_CUSTIDENT NUMBER(6) NOT NULL,

VS_CUSTLNAME VARCHAR2(30),

VS_CUSTFNAME CHAR (20),

VS_CUSTADDRS VARCHAR2(30),

VS_CUSTCITY CHAR (20),

VS_CUSTSTATE CHAR (2),

VS_CUSTEMAIL VARCHAR2(40),

VS_CUSTPHONE NUMBER(10),

VS_FILLER VARCHAR2(100),

CONSTRAINT PK_CUSTOMER PRIMARY KEY (

VS_CUSTIDENT)

);

 

 

WHENEVER SQLERROR CONTINUE;

DROP TABLE VS_CUSTBDATE CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE VS_CUSTBDATE (

VS_CUSTBDATE_CUSTIDENT NUMBER(6) NOT NULL,

VS_CUSTBDATE NUMBER(8),

CONSTRAINT FK_VS_CUSTBDATE_CUSTOMER FOREIGN KEY (

VS_CUSTBDATE_CUSTIDENT) REFERENCES CUSTOMER (

VS_CUSTIDENT),

CONSTRAINT PK_VS_CUSTBDATE PRIMARY KEY (

VS_CUSTBDATE_CUSTIDENT)

);

 

WHENEVER SQLERROR CONTINUE;

DROP TABLE VS_CUSTBDATE_G CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE VS_CUSTBDATE_G (

VS_CUSTBDATE_G_CUSTIDENT NUMBER(6) NOT NULL,

VS_CUSTBDATE_CC NUMBER(2),

VS_CUSTBDATE_YY NUMBER(2),

VS_CUSTBDATE_MM NUMBER(2),

VS_CUSTBDATE_DD NUMBER(2),

CONSTRAINT FK_VS_CUSTBDATE_G_CUSTOMER FOREIGN KEY (

VS_CUSTBDATE_G_CUSTIDENT) REFERENCES CUSTOMER (

VS_CUSTIDENT),

CONSTRAINT PK_VS_CUSTBDATE_G PRIMARY KEY (

VS_CUSTBDATE_G_CUSTIDENT));

 

Discrimination Rules

A discrimination rule must be set on the redefined field; it describes the code to determine which description of the REDEFINES to use and when.

[field <field_name>]

[…]

rule if <condition> then Field_Name_x

[elseif <condition> then field_Name_y]

[else Field_Name_z]

Discrimination Rules Syntax and Parameters

 

Table 7‑6 Discrimination Rules

Syntax	Description
Field_Name_{X,Y,Z}	This is the field that will be used when the associated condition is validated; this field is one of the redefined fields.
Condition	Is a conditional expression composed with field name, operators and COBOL constants. • Logical operators are: not, and, or • Comparison operators are: = <> < > • Specific operators are: is numeric, is all SPACE • Following COBOL constants may be used: spaces, zeros, high-value, low-value Note: These conditions can be parenthesized.

Discrimination Rules Examples

In the following example the fields DPODP-DMDCHQ, DPONO-PRDTIV, DP5CP-VALZONNUM are redefined.

Listing 7‑15 Discrimination Rule COBOL Description

01 ZART1.

05 DPODP PIC X(20).

05 DPODP-RDCRPHY PIC 9.

05 DPODP-DMDCHQ PIC X(6).

05 DPODP-REMCHQ REDEFINES DPODP-DMDCHQ.

10 DPODP-REMCHQ1 PIC 999.

10 DPODP-REMCHQ2 PIC 999.

05 DPODP-VIREXT REDEFINES DPODP-DMDCHQ.

10 DPODP-VIREXT1 PIC S9(11) COMP-3.

05 DPONO-NPDT PIC X(5).

05 DPONO-PRDTIV PIC 9(8)V99.

05 DPONO-PRDPS REDEFINES DPONO-PRDTIV PIC X(10).

05 DP5CP-VALZONNUM PIC 9(6).

05 DP5CP-VALZON REDEFINES DP5CP-VALZONNUM PIC X(6).

 

The following discrimination rules are applied:

Listing 7‑16 Discrimination Rules

field DPODP-DMDCHQ

rule if DPODP-RDCRPHY = 1 then DPODP-DMDCHQ

elseif DPODP-RDCRPHY = 2 then DPODP-REMCHQ

elseif DPODP-RDCRPHY = 3 then DPODP-VIREXT

else DPODP-DMDCHQ,

field DPONO-PRDTIV

rule if DPONO-NPDT (1:2) = "01" then DPONO-PRDTIV

elseif DPONO-NPDT (1:2) = "02" then DPONO-PRDPS,

field DP5CP-VALZONNUM

rule if DPODP-RDCRPHY is numeric then DP5CP-VALZONNUM

else DP5CP-VALZON

 

The first rule is to test the value of the numeric field DPODP-RDCRPHY.

The second rule tests the first two characters of an alphanumeric field DPONO-NPDT. Only the values 01 and 02 are allowed.

The third rule tests whether the field DPODP-RDCRPHY is numeric.

COBOL Description

Tuxedo ART Workbench File-to-Oracle Converter needs a description associated with each file, so a first step consists in preparing a COBOL copy description.

Once the COBOL description files have been prepared, the copy files described in the mapper-<configuration name>.re file should be placed in the $PARAM/file/recs-source directory.

If you use a COBOL copy book from the source platform to describe a file (see COBOL Description), then it is the location of the copy book that is directly used.

Description of the Output Files

File Locations

Location of Temporary Files

The temporary objects generated by Tuxedo ART Workbench File-to-Oracle Converter are stored in:

$TMPPROJECT

$TMPPROJECT/Template/<configuration name>

$TMPPROJECT/outputs/<configuration name>

Note:

The $TMPPROJECT variable is set to: $HOME/tmp

Location of Log Files

The execution log files are stored in:

•	Log generated by the option -g:

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

Location of Generated Files

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

 

Table 7‑7 Component Locations

Location	Contents
$HOME/trf/unload/file/<configuration name>	The JCL used for each unloading table are generated for each <configuration name>. These JCL are named: <file name>.jclunload
$HOME/trf/reload/file/<configuration name>	For a file to Oracle migration, the programs and KSH are named: RELTABLE-<target file name>.pco loadtable-<target file name>.ksh
$HOME/trf/SQL/file/<configuration name> (When migrating files to Oracle tables).	Location by <configuration name> of the SQL scripts used to create the Oracle objects.
$HOME/trf/config/tux	Location of configuration files used by Oracle Tuxedo Application Runtime for files migrated to tables.
$HOME/trf/DML	List of components is depending on the optional attributes clause initialized in the mapper file. See Mapping Strategy Clause Syntax and Parameters

Note:

<target table name> is the file name on the target platform, this file name is furnished in the mapper file.

Generated Objects

The following sections describe the objects generated during the migration of z/OS files and the directories in which they are placed.

Unloading JCL

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

$HOME/trf/unload/file/<configuration name>

Each JCL contains two steps and unloads one file using the z/OS IDCAMS REPRO 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 files)
Step 2	UNLOAD	IDCAMS REPRO of the indicated file

The JCLs are named: <file name>.jclunload

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: <cardjob_parameter_id>,

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

Listing 7‑17 Unload JCL Example

//<crdjob> <cardjob_parameter_1>,'FIL QSAM',

// <cardjob_parameter_2>

// <cardjob_parameter_3>

// <cardjob_parameter_4>

//*@ (C) Metaware:jcl-unload-MVS-REPRO.pgm. $Revision: 1.6 $

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

//* UNLOAD THE FILE:

//* <datain>.QSAM.CUSTOMER

//* INTO <data>.AV.QSAM

//* LENGTH=266

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

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

//* DELETE DATA AND LOG FILES

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

//DEL EXEC PGM=IDCAMS

//SYSPRINT DD SYSOUT=*

//SYSOUT DD SYSOUT=*

//SYSIN DD *

DELETE <data>.AV.QSAM.LOG

DELETE <data>.AV.QSAM

SET MAXCC=0

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

//* LAUNCH REPRO UTILITY

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

//COPYFILE EXEC PGM=IDCAMS

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

// DISP=(NEW,CATLG),

// UNIT=SYSDA,

// DSN=<data>.AV.QSAM.LOG

//SYSOUT DD SYSOUT=*

//INDD DD DISP=SHR,

DSN=METAW00.QSAM.CUSTOMER

//OUTD DD SPACE=(CYL,(150,150),RLSE),

// DISP=(NEW,CATLG),

// UNIT=SYSDA,

// DCB=(LRECL=266,RECFM=FB),

// DSN=<data>.AV.QSAM

//SYSIN DD *

REPRO INFILE(INDD) OUTFILE(OUTD)

/*

 

COBOL Transcoding Programs

Migration of z/OS Files to Oracle Tables

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

$HOME/trf/reload/file/<configuration name>/src

The programs are named: RELTABLE-<logical file name>.pco

The programs should be compiled using the target COBOL compilation options and Oracle Precompiler options documented in Compiler Options.

The compilation of these programs requires the presence of a CONVERTMW.cpy copy file adapted to the project, documented in Codeset Conversion..

These files read a file on input and directly load an Oracle table using the SQL INSERT verb.

Listing 7‑18 FILE CONTROL Section - for Rranscoding Programs

SELECT MW-ENTREE

ASSIGN TO "ENTREE"

ORGANIZATION IS RECORD SEQUENTIAL

ACCESS IS SEQUENTIAL

FILE STATUS IS IO-STATUS.

 

For Oracle table with technical column, a SEQUENCE object is created:

CREATE SEQUENCE <table_name>_<type>_SEQ START WITH <num_rows>

A commit is made every 1000 records:

IF MW-NB-INSERT >= 1000

CALL "do_commit"

Note:

The do_commit module is part of Oracle Tuxedo Application Runtime Batch.

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

DISPLAY "RELOADING TERMINATED OK".

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

DISPLAY " ".

DISPLAY "NUMERIC MOVED WHEN USING CHAR FORMAT : "

DISPLAY " NUMERIC-BCD : " MW-COUNT-NUMERIC-BCD-USE-X.

DISPLAY " NUMERIC-DISP: " MW-COUNT-NUMERIC-DISP-USE-X.

The last two lines displayed signal the movement of data into fields where the COBOL description does not match the content of the input file (packed numeric fields containing non-numeric data and numeric DISPLAY fields containing non-numeric data). When such cases are encountered, each error is displayed.

Note:

When migrating to a target platform using Intel hardware the message: “PROCESSOR UNIT IS INTEL” is output at the beginning of transcoding.

Reloading Korn Shell Scripts

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

$HOME/trf/reload/file/<configuration name>

Reloading Korn Shell Scripts for Migrating z/OS Files to Oracle Tables

The scripts are named: loadtable-<logical file name>.ksh

They contain a DDL creation phase, a transcoding (or loading) phase and a check phase. The different phases may be launched separately.

The execution of the scripts produces an execution log in $MT_LOG/<logical file name>.log

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

Listing 7‑19 Reloading Table Script Variables

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

echo "reloading ODCSF0B into ORACLE"

export DD_ENTREE=${DD_ENTREE:-${DATA_SOURCE}/ODCSF0B}

logfile=$MT_LOG/ODCSF0B.log

reportfile=${MT_LOG}/ODCSF0B.rpt

ddlfile=${DDL}/STFILEORA/ODCSF0B.sql

[…]

 

To change the file names, set the DD_ENTREE and DD_SORTIE variables before calling the script.

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

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

Creating Oracle DDL Phase

Oracle objects are created under SQLPLUS using: ${DDL}/STFILEORA/ODCSF0B.sql

sqlplus $MT_DB_LOGIN >>$logfile 2>&1 <<!EOF

WHENEVER SQLERROR EXIT 3;

start ${ddlfile}

exit

!EOF

 

On normal termination the following message is displayed:

echo "Table(s) created"

Transcoding and Loading Phases

These steps launch the execution of the COBOL transcoding program associated with the file processed:

runb RELTABLE-ODCSF0B >> $logfile 2>&1

On normal termination the following message is displayed:

echo "File ${DD_ENTREE} successfully transcoded and reloaded into ORACLE"

Check Phase

This step verifies after the reloading that the reloaded Oracle table contains the same number of records as the records transferred from ZOS on target platform. If the number of records is different, an error message is produced:. If the number of records is equal, this message is produced:

"Number of rows written in output file is equal to number calculated using the log file: OK"

Target DDL

The ORACLE DDL is generated using the -g option of the file.sh command. They are then (using the -i option) installed in:

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

They are named: <target file name>.sql

The format used is:

WHENEVER SQLERROR CONTINUE;

DROP TABLE <target_table_name> CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE <target_table_name> (

<seq_num_column_name> NUMBER(8) NOT NULL,

<target_column_name> <column_data_type> <attribute>[, …]

CONSTRAINT <constraint_name> PRIMARY KEY (<target_column_name>)

CONSTRAINT <uk_constraint_name> UNIQUE (<seq_num_column_name>)

CONSTRAINT <fk_constraint_name> FOREIGN KEY (<target_column_name>)]

);

Where:

<target_table_name>:

Oracle table name

<seq_num_column_name>:

Oracle column name; the format is xx_SEQ_NUM

<target_column_name>:

Oracle column name

<column_data_type>:

Oracle data type (CHAR, VARCHAR2 or NUMBER)

<attribute>:

NOT NULL when primary key

<constraint_name>:

Constraint name of primary key (PK_<Oracle table name>

<uk_constraint_name>:

Constraint name of unique key (UK_<Oracle table name>)

<fk_constraint_name>:

Constraint name of foreign key (FK_<Oracle table name>_<parent_table_name>)

 

Listing 7‑20 DDL Generation sql Example

WHENEVER SQLERROR CONTINUE;

DROP TABLE CUSTOMER CASCADE CONSTRAINTS;

WHENEVER SQLERROR EXIT 3;

CREATE TABLE CUSTOMER (

VS_CUSTIDENT NUMBER(6) NOT NULL,

VS_SEQ_NUM NUMBER(8) NOT NULL,

VS_CUSTLNAME VARCHAR2(30),

VS_CUSTFNAME CHAR (20),

VS_CUSTADDRS VARCHAR2(30),

VS_CUSTCITY CHAR (20),

VS_CUSTSTATE CHAR (2),

VS_CUSTBDATE NUMBER(8),

VS_CUSTEMAIL VARCHAR2(40),

VS_CUSTPHONE NUMBER(10),

VS_FILLER VARCHAR2(100),

CONSTRAINT PK_CUSTOMER PRIMARY KEY (

VS_CUSTIDENT)

CONSTRAINT UK_CUSTOMER UNIQUE (

VS_SEQ_NUM)

);

 

Access Functions and Utility Programs

Access Functions

These access functions are generated using the -g option of file.sh and installed in$HOME/trf/DML using the -i and -s options.

 

Table 7‑8 Access Functions

Access function	Role
RM_<logical file name>.pco	Relational access module to Oracle table that replaces the specified logical file name.
DL_<logical file name>.cbl	Download module of the specified logical file (function used by Oracle Tuxedo Application Runtime Batch).
UL_<logical file name>.cbl	Upload module of the specified logical file (function used by Oracle Tuxedo Application Runtime Batch).
ASG_<logical file name>.cbl	Optional module generated when there are multiple assigns. See Mapper File Attributes
getfileinfo.cbl	This program checks if the <logical file name>.rdb associated with the assign-name given as an input argument exists. This function is called by ASG_<logical file name>.cbl.
init_all_files.cbl	Calls all init_all_files_<configuration name>.cbl (function used by Oracle Tuxedo Application Runtime Batch).
init_all_files_<configuration name>.cbl	Initializes a transaction. All variables used by relational module and ASG_<logical file name> module are initialized for the configuration name listed (function used by Oracle Tuxedo Application Runtime Batch).
dml_locking.cbl	This program manages locking for all configuration files (function used by Oracle Tuxedo Application Runtime Batch).
close_all_files_<configuration name>.cbl	Closes a transaction. This program closes all cursors opened in tables for the configuration listed and unlocks all files opened with logical accessor ASG_<logical file name> (function used by Oracle Tuxedo Application Runtime Batch).
close_all_files.cbl	Calls all close_all_files_<configuration name>.cbl (function used by Oracle Tuxedo Application Runtime Batch).

Access Function Call Arguments

The RM_<logical file name>.pco and ASG_<logical file name>.cbl access functions use the following variables:

 

Table 7‑9 Access Call Implemented Variables

Variable	Description/origin
Function code	Indicates the type of operation to execute, for example OPEN, WRITE, etc. The code is passed using the FILE-CODE-F variable of the MWFITECH copy file.
File open mode	A file can be opened in different modes: INPUT, OUTPUT, I O, EXTEND. The mode is passed using the FILE-OPEN-MODE variable of the MWFITECH copy file.
IO-STATUS	The IO-STATUS variable is linked to each file providing the execution status of the last relational module operation.
Record to transmit or receive	The record to transmit has an access function for write operations or access by key; the record to receive has a read access function. These are described in the LINKAGE SECTION.
Name of secondary key to use	For indexed files with secondary keys, and only for files with this organization, an extra variable is required to identify the secondary key to use for a START operation. The name of the secondary key is passed using the FILE-ALT-KEY-NAME variable of the MWFITECH copy file. For files without secondary keys, this argument is unnecessary.
Relative Key	For a relative file, the value of the relative key is passed to or from the access module using the FILE-REL-KEY variable of the MWFITECH copy file.

Listing 7‑21 LINKAGE SECTION Structure

LINKAGE SECTION.

01 IO-STATUS PIC XX.

COPY MWFITECH.

* *COBOL Record Description

01 VS-ODCSF0-RECORD.

06 X-VS-CUSTIDENT.

07 VS-CUSTIDENT PIC 9(006).

06 VS-CUSTLNAME PIC X(030).

06 VS-CUSTFNAME PIC X(020).

06 VS-CUSTADDRS PIC X(030).

06 VS-CUSTCITY PIC X(020).

06 VS-CUSTSTATE PIC X(002).

06 X-VS-CUSTBDATE.

07 VS-CUSTBDATE PIC 9(008).

06 VS-CUSTBDATE-G REDEFINES VS-CUSTBDATE.

11 X-VS-CUSTBDATE-CC.

12 VS-CUSTBDATE-CC PIC 9(002).

11 X-VS-CUSTBDATE-YY.

12 VS-CUSTBDATE-YY PIC 9(002).

11 X-VS-CUSTBDATE-MM.

12 VS-CUSTBDATE-MM PIC 9(002).

11 X-VS-CUSTBDATE-DD.

12 VS-CUSTBDATE-DD PIC 9(002).

06 VS-CUSTEMAIL PIC X(040).

06 X-VS-CUSTPHONE.

07 VS-CUSTPHONE PIC 9(010).

06 VS-FILLER PIC X(100).

PROCEDURE DIVISION USING IO-STATUS

MW-FILE-TECH

VS-ODCSF0-RECORD.

 

Call Arguments Used

OPEN

For all OPEN operations, the FILE-CODE-F variable should contain the key-word OPEN.

The FILE-OPEN-MODE variable should contain the type of OPEN to perform as follows:.

 

Table 7‑10 Call Argument File Open Modes

Source	Target
OPEN INPUT filename1	INPUT => FILE-OPEN-MODE
OPEN OUTPUT filename1	OUTPUT => FILE-OPEN-MODE
OPEN I-O filename1	I-O => FILE-OPEN-MODE
OPEN EXTEND filename1	EXTEND => FILE-OPEN-MODE

CLOSE

For CLOSE operations, the FILE-CODE-F variable should contain the key-word CLOSE.

CLOSE-LOCK

For CLOSE LOCK operations, the FILE-CODE-F variable should contain the key-word CLOSE-LOCK.

DELETE

Depending on the file access mode, the DELETE operation is either the current record or the one indicated by the file key, or by RBA value.

The corresponding function code is indicated as follows:

 

Table 7‑11 Call Argument Delete Modes

Access	Source	Target
Sequential	DELETE filename1	DELETE-CUR => FILE-CODE-F
Random or dynamic	DELETE filename1	DELETE-KEY => FILE-CODE-F

READ

The function code depends on the file access mode and the type of read required: sequential read, read primary key or read secondary key, or by RBA value.

 

Table 7‑12 Read Operation Values Depending on Arguments

Access	Source	Target
Sequential	READ filename1 [NEXT]	READ-NEXT => FILE-CODE-F
Random	READ filename1	READ-KEY => FILE-CODE-F
Dynamic	READ filename1 NEXT	READ-NEXT => FILE-CODE-F
	READ filename1	READ-KEY => FILE-CODE-F
	READ filename1 PREVIOUS	READ-PREV => FILE-CODE-F
If DataName1 is a variable corresponding to the keyAltKey1	READ filename1 KEY DataName1	READ-ALT-KEY => FILE-CODE-F "AltKey1" => FILE-ALT-KEY-NAME
DataName1 represents the relative key	READ filename1 KEY DataName1	READ-REL-KEY => FILE-CODE-F "RelKeyVar" => FILE-REL-KEY
Lock used by Oracle Tuxedo Application Runtime CICS	READ filename1 UPDATE	READ-UPDATE => FILE-CODE-F

Note:

If the INTO clause is found, a MOVE operation is added after the call in order to set the value of the indicated field.

REWRITE

The function code depends on the file access mode and the type of read required: sequential read, read primary key or read secondary key, or by RBA value.

 

Table 7‑13 Rewrite Operation Values Depending on Arguments

Access	Source	Target
Sequential	REWRITE RecName1	REWRITE-CUR => FILE-CODE-F
Random or dynamic	REWRITE RecName1	REWRITE-KEY => FILE-CODE-F

Note:

If the FROM clause is found, a MOVE operation is added before the call in order to set the value of the indicated field.

START

Whether the file is relative, indexed, with or without secondary key, the function code depends on the exact type of start.

 

Table 7‑14 Rewrite Operation Values Depending on Arguments

When	Source	Target
	START file1	START-EQUAL => FILE-CODE-F
DataName1 represents the relative key or Primary Key of file1	START file1 KEY {EQUAL| = |EQUALS} DataName1	START-EQUAL => FILE-CODE-F
	START file1 KEY {EXCEEDS| > |GREATER} DataName1	START-SUP => FILE-CODE-F
	START file1 KEY {NOT LESS |GREATER OR EQUAL | NOT < | >= } DataName1	START-SUPEQ => FILE-CODE-F
	START file1 KEY {< |LESS} DataName1	START-INF => FILE-CODE-F
	START file1 KEY {NOT GREATER |LESS OR EQUAL | NOT > | <= } DataName1	START-INFEQ => FILE-CODE-F
	START file1 KEY {EQUAL| = |EQUALS} DataName1 GENERIC	START-GENEQ => FILE-CODE-F
DataName1 is a variable corresponding to the AltKey1 key	START file1 KEY {EQUAL| = |EQUALS} DataName1	AltKey1 => FILE-ALT-KEY-NAME START-ALT-EQUAL => FILE-CODE-F
	START file1 KEY {EXCEEDS| > |GREATER} DataName1	AltKey1 => FILE-ALT-KEY-NAME START-ALT-SUP => FILE-CODE-F
	START file1 KEY {NOT LESS| GREATER OR EQUAL | NOT < | >=} DataName1	AltKey1 => FILE-ALT-KEY-NAME START-ALT-SUPEQ => FILE-CODE-F
	START file1 KEY {< |LESS} DataName1	AltKey1 => FILE-ALT-KEY-NAME START-ALT-INF => FILE-CODE-F
	START file1 KEY {NOT GREATER |LESS OR EQUAL | NOT > | <= } DataName1	AltKey1 => FILE-ALT-KEY-NAME START-ALT-INFEQ => FILE-CODE-F

WRITE

The function code depends on the file access mode and the type of read required: sequential read, read primary key or read secondary key

 

Table 7‑15 Write Operation Values Depending on Arguments

Access	Source	Target
Sequential	WRITE RecName1	WRITE-SEQ => FILE-CODE-F
Random or dynamic	WRITE RecName1	WRITE-KEY => FILE-CODE-F

Note:

If the FROM clause is found, a MOVE operation is added before the call in order to set the value of the indicated field.

Copy Files to Be Implemented

The following copy files are used by certain access functions. They should be placed in the directory: < installation platform>/fixed-copy/ during the installation of Tuxedo ART Workbench:

•	MW-PARAM-TRACE-VAR.cpy

•	MW-PARAM-TRACE.cpy

•	MW-PARAM-GETFILEINFO-VAR.cpy

•	MW-PARAM-GETFILEINFO.cpy

•	MW-PARAM-ERROR-VAR.cpy

•	MW-PARAM-ERROR.cpy

•	MW-PARAM-DML-LOCKING.cpy

•	MWFITECH.cpy

•	ERROR-SQLCODE.cpy

Korn Shell Utilities

These KSH scripts are generated using the -g option of file.sh and then installed in $HOME/trf/SQL/file/<configuration name> using the -i option. When necessary, they are used by Oracle Tuxedo Application Runtime Batch.

 

Table 7‑16 Korn Shell Utilities

Korn shell script name	Role
cleantable-<logical file name>.ksh	Script file that CLEANs all tables associated with this file.
createtable-<logical file name>.ksh	Script file that CREATEs all table, constraint and indexes associated with this file.
droptable-<logical file name>.ksh	Script file that DROPs all tables associated with this file.
ifemptytable-<logical file name>.ksh	Script file that checks if all tables are empty.
ifexisttable-<logical file name>.ksh	Script file that checks if all tables exist.

Oracle Tuxedo Application Runtime for CICS Configuration Files

The desc.vsam and envfile_tux files are generated in the $HOME/trf/config/tux/ directory when VSAM files are migrated to Oracle tables. They are used by Oracle Tuxedo Application Runtime CICS.

COBOL and JCL Conversion Guide Files

These files are generated using the -s option of the file.sh command.

This file is used by Tuxedo ART Workbench COBOL Converter and JCL Converter to rename object names.

 

Table 7‑17 Conversion File Names

File-in-table-<configuration name>	Used by Tuxedo ART Workbench JCL converter
File-in-table.txt	Used by Tuxedo ART Workbench JCL converter
Conv-ctrl.txt	Used by Tuxedo ART Workbench COBOL converter
Conv-ctrl-<configuration name>	Used by Tuxedo ART Workbench COBOL converter

.rdb Files

These files are created when VSAM files are converted to Oracle tables. They are used by Oracle Tuxedo Application Runtime Batch to bridge the technical differences between the z/OS file on the source platform and the corresponding Oracle table on the target platform.

The files are generated in: $HOME/trf/data

They are named: <source platform physical file name>.rdb

The files contain two lines described in the next section.

Parameters and Syntax

${DATA}/<source platform physical file name> <max> <org> <form> UL_<logical file name> <asgn_in> DL_<logical file name> <asgn_out> RM_<logical file name> <target table name> ${DDL}/<configuration name/cleantable-<target table name>.ksh ${DDL}/<configuration name>/droptable-<target table name>.ksh ${DDL}/<configuration name>/createtable-<target table name>.ksh ${DDL}/<configuration name>/ifemptytable-<target table name>.ksh ${DDL}/<configuration name>/ifexisttable-<target table name>.ksh

IDX_KEY <column name> <n m>

REL_KEY - <m>

 

Table 7‑18 .rdb file Parameters

Parameter	Description
First Line:
<source platform physical file name>	Physical file name
<max>	Maximum Record Size (in COBOL description).
<org>	File organization: • IDX: indexed without alternate key • IDX_ALT: indexed with alternate key(s) • SEQ: sequential • REL: relative
<form>	Record format: • FIX: fixed file • VAR:<min> variable file with minimal size. If <min> is missing, minimal size will be 1.
UL_<logical file name>	Uploading component name used by Oracle Tuxedo Application Runtime Batch.
<asgn_in>	Assign file name used by the uploading component.
DL_<logical file name>	Downloading component name used by Runtime.
<asgn_out>	Assign file name used by the downloading component.
RM_<logical file name>	Relational module name.
<target table name>	Name of the first table name (master table name or first table name for multi-record).
${DDL}/<configuration name/cleantable-<target table name>.ksh	Name of the script file that CLEANs all tables associated with this file.
${DDL}/<configuration name/droptable-<target table name>.ksh	Name of the script file that DROPs all tables associated with this file
${DDL}/<configuration name>/createtable-<target table name>.ksh	Name of the script file that CREATEs all tables associated with this file and their objects (constraints, indexes).
${DDL}/<configuration name>/ifemptytable-<target table name>.ksh	Name of the script file that checks if all tables are empty.
${DDL}/<configuration name>/ifexisttable-<target table name>.ksh	Name of the script file that checks if all tables exist.
Second Line for indexed file and indexed with alternate key file only:
IDX_KEY	Constant.
<VS-column name>	Indexed key name (group zone name or elementary field name as described in COBOL description).
<n m>	• n: offset of the indexed key (in COBOL description). • m: length of the indexed key (in COBOL description).
Second Line for relative file:
REL_KEY	Constant
-	Constant
<m>	m: length of the relative key (in COBOL description).

Example of .rdb File

The following example is generated when migrating an indexed VSAM file to an Oracle table. On the source platform, the VSAM file is named: PJ01AAA.SS.VSAM.CUSTOMER

Listing 7‑22 .rdb indexed VSAM Example

${DATA}/PJ01AAA.SS.VSAM.CUSTOMER 266 IDX FIX UL_ODCSF0B ENTREE DL_ODCSF0B SORTIE RM_ODCSF0B CUSTOMER ${DDL}/STFILEORA/cleantable-ODCSF0B.ksh ${DDL}/STFILEORA/droptable-ODCSF0B.ksh ${DDL}/STFILEORA/createtable-ODCSF0B.ksh ${DDL}/STFILEORA/ifemptytable-ODCSF0B.ksh ${DDL}/STFILEORA/ifexisttable-ODCSF0B.ksh

IDX_KEY VS-CUSTIDENT 1 6

 

Execution Reports

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

file.sh -gmi $HOME/trf STFILEORA

 

Listing 7‑23 Messages Produced when Using the options -g with file.sh (step 1)

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

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

Control of configuration STFILEORA

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

Control of templates

OK: Use Default Templates list file

File name is /Qarefine/release/M3_L3_6/convert-data/default/file/file-templates.txt

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

Control of Mapper

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

COMPONENTS GENERATION

CMD : /Qarefine/release/M3_L3_6/scripts/launch file-converter -s /home2/wkb4/param/system.desc -mf /home2/wkb4/tmp/mapper-STFILEORA.re.tmp -dmf /home2/wkb4/param/file/Datamap-STFILEORA.re -td /home2/wkb4/tmp -tmps /home2/wkb4/tmp/file-templates-STFILEORA.tmp -target-sgbd oracle11 -target-os unix -varchar2 29 -print-ddl -print-dml -abort

MetaWorld starter

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

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

FILE-0092: *File-Converter*: We are in BATCH mode

FILE-0087: * Comand line arguments: begining of analyze

FILE-0088: * recognized argument -s value: /home2/wkb4/param/system.desc

FILE-0088: * recognized argument -mf value: /home2/wkb4/tmp/mapper-STFILEORA.re.tmp

FILE-0088: * recognized argument -dmf value: /home2/wkb4/param/file/Datamap-STFILEORA.re

FILE-0088: * recognized argument -td value: /home2/wkb4/tmp

FILE-0088: * recognized argument -tmps value: /home2/wkb4/tmp/file-templates-STFILEORA.tmp

FILE-0088: * recognized argument -target-sgbd value: oracle11

FILE-0088: * recognized argument -target-os value: unix

FILE-0088: * recognized argument -varchar2 value: 29

FILE-0089: * recognized argument -print-ddl

FILE-0089: * recognized argument -print-dml

FILE-0089: * recognized argument -abort

FILE-0091: * End of Analyze

FILE-0094: * Parsing mapper file /home2/wkb4/tmp/mapper-STFILEORA.re.tmp ...

FILE-0095: * Parsing data-map file /home2/wkb4/param/file/Datamap-STFILEORA.re ...

FILE-0096: * Parsing system description file /home2/wkb4/param/system.desc ...

Warning! OS clause is absent, assuming OS is IBM

Current OS is IBM-MF

Loading /home2/wkb4/source/symtab-STFILEORA.pob at 16:38:42... done at 16:38:42

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

1 classes

13 classes

Loading /home2/wkb4/source/BATCH/pob/RSSABB01.cbl.shrec...

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

Loading /home2/wkb4/source/COPY/pob/ODCSF0B.cpy.cdm...

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

FILE-0001: * Point 1 !!

FILE-0002: * Point 2 !!

FILE-0010: * Parsing file /home2/wkb4/source/COPY/ODCSF0.cpy ...

*Parsed 22 lines*

FILE-0010: * Parsing file /home2/wkb4/source/COPY/MW_SYSOUT.cpy ...

*Parsed 8 lines*

FILE-0010: * Parsing file /home2/wkb4/source/COPY/ODCSFU.cpy ...

*Parsed 24 lines*

FILE-0010: * Parsing file /home2/wkb4/source/COPY/ODCSF0B.cpy ...

*Parsed 22 lines*

FILE-0003: * Point 3 !!

FILE-0004: * Point 4 !!

FILE-0005: * Point 5 !!

FILE-0052: * loading pob file /Qarefine/release/M3_L3_6/convert-data/templates/file/unloading/jcl-unload-MVS-REPRO.pgm.pob

FILE-0085: * Expanding /Qarefine/release/M3_L3_6/convert-data/templates/file/unloading/jcl-unload-MVS-REPRO.pgm ...

FILE-0054: * Writing MW-SYSOUT.jclunload

FILE-0054: * Writing ODCSFU.jclunload

FILE-0054: * Writing ODCSF0Q.jclunload

[...]

FILE-0052: * loading pob file /Qarefine/release/M3_L3_6/convert-data/templates/file/dml/generate-post-process.pgm.pob

FILE-0085: * Expanding /Qarefine/release/M3_L3_6/convert-data/templates/file/dml/generate-post-process.pgm ...

FILE-0054: * Writing post-process-file.sh

FILE-0053: * Parsing template file /Qarefine/release/M3_L3_6/convert-data/default/file/file-move-assignation.pgm

FILE-0085: * Expanding /Qarefine/release/M3_L3_6/convert-data/default/file/file-move-assignation.pgm ...

FILE-0054: * Writing file-move-assignation.lst

Rest in peace, Refine...

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

Generated components are in /home2/wkb4/tmp/Template/STFILEORA

(Optionaly in /home2/wkb4/tmp/SQL/STFILEORA)

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

 

Listing 7‑24 Messages Produced when Using the Options -m with file.sh (step 2)

 

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

FORMATTING COBOL LINES

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

CHANGE ATTRIBUTE TO KSH or SH scripts

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

Components are modified into /home2/wkb9/tmp directory

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

Messages produced using the -i option in file.sh (step 3)

INSTALL COMPONENTS INTO SPECIFIC DIRECTORY USING file-move-assignation.lst

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

==_PJ01AAA.SS.VSAM.CUSTOMER_==

Copied <Templates>:ODCSF0B.jclunload to <td>/unloading/file/STFILEORA/ODCSF0B.jclunload

Copied <Templates>:loadtable-ODCSF0B.ksh to <td>/reload/file/STFILEORA/loadtable-ODCSF0B.ksh

Copied <Templates>:RELTABLE-ODCSF0B.pco to <td>/reload/file/STFILEORA/RELTABLE-ODCSF0B.pco

Copied <Templates>:ASG_ODCSF0B.cbl to <td>/DML/ASG_ODCSF0B.cbl

Copied <Templates>:RM_ODCSF0B.pco to <td>/DML/RM_ODCSF0B.pco

Copied <Templates>:DL_ODCSF0B.cbl to <td>/DML/DL_ODCSF0B.cbl

Copied <Templates>:UL_ODCSF0B.cbl to <td>/DML/UL_ODCSF0B.cbl

Copied <Templates>:PJ01AAA.SS.VSAM.CUSTOMER.rdb to <td>/data/PJ01AAA.SS.VSAM.CUSTOMER.rdb

Copied <SQL>:ODCSF0B.sql to <td>/SQL/file/STFILEORA/ODCSF0B.sql

Copied <Templates>:cleantable-ODCSF0B.ksh to <td>/SQL/file/STFILEORA/cleantable-ODCSF0B.ksh

Copied <Templates>:droptable-ODCSF0B.ksh to <td>/SQL/file/STFILEORA/droptable-ODCSF0B.ksh

Copied <Templates>:createtable-ODCSF0B.ksh to <td>/SQL/file/STFILEORA/createtable-ODCSF0B.ksh

Copied <Templates>:ifemptytable-ODCSF0B.ksh to <td>/SQL/file/STFILEORA/ifemptytable-ODCSF0B.ksh

Copied <Templates>:ifexisttable-ODCSF0B.ksh to <td>/SQL/file/STFILEORA/ifexisttable-ODCSF0B.ksh

[...]

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

==_PJ01AAA.SS.QSAM.CUSTOMER.REPORT_==

Copied <Templates>:loadfile-MW-SYSOUT.ksh to <td>/reload/file/STFILEORA/loadfile-MW-SYSOUT.ksh

Copied <Templates>:RELFILE-MW-SYSOUT.cbl to <td>/reload/file/STFILEORA/RELFILE-MW-SYSOUT.cbl

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

==_PJ01AAA.SS.QSAM.CUSTOMER.UPDATE_==

Copied <Templates>:loadfile-ODCSFU.ksh to <td>/reload/file/STFILEORA/loadfile-ODCSFU.ksh

Copied <Templates>:RELFILE-ODCSFU.cbl to <td>/reload/file/STFILEORA/RELFILE-ODCSFU.cbl

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

==_PJ01AAA.SS.QSAM.CUSTOMER_==

Copied <Templates>:loadfile-ODCSF0.ksh to <td>/reload/file/STFILEORA/loadfile-ODCSF0.ksh

Copied <Templates>:RELFILE-ODCSF0.cbl to <td>/reload/file/STFILEORA/RELFILE-ODCSF0.cbl

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

Copied <Templates>:close_all_files_STFILEORA.cbl to <td>/DML/close_all_files_STFILEORA.cbl

Copied <Templates>:init_all_files_STFILEORA.cbl to <td>/DML/init_all_files_STFILEORA.cbl

Copied <Templates>:reload-files.txt to <td>/reload/file/STFILEORA/reload-files.txt

Copied <fixed-components>:getfileinfo.cbl to <td>/DML/getfileinfo.cbl

Copied <fixed-components>:MWFITECH.cpy to <td>/fixed-copy/MWFITECH.cpy

Copied <fixed-components>:MW-PARAM-ERROR.cpy to <td>/fixed-copy/MW-PARAM-ERROR.cpy

Copied <fixed-components>:MW-PARAM-ERROR-VAR.cpy to <td>/fixed-copy/MW-PARAM-ERROR-VAR.cpy

Copied <fixed-components>:MW-PARAM-TRACE.cpy to <td>/fixed-copy/MW-PARAM-TRACE.cpy

Copied <fixed-components>:MW-PARAM-TRACE-VAR.cpy to <td>/fixed-copy/MW-PARAM-TRACE-VAR.cpy

Copied <fixed-components>:MW-PARAM-GETFILEINFO.cpy to <td>/fixed-copy/MW-PARAM-GETFILEINFO.cpy

Copied <fixed-components>:MW-PARAM-GETFILEINFO-VAR.cpy to <td>/fixed-copy/MW-PARAM-GETFILEINFO-VAR.cpy

Copied <fixed-components>:MW-PARAM-DML-LOCKING.cpy to <td>/fixed-copy/MW-PARAM-DML-LOCKING.cpy

Copied <fixed-components>:ERROR-SQLCODE.cpy to <td>/fixed-copy/ERROR-SQLCODE.cpy

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

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

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

Dynamic_configuration

Copied <Templates>:File-in-table-STFILEORA to /home2/wkb9/param/dynamic-config/File-in-table-STFILEORA

Copied <Templates>:../../Conv-ctrl-STFILEORA to /home2/wkb9/param/dynamic-config/Conv-ctrl-STFILEORA

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

post-process

executed <Templates>:post-process-file.sh

/home2/wkb9/param/dynamic-config/Conv-ctrl-STFILEORA treated

=====

Number of copied files: 37

Number of executed scripts: 1

Number of ignored files: 0

 

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

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

Components are copied into /home2/wkb9/trf directory

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

 

 

Detailed Processing

This section describes the Command-Line Syntax used by the File-to-Oracle Converter, and the Process Steps summary.

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

file.sh

Name

file.sh - generate file migration components.

Synopsis

file.sh [ [-g] [-m] [-i <installation directory>] <configuration name> | -s <installation directory> (<configuration name1>,<configuration name2>,...) ]

Description

file.sh generates Tuxedo ART Workbench components used to migrate z/OS files to Oracle databases.

Options

Generation Options

-g <configuration name>

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

Modification Options

-m <configuration name>

Makes the generated SHELL scripts executable. COBOL programs are adapted to the target COBOL fixed format. When present, the shell script described in File Modifying Generated Components is executed.

Installation Option

-i <installation directory> <configuration name>

Places the components in the installation directory. This operation uses the information located in the file-move-assignation.pgm file.

Final Option

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

Enables the generation of the COBOL and JCL converter configuration files and DML utilities. These generated files take all of the unitary files of the project.

All configuration files are created in $PARAM/dynamic-config and DML files in <trf>/DML directory.

Example

file.sh -gmi $HOME/trf FTFIL001

Unitary Usage Sequence

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

1.

=> -g

2.

=> -m

3.

=> -i

4.	=> -s (should be executed once steps 1 to 3 have been executed for all configurations).

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/file) 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 out put files).

Installing the Reloading Components on the Target Platform

The components used for the reloading (generated in $HOME/trf/reload/file) should be installed on the target platform.

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

 

Table 7‑19 Target Platform Environment Variables

Variable	Value
DATA_SOURCE	The name of the directory containing the unloaded files 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.
DATA_TRANSCODE	Temporary directory used by the file binary data transcoding script (contains temporary files in ASCII format).
DDL	The location of SQL scripts used to create Oracle objects: ($HOME/trf/SQL/file/<configuration name>).
NLS_LANG	Set according to the instructions in the Oracle Database Globalization Support Guide.
PATH	This UNIX/Linux variable has to contain the directory of the Oracle Tuxedo Application Runtime for Batch utilities.
RDB_DATA	The directory where the *.rdb files are generated by workbench. When "use-file-catalog" is set in system description file, this variable is necessary. E.g., "$HOME/trf/data".

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

•	MT_DB_LOGIN.

Compiling COBOL Transcoding Programs

The COBOL transcoding programs should be compiled using the options specified in Compiler Options.

Compiling these programs requires the presence of a copy of CONVERTMW.cpy adapted to the project.

Unloading Data

To unload each file, a JCL using the IBM IDCAMS REPRO utility is executed. The IDCAMS REPRO utility creates two files for each file:

•	a data file

•	a log file

These unloading JCLs are named <logical filename>.jclunload

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

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 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/file/<configuration name>/

For a file-to-Oracle conversion, the format of the script names is:

loadtable-<logical file name>.ksh

Transcoding and Reloading Command for Tables

Name

loadtable transcode and reload data to table.

Synopsis

loadtable-<logical file name>.ksh [-t] [-l] [-c: <method>]

Options

-t

Transcode the file.

-l

Reload the file

-c ftp:<…>:<…>

Implement the verification of the transfer (see Checking the Transfers).

Checking the Transfers

This check uses the following option of the loadtable-<logical file name>.ksh

-c ftp:<name of transferred physical file>:<name of FTP log under UNIX>

This option verifies, after the reloading, that the physical file transferred from z/OS and the Oracle table reloaded on the target platform contains the same number of records. This check is performed using the FTP log and the execution report of the reloading program. If the number of records is different, an error message is produced.