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

call db2dari proc pb on V8.1 FP4

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


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
or was it Prep... ?

PM
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.