472,805 Members | 965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

problem using java packages in jdbc stored procedures

hi all,

Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.

I wanted to implemented a generic "Helper" class like this:

/**
* Helper
*
* Has the various common functions shared by the DB2 Stored
Procedures,
* such as for handling logging (printWriter), create / dropping
tables,
* performing SQL updates, inserts (executeUpdate) and selects
(executeQuery)
*
*/

package com.mycompany.db2;

import lotsofstuff;

public class Helper {

public static Connection getConnection(String databaseAlias,
String userid, String password) throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException
{
//driver for Db2 v7
//String driver = System.getProperty("driver",
"COM.ibm.db2.jdbc.app.DB2Driver");
//driver for Db2 v8
String driver = System.getProperty("driver",
"com.ibm.db2.jcc.DB2Driver");
System.out.println("jdbcDriver: " + driver);
String jreVersion = System.getProperty("java.version");
System.out.println("jreVersion: " + jreVersion);

System.out.println("Helper.getConnection: jdbcDriver=" +
driver + ", databaseAlias=" + databaseAlias + "userid=" + userid + ",
password=" + password);
Class.forName(driver).newInstance();

String url = "jdbc:db2:" + databaseAlias;
Connection connection = DriverManager.getConnection(url,
userid, password);
connection.setTransactionIsolation(Connection.TRAN SACTION_READ_COMMITTED);
connection.setAutoCommit(true);
return connection;
}

public static long getElapsedTime(long previousTime) {
long currentTime = System.currentTimeMillis();
long lDifference = (currentTime - previousTime);
return lDifference;
}

public static String getTimeStamp() {
Calendar cal = new
GregorianCalendar(TimeZone.getTimeZone("ECT"));
SimpleDateFormat formater = new SimpleDateFormat();
String timeStamp = formater.format(cal.getTime()); //
02.03.03 23:41
return timeStamp;
}

etc. etc.

--- (end of Helper code) ---

So I first create 2 or 3 different classes, where the "Helper" class
has the "util" methods I want to re-use (like "getElapsedTime()"
etc.). Then I compile and "jar" all the classes together and then
call on one of the above Helper methods from another class in the same
package.

However, good old DB2 so far has only been giving me errors when I try
to do this, like the following:

D:\DB2 - Stored Procedures\build>java -cp
".;D:\SQLLIB\java\db2java.zip;D:\SQLLI
B\java\runtime.zip;D:\SQLLIB\java\sqlj.zip;D:\SQLL IB\java\db2jcc.jar;D:\SQLLIB\j
ava\db2jcc_license_cu.jar;CleanCompany.jar" -Ddatabase=xxx
com.calcucare.db
2sp.Driver
jdbcDriver: com.ibm.db2.jcc.DB2Driver
jreVersion: 1.4.2_05
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -1131,
SQLSTATE: 38503,
SQLERRMC: -2029060040;*;*;*;*;;SEMAPHORE WAIT;
at com.ibm.db2.jcc.c.yc.d(yc.java:1257)
at com.ibm.db2.jcc.a.db.l(db.java:365)
at com.ibm.db2.jcc.a.db.e(db.java:101)
at com.ibm.db2.jcc.a.r.e(r.java:108)
at com.ibm.db2.jcc.a.sb.i(sb.java:191)
at com.ibm.db2.jcc.c.yc.o(yc.java:1225)
at com.ibm.db2.jcc.c.zc.d(zc.java:2315)
at com.ibm.db2.jcc.c.ad.pb(ad.java:139)
at com.ibm.db2.jcc.c.ad.execute(ad.java:122)
at com.calcucare.db2sp.CleanCompany_Driver.main(Clean Company_Driver.java
:22)

this error (from 22 in the class "CleanCompany_Driver") is raised in
the following line of code ("callableStatement.execute()"):

CallableStatement callableStatement =
connection.prepareCall(statement);
callableStatement.execute();

can any of you explain this? is it related to some kind of limitation
in db2 that prevents me from implementing my jdbc stored procedures
using java packages?

I did find the following comment in "Db2 java stored procedures -
learning by example" (pdf), section 6.5.1:
----
One common technique when writing stored procedures in any language
has
the developer start by writing the code as a standalone program. Write
the
stored procedure as ONE CLASS and the driver as a separate class. When
you
complete the draft version you customize the stored procedure class to
fit
within the database. One item that must change will be the Connection,
which
would have been established by the driver in the standalone program.
----
=> this indicates to me ibm is not recommending the "packaged java
class" approach.

Obviously, I could research the documentation in DB2 more for the
answers to this question, but so far haven't had any luck with this.
If any of you have already dealt with similar problems I would
appreciate your thoughts.

Kent
Nov 12 '05 #1
2 9081

"Kent Lewandowski" <ke*******@yahoo.com> wrote in message
news:ef**************************@posting.google.c om...
hi all,

Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.

I wanted to implemented a generic "Helper" class like this:

/**
* Helper
*
* Has the various common functions shared by the DB2 Stored
Procedures,
* such as for handling logging (printWriter), create / dropping
tables,
* performing SQL updates, inserts (executeUpdate) and selects
(executeQuery)
*
*/

package com.mycompany.db2;

import lotsofstuff;

public class Helper {

public static Connection getConnection(String databaseAlias,
String userid, String password) throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException
{
//driver for Db2 v7
//String driver = System.getProperty("driver",
"COM.ibm.db2.jdbc.app.DB2Driver");
//driver for Db2 v8
String driver = System.getProperty("driver",
"com.ibm.db2.jcc.DB2Driver");
System.out.println("jdbcDriver: " + driver);
String jreVersion = System.getProperty("java.version");
System.out.println("jreVersion: " + jreVersion);

System.out.println("Helper.getConnection: jdbcDriver=" +
driver + ", databaseAlias=" + databaseAlias + "userid=" + userid + ",
password=" + password);
Class.forName(driver).newInstance();

String url = "jdbc:db2:" + databaseAlias;
Connection connection = DriverManager.getConnection(url,
userid, password);
connection.setTransactionIsolation(Connection.TRAN SACTION_READ_COMMITTED); connection.setAutoCommit(true);
return connection;
}

public static long getElapsedTime(long previousTime) {
long currentTime = System.currentTimeMillis();
long lDifference = (currentTime - previousTime);
return lDifference;
}

public static String getTimeStamp() {
Calendar cal = new
GregorianCalendar(TimeZone.getTimeZone("ECT"));
SimpleDateFormat formater = new SimpleDateFormat();
String timeStamp = formater.format(cal.getTime()); //
02.03.03 23:41
return timeStamp;
}

etc. etc.

--- (end of Helper code) ---

So I first create 2 or 3 different classes, where the "Helper" class
has the "util" methods I want to re-use (like "getElapsedTime()"
etc.). Then I compile and "jar" all the classes together and then
call on one of the above Helper methods from another class in the same
package.

However, good old DB2 so far has only been giving me errors when I try
to do this, like the following:

D:\DB2 - Stored Procedures\build>java -cp
".;D:\SQLLIB\java\db2java.zip;D:\SQLLI
B\java\runtime.zip;D:\SQLLIB\java\sqlj.zip;D:\SQLL IB\java\db2jcc.jar;D:\SQLL
IB\j ava\db2jcc_license_cu.jar;CleanCompany.jar" -Ddatabase=xxx
com.calcucare.db
2sp.Driver
jdbcDriver: com.ibm.db2.jcc.DB2Driver
jreVersion: 1.4.2_05
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -1131,
SQLSTATE: 38503,
SQLERRMC: -2029060040;*;*;*;*;;SEMAPHORE WAIT;
at com.ibm.db2.jcc.c.yc.d(yc.java:1257)
at com.ibm.db2.jcc.a.db.l(db.java:365)
at com.ibm.db2.jcc.a.db.e(db.java:101)
at com.ibm.db2.jcc.a.r.e(r.java:108)
at com.ibm.db2.jcc.a.sb.i(sb.java:191)
at com.ibm.db2.jcc.c.yc.o(yc.java:1225)
at com.ibm.db2.jcc.c.zc.d(zc.java:2315)
at com.ibm.db2.jcc.c.ad.pb(ad.java:139)
at com.ibm.db2.jcc.c.ad.execute(ad.java:122)
at com.calcucare.db2sp.CleanCompany_Driver.main(Clean Company_Driver.java :22)

this error (from 22 in the class "CleanCompany_Driver") is raised in
the following line of code ("callableStatement.execute()"):

CallableStatement callableStatement =
connection.prepareCall(statement);
callableStatement.execute();

can any of you explain this? is it related to some kind of limitation
in db2 that prevents me from implementing my jdbc stored procedures
using java packages?

I did find the following comment in "Db2 java stored procedures -
learning by example" (pdf), section 6.5.1:
----
One common technique when writing stored procedures in any language
has
the developer start by writing the code as a standalone program. Write
the
stored procedure as ONE CLASS and the driver as a separate class. When
you
complete the draft version you customize the stored procedure class to
fit
within the database. One item that must change will be the Connection,
which
would have been established by the driver in the standalone program.
----
=> this indicates to me ibm is not recommending the "packaged java
class" approach.

Obviously, I could research the documentation in DB2 more for the
answers to this question, but so far haven't had any luck with this.
If any of you have already dealt with similar problems I would
appreciate your thoughts.

I've written several Java stored procedures for DB2 V7.2
(Windows/Unix/Linux) and I didn't have a problem using Java packages. V7.2
does not support nested Java procedures or the ability of one Java procedure
to call another but, as I understand it, V8.1 *does* support this. I haven't
upgraded to V8.1 yet so I'm going by something I read in the V8.1 docs, not
personal experience.

I don't agree that the document you cited is trying to hint that you
shouldn't use Java packages. They don't even mention Java packages in that
passage, let alone try to talk you out of using them. Mind you, I'm not
quite sure what they *are* trying to say! The passage suggests that you
write your stored procedure as one class and the driver as a separate class
but that doesn't make a lot of sense to me: IBM writes the drivers and
provides them to you with DB2, you don't write them yourself. They are
already separate classes.

The SQLCODE you are getting, -1131 a.k.a. SQL1131N, means:

SQL1131N DARI (Stored Procedure) process has been terminated
abnormally.

Explanation: The cause of this error may be:

a.. There was a coding error (e.g. segmentation violation) within the DARI
routine.
b.. The DARI process has been terminated by another process through the
use of a signal.
User Response: Reinitiate the DARI request after doing the following :

a.. Ensure that the DARI procedure is free from programming errors.
b.. Make sure that no user is sending a termination signal to the DARI
process.
sqlcode: -1131

sqlstate: 38503

(I'm using the V7.2 Message Reference; maybe the V8.1 Message Reference has
a more detailed message.)

Assuming that your line 22 is in the client that is calling the procedure -
which it should be! - it suggests that you are not getting a connection
because DARI is stopped.

Have you killed DARI by any chance? I've had to stop the DB2DARI task myself
on occasion when a stored proc became a runaway due to programming errors in
my proc. Stopping and starting DB2 should restart DB2DARI if that is the
case.

Rhino


Nov 12 '05 #2
Hi,

Thanks for your answer Rhino. I agree with you that my interpretation
of the DB2 documentation may be false. As to your suggestion about
DARI processes being stopped, it is possible but not likely, since the
first 2 times I got this error, I stopped the database and restarted /
killing all processes, and then when I tried to run the
com.mycompany.myclass program (calling the procedure), I got the same
error, again.

Based on your answer and experience, I'd say the chances are high this
problem was caused by programmer error. I don't want to spend more
time right now investigating this particular problem because we're
still in the prototyping mode. I will definitely get back to you
should I get more clues or answers as to why this happened, or if I
notice different problems implementing java packages in a db2 v8 SP.

Kent
"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<Oh*********************@news20.bellglobal.co m>...
"Kent Lewandowski" <ke*******@yahoo.com> wrote in message
news:ef**************************@posting.google.c om...
hi all,

Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.

I wanted to implemented a generic "Helper" class like this:

/**
* Helper
*
* Has the various common functions shared by the DB2 Stored
Procedures,
* such as for handling logging (printWriter), create / dropping
tables,
* performing SQL updates, inserts (executeUpdate) and selects
(executeQuery)
*
*/

package com.mycompany.db2;

import lotsofstuff;

public class Helper {

public static Connection getConnection(String databaseAlias,
String userid, String password) throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException
{
//driver for Db2 v7
//String driver = System.getProperty("driver",
"COM.ibm.db2.jdbc.app.DB2Driver");
//driver for Db2 v8
String driver = System.getProperty("driver",
"com.ibm.db2.jcc.DB2Driver");
System.out.println("jdbcDriver: " + driver);
String jreVersion = System.getProperty("java.version");
System.out.println("jreVersion: " + jreVersion);

System.out.println("Helper.getConnection: jdbcDriver=" +
driver + ", databaseAlias=" + databaseAlias + "userid=" + userid + ",
password=" + password);
Class.forName(driver).newInstance();

String url = "jdbc:db2:" + databaseAlias;
Connection connection = DriverManager.getConnection(url,
userid, password);

connection.setTransactionIsolation(Connection.TRAN SACTION_READ_COMMITTED);
connection.setAutoCommit(true);
return connection;
}

public static long getElapsedTime(long previousTime) {
long currentTime = System.currentTimeMillis();
long lDifference = (currentTime - previousTime);
return lDifference;
}

public static String getTimeStamp() {
Calendar cal = new
GregorianCalendar(TimeZone.getTimeZone("ECT"));
SimpleDateFormat formater = new SimpleDateFormat();
String timeStamp = formater.format(cal.getTime()); //
02.03.03 23:41
return timeStamp;
}

etc. etc.

--- (end of Helper code) ---

So I first create 2 or 3 different classes, where the "Helper" class
has the "util" methods I want to re-use (like "getElapsedTime()"
etc.). Then I compile and "jar" all the classes together and then
call on one of the above Helper methods from another class in the same
package.

However, good old DB2 so far has only been giving me errors when I try
to do this, like the following:

D:\DB2 - Stored Procedures\build>java -cp
".;D:\SQLLIB\java\db2java.zip;D:\SQLLI

B\java\runtime.zip;D:\SQLLIB\java\sqlj.zip;D:\SQLL IB\java\db2jcc.jar;D:\SQLL
IB\j
ava\db2jcc_license_cu.jar;CleanCompany.jar" -Ddatabase=xxx
com.calcucare.db
2sp.Driver
jdbcDriver: com.ibm.db2.jcc.DB2Driver
jreVersion: 1.4.2_05
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -1131,
SQLSTATE: 38503,
SQLERRMC: -2029060040;*;*;*;*;;SEMAPHORE WAIT;
at com.ibm.db2.jcc.c.yc.d(yc.java:1257)
at com.ibm.db2.jcc.a.db.l(db.java:365)
at com.ibm.db2.jcc.a.db.e(db.java:101)
at com.ibm.db2.jcc.a.r.e(r.java:108)
at com.ibm.db2.jcc.a.sb.i(sb.java:191)
at com.ibm.db2.jcc.c.yc.o(yc.java:1225)
at com.ibm.db2.jcc.c.zc.d(zc.java:2315)
at com.ibm.db2.jcc.c.ad.pb(ad.java:139)
at com.ibm.db2.jcc.c.ad.execute(ad.java:122)
at

com.calcucare.db2sp.CleanCompany_Driver.main(Clean Company_Driver.java
:22)

this error (from 22 in the class "CleanCompany_Driver") is raised in
the following line of code ("callableStatement.execute()"):

CallableStatement callableStatement =
connection.prepareCall(statement);
callableStatement.execute();

can any of you explain this? is it related to some kind of limitation
in db2 that prevents me from implementing my jdbc stored procedures
using java packages?

I did find the following comment in "Db2 java stored procedures -
learning by example" (pdf), section 6.5.1:
----
One common technique when writing stored procedures in any language
has
the developer start by writing the code as a standalone program. Write
the
stored procedure as ONE CLASS and the driver as a separate class. When
you
complete the draft version you customize the stored procedure class to
fit
within the database. One item that must change will be the Connection,
which
would have been established by the driver in the standalone program.
----
=> this indicates to me ibm is not recommending the "packaged java
class" approach.

Obviously, I could research the documentation in DB2 more for the
answers to this question, but so far haven't had any luck with this.
If any of you have already dealt with similar problems I would
appreciate your thoughts.

I've written several Java stored procedures for DB2 V7.2
(Windows/Unix/Linux) and I didn't have a problem using Java packages. V7.2
does not support nested Java procedures or the ability of one Java procedure
to call another but, as I understand it, V8.1 *does* support this. I haven't
upgraded to V8.1 yet so I'm going by something I read in the V8.1 docs, not
personal experience.

I don't agree that the document you cited is trying to hint that you
shouldn't use Java packages. They don't even mention Java packages in that
passage, let alone try to talk you out of using them. Mind you, I'm not
quite sure what they *are* trying to say! The passage suggests that you
write your stored procedure as one class and the driver as a separate class
but that doesn't make a lot of sense to me: IBM writes the drivers and
provides them to you with DB2, you don't write them yourself. They are
already separate classes.

The SQLCODE you are getting, -1131 a.k.a. SQL1131N, means:

SQL1131N DARI (Stored Procedure) process has been terminated
abnormally.

Explanation: The cause of this error may be:

a.. There was a coding error (e.g. segmentation violation) within the DARI
routine.
b.. The DARI process has been terminated by another process through the
use of a signal.
User Response: Reinitiate the DARI request after doing the following :

a.. Ensure that the DARI procedure is free from programming errors.
b.. Make sure that no user is sending a termination signal to the DARI
process.
sqlcode: -1131

sqlstate: 38503

(I'm using the V7.2 Message Reference; maybe the V8.1 Message Reference has
a more detailed message.)

Assuming that your line 22 is in the client that is calling the procedure -
which it should be! - it suggests that you are not getting a connection
because DARI is stopped.

Have you killed DARI by any chance? I've had to stop the DB2DARI task myself
on occasion when a stored proc became a runaway due to programming errors in
my proc. Stopping and starting DB2 should restart DB2DARI if that is the
case.

Rhino

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Mark | last post by:
Using a Java program, I can connect to the test database in MySQL. The test database is installed using a MySQL utility, and it can be accessed by any user without supplying a password. The...
1
by: Rhino | last post by:
I am trying to get a sense of requirements and best practices for Java stored procedures in DB2 V7.2 for Windows. 1. Is it required or recommended that any of the following be closed before...
7
by: Anthony Robinson | last post by:
Have been encountering an odd issue. Every now and again, certain packages of stored procedures just become invalid. I'm aware that dropping or altering an underlying table would render a package...
1
by: rdshultz | last post by:
Good morning everyone. Could someone tell me if there is a book out there which gives examples of both VB.net code and creating SQL Server 2000 stored procedures and how you get them to work...
3
by: Andrew Hall | last post by:
Hello, We are using a number of stored procedures that are called often from our client programs. I include one here as an example. The problem we are seeing is that when executing some of these...
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
3
by: satish mullapudi | last post by:
Hi, I am using DB2 v8.2 & jdk 1.5. I have written a Hello World program in java. Now is it possible to call the HelloWorld program from a stored procedure ? If so, how? I want to catch the result...
3
by: LataChavan | last post by:
I have tried to look for a solution to the problem of sending parameters to stored procedures through crystal report. Following is the code: Now what happens is that if i do not apply the logon...
0
by: Khushboo Chhabra | last post by:
Hello, Iím working on mainframes platform and have been trying to collect some knowledge on Stored Procedures. I referred to IBMís red book. And found details regarding how to create stored...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.