DB2 Package Issue - Cross Collection package use

In this post I am trying to explain a wee complex scenario that we had while we were trying to fix an production application issue. This is not at all a good read because I am writing this only for the sake of documenting the issue and the solution we choose so that it can remind me later.

Restricted

1. There are defined DB2 Prefixes, lets call them AB01, CD01 and a DB2 package with prefix AB01 can access the tables under same prefix. So to access a CD01PROD.TABLE1 the package must have a prefix of CD01PROD. Another package say AB01PROD.NAB01 cannot access the table. 
2. A package can only be added to one single collection. So the package NCD01 cannot coexists in both CD01PROD and AB01PROD.

Allowed

1. The DB2 Plans are allowed to access packages in multiple collections mentioned in its PKLIST.

The Issue

We had an existing sub program called NCD01 which was accessing a table CD01PROD.TABLE1 and will confirm if a particular policy is of a special type or not. We had another sub program NAB02 which need to do the same check that NCD01 was doing additional to its own logic. So instead of re-adding the logic from from NCD01 into NAB02 we called NCD01 from NAB02 which to reuse the existing logic. So the situation is a program which is in collection AB01PROD need access to a package in CD01PROD collection. Due to restriction no-2 we cannot have the package of NCD01 in AB01PROD collection.

This is no issue for the main program in AB01PROD because we had added CD01PROD to the PKLIST of the main program and that enables the main program in AB01PROD to use a package in the CD01PROD collection directly or via another sub program.

The issue was this sub program NAB02 was also being called from a COBOL-DB2 Stroed Procedure NABSP01. Now any COBOL DB2 Stored Procedure runs as a sub program under the DB2 plan DSNTSERV

Solution

We had to dynamically change the package path using SET CURRENT PACKAGE PATH command of DB2. So in NAB01 before calling NCD01 we set the package path to CD01PROD. Then call NCD01 and reset the package back to what it was.