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. no “IN 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