DB2- Local and Remote Collections under a Plan

Recently in our workshop we faced an issue with switching between two DB2 sub systems. Our data resides in Legacy DB2 sub-system and New DB2 Sub-system. whenever old account comes it should point to Legacy DB2, when new account comes it should point to New DB2 sub system.Untitled

Initially we struggled a lot to understand how this concept works.

Finally we came to know that Plan is fixed and we can switch between collections. Let us see digramatically, how it works. A PLAN can have multiple collections. The collections can be a remote are local.

IBM Reference on Collections and Packages

One important point is there are two ways, we can access remote tables:

  • Aliases can be used point to remote tables (known as “system-directed access”)
  • A local program can issue a CONNECT statement to establish a connection to another DB2 (or non-DB2) location, and execute SQL at the remote location (known as “application-directed access”)

How to add local package list and Remote package list in a collection?

Invoking a package in the other application, We might have our application defined in Production as simply as this:


We can include code from the Payroll application in the plan (support must also be provided in the load library hierarchy):


A call from a Financial program to a Payroll program allows DB2 to load the package associated with the Payroll program successfully as it finds the package in the FIN_PROD_CICS collection.



Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.