Q and A

Asked and Answered

Q. An article I read gave me the impression that if you want to use one DB2 for z/OS stored procedure program for multiple different schemas in a database, the only feasible way to do this is to use WLM-managed stored procedure execution environments, because you can have several of these, and you would need one for each schema the stored procedure will reference (you would have different stored procedure load modules, one for each schema, and the different load modules would execute in the different WLM-managed environments). You couldn't use the DB2-managed stored procedure address space, because you can have only one such address space.

Do you know of any other way to handle this issue? I read in one of your articles that you can use unqualified SQL in an application program, and bind the one program into different collections corresponding to different database schemas. Can the same be done for stored procedures?

Robert Catterall responds:

You don't have to have multiple different stored procedure address spaces to do what you want to do (as I'll explain momentarily). That said, I strongly encourage you to use WLM-managed environments for your DB2 for z/OS stored procedures. WLM-managed stored procedure address spaces provide better scalability and more flexibility than the DB2-managed stored procedure address space (SPAS), even if you only need one, and not several, WLM-managed stored procedure execution environments. On top of that, in a DB2 for z/OS V8 system, you cannot create new stored procedures that will run in a DB2-managed SPAS.

So, USE WLM-MANAGED EXECUTION ENVIRONMENTS FOR YOUR DB2 FOR Z/OS STORED PROCEDURES, OK? Now, here is how you can have one stored procedure program (one load module), to be executed in one WLM-managed environment, and still have multiple associated stored procedures, one for each database schema. The key, as you've mentioned in your note, is to use unqualified table and/or view names in the SQL statements in the stored procedure program. Then, you have a package collection for each of your schemas, and you bind the package associated with the stored procedure program into the respective collections specifying the appropriate schema name as QUALIFIER for each BIND PACKAGE command. For the respective CREATE PROCEDURE statements, you'd specify the one WLM environment.

For example:

Suppose the stored procedure will be named MANY_SCHEMA_PROC. The name of the external program associated with the stored procedure is SPLDMOD1, and it's a member of a library called PRODLIB. The package name is the same as the program name. You have four schemas (EAST, WEST, NORTH, and SOUTH) and associated collections (EAST_COLL, WEST_COLL, NORTH_COLL, and SOUTH_COLL).

For the SQL statements in the program SPLDMOD1, you use unqualified table names (for example, SELECT... FROM TABLE_A vs. SELECT... FROM EAST.TABLE_A). You bind the package SPLDMOD1 four times, to get the package into each of the four collections associated with the respective schemas. For each BIND PACKAGE operation, you use the QUALIFIER option to specify the appropriate schema name to be used in qualifying the unqualified table names in the SQL statements in program SPLDMOD1. So, the BIND PACKAGE command used to get the package into collection EAST_COLL would include the following specifications:

BIND PACKAGE (COLL_EAST)
MEMBER (SPLDMOD1)
QUALIFIER (EAST)

You would issue four CREATE PROCEDURE statements, one for each schema. The statement used to define the EAST.MANY_SCHEMA_PROC stored procedure would include the following specifications:

CREATE PROCEDURE EAST.MANY_SCHEMA_PROC
EXTERNAL NAME SPLDMOD1
WLM ENVIRONMENT WLMENV1
COLLID EAST_COLL

When a program calls EAST.MANY_SCHEMA_PROC (for example), DB2 finds the corresponding row in the SYSROUTINES catalog table. It tells WLM to execute program SPLDMOD1 in application environment WLMENV1. Because the load library PRODLIB is in a STEPLIB in the JCL for WLM environment WLMENV1 (or in a JOBLIB or in the link list), the module SPLDMOD1 is found and executed. The package from EAST_COLL will be used (as specified in the CREATE PROCEDURE statement), and so tables qualified by EAST will be accessed because the qualifier EAST is used to qualify unqualified SQL statements in the program (also specified in the CREATE PROCEDURE statement).

See a complete archive of reader/author Q&As .