|
View previous topic :: View next topic
|
| Author |
Message |
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
Hi ,
I am performing a insert in table1 and update table2 based on different conditions. After the insert and After all updates i have given commit. The sql code returned from the update statement is zero and the program also doesnt have any abends. But only insert of the table was working fine and none of the records where updated though control has reached the update( had put displays in the program to find out the flow). i am unable to understand why table was not updated. can i be the problem with update statement ? if so why was the sql code from update zero ? any suggestions as to what could be the problem and which direction i should analyse to solve it ? |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Is there a ROLLBACK anywhere in the code? If there is, put a display there and see if the rollback is issued for some reason during an update.
You might change your input data so that there will only be 1 update (and no inserts) and debug from there. |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Does this program use DB2 only or DB2 and IMS where it runs under IMS control? |
|
| Back to top |
|
|
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
Terry,
This uses Db2 only.
regards,
Nithya |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| What about the SQLCODE after UPDATE, Did you DISPLAY that also? |
|
| Back to top |
|
|
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
arun ,
i had debugged the code and sql code after update query is -803. No idea why this occured as the value i am trying to update with is not present in the table and the table has multiple rows for the same facility id which i am trying to update under certain conditions.
Thanks and regards,
Nithya |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| -803 indicates that you tried to violate a unique index. You'll have to change your logic to avoid this situation. |
|
| Back to top |
|
|
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
arun,
the unique index for this table is facil-no, seq-no,cmpnt-type ,cd-main,date-ef.
i am trying to update facil-no and seq-no . i want to update these values should i delete the row and then update ?
thanks and regards,
Nithya |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| should i delete the row and then update ? |
Whoever designed this should have expected such a case. All these depend on your requirement. I cant decide on that. |
|
| Back to top |
|
|
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
Arun,
It was a part of the requirement to update the "Cmpnt table", to update facil-no and seq-no after inserting in another table viz facility table.
Design I had provided based on the requirements  and my fault that i didnt analyse unique indexes of the table :-( .
Is there any alternative solution ?
Regards,
Nithya |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hello,
| Quote: |
| Is there any alternative solution ? |
It's nobody but you who knows what goes on in your system. Nobody here knows what your program is supposed to do. I would suggest revisiting your design first. |
|
| Back to top |
|
|
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
Thank you for the suggestion Arun.
Thanks and regards,
Nithya |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You might consider (on an update) determining if the value about to be updated already exists. Even if you detect the existence of the "new key", you need to determine what should happen. A programmed "duplicate error" is better than an abend due to an unacceptable sql return code (IMHO).
Almost anything can be done but what should be done depends on your business. Your business rules/requirements need to be completely understood before continuing.
We can help with implementation, but we probably cannot contribute much to what the rules should be. |
|
| Back to top |
|
|
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
Dick,
I changed my design and code to include a random number generator module and a duplicate check for the unique indexes of both the tables using select statement.
It worked fine and i have done with the unit testing too successfully.
Thanks for all the suggestions given via this forum . It really helps to have better understanding of the issues discussing with so many experienced hands.
Thanks once again .
Regards,
Nithya |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Nice to hear that at last you got this working. But I wonder how this 'random number generator' came into picture.  |
|
| Back to top |
|
|
Nithya.U.M
Warnings : 1
New User
Joined: 11 Nov 2008
Posts: 21
Location: India
|
|
|
|
Arun,
The random number generator is a module that i have coded which generated any number randomly based on the input time stamp given. this wud ensure that the number generated is unique and then check for duplicates.
Earlier i was using a different logic which was more error- prone and cud generate duplicate numbers.
Thanks and regards,
Nithya |
|
| Back to top |
|
|
|
|
