Solve common problems with DB2 UDB Java stored procedures

Review the basic setup required to run Java™ stored procedures on IBM® DB2® Universal Database™ (DB2 UDB), then examine common problems developers may experience in the course of developing and deploying Java routines. The authors show working examples and common solutions to help you get up and running, problem free, with your own Java routines.

Christine Law, DB2 Advanced Support Specialist, IBM

Christine Law is an IBM certified Solutions Expert with the DB2 UDB Advanced Support team at the IBM Toronto Lab. She joined IBM in 2001, and has over 4 years of experience working directly with DB2 customers. Her expertise is in DB2 application development, specializing in JDBC, SQLJ, stored procedures and embedded SQL.



Shakeb Shere (shakebs@ca.ibm.com), DB2 Advanced Support Specialist, IBM

Shakeb Shere is a certified IBM DB2 Universal Database Application Developer. He has worked for the IBM DB2 technical support team for over 5 years, and has in-depth knowledge in all forms of application development support for DB2 including Java and stored procedure support. He currently focuses his support in CLI, ODBC, .NET and OLEDB, and currently works in the Advanced Support Tools team for DB2 Universal Database on Linux, UNIX, and Windows.



27 October 2005

Introduction

There are numerous advantages to using stored procedures for your database applications. Some of the benefits include reduced network usage, improved performance, and reduced development cost. Java stored procedures have always been one of the most popular routines supported by DB2. One reason is that there are a large number of Java developers due to the popularity of the programming language; thus Java routines usually become the preference when several languages are considered.

Not all DB2 stored procedures should be written in Java. If your business logic requires simple, concise stored procedures, then consider the SQL Procedure Language (SQL PL), as your choice for stored procedure development. SQL stored procedures are always run as trusted stored procedures, and since they do not rely on an external Java Virtual Machine (JVM) process to load the procedure, they are faster than Java routines.

The advantages of using a Java stored procedure are the same advantages you gain when creating any Java application. Java is a very secure programing language. Only the Java bytecode is available to users. Java code can be compiled once, and run on any machine and operating system combination that supports a JVM. Since the Java code runs within a separate JVM, a dangerous operation (one that could cause the JVM to crash) can be handled properly by the JVM. You don't need to implement a separate infrastructure to handle dangerous situations, as Java has built-in mechanisms to catch exceptions, and so on.

Throughout this article we sometimes refer to Java stored procedures as routines. A routine and a stored procedure are synonymous in DB2 UDB. In DB2 V8 the concept of a routine was introduced because it applied to both stored procedures and user-defined functions (UDFs).

This article discusses common error messages that can be encountered in the life cycle of developing or executing a Java stored procedure. To start off the discussion, we'll cover important concepts and configuration parameters that are important to Java stored procedure development. Next, we describe how to enable DB2 Java. You need to set up the Java environment in order to invoke a Java stored procedure successfully.


Key concepts

The following concepts are important to understanding how stored procedures work in the DB2 environment:

  • FENCED or NOT FENCED: This clause specifies whether the routine is considered "safe" to run in the database manager operating environment's process or address space.

    If a stored procedure is registered as FENCED, the database manager protects its internal resources (for example, data buffers) from access by the procedure. Most routines have the option of running as FENCED or NOT FENCED. Java routines, however, can only be registered as FENCED. In general, a routine running as FENCED will not perform as fast as a similar one running as NOT FENCED. This is because NOT FENCED routines can take advantage of inter-process communications (IPCs) within the database engine.

    The use of NOT FENCED for routines that have not been thoroughly tested can compromise the integrity of DB2. DB2 takes some precautions against many of the common types of inadvertent failures that could occur, but cannot guarantee complete integrity when NOT FENCED routines are used. A common term used for NOT FENCED routines is trusted. A routine that is declared as trusted will run in the same address space of the database manager.

    Either SYSADM authority, DBADM authority, or a special authority (CREATE_NOT_FENCED) is required to register a routine as NOT FENCED. Only FENCED can be specified for a routine that is defined as NOT THREADSAFE.

  • THREADSAFE or NOT THREADSAFE: This clause specifies whether the routine is considered safe to run in the same process as other routines (THREADSAFE), or not (NOT THREADSAFE).

    If the procedure is defined as THREADSAFE, the database manager can invoke the procedure in the same process as other routines. In general, to be THREADSAFE, a routine should not use any global or static data areas. Most programming references include a discussion of writing threadsafe routines. Both FENCED and NOT FENCED procedures can be THREADSAFE.

    If the procedure is defined as NOT THREADSAFE, the database manager will never invoke the procedure in the same process as another routine.

    In Java stored procedures, THREADSAFE is the default, whether it is declared as a FENCED or NOT FENCED stored procedure.


Configuration parameters

DB2 has a wide array of configuration parameters. Some are defined at the database level, and others at the database management level. Most parameters that affect the behaviour of stored procedures are defined at the instance level (that is, the database management level).

  • KEEPFENCED: This is a database manager configuration (DBM CFG) parameter. In previous versions of DB2 UDB, it was called KEEPDARI. This parameter indicates whether or not a fenced mode process (db2fmp) is kept after a fenced mode routine call is complete. Fenced mode processes are created as separate system entities in order to isolate user-written fenced-mode code from the database manager agent process. This parameter is only applicable on database servers. It is highly recommended that this parameter be set to NO when developing stored procedures, so that a developer will always get a fresh copy of the stored procedure when invoked. This is especially important if the stored procedure is going to be recompiled constantly. In production environments, you should always set this parameter to YES, as it can greatly impact performance. NOT FENCED stored procedures have no impact on this configuration parameter, since they do not run within the db2fmp process.
  • FENCED_POOL: This is a database manager configuration (DBM CFG) parameter. It represents the number of idle fenced-mode processes (db2fmp) pooled on the system. For threaded db2fmp processes (processes serving threadsafe stored procedures and UDFs), this parameter represents the number of threads cached in each db2fmp process. For non-threaded db2fmp processes, this parameter represents the number of processes cached.
  • NUM_INITFENCED: This is a database manager configuration (DBM CFG) parameter. This parameter indicates the initial number of non-threaded, idle db2fmp processes that are created in the db2fmp pool at DB2START time. This parameter is ignored if KEEPFENCED is not specified.
  • JDK_PATH: This is a database manager configuration (DBM CFG) parameter. This parameter denotes the location of the JVM or Java Development Kit (JDK) that will be used to execute the Java stored procedure. This is a very important parameter. For the value, specify the full path to the directory above the "bin" directory that contains the Java executable for the JVM. An example on a Windows® platform would be C:\Program Files\IBM\SQLLIB\java\jdk. A UNIX® example would be /usr/java1.3.1. The level of the JVM is also very important, as DB2 UDB supports only certain levels of JVM depending on the db2level and platform level used. (This will be discussed a little later on in this article.)
  • JAVA_HEAP_SZ: This is a database manager configuration (DBM CFG) parameter. This parameter determines the maximum size of the heap used by the Java interpreter that is started to service Java stored procedures and UDFs. To prevent out-of-memory conditions inside the Java stored procedure, you can increase this value. However, allocating too much memory could also be detrimental if there are many stored procedures invoked in the environment (that is, each JVM would be allocating that much heap space). The rule of thumb is to keep the JAVA_HEAP_SZ at its default setting, which is 512 (4K pages).
  • ASLHEAPSZ: This is a database manager configuration (DBM CFG) parameter. The application support layer heap represents a communication buffer between the local application and its associated agent. This buffer is allocated as shared memory by each database manager agent that is started. This parameter determines the size of the buffer used for passing parameters between the routine and the calling application. The number of parameters and the size of the parameters in the stored procedure could definitely have an impact with this configuration parameter. The maximum number of db2fmp processes that are allowed on the system at the same time can be affected by this parameter as well.
  • QUERY_HEAP_SZ: This is a database manager configuration (DBM CFG) parameter. This parameter specifies the maximum amount of memory that can be allocated for the query heap. A query heap is used to store each query in the agent's private memory. The information for each query consists of the input and output SQLDA, the statement text, the SQLCA, the package name, the creator, the section number, and the consistency token. This parameter is provided to ensure that an application does not consume unnecessarily large amounts of virtual memory within an agent. A stored procedure that executes complex SQL could cause the db2fmp process to terminate unexpectedly if this parameter is set too low.
  • DB2_FMP_COMM_HEAPSZ: This is a db2set registry parameter. This parameter is applicable on all platforms except AIX 32-bit platforms, where the value is predefined as 256MB. This variable specifies the size (in 4 KB pages) of the pool used for fenced routine invocations, such as stored procedure or user-defined function calls. The space used by each fenced routine is twice the value of the ASLHEAPSZ configuration parameter. If you are running a large number of fenced routines on your system, you may need to increase the value of this variable. If you are running a very small number of fenced routines, you can reduce it. Setting this value to 0 means that no set is created, and as a result no fenced routines can be invoked. You can calculate the number of db2fmp processes that you can have running in your system at the same time with the following formula:
    Maximum Number of db2fmps = DB2_FMP_COMM_HEAPSZ / (2*ASLHEAPSZ)

Setting up your Java environment

Several steps are required before you can compile your own Java stored procedure. This section discusses the steps required to ensure your system is set up for running Java procedures.

Compatible JDK/JVM levels

Before you begin, first ensure that you have a compatible JDK/JVM installed on your database server. Each operating system supports different levels of the JDK. This is especially true if the database instance that is configured is 64-bit instead of 32-bit.

A compatibility chart for supported JDK/JVM levels can be found here: http://www-1.ibm.com/support/docview.wss?rs=71&uid=swg21251460.

Multiple JVMs can be installed on the same system. To determine which one to use when executing a Java stored procedure, DB2 reads the JDK_PATH database manager configuration parameter. You need to ensure that a compatible JVM is used with the JDK_PATH for that environment.

Setting up the Java environment

The platform of your DB2 database server needs to be properly set up for Java. Each platform may have its own requirements for Java support.

The general Java setup requirements for UNIX platforms can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0004675.htm

The general Java setup requirements for Windows platforms can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0006428.htm

HPUX and Linux

Both HPUX and Linux have extra requirements for Java setup.

The fenced id

For executing FENCED stored procedures, DB2 adds an extra layer of security by way of the fenced id. This id (and group) should be created when you create the DB2 instance. You can read more information about this id from here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/t0005077.htm

Configuration parameters

The Configuration parameters section above mentioned a set of important configuration parameters. Most of these parameters may be left at the default setting. However, when you encounter problems (especially with performance or memory), it is important to review and possibly change your DB2 configuration parameters so that they work within your system.

Database privileges

In most cases an application developer will be developing the stored procedures. This normally means that a DB2 administrator may have to provide the application developer with the required privileges to be able to create and maintain those stored procedures. Consider the following privileges for Java stored procedure developers: EXECUTE, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, CREATEIN, and BINDADD. You can read more information about database authorities here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/c0005524.htm


Creating and deploying Java routines

Once the environment is set up, you should be able to create and deploy your own Java (or SQLJ) stored procedure. The DB2 Application Development Client, if installed, contains a set of samples that you can use to work with when creating your own stored procedure for the first time. The Java stored procedure samples are located in your sqllib/samples/java/jdbc directory, and called SpServer.java. The SQLJ stored procedure samples are located in your sqllib/samples/java/sqlj directory, and called SpServer.sqlj.

Writing your routine

There are a few things you need to consider when you are writing your stored procedure. You need to decide which parameter passing technique you will be using with your stored procedure. DB2 UDB supports two parameter styles for Java applications:

  • PARAMETER STYLE JAVA -- This means that the stored procedure will use a parameter passing convention that conforms to the Java language and SQLJ routines specification. IN/OUT and OUT parameters will be passed as single entry arrays to facilitate returning values. This can only be specified when LANGUAGE JAVA is used. PARAMETER STYLE JAVA procedures do not support the DBINFO or PROGRAM TYPE clauses.
  • PARAMETER STYLE DB2GENERAL -- This means that the stored procedure will use a parameter passing convention that is defined for use with Java methods. This can only be specified when LANGUAGE JAVA is used. PARAMETER STYLE DB2GENERAL is still available to enable the implementation of the following features in Java routines: table functions, scratchpads, access to the DBINFO structure, and the ability to make a FINAL CALL (and a separate first call) to the function or method. In order to use the DB2GENERAL parameter style, you need to ensure that the class for your stored procedure extends COM.ibm.db2.app.StoredProc. More about PARAMETER STYLE DB2GENERAL can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/c0000420.htm

Register your Java routines using the PARAMETER STYLE JAVA clause in the CREATE statement.

A stored procedure, like any other application, needs to work off of a Connection context. In Java and JDBC, this is done with a Connection object from the java.sql.* classes. The application that invokes the stored procedure will be the one establishing the connection. So in a Java stored procedure, a connection is established by way of a default connection, as Listing 1 shows.

Listing 1. A sample stored procedure called INSERT_JAVASP
1   //The simplest JAVA SP
2   import java.sql.*;
3 
4   public class INSERT_JAVASP
5   {
6     public static void iNSERT_JAVASP  (String input) throws SQLException,
 	Exception
7     {
8       int errorCode;
9  
10      try
11      {
12        // get caller's connection to the database
13   Connection con = DriverManager.getConnection("jdbc:default:connection");
14     
15        String query = "INSERT INTO CWYLAW.StoreData (c) VALUES (?)";
16
17        PreparedStatement pstmt = con.prepareStatement(query);
18        pstmt.setString(1, input);
19        pstmt.executeUpdate();
20  
21      }   
22      catch (SQLException sqle)
23      {
24        errorCode = sqle.getErrorCode();
25        throw new SQLException( errorCode + " FAILED" ); 
26      }
27    }
28  }

In line 13 of Listing 1, the Connection object (con) is established as the "default" connection. The application invoking the stored procedure will establish the connection prior to invoking the procedure. The stored procedure, when using the default connection, will get its connection properties passed from the caller. The example shown is an example of a Java stored procedure that takes in one input parameter and inserts the value into the CWYLAW.StoreData table.

Restrictions on using routines

There are several restrictions on developing stored procedures for DB2 UDB. Be sure to check the following section of the DB2 Infocenter to make sure you are aware of the restrictions: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/c0009198.htm

Java type mapping

Java has its own set of supported data types. DB2 also has its own set of data types. As an example, the DB2 data type VARCHAR does not exist in Java. However Java has a String object that can be used instead. DB2 UDB has a set of "preferred" data type mappings that is best to use for Java applications and stored procedures. Table 1 (copied from the Infocenter -- see Resources) that shows these mappings.

Table 1. Java data type mappings
SQL typeJDBC 2.0 typeJava type
BIGINTBIGINTlong
BLOBBLOBjava.sql.Blob
CHARCHARString
CHAR FOR BIT DATABINARYbyte[]
CLOBCLOBjava.sql.Clob
DATEDATEjava.sql.Date
DBCLOBCLOBjava.sql.Clob
DECIMALDECIMALjava.math.BigDecimal
DOUBLEDOUBLEdouble
FLOATFLOATdouble
INTEGERINTEGERint
GRAPHICCHARString
LONG VARCHARLONGVARCHARString
LONG VARCHAR FOR BIT DATALONGVARBINARYbyte[]
LONGVARGRAPHICLONGVARCHARString
NUMERICNUMERICjava.math.BigDecimal
REALREALfloat
SMALLINTSMALLINTshort
TIMETIMEjava.sql.Time
TIMESTAMPTIMESTAMPjava.sql.Timestamp
VARCHARVARCHARString
VARCHAR FOR BIT DATAVARBINARYbyte[]
VARGRAPHICVARCHARString

Compiling your routine

Once the stored procedure is created, it needs to be compiled. Using the JDK that you have installed on your system, compile the procedure as follows: javac INSERT_JAVASP.java

This generates a class file. You can optionally move this class file to your sqllib/function directory (the default location where stored procedure executables are picked up by DB2), or you can move this to another location of your choice (and use this custom path in the CREATE PROCEDURE command).

Another option is to package your class file into a JAR file, and deploy the JAR file. You can package up the class file into a JAR file using the following command: jar -cvf INSERT_JAVASP.jar INSERT_JAVASP.class

For more information about where to place your Java classes, please read the following section from the Infocenter: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/c0006348.htm.

SQLJ routines

When writing a SQLJ stored procedure, you will need to execute two additional steps:

  • Translate the SQLJ source code using the DB2 SQLJ Translator. This will convert your sqlj code into java code, and create a SQLJ serializable (.ser) file.
  • Customize the serializable file so that the access plans for your embedded statements are stored in a package (or optionally a bind file). You do this by the db2sqljcustomize command.

More information about SQLJ can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0007588.htm

JAR file administration on the database server

When you are using JAR files instead of class files, you will need to take some extra steps so that DB2 recognizes the JAR file as the container for your stored procedure classes. There are a set of four built-in stored procedures that come with DB2 to help administer JAR files.

  • SQLJ.INSTALL_JAR: This will "install" a JAR file into DB2, such that when the DB2 class loader looks for stored procedure libraries to load, it will find and load the particular stored procedure (as opposed to another with the same name for example).
    syntax: CALL sqlj.install_jar( jar-url, jar-id )
  • SQLJ.REPLACE_JAR: This will "replace" a JAR file with a new copy in DB2. This is especially useful if the stored procedure was recently recompiled due to any changes. This way the DB2 class loader will reload the JAR file with the new contents and use it at run time.
    syntax: CALL sqlj.replace_jar( jar-url, jar-id )
  • SQLJ.REMOVE_JAR: This will "remove" a JAR file from a DB2 instance. This is useful if you plan to drop the stored procedure and not create it again. This way DB2 does not have a copy of this JAR file in memory.
    syntax: CALL sqlj.remove_jar( jar-id )
  • SQLJ.REFRESH_CLASSES: This will "refresh" all the classes contained inside a JAR file from within a DB2 instance. This is required when you update a Java routine class. It will force DB2 to load the new classes. Without this command, DB2 will use the previous version of the class. This can be used in conjunction with SQLJ.REPLACE_JAR.
    syntax: CALL sqlj.refresh_classes( void )

More information about JAR file administration can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0006425.htm and here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0006410.htm

Registering your routine

After you have compiled the stored procedure (and optionally stored it in a JAR file), and moved to a particular location, you can register that stored procedure so that applications can reference it.

To do this, use the CREATE PROCEDURE statement. In the CREATE PROCEDURE statement, you can specify several options for that stored procedure. Here are a few of them that have significance:

  • SPECIFIC: This uniquely identifies the stored procedure name in the DB2 catalogs. Generally the "SPECIFIC" name matches the stored procedure name.
  • DYNAMIC RESULT SETS: This determines if the stored procedure will return a result set or not. It is possible that a stored procedure can return multiple result sets as well. This option will determine how many result sets the procedure is going to return.
  • LANGUAGE: This should be set to JAVA. You would also use JAVA for a SQLJ stored procedure.
  • EXTERNAL NAME: This parameter determines the location of the class file or JAR file and the method inside the file for the particular stored procedure. The default location of the file will be the sqllib/function folder. You can specify a full path to the actual location of the file instead. The format for the EXTERNAL NAME clause is as follows: 'jar-id!class_id.method_id' or 'class_id.method_id'.
  • FENCED / NOT FENCED: This parameter determines if the stored procedure will be declared as FENCED or NOT FENCED. A NOT FENCED stored procedure is recommended only if you consider the code safe to execute.
  • THREADSAFE / NOT THREADSAFE: This parameter determines if the stored FENCED procedure will be executed THREADSAFE or NOT THREADSAFE. This is only valid if the procedure is defined as FENCED, as NOT FENCED procedures are always defined as THREADSAFE.
  • PARAMETER STYLE: The only possible parameter styles for Java routines are PARAMETER STYLE JAVA or PARAMETER STYLE DB2GENERAL.

For more information about the CREATE PROCEDURE statement please read the following: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0008328.htm

Listing 2 shows a sample CREATE PROCEDURE statement, using some of the options.

Listing 2. A sample CREATE PROCEDURE statement
CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))
SPECIFIC INSERT
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'
;

Invoking your routine

Once the stored procedure is cataloged, the first thing you will want to do is invoke it to make sure it works as expected and designed. DB2 has a "CALL" command that you can use to invoke any stored procedure. In most cases you will want to use an application to invoke the stored procedure dynamically, using parameter markers and so on. Read the Application Development Guide to determine the best way to invoke the procedure from the type of application you are using.

However, the DB2 command line processor (CLP) can invoke stored procedures using the following syntax:
CALL proc-name( [parm1, parm2...] )
where parm1, parm2, and so on are the parameters. If the parameter is a character-based input parameter, then you would specify the literal value surrounded in single quotes. If the parameter is a numeric-based input parameter, then you would specify the literal value as is. If the parameter is an output parameter, you would use the '?' character to represent the output parameter. For example:

$ db2 "CALL SHAKEBS.TESTPROC('hello', 'world', 1, 2.5, ?, 'testing')"

In this example, there are 6 parameters in total. The first, second, and sixth parameters are all character literals. The third parameter is a numeric literal that would work for such data types as the integer or smallint. The fourth parameter is also a numeric literal, but since it contains a decimal point, it would pass for a double, float, or decimal type. Since the fifth parameter is shown as a question mark (?), it denotes an output parameter. So when the stored procedure is invoked, it will return a value into the output parameter.

More information about the "CALL" command can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0011378.htm, http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0009000.htm, and http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0007055.htm.


Common problems

Now let's look at some common problems that application developers may experience as they develop and execute Java (or SQLJ) stored procedures. All of the examples are provided in a zip file in the download section of this article. Please note that a majority of these examples require that you have KEEPFENCED=NO set in your database manager configuration file on the server.

SQL4301 RC=0

Listing 3 shows an example of our first error, sqlcode SQL4301 with return code 0.

Listing 3. SQL4301 rc=0 example 1: INSERT_JAVASP.java on Windows
D:\>javac INSERT_JAVASP.java

D:\>copy INSERT_JAVASP.class "C:\Program Files\IBM\SQLLIB\Function"
        1 file(s) copied.

D:\>db2 -tvf Create.ddl
CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))
SPECIFIC INSERT
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'

DB20000I  The SQL command completed successfully.

D:\>db2 call INSERT('D')
SQL4301N  Java or .NET interpreter startup or communication failed, reason
code "0".  SQLSTATE=58004

This fails with a SQL4301 rc=0 error message. Why? Check the JDK_PATH database manager configuration file to see if it is set properly. The JDK_PATH should be set to the directory one level higher than "bin" for the JVM/JDK that you want to use to execute Java stored procedures. To fix the problem, check the database manager configuration parameter for JDK_PATH, then modify it.

Listing 4. SQL4301 rc=0 example 1: Snippet of the database manager configuration file
D:\>db2 get dbm cfg 

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

Database manager configuration release level         = 0x0a00

Maximum total of files open            (MAXTOTFILOP) = 16000
CPU speed (millisec/instruction)          (CPUSPEED) = 9.368161e-007
Communications bandwidth (MB/sec)   (COMM_BANDWIDTH) = 1.000000e+002

Max number of concurrently active databases  (NUMDB) = 8
Data Links support                       (DATALINKS) = NO
Federated Database System Support        (FEDERATED) = NO
Transaction processor monitor name     (TP_MON_NAME) =

Default charge-back account        (DFT_ACCOUNT_STR) =

Java Development Kit installation path    (JDK_PATH) = C:\PROGRA~1\IBM\
SQLLIB\java

...

Notice how the JDK_PATH does not point to the directory above "bin"? This needs to be modified, as shown in Listing 5.

Listing 5. SQL4301 rc=0 example 1: Updating the database manager configuration file
D:\>db2 update dbm cfg using JDK_PATH C:\PROGRA~1\IBM\SQLLIB\java\jdk

DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command 
completed successfully.


D:\>db2stop force
09/25/2005 14:33:16     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

D:\>db2start
09/25/2005 14:33:46     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

D:\>db2 connect to sample


   Database Connection Information

 Database server        = DB2/NT 8.2.3
 SQL authorization ID   = SHAKEBS
 Local database alias   = SAMPLE

D:\>db2 call INSERT_JAVASP('D')

  Return Status = 0

D:\>db2 "select * from CWYLAW.StoreData"

C
---
D

  1 record(s) selected.

Listing 6 shows another example of a SQL4301 rc=0 error. This is a result of using incompatible JVMs. A 64-bit instance requires a 64-bit JDK. A 32-bit instance requires a 32-bit JDK.

Listing 6. SQL4301 rc=0 example 2: Receiving a SQL4301 rc=0 error on AIX
$ which java
/wsdb/v81/bldsupp/AIX/jdk1.4.1/bin/java

$ java -version

java version "1.4.1"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1)
Classic VM (build 1.4.1, J2RE 1.4.1 IBM AIX build ca1411-20030930 (JIT enabled:
jitc))

$ db2level

DB21085I  Instance "dbguest4" uses "64" bits and DB2 code release 
"SQL08022" with level identifier "03030106".
Informational tokens are "DB2 v8.1.1.88", "s050422", "U800789", and FixPak "9".
Product is installed at "/usr/opt/db2_08_01".


$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 8.2.2
 SQL authorization ID   = DBGUEST4
 Local database alias   = SAMPLE

$ db2 "call out_language(?)"
SQL4301N  Java or .NET interpreter startup or communication failed, reason
code "0".  SQLSTATE=58004

Once you have the proper JDK level on your platform, the error should go away.

Listing 7. SQL4301 rc=0 example 2: Fixing a SQL4301 rc=0 error on AIX
$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 8.2.2
 SQL authorization ID   = DBGUEST4
 Local database alias   = SAMPLE

$ db2 "call out_language(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : LANGUAGE
  Parameter Value : JAVA

  Return Status = 0

$ which java
/wsdb/v81/bldsupp/AIX5L64/jdk1.4.1/bin/java

$ java -version
java version "1.4.1"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1)

Classic VM (build 1.4.1, J2RE 1.4.1 IBM AIX 5L for PowerPC (64 bit JVM) 
build ca
ix641411-20030930 (JIT enabled: jitc))

A common thing to do is check your db2diag.log for any significant error messages. This is how we determined that the JDK level being used was incorrect.

Listing 8. SQL4301 rc=0 example 2: Relevant entries in db2diag.log
2005-10-02-18.42.36.052560-240 E226800A732        LEVEL: Error (OS)
PID     : 191200               TID  : 1           PROC : db2fmp
INSTANCE: dbguest4             NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:130
CALLED  : OS, -, dlopen
OSERR   : ENOEXEC (8) "Cannot run a file that does not have a valid format."
MESSAGE : Attempt to load specified library failed.
DATA #1 : Library name or path, 55 bytes
/wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a
DATA #2 : shared library load flags, PD_TYPE_LOAD_FLAGS, 4 bytes
2
DATA #3 : String, 145 bytes

	0509-022 Cannot load module 
   /wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a.
	0509-124 The program is a discontinued 64-bit object file.

2005-10-02-18.42.36.053802-240 E227533A860        LEVEL: Error (OS)
PID     : 191200               TID  : 1           PROC : db2fmp
INSTANCE: dbguest4             NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:140
CALLED  : OS, -, dlopen
OSERR   : ENOEXEC (8) "Cannot run a file that does not have a valid format."
MESSAGE : Attempt to load specified library augmented with object name failed.
DATA #1 : Library name or path, 65 bytes
/wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a(shr_64.o)
DATA #2 : shared library load flags, PD_TYPE_LOAD_FLAGS, 4 bytes
262146
DATA #3 : String, 231 bytes
	0509-022 Cannot load module 
	   /wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a(shr_64.o).
	0509-153   File /wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a is 
	    not an archive or the file could not be read properly.

2005-10-02-18.42.36.058868-240 I228394A367        LEVEL: Error
PID     : 191200               TID  : 1           PROC : db2fmp
INSTANCE: dbguest4             NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloJVMstart, probe:30
MESSAGE : sqloloadmodule failed.  RC:
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFFC080 : 870F 009B                                  ....

2005-10-02-18.42.36.059205-240 I228762A362        LEVEL: Error
PID     : 191200               TID  : 1           PROC : db2fmp
INSTANCE: dbguest4             NODE : 000
FUNCTION: DB2 UDB, oper system services, sqloJAttach, probe:5
MESSAGE : JVM startup failed.  RC:
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFFF3E0 : FFFF EF34                                  ...4

2005-10-02-18.42.36.059511-240 I229125A363        LEVEL: Error
PID     : 191200               TID  : 1           PROC : db2fmp
INSTANCE: dbguest4             NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejAttach, probe:10
MESSAGE : Error from sqloJAttach.  RC:
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFFF4A0 : FFFF EF34                                  ...4

2005-10-02-18.42.36.060331-240 I229489A372        LEVEL: Severe
PID     : 251500               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: dbguest4             NODE : 000         DB   : SAMPLE
APPHDL  : 0-7                  APPID: *LOCAL.dbguest4.051002224226
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFmpThread, probe:20
RETCODE : ZRC=0xFFFFFBEE=-1042

2005-10-02-18.42.36.066498-240 I229862A314        LEVEL: Warning
PID     : 124744               TID  : 1           PROC : db2sysc
INSTANCE: dbguest4             NODE : 000
MESSAGE : Removing FMP from pool
DATA #1 : Hexdump, 16 bytes
0x0FFFFFFFFFFFE090 : 0000 0000 0000 0000 0002 EAE0 0002 49B8    ...I.

2005-10-02-18.44.20.194287-240 I230177A348        LEVEL: Event
PID     : 120486               TID  : 1           PROC : db2flacc
INSTANCE: dbguest4             NODE : 000
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:30
CHANGE  : CFG DBM: "JDK_path" From: "/wsdb/v81/bldsupp/AIX/jdk1.4.1" To: 
    "/wsdb/v81/bldsupp/AIX5L64/jdk1.4.1"

SQL4301 RC=2

There is no explicit example in this article for the SQL4301 RC=2 error message, but it is worth mentioning as well. As mentioned above (in the discussion of setting up your environment for Java stored procedure support), the Linux and HPUX platforms require additional setup. Without this additional setup, the SQL4301 RC=2 error can result. Please ensure your environment is set up correctly if you use either of these platforms.

SQL4301 RC=4

Listing 9. SQL4301 rc=4 example: INSERT_JAVASP.java on Windows
D:\>javac INSERT_JAVASP.java

D:\>copy INSERT_JAVASP.class "C:\Program Files\IBM\SQLLIB\Function"
        1 file(s) copied.     

D:\>db2 -tvf Create.ddl
CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))
SPECIFIC INSERT
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'

DB20000I  The SQL command completed successfully.


D:\>db2 call INSERT('A')
SQL4301N  Java or .NET interpreter startup or communication failed, 
reason code "4".  SQLSTATE=58004

This fails with a SQL4301 rc=4 error message. Why? Check the JAVA_HEAP_SZ database manager configuration setting to see if it is large enough to accommodate your Java stored procedure. The default value for the JAVA_HEAP_SZ (512 4KB pages) should suffice, but if you get this error, try doubling the value.

More information about this parameter can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0000137.htm.

Listing 10. SQL4301 rc=4 example: Snippet of the database manager configuration file
D:\>db2 get dbm cfg 

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x0a00

 Maximum total of files open               (MAXTOTFILOP) = 16000
 CPU speed (millisec/instruction)             (CPUSPEED) = 9.368161e-007
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+002

...

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = 66
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 1
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC
 Backup buffer default size (4KB)            (BACKBUFSZ) = 1024
 Restore buffer default size (4KB)           (RESTBUFSZ) = 1024

...

Obviously a JAVA_HEAP_SZ of 1 (4K pages) is not enough to run even the simplest stored procedure. The default setting of 512 (4K pages) should suffice in most cases. On the rare occasion where you might still get this error message, consider doubling the configuration parameter again.

Listing 11. SQL4301 rc=4 example: Updating the database manager configuration file
D:\>db2 update dbm cfg using JAVA_HEAP_SZ 512
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
  
D:\>db2stop force
09/25/2005 14:33:16     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

D:\>db2start
09/25/2005 14:33:46     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

D:\>db2 connect to sample

   Database Connection Information

 Database server        = DB2/NT 8.2.3
 SQL authorization ID   = SHAKEBS
 Local database alias   = SAMPLE

D:\>db2 call INSERT_JAVASP('A')

  Return Status = 0

D:\>db2 "select * from CWYLAW.StoreData"

C
---
A

  1 record(s) selected.

SQL4301 RC=-4301

Listing 12. SQL4301 rc=-4301 example: INSERT_JAVASP.java on Windows
D:\>javac INSERT_JAVASP.java

D:\>copy INSERT_JAVASP.class "C:\Program Files\IBM\SQLLIB\Function"
        1 file(s) copied.
        
D:\>db2 -tvf Create.ddl
CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))
SPECIFIC INSERT
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'

DB20000I  The SQL command completed successfully.


D:\>db2 call INSERT('D')
SQL4301N  Java or .NET interpreter startup or communication failed, 
reason code "-4301".  SQLSTATE=58004

This fails with a SQL4301 rc=-4301 error message. Why? Check the CLASSPATH environment variable, to ensure that db2java.zip is in the CLASSPATH. If db2java.zip is not in the CLASSPATH, add it.

Listing 13. SQL4301 rc=-4301 example: Changing the CLASSPATH environment variable on Windows
D:>set | more

ALLUSERSPROFILE=C:\Documents and Settings\All Users
APPDATA=C:\Documents and Settings\Administrator\Application Data
CLASSPATH=.;C:\Progra~1\IBM\SQLLIB\java\db2jcc.jar;C:\Progra~1\IBM\SQLLIB\
java\sqlj.zip;C:\Progra~1\IBM\SQLLIB\java\common.jar;C:\Progra~1\IBM\SQLLIB\
java\db2jcc_license_cisuz.jar;C:\Progra~1\IBM\SQLLIB\java\db2jcc_license_cu.jar
...

D:\>set CLASSPATH=%CLASSPATH%;C:\Progra~1\IBM\SQLLIB\java\db2java.zip

In this example we set the CLASSPATH at the command line. This is only valid for the user session that you've logged into. The recommended way to update the CLASSPATH would be to add it your global environment. On Windows, you can use the System Control Panel to do this. On UNIX systems, you would add the CLASSPATH to the .profile file for your user account.

Listing 14. SQL4301 rc=-4301 example: SQL4301 rc=-4301 resolved
D:\>db2 call INSERT_JAVASP('D')

  Return Status = 0

D:\>db2 "select * from CWYLAW.StoreData"

C
---
D

  1 record(s) selected.

SQL4302

SQL4302 usually means an exception is caught within the Java stored procedure code, or an error condition has occurred. You should always check db2diag.log. At DIAGLEVEL 3 (default), the db2diag.log captures a stack trace back, and even gives you the line number within the code where the exception is caught. The simple example below assumes that you have compiled Query.java and copied the Query.class file into the \sqllib\FUNCTION directory on a Windows machine.

Listing 15. SQL4302 example: Query.java on Windows
1    import java.sql.*;
2         
3    public class Query
4    {
5       public static void query ( int id , String[] s1 ) throws 
            SQLException, Exception
6      {
7           // Get connection to the database
8           Connection con = 
            DriverManager.getConnection("jdbc:default:connection");
9           PreparedStatement stmt = null;
10          String errorLabel =  null;
11          String sql;
12                  
13          
14          sql = "SELECT NAME FROM STAFF WHERE ID = ?";
15          stmt = con.prepareStatement( sql );     
16          stmt.setInt(1, id);   
17          ResultSet rs = stmt.executeQuery();
18                  
19          if (!rs.next()) {
20             // set errorCode to SQL0100 to indicate data not found
21             errorLabel = "SQL0100 : NO DATA FOUND, QUERY RETURNS 
               EMPTY RESULT SET";
22             throw new SQLException(errorLabel);
23          } else {
24             // move to first row of result set
25             s1[0] = rs.getString(1);                
26          }   
27                  
28                  
29          // clean up resources
30          rs.close();
31          stmt.close();
32          con.close();
33                           
34          }
35   }

D:\>db2 -tvf Create.ddl
CREATE PROCEDURE CWYLAW.QUERY (IN ID INT, OUT NAME CHAR(9))
SPECIFIC QUERY
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE JAVA
EXTERNAL NAME 'Query.query'
FENCED
THREADSAFE
PARAMETER STYLE JAVA

DB20000I  The SQL command completed successfully.


D:\>db2 call query(5, ?)
SQL4302N  Procedure or user-defined function "CWYLAW.QUERY", 
specific name "QUERY" aborted with an exception "SQL0100 : 
NO DATA FOUND, QUERY RETURNS EMPTY RESULT".  SQLSTATE=38501

D:\>db2 call query(10, ?)

  Value of output parameters
  --------------------------
  Parameter Name  : NAME
  Parameter Value : Sanders

  Return Status = 0

There is nothing seriously wrong with a SQL4302 error. In fact, it is a good sign. It means that your exception handlers in the Java code works, and it caught an exception. Below you will see that the db2diag.log actually tells you that the exception is caught on line 22 in Query.java. In this example, SQL4302 basically tells us that the query in our procedure SELECT NAME FROM STAFF WHERE ID = 5 returns an empty result set. If we were to provide a valid ID (such as 10), the stored procedure will return a name (Sanders in that case).

Listing 16. SQL4302 example: Relevant entries in db2diag.log
2005-10-02-21.51.36.325000-240 I79282H396         LEVEL: Warning
PID     : 2140                 TID  : 2684        PROC : db2fmp.exe
INSTANCE: DB2                  NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:315
MESSAGE : Exception thrown during routine invocation:
DATA #1 : Hexdump, 4 bytes
0x01ACF5EC : D480 5501                                  ..U.

2005-10-02-21.51.36.335000-240 E79680H375         LEVEL: Warning
PID     : 2140                 TID  : 2684        PROC : db2fmp.exe
INSTANCE: DB2                  NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
MESSAGE : ADM10000W  A Java exception has been caught.  The Java stack 
          traceback has been written to the db2diag.log.

2005-10-02-21.51.36.345000-240 I80057H475         LEVEL: Warning
PID     : 2140                 TID  : 2684        PROC : db2fmp.exe
INSTANCE: DB2                  NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
DATA #1 : String, 112 bytes
java.sql.SQLException: SQL0100 : NO DATA FOUND, QUERY RETURNS EMPTY 
  RESULT SET at Query.query(Query.java:22)
DATA #2 : Hexdump, 4 bytes
0x01ACF424 : 0000 0000                                  ....

2005-10-02-21.51.36.355000-240 I80534H384         LEVEL: Warning
PID     : 2140                 TID  : 2684        PROC : db2fmp.exe
INSTANCE: DB2                  NODE : 000
FUNCTION: DB2 UDB, routine_infrastructure, sqlerJavaCallRoutine, probe:30
MESSAGE : Error from DB2ER CallUDF.  RC:
DATA #1 : Hexdump, 4 bytes
0x01ACF97C : 32EF FFFF                                  2...

2005-10-02-21.51.36.365000-240 I80920H959         LEVEL: Error
PID     : 3632                 TID  : 2840        PROC : db2bp.exe
INSTANCE: DB2                  NODE : 000
APPID   : *LOCAL.DB2.051003014530
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : Hexdump, 136 bytes
0x0012FC90 : 5351 4C43 4120 2020 8800 0000 32EF FFFF    SQLCA   ....2...
0x0012FCA0 : 4600 4357 594C 4157 2E51 5545 5259 FF51    F.CWYLAW.QUERY.Q
0x0012FCB0 : 5545 5259 FF53 514C 3031 3030 203A 204E    UERY.SQL0100 : N
0x0012FCC0 : 4F20 4441 5441 2046 4F55 4E44 2C20 5155    O DATA FOUND, QU
0x0012FCD0 : 4552 5920 5245 5455 524E 5320 454D 5054    ERY RETURNS EMPT
0x0012FCE0 : 5920 5245 5355 4C54 5351 4C45 4A45 5854    Y RESULTSQLEJEXT
0x0012FCF0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................
0x0012FD00 : 0000 0000 0000 0000 2020 2020 2020 2020    ........        
0x0012FD10 : 2020 2033 3835 3031                           38501

SQL4304 RC=1

Listing 17. SQL4304 rc=1 example: SQL4304RC1.java on AIX
$ javac SQL4304RC1.java

$ cp SQL4304RC1.class ~/sqllib/function

$ db2 -tvf CreateSP_wrong.ddl
CREATE PROCEDURE SQL4304RC1 (IN INPUT int)
SPECIFIC SQL4304RC1
DYNAMIC RESULT SETS 1
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SQ4304RC1!abend'

DB20000I  The SQL command completed successfully.


$ db2 "call SQL4304RC1(3)"
SQL4304N  Java stored procedure or user-defined function "SHAKEBS.SQL4304RC1",
specific name "SQL4304RC1" could not load Java class "SQ4304RC1", reason code
"1".  SQLSTATE=42724

This fails with a SQL4304 rc=1 error message. Why? Notice the EXTERNAL NAME clause has a misspelled class name (it should read SQL4304RC1!abend - the "L" is missing). To fix the error, drop the procedure, and recreate it with the correct spelling in the EXTERNAL NAME clause.

Listing 18. SQL4304 rc=1 example: Fixing a SQL4304 rc=1 error
$ db2 drop procedure SQL4304RC1
DB20000I  The SQL command completed successfully.

$ db2 -tvf CreateSP.ddl
CREATE PROCEDURE SQL4304RC1 (IN INPUT int)
SPECIFIC SQL4304RC1
DYNAMIC RESULT SETS 1
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SQL4304RC1!abend'

DB20000I  The SQL command completed successfully.


$ db2 "call SQL4304RC1(3)"

  Result set 1
  --------------

  ID     NAME      DEPT   JOB   YEARS  SALARY    COMM
  ------ --------- ------ ----- ------ --------- ---------
     180 Abrahams      38 Clerk      3  12009.75    236.50
     230 Lundquist     51 Clerk      3  13369.80    189.65

  2 record(s) selected.

  Return Status = 0

SQL4304 RC=2

Listing 19. SQL4304 rc=2 example: SQL4304RC2.java on AIX
$ javac SQL4304RC2.java

$ cp SQL4304RC2.class ~/sqllib/function

$ db2 -tvf CreateSP_wrong.ddl
CREATE PROCEDURE SQL4304RC2 (IN INPUT int)
SPECIFIC SQL4304RC2
DYNAMIC RESULT SETS 1
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SQL4304RC2!abend'

DB20000I  The SQL command completed successfully.

$ db2 "call SQL4304RC2(3)"
SQL4304N  Java stored procedure or user-defined function "SHAKEBS.SQL4304RC2",
specific name "SQL4304RC2" could not load Java class "SQL4304RC2", reason code
"2".  SQLSTATE=42724

This fails with a SQL4304 rc=2 error message. Why? Since the PARAMETER STYLE is DB2GENERAL, you need to ensure that the Java source code extends COM.ibm.db2.app.StoredProc (which it does not). To fix the problem, add extends COM.ibm.db2.app.StoredProc to the end of the stored procedure class name.

Listing 20. SQL4304 rc=2 example: SQL4304RC2.java
1    //The simplest JAVA SP
2    import java.sql.*;
3    import COM.ibm.db2.app.*;
4     
5    public class SQL4304RC2 extends COM.ibm.db2.app.StoredProc
6    { 
7      public void abend  (int input) throws SQLException,Exception
8      {  
9        int errorCode;
10   
11       try
12       {
13         // get caller's connection to the database
14         Connection con = DriverManager.getConnection("jdbc:default:connection");
15     
16         String query = "SELECT * FROM STAFF where YEARS = ?";
17
18         PreparedStatement pstmt = con.prepareStatement(query);
19         ResultSet rs = null;
20         pstmt.setInt(1, input);
21         rs = pstmt.executeQuery();
22    
23       } 
24       catch (SQLException sqle)
25       {
26         errorCode = sqle.getErrorCode();
27         throw new SQLException( errorCode + " FAILED - " + sqle.getMessage()); 
28       }  
29     }
30   }

Line 5 in the code now extends the class properly. Recompile the code, then replace the .class file with the one from sqllib/function, and re-execute the stored procedure.

Note: Another common mistake that results in a SQL4304 rc=2 error message is that the main method for the stored procedure is declared as a "public static" method. PARAMETER STYLE DB2GENERAL procedures cannot be declared as "static" methods, as shown properly in line 7 of the code above.

Listing 21. SQL4304 rc=2 example: Fixing a SQL4304 rc=2 error
$ javac SQL4304RC2.java

$ cp SQL4304RC2.class ~/sqllib/function

$ db2 "call SQL4304RC2(3)"

  Result set 1
  --------------

  ID     NAME      DEPT   JOB   YEARS  SALARY    COMM
  ------ --------- ------ ----- ------ --------- ---------
     180 Abrahams      38 Clerk      3  12009.75    236.50
     230 Lundquist     51 Clerk      3  13369.80    189.65

  2 record(s) selected.

  Return Status = 0

SQL4306

Listing 22. SQL4306 example: INSERT.sqlj
D:\>sqlj INSERT.sqlj
   
D:\>db2sqljcustomize -user cwylaw -password xxxxxxxxx -url 
   jdbc:db2://claw.torolab.ibm.com:50000/sample INSERT_SJProfile0
[ibm][db2][jcc][sqlj]
[ibm][db2][jcc][sqlj] Begin Customization
[ibm][db2][jcc][sqlj] Loading profile: INSERT_SJProfile0
[ibm][db2][jcc][sqlj] Customization complete for profile 
    INSERT_SJProfile0.ser
[ibm][db2][jcc][sqlj] Begin Bind
[ibm][db2][jcc][sqlj] Loading profile: INSERT_SJProfile0

[ibm][db2][jcc][sqlj] Driver defaults(user may override): BLOCKING ALL 
    VALIDATE BIND STATICREADONLY YES
[ibm][db2][jcc][sqlj] Fixed driver options: DATETIME ISO DYNAMICRULES BIND
[ibm][db2][jcc][sqlj] Binding package INSERT01 at isolation level UR
[ibm][db2][jcc][sqlj] Binding package INSERT02 at isolation level CS
[ibm][db2][jcc][sqlj] Binding package INSERT03 at isolation level RS
[ibm][db2][jcc][sqlj] Binding package INSERT04 at isolation level RR
[ibm][db2][jcc][sqlj] Bind complete for INSERT_SJProfile0

D:\>jar -cvf INSERT.jar *.class *.ser
added manifest
adding: INSERT.class(in = 1192) (out= 684)(deflated 42%)

D:\>db2 call sqlj.install_jar("file:///D:\INSERT.jar", 
'INSERTJAR')
DB20000I  The CALL command completed successfully.

D:\>db2 -tvf Create.ddl
CREATE PROCEDURE INSERT (IN INPUT CHAR(3))
SPECIFIC INSERT
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'INSERT.INSERT'

DB20000I  The SQL command completed successfully.

D:\>db2 call INSERT('abc')
SQL4306N  Java stored procedure or user-defined function "CWYLAW.INSERT",
specific name "INSERT" could not call Java method "INSERT", 
signature "(Ljava/lang/String;)V".  SQLSTATE=42724

Why does this produce a SQL4306 error? Take a look at the source code and the CREATE PROCEDURE statement. Notice that in the Java code, the method is defined as: public static void iNSERT (String input)

Note the small letter 'i'.

Listing 23. SQL4306 example: INSERT.sqlj on Windows
//The simplest SQLJ SP
import java.sql.*;
import sqlj.runtime.*; 
import sqlj.runtime.ref.*; 
 
public class INSERT 
{
  public static void iNSERT (String input) throws SQLException, Exception
 
    { 
    	#sql { INSERT INTO CWYLAW.StoreData (c) VALUES (:input) }; 
    }  
}
Listing 24. SQL4306 example: CREATE TABLE statement for the StoreData table required for the INSERT procedure
CREATE TABLE StoreData (c char(3));

But in the CREATE PROCEDURE statement, the EXTERNAL NAME is defined as EXTERNAL NAME 'INSERT.INSERT'. Note the capital 'I'. So the reason why the SQL4306 occurs is because the Java method name in the source code and the EXTERNAL NAME in the CREATE PROCEDURE statement do not match. To fix the problem, make sure the Java method and the EXTERNAL NAME clause in the CREATE PROCEDURE statement match exactly. In this example, we have chosen to fix the CREATE PROCEDURE statement instead of modifying the source code.

Listing 25. SQL4306 example: Correct CREATE PROCEDURE statement for the INSERT stored procedure
CREATE PROCEDURE INSERT (IN INPUT CHAR(3))
SPECIFIC INSERT
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'INSERT.iNSERT'
;

Now the INSERT procedure runs successfully.

Listing 26. Running the INSERT procedure
                D:\>db2 drop procedure INSERT
DB20000I  The SQL command completed successfully.

D:\>db2 call sqlj.remove_jar('INSERTJAR')
DB20000I  The CALL command completed successfully.

D:\>db2 call sqlj.install_jar("file:///D:\INSERT.jar", 'INSERTJAR')
DB20000I  The CALL command completed successfully.

D:\>db2 -tvf Create.ddl
CREATE PROCEDURE INSERT (IN INPUT CHAR(3))
SPECIFIC INSERT
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'INSERT.iNSERT'

DB20000I  The SQL command completed successfully.
D:\>db2 call INSERT('abc')

  Return Status = 0
  
D:\>db2 select * from StoreData

C
---
abc

  1 record(s) selected.

It is also worth taking some time to take a look at the JVM representation of type signatures, because this will help you identify the problem when you receive a SQL4306 error. Another common cause of SQL4306 is that the parameters of the procedure do not match the Java method definition. In this example, the SQL4306 error contains a somewhat cryptic error token at the end:

Listing 27. SQL4306 error showing Java type returned by JVM
SQL4306N  Java stored procedure or user-defined function "CWYLAW.INSERT",
specific name "INSERT" could not call Java method "INSERT", signature
"(Ljava/lang/String;)V".  SQLSTATE=42724

(Ljava/lang/String;)V is actually the Java type signature returned by the JVM.

Table 2. Java VM type signatures
Type Signature Java Type
Zboolean
Bbyte
Cchar
Sshort
Iint
Jlong
Ffloat
Ddouble
L fully-qualified-class ;fully-qualified-class
[ typetype[]
( arg-types ) ret-typemethod type

So the error message says that DB2 is trying to invoke a Java stored procedure INSERT that has a Java VM type signature of (Ljava/lang/String;)V, where the fully-qualified-class is java/lang/String (that is, the input argument is type String), and the return type is V of type void. This matches the Java method definition exactly:

public static void iNSERT (String input)

At this point, you have verified that the Java VM signature is correct, so you know that the problem is not caused by a mismatch of the procedure parameters and the Java method definition. As you found out earlier, the mismatch in the EXTERNAL NAME and the Java method definition is the cause of the problem. And as before, you can fix the problem by re-issuing the correct CREATE PROCEDURE statement. For more information about Java VM type signatures, go to this URL:

http://java.sun.com/j2se/1.4.2/docs/guide/jni/spec/types.html

SQL20200

SQL20200 error indicates that the JAR file could not be located. This usually means that either the JAR URL is incorrect, or DB2 is unable to locate the JAR file. In this simple example, the JAR file does not exist, causing SQL20200:

Listing 28. SQL20200 example: OUT_20200.java on AIX
cwylaw@bugdbug:/home/cwylaw> javac Out_20200.java

cwylaw@bugdbug:/home/cwylaw> db2 connect to sample

   Database Connection Information

 Database server        = DB2/6000 8.2.3
 SQL authorization ID   = CWYLAW
 Local database alias   = SAMPLE

cwylaw@bugdbug:/home/cwylaw> db2 "call 
sqlj.install_jar('file:/home/cwylaw/Out_20200.jar','OUT20200JAR')"
SQL20200N  The install or replace of "CWYLAW  .OUT20200JAR" failed as
"/home/cwylaw/Out_20200.jar" could not be located.
SQLSTATE=46001

The error message clearly indicates that the JAR file could not be located. In this case, the solution is simple. We need to make sure that the JAR file name Out_20200.jar is correct, and the file is located in the /home/cwylaw/ directory, then install the JAR file again. In this particular example, the JAR file was not created prior to invoking the sqlj.install_jar routine to install the JAR file. Create the JAR file and install it again.

Listing 29. SQL20200 example: Fix the problem by creating the JAR file
cwylaw@bugdbug:/home/cwylaw> ls
Create.ddl       Out_20200.class  Out_20200.java

cwylaw@bugdbug:/home/cwylaw> jar -cvf Out_20200.jar *.class

adding: Out_20200.class (in=1466) (out=862) (deflated 41%)

cwylaw@bugdbug:/home/cwylaw> ls
Create.ddl       Out_20200.jar    Out_20200.class
Out_20200.java

cwylaw@bugdbug:/home/cwylaw> db2 "call 
sqlj.install_jar('file:/home/cwylaw/Out_20200.jar','OUT20200JAR')"
DB20000I  The CALL command completed successfully.

SQL20201

The example for SQL20201 uses the same INSERT stored procedure used in the SQL4306 example above to illustrate a SQL20201 problem. SQL20201 can occur:

  • When you attempt to drop and recreate the stored procedure, but did not remove the JAR file before calling sqlj.install_jar again.
  • When you attempt to remove the JAR file with an invalid JAR ID.
Listing 30. SQL20201 example 1: Error occurs when installing the JAR file on Windows
D:\>db2 drop procedure INSERT
DB20000I  The SQL command completed successfully.

D:\>db2 call sqlj.install_jar("file:///D:\INSERT.jar", 'INSERTJAR')
SQL20201N  The install, replace or remove of "CWYLAW  .INSERTJAR" failed as
the jar name is invalid.  SQLSTATE=46002

For the first situation, you will have to call sqlj.remove_jar first before you call sqlj.install_jar to install the JAR file again. Or you can simply call sqlj.replace_jar if you want to replace the JAR file with updated class files.

Listing 31. SQL20201 example 1: Fixing the problem by removing the JAR file first, or simply replacing the JAR file
D:\>db2 call sqlj.remove_jar('INSERTJAR')
DB20000I  The CALL command completed successfully.

D:\>db2 call sqlj.install_jar("file:///D:\INSERT.jar", 'INSERTJAR')
DB20000I  The CALL command completed successfully.

Or simply call sqlj.replace_jar to replace the JAR file:

D:\>db2 call sqlj.replace_jar("file:///D:\INSERT.jar", 'INSERTJAR')
DB20000I  The CALL command completed successfully.
Listing 32. SQL20201 example 2: Error occurs when removing JAR file on Windows
D:\>db2 call sqlj.remove_jar('OUT20200')
SQL20201N  The install, replace or remove of "CWYLAW  .OUT20200" failed 
   as the jar name is invalid.  SQLSTATE=46002

The above indicates that the the JAR ID CWYLAW.OUT20200 is invalid. Make sure you have provided the correct JAR ID, and try removing the JAR file again. In our example, the correct JAR ID is OUT20200JAR, notOUT20200 (Refer to the previous SQL20200 example). So now if you try to remove the JAR again, it succeeds:

Listing 33. SQL20201 example 2: Fixing the problem by providing the correct JAR ID
D:\>db2 call sqlj.remove_jar('OUT20200JAR')
DB20000I  The CALL command completed successfully.

SQL20204

For SQL20204, the most common problem is that the EXTERNAL NAME in the CREATE PROCEDURE statement does not conform to the proper format. The proper format is as follows:

Listing 34. Java EXTERNAL NAME format
>>-'--+----------+--class_id--+-.-+--method_id--'--------------><
      '-jar_id :-'            '-!-'
Listing 35. SQL20204 example: Example of a SQL20204 error on Windows
D:\>db2 call sqlj.install_jar("file:///D:\Out_Language.jar",'OUTLANGUAGEJAR')
DB20000I  The CALL command completed successfully.

D:\>db2 -tvf Create.ddl

CREATE PROCEDURE OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
SPECIFIC OUT_LANGUAGE
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'OUT_LANGUAGE:Out_Language!outLanguage'

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL20204N  The user defined function or procedure "CWYLAW.OUT_LANGUAGE" was
unable to map to a single Java method.  LINE NUMBER=10.  SQLSTATE=46008

In the above, the EXTERNAL NAME tells DB2 to look for a JAR ID with a name of OUT_LANGUAGE, where the class name is Out_Language, and the Java method is outLanguage. But DB2 is unable to locate the Java method. In response to this problem, you should verify:

  1. Is a JAR file created for this OUT_LANGUAGE stored procedure? If so, is the JAR ID name correct?
  2. Is the class file name correct? Does the class file exist?
  3. Is the Java method name correct?

So what is causing the SQL20204 problem? Take a look at the snippet of the source code in the Out_Language.java file:

Listing 36. SQL20204 example: Class and Java method definition in Out_Language.java
public class Out_Language {
     public static void outLanguage(String[] outLanguage)

...

From the above, you can see that the class file name is correct (it is Out_Language). The Java method name is also correct (it is outLanguage). So the last thing to verify is that you have the correct JAR ID. Notice that the JAR has been installed perviously with the JAR ID OUTLANGUAGEJAR in the sqlj.install_jar step. However, OUT_LANGUAGE:Out_Language!outLanguage has been specified in the EXTERNAL NAME clause of the CREATE PROCEDURE statement. This says that the JAR ID is OUT_LANGUAGE instead of OUTLANGUAGEJAR. To fix the problem, use the correct JAR ID in the EXTERNAL NAME clause:

Listing 37. SQL20204 example: Correct CREATE PROCEDURE statement for the OUT_LANGUAGE procedure
D:\>db2 -tvf Create.ddl

CREATE PROCEDURE OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
SPECIFIC OUT_LANGUAGE
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'OUTLANGUAGEJAR:Out_Language!outLanguage'

DB20000I  The SQL command completed successfully.

SQL0449

SQL0449 error is quite simlar to the SQL20204 situation. If you get a SQL0449 error, most of the time it is because your EXTERNAL NAME clause is not in a valid format.

Listing 38. SQL449 example: Invalid EXTERNAL NAME
D:\>db2 connect to sample

   Database Connection Information

 Database server        = DB2/NT 8.2.2
 SQL authorization ID   = CWYLAW
 Local database alias   = SAMPLE


D:\>db2 -tvf Create.ddl
CREATE PROCEDURE OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
SPECIFIC OUT_LANGUAGE
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Out_Language:outLanguage'

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0449N  The statement defining routine "CWYLAW.OUT_LANGUAGE" contains an
invalidly formatted library/function identification in the EXTERNAL NAME
clause.  LINE NUMBER=10.  SQLSTATE=42878

The error message says that the EXTERNAL NAME clause is not a valid formatted string. As discussed above in the SQL20202 example, the proper Java EXTERNAL NAME format is as follows:

Listing 39. Java EXTERNAL NAME format
>>-'--+----------+--class_id--+-.-+--method_id--'--------------><
      '-jar_id :-'            '-!-'

The error occurs because a ':' is only used to separate the JAR ID and class ID. It cannot be used to separate the class ID from the method ID. In this case, we wanted to tell DB2 to look for Java method outLanguage in class Out_Language. Therefore, to fix the problem, use a '!' or a '.' instead of the ':'.

Listing 40. SQL449 example: Correct CREATE PROCEDURE statement with valid EXTERNAL NAME
D:\>db2 -tvf Create.ddl
CREATE PROCEDURE OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
SPECIFIC OUT_LANGUAGE
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Out_Language!outLanguage'

DB20000I  The SQL command completed successfully.


D:\>db2 call out_language(?)

  Value of output parameters
  --------------------------
  Parameter Name  : LANGUAGE
  Parameter Value : JAVA

  Return Status = 0

Now the CREATE PROCEDURE statement succeeds, and the stored procedure runs successfully.

SQL0444

For SQL0444, common problems involving Java Stored Procedures include:

  • SQL0444 RC=4 -- This usually indicates that the library or path could not be found. In most cases, this error occurs while installing or replacing JAR files, or when trying to build a Java stored procedure after a fixpak upgrade.

    Solution: Run db2updv8 against the database or db2iupdt against the instance (or both). The SQL0444 RC=4 error occurs because the database needs to be updated to the current fixpak level, and the instance links need to be refreshed after the fixpak upgrade.

  • SQL0444 RC=9 -- This error can occur when executing a Java stored procedure. Most of the time this is due to insufficient memory.

    Solution: Check your ulimit settings, also check the DBM CFG parameters for your instance, and the DB CFG parameters for the database. You may need to adjust these parameters so that more memory is available to run the Java stored procedure. Some important parameters to check are:

    • java_heap_sz -- Maximum Java interpreter heap size configuration parameter
    • query_heap_sz -- Query heap size configuration parameter
    • aslheapsz -- Application support layer heap size configuration parameter

    In most situations, you may need to increase java_heap_sz and query_heap_sz, but decrease aslheapsz.

SQL0440

A SQL0444 error indicates that the procedure with compatible arguments could not be found. This error does not indicate a serious problem, and it usually occurs at run time. There are two common causes:

  • CREATE PROCEDURE statement was not issued to define a procedure at the current server. Therefore DB2 is unable to locate the procedure.

    Solution: Issue the missing CREATE PROCEDURE statement.

  • The procedure is invoked with an incorrect number of agruments. That is, you may have provided an incorrect number of input or output parameters, or you may have provided an improper data type in one of the parameters. Therefore, DB2 could not find a procedure with the matching arguments to invoke.

    Solution: Check the CREATE PROCEDURE statement and make sure you are passing the correct number of input and output parameters with proper data types.

Here is an example of the second situation where an incorrect number of parameters are passed to the procedure during runtime. This example uses the sample stored procedures OUT_LANGUAGE and ALL_DATA_TYPES that are shipped with the DB2 product. You may locate the source code in /instance_home/sqllib/samples/java/jdbc/SpServer.java, and the corresponding CREATE PROCEDURE statements in /instance_home/sqllib/samples/java/jdbc/SpCreate.db2 on UNIX platforms, where instance_home is your instance home directory. On Windows, the default location of the files are in C:\Program Files\IBM\SQLLIB\samples\java\jdbc\SpServer.java and C:\Program Files\IBM\SQLLIB\samples\java\jdbc\SpCreate.db2.

Listing 41. SQL0440 example: Calling the OUT_LANGUAGE() and ALL_DATA_TYPEs procedure on Windows
D:\>db2 call out_language()
SQL0440N  No authorized routine named "OUT_LANGUAGE" of type "PROCEDURE"
having compatible arguments was found.  SQLSTATE=42884

D:\>db2 call all_data_types (32000, 2147483000, 21478483000, 100000, 2500000)
SQL0440N  No authorized routine named "ALL_DATA_TYPES" of type "PROCEDURE"
having compatible arguments was found.  SQLSTATE=42884

Let's take a look at why SQL0440 is returned. First look at the CREATE PROCEDURE statement to confirm what parameters each of the stored procedures expects.

Listing 42. SQL0440 example: CREATE PROCEDURE statements for OUT_LANGUAGE and ALL_DATA_TYPES procedures
CREATE PROCEDURE OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
SPECIFIC JDBC_OUT_LANGUAGE
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SpServer.outLanguage'

CREATE PROCEDURE ALL_DATA_TYPES (
  INOUT small SMALLINT, 
  INOUT intIn INTEGER,
  INOUT bigIn BIGINT,
  INOUT realIn REAL, 
  INOUT doubleIn DOUBLE,
  OUT charOut CHAR(1),
  OUT charsOut CHAR(15),
  OUT varcharOut VARCHAR(12),
  OUT dateOut DATE,
  OUT timeOut TIME)
SPECIFIC JDBC_ALL_DAT_TYPES
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SpServer.allDataTypes'

From the CREATE PROCEDURE statements, you can see that:

  1. The OUT_LANGUAGE procedure expects one output parameter of type char.
  2. The ALL_DATA_TYPES procedure expects 5 input/output parameters, and 5 output parameters, where each of the argument types expected is shown above.

For input parameters, provide a proper value with the correct data type. For output parameters, use a '?' for each output parameter. For input/output parameters, since they are used for both input and output purposes, provide the proper value as if you are just passing a value for an input parameter. Therefore, you should call each of the procedures like below:

Listing 43. SQL0440 example: Calling the OUT_LANGUAGE and ALL_DATA_TYPES procedures properly
D:\>db2 call out_language(?)

  Value of output parameters
  --------------------------
  Parameter Name  : LANGUAGE
  Parameter Value : JAVA

  Return Status = 0

D:\>db2 call all_data_types (32000, 2147483000, 21478483000, 100000, 
2500000, ?, ?, ?, ?, ?)

  Value of output parameters
  --------------------------
  Parameter Name  : SMALL
  Parameter Value : 16000

  Parameter Name  : INTIN
  Parameter Value : 1073741500

  Parameter Name  : BIGIN
  Parameter Value : 10739241500

  Parameter Name  : REALIN
  Parameter Value : +5.00000E+004

  Parameter Name  : DOUBLEIN
  Parameter Value : +1.25000000000000E+006

  Parameter Name  : CHAROUT
  Parameter Value : S

  Parameter Name  : CHARSOUT
  Parameter Value : SCOUTTEN

  Parameter Name  : VARCHAROUT
  Parameter Value : MARILYN

  Parameter Name  : DATEOUT
  Parameter Value : 09/27/2005

  Parameter Name  : TIMEOUT
  Parameter Value : 11:30:16

  Return Status = 0

For more information about how to call procedures from the Command Line Processor (CLP), see this URL: http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/t0007055.htm.

SQL1042

Although the error message "SQL1042C An unexpected system error occurred" might sound very critical, do not worry. There are often things that you can do to fix this problem. When you see this generic SQL1042 error, it is very important that you check your db2diag.log, as the log file often contains useful information that will tell you what is causing the problem. The most common problems are:

  1. SQL1042 when installing the JAR file

    Solution 1: Check to see if you have followed all the Java setup instructions for your specific platform. This problem occurs more likely on UNIX platforms because there are special Java setup requirements for HPUX and Linux. Also check and make sure you have created symbolic links to point to the Java shared libraries for your specific platform. Please refer to the "Setting up your Java Environment" section above.

    Solution 2: Verify whether you have a permission problem that makes DB2 unable to access the /sqllib/function/jar directory. SQL1042 can also occur due to a invalid path name, or an inaccessible network path.

  2. SQL1042 when calling a stored procedure.

    Solution 1: Check your ASLHEAPSZ and/or QUERY_HEAP_SZ parameters.

    Solution 2: Check your DB2_FMP_COMM_HEAPSZ registry variable.

Listing 44. SQL1042 example 1: Permission problems causing SQL1042 during installation of JAR file
cwylaw@bugdbug:/home/cwylaw> javac Out_20200.java

cwylaw@bugdbug:/home/cwylaw> jar -cvf Out_20200.jar *.class

cwylaw@bugdbug:/home/cwylaw> db2 connect to sample

   Database Connection Information

 Database server        = DB2/6000 8.2.3
 SQL authorization ID   = CWYLAW
 Local database alias   = SAMPLE
 
 
cwylaw@bugdbug:/home/cwylaw> db2 "call 
sqlj.install_jar('file:/home/cwylaw/Out_20200.jar','OUT20200')"
SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

It is necessary to check db2diag.log for more information:

Listing 45. SQL1042 example 1: Permission problems causing SQL1042 during installation of JAR file. Relevant db2diag.log entries
2005-09-29-11.32.42.602876-240 E102575C539        LEVEL: Warning (OS)
PID     : 214958               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-265                APPID: *LOCAL.cwylaw.050929151244
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:100
CALLED  : OS, -, unspecified_system_function
OSERR   : EACCES (13) "The file access permissions do not allow 
the specified action."
DATA #1 : File name, 39 bytes
/home/cwylaw/sqllib/function/jar/CWYLAW

2005-09-29-11.32.42.627442-240 I103115C588        LEVEL: Error
PID     : 214958               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-265                APPID: *LOCAL.cwylaw.050929151244
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:10
MESSAGE : directory:
DATA #1 : Hexdump, 39 bytes
0x2FF11FB0 : 2F68 6F6D 652F 6377 796C 6177 2F73 716C    /home/cwylaw/sql
0x2FF11FC0 : 6C69 622F 6675 6E63 7469 6F6E 2F6A 6172    lib/function/jar
0x2FF11FD0 : 2F43 5759 4C41 57                          /CWYLAW

2005-09-29-11.32.42.627837-240 I103704C440        LEVEL: Error
PID     : 214958               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-265                APPID: *LOCAL.cwylaw.050929151244
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:20
MESSAGE : permissions:
DATA #1 : Hexdump, 4 bytes
0x2FF11E4C : 0000 01FD                                  ...

2005-09-29-11.33.35.148685-240 E104145C539        LEVEL: Warning (OS)
PID     : 214958               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-291                APPID: *LOCAL.cwylaw.050929153332
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:100
CALLED  : OS, -, unspecified_system_function
OSERR   : EACCES (13) "The file access permissions do not allow the 
specified action."
DATA #1 : File name, 39 bytes
/home/cwylaw/sqllib/function/jar/CWYLAW

2005-09-29-11.33.35.149550-240 I104685C588        LEVEL: Error
PID     : 214958               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-291                APPID: *LOCAL.cwylaw.050929153332
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:10
MESSAGE : directory:
DATA #1 : Hexdump, 39 bytes
0x2FF11FB0 : 2F68 6F6D 652F 6377 796C 6177 2F73 716C    /home/cwylaw/sql
0x2FF11FC0 : 6C69 622F 6675 6E63 7469 6F6E 2F6A 6172    lib/function/jar
0x2FF11FD0 : 2F43 5759 4C41 57                          /CWYLAW

2005-09-29-11.33.35.149928-240 I105274C440        LEVEL: Error
PID     : 214958               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-291                APPID: *LOCAL.cwylaw.050929153332
FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:20
MESSAGE : permissions:
DATA #1 : Hexdump, 4 bytes
0x2FF11E4C : 0000 01FD

From the db2diag.log, you can see that you are unable to access the /home/cwylaw/sqllib/function/jar/CWYLAW directory. This is where the JAR files will go by default, where CWYLAW is the instance name in this case. Now you should check the permission on the /home/cwylaw/sqllib/function/jar/CWYLAW directory to find out what is wrong. The example below shows that the read and write permissions for the /function/jar directory have been removed. To fix the problem, you would change the permission back to the default:

Listing 46. SQL1042 example 1: Fixing the permission problem that causes SQL1042 during installation of JAR file
cwylaw@bugdbug:/home/cwylaw/sqllib/function> ls -al | grep jar
d--x-wx--x   3 cwylaw   build           512 Sep 28 12:36 jar
cwylaw@bugdbug:/home/cwylaw/sqllib/function> chmod +rw jar
cwylaw@bugdbug:/home/cwylaw/sqllib/function> ls -al | grep jar
drwxrwxr-x  3 cwylaw   build           512 Sep 28 12:36 jar
cwylaw@bugdbug:/home/cwylaw/sqllib/function> db2 "call 
   sqlj.install_jar('file:/home/cwylaw/Out_20200.jar','OUT20200')"

DB20000I  The CALL command completed successfully.
Listing 47. SQL1042 example 2: Improper ASLHEAPSZ and/or QUERY_HEAP_SZ
cwylaw@bugdbug:/home/cwylaw> db2 "call out_language(?)"
SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

Look at the db2diag.log for the relevant entries that match the timestamp when the problem occurs.

Listing 48. SQL1042 example 2: Improper ASLHEAPSZ and/or QUERY_HEAP_SZ. Relevant db2diag.log entries
2005-09-28-23.09.01.831251-240 I32479C640         LEVEL: Error
PID     : 180988               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-16                 APPID: *LOCAL.cwylaw.050929030901
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:70
MESSAGE : Insufficient memory available for IPC communication with the 
          db2fmp process. Use the DB2_FMP_COMM_HEAPSZ registry variable 
          to adjust the amount of memory available for fenced routines.
DATA #1 : Hexdump, 4 bytes
0x2FF14260 : 0000 0000                                  ...

2005-09-28-23.09.01.837606-240 E33120C586         LEVEL: Error
PID     : 180988               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-16                 APPID: *LOCAL.cwylaw.050929030901
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:70
MESSAGE : ADM11002E  Insufficient shared memory available for 
          communication with the db2fmp process. Use the 
          DB2_FMP_COMM_HEAPSZ registry variable to increase the amount 
          of shared memory available for fenced routines.

2005-09-28-23.09.01.840540-240 I33707C493         LEVEL: Severe
PID     : 180988               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-16                 APPID: *LOCAL.cwylaw.050929030901
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:60
RETCODE : ZRC=0x8B0F003B=-1961951173=SQLO_NOMEM_UND
          "No memory available in 'Undefined Heap'"
          DIA8300C A memory heap error has occurred.

2005-09-28-23.09.01.893785-240 I34201C446         LEVEL: Severe
PID     : 180988               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-16                 APPID: *LOCAL.cwylaw.050929030901
FUNCTION: DB2 UDB, routine_infrastructure, sqlerAddFmpToPool, probe:20
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x2FF143F0 : FFFF FBEE                                  ...

2005-09-28-23.09.01.939694-240 I34648C640         LEVEL: Error
PID     : 180988               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-16                 APPID: *LOCAL.cwylaw.050929030901
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:70
MESSAGE : Insufficient memory available for IPC communication with the 
          db2fmp process. Use the DB2_FMP_COMM_HEAPSZ registry variable 
          to adjust the amount of memory available for fenced routines.
DATA #1 : Hexdump, 4 bytes
0x2FF14260 : 0000 0000                                  ...

Notice that db2diag.log says that there is insufficient memory, and also suggests adjusting the DB2_FMP_COMM_HEAPSZ variable. Before you start adjusting any of the current settings, check the two most common configuration parameters ASLHEAPSZ and QUERY_HEAP_SZ that are usually involved in a SQL1042 error.

Listing 49. SQL1042 example 2: ASLHEAPSZ and QUERY_HEAP_SZ values when SQL1042 occurs
Application support layer heap size (4KB)   (ASLHEAPSZ) = 20000
Max requester I/O block size (bytes)         (RQRIOBLK) = 32767
Query heap size (4KB)                   (QUERY_HEAP_SZ) = 20000

The default value for ASLHEAPSZ is 15, and the default value for QUERY_HEAP_SZ is 1000. Evaluate to see if these values are too high or too low for your environment. In most cases, either your QUERY_HEAP_SZ is too low, so DB2 failed to execute the query within the stored procedure, or your ASLHEAPSZ is too high and there is insufficient memory to allocate a huge ASLHEAPSZ. In this case, the ASLHEAPSZ is much too high, so the example below reduces it to 2000. It also reduces the QUERY_HEAP_SZ down to 2000 because the sample OUT_LANGUAGE stored procedure contains a very simple query. Now the stored procedure runs successfully.

Listing 50. SQL1042 example 2: Fixing the problem by reducing both ASLHEAPSZ and QUERY_HEAP_SZ
cwylaw@bugdbug:/home/cwylaw> db2 update dbm cfg using ASLHEAPSZ 2000
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
SQL1362W  One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
cwylaw@bugdbug:/home/cwylaw> db2 update dbm cfg using QUERY_HEAP_SZ 2000
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
SQL1362W  One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
cwylaw@bugdbug:/home/cwylaw> db2stop
SQL1064N  DB2STOP processing was successful.

cwylaw@bugdbug:/home/cwylaw> db2start
SQL1063N  DB2START processing was successful.

cwylaw@bugdbug:/home/cwylaw> db2 connect to sample

   Database Connection Information

 Database server        = DB2/6000 8.2.3
 SQL authorization ID   = CWYLAW
 Local database alias   = SAMPLE

cwylaw@bugdbug:/home/cwylaw> db2 "call out_language(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : LANGUAGE
  Parameter Value : JAVA

  Return Status = 0

A SQL1042 error can also occur if DB2_FMP_COMM_HEAPSZ is set to 0, which prevents any fenced routines from being invoked.

Listing 51. SQL1042 example 3: DB2_FMP_COMM_HEAPSZ=0. Relevant db2diag.log entries
2005-09-28-23.26.05.803665-240 I88569C412         LEVEL: Warning
PID     : 184868               TID  : 1           PROC : db2sysc
INSTANCE: cwylaw               NODE : 000
FUNCTION: DB2 UDB, base sys utilities, sqleInitSysCtlr, probe:92
DATA #1 : String, 146 bytes
Warning! DB2_FMP_COMM_HEAPSZ is set to 0.
This means no fmps (including Health Monitor) and automatic
maintenance features of DB2 will be started.

...

2005-09-28-23.26.20.134728-240 I89996C486         LEVEL: Error
PID     : 180134               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-7                  APPID: *LOCAL.cwylaw.050929032612
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:65
MESSAGE : Can not run fenced routines (including Health Monitor) and 
          automatic maintenance features of DB2 because 
          DB2_FMP_COMM_HEAPSZ = 0.

2005-09-28-23.26.20.139210-240 E90483C515         LEVEL: Error
PID     : 180134               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-7                  APPID: *LOCAL.cwylaw.050929032612
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:65
MESSAGE : ADM11001E  DB2 did not create a memory segment for running 
          fenced routines. This was specified by the use of 
          DB2_FMP_COMM_HEAPSZ registry variable.

2005-09-28-23.26.20.142093-240 I90999C379         LEVEL: Severe
PID     : 180134               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-7                  APPID: *LOCAL.cwylaw.050929032612
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:8
RETCODE : ZRC=0x00000000=0=PSM_OK "Unknown"

2005-09-28-23.26.20.142601-240 I91379C446         LEVEL: Severe
PID     : 180134               TID  : 1           PROC : db2agent (SAMPLE)
INSTANCE: cwylaw               NODE : 000         DB   : SAMPLE
APPHDL  : 0-7                  APPID: *LOCAL.cwylaw.050929032612
FUNCTION: DB2 UDB, routine_infrastructure, sqlerAddFmpToPool, probe:20
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x2FF14410 : FFFF FBEE                                  ...

To fix the problem, you can set DB2_FMP_COMM_HEAPSZ to an appropriate value, or unset it (so that it uses the default).

Listing 52. SQL1042 example 3: Improper DB2_FMP_COMM_HEAPSZ=0. Fixing the problem
cwylaw@bugdbug:/home/cwylaw> db2set -all
[i] DB2_FMP_COMM_HEAPSZ=0
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=BUGDBUG
[g] DB2DBDFT=SAMPLE
[g] DB2COMM=TCPIP
[g] DB2ADMINSERVER=db2asv8
[g] DB2AUTOSTART=YES

cwylaw@bugdbug:/home/cwylaw> db2set DB2_FMP_COMM_HEAPSZ=

cwylaw@bugdbug:/home/cwylaw> db2set -all
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=BUGDBUG
[g] DB2DBDFT=SAMPLE
[g] DB2COMM=TCPIP
[g] DB2ADMINSERVER=db2asv8
[g] DB2AUTOSTART=YES
cwylaw@bugdbug:/home/cwylaw> db2 terminate
DB20000I  The TERMINATE command completed successfully.
cwylaw@bugdbug:/home/cwylaw> db2stop
SQL1064N  DB2STOP processing was successful.
cwylaw@bugdbug:/home/cwylaw> db2start
SQL1063N  DB2START processing was successful.
cwylaw@bugdbug:/home/cwylaw> db2 connect to sample

   Database Connection Information

 Database server        = DB2/6000 8.2.3
 SQL authorization ID   = CWYLAW
 Local database alias   = SAMPLE

cwylaw@bugdbug:/home/cwylaw> db2 "call out_language(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : LANGUAGE
  Parameter Value : JAVA

  Return Status = 0

cwylaw@bugdbug:/home/cwylaw>

SQL1131

Listing 53. SQL1131 example: SQL1131.java on Windows
D:\>javac SQL1131.java

D:\>copy SQL1131.class "C:\Program Files\IBM\SQLLIB\Function"
        1 file(s) copied.


D:\>db2 -tvf Create.ddl
CREATE PROCEDURE SQL1131 (IN INPUT CHAR(10))
SPECIFIC SQL1131
DYNAMIC RESULT SETS 1
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SQL1131!abend'
;

DB20000I  The SQL command completed successfully.

D:\>db2 call SQL1131('Mgr')
SQL1131N  DARI (Stored Procedure) process has been terminated abnormally.
SQLSTATE=38503

This fails with a SQL1131N error. Why? This can happen for a number of reasons. You may not have a large enough ASLHEAPSZ or QUERY_HEAP_SZ, both of which are defined in the database manager configuration file. If the stored procedure is written in such a way that the JVM that it runs under is terminated, it can also throw this error. For determining the reason for the abnormal end, the db2diag.log sometimes contains valuable information, including a stack trace back that contains the last calls that were performed by the Java stored procedure, to give you evidence as to why it failed.

In this example, check the Java stored procedure code to find the problem.

Listing 54. SQL1131 example: SQL1131.java
1      //The simplest JAVA SP
2      import java.sql.*;
3  
4      public class SQL1131
5      {
6        public static void abend  (String input, ResultSet[] rsout) throws 
               SQLException, Exception
7        {
8
9              
10       int errorCode;
11
12       try
13       {
14         // get caller's connection to the database
15         Connection con = 
               DriverManager.getConnection("jdbc:default:connection");
16
17         String query = "SELECT * FROM STAFF WHERE JOB = ?";
18
19         PreparedStatement pstmt = con.prepareStatement(query);
20         pstmt.setString(1, input);
21         rsout[0] = pstmt.executeQuery();
22
23         java.lang.Runtime.getRuntime().exit(0);
24
25       }         
26       catch (SQLException sqle)
27       {
28         errorCode = sqle.getErrorCode();
29         throw new SQLException( errorCode + " FAILED" ); 
30       }
31
32       }
33     }

In the above code, line 23 will terminate the currently running JVM, which will cause the Stored Procedure to "abnormally end" with it. As you can see, this is an obvious (over-simplified) example of receiving such an error. To fix the problem, remove line 23 from the above code, recompile the stored procedure, and move the new class file to the sqllib\function folder.

Listing 55. SQL1131 example: Fixing a SQL1131 error
D:\>javac SQL1131.java

D:\>copy SQL1131.class "C:\Program Files\IBM\SQLLIB\Function"
Overwrite C:\Program Fils\IBM\SQLLIB\Function\SQL1131.class? (Yes/No/All): Y
        1 file(s) copied.


D:\>db2 call SQL1131('Mgr')


  Result set 1
  --------------

  ID     NAME      DEPT   JOB   YEARS  SALARY    COMM
  ------ --------- ------ ----- ------ --------- ---------
      10 Sanders       20 Mgr        7  18357.50         -
      30 Marenghi      38 Mgr        5  17506.75         -
      50 Hanes         15 Mgr       10  20659.80         -
     100 Plotz         42 Mgr        7  18352.80         -
     140 Fraye         51 Mgr        6  21150.00         -
     160 Molinare      10 Mgr        7  22959.20         -
     210 Lu            10 Mgr       10  20010.00         -
     240 Daniels       10 Mgr        5  19260.25         -
     260 Jones         10 Mgr       12  21234.00         -
     270 Lea           66 Mgr        9  18555.50         -
     290 Quill         84 Mgr       10  19818.00         -

  11 record(s) selected.

  Return Status = 0

Acknowledgements

Special thanks to Stephanie Kivell, IBM DB2 Premium Support, for providing input and reviewing this article.


Further Information

During Java Stored procedure development, you may hit one or more of the problems described above. Our intention is not to teach you how to write Java routines, but to help you solve common Java routine problems. We hope you were able to resolve most, if not all of your problems after reading this article. If you are still unable to fix the problem after trying the suggestions given in this article, you may be hitting a more complex situation. In this case, you should contact IBM DB2 support so we may further assist you. If you need information about how to write Java stored procedures, you should refer to our Application Developement Guide available at this URL:

http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html


Download

DescriptionNameSize
stored procedure samplesSamples.zip  ( HTTP | FTP )10 KB

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=97289
ArticleTitle=Solve common problems with DB2 UDB Java stored procedures
publish-date=10272005