RE: SQLCODE -907

[tomh@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Both on the same machine.  When we looked up -907, we found that it said
something like "attempt to update same row twice".

Tom Hightower
Solutions, Inc
http://www.simas.com





Gord Royle <GRoyle@cott.com>
Sent by: midrange-l-admin@midrange.com
03/13/02 09:27 AM
Please respond to midrange-l


        To:     "'midrange-l@midrange.com'" <midrange-l@midrange.com>
        cc:
        Subject:        RE: SQLCODE -907


The text that I pulled from my manual points to a communications problem
between the AS400 and a remote. Are these tables on different machines?
I've
included the text below.

<  -907   UNABLE TO DETERMINE IF THE ATTEMPT TO COMMIT UPDATES MADE AT A
          REMOTE SITE WAS SUCCESSFUL OR NOT.

   Explanation:  A system error occurred at the remote site where the
updates
   were made, or a communication failure with the remote site occurred.
The

   error occurred after the request to commit the updates was transmitted
to

   the site.  The error prevents DB2 from determining whether the commit
   operation succeeded.

   System Action:  If the commit operation succeeded, the updates appear
in
   the data stored at the remote site.  If the operation did not succeed,
the
   updates are backed out.

   The SQL COMMIT operation was not completed and an 0FE abend was
requested

   for the application.  The application program, however, may have a
   recovery routine and may retry SQL statements.

   Programmer Response:  After communication has been reestablished with
the

   remote site, you can use SQL to query the data to determine whether the
   COMMIT operation was successful.

   An application that receives this return code is allowed to retry or
   execute additional SQL statements.

   SQLSTATE:  58007
>

Gord Royle
Cott Corporation

-----Original Message-----
From: tomh@simas.com [mailto:tomh@simas.com]
Sent: Wednesday, March 13, 2002 9:27 AM
To: midrange-l@midrange.com
Subject: SQLCODE -907


This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I'm having a trigger problem.  We've got an SQLC program compiled with
Commitment Control = *ALL.  The program updates a sequential-number field
in one table ("AUTONUMBER"), issues a COMMIT on the "AUTONUMBER" table,
and then writes that number to a row in another table ("CASE").  The
trigger has been added to table "CASE", trigger time=*AFTER, trigger
event=*INSERT.

In testing, we run STRSQL command to INSERT INTO "CASE" (list_of_fields)
VALUES(list_of_values) WITH CS.  (I've also tried WITH RS).

The SQLC program properly updates the AUTONUMBER table, but is failing
with SQLCODE -907 on table "CASE".  If I remove the "WITH CS" from the SQL
INSERT command, both tables update without an error message but a ROLLBACK
command doesn't work.

How can I get around this?