|
View previous topic :: View next topic
|
| Author |
Message |
ibmlearner
New User
Joined: 07 May 2007
Posts: 7
Location: Bangalore
|
|
|
|
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 |
|
 |
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
IBM Optim (formerly Princeton SofTech) can do that.
It will cost your company money however. |
|
| Back to top |
|
|
ibmlearner
New User
Joined: 07 May 2007
Posts: 7
Location: Bangalore
|
|
|
|
| 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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
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 |
|
|
ibmlearner
New User
Joined: 07 May 2007
Posts: 7
Location: Bangalore
|
|
|
|
| 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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Is the data already in the table? If so, why can you not use SQL to perform the update? |
|
| Back to top |
|
|
ibmlearner
New User
Joined: 07 May 2007
Posts: 7
Location: Bangalore
|
|
|
|
| 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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| 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 |
|
|
ibmlearner
New User
Joined: 07 May 2007
Posts: 7
Location: Bangalore
|
|
|
|
| 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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Dhrubojoty mukherjee
New User
Joined: 23 Mar 2010
Posts: 14
Location: kolkata
|
|
|
|
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...  |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Dhruv,
I was suggesting to use MERGE statement in a COBOL+DB2 program not in SPUFI.
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
