Setting up the DB2 environment for Oracle application enablement

You can reduce the time and complexity of enabling Oracle applications to work with DB2® data servers if you set up the DB2 environment appropriately.

To enable Oracle applications to work with DB2 data servers:

1. In a DB2 command window, start the DB2 database manager by issuing the following command:

   db2start

2. Set the DB2_COMPATIBILITY_VECTOR registry variable to one of the following values:
   • The hexadecimal value that enables the specific compatibility feature that you want to use.
   • To take advantage of all the DB2 compatibility features, ORA, as shown in the following command. This is the recommended setting.

   db2set DB2_COMPATIBILITY_VECTOR=ORA

3. Enable deferred prepare support by setting the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to YES, as shown:

   db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

   If you set the DB2_COMPATIBILITY_VECTOR registry variable to ORA and do not set the DB2_DEFERRED_PREPARE_SEMANTICS registry variable, a default value of YES is used. However, it is recommended that you explicitly set the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to YES.
4. Stop the database manager by issuing the db2stop command:

   db2stop

5. Start the database manager by issuing the db2start command:

   db2start

6. Create your DB2 database by issuing the CREATE DATABASE command. By default, databases are created as Unicode databases (this is also the preferred setting). For example, to create a database that is named DB, issue the following command:

   db2 CREATE DATABASE DB

7. Optional: Run a Command Line Processor Plus (CLPPlus) or command line processor (CLP) script (for example, script.sql) to verify that the database supports PL/SQL statements and data types. The following CLPPlus script creates and then calls a simple procedure:

   CONNECT user@hostname:port/dbname;
   
   CREATE TABLE t1 (c1 NUMBER);
   
   CREATE OR REPLACE PROCEDURE testdb(num IN NUMBER, message OUT VARCHAR2)
   AS
   BEGIN
    INSERT INTO t1 VALUES (num);
   
    message := 'The number you passed is: ' || TO_CHAR(num); 
   END;
   /
   
   CALL testdb(100, ?);
   
   DISCONNECT;
   EXIT;

   To run the CLPPlus script, issue the following command:

   clpplus @script.sql  

   The following example shows the CLP version of the same script. This script uses the SET SQLCOMPAT PLSQL command to enable recognition of the forward slash character (/) on a new line as a PL/SQL statement termination character.

   CONNECT TO DB;
   
   SET SQLCOMPAT PLSQL;
   
   -- Semicolon is used to terminate
   -- the CREATE TABLE statement:
   CREATE TABLE t1 (c1 NUMBER);
   
   -- Forward slash on a new line is used to terminate
   -- the CREATE PROCEDURE statement:
   CREATE OR REPLACE PROCEDURE testdb(num IN NUMBER, message OUT VARCHAR2)
   AS
   BEGIN
    INSERT INTO t1 VALUES (num);
   
    message := 'The number you passed is: ' || TO_CHAR(num); 
   END;
   /
   
   CALL testdb(100, ?);
   
   SET SQLCOMPAT DB2;
   
   CONNECT RESET;

   To run the CLP script, issue the following command:

   db2 -tvf script.sql