Stored procedure support - IBM DB2

DB2 supports the Java stored procedure, which can be run on the server side by the application. Follow these guidelines to create the Java stored procedure:

  • The method in the class, which will map to the stored procedure, must be defined as public static void method.
  • Output and InOut parameters must be set up as a single element array.

To create, register, and use a Java stored procedure in an application, follow these steps:

  1. Write a Java program with the stored procedure method. The method should be public static void.
  2. Compile the program using the Java compiler.
  3. Copy the .class file of the program to sqllib/function directory on server side. If you declare a class to be part of the Java package, create subdirectories in the function directory that correspond to the fully qualified class names and place the related class files in the corresponding subdirectories. You can create a JAR file too in case you want to copy a set of stored procedure class files.
  4. Register the stored procedure to the database using the CREATE PROCEDURE command.
  5. Call the stored procedure from the client program.
  6. Let us register the stored procedure to create an XML document for the porder column of the PURCHASEORDER table.

Step 1
Below example shows the stored procedure. Make sure that the class should be public and extends the StoredProc class. The method in the class should be public void. The OUT parameter values are set using the set function.

Stored procedure

Step 2
Compile the above class using the javac compiler.

Step 3
Copy the CreateOrder.class to the sqllib/function directory.

Step 4
Register the stored procedure using the following command:

DYNAMIC RESULT SETS 0 NOT DETERMINISTIC LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO DBINFO FENCED THREADSAFE READS SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'CreateOrder.createOrder'

Step 5
Call the stored procedure from the client program. The createPorder method of the application calls this stored procedure to create the PORDER column value for the PURCHASEORDER table. You can call this stored procedure from the command line using the following command:

where poid is the purchase order ID and cart is the table name. Before calling the stored procedure, make sure that the poid value does not exist in the PURCHASEORDER table and CART table has the PID and QUANTITY columns of varchar(10) and int data type respectively. Also, populate the CART table with product ID and quantity values. PID inserted in this table should exist in the PRODUCT table. The stored procedure returns the XML value in its output parameter denoted by the question mark (?) while calling the stored procedure.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics