| View previous topic :: View next topic |
| Author |
Message |
monaco
Beginner
Joined: 20 May 2004
Posts: 77
Topics: 31
|
 Posted: Wed Sep 22, 2004 1:57 pm Post subject: ADD & UPDATE |
 |
|
Hi all,
We wonder if you could help us with a DB2 problem.
We have a DB2 database that is a copy of of our master DB2 database. This copy can be changed by everybody, ie they can add values to a table, modify them or delete values from a table. Each week we authorize these changes and then want to extract them into the DB2 master. We retrieve all the rows that were added or updated (with a SELECT on the associated timestamp).
Our next step is to load this extract into the DB2 master (using RESUME YES). But rows that have been updated (the added rows load fine), would not load due to primary key violation ("duplicate primary key"). Is there any way to prevent this from happening and by loading the data also updating the rows in the DB2 master that have been updated in the DB2 copy, instead of them being discarded?
We are very grateful for any support, help or ideas you can give us, since we've been thinking over this problem for the last few weeks without finding a proper solution. Thank you very much for all your time and input! |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Sep 22, 2004 5:53 pm Post subject: |
|
|
Monaco,
There are many ways to do it.
Approach 1:
| Code: |
1. Generate delete sql statements with commit after every 500 sql statemtnts for all updated keys.
2. Run the delete step prior to Loading
3. Now run the load utilty with RESUME yes option.
|
Check this link which discusses about generating and running dynamic sql statements.
http://www.mvsforums.com/helpboards/viewtopic.php?t=2787&highlight=commit
Approach 2: If the table volume is less than 5 million , then
| Code: |
1. LOAD replace will be a good option.
|
Approach 3:
| Code: |
1. Have the table unload prior to the changes
2. Have another unload of the table after all the changes.
3. Run a pgm/utility to compare and create a load file
4. Load Replace using the output from step3
|
Personally I prefer Approach 1 or 2.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
monaco
Beginner
Joined: 20 May 2004
Posts: 77
Topics: 31
|
| Posted: Fri Sep 24, 2004 4:57 am Post subject: |
|
|
| Thanks a lot. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
