Sporatic SQL0423N message

[dbmsguy]

Exception in thread "main" COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0423N Locator variable "1" does not currently represent any value. SQLSTATE=0F001


I am sporatically getting the above error and don't know how to determine the problem. From the message text, I know what it is, but it only happens occasionally. Process with regards to the LOB is always the same, so I would expect that the problem would occur all the time if it was a coding problem.

Here is the code that seems to be causing the problem:

try {
// Create a result set containing all data from my_table
Statement stmt2 = con_cs.createStatement();
rs2 =
stmt2.executeQuery(
"select table_name, validation_detail from ezk100c.data_validation "
+ "where run_date = (select max(run_date) from ezk100c.data_validation)"
}
catch (SQLException e) {
System.out.println(e);
}
outputLogFile.newLine();
outputLogFile.newLine();
outputLogFile.newLine();
outputLogFile.write(
" ******** Sampled Data Validation Detail Report ******** "
outputLogFile.newLine();
outputLogFile.write(" ******** " + runDateTime + " ******** "
outputLogFile.newLine();
outputLogFile.newLine();
outputLogFile.newLine();
while (rs2.next()) {
Clob errorComment = rs2.getClob(2);
// ************ this requires a higher level of java on unix AND java12
long clobLength = errorComment.length();
if (clobLength > 0) {
outputLogFile.write(
"For Table "
+ rs2.getString(1)
+ " the following inconsistencies were encountered"
outputLogFile.newLine();
outputLogFile.newLine();
outputLogFile.write(rs2.getString(2));
outputLogFile.newLine();
Paul

http://www.dbmsguy.com

 

[idarke]

The exception stack trace should give a line number in Main that the error is occurring on. I would be interested to know if it consistantly fails on the first attempt to get the CLOB:

Clob errorComment = rs2.getClob(2);

or the second one:

outputLogFile.write(rs2.getString(2));

It may be that the error is not sporadic at all - the second call may fail all the time, but if not all the results have (clobLength > 0), then only those that do would reach the failing statement.
"When you have eliminated the impossible, whatever remains, however
improbable, must be the truth." ~ Arthur Conan Doyle

 

[JVZ]

I'm not sure if this help you out or not...But I found this on IBM's site:

15.3 SQL0423N (Revised Text)
Locator variable &quot;<variable-position>&quot; does not currently represent any value.

Explanation: A locator variable is in error. Either it has not
had a LOB value assigned to it, the locator
associated with the variable has been freed,
or the result set cursor has been closed.

If &quot;<variable-position>&quot; is provided, it gives
the ordinal position of the variable in error
in the set of variables specified. Depending on when
the error is detected, the database manager may not
be able to determine &quot;<variable-position>&quot;.

Instead of an ordinal position, &quot;<variable-position>&quot;
may have the value &quot;function-name RETURNS&quot;, which
means that the locator value returned from the user-defined
function identified by function-name is in error.

User Response: If this was a LOB locator, correct the program
so that the LOB locator variables
used in the SQL statement have valid LOB values before
the statement is executed. A LOB value can be assigned
to a locator variable by means of a SELECT INTO statement,
a VALUES INTO statement, or a FETCH statement.

If this was a with return cursor, you must ensure that
the cursor is opened before attempting to allocate it.

sqlcode: -423
sqlstate: 0F001

 

[dbmsguy]

I would say that it is failing on the first get. I say this because, for the most part there is only one clob w/length > 0. So it must be.

As well, generally this does not fail frequently.

Actully, in my development env (winNT using VAJ as IDE), it happens frequently, but using exactly the same data (so the results are the same), not always (like maybe 1 in 10 runs will fail). It is therefore sporatic.

In our production env, we are on unix AIX platform (4.3) and it fails very infrequently.


as well, note that I am really a dba that writes utility pgms in Java, not a professional OO guy. At any rate, if there is a suggestion of some sort of debug I can do that will help catch it or something that would be great. Paul

http://www.dbmsguy.com

 

[idarke]

In my searches I found another thread in another forum where someone was complaining about a similar situation - he said on average about 5% of his CLOB results got this same exception. There was no solution posted.

It has the feeling of a memory issue. If nothing else I think you can probably discount java as the culprit, and perhaps repost the problem on the DB2 forum.

Good luck!

&quot;When you have eliminated the impossible, whatever remains, however
improbable, must be the truth.&quot; ~ Arthur Conan Doyle

 

[dbmsguy]

Thanks for you comments. Helpful.



Paul

http://www.dbmsguy.com