View previous topic :: View next topic
|
Author |
Message |
sijayapal
New User
Joined: 23 Dec 2008 Posts: 69 Location: India
|
|
|
|
Hi,
We have set of load jobs which uses DSNUTILB to load the data into the database. After loading the tables go into copy pending state. To free the table, there are image copy steps at table space level in the job. There are about 150 tables totally.
Performance wise the image copy steps are taking huge time. Is there anyother approach for this problem instead of taking image copy?
Please kindly share your ideas. Thanks all. |
|
Back to top |
|
|
ramsankar rajkumar
New User
Joined: 07 Jun 2011 Posts: 18 Location: Chennai, India
|
|
|
|
If you change controlcard of load job as mentioned below, you can avoid copy pending state of tables.
LOAD DATA -- REPLACE NOCOPYPEND INDDN SYSREC00 INTO TABLE |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
It is surprising that between load/rebuild/(check)/IC, It is the IC that is the performance bottleneck. But I guess it's dependant on the media where you write your image copies.
Do you need the image copies ? if you don't need a backup (= if necessary, you can reload) , you can "repair". |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Sija,
What kind of you LOAD you are doing ?
LOAD REPLACE - replace entire table's data
LOAD RESUME - append load
If you are doing an LOAD REPLACE, you can do what ramshankar and GuyC suggested(NOCOPYPEND or REPAIR)
If its LOAD RESUME, you did to discuss with DBA regarding recovery strategy and do incremental COPY(only changes since the last image copy), which will reduce time during appilcation batch processing, but may increase little bit DB2 Maintainance Cycle Time.
Thanks,
Sushanth |
|
Back to top |
|
|
sijayapal
New User
Joined: 23 Dec 2008 Posts: 69 Location: India
|
|
|
|
Hi We are using load RESUME.. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Sija,
How many rows are you inserting at each run and How many rows are already existing in the table ?
Thanks,
Sushanth |
|
Back to top |
|
|
sijayapal
New User
Joined: 23 Dec 2008 Posts: 69 Location: India
|
|
|
|
Mostly less than a million... in few jobs it will cross million records |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Sija,
You have following options, but you need to discuss this with your DBA,
1. Start Taking INCREMENTAL image copy - additional maintainance needs to be done for merging image copies
2. Use LOG YES in your load resume - may flood DB2 Logs, if there are any storage constraints.
Quote: |
Performance wise the image copy steps are taking huge time. |
Just curious, can i know how much time Image Copy Step runs ?
Thanks,
Sushanth |
|
Back to top |
|
|
prasanth_thavva
New User
Joined: 28 Jul 2005 Posts: 86 Location: Chennai
|
|
|
|
Regd below response to avoid copypending status suggestion.
"
If you change controlcard of load job as mentioned below, you can avoid copy pending state of tables.
LOAD DATA -- REPLACE NOCOPYPEND INDDN SYSREC00 INTO TABLE"
almost all the shops/projects customizes there load control card like above even though the status of the data base gets changes to COPYPENDING status due to incorrect data like so many reasons. can throw light on this topic how to avoid Copypending status. when ever we get this we used to run a command i beleive its FORCE in Db2 command prompt to reset the database status, or REPAIR utility using batch job.
Thanks
Prasanth |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Forcing/ repairing/ using NOCOPYPEND does have its consequences. I hope you know them when you use it. |
|
Back to top |
|
|
|