DB2 for z/OS: DDF, Stored Procedures, and SET CURRENT PACKAGESET

Recently, I was talking with people from a company's IT department about modernizing their mainframe application architecture.Among other things, we discussed increasing the company's use of stored procedures for DB2 for z/OS data access. That prompted some questions around the use of the DB2 SQL statement SET CURRENT PACKAGESET in stored procedures called by DRDA requesters (these being calls that would get to DB2 via the distributed data facility, or DDF). Why did this issue come up? Because the organization's DB2 database had been divided into several schemas (sets of tables) along customer lines, with information pertaining to one set of customers being stored in tables in schema A (e.g., in a table called A.ORDERS), and data for another customer set going into schema B tables (such as B.ORDERS), and data for yet another customer set going into schema C tables, etc. This is not an unusual approach in environments characterized by very large amounts of data that can be relatively easily separated according to some identifier such as customer or account number. Such a mode of data organization can deliver some scalability and availability benefits, and if the number of database "instances" (referring to the different schemas, distinguished by high-level qualifier) doesn't get to be too large (I would want it to be in the single digits), the cost in terms of operational complexity is generally not too high. Does the presence of "n" database instances -- same tables, structurally, in each instance, but with different data -- require the coding of "n" versions of each database-accessing program? No. Programs are coded using unqualified table names in SQL statements, and then the packages associated with the programs are bound into "n" collections, one for each database instance, with the appropriate high-level qualifier provided via the QUALIFIER option of the BIND PACKAGE command. At execution time, application programs can then very easily and efficiently navigate between database instances by pointing to an instance's package collection using the SQL statement SET CURRENT PACKAGESET.

Nothing new here -- this kind of thing has been done for years at plenty of sites. The concern raised during the aforementioned meeting had to do with the use of SET CURRENT PACKAGESET in DB2 for z/OS stored procedures called through DDF. One of the DBAs with the company to which I've referred expressed doubt that SET CURRENT PACKAGESET could be used in this situation. The reason for this DBA's skepticism? He was pretty sure that the value used in a SET CURRENT PACKAGESET statement had to be a collection name in a plan's package list (often called the PKLIST). For DDF-using applications, the plan name always defaults to DISTSERV, and that plan doesn't have a package list; therefore (the DBA believed), SET CURRENT PACKAGESET, when issued from a stored procedure called by a DRDA requester, will fail.

This DBA even had what appeared to be solid evidence backing his contention: he created a stored procedure (it happened to be of the native SQL variety -- more on that to come), called ST_PROC_X, through which a SET CURRENT PACKAGESET = 'COLL_A' statement was issued, and when that stored procedure was called through DDF at DB2 location DB2LOCN, the SET CURRENT PACKAGESET failed with a -805 SQL error code (I've of course changed location, collection, and other names from the actual values used at the DBA's site):

DBRM OR PACKAGE NAME DB2LOCN.COLL_A.ST_PROC_X.0123456789ABCDEF NOT FOUND IN PLAN DISTSERV. REASON 02.

There, see? "Not found in plan DISTSERV." That means the program's DBRM wasn't bound directly into the DISTSERV plan, nor was it in a collection in DISTSERV's package list. And of course that must be the case, because a DBRM can't be bound directly into a plan in a DB2 10 (or later) environment (and even before DB2 10 you couldn't bind any DBRM's into plan DISTSERV), and you can't define any package collections for DISTSERV; therefore, SET CURRENT PACKAGESET is doomed to fail for DRDA requesters. Case closed, right?

Umm, no. I will say, however, that the DBA's conclusion was very understandable, given the text that accompanied the -805 SQL error code. This is one of those cases in which the words associated with a DB2 error code are a bit misleading. The error code text suggests that a -805 is always caused by a DBRM not being found in a plan, or a package not being found in a collection named in a plan's PKLIST. In fact, that is one -- not the only -- cause of a -805. If you look up the -805 in the DB2 for z/OS Codes manual, you'll see another reason for the error that's possible when the '02' reason code is indicated:

"The CURRENT PACKAGESET special register was not set correctly by the application."

I figured that was the problem, and might be so because the stored procedure in question was a native SQL procedure. How's that? Well, an external stored procedure's package can be bound into any collection via the COLLID option of the CREATE PROCEDURE statement. For a native SQL procedure, COLLID is not an option for the CREATE PROCEDURE statement. Where, then, does a native SQL procedure's package go? It goes into a collection that has the same name as the native SQL procedure's schema. The native SQL procedure ST_PROC_X referenced above was created with a high-level qualifier of HLQ_X (again, not the real identifier), so it's package was bound into collection HLQ_X. The stored procedure issued SET CURRENT PACKAGESET = 'COLL_A'. That would cause DB2 to look in collection COLL_A for package ST_PROC_X (same name as the stored procedure) to execute subsequent SQL statements issued by the stored procedure. Package ST_PROC_X was not in collection COLL_A, and that's why the -805 was issued, right?

Wrong on my part. The DBA told me that the ST_PROC_X package had, in fact, been bound into each of the database instance-aligned collections. OK, how had that been accomplished? Via BIND PACKAGE with the DEPLOY option, said the DBA.

And, BINGO, that was the problem. See, the DEPLOY option of BIND PACKAGE is typically used to migrate a native SQL procedure from one DB2 environment to another (e.g., from a test to a production environment). To get a native SQL procedure's package into various collections in the same DB2 environment as the package's "root" collection (i.e., the collection that has the same name as the native SQL procedure's schema), one should use BIND PACKAGE with the COPY option, not the DEPLOY option. When BIND PACKAGE was executed with DEPLOY by the DBA with whom I was working, new consistency tokens for the packages were generated by DB2; thus, the search for package ST_PROC_X in collection COLL_A failed at the consistency token level (the fourth-level qualifier of the fully qualified package name: location.collection.package.token). The DBA freed the packages that had been added to the database instance-aligned collections via BIND PACKAGE with DEPLOY, then copied the ST_PROC_X package from its root collection to the database instance-aligned collections using BIND PACKAGE with COPY, then called the stored procedure, and presto: everything worked like a champ.

So, SET CURRENT PACKAGESET is absolutely valid in a stored procedure (native or external) that is called by a DRDA requester. Just make sure that the stored procedure's package was placed in any collection named by SET CURRENT PACKAGESET, and put the stored procedure in that collection (or collections) the right way -- and that means via BIND PACKAGE with COPY when you're replicating a package across several collections within a given DB2 environment.