Partition By Growth Table Spaces - Partition 1, Getting Started

Partition By Growth Table Spaces - Partition 1, Getting Started

 

Universal Table Space (UTS)

 

Universal Table Spaces (UTS) were introduced in Db2 9 to bring together the advantages of partitioned table spaces, where you can have a capacity >64Gb, and segmented table spaces which benefit from excellent spacemap management within each partition, and also you are able to do mass deletes, which was not possible with non-UTS table controlled partitioning.

 

Partition by Range (PBR) follows on from the Db2 8 enhancement of Table Controlled Partitioning with the simple addition of the SEGSIZE parameter in the table space definition, allowing us to enjoy the benefits of range partitioning but with the added performance benefits brought with the spacemap management of segmented table space.

 

Partition by Growth (PBG) table spaces also have the SEGSIZE parameter, and this, along with the MAXPARTITIONS parameter makes it Partition by Growth, instead of a Partition by Range table space

 

Starting at Db2 12 there is a new type of UTS: Partition by Range - Relative Page Numbering (PBR RPN), this means that the PBR that was introduced in in Db2 9 is now officially PBR Absolute Page Numbering in order to differentiate between the 2 types of Partition by Range table space.

 

UTS have the potential to grow up to 128Tb at Db2 11 (or 4Pb for PBR RPN at Db2 12 – 4096 * 1Tb partitions). Universal table spaces (whichever type) need to be single-table table spaces.

 

In this blog, we’ll just be discussing Partition by Growth (PBG) table spaces.

 

The Basics of PBGs

 

PBGs are both partitioned and segmented and when data is inserted, Db2 starts to fill up the first partitions or existing partitions. When they become full a new partition is automatically and seamlessly created which can be used immediately. This is similar to how a segmented table space works where it grows up to 32 datasets of 2Gb each i.e. max size of 64Gb. PBG partitions are sized based on the DSSIZE parameter.

 

When a new partition is created a number of characteristics are copied from the previous partition:

FREEPAGE

PCTFREE

LOGGED or NOT LOGGED

TRACKMOD YES or NO

COMPRESS YES or NO

COMPRESSION DICTIONARY (if one exists)

 

The action of creating a new partition also inserts a row into SYSCOPY, with ICTYPE = A (ALTER) so it’s easy for us to track the date and time that new partitions are being created. For example, this shows 4 new partitions being added to the PBG table space automatically by Db2.

 

SELECT TSNAME, DSNUM, ICTYPE, ICDATE, ICTIME
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'A'
AND   TSNAME = 'SACH007'
AND   ICDATE > '180829'
ORDER BY ICDATE, ICTIME ASC
WITH UR;
---------+---------+---------+---------+---------+---------
TSNAME          DSNUM  ICTYPE  ICDATE  ICTIME
---------+---------+---------+---------+---------+---------
SACH007           295  A       180830  074606
SACH007           296  A       180830  152811
SACH007           297  A       180831  102728
SACH007           298  A       180831  170619
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

 

Creation/Conversion

 

We can create a table space as PBG in 3 ways:

  • Explicitly, using DDL to specify the characteristics of the table space

  • Implicitly, by allowing Db2 and our zParms settings to define the table space

  • By converting from a simple or segmented table space with SQL ALTERs and an online REORG

Explicit PBG table space DDL

When we explicitly define our DDL to create a PBG, the important keyword is MAXPARTITIONS. This can be a number between 1 and 4096 and this is the maximum number of partitions we want to allow to be created (we can ALTER to increase or decrease later if we need to). While it might be tempting to simply go for MAXPARTITIONS 4096 and then not have to worry about it, there can be a storage and CPU overhead relative to the MAXPARTITIONS rather than number of defined/used partitions, so it’s worth spending some time thinking about a realistic maximum number. A large number for MAXPARTITIONS will result in a large amount of storage being assigned to the thread, but if you are never going to need that many partitions then it could cause an unnecessary impact on available storage and possibly CPU for other Db2 threads.

 

Your DDL might look something like this:

CREATE TABLESPACE EXPLTS
    IN MYDB
    USING STOGROUP MYSTOG
    DSSIZE 2 G
    SEGSIZE 32
    MAXPARTITIONS 32
    BUFFERPOOL BP1
    CCSID UNICODE
;

This will result in a table space with up to 32 partitions, of 2Gb per partition i.e. max size of 64Gb.

 

Implicit PBG table space DDL

To get implicitly created PBG table spaces, they are created when our table DDL either has:

 

1. noIN DATABASE.TABLESPACE” clause which results in an implicitly created database AND PBG table space e.g.

CREATE TABLE
  MYSCHEMA.MYTABLE
   (
   COLUMN1   CHAR(5)
    FOR SBCS DATA
  ,COLUMN2   CHAR(5)
    FOR SBCS DATA
  ,COLUMN3   TIMESTAMP
   )
;

This results in the following DDL which shows the additional parameters and values populated by Db2, many of which are taken from the zParm values for the member or subsystem that the object is created in:

 

CREATE
 TABLESPACE MYTABLE <- Db2 creates the table space name with a likeness to the table name
    IN DSN01333 <- Db2 implicitly creates a database like DSNnnnnn
    USING STOGROUP SYSDEFLT
    PRIQTY -1
    SECQTY -1
    ERASE NO
    FREEPAGE 0
    PCTFREE 5
    GBPCACHE CHANGED
    COMPRESS NO
    TRACKMOD YES
    LOGGED
    DSSIZE 4 G <- Db2 default
    SEGSIZE 32 <- site default from DPSEGSZ
    MAXPARTITIONS 256 <- Db2 default
    BUFFERPOOL BP0 <- site default from TBSPOOL
    LOCKSIZE ROW <- Db2 default
    LOCKMAX SYSTEM
    CLOSE YES
    CCSID EBCDIC <- site default from CCSID
    MAXROWS 255
;

2. or, the CREATE TABLE DDL only has an “IN DATABASE dbname” clause, which results in an implicitly created PBG table space in a previously (explicitly) defined database. E.g.

CREATE TABLE
  MYSCHEMA.MYTABLE2
   (
   COLUMN1   CHAR(5)
    FOR SBCS DATA
  ,COLUMN2   CHAR(5)
    FOR SBCS DATA
  ,COLUMN3   TIMESTAMP
   )
IN DATABASE MYDB
;

 

This results in the following DDL with some characteristics inherited from the database that it has been created in, and some from the Db2 defaults:

CREATE
 TABLESPACE MYTABLE2
    IN D205040
    USING STOGROUP SGMYSTOG
    PRIQTY -1    SECQTY -1
    ERASE NO
    FREEPAGE 0
    PCTFREE 5
    GBPCACHE CHANGED
    COMPRESS NO
    TRACKMOD YES
    LOGGED
    DSSIZE 4 G
    SEGSIZE 32
    MAXPARTITIONS 256
    BUFFERPOOL BP3
    LOCKSIZE ROW
    LOCKMAX SYSTEM
    CLOSE YES
    CCSID UNICODE
    MAXROWS 255
;

In both examples, with or without an explicitly created database, the table DDL that is generated after creation of the table spaces includes the line PARTITION BY SIZE EVERY 4 G :

CREATE TABLE
  MYSCHEMA.MYTABLE
   (
   COLUMN1   CHAR(5)
    FOR SBCS DATA
  ,COLUMN2   CHAR(5)
    FOR SBCS DATA
  ,COLUMN3   TIMESTAMP
   )
    CCSID EBCDIC
    NOT VOLATILE
    APPEND NO
  PARTITION BY SIZE EVERY 4 G
;

 

We don’t see PARTITION BY SIZE EVERY 4 G when the table space has been explicitly created. These DDLs have been generated using a 3rd party Catalog Management tool, but you would see the same from Db2 Admin Tool or Data Studio.

 

Implicitly created table spaces are always PBG, unless the CREATE TABLE DDL has range partitioning definitions.

 

The implicit PBG table space will be defined with the following characteristics when both the database and table space are implicitly created, where only the table space is created implicitly within an existing database, some characteristics will be inherited from the explicit database (marked in the table below with X)

 

DDL characteristic    Default Value                   Implicit TS & DB        Implicit TS only
----------------------------------------------------------------------------------------------
DSSIZE 4G I I
MAXPARTITIONS 256 I I
LOCKSIZE ROW I I
STOGROUP SYSDEFLT I X
SEGSIZE Based on DPSEGSZ zPARM value I I
BUFFERPOOL Based on TBSPOOL zPARM value I X
(or TBSBP8K/16K/32K if required) 
COMPRESSION Based on IMPTSCMP zPARM value I I
SECQTY based on MGEXTSZ zPARM value I I
DEFINE based on IMPDSDEF zPARM value I I
CCSID based on ENSCHEME zPARM value I X

The table space will have a name generated by Db2 which will be derived from the table name. In the case of an implicit database also being created it will follow the convention DSNnnnnn. The number of the database increments each time a new database is created, and each implicitly created database may only contain 1 table space.

Convert from Simple/Segmented table space to PBG

The third way to get PBG table spaces is to convert an existing simple or segmented table space to be PBG.

 

If the original table space is a single table simple table space, then with a minimum of just 1 ALTER it can become PBG. First you have to do the ALTER for MAXPARTITIONS. You have to run this ALTER first otherwise any subsequent ALTERs for SEGSIZE or DSSIZE will fail (because they can only be run against a UTS). Running an ALTER for DSSIZE or SEGSIZE before MAXPARTITIONS will result in an SQLCODE -650 with REASON 7 or 8 respectively.

 

If the table space is already segmented, but with a SEGSIZE <32, Db2 will increase it to 32 during the REORG unless you specify an ALTER TABLESPACE statement for SEGSIZE prior to the REORG. If the SEGSIZE is 64 and you do not specify an ALTER with a smaller SEGSIZE, then SEGSIZE 64 will remain after the REORG.

 

For DSSIZE if you do not specify anything, you get the default of 4G, or you can run an ALTER TABLESPACE to set the DSSIZE prior to the materialising REORG.

 

You can run all 3 ALTERs in one step, followed by a single REORG and all 3 changes are materialised.

 

ALTER TABLESPACE dbname.tsname MAXPARTITIONS n;
ALTER TABLESPACE dbname.tsname SEGSIZE n;
ALTER TABLESPACE dbname.tsname DSSIZE nG

These ALTERs, or Pending Definition Changes (PDCs), will be inserted into the SYSIBM.SYSPENDINGDDL table and the SQL will complete with SQLCODE +610, a warning that the table space is in AREOR (advisory REORG pending) as a result of the PDCs.

 

A SHRLEVEL CHANGE or REFERENCE REORG will materialise the changes. The REORG doesn’t need to be run immediately after the ALTERs have run; it can be scheduled for another time e.g. a maintenance window. There is no impact to the applications that use the table space while the object is in AREOR pending, they can continue to read and write to the table.

 

For large objects where there may not be sufficient disk space available for sorting, or to reduce the elapsed time of the materialising REORG, you can use SHRLEVEL CHANGE with keywords SORTDATA NO and RECLUSTER NO.

 

Note: a REORG with SHRLEVEL NONE will ignore the AREOR status and will NOT materialise the pending definition changes.

 

Summary

So there we have it, 3 ways that we can get Partition by Growth table spaces. In the next part, we will look at the use of PBGs in more detail; living with PBGs and subsequent changes which we may need to make, the advantages and disadvantages of PBGs, and recommendations based on experts and experience.

 

Reference:

IBM Knowledge Center: Partition by Growth Tablespaces

 

 

6 Likes
Recent Stories
Partition By Growth Table Spaces - Partition 2, Living with PBGs

Partition By Growth Table Spaces - Partition 1, Getting Started

Lateral join