DB2 - L

 View Only
Expand all | Collapse all

Moving tables from non-UTS tablespace (segment table spaces)

  • 1.  Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 07, 2022 10:21 AM
    Is IBM Db2 providing  any option under Db2 admin tool  to move tables from segmented table space ?  I am hearing that we need to do that manually - if there is an option or anyone having some experience kindly share the same.
    Thanks in advance

    ------------------------------
    VASUDEVANNATARAJANwalmart
    ------------------------------


  • 2.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 07, 2022 04:38 PM
    Why not use native Db2?

    "Function level 508 enhances the ALTER TABLESPACE statement with a new MOVE
    TABLE option, which can be used to move a table from a source table space to a target table
    space."


    https://www.ibm.com/docs/en/db2-for-zos/12?topic=d1fl-function-level-508-activation-enabled
    -by-apar-ph29392-october-2020#db2z_fl_v12r1m508__e101

    James Campbell


    On 7 Nov 2022 at 15:21, VASUDEVAN NATARAJAN via Inter wrote:

    > Is IBM Db2 providing any option under Db2 admin tool to move tables from segmented table space ? I am hearing that we need to do that manually - if there is an option or anyone having some experience kindly share the same.
    > Thanks in advance
    >
    > ------------------------------
    > VASUDEVANNATARAJANwalmart
    > ------------------------------
    >




  • 3.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 03:28 AM

    Hi,

     

    when you choose >1 - DB2 system catalog<  and zoom in (for example >D – Databases<) you'll get

    >Commands: GRANT  MIG  DIS  STA  STO  UTIL  CT   MOVETB<

    I'm new to admin tool and haven't tested yet but looks promising.

     

    HTH

    Thomas

     

    Thomas Weber

     

     

     






  • 4.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 04:16 AM
    Hi,
    In admin tool there is an option MAKEPBG in the panel ADB21SAR when you do an ALT against a TS.
    This will generate the analysis and execution jobs to convert the TS into a PBG.
    Other options are MAKEPBR and MAKEPBR2 (think this is for RPN format).
    Please be aware of the DSSIZE as the MAKEPBG will choose 4096 as maxpartitions.
    Hopefully I understood your requirements correctly.
    Regards,
    Dhiren Chaudhary

    ------------------------------
    Dhiren Chaudhary
    ------------------------------



  • 5.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 04:28 AM
    Does it really default to 4096 parts?? That would be epically bad....

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 6.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 04:54 AM
    Hi Roy,

    the change that the tool generates puts maxpartitions 4096 on the panel once the MAKEPBG is chosen.
    You can obviously override to a more reasonable value. I was merely pointing out the option in admin tool and also that the user should watch out for the maxpartitions and dsssize considerations. 

    regards

    ------------------------------
    Dhiren Chaudhary
    ------------------------------



  • 7.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 04:55 AM
    Hi

    Phew! At least that gives you a good chance... Wonder why they did not pick 4 or 6... 4096 is a bit high!!!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 8.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 04:57 AM

    Must... keep... schtum...  ��

     






  • 9.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 04:59 AM
    Quiet in the cheap seats!!!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 10.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 09, 2022 03:01 PM

    We are needing tools to manage/advise SQL workloads, BP analysis and system performance, anything else would be a bonus.

     

    On the surface Query Monitor seems like a subset of Db2 AI.

     

    Anyone have comments or experiences they could share or even a different IBM product.

    Has to be IBM, I am using our credit pool to fund this.

     

    Thanks in advance,

     

    FNTS

    FaceBook

     

     

    Mark Vickers

    Sr. Db2 Engineer

    w: (402) 997-1542    

     c: (402) 616-4607

     

     






  • 11.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 10, 2022 04:40 AM
    [AD] Hi Mark, Db2 Query Monitor can monitor SQL workloads. It has capabilities to detect anomalies in your workloads but is different to Db2 zAI. I will send you a private message with more details.
    Best regards
    Christoph Theisen

    ------------------------------
    Christoph Theisen
    Rocket Software Inc.
    ------------------------------



  • 12.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 10:53 AM
    Edited by Kate Wheat Nov 08, 2022 10:53 AM
    Here's some resources that might help:

    This video shows how to do this move in Db2 Admin Tool:
    https://video.ibm.com/recorded/129361653

    Here's the written instructions:
    https://www.ibm.com/docs/en/db2admintool/12.1.0?topic=doc-moving-tables-from-multi-table-table-spaces-uts

    ------------------------------
    Kate Wheat 
    Rocket Software

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



  • 13.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 10, 2022 09:34 AM

    Kate,

     

    THANK YOU VERY MUCH.

     

     

    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

     






  • 14.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 08, 2022 03:34 PM
    Hi,

    Admin Tool should default to the value you used previously.

    Jørn

    ------------------------------
    Jørn Thyssen
    Rocket Software
    2022 IBM Champion
    ------------------------------



  • 15.  RE: Moving tables from non-UTS tablespace (segment table spaces)

    Posted Nov 09, 2022 08:43 AM
    I used a batch process to convert segmented tablespaces with one table to UTS/PBG. I did an unload first to create the alter statements in a flat file:
    SELECT
    CAST
    (' ALTER TABLESPACE ' CONCAT STRIP(P.DBNAME, BOTH) CONCAT '.'
    CONCAT STRIP(P.NAME,BOTH) CONCAT ' MAXPARTITIONS 5;'
    AS VARCHAR(80))
    FROM SYSIBM.SYSTABLESPACE P
    WHERE P.DBNAME = 'ABCDB'
    AND P.MAXPARTITIONS < 5
    AND P.NTABLES = 1
    AND P.TYPE = ''
    ;
    I then executed IKJEFT01 to execute the alter commands, then followed that with an online reorg with SCOPE PENDING. I did this at the database level. It all worked great. The only issues I encountered had to do with the base table for LOBs. When you alter the base table to UTS/PBG you have to run a reorg specifying the base table only and cannot use tape stacking. The reorg of the base table will also trigger a reorg of the aux table. We do inline image copies written directly to vtape; tape stacking is not allowed in this situation which I think is odd and it caused some failures so I had to separate those tables out of the reorg step and run a separate step with no tape stacking. 
    This took several months to complete but it worked great.

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