Skip Headers

Oracle Transparent Gateway for DB2 Installation and User's Guide
Release 9.2.0.1.1 for OS/390

Part Number A95217-01
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

9
Developing Applications

Oracle Transparent Gateway for DB2 allows applications written for the Oracle database server to access tables in a DB2 database.  Using a database link, the access can be made transparent by using synonyms or views of the DB2 tables.  However, there are fundamental SQL, data type, and semantic differences between the Oracle database server and the DB2 database.  Read this chapter to learn these differences and for information on developing applications. 

The following topics are included:

Gateway Appearance to Application Programs

An application written to access information in a DB2 database interfaces with an Oracle database server.  When developing applications, remember the following:

Array Processing

When evaluating and tuning your gateway configuration, you can achieve performance gains by using the Oracle array processing interface.  An array is a collection of data items, called elements, associated with a single variable.  With arrays, you can use a single SQL statement to manipulate an entire collection of data items.  For example, suppose you want to insert information regarding 100 employees into the EMP table on DB2.  Without arrays, your program must do 100 individual INSERTs--one for each employee.  With arrays, only one INSERT is necessary.

The use of array processing reduces network calls, which can save elapsed time and CPU cycles.  In addition, when using INSERT for multiple rows, DB2 processing is optimized by retaining the original SQL statement for repeated running.

You can set the array size between the client and the gateway by using your Oracle application implementation for UPDATE, DELETE, and INSERT.

For more information on array processing usage and implementation in your Oracle application, refer to the Oracle SQL*Plus User's Guide and Reference manual or Programmer's Guide to the Oracle Call Interface.


Note:

For performance reasons, Oracle Corporation recommends setting the initial Oracle application array size between 10 and 100.


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 your application design, installation type, and workload. 

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

Refer to "Step 10: Gateway Service Definition" 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 server and a remote instance.  The following example shows two stored procedures: oraproc1 is a procedure stored in the ORA1 Oracle instance, while oraproc2 is a procedure stored in the ORA2 Oracle instance.

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. 

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.

Empproc is an Oracle stored procedure, which subsequently accesses data in DB2 using the gateway:

Like the Oracle database server, standard PL/SQL is used to create and run the procedure.  There is no difference with the gateways, except the stored procedure is accessing DB2 instead of the Oracle database server. 

Gateway two-phase commit processing also applies to updates to DB2 being made within an Oracle stored procedure.  This means the stored procedure can update a single instance of DB2 while also updating any number of Oracle database servers within a single transaction.

Using DB2 Stored Procedures with the Gateway

The procedural feature of the gateway enables completion of native DB2 stored procedures.  In other words, the stored procedure is no longer defined in the Oracle database server, but instead, is defined to DB2.  Again, standard Oracle PL/SQL is used by the Oracle application to run the DB2 stored procedure. 

The gateway does not require special definitions to call the DB2 stored procedure.  Once the stored procedure is defined to DB2, the gateway is able to use the existing DB2 definition to run the procedure.

An Oracle application calls the empproc stored procedure that is defined to DB2.

From the application's perspective, running the DB2 stored procedure is no different than invoking a stored procedure at a remote Oracle instance.

Oracle Application DB2 Stored Procedure Completion

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 the gateway receives a call to run a DB2 stored procedure, it first does a lookup of the procedure in the SYSIBM.SYSPROCEDURES DB2 catalog table to determine:

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

Procedural Feature Considerations with DB2     

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

Passing DB2 SQL Statements through the Gateway

The passthrough SQL feature allows an application developer to send a SQL statement directly to DB2 without the statement being interpreted by the Oracle database server.  The DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE SQL passthrough statements supported by the gateway are limited to non queries (INSERT, UPDATE, DELETE, and DDL statements) and cannot contain bind variables.  The gateway can run native DB2 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 are reserved names of the gateway and are used specifically for running native DB2 SQL. 

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

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:

(The following information is listed in a two-column table with three rows.)

number_of_rows

is a variable that is assigned the number of rows 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 used to access the gateway.

native_DB2_sql

is a valid DB2 non query SQL statement (except CONNECT, COMMIT, and ROLLBACK). The statement cannot contain bind variables. DB2 SQL statements that cannot be dynamically prepared are rejected by DB2. The SQL statement passed by the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function must be a character string. For more information regarding the DB2 SQL statements, refer to the IBM documents for your platform and operating system.

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 Results 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 Oracle9i Server Distributed Database Systems for additional information.

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

DB2 Data Types to Oracle Data Type Conversion

To move data between applications and the underlying database, the gateway maps data values from a host variable or literal of a specific data type to a data type understood by the underlying database. 

Oracle tools expect Oracle data types.  Consequently, the gateway maps values from DB2 servers into appropriate Oracle data types before passing these values back to the application or Oracle tool.  The data type mapping and restrictions are:

(The following information is listed in a three-column table with 15 rows plus a header and a footer row.)

DB2 Server Oracle External Criteria

CHAR(N)

CHAR(N)

N=<254*

VARCHAR(N)

VARCHAR2(N)
LONG

N=<4000 (refer to Note 1)
4000<N<DB2 maximum long value

LONG VARCHAR(N)

VARCHAR2(N)
LONG

N=<4000 (refer to Note 1)
4000<N<DB2 maximum long value

CHAR(N)
FOR BIT DATA

RAW(N)

N=<254 (refer to Note 2)

VARCHAR(N)
FOR BIT DATA

RAW(N)
LONG RAW(N)

1=<N=<255 (refer to Note 1)
255<N=<DB2 maximum long value

LONG VARCHAR(N)
FOR BIT DATA

RAW(N)
LONG RAW(N)

1=<N=<255 (refer to Note 1)
255<N=<DB2 maximum long value

DATE
TIME
TIMESTAMP

DATE
CHAR(8)
CHAR(26)

Refer to "Performing Date and Time Operations"

GRAPHIC (N)

CHAR(2N+2)

N<126

VARGRAPHIC (N)

VARCHAR(2N+2)

N<1999

LONG
VARGRAPHIC (N)

VARCHAR2(2N+2)

FLOAT(N) (single)

FLOAT(21)

1=<N=<21

FLOAT(N) (double)

FLOAT(53)

22=<N=<53

Decimal(P,S)

NUMBER(P,S)

n/a

INTEGER

NUMBER(10)

n/a

SMALLINT

NUMBER(5)

n/a

Note 1: Although the limits of some data types within the Oracle database server has increased, the limits used in the gateway are not changed. This is so that you can maintain existing user application compatibility.

Note 2: The Oracle database server can support a length of up to 2000 fixed character columns; but the maximum for DB2 is 254.


Note:

The Oracle9i database server allows only one LONG column per table.  This might allow for a situation where a DB2 table cannot be directly replicated as an Oracle table.


To determine DB2 maximum long values, refer to the IBM documents for your platform and operating system.

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.

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 2G 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. 

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:

(The following information is listed in a two-column table with three rows.)

DATE

is the calendar date only.

TIME

is the time of day only.

TIMESTAMP

is a numerical value combining calendar date and time of day with microsecond resolution of the time value.

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. 

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 following information is listed in a three-column table with six rows plus a header row.)

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

LOCAL

DD-MON-YY

30-OCT-94

DD-MON-RR

29-MAR-05

USA

MM/DD/YYYY

10/30/1994

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

Any non-gateway DB2 access (for example, through batch, TSO, or CICS) supplying date strings (ISO, JIS, USA, or EUR formats) do not call the gateway's DB2 local date exit.  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:

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" for further information.

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

NLS_DATE_FORMAT Support

The following patterns can be used for the NLS_DATE_FORMAT:

(The following information is listed in a three-column table with four rows plus a header row.)

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

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 allowed.  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 allow 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.

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 post processed by the integrating server.


Note:

Oracle Corporation recommends avoiding date arithmetic expressions in all gateway SQL statements.


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

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:

Double Byte Character Set Support     

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.

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.

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. 

Ensure your programs:

SQL Functions

One of the most important features of the Oracle Open Gateways product family is providing SQL transparency to the user and the application programmer.  Foreign data store SQL functions can be categorized into three areas:

Refer to Appendix F, "Quick Reference to Oracle SQL Functions", for a listing of the Oracle database server functions.  For more detailed information, refer to the Oracle9i Server SQL Reference.

Oracle Database Server SQL Construct Processing

Some gateway post-processing considerations are explained below.

SELECT without the FOR UPDATE Clause

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

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.

SELECT FOR UPDATE, INSERT, and DELETE Clauses

DB2 must process the entire SELECT FOR UPDATE, INSERT, and DELETE clauses.  The Oracle database server cannot post process 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, and DELETE clauses:

Oracle Database Server and DB2 Differences

Please be aware of the following differences between the Oracle Database Server and DB2.

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.

Mapping the COUNT Function 

The Oracle database server supports four options for the COUNT function:         

DB2 servers support only two options for the COUNT function:

COUNT(ALL expression) and COUNT(expression) are post processed.

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.

Oracle Data Dictionary Emulation on a DB2 Server

The gateway can optionally augment the DB2 database catalogs with data dictionary views modeled after the Oracle data dictionary.  These views are based upon the dictionary tables in the DB2 database, presenting the 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 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 E, "Data Dictionary Views", for descriptions of Oracle DB2 catalog views.

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, in order 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 E, "Data Dictionary Views", for a description of the OTGREGISTER view.     


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Table Of Contents
Contents
Go To Index
Index