How to Write DB2 Stored Procedures in JAVA

What is Java stored procedure?

The first—and biggest—difference between traditional SQL-style stored procedures and Java stored procedures is that no logic is included in the body of the procedure.

That means that all you need is a definition and to set the relevant options for your procedure; the remainder is covered by the Java class.

Java stored procedure declaration for the MYJAVASP

create procedure MYJAVASP (in input char(6))
specific myjavasp
dynamic result sets 0
language java
parameter style java
no dbinfo
modifies sql data
program type sub
external name 'MYJAVASP!my_JAVASP';

Below are New Options Introduced for a Java stored procedure:

  • Parameter style java: Indicates that Java-style parameter passing will be used. This usually means that parameters are marshaled into arrays for exchange between the stored procedure and the underlying Java. This is essentially transparent to the developer.
  •  [no] dbinfo: dbinfo allows DB2-specific constructs to be passed to the underlying code on calling. no dbinfo is the only supported option for Java stored procedures.
  • [not] fenced [[not] threadsafe]: Fencing is the concept of allowing or preventing external code to run in the same process space as the DB2 instance itself. A fenced procedure is spawned in a separate process to the instance (on the other side of a fence, so to speak). Thread safety in this context is identical to typical Java thread safety.
  • Program type { main | sub }: The program type controls whether the argument passing will be using the argv/argc style typical of main methods or as individual parameters to submethods.
  • external name: The external name is perhaps the most self-evident of the options, specifying the class file and method name for your underlying Java code. Where the routine is part of a package multiple directories deep, use the period to separate directories (for example, ‘MYPACK.MYPROCS.MYJAVASP!my_JAVASP’).

From those descriptions, you can determine that all these modifiers act to control the
programmatic behavior of the Java stored procedure instead of altering its SQL characteristics.

Also read: COBOL stored procedure in DB2 

Read sample java stored procedure.

import java.sql.*;
public class MYJAVASP
public static void my_JAVASP (String inparam) throws SQLException, Exception
// Obtain the calling context's connection details
Connection myConn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement myStmt = myConn.prepareStatement(myQuery);
myStmt.setString(1, inparam);
catch (SQLException sql_ex)
throw sql_ex;
catch (Exception ex)
throw ex;

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.