DB2 - L

 View Only

  • 1.  Db2 12 for z/OS Rotate partition

    Posted Apr 05, 2022 08:25 AM
    Hi,

    I have come across a use case where I believe that ALTER TABLE schema.table ROTATE PARTITION FIRST TO LAST ENDING AT ( ...... ) RESET; can be handy, but all the technicalities around how the rotation works and affect different components are not clear, and knowledgecenter are really not informative enough regarding this.

    In my tests is seems like you have to use RESET with the rotation and when you do, all rows in the rotated partition are deleted, this can be quite a severe issue for a big table. From reading at knowledgecenter I get the feeling that RESET doesn't always do delete, but the formulation are vague and open for uncertainty, and during my tests it always seem to do delete. Is there a way to make the rotation to discard (or something similar to dummy load) instead?
    If no, then the RESET functionality seem quite useless and you should discard or dummy load the partition before rotation instead.

    I have tried
    UTS PBR with 1 Index, UNIQUE, CLUSTER, PARTITIONED (on exactly the same row as the table)
    UTS PBR with 1 Index, UNIQUE, CLUSTER
    UTS PBR with 1 Index, Not-Unique, CLUSTER
    UTS PBR with 2 Index, UNIQUE, CLUSTER and  Not-UNIQUE, Not CLUSTER
    Same results

    Since the RESET writes to the log and I can use IBM Log Analysis Tool to generate UNDO (insert) statements, I guess it does deletes, but I'm not 100% sure. And even if it doesn't do delete, it's still quite bad to write so much to log compared to a reorg-discard.

    Any thoughts?

    Kind regards
    Johan

    ------------------------------
    Johan Sundborg, Swedbank AB
    ------------------------------


  • 2.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 05, 2022 08:56 AM
    We do a daily ALTER ROTATE daily for our Omegamon / SMF data warehouse; the first step in the process is that dummy load you mentioned.
    There were some specific cases not covered by the documentation where logging was done, but it was so long ago I've forgotten the details :(
    Identity columns?


    ------------------------------
    Steven Lamb
    ------------------------------

    Original Message


  • 3.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 06, 2022 09:16 AM
    We have used monthly rotate on several tables for years. We do a dummy load on the partition that is being rotated off prior to the rotate, otherwise the rotate takes quite a while to finish.

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

    Original Message


  • 4.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 05, 2022 09:06 AM

    Johan,

     

    At my Agency, we use the rotation of partitions on about forty large tables. Some of the rotations involve old-partitions which contain fifty to a hundred million rows.

     

    For our ROTATE work, we start by REORGing the oldest partition (the one which we're going to rotate), with DISCARD of all records.  This is a partition level REORG.

     

    To specify the REORG, you will have to identify the PARTITION where the LOGICAL_PART=1, and its LIMITKEY.

     

    The REORG should be of the oldest PARTITION.  The statement should specify the partition which will later rotate, to avoid unnecessary work:

    REORG TABLESPACE [dbname.tsname] PART [physical partition number, NOT logical]

     

    You must also specify DISCARDDN*:

    DISCARDDN [template or DDName]

     

    You can DISCARD all records from the oldest partition, by using a DISCARD statement in the REORG:

    DISCARD FROM TABLE [qualified table name] WHEN PartitionColumnName <= LimitKeyOfOldestPartition

     

    * The DISCARDDN file specification (use a TEMPLATE statement) can be used to retain the discarded rows; you can also use this dataset to populate a history table for rows which have aged off the primary table.  We do this with several of our large tables.

     

    After the DISCARD is complete, your oldest partition has no rows to fit the automatic SQL DELETE which is executed when you ROTATE FIRST TO LAST.  We find this process to be much faster (and it doesn't LOG as much data!).

     

    Hope this helps!

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     




    Original Message


  • 5.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 07, 2022 07:54 AM

    Hi Philip, that is surely a possible way to do it.

    I've considered reorg-discard before doing the rotation, but if you don't need the rows then dummy load should be faster.

    You are correct that one need to in mind when to use logical_part and when to use physical_part. I planned to query the catalogue tables for that information and dynamically build the job for each run, that way I can let Db2 keep track of which partition to rotate and also let either Db2 or OPC set the new partition limiter (will be a DATE in my case).

    We use a template for discards, very handy :)

    Regards
    Johan



    ------------------------------
    Johan Sundborg, Swedbank AB
    ------------------------------

    Original Message


  • 6.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 07, 2022 10:29 AM

    Johan,

     

    I agree with you on the "DD DUMMY" load-replace of the oldest partition being faster.  There are, however, at least two possible events which could cause that process to get rid of rows which you might want to keep:

    1.       If the process to identify the target partition is in error (It's coded by human beings! What are the possible failure modes?), then you might specify the wrong PART number. 
    For the audience: LOAD / INTO PART must specify the physical partition number rather than the logical number.

    2.       If someone causes the job to run too frequently, you will destroy the "oldest" data too soon; this is a logical error and might have actual impact, causing reports to be wrong if they're expecting or (worse!) aggregating old data.

     

    We don't LOAD INTO PART/DUMMY.  While our "Purge and Rotate" applications use scheduled jobs, we have had the job run twice in the same month on occasion.  It was almost ten years ago, but once burned...  Running it as a REORG with DISCARD, and reasonably long retention of the DISCARDDN saves the rows, so that we can recover them if there was an error. 

     

    Since that time, I have _also_ built code into the purge-and-rotate applications, which confirms that the data being discarded-or-migrated is old enough to qualify for it.  If the job runs too early, my checker program (A query checked by a program, REXX of course!) notes that data would be discarded too soon and sets a return code 1. Coded IF/THEN/ELSE JCL logic detects the RC=1 and bypasses the rest of the job.

     

    /phil

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     




    Original Message


  • 7.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 06, 2022 01:26 AM
    Hi
    We use ROTATE for some tables in our Data Warehouse. Before we do the ROTATE we alter the table to DATA CAPTURE NONE. Then the ROTATE take no time.
    Probably not the best way to do it but it is a way...
    Regards Martin

    ------------------------------
    Martin Ålund
    Handelsbanken
    ------------------------------

    Original Message


  • 8.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 07, 2022 07:40 AM
    Hi Martin, thanks for the insight.
    Well, if one are sure that the table will have no activity to log during the time between alters then it's probably fine, otherwise you run the risk is not logging things that should be logged. I think I find the partition wise DUMMY load safer :)

    Regards
    Johan

    ------------------------------
    Johan Sundborg, Swedbank AB
    ------------------------------

    Original Message


  • 9.  RE: Db2 12 for z/OS Rotate partition

    Posted Apr 08, 2022 08:11 AM

    I inherited a large extremely active table that came with the need to continually rotate partitions. The team was already rotating it on occasion. Everything works fine and I'm using the reset.


    ***** One huge point to think about is the impact to any packages (including dynamic). The rotate is a structure change. *****


    May not be an issue for your case but this table is insanely busy. I tried an automated rotate once and that didn't go well. Back to keeping many empty partitions and manually rotating about 15-20 during planned maintenance window.

    I have plans to replace the table. I've had date based partitioning burn me a few times in various scenarios.  



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

    Original Message