Q and A

Asked and Answered

I have a question about your article " Programs and Packages, Plans and Collections " in Quarter 1, 2001 issue of DB2 Magazine . My question relates to programs that may use different collection ids and schemas. I'm using DB2 for OS/390 version 6, and the language is COBOL.

I've seen quite a few situations where within the same program, different collection IDs or schemas are used to qualify completely different tables. Take this example, in which tablea and tableb are different tables.

SQL Stmt #1: 
Select Cust
From Collid1.tablea
Where SSN = :HV-SSN.
SQL Stmt #2 in the same program:
Select Store_Num
From Collid2.tableb
Where Store_id = :HV-Store-id

In these situations I've noticed that the collid has been hardcoded. This can't be good. How does one control which collid to use? I know at run time I can (and do) use a packageset to control which schema I connect to. What should I be using for Bind parameters if I don't want to hardcode the collection IDs?

I'm confused as to how to control which schema I want to use when I am using more than one in a program.

Robert Catterall responds:

Suppose that your production DB2 for OS/390 database is instanced by client ID. That is to say, you have one set of tables for one group of clients, and another set of tables for another group of clients. The high-level qualifiers used for the two sets of tables are GROUP1 and GROUP2 , respectively. You can set up two collections, also called GROUP1 and GROUP2 . All programs use unqualified table names in SQL statements (that is to say, you have SELECT COL1 FROM EMPLOYEE instead of having SELECT COL1 FROM GROUP1.EMPLOYEE ). Every program is bound into each of the two collections. When binding program PROGXYZ into collection GROUP1 , you specify that GROUP1 is to be used as the high-level qualifier for unqualified table names (this is accomplished by specifying QUALIFIER(GROUP1) on the BIND PACKAGE command). When binding the same program into the GROUP2 collection, you specify QUALIFIER(GROUP2) on the BIND PACKAGE command.

At execution time, you use SET CURRENT PACKAGESET to determine the table to be accessed. Suppose, for example, that you need to access data for an employee of a client in the GROUP1 set of clients. In your program, you'd have a SET CURRENT PACKAGESET = :hvar , and you'd place GROUP1 in the host variable. Then, you'd have your select-statement, for example, SELECT NAME, ADDRESS FROM EMPLOYEE WHERE... Because you have pointed DB2 to the GROUP1 collection, DB2 will look in that collection for the package corresponding to the program. It will find the package because all programs are bound into all collections. Because QUALIFIER(GROUP1) was specified for that package at the time it was bound into the GROUP1 collection, the above SQL statement will actually be executed as SELECT NAME, ADDRESS FROM GROUP1.EMPLOYEE WHERE... Thus, picking the right table is as simple as picking the right collection.

See a complete archive of reader/author Q&As .

Back to Simplifying Warehouse Management by Eric Michiels