IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

BUILD2 Phase on Online Reorg with DISCARD Option.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Thu Aug 19, 2010 8:00 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Aug 19, 2010 9:12 pm
Reply with quote

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
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Sat Aug 21, 2010 12:26 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat Aug 21, 2010 8:21 pm
Reply with quote

Hello,

Suggest you ask IBM. If the specifics are not proprietary, they can answer this.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Aug 27, 2010 2:46 pm
Reply with quote

robertsdb2blog.blogspot.com/2010/08/db2-9-for-zos-important-enhancement-of.html
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Reorg abended with REASON=X'00E40347' DB2 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts OUTFIL with SAVE option DFSORT/ICETOOL 7
No new posts CICS vs LE: STORAGE option CICS 0
No new posts Underscore was not populated to onlin... CICS 1
Search our Forums:

Back to Top