Re: JDBC update error

I use  “wrksplf *all” and I don’t see a job log for this SQL7008 error. I am
using jdbc driver com.ibm.as400.access.AS400JDBCDriver to access as400 from
PC, which job is run on as400?

When I changed to use native driver com.ibm.db2.jdbc.app.DB2Driver() and ran
this program on as400 (v4r4), it ran correctly! Both rows were updated!

Bruce

-----Original Message-----
From: cujo@us.ibm.com <cujo@us.ibm.com>
To: JAVA400-L@midrange.com <JAVA400-L@midrange.com>
Date: Thursday, November 16, 2000 5:49 PM
Subject: 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
>+---

+---
| 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
+---