Nested Stored Procedures

Answer

DB2® uses the Workload Manager (WLM) to schedule every stored procedure that is invoked or every UDF that is the first UDF of the cursor that is being accessed. Whether the stored procedures are nested or not is not a factor in terms of performance. The cost of using the WLM to schedule the stored procedure is the same whether the stored procedure is the highest level stored procedure in the nesting or the lowest.

So the question is, if I have stored procedure A defined in WLM environment 1 and stored procedure B defined in WLM environment 2, how can I force them both to run in WLM environment 1? On CREATE or ALTER PROCEDURE, the WLM ENVIRONMENT value can be set to (WLMENVB,*). If you specify this for procedure B, it will run in the same environment as procedure A when it is invoked by procedure A, and in WLMENVB when it is invoked directly by an application. However, please be aware that scheduling the stored procedures in the same address space does not offer a significant performance advantage.

You declare the Workload Manager environment in which you need to run a particular stored procedure. DB2 honors that declaration, because it assumes that your program is dependent on certain things in that WLM proc. For instance, your program might be dependent on the STEPLIB concatenation to get the right program loaded into memory. Your program might also be dependent on certain DD cards in the proc that provide access to specific data sets. There is a wide variety of other possible dependencies for a particular WLM proc.