Calling Db2 12 for z/OS, Are you available ?

Calling Db2 12 for z/OS, Are you available ?


Availability of data is a must these days. Everything has to be up and running, data outages are becoming few and far between and preferable non-existing. With every release Db2 for z/OS becomes more highly available; however this requires the usage of new features and structures. If your system is running Db2 like it’s 1999, then you will still need some of the outages of 1999.


Choose your table space type carefully.

The two types of Universal Table Spaces (UTS) are now the preferred types.


When creating new objects, only UTS should be considered, especially if availability is a concern. In DB2 11 most physical features of a table space became more highly available by making them deferred alters. This means that rather than unloading, dropping, recreating and loading the table space, a simple alter followed by an online reorg would do the trick. The pre-requisite? Universal Table Spaces.


In Db2 12 for z/OS the deferred alter behavior can become the default for all your table space alters improving availability even further. This can be achieved by setting the system parameter DDL_MATERIALIZATION to ALWAYS_PENDING. For a full explanation on this DSNZPARM, go to our previous article. ( )


In Db2 12 a new type of Universal Table Space is introduced, the UTS PBR RPN (say that 10 times fast), which stands for Universal Table Space Partition By Range Relative Page Numbering.


UTS PBR RPN removes the restriction between the number of partitions and the number of pages a single partition can have. The problem with partitioned table spaces was their absolute numbering, that means that the 4 byte page number includes a number of bits that identify the partition number. So the more partitions the fewer bits are left for the page number and vice versa.

Db2 12 for z/OS with UTS PBR RPN has separated the partition number from the page number. This means that now RIDs now use 7 bytes (8 are allocated, but only 7 in use) instead of 5. This now means we have a 2-byte partition number, a 4-byte page number and a 1-byte IDMAP value (as before).


Since the page number no longer contains the partition number, the page number is relative to the start of a given partition. Hence this enhancement is called “Relative Page Numbering”. This feature only exists for Universal Table Spaces PBR, not PBG or classic partitioned.


An advantage is that individual partitions can now be up to 1Tb in size, regardless of number of partitions page size. Multiplied by 4096, this means that the maximum size of a PBR RPN table is 4Pb.


The most exciting consequence is that there now is an ALTER to increase DSSIZE for a PBR RPN, for a single partition. Not only that, it is an immediate ALTER not even requiring a REORG to materialize. That means the data remains online, although packages are invalidated and will require a rebind. For a full explanation on UTS PBR RPN, please check out our previous article (


Index compression

With data growing ever bigger, the ability to compress indexes becomes more important. Indexes with a page size of lager than 4K can be compressed for a while now. This is a significant improvement for space management as well as more efficient I/O behavior. To pick the correct page size for your indexes, it is a good idea to review the DSN1COMP report and avoid wasting memory.


Before Db2 12 for z/OS altering an index to turn compression on, would result in a rebuild pending (RBDP) exception status, rendering this index unusable until a rebuild index utility was run. This could obviously impact your applications.


As of Db2 12 for z/OS turning index compression on, has become a deferred alter. Meaning an entry is made in SYSPENDINGDDL and it will only materialize when running an online REORG index or an online REORG table space.


Quick check to see who has been paying attention to this article. Since compress index has become a deferred alter, can you guess the prerequisite (a hint it’s in the first part of this article)? Yes, the index has to be related to a Universal Table Space.


Insert partition anywhere

Before Db2 12 for z/OS we had the ability to add a partition to the end of the table space, but now we have that ability to insert a partition anywhere. Imagine table space with 3 partitions ending at 200, 400 and 800.

 part orig.jpg


So, you could give the command to add a partition and that partition would have to have a higher limit key than the highest partition key of the table space.

 part old.jpg


If you wanted a partition somewhere in the middle you either had to drop and recreate the entire table space or you had to add a partition and then alter all the necessary limit keys and then reorg.


Now it’s possible to issue the same command with any partition/limit key and Db2 will figure out where the new partition fits. Imagine you would want a partition ending at 600 between partition 2 & 3. DB2 12 for z/OS now allows you to do so.

 part new.jpg


Pay attention, this allocates partition 5 which logically comes between partitions 2 & 3. The partition key specified is always inclusive, meaning the value 600 is stored in partition 5. Only one partition can be added within one SQL statement. But multiple statements can be issues one after another.


Adding a partition in the middle is a pending change, when there is already at least one other pending change on that table space or when both of the following conditions are true: The partition is in the middle of the table (as in not at the end) and the data sets of the table space are already created.  A materializing online reorg of the next highest partition (in this case Part 3) is needed to end up in the wanted state.


Automatic retry of GRECP and LPL recovery

When a group buffer pool structure fails before data in the structure is written to disk, the group buffer pool is put in the “group buffer pool recovery pending” (GRECP) exception status. The –start database command must be issued to recover the data.

The logical page list (LPL) contains a list of pages that can’t be read or written to. In a data sharing environment, the LPL also contains pages for must complete operations such as commit or restart, due to coupling facility issues.


Before Db2 12 for z/OS we had automatic LPL recovery and automatic recovery of GRECP. However, Db2 would try once and if that automatic recovery failed the list remains and a user had to issue the –start database command. If this was not scripted and automated, that meant an outage until a dba could log on and issue the command.


In Db2 12 for z/OS this retry logic became standard. Db2 will retry the recovery every 3 minutes until it is able to.


With every release Db2 tries to improve the availability, many new features are introduced so it’s of vital importance to stay current with your Db2 education (through IDUG, training partners, redbooks etc). This blog post is just an introduction of some of the availability features in Db2 12 for z/OS an encouragement to start your learning path. It didn’t include topics like “asynchronous lock duplexing”, larger log datasets, utility improvements (load resume yes backout yes) and many other. Understanding and apply these Db2 12 for z/OS features will lead to fewer outages and letting Db2 answer when it gets called upon.


Kurt Struyf

Gold Consultant
content committee chair

Recent Stories
Cardinality Without Runstats

Jenkins Pipeline H2 Database Integration Testing

December Content Recap