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

dynamic SQL from Hibernate and SQLCODE -301

P: n/a
I have a situation which I cannot get a good lead on how to resolve.
One of the applications I support uses the Hibernate software to
generate SQL. The app is JAVA with JDBC. In testing the users see no
problems and think the app is running okay. I turned a database
monitor on the app and see that the database is getting SQL return
coes of -301 for a variety of database accesses (some times the same
access works, okay other times -301). The SQLCODE points to a data
type problem. If anyone could point me in the right direction to
resolve this I would greatly appreciate it. The output from the
monitor is below:

62) Statement Event ...
Appl Handle: 196
Appl Id: 0AFD854B.FA73.041112193916
Appl Seq number: 0007

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 9
Creator : NULLID
Package : SQLLF300
Consistency Token : hADcQaDP
Package Version ID :

Cursor : SQLCUR9
Cursor was blocking: FALSE
Text : update PW_PLWS_RO.CREDIT_RPT_REQ set RPT_ID=?, CO_ID=?,
CHOICE_PT_A
CCT_NUM=?, EFF_DT=?, FIRST_NM=?, LST_NM=?, MID_INIT_NM=?, SFX_NM=?,
STREET=?, CI
TY=?, STATE=?, ZIP=?, DOB_DT=?, SSN=?, STAT_CD=?, GATEWAY_RPT_ID=?
where CREDIT_
REQ_ID=?
-------------------------------------------
Start Time: 11-12-2004 14:39:16.419955
Stop Time: 11-12-2004 14:39:16.420144
Exec Time: 0.000189 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: -301
sqlstate: 07006
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
aj
Check your <entity>.hbm.xml mapping file for
CREDIT_RPT_REQ. I think you have incorrectly
specified a column TYPE somewhere.

The line looks like:
property name="blahblah" TYPE="string" column="columnblah"/>

I think the reason you sometimes see the error and sometimes
not is that in some cases the column w/ the bad TYPE is not
being updated, sometimes it is...

As an aside, and IMNSHO, Hibernate is kick-ass software..

Good luck.

aj
JDPope wrote:
I have a situation which I cannot get a good lead on how to resolve.
One of the applications I support uses the Hibernate software to
generate SQL. The app is JAVA with JDBC. In testing the users see no
problems and think the app is running okay. I turned a database
monitor on the app and see that the database is getting SQL return
coes of -301 for a variety of database accesses (some times the same
access works, okay other times -301). The SQLCODE points to a data
type problem. If anyone could point me in the right direction to
resolve this I would greatly appreciate it. The output from the
monitor is below:

62) Statement Event ...
Appl Handle: 196
Appl Id: 0AFD854B.FA73.041112193916
Appl Seq number: 0007

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 9
Creator : NULLID
Package : SQLLF300
Consistency Token : hADcQaDP
Package Version ID :

Cursor : SQLCUR9
Cursor was blocking: FALSE
Text : update PW_PLWS_RO.CREDIT_RPT_REQ set RPT_ID=?, CO_ID=?,
CHOICE_PT_A
CCT_NUM=?, EFF_DT=?, FIRST_NM=?, LST_NM=?, MID_INIT_NM=?, SFX_NM=?,
STREET=?, CI
TY=?, STATE=?, ZIP=?, DOB_DT=?, SSN=?, STAT_CD=?, GATEWAY_RPT_ID=?
where CREDIT_
REQ_ID=?
-------------------------------------------
Start Time: 11-12-2004 14:39:16.419955
Stop Time: 11-12-2004 14:39:16.420144
Exec Time: 0.000189 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: -301
sqlstate: 07006

Nov 12 '05 #2

P: n/a
Actually a few monitor events and thousands of a second later the
exact same statement gets a zero SQL return code, as shown below. The
events in between are:
63) COMMIT SQLCODE 0 Appl Seq number 0007
64) COMPLETION Appl Seq Number 0007
65) DESCRIBE of the this SQL code Appl Seq number 0008

Is hibernate correcting itself or what is really happening here. As I
said before the update on the database takes place but why the first
-301?

Thanks for taking time with this. It seems like it could be a common
problem that no one has discussed much and may not be a real problem
but a bad return code is a bad return code.
********************** MONITOR OUTPUT *********************
66) Statement Event ...
Appl Handle: 196
Appl Id: 0AFD854B.FA73.041112193916
Appl Seq number: 0008

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 9
Creator : NULLID
Package : SQLLF300
Consistency Token : hADcQaDP
Package Version ID :

Cursor : SQLCUR9
Cursor was blocking: FALSE
Text : update PW_PLWS_RO.CREDIT_RPT_REQ set RPT_ID=?, CO_ID=?,
CHOICE_PT_A
CCT_NUM=?, EFF_DT=?, FIRST_NM=?, LST_NM=?, MID_INIT_NM=?, SFX_NM=?,
STREET=?, CI
TY=?, STATE=?, ZIP=?, DOB_DT=?, SSN=?, STAT_CD=?, GATEWAY_RPT_ID=?
where CREDIT_
REQ_ID=?
-------------------------------------------
Start Time: 11-12-2004 14:39:16.426059
Stop Time: 11-12-2004 14:39:16.426637
Exec Time: 0.000578 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 1
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 2
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 2
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
aj <ro****@mcdonalds.com> wrote in message news:<10*************@news.supernews.com>...
Check your <entity>.hbm.xml mapping file for
CREDIT_RPT_REQ. I think you have incorrectly
specified a column TYPE somewhere.

The line looks like:
property name="blahblah" TYPE="string" column="columnblah"/>

I think the reason you sometimes see the error and sometimes
not is that in some cases the column w/ the bad TYPE is not
being updated, sometimes it is...

As an aside, and IMNSHO, Hibernate is kick-ass software..

Good luck.

aj
JDPope wrote:
I have a situation which I cannot get a good lead on how to resolve.
One of the applications I support uses the Hibernate software to
generate SQL. The app is JAVA with JDBC. In testing the users see no
problems and think the app is running okay. I turned a database
monitor on the app and see that the database is getting SQL return
coes of -301 for a variety of database accesses (some times the same
access works, okay other times -301). The SQLCODE points to a data
type problem. If anyone could point me in the right direction to
resolve this I would greatly appreciate it. The output from the
monitor is below:

62) Statement Event ...
Appl Handle: 196
Appl Id: 0AFD854B.FA73.041112193916
Appl Seq number: 0007

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 9
Creator : NULLID
Package : SQLLF300
Consistency Token : hADcQaDP
Package Version ID :

Cursor : SQLCUR9
Cursor was blocking: FALSE
Text : update PW_PLWS_RO.CREDIT_RPT_REQ set RPT_ID=?, CO_ID=?,
CHOICE_PT_A
CCT_NUM=?, EFF_DT=?, FIRST_NM=?, LST_NM=?, MID_INIT_NM=?, SFX_NM=?,
STREET=?, CI
TY=?, STATE=?, ZIP=?, DOB_DT=?, SSN=?, STAT_CD=?, GATEWAY_RPT_ID=?
where CREDIT_
REQ_ID=?
-------------------------------------------
Start Time: 11-12-2004 14:39:16.419955
Stop Time: 11-12-2004 14:39:16.420144
Exec Time: 0.000189 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: -301
sqlstate: 07006

Nov 12 '05 #3

P: n/a
This actually turned out to be a JDBC driver problem. Text concerning
this is below:
DB2 Topics
|Deferred Prepares with the Universal JDBC Driver
|You can use the deferPrepares connection property as a |performance
directive
to affect the internal semantics of the input data type |conversion
capability
of the driver. By default the univeral JDBC |driver defers internal
prepare
requests (that is, the |java.sql.Connection.prepareStatement()),
|until the
statement is executed (that is,
|java.sql.PreparedStatement.execute()).
||If enabled, the internal server prepare requests are deferred until
|execute
time. This allows the prepare and execute requests to be sent |as a
single
request to the server, which reduces network delays for both type |2
and type 4
connectivity. The deferred prepare means that the driver |works
without the
benefit of described parameters or result set metadata until
|execution time:
undescribed input data is sent as is to the |server, without any data
type
cross-conversion of the input data.
|Support for cross-conversion of the input data types is a JDBC
extension |that
does not require such support, but is supported by the universal JDBC
|driver.
Therefore, if statement execution fails when |deferPrepares is
enabled, the
driver tries the execution again with |described input. The statement
retry
logic is internal to the driver, |and is invisible to the application.
However,
if an application enables |deferPrepares, the input data types should
match the
database |column types as required by the JDBC specification. For
example, if
|deferPrepares is enabled, setter calls such as
|PreparedStatement.setShort()
should match the type |of the underlying column type (for example,
SMALLINT) for
optimal |performance. If deferPrepares is disabled, the driver
|requests
describe information at prepare time. Therefore, |the driver has
described input
parameter metadata available, and the |cross-conversion of input data
types is
supported for all |PreparedStatement setter methods without requiring
the
internal |statement retry logic.
|If the universal JDBC driver is performing deferred prepares without
the
|statement retry logic, setBytes() against a BLOB column |would yield
a -301
error. setObject() (with |Types.BLOB), setBinaryStream(), or
|setBlob() should
be used against BLOB columns when |deferPrepares is enabled to avoid
the
internal retry.
|The statement retry logic is not performed for batched executes.
|Therefore
input data types must match the described column types for all
|batched
parameters.
|It is not possible to flow BLOB data to LONG VARCHAR FOR BIT DATA.
|Therefore,
the setBinaryStream() method can only be |called against BLOB columns.
If the
target column is not a BLOB column, |the JCC client will receive a
-301 error.
No statement retry will be |attempted because the driver does not
perform
retries if any input parameter |is a stream. After an input stream has
been
exhausted it cannot be read |again, and it is impractical to fully
materialize
the input stream to retry |stream parameters. Statement retry logic is
not
performed when any |input parameter is a stream.
|If PreparedStatement.getMetaData() or |PreparedStatement.setObject()
is called
and no |target type is provided, a prepare will result.
jo*******@libertymutual.com (JDPope) wrote in message news:<9d**************************@posting.google. com>...
Actually a few monitor events and thousands of a second later the
exact same statement gets a zero SQL return code, as shown below. The
events in between are:
63) COMMIT SQLCODE 0 Appl Seq number 0007
64) COMPLETION Appl Seq Number 0007
65) DESCRIBE of the this SQL code Appl Seq number 0008

Is hibernate correcting itself or what is really happening here. As I
said before the update on the database takes place but why the first
-301?

Thanks for taking time with this. It seems like it could be a common
problem that no one has discussed much and may not be a real problem
but a bad return code is a bad return code.
********************** MONITOR OUTPUT *********************
66) Statement Event ...
Appl Handle: 196
Appl Id: 0AFD854B.FA73.041112193916
Appl Seq number: 0008

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 9
Creator : NULLID
Package : SQLLF300
Consistency Token : hADcQaDP
Package Version ID :

Cursor : SQLCUR9
Cursor was blocking: FALSE
Text : update PW_PLWS_RO.CREDIT_RPT_REQ set RPT_ID=?, CO_ID=?,
CHOICE_PT_A
CCT_NUM=?, EFF_DT=?, FIRST_NM=?, LST_NM=?, MID_INIT_NM=?, SFX_NM=?,
STREET=?, CI
TY=?, STATE=?, ZIP=?, DOB_DT=?, SSN=?, STAT_CD=?, GATEWAY_RPT_ID=?
where CREDIT_
REQ_ID=?
-------------------------------------------
Start Time: 11-12-2004 14:39:16.426059
Stop Time: 11-12-2004 14:39:16.426637
Exec Time: 0.000578 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 1
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 2
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 2
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
aj <ro****@mcdonalds.com> wrote in message news:<10*************@news.supernews.com>...
Check your <entity>.hbm.xml mapping file for
CREDIT_RPT_REQ. I think you have incorrectly
specified a column TYPE somewhere.

The line looks like:
property name="blahblah" TYPE="string" column="columnblah"/>

I think the reason you sometimes see the error and sometimes
not is that in some cases the column w/ the bad TYPE is not
being updated, sometimes it is...

As an aside, and IMNSHO, Hibernate is kick-ass software..

Good luck.

aj
JDPope wrote:
I have a situation which I cannot get a good lead on how to resolve.
One of the applications I support uses the Hibernate software to
generate SQL. The app is JAVA with JDBC. In testing the users see no
problems and think the app is running okay. I turned a database
monitor on the app and see that the database is getting SQL return
coes of -301 for a variety of database accesses (some times the same
access works, okay other times -301). The SQLCODE points to a data
type problem. If anyone could point me in the right direction to
resolve this I would greatly appreciate it. The output from the
monitor is below:

62) Statement Event ...
Appl Handle: 196
Appl Id: 0AFD854B.FA73.041112193916
Appl Seq number: 0007

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 9
Creator : NULLID
Package : SQLLF300
Consistency Token : hADcQaDP
Package Version ID :

Cursor : SQLCUR9
Cursor was blocking: FALSE
Text : update PW_PLWS_RO.CREDIT_RPT_REQ set RPT_ID=?, CO_ID=?,
CHOICE_PT_A
CCT_NUM=?, EFF_DT=?, FIRST_NM=?, LST_NM=?, MID_INIT_NM=?, SFX_NM=?,
STREET=?, CI
TY=?, STATE=?, ZIP=?, DOB_DT=?, SSN=?, STAT_CD=?, GATEWAY_RPT_ID=?
where CREDIT_
REQ_ID=?
-------------------------------------------
Start Time: 11-12-2004 14:39:16.419955
Stop Time: 11-12-2004 14:39:16.420144
Exec Time: 0.000189 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: -301
sqlstate: 07006

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.