Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to use Multiple qualifiers in DB2 program

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rarvins

New User


Joined: 24 Jan 2007
Posts: 59
Location: Texas

PostPosted: Thu Feb 22, 2007 3:14 pm    Post subject: How to use Multiple qualifiers in DB2 program
Reply with quote

Hi,

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

Can somebody pls help me out
Back to top
View user's profile Send private message

UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 755
Location: Germany

PostPosted: Thu Feb 22, 2007 4:39 pm    Post subject:
Reply with quote

Hi !

What's about PackageBinding ???

Regards, UmeySan

...More Information for your example:

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.
Back to top
View user's profile Send private message
rarvins

New User


Joined: 24 Jan 2007
Posts: 59
Location: Texas

PostPosted: Thu Feb 22, 2007 6:10 pm    Post subject: Re: How to use Multiple qualifiers in DB2 program
Reply with quote

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....?
Back to top
View user's profile Send private message
UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 755
Location: Germany

PostPosted: Thu Feb 22, 2007 8:40 pm    Post subject:
Reply with quote

Hi Rarvins !

So for now, I don't really know what you mean. Do you think of something
like a Cross-Region-Communikation ???

Regards, UmeySan
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
No new posts Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts Need Suggestion on COBOL program vickey_dw COBOL Programming 5 Thu Jan 05, 2017 10:55 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us