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
 

 

DB2 utility for Insert and Update

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ibmlearner

New User


Joined: 07 May 2007
Posts: 7
Location: Bangalore

PostPosted: Thu Apr 28, 2011 10:04 pm    Post subject: DB2 utility for Insert and Update
Reply with quote

Hello Folks,

Before read this query, I just wanted to let you know guys that DSNUTILB LOAD utility will not perform update. LOAD utility used to place the bulk data.

And one more thing, we can go for Cobol+DB2 program, However I wanted to know if any DB2 utility performs Insert and Update as well.
Now Please read the below query.

I have a requirement where I need to insert & update the DB2 table. Challenge is here

Insert ---> Insert new records
Update --> Based on the Key columns - need to update couple of columns

Example for update:

ACCTNO ACCTNAME
111111 DEBBIE
222222 CINDY

Now I need to update the ACCTNO=333333 if the ACCTNAME is CINDY, so after the update DB2 table should be look like this.

ACCTNO ACCTNAME
111111 DEBBIE
333333 CINDY

Remember, Record=1 (111111 DEBBIE) should remain on the Table.

I appreciate your help on this.

-ibmlearner
Back to top
View user's profile Send private message

daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Thu Apr 28, 2011 10:51 pm    Post subject:
Reply with quote

IBM Optim (formerly Princeton SofTech) can do that.

It will cost your company money however.
Back to top
View user's profile Send private message
ibmlearner

New User


Joined: 07 May 2007
Posts: 7
Location: Bangalore

PostPosted: Thu Apr 28, 2011 10:59 pm    Post subject:
Reply with quote

daveporcelan wrote:
IBM Optim (formerly Princeton SofTech) can do that.

It will cost your company money however.


Thanks for your reply Celan!

So, you meant to say that, we can not perform with any of the current DB2 utility..
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Thu Apr 28, 2011 11:04 pm    Post subject:
Reply with quote

You should look in the manual.

Look for keyword RESUME.

In your case if the primary index of the table is the account number, then no utility I know of can help you. You would need to write a program.
Back to top
View user's profile Send private message
ibmlearner

New User


Joined: 07 May 2007
Posts: 7
Location: Bangalore

PostPosted: Thu Apr 28, 2011 11:25 pm    Post subject:
Reply with quote

daveporcelan wrote:
You should look in the manual.

Look for keyword RESUME.

In your case if the primary index of the table is the account number, then no utility I know of can help you. You would need to write a program.



RESUME - YES/NO
REPLACE

Above two parameters on the LOAD control statement will not perform update as per my requirement. I have tested already.

Thanks for your time Celan
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Fri Apr 29, 2011 12:12 am    Post subject:
Reply with quote

Is the data already in the table? If so, why can you not use SQL to perform the update?
Back to top
View user's profile Send private message
ibmlearner

New User


Joined: 07 May 2007
Posts: 7
Location: Bangalore

PostPosted: Fri Apr 29, 2011 11:19 pm    Post subject:
Reply with quote

don.leahy wrote:
Is the data already in the table? If so, why can you not use SQL to perform the update?



Don, I believe you have not read my post clearly. I need to update the table based on the key fields also need to insert the new records by using any DB2 utility. I know LOAD will perform mass insert but not update. Also I can go with Cobol + DB2 program, however I need to do thru any DB2 utility.

Thanks
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Fri Apr 29, 2011 11:36 pm    Post subject:
Reply with quote

ibmlearner wrote:
don.leahy wrote:
Is the data already in the table? If so, why can you not use SQL to perform the update?



Don, I believe you have not read my post clearly. I need to update the table based on the key fields also need to insert the new records by using any DB2 utility. I know LOAD will perform mass insert but not update. Also I can go with Cobol + DB2 program, however I need to do thru any DB2 utility.

Thanks
Never mind. Your requirement is not stated clearly enough for me to provide any advice.
Back to top
View user's profile Send private message
ibmlearner

New User


Joined: 07 May 2007
Posts: 7
Location: Bangalore

PostPosted: Fri Apr 29, 2011 11:42 pm    Post subject:
Reply with quote

don.leahy wrote:
ibmlearner wrote:
don.leahy wrote:
Is the data already in the table? If so, why can you not use SQL to perform the update?



Don, I believe you have not read my post clearly. I need to update the table based on the key fields also need to insert the new records by using any DB2 utility. I know LOAD will perform mass insert but not update. Also I can go with Cobol + DB2 program, however I need to do thru any DB2 utility.

Thanks
Never mind. Your requirement is not stated clearly enough for me to provide any advice.


Don, I am not sure where you have got confused on my requirement. Please go thru my post and let me know what information needed more from my side to make it success.

Or still you are not clear please let me know I can leverage it.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Sat Apr 30, 2011 12:25 am    Post subject:
Reply with quote

ibmlearner,

what is not clear is of the two columns, which is the key?

acctno, acctname or both?

If it is acctno or both, then you can not possibility update 222222 CINDY with 333333 CINDY with any utility.

How is the utility to know you are not try to insert this as a new record?

You are going to need a program with logic provided by you (ugh), for this to work.

This poor horse has been beaten to death. Please do not kick it any more.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sun May 01, 2011 10:00 am    Post subject:
Reply with quote

IBMlearner,

Learn this, LOAD utility is load basically, FULL LOAD(resume no) or APPEND LOAD(resume yes), anything other than this better to write a program.

As daveporcelan said,
Quote:
You are going to need a program with logic provided by you (ugh), for this to work.

In your program use MERGE statement, that fits your requirement.

For more infomation, read the SQL REFERENCE manual and you did not mention what version of DB2 you are using, if DB2 V9(good for you, use MERGE), if less start coding.

Thanks,
Sushanth
Back to top
View user's profile Send private message
Dhrubojoty mukherjee

New User


Joined: 23 Mar 2010
Posts: 14
Location: kolkata

PostPosted: Sun Nov 17, 2013 1:46 pm    Post subject:
Reply with quote

For merge (using batch spufi) there need to have another table/view from where the date would be updated or inserted into the table depending on some unique constrains.
But for load when the data comes from a file then I think there in no way to to process it.
So to do this one have to create a table loaded with the recent data and then use merge with the base table and then delete the previous table.
Please correct me if I am wrong... icon_smile.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Nov 18, 2013 8:30 am    Post subject:
Reply with quote

Dhruv,

I was suggesting to use MERGE statement in a COBOL+DB2 program not in SPUFI.

Thanks,
Sushanth
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 -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
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm


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