Db2 for z/OS: Using the Profile Tables to Direct DDF Applications to Particular Package Collections

I've posted several entries to this blog on the topic of high-performance DBATs (for example, one from a few years ago covered a MAXDBAT implication related to high-performance DBAT usage). You may well be aware that a "regular" DBAT (a DBAT being a DDF thread) becomes a high-performance DBAT when a package bound with RELEASE(DEALLOCATE) is allocated to the DBAT for execution. How do you get a RELEASE(DEALLOCATE) package allocated to a DBAT? Well, for a DDF-using application that calls Db2 stored procedures, it's easy: bind the packages of frequently-executed stored procedures with RELEASE(DEALLOCATE) and, boom - you get high-performance DBATs when those stored procedures are called. The same goes for packages associated with DDF-using applications that issue static SQL statements from the client side (for example, Java programs that use SQLJ instead of JDBC): bind those packages (the ones that are executed frequently) with RELEASE(DEALLOCATE) and you get high-performance DBATs when the corresponding programs are executed.

Here's the thing, though: there are plenty of DDF-using applications that don't involve execution of static SQL in any form: not in stored procedures, not issued from client-side programs. Can these applications use high-performance DBATs? Sure, they can. Those applications use packages. Which packages? The IBM Data Server Driver packages, of course (these are also referred to as Db2 Connect packages - the Data Server Driver is the more modern version of the Db2 client software). The Data Server Driver packages are bound, by default, into a collection called NULLID. Could they be bound into NULLID with RELEASE(DEALLOCATE)? Yes, but that would NOT be a good idea. Why? Because that would cause all DDF work, by default, to run by way of high-performance DBATs. The optimal set-up is to limit high-performance DBAT usage to your higher-volume DDF-using applications (especially those characterized by transactions that have a low average in-Db2 CPU time - maybe just a few milliseconds). How could that be done, if the NULLID packages are bound with RELEASE(COMMIT)? Easy: you direct the higher-volume DDF applications to a collection, other than NULLID, into which the IBM Data Server Driver packages were bound with RELEASE(DEALLOCATE). And how is that accomplished? Well, for a while the aim was achieved by changing a client-side data source property to point to the alternate Data Server Driver package collection (the one associated with RELEASE(DEALLOCATE) packages) instead of the default NULLID collection. Actually getting the client-side change done? There's the rub: you had to rely on an application server administrator or a client-side programmer to do the deed, and those people were already busy and your change request might not be acted upon - or at least, might not be acted upon in the near future. Oh, for a way to do that Data Server Driver package collection redirect from the Db2 side!

In fact, you can direct a DDF-using application to a collection other than NULLID by way of a  Db2-side action, and that Db2-side capability is the crux of this blog entry.

The solution: you cause the value of a special register to be set automatically for a given DDF application. The special register of interest here is CURRENT PACKAGE PATH, and the means of accomplishing, on the Db2 for z/OS server side, the automatic setting of the special register for a particular DDF-using application is a Db2 profile and an associated profile attribute. I'm talking about the Db2 profile tables, SYSIBM.DSN_PROFILE_TABLE and SYSIBM.PROFILE_ATTRIBUTES. First, the profile: by way of a row inserted into SYSIBM.DSN_PROFILE_TABLE, you identify the DDF-using application for which you want high-performance DBATs to be used. That could be done in several ways, such as through the authorization ID the application uses in connecting to the Db2 for z/OS system. Alternatively, you could identify the application using the IP address (or addresses) of the server(s) on which the client application runs. There are other application identifier options, as well - check them out in the Db2 for z/OS Knowledge Center on the Web. Note that wild cards can be used with DSN_PROFILE_TABLE application-identifier values, so that one profile could cover a set of application server IP addresses, or a set of application-utilized authorization IDs that begin with (for example) the characters CRM (so, the value CRM* in the AUTHID column of DSN_PROFILE_TABLE would apply to authorization IDs CRM01, CRM02, CRM03, etc.).

With the profile row entered into DSN_PROFILE_TABLE, you're ready to set up the attribute that will cause the application of interest to use the IBM Data Server Driver (or Db2 Connect) packages in collection HIPRFCOLL (if that's what you name the collection with RELEASE(DEALLOCATE) packages) instead of those in the NULLID collection. To do that, you insert a row into SYSIBM.DSN_PROFILE_ATTRIBUTES with a value in the PROFILEID column that matches the PROFILEID value in the DSN_PROFILE_TABLE row for the application with which you're working. In that DSN_PROFILE_ATTRIBUTES row, place the value SPECIAL_REGISTER in the KEYWORDS column, and set the value of the ATTRIBUTES1 column to this character sting (again using HIPRFCOLL as the name of the collection into which Data Server Driver packages were bound with RELEASE(DEALLOCATE)):

SET CURRENT PACKAGE PATH = HIPRFCOLL

And you're done: the application associated with the profile will use the IBM Data Server Driver (or Db2 Connect) packages in the HIPRFCOLL collection, and so will utilize high-performance DBATs because the packages in HIPRFCOLL are bound with RELEASE(DEALLOCATE). You can read more about setting special registers via profiles in the Db2 for z/OS Knowledge Center.

Now, I've focused on alternate collections (alternate relative to NULLID, that is) as a means of getting high-performance DBAT functionality for DDF applications that don't otherwise use packages because there is a lot of interest in high-performance DBATs these days. Think, though, about other ways in which NULLID-alternative collections could be useful in your DDF application environment. What if you want certain DDF applications to run with ARCHIVESENSITIVE(NO) behavior, instead of the default ARCHIVESENSITIVE(YES)? What if you want certain DDF applications to run with an isolation level other than the default CURSOR STABILITY? What if you want to use statement concentration (i.e., the automatic parameterization by Db2 of queries that contain literal values in predicates) for certain DDF applications (starting with Db2 12, that can be "turned on" by way of the package bind option CONCENTRATESTMT)? By using profiles to direct DDF applications to Data Server Driver (or Db2 Connect) collections holding packages bound with the desired options and specifications, you can get the application behavior you want, without placing on client-side folks (application server administrators or application developers) the burden of using particular data source properties or coding SET statements for various Db2 special registers (and keep in mind that we're talking here about DDF applications that do not rely completely on Db2 stored procedures, as stored procedures provide you with a great deal of server-side control over application behavior, including package bind specifications).

For a long time, we've been accustomed to NULLID being the one and only collection for the IBM Data Server Driver / Db2 Connect packages, but with many DDF-using applications being of the type that use only (or at least primarily) those packages, and with a growing desire and need to establish behaviors for those applications that can be most readily implemented through Db2-side direction of the applications to collections with packages bound with particular option specifications, it will become more and more commonplace for Db2-using organizations to have multiple collections for the Data Server Driver / Db2 Connect packages, with direction of applications to collections accomplished via Db2 profiles as described above. Remember: in all likelihood it would be best for the packages in your NULLID collection to be bound with standard default specifications, so that applications for which specialized functionality is desired can be selectively directed to the Data Server Driver / Db2 Connect collections containing the packages bound with the options and specifications that can enable the desired functionality and behavior. Using the Db2 profile tables to set the CURRENT PACKAGE PATH special register is a great way to implement server-side direction of DDF applications to Data Server Driver / Db2 Connect package collections. Give this some thought and give it a try if you think it would be useful in your Db2 for z/OS environment.