We are looking to convert our application tablespaces and indexes to use DB2 sliding scale allocations (PRIQTY -1 SECQTY -1) across all of our DB2 subsystems.
We've already done this for one of our applications in one of our subsystems, and followed up the ALTER TABLESPACE and ALTER INDEX statements with REORGs of all of the tablespaces just to make sure the "new" allocations are in place.
But as we look to roll this out across the board for everybody else, we don't have the appetite to follow up with tens of thousands of REORGs. The plan is to just update the catalog with PRIQTY -1 SECQTY -1 and let nature take its course. Tables will get LOAD REPLACED or REORGed over time just from normal daily work.
My specific question is: if we do the alters for PRIQTY -1 SECQTY -1 and do not do REORGs, what will happen when a tablespace or an index takes the next secondary allocation due to normal growth (i.e. inserts)? Will the sliding scale methodology kick in immediately, or will the "old" secondary allocations that were in place when the physical VSAM datasets were created still occur?
Or, to rephrase: do the physical VSAM datasets need to be created with sliding scale allocations defined in order to take advantage of the methodology?
If the catalog name changes, the changes take effect after you move the data and start the table space or partition using the START DATABASE…SPACENAM… command. The catalog name can be implicitly or explicitly changed by the ALTER TABLESPACE statement. The catalog name also changes when you move the data to a different device. See the procedures for moving data in Db2 Administration Guide.
Changes to the secondary space allocation (SECQTY) take effect the next time Db2 extends the data set; however, the new value is not reflected in the integrated catalog until you use the REORG, RECOVER, or LOAD REPLACE utility on the table space or partition. The changes to the other storage attributes take effect the next time the page set is reset. For a non-LOB table space, the page set is reset when you use the REORG, RECOVER, or LOAD REPLACE utilities on the table space or partition. For a LOB table space, the page set is reset when RECOVER is run on the LOB table space or LOAD REPLACE is run on its associated base table space. If there is not enough storage to satisfy the primary space allocation, a REORG might fail. If you change the primary space allocation parameters or erase rule, you can have the changes take effect earlier if you move the data before you start the table space or partition.HTHJack
Thanks to all for the comments and discussion on this topic.
We piloted a blanket sliding scale implementation on one of our larger database applications in a pre-production subsystem a couple of months. Things have been running very smoothly with no issues. We've monitored our largest tablespace and index objects in that database in terms of space management and extents, and have seen nothing that gives us any pause about continuing forward.
We did have a discussion to talk about whether we wanted or needed to address whether we might still want to hard-code values for PRIQTY for our larger tablespace objects, and ultimately decided that because we're confident with how our storage pools and current space monitoring and alerts are set up, we will continue to move forward with a blanket approach of PRIQTY -1 SECQTY -1 rather than a hybrid approach of selectively hard coding PRIQTY for larger objects.
However, we also decided to take a slight detour in our implementation plan to change the order in how we will be rolling this out across DB2 subsystems. We're going to slow it down a bit, target our pre-production subsystems next, let that burn in for a couple of months, then do our lower environments next, wait another month, and then do production (assuming there are no issues encountered).
As far as REORGs go, we do not currently have conditional REORGs in place, but we do have an active effort in progress to do so. We're close to being able to start implementing that in the near future.
Thanks, Guys, I also liked the discussion, and sensibleness measures.
Objectives are important as Scott mentions, and we don't necessarily need to Reorg.I have Reorged in some cases to reclaim some greatly over allocated space.Yes our Dev system was using many Terabytes and getting short on available disk. Ha ha.Some Dev objects used many Gigs more than needed.Otherwise would normally Reorg when performance suffers. Otherwise why bother?
UPS6756, #1544403 Oleander Drive, Suite CWilmington, NC 28403 Phone: (910) 660-8649Fax: (910) 523-5504