I have a cobol DB2 program which is to be executed in 2 regions
Each region contains 2 qualifiers like
Region 1:
XY1
XYZ1
Regions 2:
AB1
ABC1
Now say my program has a query like
select table1.col1,table2.col2
from table1,table2
where table1.col1 = table2.col1
The qualifiers for the table1 and table2 used in my query are
Region1
XY1.Table1
XYZ1.Table2
Region2
AB1.Table1
ABC1.Table2
but while binding we can specify only one qualifier
I cannot hard code the qualifier in the program since i have to use
it in 2 different regions
How can i access 2 different qualifiers in the same program
Now, take the situation in which a plan's package list contains multiple collections. Why might this be the case? Two possible reasons are database segmentation and package versioning. By database segmentation, I refer to the practice of dividing at least a portion of the data among sets of otherwise identical tables within a single DB2 for OS/390 subsystem (or a single data sharing group). The division of data could be along geographic lines (for example, REGION1 and REGION2 tables) or by client or groups of client organizations (COMPANY1 and COMPANY2 tables, for example). Some DB2 shops with very large databases (including CheckFree) undertake such segmentation for database manageability and availability reasons. You distinguish tables in the various database segments by way of a segment-related high-level qualifier. For example, sales data would be divided between tables with fully qualified names such as REGION1.SALES, REGION2.SALES, REGION3.SALES, and so on. In this type of scenario, package bind allows you to develop one program that you can use in each database segment, as follows:
First, the program is written using unqualified table names (for example, SELECT TERRITORY FROM SALES). Then the program is bound into multiple collections, one for each database segment (REGION1 collection, REGION2 collection, and so on). For each of these multiple bind operations, you specify the appropriate high-level qualifier by way of the QUALIFIER option of the BIND PACKAGE command. Thus, the package bound into the REGION1 collection will, when executed, access tables in the REGION1 segment of the database.
Given this database and application architecture, accessing the right data means executing the right package; in other words, the package bound into the collection associated with the database segment of interest. Given a plan with a multicollection package list, how does DB2 know where to look for the package when executing an SQL statement? The search process is as follows (assuming that programs are not bound directly into the plan):
DB2 first checks to see whether a special register called CURRENT PACKAGESET (one such register is maintained for each DB2 thread) contains a nonblank value. If it does, DB2 will search for the package in the collection specified (and will find the package, assuming - as is probably true - that each segment-related collection contains the same set of packages, distinguished only by the high-level qualifier specified at bind time). The value of CURRENT PACKAGESET is blank at the beginning of a transaction or batch job and can be updated by way of the SQL statement SET CURRENT PACKAGESET. Thus, if a program needs to access data in the REGION2 database segment, it can do so by issuing the statement SET CURRENT PACKAGESET = 'REGION2'.
If the value of CURRENT PACKAGESET is blank, DB2 will check to see whether the package is already allocated to the thread. This could be the case if, for example, the thread is reused by multiple transactions (an example being a CICS-DB2 protected thread) and the package in question was bound with RELEASE(DEALLOCATE). If the package is already allocated to the thread, DB2 will use that package.
If the value of CURRENT PACKAGESET is blank and the package is not already allocated to the thread, DB2 will search for the package in the collections listed in the plan's package list - searching in the order in which the collections are listed - until the package is found.
Given a situation in which multiple collections appear in a plan's package list and all packages are bound into each collection, some people assume that the first collection listed will serve, in effect, as the default collection. In other words, if the REGION1 collection is listed first, the package from that collection will be selected if the value of CURRENT PACKAGESET is blank when an application program issues a call to DB2. The problem with this assumption is that it does not take into account the possibility that the package might already be allocated to the thread, as I mentioned earlier. If, for example, DB2 needs to find package PROGABC, and if the version of that package bound into the REGION3 collection is already allocated to the program's DB2 thread, that version of the PROGABC package - and not the version in the REGION1 collection - will be used for SQL statement execution if CURRENT PACKAGESET is blank, even though the REGION1 collection is listed first in the plan's package list. If, for performance reasons, you drive thread reuse and bind your most frequently executed programs with RELEASE(DEALLOCATE), you'd better not think of any collection in a multicollection package list as the default. Instead, explicitly direct DB2 to the desired collection by way of SET CURRENT PACKAGESET. Even if all of your packages are bound with RELEASE(COMMIT), you should probably use SET CURRENT PACKAGESET to get where you want to go, in terms of collections. It's simply good programming practice.
Hi UmeySan,
Thanks a lot for such detailed information...
Select territory from sales
Here sales tables can be present in region1,region2 or region3
We can bind it accordingly to the 3 regions and we can set the packageset to any one of the 3 regions as and when required.
But what if I have to access sales in both region1 and region 2 at the same time...? We can make it point either to region1 or region2 only.
Can we access multiple regions...?
Is it possible....?
