Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1249
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: 2285
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: 2285
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: 2285
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: 2285
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: 2285
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 Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us