7 Data Type Support and Conversion in Oracle Database Provider for DRDA

Overview of Data Type Conversion

DRDA utilizes Formatted Data Object Content Architecture (FD:OCA) for datatype encoding. Several types do not have a direct analog to Oracle native types, and require conversion. Also, some Oracle data types have no direct encoding support in FD:OCA.For example, consider Oracle NUMBER, which may contain a wide range of values, both integers and floating point. This duality prevents it from being mapped to a specific DRDA type, to mitigate loss of value of the number. Any choice of type will have some loss of either precision or scale at extreme ranges of value.

There are two data type conversions used by Oracle Database Provider for DRDA: conversion of DRDA MetaData Descriptors to Oracle OCI interface types, and conversion of Oracle column types to DRDA MetaData Descriptors. For application programmers, these are described through the SQL Type of the bind variable or described column type. See sections "Converting DRDA Data Types to Oracle Data Types" and "Converting Oracle Data Type to DRDA"

A general mechanism for mapping Oracle NUMBER is covered in section "Data Type Equivalence and Remapping".

Numerical Range Considerations; General

When converting between Oracle NUMBER, IEEE floating point, IBM Hexadecimal floating point (HEX floating point, S390 or System390 floating point), and Decimal floating point (DECFLOAT) datatypes, note that they have different ranges and capabilities. For example, all values of IBM HEX FLOAT bind variables in a client-side program fit in an Oracle NUMBER, but not all values of Oracle NUMBER may be returned correctly in an IBM HEX FLOAT; DECFLOAT34 is a better choice.

Some other considerations include the following:

• Infinities. Some floating point types support positive and negative infinities.

  When infinities are used for datatypes that don't support them, the highest possible number for positive infinities and its negative for negative infinities is used.

• Floating Point. IEEE FLOAT columns may be defined in Oracle with types of BINARY_FLOAT and BINARY_DOUBLE. In DB2 z/OS the floating point types (REAL, FLOAT, DOUBLE and DOUBLE PRECISION) are IBM HEX floating point. In DB2/400 and DB2 LUW, the floating point types (REAL, FLOAT, DOUBLE and DOUBLE PRECISION) are IEEE floating point.

• Not a Number. Some data types support Not A Number (NAN), a special value to indicate either that no value was assigned, or the result of a computation is invalid or undefined.

Numerical Range Considerations, for COBOL Users

DRDA databases offer three options for integer types: SMALLINT (2 binary bytes), INTEGER (4 binary bytes), and BIGINT (8 binary bytes). During conversion, Oracle columns that hold equivalent values must be defined based on usage rather than on the type used in the DB2 CREATE TABLE definition.

The actual range of DRDA SMALLINT, INTEGER and BIGINT follows:

• SMALLINT has a lower bound of -32,768 and an upper bound of 32,767

• INTEGER has a lower bound of -2,147,483,648 and an upper bound of 2,147,483,647

• BIGINT has a lower bound of -9,223,372,036,854,775,808 and an upper bound of 9,223,372,036,854,775,807

However, at the level of the application, the COBOL variables that hold these DRDA column values may be declared either with a fixed number of decimal digits, or with the full binary precision of the corresponding DRDA integer datatypes.

In COBOL, the equivalent binary integer datatypes are defined as follows:

• USAGE of BINARY, COMPUTATIONAL, COMP, COMPUTATIONAL-4, and COMP-4; these are equivalent

• PICTURE of S9(1-4) for a 2-byte integer, S9(5-9) for a 4-byte integer, and S9(10-18) for an 8-byte integer.

The value is normally limited to the number of digits in the picture.

For example PICTURE S9(4) COMP is a 2-byte integer that normally ranges from -32,768 to +32,767. However, the generated COBOL code only allows the value to range from -9,999 to +9,999. When using these types of bind variables exclusively to access and update DRDA SMALLINT, INTEGER, and BIGINT columns, define the columns in Oracle as NUMBER(n), where n matches the above PICTURE S9(n) definition.

When using BINARY, COMPUTATIONAL, COMP, COMPUTATIONAL-4, and COMP-4 COBOL variables with the TRUNC(BIN) COBOL compiler option, the binary integers may range to the full bounds of the data type. Using COMPUTATIONAL-5 or COMP-5 has the same effect, regardless whether the TRUNC compiler option is in effect. When programming in COBOL, C, PL/I, or Assembler with a full range of the binary integers, define the Oracle column as NUMBER(n+1), where n matches the above PICTURE S9(n) definition.

Based on data type and usage in DRDA, here are the recommended substitute Oracle data types:

Used with COBOL COMP:

• SMALLINT should be converted to Oracle NUMBER(4)

• INTEGER should be converted to Oracle NUMBER(9)

• BIGINT should be converted to Oracle NUMBER(18)

Used with COBOL COMP, TRUNC(BIN), COMP-5, C, PL/I, or Assembler binary integer variables:

• SMALLINT should be converted to Oracle NUMBER(5)

• INTEGER should be converted to Oracle NUMBER(10)

• BIGINT should be converted to Oracle NUMBER(19)

When using the full range of binary integer values, it is advisable to implement Oracle constraints and limit the value to the range of the corresponding datatype.

For example, a DRDA SMALLINT gets an equivalent Oracle NUMBER column that supports a full range of SMALLINT values, only, as demonstrated in Example 7-1.

CREATE TABLE smint_tab
  (smint NUMBER(5) 
         CONSTRAINT check_smallint CHECK (smint BETWEEN -32768 AND 32767)
  )

Note however that there is a performance penalty for specifying this type of check constraint, Oracle verifies all constraints every time the column is updated.

Converting DRDA Data Types to Oracle Data Types

This section describes the mappings between DRDA and Oracle data types.

Note the following abbreviations:

• In a Single Byte Character Set (SBCS), the column can only contain single byte data.

• In a Multi-Byte Character Set (MBCS), the column may contain a combination of single-byte and multi-byte characters.

Converting Oracle Data Type to DRDA

Tables and procedures use Oracle data types. When describing objects, or returning data from a table or procedure, Oracle maps Oracle data types onto equivalent DRDA data types. This section discusses these mappings.

Data Type Equivalence and Remapping

Oracle does not provide discrete database data types such as SMALLINT, INTEGER or BIGINT DRDA data types. In some cases, often to limit the column's range of values, it may become necessary to define a numeric column with specific precision or scale. Oracle therefore supplies a more flexible numeric database data type, Oracle NUMBER, which may be defined by specified precision and scale. Oracle NUMBER may contain both integral and fractional values in the same column, if no specific range limitations have been defined for the column.

Consideration, therefore, must be made for appropriate database data types when migrating data from a non-Oracle database. This is particularly important when migrating applications that expect to handle data of a limited range or form.

For example, if the application accepts a data range specific to NUMBER(5), but the column is defined by data type NUMBER, it is likely that an inappropriate or invalid values may be inserted into the column and causing data issues when using or retrieving that value.

If the table definition is mapped to a close approximation of the original non-Oracle data, there should be no datatype compatibility issues. However, in cases where data that was not modeled accurately must be accessed, or if a query uses an expression that yields a non-range limited datatype, it may become necessary to apply an alternate datatype that is more compatible.

Consider that the COUNT(*) expression results in a non-range limited Oracle NUMBER datatype. If the application expects the result of the query that uses COUNT to be represented as a DRDA INTEGER data type, it becomes necessary perform one of the following steps to avoid a type mis-match:

• change the application to use the Oracle NUMBER

• change the query expression to CAST the result to the appropriate form

• remap the resulting datatype form

Often, it is neither practical nor feasible to modify the application, and remapping the datatype is the only workable solution.

The Application Server has a limited facility to convert Oracle NUMBER data type results to more discrete equivalent DRDA data types, on a per table or per column basis. This mechanism may also be used when the client AR is unable to properly convert the default mappings of Oracle NUMBER to DRDA data type. See "Converting Oracle Data Type to DRDA" for all supported conversions.

To apply data type mappings, you must invoke the PL/SQL function "SET_TYPEMAP". The procedure SET_TYPEMAP implements a specified type conversion map for a specified table and column expression. The syntax for the type map object name is table_name:column_expression. The wildcard character, *, may be used in place of table name to include all tables with the specified column expression. It may also be used to indicate that all column expressions for a specified table that evaluate to an Oracle NUMBER be type mapped.

The syntax for converting from Oracle NUMBER to another data type is NUMBER=datatype. See Table 7-3 for available data type names.

The default mapping of Oracle NUMBER is to DRDA DECFLOAT(34). Example 7-2 shows that queries that use a column directly may use re-mapping on the retrieved column as a DRDA type INTEGER. When using a column in a function it may be necessary to apply a typemap for the expression, as described in Example 7-3.

CREATE TABLE employees(employee_id NUMBER(6), first_name VARCHAR2(20), ...);

begin
   dbms_drdaas.set_typemap (
      'ORACLE', 'MYPACKAGE', 'EMPLOYEES:EMPLOYEE_ID',
      'NUMBER=INTEGER');
end;

SELECT COUNT(employee_id) FROM employees;

begin
  dbms_drdaas.set_typemap (
    'ORACLE', 'MYPACKAGE', 'EMPLOYEES:COUNT(EMPLOYEE_ID)', 
    'NUMBER=INTEGER' );
end;

Table 7-3 lists available typemap names and their conversion to DRDA data types.