467,076 Members | 878 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

home > topics > db2 database > questions > call db2dari proc pb on v8.1 fp4

Post your question to a community of 467,076 developers. It's quick & easy.

call db2dari proc pb on V8.1 FP4

Dear all,

I have simulated the windows MULTI application with a java program calling
the SQLTP1DL proc referenced as DB2DARI application, on Linux Intel or
ZLinux. If the proc is NOT FENCED, there is no pb and the program works
fine. If either the proc is FENCED, or FENCED THREADSAFE, I get a SQL1042C,
or the instance crashes on ZLinux (V8.1 FP4).

The proc does a return(SQLZ_HOLD_PROC):
[db2inst1@gamma cf41]$ /opt/IBMJava2-131/bin/java sqltp1ri 1

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1042C An
unexpected system error occurred. SQLSTATE=58004

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:273)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:215)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen
erator.java:440)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java
:2199)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java:
3969)

at sqltp1ri.main(sqltp1ri.java:39)

The proc does a return(SQLZ_DISCONNECT_PROC):

[db2inst1@gamma cf41]$

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1131N DARI
(Stored Procedure) process has been terminated abnormally. SQLSTATE=38503

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:273)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:215)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen
erator.java:440)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java
:2199)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java:
3969)

at sqltp1ri.main(sqltp1ri.java:39)

[db2inst1@gamma cf41]$
Do you have any idea if I could change a parameter to make it work - all the
pb seems to be at the return code level ?

Thanks for your help,

Jean-Marc

The java program:

class sqltp1ri
{
public static void main(String argv[])
{
Connection con = null;
int loop = 1;

try
{
// Get a connection
// Class.forName("com.ibm.db2.jcc.DB2Driver").newInst ance();
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").ne wInstance();

if (argv.length == 1) {
con = DriverManager.getConnection("jdbc:db2:tp1c1");
} else if (argv.length == 3) {
con = DriverManager.getConnection("jdbc:db2:tp1c1", argv[1],
argv[2]);
}
con.setAutoCommit(false);
int param = Integer.valueOf( argv[0] ).intValue();

System.out.println("Call stored procedure named SQLTP1DL for thread "
+ argv[0]);

String sql = "CALL SQLTP1DL (?, ?, 10, ?)";
CallableStatement callStmt = con.prepareCall(sql);

while (loop <= 50) {

// set input parameters
callStmt.setInt(1, param);
callStmt.setInt(2, param);
callStmt.setInt(3, param);

// call the stored procedure

callStmt.execute();

// roll back any changes to the database
con.commit();
loop+=1;
}
callStmt.close();
con.close();
}
catch (Exception e)
{
try
{
con.rollback();
con.close();
}
catch (Exception x)
{ }

e.printStackTrace();
}
} // end main

}
Nov 12 '05 #1
  • viewed: 2971
Share:
4 Replies
In fact, it appears the db2dari stored procedure (fenced) call makes no
problem if I do it from the db2 clp. My problem is the call from the java
program works if the stored proc is NOT FENCED, but fails if the stored proc
is FENCED.

Any idea ?

"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:br**********@news-reader5.wanadoo.fr...
Dear all,

I have simulated the windows MULTI application with a java program calling
the SQLTP1DL proc referenced as DB2DARI application, on Linux Intel or
ZLinux. If the proc is NOT FENCED, there is no pb and the program works
fine. If either the proc is FENCED, or FENCED THREADSAFE, I get a SQL1042C, or the instance crashes on ZLinux (V8.1 FP4).

The proc does a return(SQLZ_HOLD_PROC):
[db2inst1@gamma cf41]$ /opt/IBMJava2-131/bin/java sqltp1ri 1

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1042C An
unexpected system error occurred. SQLSTATE=58004

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:273)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:215)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen erator.java:440)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java :2199)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java: 3969)

at sqltp1ri.main(sqltp1ri.java:39)

The proc does a return(SQLZ_DISCONNECT_PROC):

[db2inst1@gamma cf41]$

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1131N DARI
(Stored Procedure) process has been terminated abnormally. SQLSTATE=38503

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:273)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:215)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen erator.java:440)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java :2199)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java: 3969)

at sqltp1ri.main(sqltp1ri.java:39)

[db2inst1@gamma cf41]$
Do you have any idea if I could change a parameter to make it work - all the pb seems to be at the return code level ?

Thanks for your help,

Jean-Marc

The java program:

class sqltp1ri
{
public static void main(String argv[])
{
Connection con = null;
int loop = 1;

try
{
// Get a connection
// Class.forName("com.ibm.db2.jcc.DB2Driver").newInst ance();
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").ne wInstance();

if (argv.length == 1) {
con = DriverManager.getConnection("jdbc:db2:tp1c1");
} else if (argv.length == 3) {
con = DriverManager.getConnection("jdbc:db2:tp1c1", argv[1],
argv[2]);
}
con.setAutoCommit(false);
int param = Integer.valueOf( argv[0] ).intValue();

System.out.println("Call stored procedure named SQLTP1DL for thread " + argv[0]);

String sql = "CALL SQLTP1DL (?, ?, 10, ?)";
CallableStatement callStmt = con.prepareCall(sql);

while (loop <= 50) {

// set input parameters
callStmt.setInt(1, param);
callStmt.setInt(2, param);
callStmt.setInt(3, param);

// call the stored procedure

callStmt.execute();

// roll back any changes to the database
con.commit();
loop+=1;
}
callStmt.close();
con.close();
}
catch (Exception e)
{
try
{
con.rollback();
con.close();
}
catch (Exception x)
{ }

e.printStackTrace();
}
} // end main

}

Nov 12 '05 #2
A different client, means different types may be getting passed into the
routine. This usually happens for char types, and with dari routines can
result in logic errors when not all types are handled. Dari style sps
should never bring down the instance though...can you post the trap file?

Jean-Marc Blaise wrote:
In fact, it appears the db2dari stored procedure (fenced) call makes no
problem if I do it from the db2 clp. My problem is the call from the java
program works if the stored proc is NOT FENCED, but fails if the stored proc
is FENCED.

Any idea ?

"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:br**********@news-reader5.wanadoo.fr...
Dear all,

I have simulated the windows MULTI application with a java program calling
the SQLTP1DL proc referenced as DB2DARI application, on Linux Intel or
ZLinux. If the proc is NOT FENCED, there is no pb and the program works
fine. If either the proc is FENCED, or FENCED THREADSAFE, I get a


SQL1042C,
or the instance crashes on ZLinux (V8.1 FP4).

The proc does a return(SQLZ_HOLD_PROC):
[db2inst1@gamma cf41]$ /opt/IBMJava2-131/bin/java sqltp1ri 1

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1042C An
unexpected system error occurred. SQLSTATE=58004

at


COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:273)

at


COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:215)

at


COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen
erator.java:440)

at


COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java
:2199)

at


COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java:
3969)

at sqltp1ri.main(sqltp1ri.java:39)

The proc does a return(SQLZ_DISCONNECT_PROC):

[db2inst1@gamma cf41]$

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1131N DARI
(Stored Procedure) process has been terminated abnormally. SQLSTATE=38503

at


COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:273)

at


COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe
nerator.java:215)

at


COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen
erator.java:440)

at


COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java
:2199)

at


COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java:
3969)

at sqltp1ri.main(sqltp1ri.java:39)

[db2inst1@gamma cf41]$
Do you have any idea if I could change a parameter to make it work - all


the
pb seems to be at the return code level ?

Thanks for your help,

Jean-Marc

The java program:

class sqltp1ri
{
public static void main(String argv[])
{
Connection con = null;
int loop = 1;

try
{
// Get a connection
// Class.forName("com.ibm.db2.jcc.DB2Driver").newInst ance();
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").ne wInstance();

if (argv.length == 1) {
con = DriverManager.getConnection("jdbc:db2:tp1c1");
} else if (argv.length == 3) {
con = DriverManager.getConnection("jdbc:db2:tp1c1", argv[1],
argv[2]);
}
con.setAutoCommit(false);
int param = Integer.valueOf( argv[0] ).intValue();

System.out.println("Call stored procedure named SQLTP1DL for thread


"
+ argv[0]);

String sql = "CALL SQLTP1DL (?, ?, 10, ?)";
CallableStatement callStmt = con.prepareCall(sql);

while (loop <= 50) {

// set input parameters
callStmt.setInt(1, param);
callStmt.setInt(2, param);
callStmt.setInt(3, param);

// call the stored procedure

callStmt.execute();

// roll back any changes to the database
con.commit();
loop+=1;
}
callStmt.close();
con.close();
}
catch (Exception e)
{
try
{
con.rollback();
con.close();
}
catch (Exception x)
{ }

e.printStackTrace();
}
} // end main

}


Nov 12 '05 #3
Vaguely remember somathing about w_char compile option needed on some
procs...
I'd have to check more in the docs.

PM

"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:br**********@news-reader5.wanadoo.fr...
Dear all,

I have simulated the windows MULTI application with a java program calling
the SQLTP1DL proc referenced as DB2DARI application, on Linux Intel or
ZLinux. If the proc is NOT FENCED, there is no pb and the program works
fine. If either the proc is FENCED, or FENCED THREADSAFE, I get a SQL1042C, or the instance crashes on ZLinux (V8.1 FP4).

The proc does a return(SQLZ_HOLD_PROC):
[db2inst1@gamma cf41]$ /opt/IBMJava2-131/bin/java sqltp1ri 1

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1042C An
unexpected system error occurred. SQLSTATE=58004

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:273)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:215)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen erator.java:440)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java :2199)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java: 3969)

at sqltp1ri.main(sqltp1ri.java:39)

The proc does a return(SQLZ_DISCONNECT_PROC):

[db2inst1@gamma cf41]$

Call stored procedure SQLTP1DL for thread 1

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1131N DARI
(Stored Procedure) process has been terminated abnormally. SQLSTATE=38503

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:273)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_S QLException(SQLExceptionGe nerator.java:215)

at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_r eturn_code(SQLExceptionGen erator.java:440)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (DB2PreparedStatement.java :2199)

at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute( DB2PreparedStatement.java: 3969)

at sqltp1ri.main(sqltp1ri.java:39)

[db2inst1@gamma cf41]$
Do you have any idea if I could change a parameter to make it work - all the pb seems to be at the return code level ?

Thanks for your help,

Jean-Marc

The java program:

class sqltp1ri
{
public static void main(String argv[])
{
Connection con = null;
int loop = 1;

try
{
// Get a connection
// Class.forName("com.ibm.db2.jcc.DB2Driver").newInst ance();
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").ne wInstance();

if (argv.length == 1) {
con = DriverManager.getConnection("jdbc:db2:tp1c1");
} else if (argv.length == 3) {
con = DriverManager.getConnection("jdbc:db2:tp1c1", argv[1],
argv[2]);
}
con.setAutoCommit(false);
int param = Integer.valueOf( argv[0] ).intValue();

System.out.println("Call stored procedure named SQLTP1DL for thread " + argv[0]);

String sql = "CALL SQLTP1DL (?, ?, 10, ?)";
CallableStatement callStmt = con.prepareCall(sql);

while (loop <= 50) {

// set input parameters
callStmt.setInt(1, param);
callStmt.setInt(2, param);
callStmt.setInt(3, param);

// call the stored procedure

callStmt.execute();

// roll back any changes to the database
con.commit();
loop+=1;
}
callStmt.close();
con.close();
}
catch (Exception e)
{
try
{
con.rollback();
con.close();
}
catch (Exception x)
{ }

e.printStackTrace();
}
} // end main

}

Nov 12 '05 #4
or was it Prep... ?

PM
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics
db2dari processes remaning after db2stop/db2start
3 posts views Thread by Bruce Pullen | last post: by
how to call an oracle function
12 posts views Thread by Newbie | last post: by
Call back from a aspx page
4 posts views Thread by david | last post: by
Call stored proc inside another stored proc
3 posts views Thread by mandible | last post: by
BYTES.COM © 2021
About Us
Advertise
Terms Of Use
Privacy Policy
Manage Cookies
Contact Us
Sitemap
DB2 Database Answers Sitemap
DB2 Database Insights Sitemap

Follow us! Get the Latest Bytes Updates
By using this site, you agree to our Privacy Policy and Terms of Use.