Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Regarding Update records on different conditions

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Fri Dec 12, 2008 11:22 pm    Post subject: Regarding Update records on different conditions
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Dec 12, 2008 11:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Sat Dec 13, 2008 12:15 am    Post subject:
Reply with quote

Does this program use DB2 only or DB2 and IMS where it runs under IMS control?
Back to top
View user's profile Send private message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Sat Dec 13, 2008 1:43 pm    Post subject:
Reply with quote

Terry,
This uses Db2 only.

regards,
Nithya
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Sat Dec 13, 2008 3:59 pm    Post subject:
Reply with quote

What about the SQLCODE after UPDATE, Did you DISPLAY that also?
Back to top
View user's profile Send private message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Sat Dec 13, 2008 4:17 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Sat Dec 13, 2008 4:20 pm    Post subject:
Reply with quote

-803 indicates that you tried to violate a unique index. You'll have to change your logic to avoid this situation.
Back to top
View user's profile Send private message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Sat Dec 13, 2008 4:26 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Sat Dec 13, 2008 4:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Sat Dec 13, 2008 4:42 pm    Post subject:
Reply with quote

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 icon_smile.gif and my fault that i didnt analyse unique indexes of the table :-( .

Is there any alternative solution ?

Regards,
Nithya
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Sat Dec 13, 2008 4:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Sat Dec 13, 2008 5:00 pm    Post subject:
Reply with quote

Thank you for the suggestion Arun.

Thanks and regards,
Nithya
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Dec 14, 2008 12:23 am    Post subject:
Reply with quote

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
View user's profile Send private message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Mon Dec 15, 2008 8:10 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Mon Dec 15, 2008 8:16 pm    Post subject:
Reply with quote

Nice to hear that at last you got this working. But I wonder how this 'random number generator' came into picture. icon_rolleyes.gif
Back to top
View user's profile Send private message
Nithya.U.M
Warnings : 1

New User


Joined: 11 Nov 2008
Posts: 21
Location: India

PostPosted: Mon Dec 15, 2008 10:36 pm    Post subject: Reply to: Regarding Update records on different conditions
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us