2.5 Known Restrictions

This topic describes known restrictions with Oracle Database Gateway for DRDA.

The following restrictions are known to exist for the products in the 12c Release 2 (12.2). Restrictions are not scheduled to change in future releases. Refer to Developing Applications, for information or limitations when developing your applications.

• DB2 Considerations
  These topics describe that DB2 considerations that exist in the 12c Release 2 (12.2).
• SQL Limitations
  These topics describe the SQL limitations for Oracle Database Gateway for DRDA.

2.5.1 DB2 Considerations

These topics describe that DB2 considerations that exist in the 12c Release 2 (12.2).

• DD Basic Tables and Views
  The owner of DD basic tables and views is OTGDB2. This cannot be changed.
• SUBSTR Function Post-Processed
  The SUBSTR function can be used with the Oracle database in ways that are not compatible with a DRDA server, such as DB2 UDB for z/OS.
• Data Type Limitations
  Data type limitations with Oracle Database Gateway for DRDA.
• Null Values and Stored Procedures
  Null values are not passed into, or returned from, calls to stored procedures through the gateway.
• String Concatenation of Numbers
  DB2 Universal Database does not support string concatenation of numbers.
• GLOBAL_NAMES Initialization Parameter
  If GLOBAL_NAMES is set to TRUE in the Oracle database INIT.ORA file, then in order to be able to connect to the gateway, you must specify the Heterogeneous Services (HS) initialization parameter, HS_DB_DOMAIN, in the Gateway Initialization Parameter file to match the value of the DB_DOMAIN parameter of the Oracle database.
• DRDA Package and DB2 Considerations
  The gateway utilizes a package for statement execution. This package is implicitly bound the first time the gateway connects to the target DB2 system.
• Date Arithmetic
  DB2 does not allow number addition or subtraction with date data types.
• Row Length Limitation
  Because of a restriction of the DRDA architecture, rows with aggregate length exceeding 32 KB in DRDA representation cannot be stored or retrieved.
• LONG Data Type in SQL*Plus
  SQL*Plus cannot fetch columns with the LONG data type from the Oracle Database Gateway for DRDA.
• Stored Procedures and Transaction Integrity
  IBM DB2 has introduced a feature called Commit on Return for stored procedures.

2.5.1.1 DD Basic Tables and Views

The owner of DD basic tables and views is OTGDB2. This cannot be changed.

2.5.1.2 SUBSTR Function Post-Processed

The SUBSTR function can be used with the Oracle database in ways that are not compatible with a DRDA server, such as DB2 UDB for z/OS.

Therefore, the SUBSTR function is post-processed. However, it is possible to allow the server to process it natively using the Native Semantics feature. Refer to Developing Applications, for details.

2.5.1.3 Data Type Limitations

Data type limitations with Oracle Database Gateway for DRDA.

Refer to DRDA Data Type to Oracle Data type Conversion for detailed information about data types. 

2.5.1.4 Null Values and Stored Procedures

Null values are not passed into, or returned from, calls to stored procedures through the gateway.

2.5.1.5 String Concatenation of Numbers

DB2 Universal Database does not support string concatenation of numbers.

For example, the following is not allowed:

SELECT 2||2 FROM table@dblink

2.5.1.6 GLOBAL_NAMES Initialization Parameter

If GLOBAL_NAMES is set to TRUE in the Oracle database INIT.ORA file, then in order to be able to connect to the gateway, you must specify the Heterogeneous Services (HS) initialization parameter, HS_DB_DOMAIN, in the Gateway Initialization Parameter file to match the value of the DB_DOMAIN parameter of the Oracle database.

Refer to Oracle Database Gateway Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), Oracle Solaris on x86-64 (64-Bit) and HP-UX Itanium or Oracle Database Gateway Installation and Configuration Guide for Microsoft Windows, depending on your platform, for more information.

2.5.1.7 DRDA Package and DB2 Considerations

The gateway utilizes a package for statement execution. This package is implicitly bound the first time the gateway connects to the target DB2 system.

Ensure that the user ID connecting to the DB2 system has the necessary privileges to bind a package. Refer to Oracle Database Gateway Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), Oracle Solaris on x86-64 (64-Bit) and HP-UX Itanium or Oracle Database Gateway Installation and Configuration Guide for Microsoft Windows, depending on your platform, for more information.

2.5.1.8 Date Arithmetic

DB2 does not allow number addition or subtraction with date data types.

In general, the following types of SQL expression forms do not work correctly with the gateway because of DB2 limitations:

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 DB2 where they are rejected.

Also, DB2 does not perform date subtraction consistently. When you subtract two dates (date1 - date2), differing interpretations of date subtraction in DB2 cause the results to vary by server.

Note:

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

2.5.1.9 Row Length Limitation

Because of a restriction of the DRDA architecture, rows with aggregate length exceeding 32 KB in DRDA representation cannot be stored or retrieved.

2.5.1.10 LONG Data Type in SQL*Plus

SQL*Plus cannot fetch columns with the LONG data type from the Oracle Database Gateway for DRDA.

2.5.1.11 Stored Procedures and Transaction Integrity

IBM DB2 has introduced a feature called Commit on Return for stored procedures.

This feature allows DB2 to perform an automatic commit after a stored procedure runs successfully. This feature is enabled when the procedure is created. To ensure data integrity, the Oracle Database Gateway for DRDA does not support this feature in a heterogeneous environment. When attempting to call a stored procedure that has this feature enabled, through the gateway, the gateway will return an error, ORA-28526 or PLS-00201 (identifier must be declared).

2.5.2 SQL Limitations

These topics describe the SQL limitations for Oracle Database Gateway for DRDA.

• Oracle ROWID Column
  The DB2 ROWID column is not compatible with the Oracle ROWID column.
• Oracle Bind Variables
  Oracle bind variables become SQL parameter markers when used with the gateway.
• CONNECT BY Is Not Supported
  Oracle Database Gateway for DRDA does not support CONNECT BY in SELECT statements.

2.5.2.1 Oracle ROWID Column

The DB2 ROWID column is not compatible with the Oracle ROWID column.

Because the ROWID column is not supported, the following restrictions apply:

• UPDATE and DELETE are not supported with the WHERE CURRENT OF CURSOR clause. To update or delete a specific row through the gateway, a condition style WHERE clause must be used. (Bug No. 205538)

  When UPDATE and DELETE statements are used in precompiler and PL/SQL programs, they rely internally on the Oracle ROWID function.

• Snapshots between Oracle database and DB2 are not supported.

  Snapshots rely internally on the Oracle ROWID column.

2.5.2.2 Oracle Bind Variables

Oracle bind variables become SQL parameter markers when used with the gateway.

Therefore, the bind variables are subject to the same restrictions as SQL parameter markers.

For example, the following statements are not allowed:

WHERE :x IS NULL 
WHERE :x = :y 

2.5.2.3 CONNECT BY Is Not Supported

Oracle Database Gateway for DRDA does not support CONNECT BY in SELECT statements.