Setting up DB2 for Oracle application enablement

Oracle applications can be enabled to work with DB2® data servers when the DB2 environment is set up appropriately.

1. Open a DB2 command window.
2. Start the DB2 database manager.

   db2start

3. Set the DB2_COMPATIBILITY_VECTOR registry variable to the hexadecimal value that enables the compatibility features that you want to use. To take full advantage of these DB2 compatibility features, set the value to ORA. This is the recommended setting.

   db2set DB2_COMPATIBILITY_VECTOR=ORA

4. Set the DB2_DEFERRED_PREPARE_SEMANTICS registry variable to YES to enable deferred prepare support. If the DB2_COMPATIBILITY_VECTOR registry variable is set to ORA, and the DB2_DEFERRED_PREPARE_SEMANTICS registry variable is not set, a default value of YES is used. However, it is recommended that the DB2_DEFERRED_PREPARE_SEMANTICS registry variable be explicitly set to YES.

   db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

5. Issue the db2stop command and the db2start command to stop and then restart the database manager.

   db2stop
   db2start

6. Create your DB2 database by issuing the CREATE DATABASE command. The database should be created as a UNICODE database, which is the default. For example, to create a database named DB, issue the following command:

   db2 CREATE DATABASE DB

7. Optional: Run a CLPPlus or command line processor (CLP) script (script.sql) to verify that the database supports PL/SQL statements and data types. The following CLPPlus script creates a simple procedure and then calls that 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