9
Developing Applications

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

To develop applications that access AS/400 data through the gateway, you need to understand the topics in this chapter.  It contains the following sections:

• Gateway Appearance to Application Programs

• Array Processing

• Using Oracle Stored Procedures with the Gateway

• Using DB2/400 Stored Procedures with the Gateway

• Passing DB2/400 SQL Statements through the Gateway

• Converting DB2/400 Datatypes to Oracle Datatypes

• SQL Functions

• Oracle Server SQL Construct Processing

• Oracle Server and DB2/400 Differences

• Oracle Data Dictionary Emulation in a DB2/400 Server

• Oracle Developer Forms Compatibility

Gateway Appearance to Application Programs

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

• You must define the DB2/400 database to the application by use of a database link.  Your application specifies tables existing on a DB2/400 database using the name defined in the database link.  For example, if you define a database link naming the DB2/400 database link as DB2400, and an application needs to retrieve data from an Oracle database and the DB2/400 database, the following SQL statement retrieves data from both Oracle and DB2/400:

  SELECT EMP.EMPNO, EMPS.SALARY FROM EMP, EMPS@DB2400 
       WHERE EMP.EMPNO = EMPS.EMPNO;
  
  

  In this example, EMP is a table on the Oracle server and EMPS is a table on the DB2/400 server.  Alternatively, you can define a synonym or a view referencing the DB2/400 server table and access the information without the database link suffix.

  For example:

     CREATE SYNONYM EMPS FOR EMPS@DB2400;                                                                                                                                
  SELECT EMP.EMPNO, EMPS.SALARY FROM EMP, EMPS
        WHERE EMP.EMPNO = EMPS.EMPNO;
  
  

• You can perform reads and writes of data to a defined DB2/400 database.  SELECT, INSERT, UPDATE, and DELETE are all valid operations.

• A single transaction can NOT write to more than one DB2/400 database.  A stored procedure call via DB2/400 counts as a write.

• Single SQL statements, using a JOIN, can refer to tables in multiple Oracle databases, multiple DB2/400 databases, or both.

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 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 AS/400 server resources.

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.  In addition, only one DB2/400 database may participate in a stored procedure call.

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

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

Accessing Tables in Multiple Databases

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

Array Processing

When evaluating and tuning your gateway configuration, you may achieve performance gains by using Oracle's 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/400.  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 may save elapsed time and CPU cycles.  In addition, when using INSERT for multiple rows, DB2/400 processing is optimized by retaining the original SQL statement for repeated execution.

The array size is a factor in transferring data between the client application (for example, SQL*Plus) and the Oracle integrating server when using UPDATE, DELETE, and INSERT statements, in that it allows one UPDATE, DELETE or INSERT statement to be used with a series of values.  For more information on array processing usage and implementation in your Oracle application, refer to Oracle9i SQL Reference.

Figure 9-1 Use of Array Size Definition in the DB2/400 Architecture for UPDATE, DELETE, and INSERT

Text description of the illustration 9_1.gif

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

Fetch Reblocking

The array size of the application for SELECT is effective between the application and the Oracle9i database server.  However, the block size of the buffer between the integrating Oracle server and the gateway is controlled 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 below illustrates this.  Refer to Oracle9i Database Administrator's Guide for more information.

With version 9 of the gateway, the Oracle9i database 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 Oracle9i database 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 Oracle9i database 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 gateway's ARRAY BLOCK SIZE parameter.  For more information on the gateway's ARRAY BLOCK SIZE parameter, refer to "Setting Optional Parameters".  For more information on array processing usage and implementation in your Oracle application, refer to Oracle9i SQL Reference.

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

Text description of the illustration 9_2.gif

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 continual use.  Applications use standard Oracle PL/SQL to invoke stored procedures.

Oracle stored procedures can be located in a local instance of Oracle and a remote instance.  Figure 9-3 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.

Figure 9-3 Calling Oracle Stored Procedures in a Distributed Oracle Environment

Text description of the illustration 9_3.gif

If the application wants to maintain location transparency, a synonym can be created:

CREATE SYNONYM ORAPROC2 FOR oraproc2@ora2;

where oraproc2 is the procedure stored in ORA2.

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

In Figure 9-3, 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/400 tables through the gateway.

In Figure 9-4, empproc is an Oracle stored procedure that subsequently accesses data in DB2 using the Oracle Transparent Gateway for DB2/400.

Figure 9-4 Using Oracle Stored Procedures with DB2/400

Text description of the illustration 9_4.gif

Like the Oracle gateway, standard PL/SQL is used to create and execute the procedure.  There is no difference in the gateways, except that the stored procedure is accessing DB2/400 instead of Oracle.

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

A call to a DB2/400 stored procedure counts as a write since, as far as the Oracle Transparent Gateway for DB2/400 is concerned, the gateway cannot tell if the stored procedure is actually changing anything on the AS/400.  Therefore, you cannot have a transaction that calls two DB2/400 stored procedures via two separate database links.  Correspondingly, during a transaction, you cannot call a DB2/400 stored procedure via one database link and also do an INSERT, UPDATE, or DELETE relative to DB2/400 using another database link (even if the two database links resolve to the same AS/400).

Using DB2/400 Stored Procedures with the Gateway

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

The gateway does not require special definitions in order to invoke the DB2/400 stored procedure.  Once the stored procedure is defined to DB2/400, the gateway is able to use the existing DB2/400 definition to execute the procedure.

In Figure 9-5, an Oracle application calls the empproc stored procedure that is defined to DB2/400.

Figure 9-5 Executing DB2/400 Stored Procedures

Text description of the illustration 9_5.gif

From the perspective of the application, executing the DB2/400 stored procedure is no different than invoking a stored procedure at a remote Oracle instance.

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 using the procedures documented in the IBM reference for DB2/400.

After the stored procedure is defined to DB2/400, 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/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 updated EMP in a table of an Oracle database:

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 tables to determine:

1. The stored procedure to execute.

   The gateway retrieves information from the ROUTINE_SCHEMA and ROUTINE_NAME columns of the QSYS2/SYSPROCS table to locate the DB2/400 stored procedure to be invoked by the gateway.  Multiple DB2/400 stored procedures can have the same ROUTINE_NAME, but 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 server and executes the DB2/400 stored procedure 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 Corporation recommends 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 datatype of each argument to its corresponding PL/SQL datatype.  Refer to "Converting DB2/400 Datatypes to Oracle Datatypes" for gateway datatype 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.

Considerations for Using Stored Procedures with DB2/400

There are special considerations for using the procedural feature 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.  Refer to the IBM reference for DB2/400 for more information regarding commitment control.

• The gateway supports both GENERAL and GENERAL WITH NULLS linkage conventions of DB2/400 stored procedures.
  • The GENERAL linkage convention means that the parameters 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 on 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 *PGM object containing the DB2/400 stored procedure using the activation group attribute *CALLER.  If you do not use the *CALLER attribute, 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 defined by the stored procedure ends).  For more information on 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 that either may be planned by you or may be forced because of some failure.

Passing DB2/400 SQL Statements through the Gateway

The passthrough SQL feature allows an application developer to send a SQL statement directly to DB2/400 without the statement being interpreted by the Oracle9i database server.  DB2/400 SQL statements that are 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/400 SQL statements using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function.

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE is a function that is built into the gateway.  This function takes 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 DB2/400 SQL.

This release of Oracle Transparent Gateway for DB2/400 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 execute a passthrough SQL statement 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.  Refer to the IBM reference for DB2/400 for more information regarding DB2/400 SQL statements.

Examples

Following are examples of the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function.

Refer to "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;
   /
   
   

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 journalling:

• 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 journalled.  In which case, you can disregard the error message and journal the file manually.  Refer to "Step 2: Verify Journalling" and "Step 3: Journal the ORACLE2PC File" for more information on journalling.

Retrieving Results Sets Through Passthrough

Oracle Transparent Gateway for DB2/400 provides a facility to retrieve results sets from a SELECT SQL statement 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 table PT_TABLE_LOCAL.  Refer to Oracle9i Database Administrator's Guide 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;
/ 

Converting DB2/400 Datatypes to Oracle Datatypes

To move data between applications and the underlying database, the gateway binds data values from a host variable or literal to a datatype that is understood by the underlying database.

Oracle tools expect Oracle datatypes.  Consequently, the gateway maps values from DB2/400 servers into appropriate Oracle datatypes before passing these values back to the application or Oracle tool.  The following table lists the datatype mapping and restrictions:

Table 9-1  Datatype Mapping and Restrictions

DB2/400	Oracle	Criteria	Comments
CHAR(N)	CHAR(N) VARCHAR2(N) LONG	1 N 255 255 < N 4000 4000 < N	Maximum length of a DB2/400 CHAR column is 32766 characters.
VARCHAR(N)	VARCHAR2(N) LONG	N 4000 4000 < N 32740	Maximum length of a DB2/400 VARCHAR column is 32740 characters
LONG VARCHAR(N)	VARCHAR2(N) LONG	N 4000 4000 < N 32740	Maximum length of a DB2/400 VARCHAR column is 32740 characters.
VARCHAR(N) for Bit Data	RAW(N) LONG RAW(N)	1 N 255 255 < N 32740	Maximum length of a DB2/400 VARCHAR column is 32740 characters.
DATE	DATE	Refer to"Performing Date and Time Operations".	Refer to "Performing Date and Time Operations" .
TIME	CHAR(8)
TIMESTAMP	CHAR(26)
* GRAPHIC(N)	CHAR(2*N) VARCHAR2(2*N)	1 N 127 127 < N 16370	Maximum length of a DB2/400 GRAPHIC column is 16370 graphic characters
* VARGRAPHIC(N)	VARCHAR2(2*N) LONG(2*N)	1 N 2000 2000 < N	Maximum length of a DB2/400 VARGRAPHIC column is 16370 graphic characters
* LONG VARGRAPHIC(N)	VARCHAR2(2*N) LONG(2*N)	1 N 2000 2000 < N	Maximum length of a DB2/400 VARGRAPHIC column is 16370 graphic characters
FLOAT(N)	FLOAT(21)	1 N 21
FLOAT(N)	FLOAT(53)	22 N 53
DECIMAL(P,S)	NUMBER(P,S)		Packed decimal on the AS/400
NUMERIC(P,S)	NUMBER(P,S)		Zoned decimal on the AS/400
INTEGER	NUMBER(10)
SMALLINT	NUMBER(5)

* Table Note: If the column CCSID is 13488 (UCS-2), and if the gateway NLS_LANG specifies a single-byte character set, then replace "2*N" in the Oracle column with "N", replace 127 with 255, and replace 2000 with 4000.  If the column CCSID is 13488 and Oracle is expecting single-byte data, then (VAR)GRAPHICs are treated as (VAR)CHARs.

DB2/400 GRAPHIC Support

Oracle8i server supports NCHAR and NVARCHAR2 datatypes in order to support multi-byte character sets.  Oracle9i server does not support NCHAR or NVARCHAR2 datatypes.  With Oracle9i, the Oracle Transparent Gateway for DB2/400 supports double-byte datatypes (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 datatypes of the appropriate size. 

In order 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.  Thus, 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.

For DBCS Users

Double-Byte Character Support

This version of the Oracle Transparent Gateway for DB2/400 allows you to access and change columns in DB2/400 that are designated as VARGRAPHIC or GRAPHIC.  Because of the IBM definition of a (VAR)GRAPHIC column, you can insert only double-width characters into (VAR)GRAPHIC columns -- in other words, the string to be inserted must consist entirely of DBCS characters.  Attempts to insert a mixed-byte string into a (VAR)GRAPHIC column will result in an error.  For example, attempting to insert a value represented by a mixed-byte string constant into a (VAR)GRAPHIC column will result in a SQL0105 error: "Mixed or Graphic String Constant Not Valid."

An ASCII client might attempt to use the following INSERT statement:

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

where "A" and "B" are the normal ASCII single-width characters and "xx" designates a double-width character in one of the Far Eastern ASCII-based character sets such as JA16SJIS, ZHS16CGB213280, ZHT16BIG5, or KO16KSC5601.  There is no unambiguous way in which to insert such data into a (VAR)GRAPHIC column.  The problem exists because no DBCS character corresponds to each of the single-byte characters.

This version of the Oracle Transparent Gateway for DB2/400 provides a workaround, but the workaround carries a risk for data integrity.  If you desire to choose this path, then you must realize that if you attempt to INSERT a string with a single-byte character, then that string may be returned to you entirely as a DBCS string in a subsequent SELECT as a double-byte character.  The rules are as follows:

1. With no options specified, the user must ensure that any strings to be INSERTed into (VAR)GRAPHIC columns are in their "wide" form on the ASCII machine.  The string can not contain any single-byte characters.  This means that when converting from the ASCII-based string to a DBCS string, the conversion does not result in a DBCS string with a SHIFT-OUT or SHIFT-IN character at any other position than the first character position for the SHIFT-OUT character or the last character position for the SHIFT-IN character.  The same is true for values represented by constants or for values represented by bind-variables.

2. Specifying the FORCE_SB option causes all single-byte characters to be translated to their corresponding double-byte values on input -- via either an INSERT or a bind-variable.  The entire string is then INSERTed into the (VAR)GRAPHIC column.  On output, that is to say, on a SELECT, each (VAR)GRAPHIC column will be examined for double-byte characters that correspond to single-byte characters.   Each such character is replaced by its corresponding single-byte character.  This is where the data integrity problem arises.  Since single-byte characters have been forced to their corresponding double-byte characters on input, there is no way to know if a double-byte character actually came from the translation of an ASCII wide character or from the process of forcing single-byte characters to the corresponding double-byte characters.

As an example, use the previous INSERT statement, which is:

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

If you use the FORCE_SB option, then the resultant DBCS value in the DB2/400 column may look like the following:

wAyywB

where "wA" is the DBCS correspondence (usually 0x42C1) for the single byte 'A', "wB" is the DBCS correspondence (usually 0x42C2) for the single byte 'B', and "yy" is the DBCS character corresponding to the ASCII-based 'xx'.

When SELECTing from this column, on the client you would get exactly what you INSERTed, that is, 'AxxB' -- which is good.  But what if the DBCS character that is represented in the DB2/400 column by "wa" had actually been INSERTed into the column via a valid ASCII representation for a wide A?  With the FORCE_SB option, you get a single-byte A on the client.  This may not be exactly what you wanted.

In the end, it is you, the customer, who must decide whether this option is valuable to you.  You must decide if you can accept the possible problems that can arise.

The ORADBMBOPT Data Area (or Environment variable) controls this feature.  If there is no ORADBMBOPT Data Area (or Environment variable), then no forcing of single-byte character to double-byte character will take place when transferring data into DB2/400 columns, and no forcing of double-byte characters to the single-byte correspondences will take place when transferring data from DB2/400 columns.  Placing the string "FORCE_SBCS" into ORADBMBOPT turns the feature ON.

Note that you should not use PassThrough to execute any SQL commands that contain graphic constants, unless those constants conform fully with DB2/400 graphic constants.  DB2/400 graphic constants start with G' (G apostrophe) or N' (N apostrophe) and end with an apostrophe ( ' ) character.  The first character after the G' or N' must be a SHIFT-OUT character and the character preceding the apostrophe ( ' ) at the end of the constant must be a SHIFT-IN character.  Between the SHIFT-OUT and SHIFT-IN characters, an even number of bytes must be present, with each byte pair making up a single DBCS character.

Performing Character String Operations

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

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

Converting Character String Datatypes

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 datatype (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 datatype 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 requested by the application.  For character strings, the format is fixed length string.

The DB2/400 VARCHAR datatype can be from 1 to 32740 bytes in length.  This datatype is converted to an Oracle VARCHAR2 datatype if it is between 1 and 4000 characters in length.  If it is between 4001 and 32740 characters in length, it is converted to an Oracle LONG datatype.

The Oracle LONG datatype can be from 1 byte to 2 GB in length.  Because the DB2/400 VARCHAR datatype 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 datatype.

Performing Date and Time Operations

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

DATE	is the calendar date only.
TIME	is the time of day only.
TIMESTAMP	is a numerical value combining 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 datatypes in the Oracle CHAR format with a length of 8 bytes.  An application must process the TIMESTAMP datatype in the Oracle CHAR format with a length of 26 bytes.

An application reads TIME and TIMESTAMP functions as character strings and converts portions 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 datatypes and IBM DATE datatypes are mapped to each other.  If an IBM DATE is queried, 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, the date value is converted to the IBM DATE format and any time value is discarded.  Columns of DATE or TIME datatype are 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 Oracle TO_DATE( ) function notation.

For example, if EMP is a synonym or view accessing data in DB2/400, instead of the following SQL statement:

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

you must use:

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.

Note: There are special considerations for using date or time datatypes in stored procedures with DB2/400.  Refer to "Known Problems" for more information.

Dates in the 21st Century

Oracle Corporation recommends that you set the Oracle9i database 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.

Performing Numeric Datatype Operations

DB2/400 performs automatic conversions to the numeric datatype of the destination column such as INTEGER, SMALLINT, PACKED DECIMAL, and NUMERIC.  You have no control over the datatype conversion, and the conversion is independent of the datatype of the destination column in the database.

For example, if PRICE is an integer column of the PRODUCT table in DB2/400, 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, the AS/400 server automatically converts the decimal data value of 1.50 to 1.

The gateway returns values bound in the manner 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.

SQL Functions

One of the most important features of the Oracle Enterprise Gateway product family is the ability to provide SQL transparency to the end user and to the application programmer.  Foreign data store SQL functions can be categorized into three areas:

1. Compatible

   Compatible SQL functions have the same meaning and results on both Oracle and foreign data store.  Compatible SQL functions include both column and scalar functions.

   Column Functions

   • AVG

   • COUNT (*) only

   • COUNT (DISTINCT expression)

   • MAX

   • MIN

   • STDDEV

   • SUM

   Scalar Functions

   • ABS

   • COS

   • COSH

   • EXP

   • LENGTH (char) only

   • LN

   • MOD

   • POWER

   • SIN

   • SINH

   • SQRT

   • TAN

   • TANH

   • UPPER

2. Translated

   Translated SQL functions provide the same functionality, but are referenced by different names at the Oracle server and the foreign data store.

   Translated SQL functions include:

   Oracle	DB2/400
   NVL	VALUE
   LOWER	TRANSLATE
   || operator	CONCAT

   Note: Use the passthrough feature for native DB2/400 SQL statements when updating the VALUE function.  Refer to "Passing DB2/400 SQL Statements through the Gateway" for additional information.

3. Compensated

   Compensated SQL functions are advanced SQL functions that are supported by the Oracle database and that cannot be expressed or recognized by the foreign data store.

   SQL compensation in the Oracle Enterprise Gateways enriches the semantics of the native SQL of a remote data source, such as DB2/400.  This important feature of the gateway allows application developers and end-users to leverage the advanced features of the Oracle database.

   Oracle SQL functions that are not listed in the compatible or translated lists above are compensated SQL functions.

Oracle Server SQL Construct Processing

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

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

  The portion of the WHERE clause acceptable for DB2/400 is sent to DB2/400.  The Oracle 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, an unqualified SELECT (without the WHERE clause) is sent for DB2/400, and the Oracle server post-processes the entire WHERE clause.

The Oracle 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/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.  Refer to the IBM reference for DB2/400 for DB2/400 SQL syntax.

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

Oracle Server and DB2/400 Differences

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/400 sets the field to -1 (negative 1) even if more rows are actually deleted.

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

Refer to the IBM reference for DB2/400 for more information about DB2/400 parameter marker restrictions.

Mapping the COUNT Function

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

• COUNT(*)

• COUNT(DISTINCT expression)

• COUNT(ALL expression)

• COUNT(expression)

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

DB2/400 servers support only two options for the COUNT function:

• COUNT(*)

• COUNT(DISTINCT expression)

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 any supported Oracle numeric datatype into a zoned decimal column.  The gateway converts this number into the most suitable datatype.  Data can be retrieved from the AS/400 into any Oracle datatype provided it does not result in a loss of information.

Oracle Data Dictionary Emulation in a DB2/400 Server

The gateway can optionally augment DB2/400 database catalogs with data dictionary views modeled after the Oracle data dictionary.  These views are based upon the dictionary tables in DB2/400, 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 you with an Oracle-like interface to the contents and use of DB2/400.  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.

DB2/400 Special Registers

You can access DB2/400 special registers using the gateway.  During installation of the gateway, a DB2/400 view is created to access special registers.  For example, in order to find out the primary authorization ID being used by the gateway, execute this 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.

Oracle Developer Forms Compatibility

You can use Oracle Developer Forms version 4 or higher to build applications stored in DB2/400.  When you connect to a non-Oracle data source with a gateway product, you have four transaction processing options in Oracle Developer Forms of which you should be aware.  These options include two block properties and two form module properties.  When designing the form, set these options as described below:

Option	Set Value To....
Key Mode block property	Anything except Unique_key
Locking Mode block property	Delayed
Cursor Mode form module	Close_at_commit
Savepoint Mode form module	Off

You should also ensure that at item and block level the primary key is set to TRUE.

For more information refer to your Oracle Forms documentation.