9 Developing Applications

9.2.1 Fetch Reblocking

The Oracle database server 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 server.  The buffer might contain one or more qualified rows from DB2.  This feature can provide significant performance enhancements, depending on the application design, installation type, and workload. 

The array size between the client and the Oracle database server is still determined by the Oracle application.

9.4.1 Oracle Application DB2 Stored Procedure Execution

In order for an Oracle application to call a DB2 stored procedure, it is first necessary to create the DB2 stored procedure on the DB2 system using the procedures found in the IBM documents for your platform and operating system. 

After the stored procedure is defined to DB2, the gateway is 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';
  SYSPROC.REVISE_SALARY@DB2(INPUT, RESULT);
  UPDATE EMP SET SAL = RESULT WHERE ENAME = INPUT;
END;

When running a DB2 stored procedure, a two-part procedure name is sent to DB2 through the gateway.  If no qualifier is used in the Oracle application to call the stored procedure, then the userid passed over the database link, or PUBLIC, is used as the qualifier for the procedure name.

DB2 stored procedures migrated from DB2 V5.1 require that SYSPROC must be the first qualifier of a stored procedure name.  Therefore, the application must ensure SYSPROC is used as the qualifier for the DB2 stored procedure.  One way to do this is to explicitly qualify the procedure name:

BEGIN
  SYSPROC.PROC1(parm1)
END

DB2 stored procedures for DB2 V6.1 and subsequent allows stored procedures to be qualified by schema name other than SYSPROC.

9.4.2 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 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 rollback 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.

• When running DB2 stored procedures containing DB2 SQL, you must have the collection ID of the DB2 package specified in the CREATE PROCEDURE. If this was not initially specified, an ALTER PROCEDURE can be done to add this.

  This is required because the DB2 plan for the gateway must identify the packages for the DB2 stored procedures it runs.  The default DB2 bind JCL, delivered with the gateway, uses *.* to identify the package list for the gateway.  When this is specified, DB2 identifies the collection ID of the DB2 stored procedure as the one specified in the COLLID column of the DB2 stored procedure entry in the SYSROUTINES table.

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

• The gateway supports DB2SQL, GENERAL and GENERAL WITH NULLS linkage conventions of DB2 stored procedures. 

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

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

  • Embedded PL/SQL or OCI can be used in the host program to operate on indicator variables.  Refer to Appendix E, "Sample Applications", for a sample DB2 stored procedure and PL/SQL program using the GENERAL WITH NULLS linkage convention.

9.5.1 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_DB2_sql'); 

where:

DECLARE
  num_rows integer;
BEGIN
  num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink ('INSERT 
  INTO SCOTT.DEPT VALUES (10,''PURCHASING'',''PHOENIX'')');
END;
/

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;
/

9.5.2 Retrieving Result Sets Through Passthrough

Oracle Transparent Gateway for DB2 provides a facility to retrieve results sets from a SELECT SQL statement entered through passthrough. Refer to the Oracle Database Heterogeneous Connectivity 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.6.1 Performing Character String Operations

Frequently, DB2 databases are designed to hold non-character binary data in character columns.  Applications run on DB2 systems can store and retrieve data as though it contained character data.  However, when an application accessing this data runs in an environment using a different character set, inaccurate data might be returned. 

When character data is sent to DB2 from an ASCII system, ASCII data is translated to EBCDIC.  This translation is meaningless when the characters are binary data in a character column.  The application receives incorrect information or errors.

To resolve these errors, the gateway requires character columns on DB2 holding non-character data be created with the FOR BIT DATA option.  In the application, the character columns holding non-character data can 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.

Existing DB2 tables can be changed by directly updating the DB2 catalog.

For more information about DB2 parameters, refer to the IBM documents for your platform and operating system.

9.6.2 Converting Character String Data Types

The DB2 VARCHAR data type can be from one to the maximum long value for DB2.  This data type is converted to an Oracle VARCHAR2 data type if it is between 1 and 4000 characters in length.  If character length is between 4000 and the maximum long value for DB2, then it is converted to an Oracle LONG data type.

For additional information about determining the maximum long value for DB2, refer to the IBM documents for your platform and operating system.

The Oracle LONG data type can be from 1 to 2 G in length, but the DB2 VARCHAR data type can be no longer than 32 740 bytes.  If you define a LONG data type longer than 32 740 bytes in length, then you receive an error message. 

9.6.3 Performing Date and Time Operations

The implementation of date and time data differs significantly in DB2 databases and the Oracle database server.  The Oracle database server has a single date data type, DATE, containing both calendar date and time of day information.  DB2 databases support the following three distinct date and time data types:

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 in 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 columns as character strings and converts or subsets portions of the string to perform numerical operations.  TIME and TIMESTAMP values can be sent to a DB2 database as character literals or bind variables of the appropriate length and format.

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 the Oracle database server format and DB2 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 a DB2-compatible format. 

9.6.4 DB2 Local Date Exit

Oracle Transparent Gateway for DB2 includes a DB2 local date exit.  The exit is called only when needed and does not interfere with normal DB2 operations or impact performance.  With the exit installed, DB2 DATE columns are handled through the gateway.  If you do not install the exit, then Oracle SQL requires changes when referencing DB2 DATE columns.

When a string constant, string bind variable, string expression, or character column is compared or assigned to a DB2 date column, it is converted from its string format to an internal DB2 format before DB2 processes it.  DB2 date conversion routines look for the following formats of date string.  The DB2 local date exit is called only if the date string does not match any of the standard formats.

The LOCAL DB2 date format is available when the gateway local date exit is installed.

The local exit is called only if the date format cannot be matched to ISO, JIS, USA, or EUR formats.  In a native DB2 program, this is frequently due to a bad date string value.  If a bad date string value is entered, then the DB2 local date exit is called and rejects the bad date string.

The DB2 local date exit is called in the following circumstances:

• A native DB2 program has a bad date string value that cannot be matched to ISO, JIS, USA, or EUR formats.

• A gateway program supplies one of the Oracle DATE formats, 'DD-MON-YY' or 'DD-MON-RR'.

When you install the DB2 local date exit (DSNXVDTX) supplied with the gateway, you can use ISO, JIS, USA, and EUR as well as the Oracle date formats 'DD-MON-YY', 'DD-MON-YYYY', and 'DD-MON-RR' through the gateway.  The DB2 local date exit must be installed in order to specify these Oracle date formats without any SQL changes.  If you do not install the exit, then you must use the Oracle TO_DATE function to pass these Oracle date formats through the gateway.  Refer to "Step 4: Make Authorization and Local Date Exits Available to DB2" on page 5-28 for further information.

9.6.5 Date Considerations in SQL Coding

If the gateway local date exit is installed on the DB2 system, then DB2 DATE columns appear as Oracle DATE columns through the gateway.  Normal Oracle DATE processing and string values can be used.  DB2 DATE columns are handled as Oracle DATE columns.

If the gateway's DB2 local date exit is not installed, then most SQL statements referencing DB2 DATE columns require changes.  When a string constant or string bind variable is compared or assigned to a DB2 DATE column, a TO_DATE function must be added to the statement, enclosing the constant or bind variable.

When the DB2 local date exit is installed, the following SQL statements are accepted:

INSERT INTO EMP (HIREDATE) VALUES ('30-OCT-94');
SELECT * FROM EMP WHERE HIREDATE = '30-OCT-94';
UPDATE EMP SET HIREDATE = '31-OCT-94'
   WHERE HIREDATE = '30-OCT-94';
DELETE FROM EMP WHERE HIREDATE = '31-OCT-94';

If the DB2 local date exit is not installed, then the following SQL statements are required:

INSERT INTO EMP (HIREDATE) VALUES (TO_DATE('30-OCT-94'));
SELECT * FROM EMP WHERE HIREDATE = TO_DATE('30-OCT-94');
UPDATE EMP SET HIREDATE = TO_DATE('31-OCT-94')
   WHERE HIREDATE = TO_DATE('30-OCT-94');
DELETE FROM EMP WHERE HIREDATE = TO_DATE('31-OCT-94');

9.6.6 NLS_DATE_FORMAT Support

The following patterns can be used for the NLS_DATE_FORMAT:

The Oracle default format of 'DD-MON-YY' is not allowed 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 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';

9.6.7 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 with one exception.  If TO_DATE(column) is coded, then it is always post-processed by Oracle because DB2 doesn't have a TO_DATE function like Oracle's.

All forms of the TO_DATE function (with one, two, or three operands) are supported.

9.6.8 Date Arithmetic

The following SQL expression forms 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 through to the DB2 server where they are rejected.  The supported servers do not allow number addition or subtraction with dates.  Because of differing interpretations of date subtraction in the supported servers, subtracting two dates (date1 - date2) does not work correctly when postprocessed by the integrating server.

9.6.9 Performing Numeric Data Type Operations

DB2 servers perform automatic conversions to the numeric data type of the destination column (such as integer, double-precision floating point, or decimal).  You have no control over data type conversion, and this conversion might 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 a DB2 database, then the update shown in the following example inaccurately sets the price of an ice cream cone to $1.00 because the DB2 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 DB2 server automatically converts the decimal data value of 1.50 to 1. 

9.6.10 Oracle ROWID Column

DB2 does not have a column equivalent to the Oracle ROWID column.  Because the ROWID column is not supported, these restrictions apply:

• UPDATE and DELETE are not supported with the WHERE CURRENT OF CURSOR clause.

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

• Oracle fast refresh materialized views between the Oracle database server and DB2 are not supported.

  Oracle fast refresh materialized views rely internally on the Oracle ROWID column.  However, complete refresh materialized views are supported.

9.6.11 Double Byte Character Set Support     

Katakana is not supported in the DB2 GRAPHIC data type because the data type is double byte only.  Katakana is encoded as single byte in IBM code pages 290 and 1027, and Oracle JA16DBCS and JA16EBCDIC930 character sets.

Katakana can be supported in DB2 CHAR and VARCHAR data types as mixed data if Oracle client programs and the Oracle database server are linked with NLSRTL release 2.3.4 or later.  This uses the correct Katakana translation routines.

9.6.12 CHAR FOR BIT DATA

CHAR FOR BIT DATA is fixed length binary data in DB2.  In the Oracle database server, CHAR FOR BIT DATA is converted to RAW, which is in variable length binary format. 

DB2 CHAR and VARCHAR for bit data (RAW data types) are supported.  Raw data in VALUES clause, WHERE clause predicate, or bind variables are treated as hexadecimal digits.

Ensure that your programs:

• Handle CHAR FOR BIT DATA as a variable length string of binary characters. 

• Set the length to the maximum length of the DB2 fixed length column and pad with the binary value your program expects to be returned. 

9.8.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, then it is given to DB2 to perform.

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

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

• If the entire WHERE clause is not acceptable for DB2, then an unqualified SELECT (without the WHERE clause) is sent for DB2, and the Oracle database server postprocesses the entire WHERE clause.

The Oracle database server postprocesses SELECT statements without the FOR UPDATE clause.  Most Oracle SELECT statements are supported.  One exception is the CONNECT BY clause.

9.8.2 SELECT FOR UPDATE, INSERT,UPDATE, and DELETE Clauses

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

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

• Only Oracle syntax that is also valid for DB2 can be used.  For DB2 SQL syntax, refer to the IBM documents for your platform and operating system.

• The following Oracle database server functions are supported with all options:

  • AVGCOUNT

  • MAX

  • MIN

  • SUM

  • TO_DATE

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

9.9.1 Mass Delete from a Segmented Tablespace

When using the following command from SQL*Plus:

DELETE FROM ABC@dblink

all rows are deleted from a segmented tablespace.  However, DB2 occasionally sets the updated rows field to negative 1 (-1) even though more rows are actually deleted.  This can cause the result from SQL*Plus to indicate an incorrect number of rows updated.

9.9.2 Oracle Bind Variables

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

WHERE :x IS NULL 
WHERE :x = :y

For more information about DB2 parameter marker restrictions, refer to the IBM documents for your platform and operating system.

9.10.1 Using the Gateway Data Dictionary

The gateway data dictionary views provide the gateway users with an interface (that looks like an Oracle database server interface) to the contents and use of the DB2 database.  Some of these views are required by Oracle products.

You can query the gateway data dictionary views to look at the objects in the DB2 database and to determine the authorized users of the DB2 database. 

All Oracle DB2 catalog views are supported in this release of the gateway.  Refer to Appendix C, "Data Dictionary Views", for descriptions of Oracle DB2 catalog views.

9.10.2 DB2 Special Registers

You are able to access DB2 special registers using the gateway.  During installation of the gateway, a DB2 view is created to access special registers.  For example, to find out the primary authorization ID being used by the gateway, run the following statement from your application: 

SELECT CURRENT_USER FROM OTGDB2.OTGREGISTER@DB2 

where OTGDB2 is the default qualifier of the OTGREGISTER view, and DB2 is the name of a database link to the gateway.  Refer to Appendix C, "Data Dictionary Views", for a description of the OTGREGISTER view.