Database Analyzer : Converting table spaces to PBG's
Article ID: 262898
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.
Feedback
Yes
No
Powered by
