The DB2 12 for z/OS Blog Series – Part 7: Relative Page Number Table Spaces

One of the most significant new features for supporting big data in a DB2 12 environment is relative page numbering (or RPN) for range-partitioned table spaces. You can either create a new RPN range-partitioned table space, or an existing range-partitioned table space can be changed to RPN via an ALTER TABLESPACE with PAGENUM RELATIVE, followed by an online REORG of the entire table space.

But why would you want RPN table spaces instead of the already-existing table spaces types in DB2? The simple answer is the ability to grow the amount of data you store. RPN table spaces enable you to store large amounts of data. The DSSIZE can grow up to 1 TB for a partition. And the maximum table size increases to 4 PB with up to 256 trillion rows per table. That is a lot of data that can be stored! Think about it this way: if you were to insert 1000 rows per second it would take more than 8000 years to fill the table to capacity!

With RPN table spaces you get the ability to create larger partition sizes. The maximum partition size is now 1 TB (it used to be 256 GB). So if you are reaching the capacity size of existing table space options, RPN table spaces will allow you to store a LOT more data. Of course, this requires an expanded RID, which increases from 5 bytes to 7 bytes. So that will impact the DDL for the mapping table for your online REORG utilities.

But size is not the only issue. RPN table spaces improve availability, too. You can specify DSSIZE at the partition level for RPN table spaces. So each partition can have its own, different DSSIZE specification. Furthermore, the allowable DSSIZE value is no longer dependent on the page size and number of table space partitions. The DSSIZE change can be an immediate change (no online REORG required to take effect) as long as the change does not decrease the DSSIZE value.

As you move your DB2 subsystems to Version 12, consider migrating your larger range-partitioned table spaces to RPN to take advantage of these new capabilities.

The DB2 12 for z/OS Blog Series – Part 6: Transferring Ownership of Database Objects

When a database object is created it is given a qualified two-part name. This applies to tables, indexes, table spaces, distinct types, functions, stored procedures, and triggers. The first part is the schema name (or the qualifier), which is either implicitly or explicitly specified. The default schema is the authorization ID of the owner of the plan or package. The second part is the name of the object. 

But things can get confusing. When an object is created, an authorization ID is assigned as the owner of the object. This may, or may not, be used as the schema qualifier for the object. The object owner implicitly inherits privileges to reference, maintain and grant privileges to the object. 

Changing the owner of a database object used to be a difficult process. But DB2 12 for z/OS delivers a simple method of transferring the ownership of your database objects: the TRANSFER OWNERSHIP SQL statement. 

The primary purpose for this new feature is to make it easier to manage database objects that are owned by an employee who no longer works for your company. You can use TRANSFER OWNERSHIP to simply switch the ownership of the database objects to another employee. The new owner can be an authorization ID or a role. You can issue the statement interactively, or embed it in an application program. 

The TRANSFER OWNERSHIP statement does not change the schema of the transferred database object. 

In order to transfer ownership of a database object, you must either be the owner of the object or have SECADM authority. The basic syntax of the statement is as follows: 

TRANSFER OWNERSHIP OF object-name
 TO {USER authorization-name | 
     SESSION_USER | 
     ROLE role-name} 
 REVOKE PRIVILEGES 

Be careful if a package depends on the current owner's privileges. The dependent package will be invalidated unless the current owner is already explicitly granted those privileges from another source prior to the object ownership transfer. For example, after the ownership of a table is transferred and if a dependent package requires the SELECT privilege on that table by the current owner, the dependent package is invalidated unless the current owner has already been explicitly granted the SELECT privilege for that table before its ownership transfer. 

Here is a quick example transferring the ownership of a specific index to a different user, in this case, JOHNDOE. 

TRANSFER OWNERSHIP OF INDEX TR_P.XHIST02
 TO USER JOHNDOE 
REVOKE PRIVILEGES;