By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

problem using java packages in jdbc stored procedures

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a

"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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.