DRDA Data Type to Oracle Data Type Conversion

4.10 DRDA Data Type to Oracle Data Type Conversion

To move data between applications and the database, the gateway binds data values from a host variable or literal of a specific data type to a data type understood by the database.

Therefore, the gateway maps values from any version of the DRDA server into appropriate Oracle data types before passing these values back to the application or Oracle tool.

Table 4-4 lists the data type mapping and restrictions. The DRDA server data types that are listed in the table are general. Refer to documentation for your DRDA database for restrictions on data type size and value limitations.

Table 4-4 Data Type Mapping and Restrictions

DRDA server	Oracle External	Criteria	If Oracle uses large varchar (32k)
`CHAR(N)`	`CHAR(N)`	`N` < = `2000`	-
`VARCHAR (N)`	`VARCHAR2(N)` `LONG`	`N` < = `4000` `4000` < `N`	`N` <= `32767` `32767` < `N`
`LONG VARCHAR(N)`	`VARCHAR2(N)`	`N` `4000`	`N` <= `32767`
`LONG VARCHAR(N)`	`LONG`	`4000` < `N`	`32767` < `N`
`CHAR(N) FOR BIT DATA`	`RAW(N)`	`N` `255`	-
`VARCHAR(N) FOR BIT DATA`	`RAW(N)`	`1` `N` `2000`	`1` <= `N` <= `32767`
`VARCHAR(N) FOR BIT DATA`	`LONG RAW`	`2000` < `N`	`N` < `32767`
`LONG VARCHAR(N) FOR BIT DATA`	`RAW(N)`	`1` <= `N` <= `2000`	`1` <= `N` <= `32767`
`LONG VARCHAR(N) FOR BIT DATA`	`LONG RAW`	`2000` < `N`	`N` < `32767`
`DATE`	`DATE`	Refer to Performing Date and Time Operations	-
`TIME`	`CHAR(8)`	See Performing Date and Time Operations	-
`TIMESTAMP`	`CHAR(26)`	See Performing Date and Time Operations	-
`GRAPHIC`	`CHAR(2N)`	`N` <= `1000`	-
`VARGRAPHIC`	`VARCHAR2(2N)` `LONG`	`N` <= `2000` `2000` <= `N`	`N` <= `16370` `16370` < `N`
`LONG VARGRAPHIC`	`VARCHAR2(2N)` `LONG`	`N` <= `2000` `2000` < `N`	`N` <=`16370` `16370` < `N`
`Floating Point Single`	`FLOAT(24)`	n/a	-
`Floating Point Double`	`FLOAT(53)`	n/a	-
`Decimal (P, S)`	`NUMBER(P,S)`	n/a	-
`CLOB`	`LONG`	n/a	-
`BLOB`	`LONG RAW(N)`	n/a	-
`DBLOB`	`LONG`	n/a	-
`SMALLINT`	`NUMBER(5)`	n/a	-
`INTEGER`	`NUMBER(10)`	n/a	-

Performing Character String Operations
The gateway performs all character string comparisons, concatenations, and sorts using the data type of the referenced columns, and determines the validity of character string values passed by applications using the gateway.
Converting Character String Data Types
The gateway binds character string data values from host variables as fixed-length character strings.
Performing Graphic String Operations
DB2 GRAPHIC data types store only double-byte string data.
Performing Date and Time Operations
The implementation of date and time data differs significantly in IBM DRDA databases and Oracle database.
Dates
This topic describes date handling.
NLS_DATE_FORMAT Support
This table lists the four patterns that can be used for the NLS_DATE_FORMAT in ALTER SESSION SET NLS_DATE_FORMAT.
Oracle TO_DATE Function
The Oracle TO_DATE function is preprocessed in SQL INSERT, UPDATE, DELETE, and SELECT WHERE clauses. TO_DATE functions in SELECT result lists are not preprocessed.
Performing Numeric Data Type Operations
IBM versions of the DRDA server perform automatic conversions to the numeric data type of the destination column (such as integer, double-precision floating point, or decimal).
Mapping the COUNT Function
This topic describes mapping the COUNT function.
Performing Zoned Decimal Operations
A zoned decimal field is described as a packed decimal on Oracle database.

Parent topic: Developing Applications

4.10.1 Performing Character String Operations

The gateway performs all character string comparisons, concatenations, and sorts using the data type of the referenced columns, and determines the validity of character string values passed by applications using the gateway.

The gateway automatically converts character strings from one data type to another and converts between character strings and dates when needed.

Frequently, DRDA databases are designed to hold non-character binary data in character columns. Applications executed on DRDA systems can generally store and retrieve data as though it contained character data. However, when an application accessing this data runs in an environment that uses a different character set, inaccurate data may be returned.

With the gateway running on the host, character data retrieved from a DB2 UDB for iSeries or DB2 UDB for z/OS host is translated from EBCDIC to ASCII. When character data is sent to DB2 UDB for iSeries or DB2 UDB for z/OS from the host, ASCII data is translated to EBCDIC. When the characters are binary data in a character column, this translation causes the application to receive incorrect information or errors. To resolve these errors, character columns on DB2 UDB for iSeries or DB2 UDB for z/OS that hold non-character data must be created with the FOR BIT DATA option. In the application, the character columns holding non-character data should be processed using the Oracle data types RAW and LONG RAW. The DESCRIBE information for a character column defined with FOR BIT DATA on the host always indicates RAW or LONG RAW.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.2 Converting Character String Data Types

The gateway binds character string data values from host variables as fixed-length character strings.

The bind length is the length of the character string data value. The gateway performs this conversion on every bind.

The DRDA VARCHAR data type can be between 1 and 32767 characters in length if the Oracle database is configured to use maximum VARCHAR2 size of 32767. Otherwise, the limit is 4000. If the DRDA VARCHAR data type is greater than the Oracle configured VARCHAR2 limit size, then it is converted to an Oracle LONG data type.

The DB2 VARCHAR data type can be no longer than 32767 bytes, which is much shorter than the maximum size for the Oracle LONG data type. If you define an Oracle LONG data type larger than 32767 bytes in length, then you receive an error message when it is mapped to the DB2 VARCHAR data type.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.3 Performing Graphic String Operations

DB2 GRAPHIC data types store only double-byte string data.

Sizes for DB2 GRAPHIC data types typically have maximum sizes that are half that of their character counterparts. For example, the maximum size of a CHAR may be 255 characters, whereas the maximum size of a GRAPHIC may be 127 characters.

Oracle database does not have a direct matching data type, and the gateway therefore converts between Oracle character data types to DB2 Graphic data types. Oracle database character data types may contain single, mixed, or double-byte character data. The gateway converts the string data into appropriate double-byte-only format depending upon whether the target DB2 column is a Graphic type and whether gateway initialization parameters are set to perform this conversion. For more configuration information, refer to Initialization Parameters.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.4 Performing Date and Time Operations

The implementation of date and time data differs significantly in IBM DRDA databases and Oracle database.

Oracle database has a single date data type, DATE, which can contain both calendar date and time of day information.

IBM DRDA databases support the following three distinct date and time data types:

DATE is the calendar date only.

TIME is the time of day only.

TIMESTAMP is a numerical value combining calendar date and time of day with microsecond resolution in the internal format of the IBM DRDA database.

Processing TIME and TIMESTAMP Data
There is no built-in mechanism that translates the IBM TIME and TIMESTAMP data to Oracle DATE data.
Processing DATE Data
Oracle and IBM DATE data types are mapped to each other.
Performing Date Arithmetic
This topic describes using date arithmetic with the Oracle Database Gateway for DRDA.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.4.1 Processing TIME and TIMESTAMP Data

There is no built-in mechanism that translates the IBM TIME and TIMESTAMP data to Oracle DATE data.

An application must process TIME data types to the Oracle CHAR format with a length of eight bytes. An application must process the TIMESTAMP data type in the Oracle CHAR format with a length of 26 bytes.

An application reads TIME and TIMESTAMP functions as character strings and converts or subsets portions of the string to perform numerical operations. TIME and TIMESTAMP values can be sent to a DRDA server as character literals or bind variables of the appropriate length and format.

Parent topic: Performing Date and Time Operations

4.10.4.2 Processing DATE Data

Oracle and IBM DATE data types are mapped to each other.

If an IBM DATE is queried, then it is converted to an Oracle DATE with a zero (midnight) time of day. If an Oracle DATE is processed against an IBM DATE column, then the date value is converted to the IBM DATE format, and any time value is discarded.

Character representations of dates are different in Oracle format and IBM DRDA format. When an Oracle application SQL statement contains a date literal, or conveys a date using a character bind variable, the gateway must convert the date to an IBM DRDA compatible format.

The gateway does not automatically recognize when a character value is being processed against an IBM DATE column. Applications are required to distinguish character date values by enclosing them with Oracle TO_DATE function notation. For example, if EMP is a synonym or view that accesses data on an IBM DRDA database, then you should not use the following SQL statement:

SELECT * FROM EMP WHERE HIREDATE = '03-MAR-81'

You should use the following:

SELECT * FROM EMP WHERE HIREDATE = TO_DATE('03-MAR-81')

In a programmatic interface program that uses a character bind variable for the qualifying date value, you must use this SQL statement:

SELECT * FROM EMP WHERE HIREDATE = TO_DATE(:1)

The above SQL notation does not affect SQL statement semantics when the statement is executed against an Oracle database table. The statement remains portable across Oracle and IBM DRDA-accessed data stores.

Any date literal other than insert value is checked to match the Oracle NLS_DATE_FORMAT before sending to DB2 for processing. TG4DB2 v10.2 does not check to match the NLS_DATE_FORMAT format. If such compatibility is desired, then you need to specify NODATECHK/ON value as part of HS_FDS_CAPABILITY parameter. You can then use any DB2 acceptable date formats:

YYYY-MM-DD (ISO/JIS)
DD.MM.YYYY (European)
MM/DD/YYYY (USA)

For example:

SELECT * FROM EMP WHERE HIREDATE = '1981-03-03'

The TO_DATE requirement also does not pertain to input bind variables that are in Oracle date 7-byte binary format. The gateway recognizes such values as dates. For DB2 UDB for z/OS, if you install the gateway supplied DATE EXIT, then you can also use two additional Oracle date formats: DD-MON-RR and DD-MON-YYYY

Parent topic: Performing Date and Time Operations

4.10.4.3 Performing Date Arithmetic

This topic describes using date arithmetic with the Oracle Database Gateway for DRDA.

The following forms of SQL expression generally do not work correctly with the gateway:

date + number 
number + date 
date - number 
date1 - date2

The date and number addition and subtraction (date + number,number + date,date - number) forms are sent through to the DRDA server, where they are rejected. The supported servers do not permit number addition or subtraction with dates.

Because of differing interpretations of date subtraction in the supported servers, subtracting two dates (date1 - date2) gives results that vary by server.

Note:

Avoid date arithmetic expressions in all gateway SQL until date arithmetic problems are resolved.

Parent topic: Performing Date and Time Operations

4.10.5 Dates

This topic describes date handling.

Date handling has two categories:

Two-digit year dates, which are treated as occurring 50 years before or 50 years after the year 2000.
Four-digit year dates, which are not ambiguous with regard to the year 2000.

Use one of the following methods to enter twenty-first century dates:

The TO_DATE function
Use any date format including a four-character year field. Refer to the Oracle Database SQL Language Reference for the available date format string options.

For example, TO_DATE('2008-07-23', 'YYYY-MM-DD') can be used in any SELECT, INSERT, UPDATE, or DELETE statement.
The NLS_DATE_FORMAT parameter
ALTER SESSION SET NLS_DATE_FORMAT should be used to set the date format used in SQL.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.6 NLS_DATE_FORMAT Support

This table lists the four patterns that can be used for the NLS_DATE_FORMAT in ALTER SESSION SET NLS_DATE_FORMAT.

For example:

DB2 Date Format	Pattern	Example
EUR	DD.MM.YYYY	30.10.1994
ISO	YYYY-MM-DD	1994-10-30
JIS	YYYY-MM-DD	1994-10-30
USA	MM/DD/YYYY	10/30/1994

The Oracle database default format of 'DD-MON-YY' is not permitted with DB2.

The following example demonstrates how to enter and select date values in the twenty-first century:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
INSERT INTO EMP (HIREDATE) VALUES ('2008-07-23');
SELECT * FROM EMP WHERE HIREDATE = '2008-07-23';
UPDATE EMP SET HIREDATE = '2008-07-24'
   WHERE HIREDATE = '2008-07-23';
DELETE FROM EMP WHERE HIREDATE = '2008-07-24';

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.7 Oracle TO_DATE Function

The Oracle TO_DATE function is preprocessed in SQL INSERT, UPDATE, DELETE, and SELECT WHERE clauses. TO_DATE functions in SELECT result lists are not preprocessed.

The TO_DATE function is often needed to provide values to update or compare with date columns. Therefore, the gateway replaces the information included in the TO_DATE clause with an acceptable value before the SQL statement is sent to DB2.

Except for the SELECT result list, all TO_DATE functions are preprocessed and turned into values that are the result of the TO_DATE function. Only TO_DATE(literal) or TO_DATE(:bind_variable) is permitted. Except in SELECT result lists, the TO_DATE(column_name) function format is not supported.

The preprocessing of the Oracle TO_DATE functions into simple values is useful in an INSERT VALUES clause because DB2 does not allow functions in the VALUES clause. In this case, DB2 receives a simple value in the VALUES list. All forms of the TO_DATE function (with one, two, or three operands) are supported.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.8 Performing Numeric Data Type Operations

IBM versions of the DRDA server perform automatic conversions to the numeric data type of the destination column (such as integer, double-precision floating point, or decimal).

The user has no control over the data type conversion, and this conversion can be independent of the data type of the destination column in the database.

For example, if PRICE is an integer column of the PRODUCT table in an IBM DRDA database, then the update shown in the following example inaccurately sets the price of an ice cream cone to $1.00 because the IBM DRDA server automatically converts a floating point to an integer:

UPDATE PRODUCT 
SET PRICE = 1.50 
WHERE PRODUCT_NAME = 'ICE CREAM CONE    ';

Because PRICE is an integer, the IBM DRDA server automatically converts the decimal data value of 1.50 to 1.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.9 Mapping the COUNT Function

This topic describes mapping the COUNT function.

Oracle database supports the following four operands for the COUNT function:

COUNT(*)
COUNT(DISTINCT colname)
COUNT(ALL colname)
COUNT(colname)

Some DRDA servers do not support all forms of COUNT, specifically COUNT(colname) and COUNT(ALL colname). In those cases the COUNT function and its arguments are translated into COUNT(*). This may not yield the desired results, especially if the column being counted contains NULLs.

For those DRDA servers that do not support the above forms, it may be possible to achieve equivalent functionality by adding a WHERE clause. For example,

SELECT COUNT(colname) FROM table@dblink WHERE colname IS NOT NULL

SELECT COUNT(ALL colname) FROM table@dblink WHERE colname IS NOT NULL

You can also use native semantics to indicate support for all COUNT functions with the following parameter in your gateway initialization file:

HS_FDS_CAPABILITY=(COUNTCOL=YES)

Refer to SQL Limitations for known DRDA servers that do not support all forms of COUNT.

Parent topic: DRDA Data Type to Oracle Data Type Conversion

4.10.10 Performing Zoned Decimal Operations

A zoned decimal field is described as a packed decimal on Oracle database.

However, an Oracle application such as a Pro*C program can insert into a zoned decimal column using any supported Oracle numeric data type. The gateway converts this number into the most suitable data type. Data can be fetched from a DRDA database into any Oracle data type, provided that it does not result in a loss of information.

Parent topic: DRDA Data Type to Oracle Data Type Conversion