Database Analyzer : Converting table spaces to PBG's
search cancel

Database Analyzer : Converting table spaces to PBG's

book

Article ID: 262898

calendar_today

Updated On:

Products

Database Analyzer for DB2 for z/OS

Issue/Introduction

Based on the Community Post below: 

Converting multi-table tablespaces to PBGs using CA Db2 Tools

Detailed steps on how this can be carried out.



Environment

Release : 20.0

Resolution

Write the Model Code in your model like this:  hlq.CDBAMDL(CONVTPBG)

#CM
#CM New tablespaces are created with a name based on the first
#CM and second byte of the table name concatenated with bytes 5 and 6
#CM and then appended with a sequence number (%INCR).
#CM
#SET %TSO1  = %SUBSTR(1,2,%OBJECT) <====the %OBJECT symbolic variable will be the table name.
#SET %TSO2  = %SUBSTR(5,2,%OBJECT)
#SET %NEWTS = %TSO1%TSO2%INCR

CREATE TABLESPACE %NEWTS in %DBNAME
       DEFINE NO
       MAXPARTITIONS 1;

ALTER TABLESPACE %DBNAME..%TSNAME MOVE TABLE
                 %CREATOR..%TBNAME
                 TO %DBNAME..%NEWTS ;

The main challenge as noted in the the community post is writing the code to generate the new tablespace names so that they make sense and are not duplicates. 

Check to ensure that your HLQ.CDBAPARM(PDA) member has this setting as a "Y".

TABLE_COND   (Y)                    /* Y = Test SQL conditions for */
                                    /*     each table in tablespace*/
                                    /*     (Default)               */
                                  /* N = Do NOT do this (faster) */

Also just to restate the information in the Community Post about PDA-STATS.
"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."

Create an Extract Procedure

RDA.CPL             ----- EXTRACT PROCEDURE SERVICES  ----    yyyy/mm/dd hh:mm
COMMAND ==>                                                   SCROLL ==> CSR

 PROCEDURE SELECTION LIST PARAMETERS
 Name ==> CONVTPBG  Creator ==> authid1    Subsystem ==> ssid
   Desc ==> *                                Location  ==> LOCAL
-------------------------------------------- SQLID: authid1  User ID: authid1
                                              S A  DB2  LAST UPDATE LAST SUBMIT
O EXT PROC PROCEDURE DESCRIPTION     CREATOR  O P  SYSD DATE   TIME DATE   TIME
C CONVTPBG convert TB's to PBG's____ authid1  u N  <=== NEW PROCEDURE CREATION
******************************** BOTTOM OF DATA *******************************

Select the objects for the extract

RDA.CBPM          ----     BUILD EXTRACT PROCEDURES     ----  yyyy/mm/dd hh:mm
COMMAND ==>

Loc: LOCAL ----------------------------------------------- User ID: authid1
  PROCEDURE NAME  ==> CONVTPBG  DESCRIPTION ==> CONVERT TB'S TO PBG'S
   Share Option ==> U         Subsystem Connection ID: ssid

OBJECT SELECTION SUBSYSTEM ==> ssid  MODE ==> N (N)ame or (A)ction Oriented
     Database     ==> DBCORP             Creator ==>
     Table Space  ==>                    Creator ==>
     Table        ==>                    Creator ==>
     Index        ==>                    Creator ==>
                          (or)
     Object Name  ==>                Object Type ==>    (PK,PL,PP,ST,VL)

  ACTION PROCEDURE TIES  ==> N  (E)xplode, (S)elect, (O)bject, (C)reate
                                 00 Tied to Extract;   00 Tied to Objects
  EXTRACT OPTIONS
    Auto-Catalog Update  ==> N  (N)one, (U)pdate
    Column Statistics    ==> N  (I)ndex, (A)ll, (N)one or (U)pdate
    Job Generation Parms ==> N  (N)one, (U)pdate
    Prioritize Obj Maint ==> N  (E)xplode, (S)elect, (C)reate-and-tie profile

 Enter Obj Sel Info to add objects, PF3 to save, or specify ? in command line.

on the next screen select the Build Option

RDA.CBPD          -------    EXTRACT DATA SELECTION   ------  yyyy/mm/dd hh:mm
Command  ==>                                                   SCROLL ==> CSR
RAP020 RAP020I - Extracting only thru databases
Procedure:   CONVTPBG  Desc : CONVERT TB'S TO PBG'S
Database:    DBCORP    Table:                     Appl Plan/Pkg:
Table Space:           Index:
OBJECT DB2 ID: ssid ---------------------------------------- User ID: authid1
O DATABASE TABLESPACE P     TABLE              CREATOR  ------ INDEX ---------
A DBCORP
******************************* BOTTOM OF DATA ********************************

Create a tied Action procedure with the "C"

RDA.CBPM          ----     BUILD EXTRACT PROCEDURES     ----  yyyy/mm/dd hh:mm
COMMAND ==>
RAP020 RAP020I - Extracting only thru databases
Loc: LOCAL ----------------------------------------------- User ID: authid1
  PROCEDURE NAME  ==> CONVTPBG  DESCRIPTION ==> CONVERT TB'S TO PBG'S
   Share Option ==> U         Subsystem Connection ID: ssid

OBJECT SELECTION SUBSYSTEM ==> ssid  MODE ==> N (N)ame or (A)ction Oriented
     Database     ==>                    Creator ==>
     Table Space  ==>                    Creator ==>
     Table        ==>                    Creator ==>
     Index        ==>                    Creator ==>
                          (or)
     Object Name  ==>                Object Type ==>    (PK,PL,PP,ST,VL)

  ACTION PROCEDURE TIES  ==> C  (E)xplode, (S)elect, (O)bject, (C)reate
                                 00 Tied to Extract;   00 Tied to Objects
  EXTRACT OPTIONS
    Auto-Catalog Update  ==> N  (N)one, (U)pdate
    Column Statistics    ==> N  (I)ndex, (A)ll, (N)one or (U)pdate
    Job Generation Parms ==> N  (N)one, (U)pdate
    Prioritize Obj Maint ==> N  (E)xplode, (S)elect, (C)reate-and-tie profile

 Enter Obj Sel Info to add objects, PF3 to save, or specify ? in command line.


Enter utility code US below, US (User App), add a procedure name and description too.
Ensure that the MODEL library being used is the one that contains the model written at the start of this task.
RDA.UBPM          ----     BUILD ACTION PROCEDURE      ----   yyyy/mm/dd hh:mm
COMMAND  ==>
RAP000 RAP000E -  Procedure/Profile name required
Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: authid1
 PROCEDURE NAME ==> CONVTPBG  DESCRIPTION ==> CONVERT TB'S TO PBG'S
   Share Option ==> U

 STEP JCL MODEL PARAMETERS (IBM codes only)     CONDITIONAL PARAMETERS
   Model Name     ==> MJUTLGL   (Def: MJUTLGL)    Condition Type    ==> N
   Process Option ==> Y                           Immediate Message ==> N
 Library Name   ==> 'HLQ.CDBAMDL'

 OPT CODE    SYM  . . . . . . . . . . UTILITY CODES . . . . . . . . . . . . .
  _   US          IBM: CD (Chk Data)  IBM: RS (Runstats)  PDA: AM (Acc Meth)
  _   __               CK (Chk IX)         RT (Report TS)      CP (Cmd Proc)
  _   __               IC (Img Copy)       ST (Stospace)       MP (Msg Proc)
  _   __               MC (MergeCopy)                          PR (PDA Rpts)
  _   __               MD (Modify)    CAI: DU (Dynamic Utils   PS (PDA Stats)
  _   __               QU (Quiesce)           replaced QC/UR)  UC (Upd Cat)
  _   __               RC (Recover)   Gen: US (User App)
  _   __               RO (Reorg)          AL (Alter)
  _   __               RR (Rpt Rcv)
  _   __
                        Press ENTER to process request.  Press PF3 to exit.
Select the model that was written at the start of this task.

RDA.UBCD               -    GENERAL MODEL REQUEST    -        yyyy/mm/dd hh:mm
COMMAND  ==>

Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: authid1

   Enter Model Name ==>  CONVTPBG    CODE: US  ==> USER DEFINITION

   Depending on the utility code selected, one of the sample models listed
   below may fit your needs.  A "US" utility code allows you the added
   flexibility to invoke your own user application or a third-party utility
   by defining and using your own model.

             Code   Model    Description
             ----  --------  ----------------------------------
              AL   MJUTLAL   ALTER object via Batch Processor
              AL   MJUTLAL1  ALTER object via DSNTIAD
              AM   MJAMSDEF  VSAM DEFINE control cards
              US   MJUTLRBN  REBIND PLANs
              US   MJUTLRPK  REBIND PACKAGEs
              US   MJUTLRPT  REBIND TRIGGER PACKAGEs
              US   MJUTLRPN  REBIND PACKAGEs Native SQL Stored Procs
              US   MJUTLTST  Action Condition testing model
 Enter model name or * in Model Name field and press ENTER. Press PF3 to exit.

PF3 back.
Select the Conditional Parameter firstly by entering an "O" for or in the Condition Type field below.

RDA.UBPM          ----     BUILD ACTION PROCEDURE      ----   yyyy/mm/dd hh:mm
COMMAND  ==>

Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: authid1
 PROCEDURE NAME ==> CONVTPBG  DESCRIPTION ==> CONVERT TB'S TO PBG'S
   Share Option ==> U

 STEP JCL MODEL PARAMETERS (IBM codes only)     CONDITIONAL PARAMETERS
 Model Name     ==> MJUTLGL   (Def: MJUTLGL)    Condition Type    ==> O
   Process Option ==> Y                           Immediate Message ==> N
 Library Name   ==> 'HLQ.CDBAMDL'

 OPT CODE    SYM  . . . . . . . . . . UTILITY CODES . . . . . . . . . . . . .
  N   US      Y   IBM: CD (Chk Data)  IBM: RS (Runstats)  PDA: AM (Acc Meth)
  _   __               CK (Chk IX)         RT (Report TS)      CP (Cmd Proc)
  _   __               IC (Img Copy)       ST (Stospace)       MP (Msg Proc)
  _   __               MC (MergeCopy)                          PR (PDA Rpts)
  _   __               MD (Modify)    CAI: DU (Dynamic Utils   PS (PDA Stats)
  _   __               QU (Quiesce)           replaced QC/UR)  UC (Upd Cat)
  _   __               RC (Recover)   Gen: US (User App)
  _   __               RO (Reorg)          AL (Alter)
  _   __               RR (Rpt Rcv)
  _   __
                        Press ENTER to process request.  Press PF3 to exit.

Find the condition we want that was referenced in the community post using the FIND command....FIND NUMBER
Enter an "S" in the "O" column and a >1 in the CONDITION column


RDA.ACON          ------    ACTION  CONDITIONS       -------  yyyy/mm/dd hh:mm
Command  ==>                                                   SCROLL ==> CSR
PT022 PT022I -  STRING FOUND
Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: authid1
Conditions listed by ==> T  (T)ype or maintenance (A)ction
List based on type   ==> A  (C)ommonly used, (T)ablespace, (I)ndex, (L)OB,
                            (R)TS, (D)B2 Catalog, (B)uilt-in PDA program,
                            (A)ll conditions
O DESCRIPTION                              T  CONDITION
s (TS) Number of tables per tablespace     S | >1                             |
_ (TS) Total number of rows                S |                                |
_ (TS) Total number of rows       Change # S |                                |
_ (TS) Total number of rows       Change % S |                                |
_ (TS) Highest row percent in tablespace   S |                                |
_ (TS) Dropped row space percent           S | >=  30                         |

PF3 back

The Extract procedure and action procedure are created.

RDA.CPL             ----- EXTRACT PROCEDURE SERVICES  ----    yyyy/mm/dd hh:mm
 COMMAND ==>                                                   SCROLL ==> CSR
 RAP015 RAP015I - EXTRACT procedure "CONVTPBG"  -  CREATED
  PROCEDURE SELECTION LIST PARAMETERS
  Name ==> CONVTPBG  Creator ==> authid1    Subsystem ==> ssid
    Desc ==> *                                Location  ==> LOCAL
-------------------------------------------- SQLID: authid1  User ID: authid1
                                               S A  DB2  LAST UPDATE LAST SUBMIT
 O EXT PROC PROCEDURE DESCRIPTION     CREATOR  O P  SYSD DATE   TIME DATE   TIME
_ ________ _________________________ authid1  N N  <=== NEW PROCEDURE CREATION
_ CONVTPBG CONVERT TB'S TO PBG'S     authid1  U Y  ssid 03/28 23:57 01/01 00:00
 ******************************** BOTTOM OF DATA *******************************

PF3 back to the main PDA menu and enter the Execution procedures XP menu item.

Create a new Execution procedure and link to the Extract procedure that has just been created.

RDA.EPL              ---- EXECUTION PROCEDURE SERVICES ----   yyyy/mm/dd hh:mm
COMMAND  ==>                                                   SCROLL ==> CSR

 PROCEDURE SELECTION LIST PARAMETERS
 Name ==> CONVTPBG  Creator ==> authid1   Subsystem ==> ssid
   Desc ==> *                               Location  ==> LOCAL
------------------------------------------- SQLID: authid1   User ID: authid1
                                                  ----UPDATE---- ----SUBMIT----
O EXEC PRC PROCEDURE DESCRIPTION     CREATOR  SHR DATE     TIME  DATE     TIME
C CONVTPBG CONVERT TB'S TO PBG'S____ authid1  U   <=== NEW PROCEDURE CREATION
******************************** BOTTOM OF DATA *******************************

Enter the Extract procedure to be linked...

RDA.EBPM          ----   BUILD EXECUTION PROCEDURES    ----   yyyy/mm/dd hh:mm
COMMAND  ==>
RAP003 RAP003E -  At least one extract option must be selected
Loc: LOCAL --------------------------------- SQLID: authid1  User ID: authid1

   PROCEDURE PARAMETERS

        Procedure Name  ==> CONVTPBG
           Description  ==> CONVERT TB'S TO PBG'S
           Share Option ==> U

         Subsystem Connection ID ==> ssid

   PROCEDURE SELECTION LIST PARAMETERS

        Extract Procedure Selection List Parameters
           Procedure Name ==> CONVTPBG    Creator ==> authid1

        Report Procedure Selection List Parameters
           Procedure Name ==>             Creator ==> authid1


Enter Procedure Selection Info to add procedures or specify ? in command line.
Select it....with an "s"

RDA.EBPD            -- EXTRACT/REPORT PROCEDURE SELECTION --  yyyy/mm/dd hh:mm
COMMAND  ==>                                                   SCROLL ==> CSR

Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: authid1
 Execution Procedure :  CONVTPBG   Description:  CONVERT TB'S TO PBG'S
Extract Procedure   :  CONVTPBG   Creator:  authid1
Report Procedure    :             Creator:  authid1

OPTION PROC TYPE    PROCEDURE   PROCEDURE DESCRIPTION       CREATOR   SHR SYSID
s    EXTRACT      CONVTPBG    CONVERT TB'S TO PBG'S       authid1   U   ssid
******************************** BOTTOM OF DATA *******************************
PF3 back and the Execution procedure is created. 

RDA.EPL              ---- EXECUTION PROCEDURE SERVICES ----   yyyy/mm/dd hh:mm
COMMAND  ==>                                                   SCROLL ==> CSR
RAP015 RAP015I - EXECUTION procedure "CONVTPBG"  -  CREATED
 PROCEDURE SELECTION LIST PARAMETERS
 Name ==> CONVTPBG  Creator ==> authid1   Subsystem ==> ssid
   Desc ==> *                               Location  ==> LOCAL
------------------------------------------- SQLID: authid1   User ID: authid1
                                                  ----UPDATE---- ----SUBMIT----
O EXEC PRC PROCEDURE DESCRIPTION     CREATOR  SHR DATE     TIME  DATE     TIME
_ ________ _________________________ authid1  N   <=== NEW PROCEDURE CREATION
_ CONVTPBG CONVERT TB'S TO PBG'S     authid1  U   yyyy/mm/dd hh:mm 01/01/01 00:00
******************************** BOTTOM OF DATA *******************************


Now the Execution procedure is ready to be submitted with an "s" in the "O" column.

RDA.EPL              ---- EXECUTION PROCEDURE SERVICES ----   yyyy/mm/dd hh:mm
COMMAND  ==>                                                   SCROLL ==> CSR
RAP015 RAP015I - EXECUTION procedure "CONVTPBG"  -  CREATED
 PROCEDURE SELECTION LIST PARAMETERS
 Name ==> CONVTPBG  Creator ==> authid1   Subsystem ==> ssid
   Desc ==> *                               Location  ==> LOCAL
------------------------------------------- SQLID: authid1   User ID: authid1
                                                  ----UPDATE---- ----SUBMIT----
O EXEC PRC PROCEDURE DESCRIPTION     CREATOR  SHR DATE     TIME  DATE     TIME
_ ________ _________________________ authid1  N   <=== NEW PROCEDURE CREATION
s CONVTPBG CONVERT TB'S TO PBG'S     authid1  U   yyyy/mm/dd hh:mm 01/01/01 00:00
******************************** BOTTOM OF DATA *******************************

The Execution screen below contains another model in the PRE field.

HLQ.CDBAMDL(TEP2)

//TEP2    EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//STEPLIB  DD   DISP=SHR,DSN=HLQ.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
DSN SYSTEM(ssid)
 RUN  PROGRAM(DSNTEP2) PLAN(DSNTEP12) -
  PARMS('/ALIGN(MID)') -
    LIB('HLQ.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
This code is added AFTER the job card MJJOBCD and then Database Analyzer adds a SYSIN card before the DDL is listed on the generated code. 
It is designed to run DSNTEP2 to process the DDL generated by our CONVTPBG model.
Another DB2 DDL execution utility could be used here too instead of TEP2.

Set execution mode to BATCH below. Change the Action JCL Destination to "P" for preview to see what the JCL looks like that will generate the output we want.

RDA.ESMN          ----  SUBMIT - BUILD  JCL PARAMETERS ----   yyyy/mm/dd hh:mm
COMMAND ==>

Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: authid1
EXECUTION OPTIONS          Proc: CONVTPBG   Desc: CONVERT TB'S TO PBG'S
  Edit Objects ==> N  1 - SELECTED;     1 - AVAILABLE
  Edit Reports ==> N  0 - SELECTED;     0 - AVAILABLE
  Edit Actions ==> N  1 - SELECTED;     1 - AVAILABLE

  Execution Mode  ==> B  (B)atch
  Collection Type ==> F   Create Reports ==> N   Create Action JCL ==> Y

COLLECTION OPTIONS
Statistics Subsystem ==> ssid   VSAM Buffers ==> 0004   WTOR Code ==> 00
  Audit Errors Allowed ==> 0001   Nbr of Scans ==> 0006   CPs/zIIPs:    4/6
REPORT OPTIONS
  Redirection ==> N   Lib ==>
ACTION OPTIONS
Action JCL Destination  ==> P       Member ==> CONVTPBG
Action JCL Library      ==> 'HLQ.CNTL'
  Related Models: Jobcard ==> MJJOBCD    Pre ==> TEP2      Post ==>
Related Models Library  ==> 'HLQ.CDBAMDL'
Preview the JCL with a "P".

//authid1B JOB (106100000),' CONVTPBG ',CLASS=B,MSGCLASS=X,
//             NOTIFY=authid1,REGION=0M,TIME=1440
/*JOBPARM SYSAFF=ssid
//*
//*
//*   GENERATED ON yyyy/mm/dd AT hh:mm:ss BY authid1
//*   HLQ.CDBAMDL
//*
//STEP1   EXEC PGM=PTLDRIVM,PARM='SUFFIX=00,EP=RALMCTL/',
//        REGION=5M
//STEPLIB  DD DISP=SHR,DSN=HLQ.CDBALOAD
//PTILIB   DD DISP=SHR,DSN=HLQ.CDBALOAD
//PTIPARM  DD DISP=SHR,DSN=HLQ.CDBAPARM
//PTIMSG   DD SYSOUT=*
//SYSPRINT DD DUMMY
//SYSUDUMP DD SYSOUT=*
//ABNLIGNR DD DUMMY                 SUPPRESS ABENDAID DUMPS
//SYSTSPRT DD DUMMY
//PTIIPT   DD *
00CONVTPBGauthid1 ssid        ~b
00          ssid/ N
00  DHLQ.CNTL                            authid1 CONVTPBG
00'HLQ.CDBAMDL'                         TEP2
01//authid1A JOB (106100000),'CONVTPB',CLASS=A,MSGCLASS=X,
01//             NOTIFY=authid1,REGION=0M,TIME=1440
01/*JOBPARM SYSAFF=ssid

Submit this JCL. It will write the result to HLQ.CNTL(CONVTPBG)

At the end of the SYSOUT this can be seen if it is successful:

Action Job Stream created =>                                            0:59:57
 DSN ==> HLQ.CNTL(CONVTPBG)                                 0:59:57
Action Processing Ended                                                 0:59:57

Execution Processing complete                                           0:59:57

Now we can look at the output...in HLQ.CNTL(CONVTPBG)
//authid1B JOB (106100000),' CONVTPBG ',CLASS=B,MSGCLASS=X,            <The job card you specified in model member MJJOBCD    
//             NOTIFY=authid1,REGION=0M,TIME=1440
/*JOBPARM SYSAFF=ssid
//TEP2    EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) <The code for DSNTEP2 specified in model member TEP2      
//STEPLIB  DD   DISP=SHR,DSN=HLQ.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
DSN SYSTEM(ssid)
 RUN  PROGRAM(DSNTEP2) PLAN(DSNTEP12) -
  PARMS('/ALIGN(MID)') -
    LIB('HLQ.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN  DD  * <PDA adds in a SYSIN card when jobcard member MJJOBCD is used.
CREATE TABLESPACE TBPT001 in DBCORP <Notice that the new tablespace name is made up of
     DEFINE NO the first two characters and the 5th and 6th characters of the table name TBDEPT
     MAXPARTITIONS 1; followed by the increment value 001 being the first one in the output
ALTER TABLESPACE DBCORP.TSDEPT    MOVE TABLE In the model the symbolic variable %OBJECT is the table name.
                authid1.TBDEPT
                 TO DBCORP.TBPT001  ;
CREATE TABLESPACE TBRK002 in DBCORP
       DEFINE NO
       MAXPARTITIONS 1;
ALTER TABLESPACE DBCORP.TSDEPT    MOVE TABLE
                authid1.TBWORK_GROUP
                 TO DBCORP.TBRK002  ;

This JCL can be submitted to complete the work in DB2.

Additional Information

See the IBM documentation about this ALTER MOVE TABLE Function:  Moving tables from multi-table table spaces to partition-by-growth table spaces

Note that there are restrictions that must be adhered to regarding this function.