Skip Headers
Oracle® Transparent Gateway for DRDA Installation and User's Guide
10g Release 2 (10.2) for Microsoft Windows

Part Number B16218-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

12 Developing Applications

The Oracle Transparent Gateway for DRDA enables applications written for the Oracle server to access tables in a DRDA database. This access can be virtually transparent by using synonyms or views of the DRDA tables accessed by a database link. However, there are fundamental SQL, data type, and semantic differences between the Oracle server and DRDA databases. Read this chapter to learn about these differences.

This chapter provides information that is specific to this release of the Oracle Transparent Gateway for DRDA, including the following sections:

Gateway Appearance to Application Programs

An application written to access information in a DRDA database interfaces with an Oracle integrating server. When developing applications, keep the following information in mind:

Fetch Reblocking

The gateway supports fetch reblocking with the HS_RPC_FETCH_REBLOCKING parameter.

When the value of this parameter is set to ON (the default), the array size for SELECT statements is determined by the HS_RPC_FETCH_SIZE value. The HS_RPC_FETCH_SIZE parameter defines the number of bytes sent with each buffer from the gateway to the Oracle Database 10g server. The buffer might contain one or more qualified rows from the DRDA Server. This feature can provide significant performance enhancements, depending on your application design, installation type, and workload.

The array size between the client and the Oracle Database 10g server is still determined by the Oracle application.

Refer to Chapter 10, "Configuring the Gateway" for more information.

Using Oracle Stored Procedures with the Gateway

The gateway stored procedure support is an extension of Oracle stored procedures. An Oracle stored procedure is a schema object that logically groups a set of SQL and other PL/SQL programming language statements together to perform a specific task. Oracle stored procedures are stored in the database for continued use. Applications use standard Oracle PL/SQL to call stored procedures.

Oracle stored procedures can be located in a local instance of the Oracle Database 10g server and a remote instance. The following example shows two stored procedures, oraproc1 and oraproc2. While oraproc1 is a procedure stored in the ORA1 Oracle instance, oraproc2 is a procedure stored in the ORA2 Oracle instance.

Figure 12-1 Calling Oracle Stored Procedures in a Distributed Oracle Environment

Description of Figure 12-1 follows
Description of "Figure 12-1 Calling Oracle Stored Procedures in a Distributed Oracle Environment"

To maintain location transparency in the application, a synonym can be created:

CREATE SYNONYM oraproc2 FOR oraproc2@ora2;

After this synonym is created, the application no longer needs to use the database link specification to call the stored procedure at the remote Oracle instance.

In Figure 12-1, the second statement in oraproc1 is used to access a table in the ORA2 instance. In the same way, Oracle stored procedures can be used to access DB2 tables through the gateway.

Using DRDA Server Stored Procedures with the Gateway

The procedural feature of the gateway enables calling of native DRDA Server stored procedures. In other words, the stored procedure is no longer defined in the Oracle Database 10g server, but instead, is defined to the DRDA Server (for example, DB2/OS390). Again, standard Oracle PL/SQL is used by the Oracle application to run the stored procedure.

The gateway does not require special definitions to call the DB2 stored procedure. Once the stored procedure is defined to the DRDA Server (e.g., DB2/OS390), the gateway will be able to use the existing DRDA Server definition to run the procedure.

In Figure 12-2, an Oracle application calls the empproc stored procedure defined to the DRDA Server (for example, DB2/OS390).

Figure 12-2 Running DRDA Server Stored Procedures

Description of Figure 12-2 follows
Description of "Figure 12-2 Running DRDA Server Stored Procedures"

From the perspective of the application, running the DB2 stored procedure is no different than calling a stored procedure at a remote Oracle instance.

Oracle Application and DRDA Server Stored Procedure Completion

As an example, suppose an Oracle application attempts to call a stored procedure in a DB2/OS390 database. In order for an Oracle application to call a DB2 stored procedure, the DB2 stored procedure must first be created on the DB2 system by using the procedures documented in the IBM reference document for DB2 for OS/390 SQL.

After the stored procedure is defined to DB2, the gateway will be able to access the data using a standard PL/SQL call. For example, an employee name, JOHN SMYTHE, is passed to the DB2 stored procedure REVISE_SALARY. The DB2 stored procedure retrieves the salary value from the DB2 database to calculate a new yearly salary for JOHN SMYTHE. The revised salary returned in RESULT is used to update the EMP table of an Oracle database server:

DECLARE
  INPUT VARCHAR2(15);
  RESULT NUMBER(8,2);
BEGIN
  INPUT := 'JOHN SMYTHE';
  REVISE_SALARY@DB2(INPUT, RESULT);
  UPDATE EMP SET SAL = RESULT WHERE ENAME = INPUT;
END;

When the gateway receives a call to run a stored procedure on the DRDA Server (for example, DB2/OS390), it first does a lookup of the procedure name in the server catalog. The information that defines a stored procedure is stored in different forms on each DRDA Server. For example, DB2/OS390 V5.0 uses the table SYSIBM.SYSPROCEDURES, while DB2/OS390 V6.1 uses the tables SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS, and DB2/400 uses the tables QSYS2.SYSPROCS and QSYS2.SYSPARMS. The gateway has a list of known catalogs to search, depending on the DRDA Server being accessed.

The search order of the catalogs is dependent on whether the catalogs support location designators (such as LUNAME in SYSIBM.SYSPROCEDURES), and Authorization or Owner IDs (such as AUTHID in SYSIBM.SYSPROCEDURES or OWNER in SYSIBM.SYSROUTINES).

Some DRDA Servers enable blank or public Authorization qualifiers. If the currently connected DRDA Server supports this form of qualification, then the gateway will apply those naming rules when searching for a procedure name in the catalog.

The matching rules will first search for a Public definition, and then an owner-qualified procedure name. For more detailed information, refer to the IBM reference document for DB2 for OS/390 SQL for the underlying database of the DRDA Server.

Procedural Feature Considerations with DB2

The following are special considerations for using the procedural feature with the gateway:

  • DB2 stored procedures do not have the ability to coordinate, commit, and rollback activity on recoverable resources such as IMS or CICS transactions. Therefore, if the DB2 stored procedure calls a CICS or IMS transaction, then it is considered a separate unit of work and does not affect the completion of the stored procedure. This means that if you are running a DB2 stored procedure from an Oracle application, and if this procedure calls a CICS or IMS transaction, then the gateway cannot recover from any activity that occurred within the CICS or IMS transaction.

    For example, the CICS transaction could roll back a unit of work, but this does not prevent the gateway from committing other DB2 work contained within the DB2 stored procedure.

    Likewise, if the DB2 stored procedure updated an irrecoverable resource such as a VSAM file, then the gateway considers this activity separate from its own recoverable unit of work.

  • PL/SQL records cannot be passed as parameters when calling a DB2 stored procedure.

  • The gateway supports the SIMPLE linkage convention of DB2 stored procedures.

    The SIMPLE linkage convention means that the parameters that are passed to and from the DB2 stored procedure cannot be null.

Database Link Behavior

A connection to the gateway is established through a database link when it is first used in an Oracle session. In this context, connection refers to both the connection between the Oracle integrating server and the gateway, and to the DRDA network connection between the gateway and the target DRDA database. The connection remains established until the Oracle session ends. Another session or user can access the same database link and get a distinct connection to the gateway and DRDA database.

Connections to the DRDA database can be limited in an APPC configuration in a parallel session limit, or by other factors, such as memory, gateway parameters, or DRDA Server resources. In a TCP/IP configuration, only resource limits (such as memory) or limits on the number of connections by the DRDA Server will limit the number of connections between the gateway and the DRDA Server.

Oracle Server SQL Construct Processing

One of the most important features of the Oracle Enterprise Integration Gateways family of products is their ability to provide SQL transparency to the user and to the application programmer. Foreign SQL constructs can be categorized into four areas:

Compatible SQL Functions

The Oracle integrating server automatically forwards to the DRDA database compatible SQL functions, that is, SQL constructs with the same syntax and meaning on both the Oracle server and DRDA database. These SQL constructs are forwarded unmodified. All of the compatible functions are column functions. Functions that are not compatible are either translated to an equivalent DRDA SQL function or are compensated by the Oracle server after the data is returned from the DRDA database.

Translated SQL Functions

Translated functions have the same meaning but different names between the Oracle integrating server and the DRDA database, but all applications must use the Oracle function name. These SQL constructs that are supported with different syntax (such as different function names) by the DRDA database, are automatically translated by the Oracle server and then forwarded to the DRDA database. The Oracle integrating server, transparent to your application, changes the function name before sending it to the DRDA database.

Compensated SQL Functions

Some advanced SQL constructs that are supported by the Oracle server may not be supported in the same manner, if at all, by the DRDA database. Compensated functions are those SQL functions that are not recognized by the DRDA Server. If a SELECT statement containing one of these functions is passed from the Oracle integrating server to the gateway, then the gateway removes the function before passing the SQL statement to the DRDA Server. The gateway passes the selected DRDA database rows to the Oracle integrating server. The Oracle integrating server then applies the function.

The Oracle server can compensate for the missing or incompatible function by automatically excluding the incompatible SQL construct from the SQL request that is forwarded to the DRDA database. The Oracle server then retrieves the necessary data from the DRDA database and applies the function. This activity is known as postprocessing.

The gateway attempts to pass all SQL functions to DRDA databases. But when a DRDA database does not support a function represented in the computation, then the gateway changes that function. For example, if a program requests:

SELECT COS(X_COOR) FROM TABLE_X;

from a DB2/OS390 database, which does not support the meaning of COS, then the gateway changes the SELECT statement to:

SELECT X_COOR FROM TABLE_X;

All data in the X_COOR column of TABLE_X is passed from the DB2/OS390 database to the Oracle integrating server. After the data is moved to the Oracle integrating server, the COS function is performed.

If you are performing operations on large amounts of data stored in a DRDA database, then keep in mind that some functions require postprocessing.

Native Semantic SQL Functions

Some SQL functions that are normally compensated may also be overridden, through the Native Semantics facility. If a SQL function has been enabled for Native Semantics, then the function may be passed on to the DRDA database for processing, instead of being compensated (post-processed). Refer to Native Semantics.

DB2/OS390 SQL Compatibility

The ways that the Oracle Database server and gateway handle SQL functions for a DB2/OS390 database are shown in Table 12-1.

Table 12-1 DB2/OS390 SQL Compatibility, by Oracle SQL Function

Oracle SQL Function Compatible Translated Compensated Native Semantics Candidate

ABS

 


 


Yes

Yes

ACOS

 


 


Yes

Yes

ADD_MONTHS

 


 


Yes

 


ASCII

 


 


Yes

Yes

ASIN

 


 


Yes

Yes

ATAN

 


 


Yes

Yes

ATAN2

 


 


Yes

Yes

AVG

Yes

 


 


 


BITAND



Yes

Yes

CAST

 


 


Yes

Yes

CEIL

 


CEILING


Yes

CHARTOROWID

 


 


Yes

 


CHR

 


 


Yes

Yes

CONCAT

Yes

 


 


 


CONVERT

 


 


Yes

Yes

COS

 


 


Yes

Yes

COSH

 


 


Yes

Yes

COUNT(*)

Yes

 


 


 


COUNT (DISTINCT colname)

Yes




COUNT (ALL colname)

Yes



COUNTCOL

COUNT (column)

Yes



COUNTCOL

DECODE

 


 


Yes

Yes

DUMP

 


 


Yes

Yes

EXP

 


 


Yes

Yes

FLOOR

Yes

 



Yes

GREATEST

 


 


Yes

Yes

HEXTORAW

 


 


Yes

Yes

INITCAP

 


 


Yes

Yes

INSTR

 


 


Yes

Yes

INSTRB

 


 


Yes

Yes

LAST_DAY

 


 


Yes

 


LEAST

 


 


Yes

Yes

LENGTH

 


 


Yes

Yes

LENGTHB

 


 


Yes

Yes

LN

 


 


Yes

Yes

LOG

 


 


Yes

Yes

LOWER

 


 


Yes

Yes

LPAD

 


 


Yes

Yes

LTRIM

 


 


Yes

Yes

MAX

Yes

 


 


 


MIN

Yes

 


 


 


MOD

 


 


Yes

Yes

MONTHS_BETWEEN

 


 


Yes

 


NEW_TIME

 


 


Yes

 


NEXT_DAY

 


 


Yes

 


NLS_INITCAP

 


 


Yes

Yes

NLS_LOWER

 


 


Yes

Yes

NLS_UPPER

 


 


Yes

Yes

NLSSORT

 


 


Yes

Yes

NVL

 


VALUE

 


 


NVL2

 


 


Yes

Yes

POWER

 


 


Yes

Yes

RAWTOHEX

 


 


Yes

Yes

REPLACE

 


 


Yes

Yes

REVERSE

 


 


Yes

Yes

ROUND

 


 


Yes

Yes

ROWIDTOCHAR

 


 


Yes

 


RPAD

 


 


Yes

Yes

RTRIM

 


 


Yes

Yes

SIGN

 


 


Yes

Yes

SIN

 


 


Yes

Yes

SINH

 


 


Yes

Yes

SOUNDEX

 


 


Yes

 


SQRT

 


 


Yes

Yes

STDDEV

 


 


Yes

Yes

SUBSTR

 


 


Yes

Yes

SUBSTRB

 


 


Yes

Yes

SUM

Yes

 


 


 


SYSDATE

 


 


Yes

 


TAN

 


 


Yes

Yes

TANH

 


 


Yes

Yes

TO_CHAR

 


 


Yes

 


TO_DATE

 


 


Yes

 


TO_MULTI_BYTE

 


 


Yes

 


TO_NUMBER

 


DECIMAL

 


Yes

TO_SINGLE_BYTE

 


 


Yes

 


TRANSLATE

 


 


Yes

Yes

TRIM

 


STRIP

Yes

Yes

TRUNC

 


 


Yes

Yes

UID

 


 


Yes

 


UPPER



Yes

Yes

USER

 


 


Yes

 


USERENV

 


 


Yes

 


VARIANCE

 


 


Yes

Yes

VSIZE

 


 


Yes

Yes


DB2/Universal Database SQL Compatibility

The ways that the Oracle Database server and gateway handle SQL functions for a DB2/UDB database are shown in the following table:

Table 12-2 DB2/Universal Database SQL Compatibility, by Oracle SQL Function

Oracle SQL Function Compatible Translated Compensated Native Semantics Candidate

ABS

Yes

 


 


Yes

ACOS

 


 


Yes

Yes

ADD_MONTHS

 


 


Yes

 


ASCII

 


 


Yes

Yes

ASIN

 


 


Yes

Yes

ATAN

 


 


Yes

Yes

ATAN2

 


 


Yes

Yes

AVG

Yes

 


 


 


BITAND



Yes

Yes

CAST

 


 


Yes

Yes

CEIL


CEILING

 


Yes

CHARTOROWID

 


 


Yes

 


CHR

Yes

 


 


Yes

CONCAT

Yes

 


 


 


CONVERT

 


 


Yes

Yes

COS

Yes

 


 


Yes

COSH

 


 


Yes

Yes

COUNT(*)

Yes

 


 


 


COUNT (DISTINCT colname)

Yes




COUNT (ALL colname)

Yes



COUNTCOL

COUNT (column)

Yes



COUNTCOL

DECODE

 


 


Yes

Yes

DUMP

 


 


Yes

Yes

EXP

Yes

 


 


Yes

FLOOR

Yes

 


 


Yes

GREATEST

 


 


Yes

Yes

HEXTORAW

 


 


Yes

Yes

INITCAP

 


 


Yes

Yes

INSTR

 


 


Yes

Yes

INSTRB

 


 


Yes

Yes

LAST_DAY

 


 


Yes

 


LEAST

 


 


Yes

Yes

LENGTH

 


 


Yes

Yes

LENGTHB

 


 


Yes

Yes

LN

Yes

 


 


Yes

LOG

 


 


Yes

Yes

LOWER

 


LCASE

 


Yes

LPAD

 


 


Yes

Yes

LTRIM

 


 


Yes

Yes

MAX

Yes

 


 


 


MIN

Yes

 


 


 


MOD

Yes

 


 


Yes

MONTHS_BETWEEN

 


 


Yes

 


NEW_TIME

 


 


Yes

 


NEXT_DAY

Yes

 


Yes

 


NLS_INITCAP

 


 


Yes

Yes

NLS_LOWER

 


 


Yes

Yes

NLS_UPPER

 


 


Yes

Yes

NLSSORT

 


 


Yes

Yes

NVL

 


VALUE

 


 


NVL2

 


 


Yes

Yes

POWER

Yes

 


 


Yes

RAWTOHEX

 


 


Yes

Yes

REPLACE

Yes

 


 


Yes

REVERSE

 


 


Yes

Yes

ROUND

Yes

 


 


Yes

ROWIDTOCHAR

 


 


Yes

 


RPAD

 


 


Yes

Yes

RTRIM

 


 


Yes

Yes

SIGN

Yes

 


 


Yes

SIN

Yes

 


 


Yes

SINH

 


 


Yes

Yes

SOUNDEX

 


 


Yes

 


SQRT

Yes

 


 


Yes

STDDEV

 


 


Yes

Yes

SUBSTR

 


 


Yes

Yes

SUBSTRB

 


 


Yes

Yes

SUM

Yes

 


 


 


SYSDATE

 


 


Yes

 


TAN

Yes

 


 


Yes

TANH

 


 


Yes

Yes

TO_CHAR

 


 


Yes

 


TO_DATE

 


 


Yes

 


TO_MULTI_BYTE

 



Yes

 


TO_NUMBER

 


DECIMAL

 


Yes

TO_SINGLE_BYTE

 


 


Yes

 


TRANSLATE

 


 


Yes

Yes

TRIM

 


 


Yes

Yes

TRUNC

Yes

 


 


Yes

UID

 


 


Yes

 


UPPER

 


UCASE

 


Yes

USER

 


 


Yes

 


USERENV

 


 


Yes

 


VARIANCE

 


 


Yes

Yes

VSIZE

 


 


Yes

Yes


DB2/400 SQL Compatibility

The ways that the Oracle Database server and gateway handle SQL functions for a DB2/400 database are shown in the following table:

Table 12-3 DB2/400 SQL Compatibility, by Oracle SQL Function

Oracle SQL Function Compatible Translated Compensated Native Semantics Candidate

ABS

 


ABSVAL

 


Yes

ACOS

 


 


Yes

Yes

ADD_MONTHS

 


 


Yes

 


ASCII

 


 


Yes

Yes

ASIN

 


 


Yes

Yes

ATAN

 


 


Yes

Yes

ATAN2

 


 


Yes

Yes

AVG

Yes

 


 


 


BITAND



Yes

Yes

CAST

 


 


Yes

Yes

CEIL

 


CEILING


Yes

CHARTOROWID

 


 


Yes

 


CHR

 


 


Yes

Yes

CONCAT

Yes

 


 


 


CONVERT

 


 


Yes

Yes

COS

Yes

 


 


Yes

COSH

Yes

 


 


Yes

COUNT(*)

Yes

 


 


 


COUNT (DISTINCT colname)

Yes




COUNT (ALL colname)

Yes



COUNTCOL

COUNT (column)

Yes



COUNTCOL

DECODE

 


 


Yes

Yes

DUMP

 


 


Yes

Yes

EXP

Yes

 


 


Yes

FLOOR

Yes

 



Yes

GREATEST

 


 


Yes

Yes

HEXTORAW

 


 


Yes

Yes

INITCAP

 


 


Yes

Yes

INSTR

 


 


Yes

Yes

INSTRB

 


 


Yes

Yes

LAST_DAY

 


 


Yes

 


LEAST

 


 


Yes

Yes

LENGTH

 


 


Yes

Yes

LENGTHB

 


 


Yes

Yes

LN

Yes

 


 


Yes

LOG

 


 


Yes

Yes

LOWER

 


 


Yes

Yes

LPAD

 


 


Yes

Yes

LTRIM

 


 


Yes

Yes

MAX

Yes

 


 


 


MIN

Yes

 


 


 


MOD

 


 


Yes

Yes

MONTHS_BETWEEN

 


 


Yes

 


NEW_TIME

 


 


Yes

 


NEXT_DAX

 


 


Yes

 


NLS_INITCAP

 


 


Yes

Yes

NLS_LOWER

 


 


Yes

Yes

NLS_UPPER

 


 


Yes

Yes

NLSSORT

 


 


Yes

Yes

NVL

 


VALUE

 


 


NVL2

 


 


Yes

Yes

POWER

 


 


Yes

Yes

RAWTOHEX

 


 


Yes

Yes

REPLACE

 


 


Yes

Yes

REVERSE

 


 


Yes

Yes

ROUND

 


 


Yes

Yes

ROWIDTOCHAR

 


 


Yes

 


RPAD

 


 


Yes

Yes

RTRIM

 


 


Yes

Yes

SIGN

 


 


Yes

Yes

SIN

Yes

 


 


Yes

SINH

Yes

 


 


Yes

SOUNDEX

 


 


Yes

 


SQRT

Yes

 


 


Yes

STDDEV

Yes

 


 


Yes

SUBSTR

 


 


Yes

Yes

SUBSTRB

 


 


Yes

Yes

SUM

Yes

 


 


 


SYSDATE

 


 


Yes

 


TAN

Yes

 


 


Yes

TANH

Yes

 


 


Yes

TO_CHAR

 


 


Yes

 


TO_DATE

 


 


Yes

 


TO_MULTI_BYTE

 


 


Yes

 


TO_NUMBER

 


 DECIMAL


Yes

TO_SINGLE_BYTE

 


 


Yes

 


TRANSLATE

 


 


Yes

Yes

TRIM

 


 


Yes

Yes

TRUNC

 


 


Yes

Yes

UID

 


 


Yes

 


UPPER

 


TRANSLATE

 


Yes

USER

 


 


Yes

 


USERENV

 


 


Yes

 


VARIANCE

 


VAR

 


Yes

VSIZE

 


 


Yes

Yes


DB2/VM SQL Compatibility

The ways that the Oracle Database server and gateway handle SQL functions for a DB2/VM database are shown in the following table:

Table 12-4 DB2/VM SQL Compatibility, by Oracle SQL Function

Oracle SQL Function Compatible Translated Compensated Native Semantics Candidate

ABS

 


 


Yes

Yes

ACOS

 


 


Yes

Yes

ADD_MONTHS

 


 


Yes

 


ASCII

 


 


Yes

Yes

ASIN

 


 


Yes

Yes

ATAN

 


 


Yes

Yes

ATAN2

 


 


Yes

Yes

AVG

Yes

 


 


 


BITAND



Yes

Yes

CAST

 


 


Yes

Yes

CEIL

 


 



Yes

CHARTOROWID

 


 


Yes

 


CHR

 


 


Yes

Yes

CONCAT

Yes

 


 


 


CONVERT

 


 


Yes

Yes

COS

 


 


Yes

Yes

COSH

 


 


Yes

Yes

COUNT(*)

Yes

 


 


 


COUNT (DISTINCT colname)

Yes




COUNT (ALL colname)



Yes


COUNT (COLUMN)



Yes


DECODE

 


 


Yes

Yes

DUMP

 


 


Yes

Yes

EXP

 


 


Yes

Yes

FLOOR

 


 



Yes

GREATEST

 


 


Yes

Yes

HEXTORAW

 


 


Yes

Yes

INITCAP

 


 


Yes

Yes

INSTR

 


 


Yes

Yes

INSTRB

 


 


Yes

Yes

LAST_DAY

 


 


Yes

 


LEAST

 


 


Yes

Yes

LENGTH

 


 


Yes

Yes

LENGTHB

 


 


Yes

Yes

LN

 


 


Yes

Yes

LOG

 


 


Yes

Yes

LOWER

 


 


Yes

Yes

LPAD

 


 


Yes

Yes

LTRIM

 


 


Yes

Yes

MAX

Yes

 


 


 


MIN

Yes

 


 


 


MOD

 


 


Yes

Yes

MONTHS_BETWEEN

 


 


Yes

 


NEW_TIME

 


 


Yes

 


NEXT_DAY

 


 


Yes

 


NLS_INITCAP

 


 


Yes

Yes

NLS_LOWER

 


 


Yes

Yes

NLS_UPPER

 


 


Yes

Yes

NLSSORT

 


 


Yes

Yes

NVL

 


VALUE

 


 


NVL2

 


 


Yes

Yes

POWER

 


 


Yes

Yes

RAWTOHEX

 


 


Yes

Yes

REPLACE

 


 


Yes

Yes

REVERSE

 


 


Yes

Yes

ROUND

 


 


Yes

Yes

ROWIDTOCHAR

 


 


Yes

 


RPAD

 


 


Yes

Yes

RTRIM

 


 


Yes

Yes

SIGN

 


 


Yes

Yes

SIN

 


 


Yes

Yes

SINH

 


 


Yes

Yes

SOUNDEX

 


 


Yes

 


SQRT

 


 


Yes

Yes

STDDEV

 


 


Yes

Yes

SUBSTR

 


 


Yes

Yes

SUBSTRB

 


 


Yes

Yes

SUM

Yes

 


 


 


SYSDATE

 


 


Yes

 


TAN

 


 


Yes

Yes

TANH

 


 


Yes

Yes

TO_CHAR

 


 


Yes

 


TO_DATE

 


 


Yes

 


TO_MULTI_BYTE

 


 


Yes

 


TO_NUMBER

 


 


Yes

Yes

TO_SINGLE_BYTE

 


 


Yes

 


TRANSLATE

 


 


Yes

Yes

TRIM

 


 


Yes

Yes

TRUNC

 


 


Yes

Yes

UID

 


 


Yes

 


UPPER

 


 


Yes

Yes

USER

 


 


Yes

 


USERENV

 


 


Yes

 


VARIANCE

 


 


Yes

Yes

VSIZE

 


 


Yes

Yes


Native Semantics

Because some of the advanced SQL constructs that are supported by the Oracle server may not be supported in the same manner (if at all) by the DRDA database, the Oracle server compensates for the missing or incompatible functionality by postprocessing the DRDA database data with Oracle (refer to the previous section, "Oracle Server SQL Construct Processing",). This feature provides maximum transparency, but may impact performance. In addition, new versions of a particular DRDA database may implement previously unsupported functions or capabilities, or they may change the supported semantics in such a manner as to make them more compatible with Oracle functions.

Some of the DRDA Servers also provide support for user-defined functions. The user may choose to implement Oracle functions natively, thus enabling the DRDA Server to pass the function on to the underlying database implementation (for example, DB2). Native Semantics provides a method of enabling specific capabilities to be processed natively by the DRDA Server.

Various considerations must be taken into account when enabling the Native Semantic feature of a particular function, because Native Semantics has advantages and disadvantages. For example, a trade-off typically exists between transparency and performance. One such consideration is transparency of data coercion. Oracle provides coercion (implicit data conversion) for many SQL functions. This means that if the supplied value for a particular function is not correct, then Oracle will coerce the value to the correct type before processing it. However, with the Native Semantic feature enabled, the value (exactly as provided) will be passed through to the DRDA Server for processing. In many cases, the DRDA Server will not be able to coerce the value to the correct type and will therefore generate an error.

Another consideration involves the compatibility of parameters to a particular SQL function. For instance, the Oracle implementation of SUBSTR permits negative values for the string index, whereas most DRDA Server implementations of SUBSTR do not permit negative values for the string index. However, if the application is implemented to call SUBSTR in a manner that is compatible with the DRDA Server, then the function will act the same in either Oracle or the DRDA Server.

Another consideration is that the processing of a function at the DRDA Server may not be desirable due to resource constraints in that environment. Refer to the DRDA_CAPABILITY parameter of Appendix C for details on enabling or disabling these capabilities. Refer as well, to the SQL*Plus User's Guide and Reference and Oracle Database PL/SQL User's Guide and Reference for the Oracle format of the following capabilities:

SQL Functions That Can Be Enabled

The following list contains SQL functions that are not enabled by default. They can be enabled as an option:

Table 12-5 List of SQL Functions That Can Be Enabled

Functions

ABS

ACOS

ASCII

ASIN

ATAN

ATAN2

BITAND

CAST

CEIL

CHR

CONVERT

COS

COSH

COUNTCOL

DECODE

DUMP

EXP

FLOOR

GREATEST

HEXOTRAW

INITCAP

INSTR

INSTRB

LEAST

LENGTH

LENGTHB

LN

LOG

LOWER

LPAD

LTRIM

MOD

NLS_INITCAP

NLS_UPPER

NLS_LOWER

NLSSORT

NVL2

POWER

RAWTOHEX

REPLACE

REVERSE

ROUND

RPAD

RTRIM

SIGN

SIN

SINH

SQRT

STDDEV

SUBSTR

SUBSTRB

TAN

TANH

TO_NUMBER

TRANSLATE

TRIM

TRUNC

UPPER

VARIANCE

VSIZE


SQL Functions That Can Be Disabled

The following SQL functions are enabled (ON) by default. They can be disabled as an option:

  • COUNTCOL, to control SQL COUNT(column) function

  • GROUPBY, to control SQL GROUP BY clause

  • HAVING, to control SQL HAVING clause

  • ORDERBY, to control SQL ORDER BY clause

  • WHERE, to control SQL WHERE clause

ORDERBY controls sort order, which may differ at various sort locations. For example, with ORDERBY ON, a DB2 sort would be based on EBCDIC sorting order, whereas with ORDERBY OFF, an Oracle sort would be based on ASCII sorting order.

Three other functions, GROUPBY, HAVING, and WHERE, can take additional processing time. If you need to minimize the use of expensive resources, then you should choose the settings of these functions so that the processing is performed on the cheaper resource.

SQL Set Operators and Clauses

The clauses WHERE and HAVING are compatible for all versions of the DRDA Server, meaning that they are passed unchanged to the DRDA Server for processing. Whether clauses GROUP BY and ORDER BY are passed to the DRDA Server or compensated by the Oracle server is determined by the Native Semantics parameters (refer to the previous section).

The set operators UNION and UNION ALL are compatible for all versions of the DRDA Server, meaning that they are passed unchanged to the DRDA Server for processing. The set operators INTERSECT and MINUS are compensated on all versions of the DRDA Server except DB2/UDB. For DB2/UDB, INTERSECT is compatible, and MINUS is translated to EXCEPT.

DRDA Data Type to Oracle Data Type Conversion

To move data between applications and the database, the gateway binds data values from a host variable or literal of a specific data type to a data type interpretable by the database. Therefore, the gateway maps values from any version of the DRDA Server into correct Oracle data types before passing these values back to the application or Oracle tool.

The table lists the data type mapping and restrictions. The DRDA Server data types listed below are general. Refer to documentation for your DRDA database for restrictions on data type size and value limitations.

Table 12-6 Data Type Mapping and Restrictions

DRDA Server
Oracle External Criteria

CHAR(N)

CHAR(N)

N < =  255

VARCHAR (N)

VARCHAR2(N)

LONG

N < = 2000

2000 < N < = 32740

LONG VARCHAR(N)

VARCHAR2(N)

N < = 2000

LONG VARCHAR(N)

LONG

2000 < N < = 32740

CHAR(N) FOR BIT DATA

RAW(N)

N < = 255

VARCHAR(N) FOR BIT DATA

RAW(N)

< =  N < =  255

VARCHAR(N) FOR BIT DATA

LONG RAW(N)

255 < N < =  32740

LONG VARCHAR(N) FOR BIT DATA

RAW(N

< =  N < =  255

LONG VARCHAR(N) FOR BIT DATA

LONG RAW(N)

255 < N < =  32740

DATE

DATE

Refer to "Performing Date and Time Operations"

TIME

CHAR(8)

Refer to "Performing Date and Time Operations"

TIMESTAMP

CHAR(26)

Refer to "Performing Date and Time Operations"

GRAPHIC

CHAR(2N)

N <= 127

VARGRAPHIC

VARCHAR2(2N)

LONG

N <= 1000

1000 <= N <= 16370

LONG VARGRAPHIC

VARCHAR2(2N)

LONG

N <= 1000

1000 <= N <= 16370

Floating Point Single

FLOAT(21)

n/a

Floating Point Double

FLOAT(53)

n/a

Decimal (P, S)

NUMBER(P,S)

n/a


Performing Character String Operations

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 noncharacter binary data in character columns. Applications run 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 might be returned.

With the gateway running on the host, character data retrieved from a DB2/400, DB2/OS390, or DB2/VM host is translated from EBCDIC to ASCII. When character data is sent to DB2/400, DB2/OS390, or DB2/VM 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/400, DB2/OS390, or DB2/VM that hold noncharacter data must be created with the FOR BIT DATA option. In the application, the character columns holding noncharacter 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.

Converting Character String data types

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 from 1 to 32740 bytes in length. This data type is converted to an Oracle VARCHAR2 data type if it is between 1 and 2000 characters in length. If it is between 2000 and 32740 characters in length, then it is converted to an Oracle LONG data type.

The DRDA VARCHAR data type can be no longer than 32740 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 32740 bytes in length, then you will receive an error message when it is mapped to the DRDA VARCHAR data type.

Performing Graphic String Operations

DB2 GRAPHIC data types store only double-byte string data. Sizes for DB2 GRAPHIC data types typically have maximum sizes which are half that of their character counterparts. For example, the maximum size of a CHAR data type may be 255 characters, whereas the maximum size of a GRAPHIC data type may be 127 characters.

Oracle does not have a direct matching data type, and the gateway therefore converts between Oracle character data types and DB2 Graphic data types. Oracle character data types may contain single, mixed, or double-byte character data. The gateway converts the string data into suitable 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 Appendix C, "DRDA-Specific Parameters" and Appendix D, "National Language Support".

Performing Date and Time Operations

The implementation of date and time data differs significantly in IBM DRDA databases and in the Oracle server. The Oracle server has a single date data type, DATE, that 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 data and time of day.

Processing TIME and TIMESTAMP Data

There is no built-in mechanism that translates the IBM TIME and TIMESTAMP data to Oracle DATE data. An application must process TIME data types to the Oracle CHAR format with a length of eight bytes. An application must process the TIMESTAMP data type in the Oracle CHAR format with a length of 26 bytes.

An application reads TIME and TIMESTAMP functions as character strings and converts or subsets portions of the string to perform numerical operations. TIME and TIMESTAMP values can be sent to an IBM DRDA database as character literals or bind variables of the correct length and format.

Processing DATE Data

Oracle and IBM DATE data types are mapped to each other. If an IBM DATE is queried, then it is converted to an Oracle DATE with a zero (midnight) time of day. If an Oracle DATE is processed against an IBM DATE column, then the date value is converted to the IBM DATE format, and any time value is discarded.

Character representations of dates are different in Oracle format and IBM DRDA format. When an Oracle application SQL statement contains a date literal, or conveys a date using a character bind variable, the gateway must convert the date to an IBM DRDA-compatible format.

The gateway does not automatically recognize when a character value is going to be processed against an IBM DATE column. Applications are required to distinguish character date values by enclosing them with the Oracle TO_DATE function notation.

For example, if EMP is a synonym or view that accesses data on an IBM DRDA database, then instead of this SQL statement:

SELECT * FROM EMP WHERE HIREDATE = '03-MAR-81'

you must use:

SELECT * FROM EMP WHERE HIREDATE = TO_DATE('03-MAR-81')

In a programmatic interface program that uses a character bind variable for the qualifying date value, you must use this SQL statement:

SELECT * FROM EMP WHERE HIREDATE = TO_DATE(:1)

The above SQL notation does not affect SQL statement semantics when the statement is run against an Oracle table. The statement remains portable across Oracle and IBM DRDA-accessed data stores.

The TO_DATE function is not required for dates in any of the following formats:

  • YYYY-MM-DD (ISO/JIS)

  • DD.MM.YYYY (European)

  • MM/DD/YYYY (USA)

For example:

SELECT * FROM EMP WHERE HIREDATE = '1981-03-03'

The TO_DATE requirement also does not pertain to input bind variables that are in Oracle date 7-byte binary format. The gateway recognizes such values as dates.

Performing Date Arithmetic

The following forms of SQL expression generally do not work correctly with the gateway:

date + number

number + date

date - number

date1 - date2

The date and number addition and subtraction (date + number, number + date, date - number) forms are sent to the DRDA Server, where they are rejected. The supported servers do not permit number addition or subtraction with dates.

Because of differing interpretations of date subtraction in the supported servers, subtracting two dates (date1 - date2) gives results that vary by server.

Note:

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

Dates

Date handling has two categories, two-digit year dates, which are treated as occurring 50 years before or 50 years after the year 2000, and four-digit year dates, which are not ambiguous with regard to the year 2000. Oracle recommends that you set the Oracle Database 10g server and gateway default HS_NLS_DATE_FORMAT parameter to a format including a four-digit year.

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 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 HS_NLS_DATE_FORMAT parameter

    The HS_NLS_DATE_FORMAT parameter defines a default format for the Oracle database server explicit TO_DATE functions without a pattern and for implicit string to date conversions.

    For example, with HS_NLS_DATE_FORMAT defined as 'YYYY-MM-DD,' '2008-07-23' can be used in any SELECT, INSERT, UPDATE, or DELETE statement.

HS_NLS_DATE_FORMAT Support

The following patterns can be used for the HS_NLS_DATE_FORMAT:

Table 12-7 HS_NLS_DATE_FORMAT Patterns

DB2 Date Format Pattern Example

EUR

DD.MM.YYYY

30.10.1994

ISO

YYYY-MM-DD

1994-10-30

JIS

YYYY-MM-DD

1994-10-30

USA

MM/DD/YYYY

10/30/1994


The Oracle default format of 'DD-MON-YY' is not permitted with DB2. As a result, the gateway local date exit is provided to change the Oracle default date format of 'DD-MON-YY' or 'DD-MON-RR' to the DB2 ISO format of 'YYYY-MM-DD' before passing the date to DB2.

The following example demonstrates the most efficient way to enter and select date values in the twenty-first century:

ALTER SESSION SET HS_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';

Oracle TO_DATE Function

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 permit 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.

Performing Numeric Data Type Operations

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.

Mapping the COUNT Function

The Oracle Database server 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 NULL values.

For those DRDA servers that do not support the above forms, it may be possible to achieve equivalent functionality by adding a WHERE clause.

SELECT COUNT(colname) FROM table@dblink WHERE colname IS NOT NULL 

or

SELECT COUNT(ALL colname) FROM table@dblink WHERE colname IS NOT NULL

Refer to Chapter 2, "SQL Limitations" for known DRDA servers which do not support all forms of COUNT.

Performing Zoned Decimal Operations

A zoned decimal field is described as packed decimal on an Oracle server. 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 loss of information.

Passing Native SQL Statements through the Gateway

The passthrough SQL feature enables an application developer to send a SQL statement directly to the DRDA Server without the statement being interpreted by the Oracle database server. DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE SQL passthrough statements that are supported by the gateway are limited to nonqueries (INSERT, UPDATE, DELETE, and DDL statements) and cannot contain bind variables. The gateway can run native SQL statements using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE.

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE is a built-in gateway function. This function receives one input argument and returns the number of rows affected by the SQL statement. For DDL statements, the function returns zero.

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE is a reserved name of the gateway and is used specifically for running native SQL.

This release of Oracle Transparent Gateway for DRDA enables retrieval of result sets from queries run with passthrough. The syntax is different from the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function. Refer to "Retrieving Result Sets Through Passthrough" for more information.

Processing DDL Statements through Passthrough

As noted above, SQL statements which are processed through the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function are not interpreted by the Oracle database server. As a result, the Oracle database server will not know if such statements are making any modifications to the DRDA Server. This means that unless you keep the Oracle database's cached information up to date after changes to the DRDA Server, the database may continue to rely upon inaccurate or outdated information in subsequent queries within the same session.

An example of this occurs when you alter the structure of a table by either adding or removing a column. When an application references a table through the gateway (for example, when you perform a query on it), the Oracle database server caches the table definition. Now, suppose that (within the same session) the application subsequently alters the table's form, by using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE to add a column. Then, the next reference to the table (by the application) will return the old column definitions of the table and will ignore the table's new column. This is because the Oracle database server did not process the statement and, so, has no knowledge of the alteration. Because the database does not know of the alteration, it has no reason to requery the table form, and, so, it will use the already-cached form to handle any new queries.

In order for the Oracle database server to acquire the new form of the table, the existing session with the gateway must be closed and a new session must be opened. This can be accomplished in either of two ways:

  • By ending the application session with the Oracle database server and starting a new session after modifications have been made to the DRDA Server; or

  • By running the ALTER SESSION CLOSE DATABASE LINK command after making any modifications to the DRDA Server.

Either of the above actions will void the cached table definitions and will force the Oracle database server to acquire new definitions on the next reference.

Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

To run a passthrough SQL statement using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE, use the following syntax:

number_of_rows = DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink ('native_DRDA_sql'); 

where:

number_of_rows is a variable that is assigned the number of rows that are affected by the passthrough SQL completion. For DDL statements, a zero is returned for the number of rows affected.

dblink is the name of the database link that is used to access the gateway.

native_DRDA_sql is a valid nonquery SQL statement (except CONNECT, COMMIT, and ROLLBACK). The statement cannot contain bind variables. Native SQL statements that cannot be dynamically prepared are rejected by the DRDA Server. The SQL statement that is passed by the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function must be a character string. For more information regarding valid SQL statements, refer to the SQL Reference for the particular DRDA Server.

Examples

  1. Insert a row into a DB2 table using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE:

    DECLARE
      num_rows integer;
    BEGIN
      num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink 
    ('INSERT INTO SCOTT.DEPT VALUES (10,''PURCHASING'',''PHOENIX'')');
    END
    /
    
    
  2. Create a table in DB2 using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE:

    DECLARE
      num_rows integer;
    BEGIN
      num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink
      ('CREATE TABLE MYTABLE (COL1 INTEGER, COL2 INTEGER, COL3 CHAR(14),
      COL4 VARCHAR(13))');
    END;
    /
    

Retrieving Result Sets Through Passthrough

Oracle Transparent Gateway for DRDA provides a facility to retrieve results sets from a SELECT SQL statement entered through passthrough.

Example

DECLARE
  CRS binary_integer;
  RET binary_integer;
  VAL VARCHAR2(10)
BEGIN
  CRS:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@gtwlink;
  DBMS_HS_PASSTHROUGH.PARSE@gtwlink(CRS,'SELECT NAME FROM PT_TABLE');
  RET:=0;
  WHILE (TRUE)
  LOOP
    RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@gtwlink(CRS,FALSE);
    DBMS_HS_PASSTHROUGH.GET_VALUE@gtwlink(CRS,1,VAL);
    INSERT INTO PT_TABLE_LOCAL VALUES(VAL);
  END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('END OF FETCH');
        DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@gtwlink(CRS);
      END;
    END;  
END;
/ 

Oracle Data Dictionary Emulation on a DRDA Server

The gateway optionally augments the DRDA database catalogs with data dictionary views modeled after the Oracle data dictionary. These views are based on the dictionary tables in the DRDA database, presenting that catalog information in views familiar to Oracle users. The views created during the installation of the gateway automatically limit the data dictionary information presented to each user based on the privileges of that user.

Using the Gateway Data Dictionary

The gateway data dictionary views provide users with an Oracle-like interface to the contents and use of the DRDA database. Some of these views are required by Oracle products. The gateway supports the DB2/OS390, DB2/400, and DB2/UDB catalog views. DB2/VM catalog views are not available.

You can query the gateway data dictionary views to see the objects in the DRDA database and to determine the authorized users of the DRDA database. Many Oracle catalog views are supported by the Oracle Transparent Gateway for DRDA. Refer to Appendix A for descriptions of Oracle DB2 catalog views. These views are completely compatible with the gateway.

Using the DRDA Catalog

Each DRDA database has its own catalog tables and views, which you might find useful. Refer to IBM documentation for descriptions of these catalogs.

Defining the Number of DRDA Cursors

You can define any number of cursors depending on your application requirements. Oracle recommends that you use the default value of 100. However, if the default is not correct for your application, there are two points to consider when defining the number of cursors for your installation:

  1. Each cursor requires an additional amount of storage and additional management.

  2. If you change DRDA_PACKAGE_SECTIONS, then you must rebind the package.

The parameter DRDA_PACKAGE_SECTIONS is specific to the DRDA package. This parameter defines the number of sections (open cursors at the IBM database). For more information about setting the DRDA_PACKAGE_SECTIONS parameter, refer to Appendix C, "DRDA-Specific Parameters".