MVSFORUMS.com

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

Hi,

I am fetching values and trying to update the Primary Key on a partitioned table, but keep getting a -803 SQLCODE. I also, tried a delete, insert, but the -803 is still occuring. Any ideas on resolve this issue?

coolman · Posted: Mon Aug 14, 2006 2:38 pm Post subject:

The values you are updating are ERR_SW, a SQNC_NUM and a timestamp

From your code, I can see that ERR_SW is always 2, SQNC_NUM is going to fetch the max no of rows in a table and I can't see that changing because there's just an update statement. The only variable entity that I can see here is the CURRENT TIMESTAMP. And I guess these 3 fields form the key. Is this correct?

Can you find this out ? Are you getting a -803 only when SQLCODE = 100 from the select query coz in this case, you just reset the sequence number to zero and then then downstream, you increase it back to 1 and who knows such a sequence might exist out there !!!
________
Luca Marmorini

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

No. All five columns are the key, and that's a date field, not TS. The update is to fetch and change all ERR_SW from '1' to '2', and the MAX SQNC is the only other field that changes on the key. The -803 is on various conditions.

coolman · Posted: Mon Aug 14, 2006 3:34 pm Post subject:

What is OCRNC? I see that in the SQL statements but not as part of your table data depiction? Now that you've said it's only the data and not the timestamp, I'm pretty positive that the chances of a -803 are pretty high. The solution is simple but needs to be manually xpedited. Try printing out the values in the your DCLGEN values that you are trying to update with before attempting the update and do a query on the database to see if they really exist. Definitely, it's a primary key violation which causes this.
________
clear trichomes

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

OCRNC is the number of occurences.

I have displays and the values do not exist, but when the insert or update is executed the result is -803.

coolman · Posted: Mon Aug 14, 2006 3:58 pm Post subject:

1. Are you sure you are doing the queries and the programs being run are pointed to the same DB2 region?

2. Are you having a background trigger that gets executed when this update happens. This trigger might update some other table which might cause the -803.
________
Mercedes-Benz buses

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

1. Yes.

2. no triggers, and this is the only table being update.

kolusu · Posted: Mon Aug 14, 2006 6:20 pm Post subject:

wdnealis,

quite simple take this key

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

You old post on this topic mentioned that "You can only update a primary key if the table is a stand alone table(no child table) or the table is not partitioned.", so I tried the delete/insert but -803 still occurr
_________________

kolusu · Posted: Tue Aug 15, 2006 7:51 am Post subject:

wdnealis,

Why do you have a WHERE statement in your Insert statement? Did the code even complie?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

That's a typo, it's an Insert into, that should be VALUES.

And the SQNC is incremented, but the resutl is -803 eventhough the displaty shows that the value was increased.

kolusu · Posted: Tue Aug 15, 2006 8:23 am Post subject:

wdnealis,

Even if you delete you are once again inserting the same row with '2' in err seqnum. So isn't it a duplicate? Also try adding a COMMIT in between the delete and insert.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

I'm adding to the MAX SQNCto make it unique.

wdnealis · Beginner Joined: 17 Jan 2006 Posts: 25 Topics: 4

The COMMIT works fine.

Thanks again.