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.

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.

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.

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.

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.

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

For example:

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';

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.

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.

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 

or

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.

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.