This chapter describes the changes and corrected problems in this release. It contains the following sections:
The production components that are included on the product CD-ROM are:
Oracle Database Gateway for DB2/400, 10g Release 2 (10.2.0.1.0)
Oracle Net, Release 10.2.0.1.0
The following changes and enhancements have been made to the Oracle Database Gateway for DB2/400 for this current release:
The DB2/400 large-object data types, namely binary large objects (BLOB
s), character large objects (CLOB
s), and graphic large objects (DBCLOB
s) are now supported. However, these data types can only be used in SELECT
statements. At the current time, data cannot be INSERT
ed into columns with these data types. In addition the previous support in 10.2.0.1.0 for large objects via the use of locators is no longer available.
DB2 stored functions (UDFs) Supported
DB2/400 User Defined Functions are now supported. DB2/400 UDFs must be made known to Oracle before their use, however. The IFS file /home/myinst/rdbms/admin/hs_call_names.ora
contains a line for each UDF.
Intermediate savepoints can now be set. Data can be rolled back to these savepoints.
Bugs fixed in this release of the Oracle Database Gateway for DB2/400 are as follows:
Bug 4959570: const CHAR
fields in DD Views sometimes coming back as RAW
s
Bug 4865825: Gateway listener hangs
Bug 4566044: SQL0804
when calling DB2/400 stored procedure with DECIMAL IN
parameter
Bug 4473636: NLS_LANG CZECH.CZECH REPUBLIC.EE8EBCDIC870
not accepted by GTW
Bug 4414487: New DataDictionary Definitions for Streams replication (to accompany fix for generic bug 3653933.
Bug 4338891: Column lengths get increased three times and padded with blanks in Oracle UTF8 DB
Bug 4333999: CRTORAGTWI
fails with CPFA0A9
copying timezone.dat
Bug number 4307761: Dead connection detection is not working when client connection is broken
Bug number 4093610: CHAR data padded with multibyte blank
Bug number 4085764: MCH0601 F/QC2STRMI/STRSTR/7
T/HOADB2/HOAParS/26
during INSERT
Bug number 4019083: MCH0601
: space offset &2 or &9 is outside current limit for object.
Bug number 3985615: ORA-0004
homtiap_initAgentProcess returns non-zero return code 1804
for CCSID 930
Bug number 3975301: SQL0901
on second and subsequent calls to stored procedure with no parameters (check IBM APAR SE18413)
Bug number 3907455: ORA-01017
on logon under V5R3M0
Bug number 3803899: Problems when cloning a new instance or when running a cloned instance.
Bug number 3755039: After upgrading to OS/400 V5R3, TG4DB2400 returns ORA-01017
.
Bug number 3699834: When duplicating data via SQL*Plus COPY
command, the length of copied data is incorrect.
Bug number 3695959: GRAPHIC(127)
in DB2 is incorrectly mapped to VARCHAR2
in ORACLE DB
Bug number 3693493: "CREATE VIEW AS SELECT ...
" incorrectly changed the length of columns.
Bug number 3629826: CCSID 930/5026
Gateway trace not displayed in WRKTRCLOG
The problems documented in the following section are specific to the Oracle Database Gateway for DB2/400 and are known to exist in this release of the product. These problems are currently being addressed by Oracle. Refer to the respective bugs for the current status of each problem. If you have any questions or concerns about these problems, then please contact Oracle Support Services.
A current list of problems is available online. Contact your local Oracle office for information about accessing this online information.
The restrictions documented in this section are known to exist for the products in this release of the Oracle Database Gateway for DB2/400. Refer to Chapter 9, "Developing Applications" for information about limitations when developing your applications.
The following sections describes general restrictions when using the gateway.
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/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.
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 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.
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.
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 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.
You must use the J option on the TO_DATE
and TO_CHAR
functions to receive valid Julian dates.
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.
Caution — Potential Loss of Data:
Oracle strongly recommends that you do not directly use graphic constants in SQL statements. If you do use graphic constants, then you should ensure that all of the characters within a graphic constant are double-width characters.Oracle recommends that you use bind variables if you wish to use such constants.
The gateway checks the graphic constants found in INSERT
statements and can force the single-width characters to become double-width characters, if requested. With graphic data resulting from a SELECT
, the gateway can force those double-width characters back to single-width characters.
The ability to do this results in a data integrity exposure because the gateway cannot determine whether a given double-width character from a DB2/400 column was INSERT
ed as a single-width character that was forced to become a double-width character or was entered as a true double-width character.
The following sections describe restrictions related to DB2/400 components.
The owner of Data Dictionary (DD) basic tables and views is OTGDB2
. This cannot be changed.
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.
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.
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.
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.
The SQLCHECK
option must be set to NONE
when precompiling programs with Oracle Precompilers.
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".
The following sections describe restrictions when using SQL.
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
Oracle Database Gateway for DB2/400 does not support CONNECT BY
in SELECT
statements.
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.
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.