| Oracle® Transparent Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-01 |
|
|
View PDF |
| Oracle® Transparent Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-01 |
|
|
View PDF |
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 Transparent 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 Transparent Gateway for DB2/400 for this current release:
New DB2 Data Types Supported
The DB2/400 large-object data types, namely binary large objects (BLOBs), character large objects (CLOBs), and graphic large objects (DBCLOBs) are now supported. However, these data types can only be used in SELECT statements. At the current time, data cannot be INSERTed 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.
Savepoints Supported
Intermediate savepoints can now be set. Data can be rolled back to these savepoints.
Bugs fixed in this release of the Oracle Transparent Gateway for DB2/400 are as follows:
Bug 4959570: const CHAR fields in DD Views sometimes coming back as RAWs
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 Transparent 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 Transparent 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 server. 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 INSERTed 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 Transparent 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 server 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.
