Deprecated Objects and APPLCOMPAT

DBA's are notoriously conservative when it comes to migrating data to new types of objects.  Our theme seems to be "if it ain't broke, don't fix it!"  IBM has decided to encourage those of us on DB2 for z/OS to migrate our data to Universal Table Spaces (UTS) by essentially deprecating non UTS spaces in FL504 of DB2 12.  Remember that we get to control the timing of when the functional changes occur.  Even if the maintenance has been applied, none of these changes take effect until FL504 is activated.  The considerations around activating the function level and setting APPLCOMPAT appropriately center around two areas:

• What to do if we want to move aggressively towards the restrictions
• What to do if we want to keep running without the restrictions.

So what are the restrictions?  IBM is deprecating the following types of objects:

• Synonyms
• Segmented tablespaces
• Classic partitioned tablespaces (i.e., not UTS)
• Hash tables

What does deprecation mean? 

It means that existing examples of these objects can continue to be used, but new ones cannot be created.  Recall that simple tablespaces have been deprecated in the same way since DB2 10; we can use existing simple tablespaces but cannot create new ones.  LOB and XML spaces are unaffected by these changes.

Please note that the results of some DDL will change. 

A space defined with SEGSIZE but neither NUMPARTS nor MAXPARTITIONS will now be a PBG, not a segmented tablespace as it was before.  Something to watch for here is that the CREATE TABLESPACE command will work (creating a PBG), but the second CREATE TABLE will fail, without an obvious reason.  DDL which was used to create a classic partitioned space will now create a Partition By Range (PBR) UTS space; the new DPSEGSZ zparm defines the default SEGSIZE which will be used if SEGSIZE is not specified.

Notice that index controlled partitioning is now deprecated.   We should start planning conversion of such tablespaces, if we have not done so already.

So, how does an aggressive shop start enforcing these rules? 

The ACTIVATE FUNCTION LEVEL (V12R1M504) command will do it, right?  Not so fast!  We can still create these objects, even after activation of FL504, if our package is bound with APPLCOMPAT V12R1M503 or lower, or we have issued the corresponding SET APPLICATION COMPATIBILITY SQL instruction.  We'll return to this point later.  For now, note that we will need to rebind SPUFI, DSNTEP2, DSNREXX, etc. with APPLCOMPAT(V12R1M504) if we want to prevent creation of these object types.  If we use an administrative tool which issues DDL, we will have to REBIND its packages as well.

The problems for most of us will consist of two flavors:

• We need to build a copy of our production non-UTS space for testing or some other function
• We need to make some kind of change to the schema which involves unloading, dropping, recreating, and loading the object. A particularly heinous variety of this problem is where testing has gone just fine on a FL503 system, say, but then our recreation script fails on our production 504 system (actually, it is the APPLCOMPAT on each package used that matters).  This is a good reason to avoid ever letting APPLCOMPAT default.

The good news is that there's a relatively easy workaround, hinted at above.  Simply issue the SET APPLICATION COMPATIBILITY instruction to set it below FL504, and we can create the objects.  If we use a vendor product or code where we don't control the SQL, we can REBIND with APPLCOMPAT(V12R1M503) or lower.

What if we want to exploit another SQL feature at level 504 or higher (like Huffman compression, for example)?  We will need to avoid combining creation of Huffman compressed tablespaces with creation of these deprecated objects in the same jobstep; luckily, this shouldn't be too difficult.  Of course, as time goes on and IBM adds more features which we want to exploit, this will become more of a problem.  Object deprecation is yet another reason to get all our tablespaces converted to UTS.

Synonyms deserve some attention of their own. 

We can replace a synonym with an ALIAS, but we need to keep in mind some significant differences:

• An alias can be defined for an object that does not yet exist, and it persists after the object is dropped. If we use synonyms, we may find ourselves in the position of having to recreate one which was dropped implicitly when the object it references was dropped.
• An alias is defined for all users.
• A user needs the CREATE ALIAS authorization to create an alias.

It is worth mentioning that IBM has talked to the third party vendors and each one has a plan for their affected products.  If you have third party products, check with your vendor.

These additional restrictions are a laudable effort by IBM to encourage migration to UTS.  I hope this article has given you some ideas of possible pitfalls, particularly for those of us who must continue to use the deprecated objects.  The potential problems are easy to handle with a little forethought and planning.