2 Release Information

SQL Development Kit, Product 5722-ST1

As described in Bug 3616425, the Gateway needs to have product 5722-ST1 (SQL Development Kit) installed on the AS/400. This is a software prerequisite if one Gateway instance is cloned from another.

Selecting CHAR Column Returns Width Doubled

Selecting CHAR/VARCHAR column returns its width doubled. To correct the problem reported in bug number 2451535, create the data area named ONLS_MINNB and set its value to 2, as in the following command:

CRTDTAARA instlib/ONLS_MINNB TYPE(*CHAR) LEN(10) value('2')

where instlib is the installation library.

Coexistence with Previous Version Gateway: Data Dictionary Tables

After the data dictionary tables are installed by a 10g Release 2 Gateway, do not reinstall the data dictionary tables from a previous version gateway, or you will not receive the benefits of the full capabilities of 10g Release 2.

Oracle Fast Refresh Snapshots

Oracle fast refresh snapshots are not supported between the gateway and the Oracle Database 10g. However, Oracle complete refresh snapshots are supported between the gateway and the Oracle Database 10g server.

Oracle SQL Command INSERT

When copying data from an Oracle Database 10g server to a DB2/400 server, the Oracle SQL command INSERT is not supported. The SQL*Plus COPY command must be used. Refer to "SQL*Plus COPY Command", for more information.

Verify the QCRTAUT Parameter Setting Before Installation

Before installing the gateway, verify that the QCRTAUT parameter is set to allow installation (not set to *EXCLUDE). Refer to "Installation Steps" for information about verifying this value.

Date Arithmetic

Date arithmetic is not supported for SQL statements. For example, the following SQL expressions do not work:

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

Refer to Chapter 9, "Developing Applications" for additional information.

Julian Dates Support

You must use the J option on the TO_DATE and TO_CHAR functions to receive valid Julian dates.

GRAPHIC Constants in SQL Commands

GRAPHIC constants in SQL statements that are valid in one character set may not be valid as G type or N type GRAPHIC constants in DB2/400. For example, graphic constants in character set KO16KCSC5601 may contain both single-width and double-width characters. GRAPHIC constants in DB2/400 can contain only double-width characters.

DD Basic Tables and Views

The owner of Data Dictionary (DD) basic tables and views is OTGDB2. This cannot be changed.

Binary Literal Notation

Oracle SQL uses hexadecimal digits surrounded by single quotation marks to express literal values being compared or inserted into columns defined as RAW. Currently, this is not converted to DB2/400 syntax (an X followed by quoted hexadecimal digits) when the SQL destination is the gateway. You must use bind variables to compare or insert into a DB2/400 server column that is defined with the FOR BIT DATA option.

Programmatic Limitations

Gateway design requires that all host variables in a SQL operation be bound before a describe function is performed. This is required because of a limitation in the Oracle Call Interface (OCI), which requires that all OCI bind calls for a given statement must be completed before an OCI describe call is made.

Columns Defined with RAW Data

When you select RAW data into character bind variables, the CHAR column must be two times the size of the RAW data. Selecting RAW data into character bind variables causes implicit RAW-to-HEX conversion. If the character bind variable column is too small, then the SELECT statement fails.

GLOBAL_NAMES Initialization Parameter

If GLOBAL_NAMES is set to TRUE in the Oracle integrating server INIT.ORA file, then, to be able to connect to the gateway, you must specify the value of the Heterogeneous Services (HS) initialization parameter, HS_DB_DOMAIN, in the "Change Oracle Gateway Initialization Parameters" panel to exactly match the value of the Oracle server DB_DOMAIN parameter. Refer to Chapter 6, "Configuring the Gateway" for more information.

Precompiler Limitations

The SQLCHECK option must be set to NONE when precompiling programs with Oracle Precompilers.

Some SQL Functions Post processed

The Oracle Database 10g server provides more functionality for some SQL functions compared to similarly named DB2/400 SQL functions. As a result, these SQL functions are not passed through from the Oracle Database 10g server to DB2/400. They are, instead, post processed. SUBSTR is an example of such a function because the semantics of the SUBSTR function in Oracle as compared to that in DB/400 show slight, but potentially problematic, differences. Some other SQL functions have different semantics in Oracle SQL and DB2/400 SQL. For details, refer to "SQL Functions" in Chapter 9, "Developing Applications".

Oracle Bind Variables

Oracle bind variables become DB2/400 SQL parameter markers when used with the gateway. The bind variables are therefore subject to the same restrictions as DB2/400 SQL parameter markers.

For example, the following statements are not allowed:

WHERE :x IS NULL
WHERE :x = :y

CONNECT BY Is Not Supported

Oracle Database Gateway for DB2/400 does not support CONNECT BY in SELECT statements.

Oracle Incremental Materialized View Refresh

Incremental Materialized View refresh is not supported between the gateway and the Oracle Database 10g server. However, Oracle complete Materialized View is supported between the gateway and the Oracle Database 10g server.

LONG Data Types

Any DB2/400 CHAR or VARCHAR column with a length greater than 4000 bytes is considered an Oracle LONG data type by the gateway.

When using a SELECT statement in SQL*Plus for any DB2/400 GRAPHIC column that is defined with a length greater than 2000 characters, or for any DB2/400 CHAR or VARCHAR column that is defined with a length greater than 4000 characters, the column will be seen by the Oracle database as a LONG column. In SQL*Plus, the default buffer length for a retrieved LONG column is 80 characters. For this reason, if you wish to retrieve a DB2/400 column that will be interpreted as an Oracle LONG column, then you must set the SQL*Plus buffer length correctly. If the buffer size is not sufficient to contain the column value, then you will receive the following error message:

ORA-28528: Heterogeneous Services datatype conversion error

To work around this problem, reset the size of the buffer using the SET LONG command in SQL*PLUS or Server Manager.

In addition, if you use a program to retrieve a LONG column on the Oracle client, then you must retrieve the entire LONG column in one call to the Oracle client. You cannot retrieve the LONG column in sections through the gateway.