An application that is written to access information in an Oracle DB can be made to access similar information in DB2/400 by simply qualifying the table, view, stored procedure, or function name with a database link. Using a database link, the access can be made transparent by using synonyms or views of the DB2/400 tables. However, fundamental SQL, data type, and semantic differences exist between the Oracle server and DB2/400. Read this chapter to learn these differences and to find information about developing applications.
To develop applications that access OS/400 data through the gateway, you need to understand the topics in this chapter. The following sections are included:
An application that is written to access information in a DB2/400 database interfaces with an Oracle database. When developing applications, remember the following:
You must define the object in the DB2/400 database to the Oracle application by use of a database link that is defined at the Oracle DB. Your application specifies DB2/400 objects (for example, tables) by qualifying those object names with a database link. For example, suppose you have defined a database link and named it DB2400. And suppose you have a table named EMP in the local Oracle database and a similarly defined EMPS table in DB2/400. And finally, suppose you want to join the two tables to extract some information. The following SQL statement retrieves data from both the Oracle database and DB2/400:
SELECT EMP.EMPNO, EMPS.SALARY FROM EMP, EMPS@DB2400 WHERE EMP.EMPNO = EMPS.EMPNO;
Alternatively, you can define a synonym or a view referencing the DB2/400 server table and access the information without the database link qualification.
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 cannot write to more than one DB2/400 database. A stored procedure call through DB2/400 counts as a write. A call to a user-defined function within a SELECT
, UPDATE
, DELETE
, or INSERT
does not count as a write.
Single SQL statements, using a JOIN
, can refer to tables in multiple Oracle databases, multiple DB2/400 databases, or both.
A connection to the gateway is established through a database link when that link is first used in an Oracle database session. The connection remains active until the session ends. Another session or user can access the same database link but will get a separate connection to DB2/400.
Connections to DB2/400 might be limited by factors such as memory, gateway parameters, or OS/400 server resources.
You can perform read and write functions to a DB2/400 database. SELECT
, INSERT
, UPDATE
, and DELETE
are all valid operations.
A single transaction can use an UPDATE
statement to change multiple Oracle databases. Only one DB2/400 database can be updated within a transaction. If a stored procedure call is directed to DB2/400, then no updates or stored procedure calls can be directed to any other DB2/400 databases during the transaction. Note that a user-defined function is treated differently as Oracle assumes a user-defined function does not write to DB2/400.
A single transaction can use a SELECT
statement to retrieve data from multiple Oracle databases and multiple DB2/400 databases.
The gateway is set up with commitment control set to *CHG
. Refer to the IBM reference for IBM DB2 for OS/400 DB2/400 SQL for more information regarding OS/400 commitment control.
When evaluating and tuning your gateway configuration, you may achieve performance gains by using the Oracle Array Processing interface. An array is a collection of data items, called elements, that are 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 INSERT
s, 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 about array processing usage and implementation in the Oracle application, refer to Oracle Database SQL Reference.
Figure 9-1 Use of Array Size Definition in the DB2/400 Architecture for UPDATE, DELETE, and INSERT
Note:
For performance reasons, Oracle recommends setting the initial Oracle application array size between 10 and 100.The array size of the application for SELECT
is effective between the application and the Oracle Database 10g server. However, the block size of the buffer between the Oracle integrating server and the gateway is governed by two initialization parameters, RPC FETCH SIZE
and RPC FETCH REBLOCKING
. These gateway initialization parameters correspond to the Heterogeneous Services initialization parameters HS_RPC_FETCH_SIZE
and HS_RPC_FETCH_REBLOCKING
, respectively. Figure 9-2 illustrates this. Refer to Oracle Database Administrator's Guide for more information.
With release 10 of the gateway, the Oracle Database 10g server supports fetch reblocking with the RPC FETCH REBLOCKING
parameter. When the value of this parameter is set to *YES
(the default), the block size of the buffer for SELECT
statements is determined by the RPC FETCH SIZE
value. The RPC FETCH SIZE
parameter defines the number of bytes sent with each buffer from the gateway to the Oracle Database 10g server. The buffer might contain one or more qualifying rows from DB2/400. This feature can provide significant performance enhancements, depending on your application design, installation type, and workload. The RPC
FETCH REBLOCKING
and RPC FETCH SIZE
parameters may be changed by using the CHGORATUN
command. Refer to "CHGORATUN, Change Initialization Parameters" for more information.
The array size between the client and the Oracle Database 10g server is still determined by the Oracle application. To ensure optimal performance, Oracle Corporation recommends that you set your Oracle application array size equal to the setting of the gateway ARRAY BLOCK SIZE
parameter. For more information about the gateway ARRAY BLOCK SIZE
parameter, refer to "Setting Optional Parameters". For more information about array processing usage and implementation in your Oracle application, refer to Oracle Database SQL Reference.
Figure 9-2 Array Size Definition in the DB2/400 Architecture for SELECT
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. There are no other ways to invoke a stored procedure.
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
If the application needs to maintain location transparency, then a synonym can be created:
CREATE SYNONYM ORAPROC2 FOR oraproc2@ora2;
where oraproc2
is the procedure that is 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 INSERT
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 by using the Oracle Database Gateway for DB2/400.
Figure 9-4 Using Oracle Stored Procedures with DB2/400
As with 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 an Oracle Database.
Gateway two-phase commit processing also applies to updates to DB2/400 that are 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 because, as far as the Oracle Database Gateway for DB2/400 is concerned, the gateway cannot tell if the stored procedure is actually changing any object in OS/400. So, 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 through 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 OS/400 system).
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. After the stored procedure is defined to DB2/400, the gateway will be 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
From the perspective of the application, executing the DB2/400 stored procedure is no different than invoking a stored procedure at a remote Oracle Database instance.
In order for an application to invoke a DB2/400 stored procedure, it is first necessary to create the DB2/400 stored procedure on the DB2/400 system by using the procedures that are documented in the IBM reference for DB2/400.
After the stored procedure is defined to DB2/400, the gateway is able to access that stored procedure by using a standard PL/SQL call. For example, an employee name, JOHN SMYTHE,
is passed to the DB2/400 stored procedure REVISE_SALARY.
The DB2/400 stored procedure retrieves the salary value from the DB2/400 database to calculate a new yearly salary for JOHN SMYTHE.
The revised salary returned in RESULT
is used to update SAL
in the EMP
table of an Oracle Database as follows:
DECLARE INPUT VARCHAR2(15); RESULT NUMBER(8,2); BEGIN INPUT := 'JOHN SMYTHE'; MYLIB.REVISE_SALARY@DB2400(INPUT, RESULT); UPDATE EMP SET SAL = RESULT WHERE ENAME := INPUT; END;
When the gateway receives a call to execute a DB2/400 stored procedure, it first does a lookup of the procedure in the QSYS2/SYSPROCS
and QSYS2/SYSPARMS
DB2/400 system tables to determine the following:
The stored procedure to execute
The gateway retrieves information from the ROUTINE_SCHEMA
and ROUTINE_NAME
columns of the QSYS2/SYSPROCS
table in order to locate the actual DB2/400 stored procedure code to be invoked by the gateway. Multiple DB2/400 stored procedures can have the same ROUTINE_NAME
, but they must be uniquely identified by the combination of the ROUTINE_SCHEMA
and ROUTINE_NAME
columns of the QSYS2/SYSPROCS
table.
The gateway always receives a qualified stored procedure name from the Oracle database and executes the DB2/400 stored procedure by using this qualified name. If a qualified name is not explicitly identified by the application, then the user ID that is passed over the database link is used to match the value in ROUTINE_SCHEMA
. To ensure that you execute the correct DB2/400 stored procedure, Oracle recommends that you use fully qualified stored procedure names when invoking DB2/400 stored procedures from Oracle PL/SQL.
The parameter list of the stored procedure
When a DB2/400 stored procedure is defined, the attributes of each entry in the argument list for the stored procedure are placed in the QSYS2/SYSPARMS
table. The gateway uses this information to convert the data type of each argument to its corresponding PL/SQL data type. Refer to "Converting DB2/400 Data Types to Oracle Data Types" for gateway data type conversion rules.
After the lookup of the description of the DB2/400 stored procedure is complete, the gateway uses this information to execute the DB2/400 stored procedure.
Following is a list of special considerations for using the procedure feature and DB2/400 stored procedures with the Oracle Database Gateway for DB2/400:
PL/SQL records cannot be passed as parameters when invoking a DB2/400 stored procedure.
The gateway executes the DB2/400 CALL
statement to execute DB2/400 stored procedures. Commitment control (of database changes that are made with the DB2/400 stored procedure) is managed by DB2/400. For more information regarding commitment control, refer to the IBM reference for DB2/400.
The gateway supports both GENERAL
and GENERAL WITH NULLS
linkage conventions of DB2/400 stored procedures.
The GENERAL
linkage convention means that the parameters that are passed to and from DB2/400 stored procedures cannot be null.
The GENERAL WITH NULLS
linkage convention means that parameters passed to and from DB2/400 stored procedures can be null when they are passed using indicator variables.
Oracle Call Interface (OCI) or embedded PL/SQL can be used in host programs to operate on indicator variables. For information about writing DB2/400 stored procedures to use indicator variables, refer to the IBM reference for DB2/400.
To ensure commitment control integrity between the gateway and the stored procedure program, you must create the ILE *PGM
object containing the DB2/400 stored procedure by using the activation group attribute *CALLER
. If you do not use the *CALLER
attribute, then stored procedures execute in a different activation group than the gateway. If the activation group attribute was given as *NEW
, then a forced COMMIT
may occur when the activation group ends (that is, when the program object that is defined by the stored procedure ends). For more information about activation groups, refer to the IBM document for ILE concepts.
If the stored procedure does not change any file or any DB2/400 table on the AS/400, then the program object may be an OPM program object. Otherwise, the program object must be an ILE program object. Failure to adhere to this may cause data to become unsynchronized in the case of a ROLLBACK
or COMMIT
that either may be planned by you or may be forced because of some failure.
The gateway stored function support is an extension of Oracle stored functions. An Oracle stored function is a schema object that logically groups a set of SQL and other PL/SQL programming language statements together to perform a specific task and to return a value to the invoker of that function. Applications may use standard Oracle PL/SQL to invoke a stored function; an Oracle stored function may also be invoked as part of an Oracle SELECT
, INSERT
, DELETE
, or UPDATE
statement.
Similar to Oracle stored procedures, an Oracle stored function can be located in a local instance of Oracle and in a remote instance. Figure 9-6 shows two stored functions, orafunc1
is a function stored in the ORA1
Oracle instance, while orafunc2
is a procedure stored in the ORA2
Oracle instance.
Figure 9-6 Calling Oracle Stored Function in a Distributed Oracle Environment
If the application needs to maintain location transparency, then a synonym can be created:
CREATE SYNONYM ORAFUNC2 FOR orafunc2@ora2;
where orafunc2
is the function that is stored in ORA2.
After this synonym is created, the application no longer needs to use the database link specification for invoking the stored function at the remote Oracle Database.
In Figure 9-6, the reference to function orafunc2
is used to call that same function in the ORA2
instance. In the same way, Oracle can be used to access DB2/400 user-defined functions through the gateway.
In Figure 9-7, empfunc is an Oracle stored function that subsequently accesses data in DB2/400 using the gateway.
Figure 9-7 Using Oracle Stored Functions with DB2/400
As with the Oracle database, standard PL/SQL is used to create the function. Standard PL/SQL may be used to execute the function or the function may be executed with a reference in a SQL command such as SELECT
, UPDATE
, INSERT
, or DELETE
. There is no difference in the gateway case except that the stored function is accessing DB2/400 rather than the Oracle database.
Oracle assumes that the underlying user defined function on the DB2/400 does not change any data on the AS/400 side if the stored function is referenced in a SQL command. One explicitly declares that a stored function does not change the AS/400 data by specifying that function name in an IFS file named /HOME/
MYINST
/RDBMS/ADMIN/CALL_NAMES.ORA
, where, MYINST
is your instance name. If you attempt to use an AS/400 stored function (DB2/400 user defined function) in a SQL command without specifying that name in the above IFS file, then you will get the Oracle error 'ORA-06571: function xxxxx does not guarantee not to update database'
Since it is assumed that AS/400 stored functions do not change the database, gateway two-phase commit processing does not result from calls made to these stored functions. To effect gateway two-phase commit processing, changes must be made via a stored procedure or via an INSERT
, DELETE
, or UPDATE
SQL command directed to an AS/400 object via the gateway.
Note:
The parameters to a DB2/400 user defined function are all of modeIN
. INOUT
or OUT
parameters are not allowed.The procedural feature of the gateway enables execution of DB2/400 native stored functions (also called user defined functions, or UDFs). In other words, the stored function are no longer defined in the Oracle Database, but instead are defined to DB2/400. Oracle PL/SQL may be used to directly call these functions within a PL/SQL block. Or the functions may be called as part of a SELECT
, INSERT
, DELETE
, or UPDATE
SQL command.
If the stored function is referenced within a SQL command, then the user must specify its name in the IFS file /HOME/
MYINST
/RDBMS/ADMIN/CALL_NAMES.ORA
, where, MYINST
is your instance name. If you attempt to use an AS/400 stored function (DB2/400 user defined function) in a SQL command without specifying that name in the above IFS file, then you will get the Oracle error 'ORA-06571: function xxxxx does not guarantee not to update database'. After changing this file, it is recommended that you bring down and then re-start the instance.
Other than the declaration of the function name in the above IFS file, the gateway does not require any other special definitions in order to invoke the DB2/400 stored function.
In Figure 9-8, an Oracle application calls the empfunc
stored function that is defined in DB2/400.
Figure 9-8 Executing DB2/400 Stored Functions
From the perspective of the application, executing the DB2/400 stored function is no different that invoking a stored function at a remote Oracle instance.
In order for an application to invoke a DB2/400 stored function, it is first necessary to create the DB2/400 stored function on the DB2/400 system by using the procedures that are documented in the IBM reference for DB2/400.
Next, if you are going to reference that function in a SQL DML statement (such as SELECT
, INSERT
, DELETE
, or UPDATE
), then you must specify the referenced name in the IFS file /HOME/
MYINST
/RDBMS/ADMIN/CALL_NAMES.ORA
, where, MYINST
is your instance name. If you attempt to use an AS/400 stored function (DB2/400 user defined function) in a SQL command without specifying that name in the above IFS file, then you will get the Oracle error 'ORA-06571: function xxxxx does not guarantee not to update database'.
After doing the above, the gateway is able to access the stored function using a standard PL/SQL call syntax or via a reference in a SQL DML statement. For example, suppose our DB2/400 stored procedure gives us what should be a revised-salary given a specific employee name. The employee name, JOHN SMYTHE
, is passed to the DB2/400 stored function REVISE_SALARY_FUNC
. The DB2/400 stored function retrieves the salary value from the DB2/400 database and calculates a new revised salary for our specific employee. Since REVISE_SALARY_FUNC
is a function, it returns this salary value as the return value of the function.
The revised salary returned in RESULT
is used to update the SAL
column in the EMP
table of the Oracle Database as follows:
DECLARE INPUT VARCHAR2(15); RESULT NUMBER(8,2); BEGIN INPUT := 'JOHN SMYTHE'; RESULT := MYLIB.REVISE_SALARY_FUNC@DB2400(INPUT); UPDATE EMP SET SAL = REUSLT WHERE ENAME := INPUT; END;
Note:
The call to theREVISE_SALARY_FUNC
function may also be done in a SELECT
statement. That SELECT
statement would be:
SELECT MYLIB.REVISE_SALARY_FUNC@DB2400(INPUT) INTO RESULT FROM OTGDB2.DUAL@DB2400;
When the gateway receives a call to execute a DB2/400 stored function, it first does a lookup of the function name in the QSYS2/SYSFUNCS
and QSYS2/SYSPARMS
DB2/400 system tables to determine the following:
The stored function to execute
The gateway retrieves information from the ROUTINE_SCHEMA
and ROUTINE_NAME
columns of the QSYS2/SYSFUNCS
table in order to locate the actual DB2/400 stored function code that is to be invoked by the gateway. Multiple DB2/400 stored functions can have the same ROUTINE_NAME
, but they must be uniquely identified by the combination of the ROUTINE_SCHEMA
and ROUTINE_NAME
columns of the QSYS2/SYSFUNCS
table.
The gateway always receives a qualified stored function name from the Oracle Database server and executes the DB2/400 stored function by using this qualified name. If a qualified name is not explicitly identified by the application, the user ID that is passed over the database link is used to match the value in ROUTINE_SCHEMA
. To ensure that you execute the correct DB2/400 stored function, Oracle recommends that you use fully qualified stored function names when invoking DB2/400 stored functions from Oracle PL/SQL and from SQL DML commands.
The parameter list of the stored functions
When a DB2/400 stored function is defined, the attributes of each entry in the argument list for that stored function are placed in the QSYS2/SYSPARMS
table. The gateway uses this information to convert the data type of each argument to its corresponding PL/SQL data type. Refer to "Converting DB2/400 Data types to Oracle Data types" on page 9-11 for gateway data type conversion rules.
After the lookup of the description of the DB2/400 stored function is complete, the gateway uses this information to execute the DB2/400 stored function.
Following is a list of special considerations for use the procedural feature for stored functions with the Oracle Database Gateway for DB2/400:
PL/SQL records cannot be passed as parameters when invoking a DB2/400 stored function.
No matter the context in which the original function call was done, that is either as a PL/SQL function call or as a function call in a SQL DML statement, the gateway executes the function as part of a SELECT
. That is, it does:
SELECT func_call(func_args, ...) FROM OTGDB2.DUAL;
The gateway supports both GENERAL
and GENERAL WITH NULLS
linkage conventions of DB2/400 stored functions.
The GENERAL
linkage convention means that the parameters that are passed to DB2/400 stored functions cannot be NULL
.
The GENERAL WITH NULLS
linkage convention means that parameters passed to DB2/400 stored functions can be NULL
when they are passed using indicator variables.
Oracle Call Interface (OCI) or embedded PL/SQL can be used in host programs to operate on indicator variables. See the appropriate DB2/400 IBM references on how to detect NULL
s passed as arguments to a DB2/400 user defined functions.
The passthrough SQL feature allows an application developer to send a SQL statement directly to DB2/400 without the statement being interpreted by the Oracle database. DB2/400 SQL statements that are supported by the gateway through the passthrough facility 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 that are 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 Database Gateway for DB2/400 enables retrieval of result sets from queries that are 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.
To execute a passthrough SQL statement by using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE
function, use the following syntax:
number_of_rows = DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@dblink ('DB2/400_sql');
where:
number_of_rows
is a variable that is assigned the number of rows that are affected by the passthrough SQL execution. For DDL statements, a zero is returned for the number of rows affected.
dblink
is the name of the database link that is used to access the gateway.
DB2/400_sql
is a valid DB2/400 nonquery SQL statement (except CONNECT
, COMMIT
, or ROLLBACK
). DB2/400 SQL statements that cannot be dynamically prepared are rejected by DB2/400. The SQL statement that is passed by the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE
function must be a character string. For more information regarding DB2/400 SQL statements, refer to the IBM reference for DB2/400.
Following are examples of the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE
function.
Refer to the next section, "Using Passthrough Statements to Create Tables", for more information.
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; /
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; /
If you use DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE
to create a DB2/400 table, then one of the following is required for journaling:
If neither of the above is true, then you will receive an error message that the table has been created but cannot be journaled. In this case, you can disregard the error message and journal the file manually. For more information on journaling, refer to "Step 2: Verify Journaling".
Oracle Database Gateway for DB2/400 provides a facility to retrieve results sets from a SELECT
SQL statement that is issued through passthrough. In the example below, the values for a single field are selected from all the rows of the foreign table PT_TABLE
, and these values are inserted into the local Oracle Database table PT_TABLE_LOCAL
. Refer to Oracle Database Administrator's Guide for additional information.
DECLARE CRS binary_integer; RET binary_integer; VAL VARCHAR2(10) BEGIN CRS:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@gtwlink; DBMS_HS_PASSTHROUGH.PARSE@gtwlink(CRS,'SELECT NAME FROM PT_TABLE'); BEGIN RET:=0; WHILE (TRUE) LOOP RET:=DBMS_HS_PASSTHROUGH.FETCH_ROW@gtwlink (CRS,FALSE); DBMS_HS_PASSTHROUGH.GET_VALUES@gtwlink (CRS,1,VAL); INSERT INTO PT_TABLE_LOCAL VALUES (VAL); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN DBMS_OUTPUT.PUT_LINE('END OF FETCH'); DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@gtwlink(CRS); END; END; END; /
To move data between applications and the underlying database, the gateway binds data values from a host variable or literal to a data type that is understood by the underlying database.
Oracle tools expect Oracle data types. Consequently, the gateway maps values from DB2/400 servers into appropriate Oracle data types before passing these values back to the application or Oracle tool. The following table lists the data type mapping and restrictions:
Table 9-1 Data Type Mapping and Restrictions
DB2/400 | Oracle | Criteria | Comments |
---|---|---|---|
|
1 £ N £ 255 255 < N £ 4000 4000 < N |
Maximum length of a DB2/400 |
|
|
|
N £ 4000 4000 < N £ 32740 |
Maximum length of a DB2/400 |
|
|
N £ 4000 4000 < N £ 32740 |
Maximum length of a DB2/400 |
|
|
1 £ N £ 255 255 < N £ 32740 |
Maximum length of a DB2/400 |
|
|
Refer to "Performing Date and Time Operations" . |
|
|
|
||
|
|
||
|
|
See note below table |
|
|
|
Based on See note below table |
|
|
|
||
|
|
||
|
|
||
|
|
1 £ N £ 127 127 < N £ 16370 |
Maximum length of a DB2/400 |
|
|
1 £ N £ 2000 2000 < N |
Maximum length of a DB2/400 |
|
|
1 £ N £ 2000 2000 < N |
Maximum length of a DB2/400 |
|
|
1 £ N £ 21 |
|
|
|
22 £ N £ 53 |
|
|
|
Packed decimal in OS/400 |
|
|
|
Zoned decimal in OS/400 |
|
|
|
||
|
|
||
|
|
# Table Note: TIMESTAMP
appears in two rows in the table. The data type that is used depends on the value in the ORATIMSTMP
DataArea. If the DataArea is missing, then the CHAR(26)
value is used. If the DataArea exists and has value ON
, then the Oracle TIMESTAMP
data type is used.
* 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 Database column with N
, replace 127
with 255
, and replace 2000
with 4000
. If the column CCSID
is 13488
, and if the Oracle Database is expecting single-byte data, then (VAR)GRAPHIC
s are treated as (VAR)CHAR
s.
Oracle database no longer supports NCHAR
or NVARCHAR2
data types. With Oracle Database 10g server, the Oracle Database Gateway for DB2/400 supports double-byte data types (GRAPHIC
and VARGRAPHIC
in DB2/400 terms) in a manner similar to the manner in which they were supported in version 4 of the Oracle Database Gateway for DB2/400. That is to say, in Oracle tools and applications, they are supported as CHAR
and VARCHAR2
data types of the appropriate size.
To support DB2/400 columns of GRAPHIC
or VARGRAPHIC
type, you must set NLS_LANG
to a language in which the character set part corresponds to the CCSID of the (VAR)GRAPHIC
column. This way, if the column is CCSID 933
, then the character set part of NLS_LANG
must be set to KO16DBCS
(refer to Appendix B, "Globalization 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.
The Oracle Database Gateway for DB2/400 allows you to use DBCS (Double Byte Character Set) data under limited conditions, which include a risk for data integrity.
This version of the Oracle Database 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 Database 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:
With no options specified, the user must ensure that any strings to be INSERT
ed into (VAR)GRAPHIC
columns are in their "wide" form on the ASCII computer. The string cannot 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 that are represented by constants or for values that are represented by bind-variables.
Specifying the FORCE_SB
option causes all single-byte characters to be translated to their corresponding double-byte values on input — through either an INSERT
or a bind-variable. The entire string is then INSERT
ed 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. Because 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, double-width 'xx'
character.
When SELECT
ing from this column, on the client, you would get exactly what you INSERT
ed, 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 INSERT
ed into the column through 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 no ORADBMBOPT
Data Area (or Environment variable) is present, 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_SB
" 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.
The gateway performs all character string comparisons, concatenations, and sorts by using the data type of the referenced columns (for example, fixed or variable length). The gateway determines the validity of character string values that are passed by applications and automatically converts character strings from one data type to another. It also automatically converts between character strings and dates when necessary.
In contrast, the Oracle Database 10g server processes character strings as variable length character strings. The Oracle database performs character string comparisons, concatenations, and sorts by using variable length character string representations. The Oracle database never pads or truncates character strings. The Oracle database also automatically converts among numbers, character strings, and dates when necessary.
The gateway binds character string data values from host variables as fixed length character strings. The bind length is the length of the character string data value. This conversion is performed on every bind.
DB2/400 performs automatic conversions to the character string data type (for example, fixed length and variable length) of the destination column. You have no control over the conversion. The conversion might be independent of the data type of the destination column in the database. For example:
UPDATE EMP@AS400 SET ENAME = 'BLAKE' WHERE EMPNO = 7654;
If ENAME
is a CHAR(9)
, fixed-length column in the ENAME
table, then the update inserts 'BLAKE
'
as the last name of employee number 7654.
BLAKE
is padded with four trailing spaces by the host database to make it conform to the fixed length definition of the column.
The gateway returns data bound in the manner that is requested by the application. For character strings, the format is fixed length string.
The DB2/400 VARCHAR
data type can be from 1 to 32740 bytes in length. This data type is converted to an Oracle VARCHAR2
data type if it is between 1 and 4000 characters in length. If it is between 4001 and 32740 characters in length, then it is converted to an Oracle LONG
data type.
The Oracle LONG
data type can be from 1 byte to 2 GB in length. Because the DB2/400 VARCHAR
data type can be no longer than 32740 bytes, you receive an error message if you attempt to insert data longer than 32740 bytes into a DB2/400 VARCHAR
or LONG
VARCHAR
data type.
The implementation of date and time data differs significantly in DB2/400 and the Oracle database. The Oracle database has a single date data type, DATE
, that can contain both calendar date and time-of-day information. DB2/400 supports the following date and time data types:
DATE
is the calendar date only.
TIMESTAMP
is a numerical value that combines a calendar date and time-of-day with microsecond resolution.
There is no mechanism that translates the IBM TIME
and TIMESTAMP
data to Oracle DATE
data. An application must process TIME
data types in the Oracle CHAR
format with a length of 8 bytes. An application must process the TIMESTAMP
data type in the Oracle CHAR
format with a length of 26 bytes.
An application reads TIME
and TIMESTAMP
columns as character strings and converts portions or subsets of the string to perform numerical operations. TIME
and TIMESTAMP
values can be sent to DB2/400 as character literals or bind variables of the appropriate length and format.
Oracle DATE
data types and IBM DATE
data types are mapped to each other. If an IBM DATE
is queried, then it is converted to an Oracle DATE
with a zero (midnight) time of day. If an Oracle DATE
is processed against an IBM DATE
column, then the date value is converted to the IBM DATE
format and any time value is discarded. DB2/400 columns of DATE
or TIME
data type are assumed to be in ISO format. For DATE
, the column format is "yyyy-mm-dd", and for TIME
columns, the format is "hh.mm.ss". TIME is in a 24-hour format.
Character representations of dates are different in Oracle format and DB2/400 format. When an Oracle application SQL statement contains a date literal or conveys a date through a character bind variable, the gateway must convert the date to a DB2/400 compatible format.
The gateway does not automatically recognize when a character value is going to be processed against an IBM DATE
column. Applications are required to distinguish character date values by enclosing them with the Oracle TO_DATE( )
function notation.
For example, if EMP
is a synonym or view that is accessing data in DB2/400, then instead of using the following SQL statement:
SELECT * FROM EMP@AS400 WHERE HIREDATE = '03-MAR-81'
you must use the following syntax:
SELECT * FROM EMP@AS400 WHERE HIREDATE = TO_DATE('03-MAR-81')
In a programmatic interface program using a character bind variable for the qualifying date value, you must use the following SQL statement:
SELECT * FROM EMP@AS400 WHERE HIREDATE = TO_DATE(:1)
This SQL notation does not affect SQL statement semantics when the statement is executed against an Oracle table. The statement remains the same across Oracle and DB2/400 data stores.
The DATE
requirement does not apply to input bind variables in Oracle date seven byte binary format. The gateway recognizes such values to be dates.
Note:
Special considerations exist for using date or time data types in stored procedures with DB2/400. Refer to "Known Problems" for more information.Oracle recommends that you set the Oracle Database 10g server default NLS_DATE_FORMAT
parameter to a format including a four-digit year. Use the TO_DATE
function to enter dates in the twenty-first century. You can use any date format which includes a four-character field for the year. For example, TO_DATE('2008-07-23', 'YYYY-MM-DD')
can be used in any SELECT
, INSERT
, UPDATE
, or DELETE
statement.
DB2/400 performs automatic conversions to the numeric data type of the destination column such as BIGINT
, INTEGER
, SMALLINT
, PACKED DECIMA
L, and NUMERIC
. You have no control over the data type conversion, and the conversion is independent of the data type of the destination column in the database.
For example, if PRICE
is an integer column of the PRODUCT
table in DB2/400, then the update shown in this example inaccurately sets the price of an ice cream cone to $1.00,
because DB2/400 automatically converts a double-precision floating point to an integer:
UPDATE PRODUCT@AS400 SET PRICE = 1.50 WHERE PRODUCT_NAME = 'ICE CREAM CONE ';
Because PRICE
is an integer (in this example), the DB2/400 server automatically converts the decimal data value of 1.50 to 1.
The gateway returns values that are bound in the manner that is requested by the Oracle application. For numbers, the format is typically an Oracle number, limited to a certain number of digits of precision by the application.
One of the most important features of the Oracle Database Gateway product family is the ability to provide SQL transparency to the user and to the application programmer. Foreign data store SQL functions can be categorized into three areas (compatible, translated, and compensated):
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.
AVG
COUNT (*)
only
COUNT (DISTINCT expression)
MAX
MIN
STDDEV (one argument)
SUM
ABS
COS
COSH
EXP
LENGTH (char) only
LN
MOD
POWER
SIN
SINH
SQRT
TAN
TANH
UPPER
VARIANCE (one argument)
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:
Note:
Use the passthrough feature for native DB2/400 SQL statements when using theVALUE
, TRANSLATE
, or CONCAT
functions. Refer to "Passing DB2/400 SQL Statements Through the Gateway" for additional information.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 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 users to leverage the advanced features of the Oracle Database.
Oracle SQL functions that are not listed in the compatible or translated lists includer earlier are compensated SQL functions.
Special circumstances and restrictions for processing.
A SELECT
without the FOR UPDATE
clause can be handled in one of three ways:
If the entire WHERE
clause of the SELECT
statement is acceptable syntax for DB2/400, then it is given to DB2/400 to perform.
If part, but not all, of the WHERE
clause of the SELECT
statement uses features that are not available in DB2/400, then the WHERE
clause is split between the DB2/400 system and the Oracle database.
The portion of the WHERE
clause that is acceptable for DB2/400 is sent to DB2/400. The Oracle database post processes the results of the DB2/400 SELECT
and applies the Oracle-specific WHERE
clause elements. This results in DB2/400 doing as much of the WHERE
clause as possible.
If the entire WHERE
clause is not acceptable for DB2/400, then an unqualified SELECT
(without the WHERE
clause) is sent for DB2/400, and the Oracle database post processes the entire WHERE
clause.
The Oracle database post processes SELECT
statements without the FOR UPDATE
clause. Most Oracle SELECT
statements are supported. One exception is the CONNECT BY
clause.
DB2/400 must process the entire SELECT FOR UPDATE
, INSERT
, and DELETE
clauses. The Oracle server cannot post process these clauses. Only SQL that is a common subset of Oracle and DB2/400 SQL can be used with these statements.
The following rules exist for the use of SELECT FOR UPDATE
, INSERT
, and DELETE
clauses:
Only Oracle syntax that is also valid for DB2/400 can be used. For DB2/400 SQL syntax, refer to the IBM reference for DB2/400.
The following Oracle functions are supported with all options:
AVG
MAX
MIN
SUM
TO_DATE
The NOWAIT
option of the FOR UPDATE
clause of the SELECT
statement is not supported.
Although DB2/400 requires a list of column names in the FOR UPDATE
clause, the unqualified Oracle syntax FOR UPDATE
with no column names is accepted. The gateway derives the column list from the SELECT
result column list.
Differing functions and operations between the two databases are explained below.
Oracle bind variables become DB2/400 parameter markers when used with the gateway. Therefore, the bind variables are subject to the same restrictions as DB2/400 parameter markers. For example, the following statement fragments are not allowed:
WHERE :x IS NULL WHERE :x = :y
For more information about DB2/400 parameter marker restrictions, refer to the IBM reference for DB2/400.
A zoned decimal field is described as packed decimal on an Oracle database. However, an Oracle application such as a Pro*C program can insert any supported Oracle numeric data type into a zoned decimal column. The gateway converts this number into the most suitable data type. Data can be retrieved from DB2/400 into any Oracle data type provided it does not result in loss of information.
The gateway can optionally augment DB2/400 database catalogs with data dictionary views that are modeled after the Oracle Database data dictionary. These views are based on the dictionary tables in DB2/400, presenting the catalog information in views familiar to Oracle Database users.
The views that are created during the installation of the gateway automatically limit the data dictionary information that is presented to each user, based on the privileges of that user.
The gateway data dictionary views provide you with an Oracle-like interface to the contents and use of the DB2/400 data dictionary. Some of these views are required by Oracle products.
You can query the gateway data dictionary views to see the objects in DB2/400 and to determine the authorized users of those objects.
Refer to Appendix C, "Data Dictionary Views" for descriptions of DB2/400 catalog views.
You can access DB2/400 special registers by using the gateway. During installation of the gateway, a DB2/400 view is created in order to enable access to special registers. For example, to find out the primary authorization ID that is being used by the gateway, the following command from your application:
SELECT CURRENT_USER FROM OTGDB2.OTGREGISTER@DB2400
where OTGDB2
is the default qualifier of the OTGREGISTER
view, and DB2400
is the name of a database link to the gateway.
You can use Oracle Developer Forms version 4 or higher to build applications that are stored in DB2/400. When you connect to a non-Oracle data source with a gateway product, you should be aware of four transaction processing options in Oracle Developer Forms. These options include two block properties and two form module properties. When designing the form, set these options as described in the following table:
Table 9-3 Transaction Processing Options in Oracle Developer Forms
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.