Db2 Tools

 View Only
  • 1.  Converting multi-table tablespaces to PBGs using CA Db2 Tools

    Broadcom Employee
    Posted Jan 08, 2021 03:49 AM
    Edited by Philippe Dubost Jan 11, 2021 02:19 AM

    The CA Database Management for Db2 for z/OS offer several options to assist/automate the conversion of traditional Db2 Tablespaces to UTS. We will cover a couple of use-cases in this article.

    Tablespaces with only one table

    Converting simple or segmented tablespaces to PBGs is pretty straightforward. All you have to do is alter the tablespace: ALTER TABLESPACE db.ts MAXPARTITIONS 1, which makes this a pending change. By executing the REORG utility, the pending change will be instantiated.

    You can accomplish this task with CA RC/Query for Db2 for z/OS:

    1. Generate the TS-EG report, which will list eligible tablespaces to be converted to PBG.
    2. Execute the ALL CPBG command and hit ENTER. RC/Query will generate the necessary ALTER commands for converting to PBG.
    3. In case you regret the pending change, simply execute DPEND command for the tablespaces where you want to UNDO/DROP the pending change.
    4. Otherwise, all that is needed is to execute the REORG to instantiate the pending changes: Command ALL REORG will generate the IBM ONLINE REORG if the RC/Query User Profile has UTILITIES set to IBM.

    Tablespaces with more than one table

    So it is pretty easy to handle this task for tablespaces having only ONE table, but it is not possible to use the same process for tables residing in multi-table tablespaces. You will have to execute the DBA nightmare of UDCL (Unload-Drop-Create-Load). If you only have a few it's not that hard, but if you have dozens or hundreds the task can be daunting.

    Db2 12 FL508 (Function Level 508) as of late 2020 delivered a new schema ALTER command which will assign a table to a new tablespace. The schema change will result in a PENDING schema change which will be instantiated upon REORG.

    Using CA Database Analyzer for Db2 for z/OS you have a few options to choose from. However, here we will only describe one method where we will let PDA find the tablespaces and convert those described here.

    You will need an Action Procedure which will use a US-model (User Defined Model). In this case, we created a model member named FL508 in the highlvl.CDBAMDL library. One word of caution, by default CA Database Analyzer operates on the tablespace level and not table level, so it is necessary to modify the highlvl.CDBAPARM(PDA) to have TABLE_COND (Y) so PDA will generate the actions on the table level, and it is also necessary to have a symbolic %TBNAME present in the model.

    Sample of the generated Action JCL creating the new tablespaces and altering the tables to be moved to these tablespaces:

    The biggest challenge is the naming convention of the new tablespaces which will hold the tables being fanned out from the multi-table tablespaces. You will have to modify the logic illustrated above to meet your needs, but in this example the new tablespaces are created with a name based on the first two bytes of the "old" tablespace concatenated with byte 5+6 and then appended a sequence number (%INCR). One caveat, if you plan to generate tablespaces within the same environment more than once, you will have to assign a start value for the %INCR for the second and subsequent execution.

    This option depends on the PDA-STATS due to an Action Procedure utilizing the statistics collected by any of the scan methods. If you don't have current PDASTATS, you could do a subsystem wide SPACEMAP scan (or on the databases of interest) in order to use the Action Condition listed below. If you decide to use this approach, please make sure the VSAM pagesets for the tablespaces are NOT migrated, and there is a batch parameter to be included in the PDA Extract job to handle this in order to collect the statistics for the number of tables residing in any given tablespace.

    A very similar approach that does not depend on PDASTATS being executed is to create a CUSTOMIZABLE SQL condition to use in the Action Based creation of the Extract Procedure to only include objects from the catalog based on certain conditions – like tablespaces with more than one table (in fact the third option described shortly is a slight variation of this one).

    Once the SQL is generated using the ALTER MOVE command, you need to execute it, and once executed you can use PDA to generate the ONLINE REORG of the "OLD" tablespaces. Once this is complete one final PDA Extract/Action execution is needed – Image Copy and RUNSTATS.

    Important note on PTDB objects

    You are probably aware that most objects in PTDB haven't been converted to PBG's yet.
    Even though you could perform this conversion, it is NOT recommended. The reason is, until we deliver the tablespaces in a UTS format when the products are being installed/upgraded, the Post Install compare job will convert them back to the original format.
    However, we are working on an enhancement to deliver the PTDB tablespaces in UTS format and utilize the IBM features to fan out the tables residing in non-UTS tablespaces to PBG's, so please stay tuned.

                                                                                                               Article originally written by Steen Rasmussen

  • 2.  RE: Converting multi-table tablespaces to PBGs using CA Db2 Tools

    Posted Apr 27, 2021 09:01 AM
    Steen and Philippe,
    Having performed this process a few times on tablespaces with 40+ tables, here is one other item to consider.   Since the end goal is for the new table to be used, a RENAME command is needed to switch existing to old and new to existing.  The source table may have some existing attributes that prevent the rename causing DB2 to return a SQLCODE -750

    I have seen SYNONYM be one blocker and this is a great time to deprecate those and change them to ALIAS.


    Db2 for z/OS Automation DBA

  • 3.  RE: Converting multi-table tablespaces to PBGs using CA Db2 Tools

    Broadcom Employee
    Posted Apr 27, 2021 09:31 AM

    Thanks Jeff, that is a great point!


    Philippe Dubost