StarQuest Technical Documents

Using StarSQL for Java to Access DB2 from an Oracle Stored Procedure

Last Update: 28 February 2007
Product: StarSQL for Java
Version: 2.31 or later
Article ID: SQV00SJ003

Abstract

This document describes how to use the StarSQL for Java driver to access a DB2 database from an Oracle database.     

Solution

The basic steps for connecting an Oracle database to a DB2 database using StarSQL for Java are to:

1. Copy the StarSQL for Java license file to the Oracle JVM library directory.
2. Load the StarSQL for Java driver so it is available to the Oracle database.
3. Create a Java application that uses StarSQL for Java to access DB2. 
4. Load the Java application classes into the Oracle database.
5. Set permissions for writing log files and connecting to the DB2 host and StarLicense server.
6. Create an Oracle stored procedure that executes a method in the Java application.
7. Execute the Oracle stored procedure.

The following sections detail the steps for using the StarSQL for Java driver to connect to DB2 from an Oracle 10i database that is configured to use the Java Virtual Machine (JVM) version 1.4 or later.  The procedures for other versions of the Oracle database may differ slightly, such as the location where the software is installed, but should be similar.

Copy the Starlicense.properties file to the Oracle JVM library Directory

The StarSQL for Java license file, Starlicense.properties, must be copied to a directory that can located by the Oracle JVM.  Be sure to configure the Starlicense.properties file with the appropriate information before copying the file.

1. Copy the Starlicense.properties file from the StarSQL for Java program directory, which is typically C:\Program Files\StarQuest\StarSQL_Java, to the Oracle Java library directory located by a path such as C:\oracle\product\10.2.0\db_1\javavm\lib.

Load the StarSQL for Java Driver into the Oracle Database

1. Open a Windows command prompt.
2. Execute the following loadjava command in the command window.  Modify the userid, password, and path to the StarSQL_JDBC.jar file as necessary.  The userid should be an Oracle userid with sufficient privileges to perform the functions described in this document.

   loadjava -u userid/password -verbose -resolve -resolver "((* PUBLIC) (* SYS) (* SYSTEM) (* -))" -grant PUBLIC "C:\Program Files\StarQuest\starsql_java\StarSQL_JDBC.jar"

Create the Java Application

1. Open a text editor or a Java IDE.
2. Copy the sample Java code provided by StarQuest into an empty text or Java file.
3. Modify the two connection URLs in the DriverManager.getConnection statements to use DB2 connection values appropriate for your environment, and set the userName and password string values to a valid DB2 user account.
4. Save the file as execQuery.java, and compile the application.  Note the location of the execQuery.class file that is created during the compile operation. 

Load the Java Application Class into the Oracle Database

1. Open a Windows command prompt.
2. Execute the following loadjava command in the command window.  Modify the userid, password, and path to the execQuery.class file as necessary.

loadjava -u userid/password -verbose -resolve -resolver "((* PUBLIC) (* SYS) (* SYSTEM) (* -))" -grant PUBLIC C:\java\execQuery.class

Set Oracle Permissions

1. Launch the Oracle SQL Plus application.
2. Execute the following statement to allow the Oracle database to connect to the DB2 host.  Modify the db2host.domain.com value with the network name or IP address of the DB2 host server.

   call dbms_java.grant_permission( 'SYSTEM', 'SYS:java.net.SocketPermission','db2host.domain.com', 'connect,resolve' );

3. Execute the following statement to allow the Oracle database to access the Starlicense.properties file.  Modify the "java.home" value with the complete path as determined in step 1 of the first section of this document.

   call dbms_java.grant_permission( 'SYSTEM', 'SYS:java.io.FilePermission', 'C:\oracle\product\10.2.0\db_1\javavm\lib\Starlicense.properties', 'read' );

4. Execute the following statement to allow the Oracle database to connect to the StarLicense server (if required).  Modify the starlic value with the network name or IP address of the StarLicense server.

call dbms_java.grant_permission( 'SYSTEM', 'SYS:java.net.SocketPermission','starlic', 'connect,resolve' );

Create and Execute the Oracle Stored Procedure

1. Launch the Oracle SQL Plus application.
2. Execute the following statement to create the stored procedure.

   create or replace procedure do_Query(sqlString varchar2, db varchar2)
   as language java
   name 'execQuery.doQuery(java.lang.String,java.lang.String)';
   /

Execute the Oracle Stored Procedure

1. Launch the Oracle SQL Plus application.
2. Execute the following statement to allow server output to be displayed in the SQL Plus window.

   set serveroutput on
   call dbms_java.set_output(10000);

3. Execute the stored procedure, modifying the select SQL statement to supply a valid DB2 table to query.

begin
do_Query('select * from TABLE','Production');
end;
/

Unload the StarSQL for Java Driver from the Oracle Database Before Updating

If you wish to update the StarSQL for Java driver with a newer version, use the dropjava command to unload the driver from the Oracle database:

1. Open a Windows command prompt.
2. Execute the following dropjava command in the command window. Modify the userid, password, and path to the StarSQL_JDBC.jar file as necessary.

   dropjava -u userid/password -verbose -resolve -resolver "((* PUBLIC) (* SYS) (* SYSTEM) (* -))" -grant PUBLIC "C:\Program Files\StarQuest\starsql_java\StarSQL_JDBC.jar"

3. Use the loadjava command, as described above, to load the new version of the StarSQL for Java driver into the Oracle database.