Identify and convert eligible tablespaces to partition by growth using RC/Query
Article ID: 247247
Updated On:
Products
RC/Query for DB2 for z/OS
Issue/Introduction
RC/Query for Db2 for z/OS (RCQ) is able to help with the task of converting old tablespaces to Partition by Growth (PBG).
Resolution
Identify tablespaces that are eligible.
The TS/EG report can identify tablespaces that are able to be converted to UTS:
RQTSEG 20.0 ------ RC/Q Table Space Eligible for PBG ------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> EG Where => N
Table Space ===> TS% > Creator ===> authid1 >
Data Base ===> dbname > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 4 >
CMD NAME CREATOR CREATEDBY DATABASE BPOOL PART PGZ STS N. TBLS
________ TSROLE authid1 authid1 dbname BP0 0 4K A 1
________ TSROLE1 authid1 authid1 dbname BP0 0 4K A 1
________ TSROLE3 authid1 authid1 dbname BP0 0 4K A 1
________ TSUSER authid1 authid1 dbname BP0 0 4K A 1
This is the SQL used to find Simple and Segmented (single-table) that are candidates.
SELECT A.NAME , A.CREATOR , A.DBNAME , A.BPOOL , A.PARTITIONS , A.LOCKRULE ,
A.PGSIZE , A.ERASERULE , A.STATUS , A.IMPLICIT , A.NTABLES ,
A.CLOSERULE , A.SEGSIZE , A.CREATEDBY , A.STATSTIME , A.TYPE ,
A.NACTIVEF , CHAR ( A.NACTIVEF )
FROM SYSIBM.SYSTABLESPACE A , SYSIBM.SYSTABLEPART B
WHERE ( A.DBNAME = B.DBNAME
AND A.NAME = B.TSNAME )
AND A.NTABLES = 1 <====tablespaces with only one table
AND A.CREATOR <> 'SYSIBM' <====not catalog tablespaces
AND A.TYPE NOT IN ( 'P' , 'R' , 'G' , 'O' ) <====tablespaces not already UTS, Implicit, or LOB
AND A.PARTITIONS = 0 <====tablespaces with no partitions
AND B.STORNAME <> ' ' <====Stogroup managed
AND A.DBNAME = 'dbname'
AND STRIP ( A.NAME , T ) LIKE 'TS%'
AND A.CREATOR = 'authid1'
ORDER BY A.NAME , A.DBNAME , A.CREATOR WITH UR
The DB2 manual says "Partition-by-growth table spaces are the suggested alternative for single-table Db2-managed segmented (non-UTS) table spaces, which are deprecated.".
Convert the existing tablespace to PBG by issuing an ALTER TABLESPACE statement with the MAXPARITIONS clause.
Take this example tablespace. It has one table and one index also.
.CONNECT ssid
SET CURRENT SQLID = 'authid1';
CREATE TABLESPACE tsname
IN dbname
USING STOGROUP stogroup
PRIQTY 12
SECQTY 12
ERASE NO
BUFFERPOOL BP0
CLOSE NO
LOCKMAX 0
SEGSIZE 64
INSERT ALGORITHM 0
FREEPAGE 0
PCTFREE 5 FOR UPDATE 0
GBPCACHE CHANGED
DEFINE YES
LOGGED
TRACKMOD YES
COMPRESS NO
LOCKSIZE ANY
MAXROWS 255
CCSID EBCDIC;
Using the RC/Query line command "CPBG" on one of these tablespaces will generate an ALTER statement.
CPBG - Convert normal TS object to Partition by Growth(PBG) TS
CMD NAME CREATOR CREATEDBY DATABASE BPOOL PART PGZ STS N. TBLS
________ TSROLE authid1 authid1 dbname BP0 0 4K A 1
________ TSROLE1 authid1 authid1 dbname BP0 0 4K A 1
________ TSROLE3 authid1 authid1 dbname BP0 0 4K A 1
CPBG____ TSUSER authid1 authid1 dbname BP0 0 4K A 1
---------------------------------------------------------------------
********************************* TOP OF DATA ************************
This DDL is generated.....
CONNECT ssid
SET CURRENT SQLID ='authid1';
ALTER TABLESPACE dbname.tsname
MAXPARTITIONS 1 ;
******************************** BOTTOM OF DATA **********************
And then executed........
.CONNECT ssid
BPA0198I: CURRENT FUNCTION LEVEL IS V12R1M500
RETCODE = 0
SET CURRENT SQLID ='authid1';
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
ALTER TABLESPACE dbname.tsname
MAXPARTITIONS 1 ;
DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT
DBCORP.TSUSER HAS PLACED OBJECT IN ADVISORY REORG PENDING
Using the DB2 display command we can see.....it is now in AREOR status.
------------------------------------------------------------ User ID: authid1
¦ -DIS DB(dbname) SPACE(tsname) LIMIT(*) ¦
¦ ¦
¦ ¦
¦ ¦
********************************* TOP OF DATA ********************************
DSNT360I !ssid ***********************************
DSNT361I !ssid * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I !ssid ***********************************
DSNT362I !ssid DATABASE = dbname STATUS = RW
DBD LENGTH = 64598
DSNT397I !ssid
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
tsname TS RW,AREOR
******* DISPLAY OF DATABASE DBCORP ENDED **********************
DSN9022I !ssid DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
******************************** BOTTOM OF DATA ******************************
After a REORG to clear the AREOR status....the DDL of the tablespace looks like this:
.CONNECT ssid
SET CURRENT SQLID = 'authid1';
CREATE TABLESPACE tsname
IN dbname
USING STOGROUP stogroup
PRIQTY 12
SECQTY 12
ERASE NO
BUFFERPOOL BP0
DSSIZE 4G
CLOSE NO
LOCKMAX 0
SEGSIZE 64
INSERT ALGORITHM 0
FREEPAGE 0
PCTFREE 5 FOR UPDATE 0
GBPCACHE CHANGED
DEFINE YES
LOGGED
TRACKMOD YES
MAXPARTITIONS 1 <------------
COMPRESS NO
LOCKSIZE ANY
MAXROWS 255
CCSID EBCDIC
NUMPARTS 1;
After conversion the tablespace TSUSER is no longer found by the TS/EG report:
RQTSEG 20.0 ------ RC/Q Table Space Eligible for PBG ------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> TS Option ===> EG Where => N
Table Space ===> * > Creator ===> authid1 >
Data Base ===> DBCORP > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 3 >
CMD NAME CREATOR CREATEDBY DATABASE BPOOL PART PGZ STS N. TBLS
________ TSROLE authid1 authid1 dbname BP0 0 4K A 1
________ TSROLE1 authid1 authid1 dbname BP0 0 4K A 1
________ TSROLE3 authid1 authid1 dbname BP0 0 4K A 1
Additional Information
Feedback
Yes
No
Powered by
