| Oracle Procedural Gateway® for APPC User's Guide 10g Release 2 (10.2) for UNIX Part Number B16210-01 |
|
|
View PDF |
| Oracle Procedural Gateway® for APPC User's Guide 10g Release 2 (10.2) for UNIX Part Number B16210-01 |
|
|
View PDF |
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:
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
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.
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.The PG DD and TIPs generated by PGAU support COBOL, specified as IBMVSCOBOLII when defining data.
When USAGE(PASS) has been specified on the PGAU DEFINE DATA statement, the following datatype and format conversions are supported:
PGAU TIPs convert the COBOL X datatype to a PL/SQL CHAR datatype of the same character length. NLS 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. NLS character set translation is also performed on signs, currency symbols, and spaces.
The following are supported:
For DISPLAY datatypes, the following sign specifications are supported:
Refer to "NUMBER_TO_RAW and RAW_TO_NUMBER Argument Values" in Appendix C, "The UTL_PG and UTL_RAW 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. |
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 RECORDs of TABLEs 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 RECORDs of TABLEs 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. |
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.
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.
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
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.
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.
