Problems insert from REXX after applying WRG21219 UDB 5.2 
Author Message
 Problems insert from REXX after applying WRG21219 UDB 5.2

Hello,

last thursday/friday we had a consultant on site who had to upgrade
our environment to the latest fixlevels. Since this time my app
produces errors that cannot be easily replicated. Backing out is no
option.

Server: OS/2 Warp Server for eBusiness
DB: DB2 UDB 5.2
Application: own - REXX

Basically I get spurious errors (i.e. entering an order once gives the
error, also a second time, on the n'th try it'll succeed).

This is what I can log ( sorry - but error message is german: ->
duplicate key during INSERT....):
22 Nov 2001 08:27:29
Error : 'EXECUTE s1 USING DESCRIPTOR :SQLDATA'; RC = 0; SQLCODE = -803
SQL0803N  Ein oder mehrere durch eine Anweisung DELETE verursachte
Werte in der Anweisung INSERT, der Anweisung UPDATE oder der
Fremdschl sselaktualisierung sind ung ltig, da durch sie f r eine
Tabelle mit Prim„rschl ssel, eindeutiger
Integrit„tsbedingung oder eindeutigem Index gleiche Zeilen
erstellt w rden.  SQLSTATE=23505

STMT = INSERT INTO  AWT.AUFTRAGSPOSITION ( ID,  ID_AUFTRAG,  ID_TEIL,
ID_PRODUKT,  ANZAHL,  ID_FEHLER,  TEILLIEFERUNG,  STATUS,
ERSTELLT_VON)  VALUES (   ?, ?, ?, ?, ?, ?, ?, ?, USER)
SQLDATA.1.SQLDATA =  1862224493
SQLDATA.2.SQLDATA =  1862224493
SQLDATA.3.SQLDATA =  3033
SQLDATA.4.SQLDATA =  1201404650
SQLDATA.5.SQLDATA =  1
SQLDATA.6.SQLDATA =  354

Several indices are defined for this table, but only the primary key
is unique. Primary key is ID. If I do a SELECT on the ID column using
the above value nothing is being found.

The following is done at the start:
:
  NUMERIC DIGITS 12
:
:
  CALL SQLDBS 'CHANGE SQLISL TO UR'
  call StartupDialog.StartupMsg.Text 'Stelle Verbindung zu Datenbank
'DBName' her ...'
  call SQLEXEC 'CONNECT TO 'dbName

The ID value is not _ALWAYS_ unique - it's generated at the
workstation as the number of tenth seconds since X - but I never had
these errors before - for 5 years! Furthermore - if it's not unique I
expect a "first" entry to be there.

Questions:
Did anybody experience compareable problems?
Does anybody have an idea how to solve the problem?
Could it be a new "uncommited read" handling has been introduced?
Fixpack has been applied to server only - clients are WSOD - I do not
know how to apply DB2 fixpak to WSOD installation - any information
here?

Thanks for responding
Wolf



Mon, 10 May 2004 19:00:22 GMT
 Problems insert from REXX after applying WRG21219 UDB 5.2

Wolf,

Since no one else has tried, I'll give it a shot.

First of all - how is ID defined in the database?

Next - are you inserting multiple items without a COMMIT?  If so, you
might have inserted two items with an ID of X (IOW, they both occurred
within the same 0.1 second interval), and both could have been backed
out by a ROLLBACK (explicit or implied).

Quote:

> Hello,

> last thursday/friday we had a consultant on site who had to upgrade
> our environment to the latest fixlevels. Since this time my app
> produces errors that cannot be easily replicated. Backing out is no
> option.

> Server: OS/2 Warp Server for eBusiness
> DB: DB2 UDB 5.2
> Application: own - REXX

> Basically I get spurious errors (i.e. entering an order once gives the
> error, also a second time, on the n'th try it'll succeed).

> This is what I can log ( sorry - but error message is german: ->
> duplicate key during INSERT....):
> 22 Nov 2001 08:27:29
> Error : 'EXECUTE s1 USING DESCRIPTOR :SQLDATA'; RC = 0; SQLCODE = -803
> SQL0803N  Ein oder mehrere durch eine Anweisung DELETE verursachte
> Werte in der Anweisung INSERT, der Anweisung UPDATE oder der
> Fremdschl sselaktualisierung sind ung ltig, da durch sie f r eine
> Tabelle mit Prim„rschl ssel, eindeutiger
> Integrit„tsbedingung oder eindeutigem Index gleiche Zeilen
> erstellt w rden.  SQLSTATE=23505

> STMT = INSERT INTO  AWT.AUFTRAGSPOSITION ( ID,  ID_AUFTRAG,  ID_TEIL,
> ID_PRODUKT,  ANZAHL,  ID_FEHLER,  TEILLIEFERUNG,  STATUS,
> ERSTELLT_VON)  VALUES (   ?, ?, ?, ?, ?, ?, ?, ?, USER)
> SQLDATA.1.SQLDATA =  1862224493
> SQLDATA.2.SQLDATA =  1862224493
> SQLDATA.3.SQLDATA =  3033
> SQLDATA.4.SQLDATA =  1201404650
> SQLDATA.5.SQLDATA =  1
> SQLDATA.6.SQLDATA =  354

> Several indices are defined for this table, but only the primary key
> is unique. Primary key is ID. If I do a SELECT on the ID column using
> the above value nothing is being found.

> The following is done at the start:
> :
>   NUMERIC DIGITS 12
> :
> :
>   CALL SQLDBS 'CHANGE SQLISL TO UR'
>   call StartupDialog.StartupMsg.Text 'Stelle Verbindung zu Datenbank
> 'DBName' her ...'
>   call SQLEXEC 'CONNECT TO 'dbName

> The ID value is not _ALWAYS_ unique - it's generated at the
> workstation as the number of tenth seconds since X - but I never had
> these errors before - for 5 years! Furthermore - if it's not unique I
> expect a "first" entry to be there.

> Questions:
> Did anybody experience compareable problems?
> Does anybody have an idea how to solve the problem?
> Could it be a new "uncommited read" handling has been introduced?
> Fixpack has been applied to server only - clients are WSOD - I do not
> know how to apply DB2 fixpak to WSOD installation - any information
> here?

> Thanks for responding
> Wolf

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

====================================



Fri, 14 May 2004 07:57:28 GMT
 Problems insert from REXX after applying WRG21219 UDB 5.2

Quote:

> Wolf,

> Since no one else has tried, I'll give it a shot.

> First of all - how is ID defined in the database?

> Next - are you inserting multiple items without a COMMIT?  If so, you
> might have inserted two items with an ID of X (IOW, they both occurred
> within the same 0.1 second interval), and both could have been backed
> out by a ROLLBACK (explicit or implied).


> > Hello,

> > last thursday/friday we had a consultant on site who had to upgrade
> > our environment to the latest fixlevels. Since this time my app
> > produces errors that cannot be easily replicated. Backing out is no
> > option.

> > Server: OS/2 Warp Server for eBusiness
> > DB: DB2 UDB 5.2
> > Application: own - REXX

> > Basically I get spurious errors (i.e. entering an order once gives the
> > error, also a second time, on the n'th try it'll succeed).

> > This is what I can log ( sorry - but error message is german: ->
> > duplicate key during INSERT....):
> > 22 Nov 2001 08:27:29
> > Error : 'EXECUTE s1 USING DESCRIPTOR :SQLDATA'; RC = 0; SQLCODE = -803
> > SQL0803N  Ein oder mehrere durch eine Anweisung DELETE verursachte
> > Werte in der Anweisung INSERT, der Anweisung UPDATE oder der
> > Fremdschl sselaktualisierung sind ung ltig, da durch sie f r eine
> > Tabelle mit Prim„rschl ssel, eindeutiger
> > Integrit„tsbedingung oder eindeutigem Index gleiche Zeilen
> > erstellt w rden.  SQLSTATE=23505

> > STMT = INSERT INTO  AWT.AUFTRAGSPOSITION ( ID,  ID_AUFTRAG,  ID_TEIL,
> > ID_PRODUKT,  ANZAHL,  ID_FEHLER,  TEILLIEFERUNG,  STATUS,
> > ERSTELLT_VON)  VALUES (   ?, ?, ?, ?, ?, ?, ?, ?, USER)
> > SQLDATA.1.SQLDATA =  1862224493
> > SQLDATA.2.SQLDATA =  1862224493
> > SQLDATA.3.SQLDATA =  3033
> > SQLDATA.4.SQLDATA =  1201404650
> > SQLDATA.5.SQLDATA =  1
> > SQLDATA.6.SQLDATA =  354

> > Several indices are defined for this table, but only the primary key
> > is unique. Primary key is ID. If I do a SELECT on the ID column using
> > the above value nothing is being found.

> > The following is done at the start:
> > :
>  NUMERIC DIGITS 12
> > :
> > :
> >   CALL SQLDBS 'CHANGE SQLISL TO UR'
> >   call StartupDialog.StartupMsg.Text 'Stelle Verbindung zu Datenbank
> > 'DBName' her ...'
> >   call SQLEXEC 'CONNECT TO 'dbName

> > The ID value is not _ALWAYS_ unique - it's generated at the
> > workstation as the number of tenth seconds since X - but I never had
> > these errors before - for 5 years! Furthermore - if it's not unique I
> > expect a "first" entry to be there.

> > Questions:
> > Did anybody experience compareable problems?
> > Does anybody have an idea how to solve the problem?
> > Could it be a new "uncommited read" handling has been introduced?
> > Fixpack has been applied to server only - clients are WSOD - I do not
> > know how to apply DB2 fixpak to WSOD installation - any information
> > here?

> > Thanks for responding
> > Wolf

Thanks for answering!

ID is defined as INT, NOT NULL. We're below 2G. Yes it may happen,
that I'm inserting 1-5 or 10 items before committing - but - the
behaviour did not change from last week to now. And as I wrote, the
application runs for over 5 years now....

I agree that it might be that I'm trying to insert something, and the
offending line gets rolled back as well - but agian - why is this
happening now? Does DB2 try to block requests more than before,
something like "oh - a new SQL statement, let's sit here for a while
before we acutally do execute it, it might be that others want to be
executed as well...."

I changed the logic over the weekend to use an ID table, and I changed
from UR to cursor stability. Let's see what happens.

Regards
Wolf



Fri, 14 May 2004 15:17:37 GMT
 Problems insert from REXX after applying WRG21219 UDB 5.2
Wolf,

Well, my first guess could be soemthing runs faster, possibly due to
optimization of something you're using.  One or more of the fixes could
have sped things up such that something which used to take > 0.1s now
takes less than that.

It could be the REXX interpreter, network connection (if you're running
on a network), DB2, or something else.

I've seen a lot of "well, it ran for X years before" type of comments.
That doesn't necessarily mean it's right - just that you haven't been
caught before.  I saw a great example of this a while back when someone
upgraded to a faster server.  Some web stuff which used to work failed
quite regularly.  They of course blamed the machine.  The real problem
was timing in a multithread program - which "used to work".

Quote:


> > Wolf,

> > Since no one else has tried, I'll give it a shot.

> > First of all - how is ID defined in the database?

> > Next - are you inserting multiple items without a COMMIT?  If so, you
> > might have inserted two items with an ID of X (IOW, they both occurred
> > within the same 0.1 second interval), and both could have been backed
> > out by a ROLLBACK (explicit or implied).


> > > Hello,

> > > last thursday/friday we had a consultant on site who had to upgrade
> > > our environment to the latest fixlevels. Since this time my app
> > > produces errors that cannot be easily replicated. Backing out is no
> > > option.

> > > Server: OS/2 Warp Server for eBusiness
> > > DB: DB2 UDB 5.2
> > > Application: own - REXX

> > > Basically I get spurious errors (i.e. entering an order once gives the
> > > error, also a second time, on the n'th try it'll succeed).

> > > This is what I can log ( sorry - but error message is german: ->
> > > duplicate key during INSERT....):
> > > 22 Nov 2001 08:27:29
> > > Error : 'EXECUTE s1 USING DESCRIPTOR :SQLDATA'; RC = 0; SQLCODE = -803
> > > SQL0803N  Ein oder mehrere durch eine Anweisung DELETE verursachte
> > > Werte in der Anweisung INSERT, der Anweisung UPDATE oder der
> > > Fremdschl sselaktualisierung sind ung ltig, da durch sie f r eine
> > > Tabelle mit Prim„rschl ssel, eindeutiger
> > > Integrit„tsbedingung oder eindeutigem Index gleiche Zeilen
> > > erstellt w rden.  SQLSTATE=23505

> > > STMT = INSERT INTO  AWT.AUFTRAGSPOSITION ( ID,  ID_AUFTRAG,  ID_TEIL,
> > > ID_PRODUKT,  ANZAHL,  ID_FEHLER,  TEILLIEFERUNG,  STATUS,
> > > ERSTELLT_VON)  VALUES (   ?, ?, ?, ?, ?, ?, ?, ?, USER)
> > > SQLDATA.1.SQLDATA =  1862224493
> > > SQLDATA.2.SQLDATA =  1862224493
> > > SQLDATA.3.SQLDATA =  3033
> > > SQLDATA.4.SQLDATA =  1201404650
> > > SQLDATA.5.SQLDATA =  1
> > > SQLDATA.6.SQLDATA =  354

> > > Several indices are defined for this table, but only the primary key
> > > is unique. Primary key is ID. If I do a SELECT on the ID column using
> > > the above value nothing is being found.

> > > The following is done at the start:
> > > :
> >  NUMERIC DIGITS 12
> > > :
> > > :
> > >   CALL SQLDBS 'CHANGE SQLISL TO UR'
> > >   call StartupDialog.StartupMsg.Text 'Stelle Verbindung zu Datenbank
> > > 'DBName' her ...'
> > >   call SQLEXEC 'CONNECT TO 'dbName

> > > The ID value is not _ALWAYS_ unique - it's generated at the
> > > workstation as the number of tenth seconds since X - but I never had
> > > these errors before - for 5 years! Furthermore - if it's not unique I
> > > expect a "first" entry to be there.

> > > Questions:
> > > Did anybody experience compareable problems?
> > > Does anybody have an idea how to solve the problem?
> > > Could it be a new "uncommited read" handling has been introduced?
> > > Fixpack has been applied to server only - clients are WSOD - I do not
> > > know how to apply DB2 fixpak to WSOD installation - any information
> > > here?

> > > Thanks for responding
> > > Wolf

> Thanks for answering!

> ID is defined as INT, NOT NULL. We're below 2G. Yes it may happen,
> that I'm inserting 1-5 or 10 items before committing - but - the
> behaviour did not change from last week to now. And as I wrote, the
> application runs for over 5 years now....

> I agree that it might be that I'm trying to insert something, and the
> offending line gets rolled back as well - but agian - why is this
> happening now? Does DB2 try to block requests more than before,
> something like "oh - a new SQL statement, let's sit here for a while
> before we acutally do execute it, it might be that others want to be
> executed as well...."

> I changed the logic over the weekend to use an ID table, and I changed
> from UR to cursor stability. Let's see what happens.

> Regards
> Wolf

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

====================================



Fri, 14 May 2004 21:38:37 GMT
 Problems insert from REXX after applying WRG21219 UDB 5.2
Quote:

> Wolf,

> Well, my first guess could be soemthing runs faster, possibly due to
> optimization of something you're using.  One or more of the fixes could
> have sped things up such that something which used to take > 0.1s now
> takes less than that.

> It could be the REXX interpreter, network connection (if you're running
> on a network), DB2, or something else.

> I've seen a lot of "well, it ran for X years before" type of comments.
> That doesn't necessarily mean it's right - just that you haven't been
> caught before.  I saw a great example of this a while back when someone
> upgraded to a faster server.  Some web stuff which used to work failed
> quite regularly.  They of course blamed the machine.  The real problem
> was timing in a multithread program - which "used to work".



> > > Wolf,

> > > Since no one else has tried, I'll give it a shot.

> > > First of all - how is ID defined in the database?

> > > Next - are you inserting multiple items without a COMMIT?  If so, you
> > > might have inserted two items with an ID of X (IOW, they both occurred
> > > within the same 0.1 second interval), and both could have been backed
> > > out by a ROLLBACK (explicit or implied).


> > > > Hello,

> > > > last thursday/friday we had a consultant on site who had to upgrade
> > > > our environment to the latest fixlevels. Since this time my app
> > > > produces errors that cannot be easily replicated. Backing out is no
> > > > option.

> > > > Server: OS/2 Warp Server for eBusiness
> > > > DB: DB2 UDB 5.2
> > > > Application: own - REXX

> > > > Basically I get spurious errors (i.e. entering an order once gives the
> > > > error, also a second time, on the n'th try it'll succeed).

> > > > This is what I can log ( sorry - but error message is german: ->
> > > > duplicate key during INSERT....):
> > > > 22 Nov 2001 08:27:29
> > > > Error : 'EXECUTE s1 USING DESCRIPTOR :SQLDATA'; RC = 0; SQLCODE = -803
> > > > SQL0803N  Ein oder mehrere durch eine Anweisung DELETE verursachte
> > > > Werte in der Anweisung INSERT, der Anweisung UPDATE oder der
> > > > Fremdschl?sselaktualisierung sind ung?ltig, da durch sie f?r eine
> > > > Tabelle mit Prim„rschl?ssel, eindeutiger
> > > > Integrit„tsbedingung oder eindeutigem Index gleiche Zeilen
> > > > erstellt w?rden.  SQLSTATE=23505

> > > > STMT = INSERT INTO  AWT.AUFTRAGSPOSITION ( ID,  ID_AUFTRAG,  ID_TEIL,
> > > > ID_PRODUKT,  ANZAHL,  ID_FEHLER,  TEILLIEFERUNG,  STATUS,
> > > > ERSTELLT_VON)  VALUES (   ?, ?, ?, ?, ?, ?, ?, ?, USER)
> > > > SQLDATA.1.SQLDATA =  1862224493
> > > > SQLDATA.2.SQLDATA =  1862224493
> > > > SQLDATA.3.SQLDATA =  3033
> > > > SQLDATA.4.SQLDATA =  1201404650
> > > > SQLDATA.5.SQLDATA =  1
> > > > SQLDATA.6.SQLDATA =  354

> > > > Several indices are defined for this table, but only the primary key
> > > > is unique. Primary key is ID. If I do a SELECT on the ID column using
> > > > the above value nothing is being found.

> > > > The following is done at the start:
> > > > :
> > >  NUMERIC DIGITS 12
> > > > :
> > > > :
> > > >   CALL SQLDBS 'CHANGE SQLISL TO UR'
> > > >   call StartupDialog.StartupMsg.Text 'Stelle Verbindung zu Datenbank
> > > > 'DBName' her ...'
> > > >   call SQLEXEC 'CONNECT TO 'dbName

> > > > The ID value is not _ALWAYS_ unique - it's generated at the
> > > > workstation as the number of tenth seconds since X - but I never had
> > > > these errors before - for 5 years! Furthermore - if it's not unique I
> > > > expect a "first" entry to be there.

> > > > Questions:
> > > > Did anybody experience compareable problems?
> > > > Does anybody have an idea how to solve the problem?
> > > > Could it be a new "uncommited read" handling has been introduced?
> > > > Fixpack has been applied to server only - clients are WSOD - I do not
> > > > know how to apply DB2 fixpak to WSOD installation - any information
> > > > here?

> > > > Thanks for responding
> > > > Wolf

> > Thanks for answering!

> > ID is defined as INT, NOT NULL. We're below 2G. Yes it may happen,
> > that I'm inserting 1-5 or 10 items before committing - but - the
> > behaviour did not change from last week to now. And as I wrote, the
> > application runs for over 5 years now....

> > I agree that it might be that I'm trying to insert something, and the
> > offending line gets rolled back as well - but agian - why is this
> > happening now? Does DB2 try to block requests more than before,
> > something like "oh - a new SQL statement, let's sit here for a while
> > before we acutally do execute it, it might be that others want to be
> > executed as well...."

> > I changed the logic over the weekend to use an ID table, and I changed
> > from UR to cursor stability. Let's see what happens.

> > Regards
> > Wolf

> --
> ====================================
> To reply, delete the 'x' from my email

> Jerry Stuckle
> JDS Computer Training Corp.

> ====================================

It seems that you are right - I implemented the number range-table - et
voil.
Altough I do not believe it's the network or the machine - my next guess
would be a time resolution issue in REXX. But - since this is a "legacy"
application and I'm having to do "serious work" - we won't ever know.
(For the same reason I won't be looking into ID datatype and all the
stuff they introduced after I switched from programming to "stone age"
SAP technology....8-(  )

Thanks again for commenting on this!
Wolf



Sun, 16 May 2004 05:21:38 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Error in Java Program, when it accesses DB2 UDB 7.2 from DB2 UDB 5.2 Client

2. Restoring DB2 UDB 5.2 to UDB 7.2 question.

3. Upgrade vom UDB 5.2 to UDB 6.1

4. Upgrading from UDB 5.2 NT - any incompatibilty problems?

5. UDB 5.2 Problem with varchar longer than 254 chars

6. DB2 UDB 5.2 and Decimal Fields

7. VB6 & UDB 5.2

8. JDBC 2.0 with DB2/Solaris UDB 5.2

9. UDB upgrade from 5.2 to 7.2

10. Restoring DB2 UDB 5.2 to 7.2

11. Restore question under DB2 UDB 5.2 for Windows NT

12. DECIMAL columns with DB2 UDB 5.2 and MDAC 2.6


 
Powered by phpBB® Forum Software