9 Developing Applications

9.1.1 Connecting to DB2/400 Databases with Database Links

A connection to the gateway is established through a database link when that link is first used in an Oracle Database server session. The connection remains active until the session ends. Another session or user can access the same database link but will get a separate connection to DB2/400.

Connections to DB2/400 might be limited by factors such as memory, gateway parameters, or OS/400 server resources.

9.1.2 Read and Write Access to DB2/400 Databases

You can perform read and write functions to a DB2/400 database. SELECT, INSERT, UPDATE, and DELETE are all valid operations.

A single transaction can use an UPDATE statement to change multiple Oracle databases. Only one DB2/400 database can be updated within a transaction. If a stored procedure call is directed to DB2/400, then no updates or stored procedure calls can be directed to any other DB2/400 databases during the transaction. Note that a user-defined function is treated differently as Oracle assumes a user-defined function does not write to DB2/400.

A single transaction can use a SELECT statement to retrieve data from multiple Oracle databases and multiple DB2/400 databases.

The gateway is set up with commitment control set to *CHG. Refer to the IBM reference for IBM DB2 for OS/400 DB2/400 SQL for more information regarding OS/400 commitment control.

9.1.3 Accessing Tables in Multiple Databases

Single SQL statements, using JOINs, can refer to tables in multiple Oracle Databases or multiple DB2/400 databases.

9.2.1 Fetch Reblocking

The array size of the application for SELECT is effective between the application and the Oracle Database 10g server. However, the block size of the buffer between the Oracle integrating server and the gateway is governed by two initialization parameters, RPC FETCH SIZE and RPC FETCH REBLOCKING. These gateway initialization parameters correspond to the Heterogeneous Services initialization parameters HS_RPC_FETCH_SIZE and HS_RPC_FETCH_REBLOCKING, respectively. Figure 9-2 illustrates this. Refer to Oracle Database Administrator's Guide for more information.

With release 10 of the gateway, the Oracle Database 10g server supports fetch reblocking with the RPC FETCH REBLOCKING parameter. When the value of this parameter is set to *YES (the default), the block size of the buffer for SELECT statements is determined by the RPC FETCH SIZE value. The 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 qualifying rows from DB2/400. This feature can provide significant performance enhancements, depending on your application design, installation type, and workload. The RPC FETCH REBLOCKING and RPC FETCH SIZE parameters may be changed by using the CHGORATUN command. Refer to "CHGORATUN, Change Initialization Parameters" for more information.

The array size between the client and the Oracle Database 10g server is still determined by the Oracle application. To ensure optimal performance, Oracle Corporation recommends that you set your Oracle application array size equal to the setting of the gateway ARRAY BLOCK SIZE parameter. For more information about the gateway ARRAY BLOCK SIZE parameter, refer to "Setting Optional Parameters". For more information about array processing usage and implementation in your Oracle application, refer to Oracle Database SQL Reference.

Figure 9-2 Array Size Definition in the DB2/400 Architecture for SELECT

Description of "Figure 9-2 Array Size Definition in the DB2/400 Architecture for SELECT"

9.4.1 Executing DB2/400 Stored Procedures from Applications

In order for an application to invoke a DB2/400 stored procedure, it is first necessary to create the DB2/400 stored procedure on the DB2/400 system by using the procedures that are documented in the IBM reference for DB2/400.

After the stored procedure is defined to DB2/400, the gateway is able to access that stored procedure by using a standard PL/SQL call. For example, an employee name, JOHN SMYTHE, is passed to the DB2/400 stored procedure REVISE_SALARY. The DB2/400 stored procedure retrieves the salary value from the DB2/400 database to calculate a new yearly salary for JOHN SMYTHE.

The revised salary returned in RESULT is used to update SAL in the EMP table of an Oracle Database as follows:

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

When the gateway receives a call to execute a DB2/400 stored procedure, it first does a lookup of the procedure in the QSYS2/SYSPROCS and QSYS2/SYSPARMS DB2/400 system tables to determine the following:

1. The stored procedure to execute

   The gateway retrieves information from the ROUTINE_SCHEMA and ROUTINE_NAME columns of the QSYS2/SYSPROCS table in order to locate the actual DB2/400 stored procedure code to be invoked by the gateway. Multiple DB2/400 stored procedures can have the same ROUTINE_NAME, but they must be uniquely identified by the combination of the ROUTINE_SCHEMA and ROUTINE_NAME columns of the QSYS2/SYSPROCS table.

   The gateway always receives a qualified stored procedure name from the Oracle Database server and executes the DB2/400 stored procedure by using this qualified name. If a qualified name is not explicitly identified by the application, then the user ID that is passed over the database link is used to match the value in ROUTINE_SCHEMA. To ensure that you execute the correct DB2/400 stored procedure, Oracle recommends that you use fully qualified stored procedure names when invoking DB2/400 stored procedures from Oracle PL/SQL.

2. The parameter list of the stored procedure

   When a DB2/400 stored procedure is defined, the attributes of each entry in the argument list for the stored procedure are placed in the QSYS2/SYSPARMS table. The gateway uses this information to convert the data type of each argument to its corresponding PL/SQL data type. Refer to "Converting DB2/400 Data Types to Oracle Data Types" for gateway data type conversion rules.

After the lookup of the description of the DB2/400 stored procedure is complete, the gateway uses this information to execute the DB2/400 stored procedure.

9.4.2 Considerations for Using Stored Procedures with DB2/400

Following is a list of special considerations for using the procedure feature and DB2/400 stored procedures with the Oracle Transparent Gateway for DB2/400:

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

• The gateway executes the DB2/400 CALL statement to execute DB2/400 stored procedures. Commitment control (of database changes that are made with the DB2/400 stored procedure) is managed by DB2/400. For more information regarding commitment control, refer to the IBM reference for DB2/400.

• The gateway supports both GENERAL and GENERAL WITH NULLS linkage conventions of DB2/400 stored procedures.

  • The GENERAL linkage convention means that the parameters that are passed to and from DB2/400 stored procedures cannot be null.

  • The GENERAL WITH NULLS linkage convention means that parameters passed to and from DB2/400 stored procedures can be null when they are passed using indicator variables.

  Oracle Call Interface (OCI) or embedded PL/SQL can be used in host programs to operate on indicator variables. For information about writing DB2/400 stored procedures to use indicator variables, refer to the IBM reference for DB2/400.

• To ensure commitment control integrity between the gateway and the stored procedure program, you must create the ILE *PGM object containing the DB2/400 stored procedure by using the activation group attribute *CALLER. If you do not use the *CALLER attribute, then stored procedures execute in a different activation group than the gateway. If the activation group attribute was given as *NEW, then a forced COMMIT may occur when the activation group ends (that is, when the program object that is defined by the stored procedure ends). For more information about activation groups, refer to the IBM document for ILE concepts.

• If the stored procedure does not change any file or any DB2/400 table on the AS/400, then the program object may be an OPM program object. Otherwise, the program object must be an ILE program object. Failure to adhere to this may cause data to become unsynchronized in the case of a ROLLBACK or COMMIT that either may be planned by you or may be forced because of some failure.

9.6.1 Executing DB2/400 Stored Functions from Applications

In order for an application to invoke a DB2/400 stored function, it is first necessary to create the DB2/400 stored function on the DB2/400 system by using the procedures that are documented in the IBM reference for DB2/400.

Next, if you are going to reference that function in a SQL DML statement (such as SELECT, INSERT, DELETE, or UPDATE), then you must specify the referenced name in the IFS file /HOME/MYINST/RDBMS/ADMIN/CALL_NAMES.ORA, where, MYINST is your instance name. If you attempt to use an AS/400 stored function (DB2/400 user defined function) in a SQL command without specifying that name in the above IFS file, then you will get the Oracle error 'ORA-06571: function xxxxx does not guarantee not to update database'.

After doing the above, the gateway is able to access the stored function using a standard PL/SQL call syntax or via a reference in a SQL DML statement. For example, suppose our DB2/400 stored procedure gives us what should be a revised-salary given a specific employee name. The employee name, JOHN SMYTHE, is passed to the DB2/400 stored function REVISE_SALARY_FUNC. The DB2/400 stored function retrieves the salary value from the DB2/400 database and calculates a new revised salary for our specific employee. Since REVISE_SALARY_FUNC is a function, it returns this salary value as the return value of the function.

The revised salary returned in RESULT is used to update the SAL column in the EMP table of the Oracle Database as follows:

DECLARE
          INPUT VARCHAR2(15);
          RESULT NUMBER(8,2);
        BEGIN
          INPUT := 'JOHN SMYTHE';
          RESULT := MYLIB.REVISE_SALARY_FUNC@DB2400(INPUT);
          UPDATE EMP SET SAL = REUSLT WHERE ENAME := INPUT;
        END;

When the gateway receives a call to execute a DB2/400 stored function, it first does a lookup of the function name in the QSYS2/SYSFUNCS and QSYS2/SYSPARMS DB2/400 system tables to determine the following:

• The stored function to execute

  The gateway retrieves information from the ROUTINE_SCHEMA and ROUTINE_NAME columns of the QSYS2/SYSFUNCS table in order to locate the actual DB2/400 stored function code that is to be invoked by the gateway. Multiple DB2/400 stored functions can have the same ROUTINE_NAME, but they must be uniquely identified by the combination of the ROUTINE_SCHEMA and ROUTINE_NAME columns of the QSYS2/SYSFUNCS table.

  The gateway always receives a qualified stored function name from the Oracle Database server and executes the DB2/400 stored function by using this qualified name. If a qualified name is not explicitly identified by the application, the user ID that is passed over the database link is used to match the value in ROUTINE_SCHEMA. To ensure that you execute the correct DB2/400 stored function, Oracle recommends that you use fully qualified stored function names when invoking DB2/400 stored functions from Oracle PL/SQL and from SQL DML commands.

• The parameter list of the stored functions

  When a DB2/400 stored function is defined, the attributes of each entry in the argument list for that stored function are placed in the QSYS2/SYSPARMS table. The gateway uses this information to convert the data type of each argument to its corresponding PL/SQL data type. Refer to "Converting DB2/400 Data types to Oracle Data types" on page 9-11 for gateway data type conversion rules.

After the lookup of the description of the DB2/400 stored function is complete, the gateway uses this information to execute the DB2/400 stored function.

9.6.2 Considerations for Using Stored Functions with DB2/400

Following is a list of special considerations for use the procedural feature for stored functions with the Oracle Transparent Gateway for DB2/400:

• PL/SQL records cannot be passed as parameters when invoking a DB2/400 stored function.

• No matter the context in which the original function call was done, that is either as a PL/SQL function call or as a function call in a SQL DML statement, the gateway executes the function as part of a SELECT. That is, it does:

  SELECT func_call(func_args, ...) FROM OTGDB2.DUAL;
  
  

• The gateway supports both GENERAL and GENERAL WITH NULLS linkage conventions of DB2/400 stored functions.

  • The GENERAL linkage convention means that the parameters that are passed to DB2/400 stored functions cannot be NULL.

  • The GENERAL WITH NULLS linkage convention means that parameters passed to DB2/400 stored functions can be NULL when they are passed using indicator variables.

  Oracle Call Interface (OCI) or embedded PL/SQL can be used in host programs to operate on indicator variables. See the appropriate DB2/400 IBM references on how to detect NULLs passed as arguments to a DB2/400 user defined functions.

9.7.1 Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

To execute a passthrough SQL statement by using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function, use the following syntax:

number_of_rows = DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink ('DB2/400_sql');
 

where:

number_of_rows is a variable that is assigned the number of rows that are affected by the passthrough SQL execution. 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.

DB2/400_sql is a valid DB2/400 nonquery SQL statement (except CONNECT, COMMIT, or ROLLBACK). DB2/400 SQL statements that cannot be dynamically prepared are rejected by DB2/400. The SQL statement that is passed by the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function must be a character string. For more information regarding DB2/400 SQL statements, refer to the IBM reference for DB2/400.

9.7.2 Examples

Following are examples of the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function.

Refer to the next section, "Using Passthrough Statements to Create Tables", for more information.

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

   declare
     num_rows NUMBER;
   begin
     num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink
     ('INSERT INTO SCOTT.DEPT VALUES ( 10, ''PURCHASING'',
     ''PHOENIX'')');
   end;
   /
   
   

2. Create a DB2/400 table using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE:

   declare
     num_rows NUMBER;
   begin
     num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink
     ('CREATE TABLE MYTABLE (COL1 INTEGER, COL2 INTEGER, COL3 CHAR(14),
     COL4 VARCHAR(13))');
   end;
   /
   
   

9.7.3 Using Passthrough Statements to Create Tables

If you use DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE to create a DB2/400 table, then one of the following is required for journaling:

• a QSQJRN journal in the target library

• a target library that was created as a collection

(This requirement applies if the DB2/400 isolation level is set to *CHG, which is the default.)

If neither of the above is true, then you will receive an error message that the table has been created but cannot be journaled. In this case, you can disregard the error message and journal the file manually. For more information on journaling, refer to "Step 2: Verify Journaling".

9.7.4 Retrieving Results Sets Through Passthrough

Oracle Transparent Gateway for DB2/400 provides a facility to retrieve results sets from a SELECT SQL statement that is issued through passthrough. In the example below, the values for a single field are selected from all the rows of the foreign table PT_TABLE, and these values are inserted into the local Oracle Database table PT_TABLE_LOCAL. Refer to Oracle Database Administrator's Guide for additional information.

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');
BEGIN
  RET:=0;
  WHILE (TRUE)
  LOOP
    RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@gtwlink (CRS,FALSE);
    DBMS_HS_PASSTHROUGH.GET_VALUES@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;
/ 

9.8.1 DB2/400 GRAPHIC Support

Oracle Database server no longer supports NCHAR or NVARCHAR2 data types. With Oracle Database 10g server, the Oracle Transparent Gateway for DB2/400 supports double-byte data types (GRAPHIC and VARGRAPHIC in DB2/400 terms) in a manner similar to the manner in which they were supported in version 4 of the Oracle Transparent Gateway for DB2/400. That is to say, in Oracle tools and applications, they are supported as CHAR and VARCHAR2 data types of the appropriate size.

To support DB2/400 columns of GRAPHIC or VARGRAPHIC type, you must set NLS_LANG to a language in which the character set part corresponds to the CCSID of the (VAR)GRAPHIC column. This way, if the column is CCSID 933, then the character set part of NLS_LANG must be set to KO16DBCS (refer to Appendix B, "National Language Support"). In addition, you must specify on the CHGORATUN screen that you want V4 graphics compatibility, and the gateway job should be running in the CCSID of the GRAPHICs columns. The job CCSID is also set on the CHGORATUN panel.

9.8.2 For DBCS Users

The Oracle Transparent Gateway for DB2/400 allows you to use DBCS (Double Byte Character Set) data under limited conditions, which include a risk for data integrity.

INSERT INTO mytable.graphcol@tg4db2400 values('AxxB')

INSERT INTO mytable.graphcol@tg4db2400 values('AxxB')

wAyywB

9.8.3 Performing Character String Operations

The gateway performs all character string comparisons, concatenations, and sorts by using the data type of the referenced columns (for example, fixed or variable length). The gateway determines the validity of character string values that are passed by applications and automatically converts character strings from one data type to another. It also automatically converts between character strings and dates when necessary.

In contrast, the Oracle Database 10g server processes character strings as variable length character strings. The Oracle Database server performs character string comparisons, concatenations, and sorts by using variable length character string representations. The Oracle Database server never pads or truncates character strings. The Oracle Database server also automatically converts among numbers, character strings, and dates when necessary.

9.8.4 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. This conversion is performed on every bind.

DB2/400 performs automatic conversions to the character string data type (for example, fixed length and variable length) of the destination column. You have no control over the conversion. The conversion might be independent of the data type of the destination column in the database. For example:

UPDATE EMP@AS400 SET ENAME = 'BLAKE' WHERE EMPNO = 7654;

If ENAME is a CHAR(9), fixed-length column in the ENAME table, then the update inserts 'BLAKE' as the last name of employee number 7654. BLAKE is padded with four trailing spaces by the host database to make it conform to the fixed length definition of the column.

The gateway returns data bound in the manner that is requested by the application. For character strings, the format is fixed length string.

The DB2/400 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 4000 characters in length. If it is between 4001 and 32740 characters in length, then it is converted to an Oracle LONG data type.

The Oracle LONG data type can be from 1 byte to 2 GB in length. Because the DB2/400 VARCHAR data type can be no longer than 32740 bytes, you receive an error message if you attempt to insert data longer than 32740 bytes into a DB2/400 VARCHAR or LONG VARCHAR data type.

9.8.5 Performing Date and Time Operations

The implementation of date and time data differs significantly in DB2/400 and the Oracle Database server. The Oracle Database server has a single date data type, DATE, that can contain both calendar date and time-of-day information. DB2/400 supports the following date and time data types:

DATE is the calendar date only.

TIME is the time of day only.

TIMESTAMP is a numerical value that combines a calendar date and time-of-day with microsecond resolution.

There is no mechanism that translates the IBM TIME and TIMESTAMP data to Oracle DATE data. An application must process TIME data types in the Oracle CHAR format with a length of 8 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 columns as character strings and converts portions or subsets of the string to perform numerical operations. TIME and TIMESTAMP values can be sent to DB2/400 as character literals or bind variables of the appropriate length and format.

Oracle DATE data types 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. DB2/400 columns of DATE or TIME data type are assumed to be in ISO format. For DATE, the column format is "yyyy-mm-dd", and for TIME columns, the format is "hh.mm.ss". TIME is in a 24-hour format.

Character representations of dates are different in Oracle format and DB2/400 format. When an Oracle application SQL statement contains a date literal or conveys a date through a character bind variable, the gateway must convert the date to a DB2/400 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 is accessing data in DB2/400, then instead of using the following SQL statement:

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

you must use the following syntax:

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

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

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

This SQL notation does not affect SQL statement semantics when the statement is executed against an Oracle table. The statement remains the same across Oracle and DB2/400 data stores.

The DATE requirement does not apply to input bind variables in Oracle date seven byte binary format. The gateway recognizes such values to be dates.

9.8.6 Dates in the 21st Century

Oracle recommends that you set the Oracle Database 10g server default NLS_DATE_FORMAT parameter to a format including a four-digit year. Use the TO_DATE function to enter dates in the twenty-first century. You can use any date format which includes a four-character field for the year. For example, TO_DATE('2008-07-23', 'YYYY-MM-DD') can be used in any SELECT, INSERT, UPDATE, or DELETE statement.

9.8.7 Performing Numeric Data Type Operations

DB2/400 performs automatic conversions to the numeric data type of the destination column such as BIGINT, INTEGER, SMALLINT, PACKED DECIMAL, and NUMERIC. You have no control over the data type conversion, and the conversion is 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 DB2/400, then the update shown in this example inaccurately sets the price of an ice cream cone to $1.00, because DB2/400 automatically converts a double-precision floating point to an integer:

UPDATE PRODUCT@AS400
SET PRICE = 1.50
WHERE PRODUCT_NAME = 'ICE CREAM CONE    ';

Because PRICE is an integer (in this example), the DB2/400 server automatically converts the decimal data value of 1.50 to 1.

The gateway returns values that are bound in the manner that is requested by the Oracle application. For numbers, the format is typically an Oracle number, limited to a certain number of digits of precision by the application.

9.10.1 SELECT without the FOR UPDATE Clause

A SELECT without the FOR UPDATE clause can be handled in one of three ways:

• If the entire WHERE clause of the SELECT statement is acceptable syntax for DB2/400, then it is given to DB2/400 to perform.

• If part, but not all, of the WHERE clause of the SELECT statement uses features that are not available in DB2/400, then the WHERE clause is split between the DB2/400 system and the Oracle Database server.

  The portion of the WHERE clause that is acceptable for DB2/400 is sent to DB2/400. The Oracle Database server post processes the results of the DB2/400 SELECT and applies the Oracle-specific WHERE clause elements. This results in DB2/400 doing as much of the WHERE clause as possible.

• If the entire WHERE clause is not acceptable for DB2/400, then an unqualified SELECT (without the WHERE clause) is sent for DB2/400, and the Oracle Database server post processes the entire WHERE clause.

The Oracle Database server post processes SELECT statements without the FOR UPDATE clause. Most Oracle SELECT statements are supported. One exception is the CONNECT BY clause.

9.10.2 SELECT FOR UPDATE, INSERT, and DELETE Clauses

DB2/400 must process the entire SELECT FOR UPDATE, INSERT, and DELETE clauses. The Oracle server cannot post process these clauses. Only SQL that is a common subset of Oracle and DB2/400 SQL can be used with these statements.

The following rules exist for the use of SELECT FOR UPDATE, INSERT, and DELETE clauses:

• Only Oracle syntax that is also valid for DB2/400 can be used. For DB2/400 SQL syntax, refer to the IBM reference for DB2/400.

• The following Oracle functions are supported with all options:

  • AVG

  • MAX

  • MIN

  • SUM

  • TO_DATE

• The NOWAIT option of the FOR UPDATE clause of the SELECT statement is not supported.

• Although DB2/400 requires a list of column names in the FOR UPDATE clause, the unqualified Oracle syntax FOR UPDATE with no column names is accepted. The gateway derives the column list from the SELECT result column list.

9.11.1 Oracle Bind Variables

Oracle bind variables become DB2/400 parameter markers when used with the gateway. Therefore, the bind variables are subject to the same restrictions as DB2/400 parameter markers. For example, the following statement fragments are not allowed:

WHERE :x IS NULL 
WHERE :x = :y

For more information about DB2/400 parameter marker restrictions, refer to the IBM reference for DB2/400.

9.11.2 Performing Zoned Decimal Operations

A zoned decimal field is described as packed decimal on an Oracle Database server. However, an Oracle application such as a Pro*C program can insert any supported Oracle numeric data type into a zoned decimal column. The gateway converts this number into the most suitable data type. Data can be retrieved from DB2/400 into any Oracle data type provided it does not result in loss of information.

9.12.1 Using the Gateway Data Dictionary

The gateway data dictionary views provide you with an Oracle-like interface to the contents and use of the DB2/400 data dictionary. Some of these views are required by Oracle products.

You can query the gateway data dictionary views to see the objects in DB2/400 and to determine the authorized users of those objects.

Refer to Appendix C, "Data Dictionary Views" for descriptions of DB2/400 catalog views.

9.12.2 DB2/400 Special Registers

You can access DB2/400 special registers by using the gateway. During installation of the gateway, a DB2/400 view is created in order to enable access to special registers. For example, to find out the primary authorization ID that is being used by the gateway, the following command from your application:

SELECT CURRENT_USER FROM OTGDB2.OTGREGISTER@DB2400 

where OTGDB2 is the default qualifier of the OTGREGISTER view, and DB2400 is the name of a database link to the gateway.