DB2 - L

 View Only
Expand all | Collapse all

Db2 z/OS alter or drop for new partitioning keys

  • 1.  Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 09:24 AM
    Hi there!

    As the title implies, I have a table where we consider changing the Partitioning_key in "PARTITION BY (Partitioning_key)" and I'm wondering if it's a drop-create or if it can be handled with an alter. Due to the lack of information I've found, I suspect it's a drop-create, but it would be nice to have it confirmed.

    From IBM knowledge center it seems that you can alter table add partition by, but there is nothing regarding alter table alter partition by, leading me to think that it's not possible. 
    https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-alter-table

    If it's not an option to alter a set partition by clause, why not? Wouldn't it be fine to have it as a reorg-pending feature that requires the whole table to be reorged to materialize? Or is there any underlying technical aspect that I'm missing?

    Regards
    Johan

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


  • 2.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 10:01 AM
    Hello John, unfortunately you will have to go through the UNLOAD-DROP-CREATE-LOAD-RUNSTATS-BIND etc. to accomplish this.
    However, if you are very patient, IBM has been talking about a new feature in Db2 Vnext where it will be possible to convert from PBG to PBR - at least this has been briefly mentioned on some IBM webex's where.

    Steen





  • 3.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 01:13 PM
    Hi Steen, thanks for the reply!

    Yes, I have too heard that the feature will come in the future in VNext, but unclear when :)

    It would still be quite the unnecessary steps to bounce PBR -> PBG - PBR just to change the partition key(s) when it should be made possible with an alter. Maybe we get something handy in the future.

    Regards
    Johan

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



  • 4.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 03:26 PM
    Maybe I misread your requirement - if your table already is PBR with LIMITKEYS defined, you can certainly do the ALTER TABLE PART xx LIMITKEY and change the value - just need to do the subsequent reorg. You can even INSERT a partition in the middle if so needed.

    Steen





  • 5.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 03:45 PM
    That is correct, you can alter table alter partition. One thing I recently found that I did not see mentioned in the ALTER command is for the change to take effect, you MUST run a REORG SHRLEVEL CHANGE for the alteration to take effect.

    David Nance
    804-683-1507







  • 6.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 03:53 PM
    Edited by Johan Sundborg Mar 14, 2022 03:55 PM
    I think you understood me correct the first time Steen, it's not the limitkey that i aim to alter, it's the actual partitioning-column. Meaning "PARTITION BY ( This is the key I want to alter )"

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



  • 7.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 04:11 PM
    Hi Johan

    I have similar experience maybe it helps 

    Assuming I want to change parts limit key for parts 99,100,101 for a table partitioned TS with 150 Parts in total.

    1-unload Dara for parts 99,100,101
    2-load replace with scratch to empty parts 99,100,101
    3-alter part limit key for parts  99,100,101
    4-Reorg parts 99,100,101 to materialize new limit key
    5-load data for parts 99,100,101
    6-Reorg parts 99,100,101
    Regards 





  • 8.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 07:40 PM
    Leila - why do the unload-load when you can ALTER the three limitkey values followed by a reorganization - nowadays this alter is a pending change and not intrusive as in the past ?

    Steen 

    Sent from my iPhone





  • 9.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 08:16 PM
    I have tried to Alter parts ,but when there is data , Alter did not apply, and Alter was for middle parts.






  • 10.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 09:17 PM
    Which Db2 release ?
    I have altered limitkeys successfully so what SQLCODE did you get ?
    In the past altering limitkeys resulted in a hard stop REORP

    Steen

    Sent from my iPhone




  • 11.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 14, 2022 05:36 PM
    Hi
    I remember that I talked to Haakon Roberts some years ago about this topic that it should be easy to add to column to a partioning key ana also easy to remove the last column of a partioning key, if there is no value  specified for any partition. But, I think because it was no official requirement, nothing happened.

    ------------------------------
    WalterJanißenRetired
    ------------------------------



  • 12.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 15, 2022 03:01 AM
    Hi Walter, Johan and all!

    I also had a discussion with Haakon Roberts about this some years ago. One possible solution he mentioned was the one Johan also mention: Going from PBR to PBG and then back to PBR, eventually without materializing the change in between. As far as I know there is no plans to include PBR to PBG conversion in Db2 vNext though, at least not at GA.
    Many years ago I wrote an Aha Idea for this. Pleas vote for it. https://ibm-data-and-ai.ideas.ibm.com/ideas/DB24ZOS-I-839

    Best regards Martin

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



  • 13.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 15, 2022 03:23 AM
    Hi Martin,

    A bounce between UTS types and only materialize at the end sounds like a manageable approach, either way, it would be nice to get away from the quite drastic drop-create. You got my vote for your Aha idea.

    Regards
    Johan

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



  • 14.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 16, 2022 10:15 AM
    Would REORG REBALANCE accomplish you goal?  It will change the partition limits and redistribute the data in partitions in one step.  
    You can REBALANCE only the partitions needed, eg "REORG TABLESPACE dbname.tsname PART 11:16 REBALANCE"

    ------------------------------
    RichardArnoldExperian
    ------------------------------



  • 15.  RE: Db2 z/OS alter or drop for new partitioning keys

    Posted Mar 16, 2022 12:56 PM
    Hi Richard,

    REORG REBALANCE is good for many thing, but it only alters the partition limits and not the partition key. So for these particular situations it wont be of any use.

    Regards
    Johan

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