D Datatype Conversions

You must convert datatypes and data formats properly when you are using the PGAU tool to generate TIPs and when you are developing a custom TIP using PL/SQL and the UTL_RAW and UTL_PG functions.

Read this appendix to learn about datatype conversion as it relates to TIPs.

This appendix contains the following sections:

Length Checking
Conversion

D.1 Length Checking

PGAU-generated TIPs perform length checking at the end of every parameter sent and received.

Table D-1 provides a list of length parameters generated by PGAU:

Table D-1 Length Parameters

Parameter	Description
expected length	Is computed by PGAU when the TIP is generated.
convert length	Is summed by the TIP from each converted field.
send length	Is the transmitted send data length and is also equal to the actual length for send parameters.
receive length	Is the transmitted receive data length.

An exception is raised when the convert length of a sent parameter does not equal its expected length. This occurs if too many or too few send field conversions are performed.

An exception is raised when the convert length of a received parameter does not equal its received length. These length exceptions result when too few or too many conversions are performed.

A warning is issued when the expected length of a received parameter does not equal its convert or received length and data conversion tracing is enabled. This occurs when a maximum length record is expected, but a shorter record is transmitted and correctly converted.

D.1.1 Parameters Over 32K in Length

PGAU generates TIPs that support transmission of individual data parameters which exceed 32K bytes.

PGAU includes this support automatically when PGAU GENERATE processing detects the maximum length of a data parameter exceeding 32K.

This support is driven by the data definitions placed in the PG DD and cannot be selected by the user. To include the support, the data definition must actually or possible exceed 32K. To remove the support, you must decrease the parameter length to less than 32K, REDEFINE the data, and GENERATE the TIP again.

This support tests for field positions crossing the 32K buffer boundaries before and after conversion of those fields which lie across such boundaries. In the case of repeating groups, This can be many fields, for repeating groups, or few fields in the case of simple linear records.

Each test and the corresponding buffer management logic adds overhead.

Caution:

The target of a REDEFINE clause cannot reside in a previously processed buffer. Run-time TIP processing of the fields containing such REDEFINE clauses get unpredictable results.

D.2 Conversion

The PG DD and TIPs generated by PGAU support COBOL, specified as IBMVSCOBOLII when defining data.

D.2.1 USAGE(PASS)

When USAGE(PASS) has been specified on the PGAU DEFINE DATA statement, the following datatype and format conversions are supported:

PIC X
PIC G

PIC X Datatype Conversions

PGAU TIPs convert the COBOL X datatype to a PL/SQL CHAR datatype of the same character length. Globalization Support character set translation is also performed.

Note: COBOL lacks a datatype specifically designated for variable length data. It is represented in COBOL as a subgroup containing a PIC 9 length field followed by a PIC X character field. For example:

10 NAME.

15 LENGTH PIC S9(4).

15 LETTERS PIC X(30).

Given this context, it cannot be guaranteed that all instances of an S9(4) field followed by an X field are always variable length data. Rather than PGAU TIPs converting the above COBOL group NAME to a VARCHAR, the TIPs instead construct a nested PL/SQL record as follows:

TYPE NAME_typ is RECORD (
  LENGTH NUMBER(4,0),
  LETTERS CHAR(30));

TYPE ... is RECORD(
  ...
  NAME NAME_typ,
  ...

It is the client application's responsibility (based upon specific knowledge of the remote host data) to extract NAME.LENGTH characters from NAME.LETTERS and assign the result to a PL/SQL VARCHAR, if a VARCHAR is desired.

Character set conversion is performed for single byte encoded:

remote host character data, using either:
- DEFINE TRANSACTION NLS_LANGUAGE character set for an entire transaction, or
- REDEFINE DATA REMOTE_LANGUAGE character set for a single field, if specified.
local Oracle character data, using either:
- LANGUAGE character set of integrating server for an entire transaction, or
- REDEFINE DATA LOCAL_LANGUAGE character set for a single field, if specified.

PIC G Datatype Conversions

PGAU generated TIPs convert the COBOL G datatype to a PL/SQL VARCHAR2 datatype of the same length, allowing 2 bytes for every character position.

Character set conversion is performed for double-byte and multi-byte encoded:

remote host character data, using either:
- DEFINE TRANSACTION REMOTE_MBCS character set for an entire transaction, or
- REDEFINE DATA REMOTE_LANGUAGE character set for a single field, if specified.
local Oracle character data, using either:
- DEFINE TRANSACTION LOCAL_MBCS character set for an entire transaction, or
- REDEFINE DATA LOCAL_LANGUAGE character set for a single field, if specified.

Alphanumeric and DBCS Editing Field Positions

Table D-2 illustrates how PGAU interprets COBOL symbols in datatype conversions, by providing the definitions for the symbols.

Table D-2 COBOL Symbol Definitions

COBOL Symbols	Oracle Definition of COBOL Symbols - Data Content
'B'	blank (1 byte SBCS or 2 bytes DBCS depending on USAGE)
'0'	zero (1 byte SBCS)
'/'	forward slash (1 byte SBCS)
'G'	double byte

Edited positions in COBOL statement data received from the remote host are converted by PGAU along with the entire field and passed to the client application in the corresponding PL/SQL VARCHAR2 output variable.

When editing symbols are present, they are interpreted to mean the remote host field contains the COBOL data content and length indicated. The editing positions are included in the length of the data field, but conversion of all field positions is processed by PGAU as a single string and no special scanning or translation is done for edited byte positions.

Edited positions in COBOL statement data sent to the remote host are converted by PGAU along with the entire PL/SQL VARCHAR2 input variable passed from the client application.

Table D-3 provides an example of how PGAU converts COBOL datatypes:

Table D-3 COBOL-PGAU Conversion

COBOL Datatype	Description of Conversion by PGAU
`PIC XXXBBXX`	Is an alphanumeric field 7 bytes in length and would be converted in a single `UTL_RAW.CONVERT` call. No testing or translation is done on the contents of the byte positions indicated by 'B'. While COBOL language rules indicate that these positions contain "`blank`" in the character set specified for the remote host, what data is actually present is the user's responsibility.
`PIC GGBGGG`	Is a DBCS field 12 bytes in length and would be converted in a single `UTL_RAW.CONVERT` call. No testing or translation is done on the contents of the byte positions indicated by 'B'. While COBOL language rules indicate that these positions contain "`blank`" in the character set specified for the remote host, what data is actually present is the user's responsibility.
`PIC 9`	PGAU TIPs convert the COBOL `9` datatype to a PL/SQL `NUMBER` datatype of the same precision and scale. Globalization Support character set translation is also performed on signs, currency symbols, and spaces. The following are supported: `COMPUTATIONAL` (binary) `COMPUTATIONAL-3` (packed decimal) `COMPUTATIONAL-4` (binary) `DISPLAY` (zoned decimal) For `DISPLAY` datatypes, the following sign specifications are supported: `SEPARATE [CHARACTER]` `LEADING` `TRAILING` Refer to "NUMBER_TO_RAW and RAW_TO_NUMBER Argument Values" in Appendix C, "The UTL_PG Interface" for more information about numeric datatype conversions. `COMPUTATIONAL-1` and `COMPUTATIONAL-2` (floating point) datatypes are not supported.
`FILLER`	COBOL `FILLER` fields are recognized by PGAU by the spelling of the element name `FILLER`. PGAU does not generate any data conversion for such elements, but does require their space be properly allocated to preserve offsets within the records exchanged with the remote host transaction. If a `RENAMES` or `REDEFINES` definition covers a `FILLER` element, PGAU generates data conversion statements for the same area when it is referenced as a component of the `RENAMES` or `REDEFINES` variable. Such data conversion reflects only the format of the `RENAMES` or `REDEFINES` definition and not the bounds of the `FILLER` definition.

Format Conversion

Table D-4 describes format conversion:

Table D-4 Format Conversion Descriptions

Item	Description
`JUSTIFIED \|JUSTIFIED RIGHT`	This causes remote host transaction data to be converted as a PL/SQL `CHAR` datatype according to character datatype, as discussed in "PIC X Datatype Conversions", for both `IN` and `OUT` parameters. `IN` parameter data passed from the application is stripped of its rightmost blanks and left padded as required. Then it is sent to the remote host. `OUT` parameter data is aligned as it is received from the remote host and padded with blanks as required on the left. Then it is passed to the application.
`JUSTIFIED LEFT`	This causes warnings to be issued during TIP generation. No alignment is performed. This is treated as documentation. The remote host transaction data is converted as a PL/SQL `CHAR` datatype according to character datatype, as discussed in "PIC X Datatype Conversions", for both `IN` and `OUT` parameters.
`LENGTH IS field-2`	This is an Oracle extension to the data definition as stored in the PG DD. This extension exists only in the PGAU context and is not valid COBOL syntax. The purpose of this extension is to provide a means for variable-length character data to be processed more efficiently by the TIP conversion logic. This is an alternative to defining a variable-length PIC X field as `PIC X(1) OCCURS DEPENDING ON` `field-2`, where `field-2` is the length of the field. With this extension, the same field could be defined as `PIC X(5000) LENGTH IS` `field-2`, where `field -2` is the length of the field. The TIP is able to pick up the length and do the character set conversion on the field with a single `UTL_RAW.CONVERT` call instead of using a loop to do the conversion one character at a time. Note that the use of this construct does not affect the COBOL program. The `PIC X` (or `PIC G`) field is still fixed-length as far as COBOL is concerned, so the position of the data does not change, nor does the amount of data that is transferred between the gateway and the OLTP. However, if the field is the last field in a COBOL definition, then the COBOL program could be modified to send only the number of bytes required to satisfy the length set in the `field-2` field referenced by the `LENGTH IS` clause. The `LENGTH IS` clause can be specified only for `PIC X` and `PIC G` fields, and the picture mask for those fields cannot contain editing characters.
`OCCURS n TIMES`	This causes conversion of exactly '`n`' instances of a set of PL/SQL variables to or from a repeating group area within the remote host record, the size of which area equals the group length times '`n`' repetitions. PGAU generated TIPs employ PL/SQL `RECORD`s of `TABLE`s to implement an array-like subscript on fields within a repeating group. PL/SQL supports a single dimension `TABLE`, and consequently PGAU supports only a single level of an `OCCURS` group. Nested `OCCURS` groups are not supported. The conversion and formatting performed are dictated by the COBOL datatype of each subfield defined within the repeating group, as documented in "PIC X Datatype Conversions" and "Format Conversion".
`OCCURS m` `TO n` `TIMES DEPENDING ON` `field-2`	This causes conversion of at least '`m`' and not over '`n`' instances of a set of PL/SQL variables to or from a repeating group area within the remote host record, the size of which area equals the group length times the repetition count contained in the named field. PGAU generated TIPs employ PL/SQL `RECORD`s of `TABLE`s to implement an array-like subscript on fields within a repeating group. PL/SQL supports a single dimension `TABLE`, and consequently PGAU supports only a single level of an `OCCURS DEPENDING ON` group. Nested `OCCURS DEPENDING ON` groups are not supported. The conversion and formatting performed are dictated by the COBOL datatype of each subfield defined within the repeating group, as documented in "PIC X Datatype Conversions" and "Format Conversion". Range conversion: PGAU-generated TIPs use a '`FOR ... LOOP`' algorithm with a range of 1 to whatever `TIMES` upper limit was specified. When the TIP has been generated with the `DIAGNOSE(PKGEX(DC))` option, the PL/SQL FOR statement which iterates an `OCCURS DEPENDING ON` repeating group is preceded by an `IF` test to ensure at TIP runtime that the `DEPENDING ON` field contains a number which lies within the specified range for which the lower limit need not be 1. An exception is raised if this test fails.
`RENAMES item-2 THRU item-3`	A single PL/SQL variable declaration corresponds to a `RENAMES` definition. If all the subfields covered by a `RENAMES` definition are `PIC X`, then the PL/SQL variable is a `VARCHAR2`. Otherwise any non-PIC X subfield causes the PL/SQL variable datatype to be RAW. Lengths of renamed fields do not contribute to the overall parameter data length because the original fields dictate the lengths.
`REDEFINES item-2 WHEN item-3=value`	The '`WHEN item-3=value`' is an Oracle extension to the data definition as stored in the PG DD. This extension exists only in the PGA context and is not valid COBOL syntax. The purpose of this extension is to provide a means for the gateway administrator or application developer to specify the criteria by which the redefinition is to be applied. For example, a record type field is often present in a record and different record formats apply depending on which record type is being processed. The specification of which type value applies to which redefinition is typically buried in the transaction programming logic, not in the data definition. To specify which conversion to perform on redefined formats in the TIP, the `WHEN` criteria was added to PGA data definitions. PGAU generates PL/SQL nested record declarations which correspond in name and datatype to the subordinate elements covered by the `REDEFINES` definition. The standard PGAU datatype determination described in "PIC X Datatype Conversions". `LEVEL 01` `REDEFINE` is ignored: This permits remote host copybooks to include definitions which `REDEFINE` other transaction working storage buffers without having to define such buffers in the TIP or alter the copybook used as input for the definition.
`SYNCHRONIZED \|SYNCHRONIZED RIGHT`	This causes the numeric field to be aligned on boundaries as dictated by the remote host environment, compiler language, and datatype. Numeric conversion is performed on the aligned data fields according to numeric datatype, as discussed in "PIC X Datatype Conversions", for both `IN` and `OUT` parameters.
`SYNCHRONIZED LEFT`	This causes warnings to be issued during TIP generation and no realignment is performed. This is treated as documentation. Numeric conversion is performed on the aligned data fields according to numeric datatype, as discussed in "PIC X Datatype Conversions", for both `IN` and `OUT` parameters.

D.2.2 USAGE(ASIS)

When USAGE(ASIS) is specified on the PGAU DEFINE DATA statement, no conversion is performed. Consequently, each such field is simply copied to a PL/SQL RAW of the same byte length. No conversion, translation, or reformatting is done.

D.2.3 USAGE(SKIP)

When USAGE(SKIP) is specified on the PGAU DEFINE DATA statement, no data exchange is performed. The data is skipped as if it did not exist. Consequently, such fields are not selected from the PG DD, not reflected in the TIP logic, and presumed absent from the data streams exchanged with the remote host. The purpose of "SKIP" is to have definitions in the PG DD, but not active, perhaps because a remote host has either removed the field or has yet to include the field. SKIP allows an existing data definition to be used even though some fields do not exist at the remote host.

D.2.4 PL/SQL Naming Algorithms

Delimiters

COBOL special characters in record, group, and element names are translated when PGAU DEFINE inserts definitions into the PG DD, and by PGAU GENERATE when definitions are selected from the PG DD. Special characters are translated as follows:

hyphen is translated to underscore (_)
period is deleted

Qualified Compound Names

PL/SQL variable names are fully qualified and composed from:

PL/SQL record name as the leftmost qualifier corresponding to level 01 or 77 COBOL record name.
PL/SQL nested record names corresponding to COBOL group names.
PL/SQL nested fields corresponding to COBOL elements of datatype:
- CHAR or NUMBER corresponding to non-repeating COBOL elements.
- TABLE corresponding to COBOL elements which fall within an OCCURS or OCCURS DEPENDING ON group (COBOL repeating fields correspond to PL/SQL nested RECORDs of TABLE's).

Note that when referencing PL/SQL variables from calling applications, the TIP package name must be prefixed as the leftmost qualifier. Thus the fully qualified reference to the PL/SQL variable which corresponds to:

SKILL is:

tipname.EMPREC_Typ.SKILL(SKILL_Key)

HOME_ADDRESS ZIP is:

tipname.EMPREC_Typ.HOME_ADDRESS.ZIP.FIRST_FIVE
tipname.EMPREC_Typ.HOME_ADDRESS.ZIP.LAST_FOUR

Truncated and Non-Unique Names

PGAU truncates field names and corresponding PL/SQL variable names when the name exceeds:

26 bytes for fields within an aggregate record or group

This is due to the need to suffix each field or PL/SQL variable name with:
- "_Typ" for group names
- "_Tbl" for element names with a repeating group
or
30 bytes due to the PL/SQL limitation of 30 bytes for any name

The rightmost four characters are truncated. This imposes the restriction that names be unique to 26 characters.

Duplicate Names

COBOL allows repetitive definition of the same group or element names within a record, and the context of the higher level groups serves to uniquely qualify names. However, because PGAU-generated TIPs declare PL/SQL record variables which reference nested PL/SQL records for subordinate groups and fields, such nested PL/SQL record types can have duplicate names.

Given the following COBOL definition, note that ZIP is uniquely qualified in COBOL, but the corresponding PL/SQL declaration would have a duplicate nested record type for ZIP.

01  EMPREC.     
    05 HIREDATE             PIC X(8).   
    05 BIRTHDATE            PIC X(8). 
    05 SKILL                PIC X(12) OCCURS 4.  
    05 EMPNO                PIC 9(4).
    05 EMPNAME.  
       10 FIRST-NAME        PIC X(10).
       10 LAST-NAME         PIC X(15).   
    05 HOME-ADDRESS. 
       10 STREET            PIC X(20).  
       10 CITY              PIC X(15). 
       10 STATE             PIC XX. 
       10 ZIP.   
          15 FIRST-FIVE     PIC X(5).  
          15 LAST-FOUR      PIC X(4).   
    05 DEPT                 PIC X(45).    
    05 OFFICE-ADDRESS.          
       10 STREET            PIC X(20).  
       10 CITY              PIC X(15).
       10 STATE             PIC XX. 
       10 ZIP.                         
          15 FIRST-FIVE     PIC X(5).     
          15 LAST-FOUR      PIC X(4).  
    05 JOBTITLE             PIC X(20).

PGAU avoids declaring duplicate nested record types, and generates the following PL/SQL:

SKILL_Key BINARY_INTEGER;                                                                                 
TYPE SKILL_Tbl is TABLE of CHAR(12) 
                     INDEX by BINARY_INTEGER;                                                                                           
      TYPE EMPNAME_Typ is RECORD (   
           FIRST_NAME            CHAR(10),   
                       LAST_NAME                           CHAR(15));                                                                                    
      TYPE ZIP_Typ is RECORD (  
           FIRST_FIVE            CHAR(5), 
                     LAST_FOUR                         CHAR(4));                                                                               
      TYPE HOME_ADDRESS_Typ is RECORD (
           STREET                CHAR(20), 
           CITY                  CHAR(15),  
           STATE                 CHAR(2), 
                      ZIP                                     ZIP_Typ);                                                                                 
      TYPE OFFICE_ADDRESS_Typ is RECORD (   
           STREET                CHAR(20),
           CITY                  CHAR(15),  
           STATE                 CHAR(2),   
           ZIP                   ZIP_Typ); 
                                                                                       
      TYPE EMPREC_Typ is RECORD (  
           HIREDATE              CHAR(8), 
           BIRTHDATE             CHAR(8),  
           SKILL                 SKILL_Tbl, 
           EMPNO                 NUMBER(4,0),   
           EMPNAME               EMPNAME_Typ, 
           HOME_ADDRESS          HOME_ADDRESS_Typ,
           DEPT                  CHAR(45),
           OFFICE_ADDRESS        OFFICE_ADDRESS_Typ,
           JOBTITLE              CHAR(20));

However, in the case where multiple nested groups have the same name but have different subfields (see ZIP following):

05 HOME-ADDRESS.   
      10 STREET             PIC X(20). 
      10 CITY               PIC X(15).   
      10 STATE              PIC XX. 
      10 ZIP.                     
         15 LEFTMOST-FOUR   PIC X(4).
         15 RIGHMOST-FIVE   PIC X(5).    
05 DEPT                     PIC X(45).   
05 OFFICE-ADDRESS.  
   10 STREET                 PIC X(20). 
   10 CITY                   PIC X(15). 
   10 STATE                  PIC XX.  
   10 ZIP.                     
      15 FIRST-FIVE          PIC X(5). 
      15 LAST-FOUR           PIC X(4).  
05 JOBTITLE                  PIC X(20).

PGAU alters the name of the PL/SQL nested record type for each declaration in which the subfields differ in name, datatype, or options. Note the "02" appended to the second declaration (ZIP_Typ02), and its reference in OFFICE_ADDRESS.

TYPE EMPNAME_Typ is RECORD (    
    FIRST_NAME              CHAR(10),
      LAST_NAME                       CHAR(15));                                                                                 
TYPE ZIP_Typ is RECORD (
    LEFTMOST_FOUR         CHAR(4),  
     RIGHTMOST_FIVE        CHAR(5));                                                                 
    TYPE HOME_ADDRESS_Typ is RECORD (    
    STREET                CHAR(20), 
    CITY                  CHAR(15),
    STATE                 CHAR(2),
       ZIP                                  ZIP_Typ);                                                                                  
TYPE ZIP_Typ02 is RECORD ( 
    FIRST_FIVE            CHAR(5), 
       LAST_FOUR                       CHAR(4));                                                                                 
TYPE OFFICE_ADDRESS_Typ is RECORD ( 
    STREET                CHAR(20),   
    CITY                  CHAR(15),   
    STATE                 CHAR(2), 
       ZIP                                  ZIP_Typ02);                                                                                
TYPE EMPREC_Typ is RECORD (
    HIREDATE              CHAR(8), 
    BIRTHDATE             CHAR(8),  
    SKILL                 SKILL_Tbl,   
    EMPNO                 NUMBER(4,0),  
    EMPNAME               EMPNAME_Typ, 
    HOME_ADDRESS          HOME_ADDRESS_Typ, 
    DEPT                  CHAR(45),    
    OFFICE_ADDRESS        OFFICE_ADDRESS_Typ,   
    JOBTITLE               CHAR(20));

And the fully qualified reference to the PL/SQL variable which corresponds to:

HOME_ADDRESS.ZIP is:

tipname.EMPREC_Typ.HOME_ADDRESS.ZIP.LEFTMOST_FOUR
tipname.EMPREC_Typ.HOME_ADDRESS.ZIP.RIGHTMOST_FIVE

OFFICE_ADDRESS.ZIP is:

tipname.EMPREC_Typ.OFFICE_ADDRESS.ZIP.FIRST_FIVE
tipname.EMPREC_Typ.OFFICE_ADDRESS.ZIP.LAST_FOUR

Note that the nested record type name ZIP_Typ02 is not used in the reference, but is implicit within PL/SQL's association of the nested records.