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 in the SBCS or Unicode database environment. When you are in the DBCS environment, the default value is YES_DBCS_GRAPHIC_TO_CHAR.
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.
   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