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.
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.
 |
|
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.
|
|
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.
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
Both HPUX and Linux have extra requirements for Java setup.
- Details of the requirements for HPUX can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0004877.htm
- Details of the requirements for Linux can be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/t0004878.htm. Extra information about Linux setup can also be found here: http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/core/r0012306.htm#wq39 (Look for the section entitled: "Setting up the Linux Java environment") and here: http://www.ibm.com/software/data/db2/udb/ad/v8/bldg/t0004878.htm
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
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.
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.
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 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.
| SQL type | JDBC 2.0 type | Java type |
|---|---|---|
| BIGINT | BIGINT | long |
| BLOB | BLOB | java.sql.Blob |
| CHAR | CHAR | String |
| CHAR FOR BIT DATA | BINARY | byte[] |
| CLOB | CLOB | java.sql.Clob |
| DATE | DATE | java.sql.Date |
| DBCLOB | CLOB | java.sql.Clob |
| DECIMAL | DECIMAL | java.math.BigDecimal |
| DOUBLE | DOUBLE | double |
| FLOAT | FLOAT | double |
| INTEGER | INTEGER | int |
| GRAPHIC | CHAR | String |
| LONG VARCHAR | LONGVARCHAR | String |
| LONG VARCHAR FOR BIT DATA | LONGVARBINARY | byte[] |
| LONGVARGRAPHIC | LONGVARCHAR | String |
| NUMERIC | NUMERIC | java.math.BigDecimal |
| REAL | REAL | float |
| SMALLINT | SMALLINT | short |
| TIME | TIME | java.sql.Time |
| TIMESTAMP | TIMESTAMP | java.sql.Timestamp |
| VARCHAR | VARCHAR | String |
| VARCHAR FOR BIT DATA | VARBINARY | byte[] |
| VARGRAPHIC | VARCHAR | String |
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.
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
sqljcode intojavacode, 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
db2sqljcustomizecommand.
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
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' ; |
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.
|
|
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.
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" |
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.
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.
|
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 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
|
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
|
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
|
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) };
}
}
|
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.
| Type Signature | Java Type |
|---|---|
| Z | boolean |
| B | byte |
| C | char |
| S | short |
| I | int |
| J | long |
| F | float |
| D | double |
| L fully-qualified-class ; | fully-qualified-class |
| [ type | type[] |
| ( arg-types ) ret-type | method 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 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.
|
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_jaragain. - 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.
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, not OUT20200 (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.
|
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:
- Is a JAR file created for this
OUT_LANGUAGEstored procedure? If so, is the JAR ID name correct? - Is the class file name correct? Does the class file exist?
- 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 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.
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
db2updv8against the database ordb2iupdtagainst 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
ulimitsettings, also check theDBM CFGparameters for your instance, and theDB CFGparameters 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_szandquery_heap_sz, but decreaseaslheapsz. -
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 PROCEDUREstatement was not issued to define a procedure at the current server. Therefore DB2 is unable to locate the procedure.Solution: Issue the missing
CREATE PROCEDUREstatement. -
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 PROCEDUREstatement 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.
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:
- The
OUT_LANGUAGEprocedure expects one output parameter of typechar. - The
ALL_DATA_TYPESprocedure 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.
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:
-
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.
-
SQL1042 when calling a stored procedure.
Solution 1: Check your
ASLHEAPSZand/orQUERY_HEAP_SZparameters.Solution 2: Check your
DB2_FMP_COMM_HEAPSZregistry 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:
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:
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> |
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
|
|
|
Special thanks to Stephanie Kivell, IBM DB2 Premium Support, for providing input and reviewing this article.
|
|
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
|
|
| Description | Name | Size | Download method |
|---|---|---|---|
| stored procedure samples | Samples.zip | 10 KB | FTP |HTTP |
|
||||
 |
Information about download methods | |
||
Learn
- IBM DB2 Universal Database v8 Information Center: Offers the complete set of official documentation and samples for the DB2 Universal Database for Linux, UNIX, and Windows products.
- IBM DB2 Universal Database v8 Documentation: PDF documentation downloads for IBM DB2 Universal Database v8 for Linux, UNIX, and Windows products.
- Java Application Development for IBM DB2 Universal Database: This page contains updates and information related to Java application development support for DB2 Universal Database v8 for Linux, UNIX, and Windows , which may not yet have been included in the published documentation, including a compatibility chart for supported JDK/JVM levels.
- IBM DB2 Universal Database Technical Support: Read technotes, flashes, and updates regarding DB2 UDB for Linux, UNIX, and Windows. Also contains downloadable links to IBM DB2 Universal Database FixPaks.
- IBM Redbook - DB2 for z/OS Stored Procedures: Through the CALL and Beyond - a guide to developing and writing stored procedures on DB2 for z/OS.
- developerWorks DB2 zone: Learn more about DB2. You'll find technical documentation, how-to articles, education, downloads, product information, and more.
Discuss
- Participate in the discussion forum.
- developerWorks blogs: Get involved in the developerWorks community.