Re: JDBC update error

There should be a reason code for the error, but you can't get it from the
SQLException object that is returned.   The SQLException object has only
the message, not the cause and recovery text in it.  A joblog should get
created for the connection that did the work.  If you have joblogs enabled,
you should be able to use the wrksplf command to find a joblog that will
have the full message text in it including an error code.  Unless, of
course, what you are saying is that you did this and there was no reason
code there... then you are right.  ;-)

Hopefully that reason code will help you pinpoint what the actual problem
was.  If the problem doesn't go away after checking into that, I suggest
you report the problem through normal channels as it could be a bug at that
point (hopefully one that already has a fix out there someplace).  I did
create a program based off of your description here and run it on v4r5
without any problem (but updates worked).

BTW:  Regarding the reason code stuff... we are adding a connection
property in the next release that will allow you to tell the native JDBC
driver to return the full second level text of the exception (the cause,
recovery, etc stuff).  I believe the Toolbox JDBC driver might already
return this information, but don't quote me on that.  If they don't, they
plan to in the future as well.  You would specify this support as follows:

Connection connection = DriverManager.getConnection("jdbc:db2:
\\*local;errors=full");

Or something close based on your URL needs.  Hope something in this note
helps you get to the bottom of this.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"TRUE! nervous, very, very dreadfully nervous I had been and am; but why
WILL you say that I am mad?
The disease had sharpened my senses, not destroyed, not dulled them. "

- Edgar Allan Poe
"The Tell-Tale Heart"




"Bruce Jin" <brucej@MRC-PRODUCTIVITY.COM>@midrange.com on 11/16/2000
09:41:18 AM

Please respond to JAVA400-L@midrange.com

Sent by:  owner-java400-l@midrange.com


To:   <JAVA400-L@midrange.com>
cc:
Subject:  JDBC update error




In the following code, the first update is performed OK, the second errors
out.
Any ideas? Thanks.

try {
  if (pstmt == null)
  pstmt = conn.prepareStatement("UPDATE BRUCELIB.LIBLIST SET ODOBTX=? WHERE
ODOBNM=?");

  pstmt.setString(1,"NEW VALUE");
  pstmt.setString(2,"ALLYF");
  pstmt.executeUpdate();                          // updated OK

  pstmt.setString(2,"ALLYO");                  // try to set another row to
"NEW VALUE"
  pstmt.executeUpdate();                         // get SQL7008 error

  pstmt.close();
}

We get error during execution:

"Query failed with:'[SQL7008] LIBLIST in BRUCELIB not valid for operation."

There is no reason code for this SQL7008. Using DSPMSGD, we see SQL7008 is
as follows:

 Message ID . . . . . . . . . :   SQL7008
Message file . . . . . . . . :   QSQLMSG
  Library  . . . . . . . . . :     QSYS

Message . . . . :   &1 in &2 not valid for operation.
Cause . . . . . :   The reason code is &3:
    -- Code 1 -- &1 has no members.
    -- Code 2 -- &1 has been saved with storage free.
    -- Code 3 -- &1 is not journaled, or you do not have authority to the
  journal.  Files with an RI constraint action of CASCADE, SET NULL, or SET
  DEFAULT must be journaled to the same journal.
    -- Code 4 and 5 -- &1 is in or is being created into a production
library
  but the user is in debug mode with UPDPROD(*NO).
    -- Code 6 -- a collection is being created, but the user is in debug
mode
  with UPDPROD(*NO).
    -- Code 7 -- a based-on table used in the creation of the view is not
  valid because it is a program described table or is in a temporary
library.
 -- Code 8 -- a user attempted to create an object but the based-on table
  resides in an Auxiliary Storage Pool (ASP) which is different than the
ASP
  where the object is being created.
    -- Code 9 -- the index is currently held or is not valid.
    -- Code 10 -- a constraint is being added but the table is not valid.
The
  table either has a maximum member value greater than one, is a source
file,
  is in QTEMP, is not in the same ASP as the parent or dependent table, is
not
  write, update, or delete capable, is not an externally described file, or
  has a parent table that does not have a member.
    -- Code 11 -- a distributed table is being created in library QTEMP, or
a
  view is being created over greater than one distributed table.
    -- Code 12 -- a table could not be created in QTEMP, QSYS or QSYS2
because
  it contains a column of type datalink having the FILE LINK CONTROL
option.
 Recovery  . . . :   A list of corrective actions follow:
     -- If code 1, add a member to &1 (ADDPFM command).
     -- If code 2, restore &1 (RSTOBJ command).
     -- If code 3, start journaling on &1 (STRJRNPF command), or get access
to
   the journal.
     -- If this is reason code 4, 5, or 6, perform a CHGDBG command with
   UPDPROD(*YES).
     -- If code 7, remove table names which identify files in QTEMP or to
   program described files.
     -- If code 8, use tables in the same ASP.
     -- If code 9, use the edit rebuild of access path (EDTRBDAP) command
and
   change the sequence of the access path from HELD to 1-99 or *OPN, or
rebuild
   or delete the unique index or constraint.

 ??.


+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---



+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---