View previous topic :: View next topic
|
Author |
Message |
Manshadi
New User
Joined: 31 Aug 2005 Posts: 82
|
|
|
|
Hi,
I am trying to delete part of my records in partition table space(100 part)
with 3 none partition index.
The number of record in the table is around 1,200,000,000 and each none partition index extended to 30 data set (2 gig).
The best solution which I found was deleting the records with online reorg and DISCARD option.
The problem is the BUILD2 phase taking to much time and I don't know why?
Please note that I am using 4 parallel reorg jobs on for example part 1 ,2 ,3 ,4.
Please explain me or proffer some reference to find that what is happening during BUILD2 phase.
Best regards
Manshadi |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Quote: |
In Version 8, during an online reorg (SHRLEVEL REFERENCE or SHLEVEL CHANGE) applying changes to non-partitioned indexes happens in the BUILD2 phase after access has been switched to the second copy of the datasets. The entries in the secondary index that point to rows in the partition being reorged are marked as unavailable until the BUILD2 phase completes. This process could be time consuming and the resulting outage could be a significant problem for applications
In DB2 9, the BUILD2 phase is eliminated for online reorgs. Instead, each partition level reorg will create a shadow copy of the entire NPI and rebuild allentries. This results in greater availability since all shadow datasets may be switched at once. This change happens in DB2 9 Conversion Mode.The new online reorg processing without the BUILD2 phase is required. There is no way to force reorg to process in the Version 8 mode. You should be aware of a few possible ramifications of this change:
•The cost of the REORG will likely increase since the utility is building the entire NPI.
•You will need additional temporary disk space in your storage pool for the shadow data sets of each NPI.
•Since the entire NPI will be built and then switched, it is no longer possible to run reorgs of different parts in separate jobs at the same time.
•If contiguous partitions are being reorged and specified using a range (e.g. PART 2:4) or when SCOPE PENDING is employed, then reorg will use parallelism. |
www.hoadb2ug.org/Docs/Simpson0903.pdf
db2portal.blogspot.com/2007/10/improvements-to-reorg-utility-db2-9-for.html |
|
Back to top |
|
|
Manshadi
New User
Joined: 31 Aug 2005 Posts: 82
|
|
|
|
Hi, Guyc
Thanks for your explanation about this matter.
What exactly I am looking for is entire process of build2 phase.
I mean that what DB2 is doing during this phase such as sorting RIDs ...
Thanks
Manshadi |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you ask IBM. If the specifics are not proprietary, they can answer this. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
Back to top |
|
|
|