学步园

Building DB2 Stored Procedure Using WSAD V5

Bibhas Bhattacharya, Web Age Solutions Inc.

Introduction

Stored procedures are generally used to:

• Build a library of common and non-trivial queries in a central location.
• Locate read/write intensive queries inside the database server for maximum performance.

In this tutorial we will develop a stored procedure for DB2 using WebSphere Studio Application Developer V5. Various software used in this tutorial are as follows:

• Windows 2000 SP2
• DB2 UDB Enterprise Edition 7.2 PTF9
• JDK 1.2 as installed by default with DB2. This JDK is used by DB2 only. WSAD and WebSphere use their own JDKs
• WSAD V5 with PTF1 and Service Upgrade 5.0.0.2.

Configuring DB2

DB2 must be configured after installation before any development work can begin. First, set up the location of JDK to be used by DB2. Open a DB2 command prompt and issue:

db2 => get dbm cfg

Look for the variable JDK11_PATH. This may be empty or wrongly configured. Set it to the JDK installed by DB2:

db2 => update dbm cfg using JDK11_PATH "c:/Program Files/SQLLIB/java/java12/jdk"

Note: If the JDK path has space in it, run the update command from a db2 command prompt as shown above. You will get an error message if you run it from the DOS prompt.

Next, increase the JVM heap size:

db2 => update dbm cfg using JAVA_HEAP_SZ 4096

Exit DB2 command prompt.

By default DB2 7.2 expects to use JDK 1.1. Change it to JDK 1.2 by running the command:

db2set DB2_USE_JDK12=TRUE

Stop and start DB2:

db2stop
db2start

Create the SAMPLE Database

This tutorial uses the SAMPLE DB2 database. Create the database by running the db2sampl command or from the First Steps GUI. Make sure that you can connect to the database.

Create a Project

Launch WSAD. Decide where do you want to create the stored procedure. We recommend creating a simple Java project to hold all of your stored procedures. This project can later be exported as a JAR file and installed in the production database server machine.

From the menu select File->New->Project. Create a Java project called StoredProcedure.

Define Database and Schema

Switch to the Database perspective. Right click on the DB Servers view and select New Connection.

Enter the following fields:

Connection name: SAMPLE
Database: SAMPLE
User ID and Password:
Database vendor type: DB2 7.2
JDBC Driver: IBM DB2 APP Deriver
Class Location: Make sure that the db2java.zip location is correct.

Click on Finish. Next, right click on the connection name and select Import to Folder and choose the StoredProcedure Java project to import the definition to.

Define Stored Procedure

We will create a simple stored procedure that will fetch the information about an employee's manager. The query for that operation is:

select * from employee where empno=
    (select mgrno from department where deptno=
        (select workdept from employee where empno=?))

In the Data Definition view expand the database connection and the schema and view the Stored Procedures node.

Right click on Stored Procedures and select New->Java Stored Procedure. In the Name field, enter GetManager and click on Next. In the Definition page accept the default and click on Next (this will cause the wizard to add sample database query code in the Java class). In the Parameters page click on Add and add the employee number input parameter as shown below.

Click on Next. In the Options page, enter the Java package name as com.webage.sp.

Click on Finish.

Edit Java Code

Double click on the GetManager stored procedure. System will open GetManager.java. You will see an error message in the code - "The declared package does not match the expected package". This is because WSAD adds the Java file to a wrong folder. To fix it, use a Navigator view and drag and drop the com folder directly under the StoredProcedure project.

Note: You need to do this fix before you first build and install the stored procedure with DB2.

Double click on the stored procedure to open the GetManager.java again. Change the code as follows:

    public static void getManager ( String empNo,
                                    ResultSet[] rs1 ) throws SQLException, Exception
    {
        // Get connection to the database
        Connection con = DriverManager.getConnection("jdbc:default:connection");
        PreparedStatement stmt = null;
        boolean bFlag;
        String sql;

        sql = "select * from employee where empno=(select mgrno from department where deptno=(select workdept from employee where empno=?))";
        stmt = con.prepareStatement( sql );
	stmt.setString(1, empNo);
        bFlag = stmt.execute();
        rs1[0] = stmt.getResultSet();
    }

Save the Java file and fix any compile problem.

Build and Install

Right click on the GetManager stored procedure and select Build. Look at the DB2 Output view to make sure that there are no problems.

Note: Every time you modify and compile the Java class, you must build the store procedure separately.

Unit Test

Right click on the GetManager stored procedure and select Run. Enter 000120 as the empNo input parameter.

Click on OK. Check the DB Output view for the result.