DB2 - L

Expand all | Collapse all

DB2 Sliding Scale

  • 1.  DB2 Sliding Scale

    Posted 11 days ago

    Happy Tuesday!

    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?

    Thanks.



    ------------------------------
    Bill Gallagher
    Travelers
    [City] [State]
    [EmailAddress]
    ------------------------------


  • 2.  RE: DB2 Sliding Scale

    Posted 10 days ago
    Bill,

    According to the manual the SECQTY = -1 should be in effect for the next extent, but as always recommend you confirm for yourself. You should be able to test this out in a NON-PROD environment

    Altering storage attributes:
    The USING, PRIQTY, SECQTY, and ERASE clauses define the storage attributes of the table space or partition. If you specify USING or ERASE when altering storage attributes, the table space or partition must be in the stopped state when the ALTER TABLESPACE statement is executed. You can use a STOP DATABASE…SPACENAM… command to stop the table space or partition.

    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.

    HTH
    Jack



    ------------------------------
    Jack Campbell
    Saxon Consulting, Inc.
    ------------------------------



  • 3.  RE: DB2 Sliding Scale

    Posted 10 days ago
    Nice reply by Jack. 

    Bill - What is your goal by doing so?

    I'm all for using the sliding scale when appropriate. I use to believe in a blanket approach but I have since changed that opinion. The reason is large tables and specific situations.

    Here are some reasons (not an inclusive list).

    1) We use sliding scale for many of our tablespaces in the lower environemnts that may have small or no rows. Production may be vastly different.

    2) We have one table that comes to mind that is partitioned by date. The partition could be empty but then suddenly grow extremely large in lightning fast speed. I don't want Db2 to guess or struggle with figuring that out when I can easily see a good estimate for the final size. I have seen first hand the pain of growing datasets to a new extent in a ultra busy application. I don't want to risk that here when I don't need to.

    3) SECQTY is based on PRIQTY. I'd much rather set primary to something valid and then let secondary build from that. That is mostly my strategy at this point if I don't use -1 for both.

    4) Space isn't costly or something as critital to pay attention to but it's still a resource that we don't want to waste. So, having proper values (whether defined or sliding scale) is important.



    From the Information Center.

    "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."

    "You can specify the primary and secondary space allocation or let Db2 choose them. Having Db2 choose the values, especially the secondary space quantity, increases the possibility of reaching the maximum data set size before running out of extents."


    Here are some links worth reading.
    https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_altertablespace.html
    https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_primaryspaceallocation.html
    https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_altertablespace.html#db2z_sql_altertablespace__rtbscsa
    https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html#db2z_sql_createtablespace__rpsalloc



    As far as reorgs, you should have a conditional reorg strategy put into place. I will assume you do. That should take care of any materialization required. My memory is fuzzy if this is a pending alter. I'm pretty sure it is not. However, be aware leaving these out there hanging may impact when it takes place whether it's pending or immediate but the dataset is not fully changed yet. As you know, pending alters could prevent future alters until those are materialized first, but I don't think that is the case here, however loss of control of timing is a concern.


    As the saying goes, "just because you can do something doesn't mean you should" and the famous DBA quote "It depends".

    ------------------------------
    Scott Walker
    Navy Federal Credit Union
    ------------------------------



  • 4.  RE: DB2 Sliding Scale

    Posted 10 days ago
    Scott, 

    I agree completely with your suggestion on been conservative with the use of PRIQTY/SECQTY = -1/-1.

    Personally I reserve this option for non-partitioned tablespaces < 64GB , for any partitioned tablespaces (or PBG > 64gb) I still go old skool and calculate the expected size and assign PRIQTY/SECQTY in good 'chunks' appropriately to minimize extents/fragmentation (*unless each partition is very small).

    Whilst I can have up to 7,257 extents, do I really want a large, important/performance critical table taking so many extents (overhead),  in potentially small increments - think of incremental inserts 24 * 7 or bulk inserts? I'm also concerned about REORG times and building the shadow copy in the same small(er) increments than if I define a decent amount of PRIQTY/SECQTY for my large partitioned tables.

    *Bill - it may just be time to REORG (at least a sample set) of them to verify you get the performance you expect with the new setting. When we moved over to -1/-1 for non-partitioned, we still got a few surprises on just how many extents could be allocated for a table close >= say 50GB.

    Regards
    Jack Campbell

    ------------------------------
    Jack Campbell
    Saxon Consulting, Inc.
    ------------------------------



  • 5.  RE: DB2 Sliding Scale

    Posted 10 days ago
    FYI: PH28280 removes MGEXTSZ and Db2 12 (not 11) behaves as though MGEXTSZ=YES
    was specified.
    https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_enha
    ncements.html

    James Campbell


    On 13 Jan 2021 at 20:04, Jack Campbell via Internation wrote:

    > Scott,
    >
    > I agree completely with your suggestion on been conservative with the use of PRIQTY/SECQTY = -1/-1.
    >
    > Personally I reserve this option for non-partitioned tablespaces < 64GB , for any partitioned tablespaces (or PBG > 64gb) I still go old skool and calculate the expected size and assign PRIQTY/SECQTY in good 'chunks' appropriately to minimize extents/fragmentation (*unless each partition is very small).
    >
    > Whilst I can have up to 7,257 extents, do I really want a large, important/performance critical table taking so many extents (overhead), in potentially small increments - think of incremental inserts 24 * 7 or bulk inserts? I'm also concerned about REORG times and building the shadow copy in the same small(er) increments than if I define a decent amount of PRIQTY/SECQTY for my large partitioned tables.
    >
    > *Bill - it may just be time to REORG (at least a sample set) of them to verify you get the performance you expect with the new setting. When we moved over to -1/-1 for non-partitioned, we still got a few surprises on just how many extents could be allocated for a table close >= say 50GB.
    >
    > Regards
    > Jack Campbell
    >
    > ------------------------------
    > Jack Campbell
    > Saxon Consulting, Inc.
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Jan 13, 2021 12:38 PM
    > From: Scott Walker
    > Subject: DB2 Sliding Scale
    >
    > Nice reply by Jack.
    >
    > Bill - What is your goal by doing so?
    >
    > I'm all for using the sliding scale when appropriate. I use to believe in a blanket approach but I have since changed that opinion. The reason is large tables and specific situations.
    >
    > Here are some reasons (not an inclusive list).
    >
    > 1) We use sliding scale for many of our tablespaces in the lower environemnts that may have small or no rows. Production may be vastly different.
    >
    > 2) We have one table that comes to mind that is partitioned by date. The partition could be empty but then suddenly grow extremely large in lightning fast speed. I don't want Db2 to guess or struggle with figuring that out when I can easily see a good estimate for the final size. I have seen first hand the pain of growing datasets to a new extent in a ultra busy application. I don't want to risk that here when I don't need to.
    >
    > 3) SECQTY is based on PRIQTY. I'd much rather set primary to something valid and then let secondary build from that. That is mostly my strategy at this point if I don't use -1 for both.
    >
    > 4) Space isn't costly or something as critital to pay attention to but it's still a resource that we don't want to waste. So, having proper values (whether defined or sliding scale) is important.
    >
    >
    >
    > From the Information Center.
    >
    > "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."
    >
    > "You can specify the primary and secondary space allocation or let Db2 choose them. Having Db2 choose the values, especially the secondary space quantity, increases the possibility of reaching the maximum data set size before running out of extents."
    >
    >
    > Here are some links worth reading.
    > https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_altertablespace.html <https: www.ibm.com/support/knowledgecenter/ssepek_12.0.0/sqlref/src/tpc/db2z_sql_altertablespace.html="">
    > https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_primaryspaceallocation.html <https: www.ibm.com/support/knowledgecenter/ssepek_12.0.0/intro/src/tpc/db2z_primaryspaceallocation.html="">
    > https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_altertablespace.html#db2z_sql_altertablespace__rtbscsa <https: www.ibm.com/support/knowledgecenter/ssepek_12.0.0/sqlref/src/tpc/db2z_sql_altertablespace.html#db2z_sql_altertablespace__rtbscsa="">
    > https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html#db2z_sql_createtablespace__rpsalloc <https: www.ibm.com/support/knowledgecenter/ssepek_12.0.0/sqlref/src/tpc/db2z_sql_createtablespace.html#db2z_sql_createtablespace__rpsalloc="">
    >
    >
    >
    > As far as reorgs, you should have a conditional reorg strategy put into place. I will assume you do. That should take care of any materialization required. My memory is fuzzy if this is a pending alter. I'm pretty sure it is not. However, be aware leaving these out there hanging may impact when it takes place whether it's pending or immediate but the dataset is not fully changed yet. As you know, pending alters could prevent future alters until those are materialized first, but I don't think that is the case here, however loss of control of timing is a concern.
    >
    >
    > As the saying goes, "just because you can do something doesn't mean you should" and the famous DBA quote "It depends".
    >
    > ------------------------------
    > Scott Walker
    > Navy Federal Credit Union
    > ------------------------------
    >
    > Original Message:
    > Sent: Jan 13, 2021 12:14 PM
    > From: Jack Campbell
    > Subject: DB2 Sliding Scale
    >
    > Bill,
    >
    > According to the manual the SECQTY = -1 should be in effect for the next extent, but as always recommend you confirm for yourself. You should be able to test this out in a NON-PROD environment
    >
    > Altering storage attributes:
    > The USING, PRIQTY, SECQTY, and ERASE clauses define the storage attributes of the table space or partition. If you specify USING or ERASE when altering storage attributes, the table space or partition must be in the stopped state when the ALTER TABLESPACE statement is executed. You can use a STOP DATABASE...SPACENAM... command to stop the table space or partition.
    > 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.
    >
    > HTH
    > Jack
    >
    >
    > ------------------------------
    > Jack Campbell
    > Saxon Consulting, Inc.
    >
    > Original Message:
    > Sent: Jan 12, 2021 03:09 PM
    > From: Bill Gallagher
    > Subject: DB2 Sliding Scale
    >
    >
    > Happy Tuesday!
    >
    > 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?
    >
    > Thanks.
    >
    >
    > ------------------------------
    > Bill Gallagher
    > Travelers
    > [City] [State]
    > [EmailAddress]
    > ------------------------------


    --
    This email has been checked for viruses by AVG.
    https://www.avg.com




  • 6.  RE: DB2 Sliding Scale

    Posted 10 days ago
    I can't answer your question regarding when the change becomes active but I can tell you that when MGEXTSZ became available we enabled it and set TSQTY=100 in the zparms and never looked back. All of our tablespaces and indexes are created with -1 for both primary and secondary allocation. I have never had a space issue since...never. It's possible that we have a few more extents now than we used to but that apparently hasn't been an issue. The old segmented tablespaces we still have just take another dataset when needed and our new PBG tablespaces just add another partition when needed. They don't run into space issues or max extents.
    I used to constantly fight issues of tables running out of space (usually in the middle of the night during batch processing) before making that change. We made that change years ago, I think way back in version 9. I let db2 handle it all and have no regrets and no concerns.

    ------------------------------
    RussellPetersCentral Technology Services
    ------------------------------



  • 7.  RE: DB2 Sliding Scale

    Posted 10 days ago
    Great input everyone. I think the main theme is how conservative to be.

    I still think a solid primary quantity for sizeable tables or exceptionally busy ones is the way to go. Let secqty be sliding and based on priqty. For small tables, sliding scale on both are fine. 

    James - Excellent find on MGEXTSZ. I do like how Db2 is now enhanced to do more of this work for us. 

    Bill - keep us updated on how this plays out. 


    ------------------------------
    Scott Walker
    Navy Federal Credit Union
    ------------------------------



  • 8.  RE: DB2 Sliding Scale

    Posted 9 days ago

    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.



    ------------------------------
    Bill Gallagher
    Travelers

    ------------------------------



  • 9.  RE: DB2 Sliding Scale

    Posted 9 days ago

    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?



    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------