RC/Query : Identify and convert eligible tablespaces to partition by growth.
search cancel

RC/Query : Identify and convert eligible tablespaces to partition by growth.

book

Article ID: 247247

calendar_today

Updated On:

Products

RC/Query for DB2 for z/OS

Issue/Introduction

RC/Query is able to help with the task of converting old tablespaces to Partition by Growth(PBG).

Environment

DB2 for Z/OS

Release : R20

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 ===> DBCORP                > 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   DBCORP    BP0         0  4K  A        1
________ TSROLE1   authid1   authid1   DBCORP    BP0         0  4K  A        1
________ TSROLE3   authid1   authid1   DBCORP    BP0         0  4K  A        1
________ TSUSER   authid1   authid1   DBCORP    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 = 'DBCORP'
    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 TSUSER
           IN DBCORP
           USING STOGROUP SYSDEFLT
               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   DBCORP    BP0         0  4K  A        1
________ TSROLE1   authid1   authid1   DBCORP    BP0         0  4K  A        1
________ TSROLE3   authid1   authid1   DBCORP    BP0         0  4K  A        1
CPBG____ TSUSER   authid1   authid1   DBCORP    BP0         0  4K  A        1

---------------------------------------------------------------------
********************************* TOP OF DATA ************************

This DDL is generated.....
CONNECT ssid

SET CURRENT  SQLID ='authid1';

 ALTER TABLESPACE DBCORP.TSUSER
  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 DBCORP.TSUSER
   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(DBCORP) SPACE(TSUSER) LIMIT(*)                       ¦
       ¦                                                              ¦
       ¦                                                              ¦
       ¦                                                              ¦
 ********************************* TOP OF DATA ********************************
DSNT360I  !ssid ***********************************
DSNT361I  !ssid *  DISPLAY DATABASE SUMMARY
                 *    GLOBAL
DSNT360I  !ssid ***********************************
DSNT362I  !ssid     DATABASE = DBCORP  STATUS = RW
                    DBD LENGTH = 64598
DSNT397I  !ssid
 NAME     TYPE PART  STATUS            PHYERRLO PHYERRHI CATALOG  PIECE
 -------- ---- ----- ----------------- -------- -------- -------- -----
 TSUSER   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 TSUSER
           IN DBCORP
           USING STOGROUP SYSDEFLT
               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   DBCORP    BP0         0  4K  A        1
________ TSROLE1   authid1   authid1   DBCORP    BP0         0  4K  A        1
________ TSROLE3   authid1   authid1   DBCORP    BP0         0  4K  A        1

Additional Information

Tablespace Reports