Hi all,

there has already been a thread regarding this subject some years ago, but there was no solution for it.

What I need to know is the following:

If you don't set the CURRENT PACKAGESET explicitely in your program, DB2 will search the PLAN for a matching PACKAGE (I won't explain this in detail here, I also know that there could be differences e.g. in a CICS module, with multiple possible programs using the same DB2 thread).

So hence DB2 has found the information about the desired qualifier in the PACKAGE from the PACKAGELIST in the PLAN used with your program...
is there any way to determin the name, collection and qualifier of the PACKAGE which is currently in use with this program?

The fact is that the special register CURRENT PACKAGESET is still empty in this case, because DB2 just takes the necessary info from the package.

But can DB2 populate this (known) info also to the current program?

I'm using DB2 from a COBOL module in an MVS environment and would like to convince all doubting Thomases, that to set the current packageset is not really necessary. So I want to DISPLAY the collection/name/qualifier in the joblog.

Is this possible???

Here's an example from real life:

- the PLAN is called MyTestPlan
- the PROGRAM is called MyProg
- The PACKAGELIST in the plan just consists of the package MMM.MyProg
(MMM is used as collection here, because this is my personal test version of MyProg, and I don't want to overwrite the package of the official XXX.MyProg package in the same environment, running from a different LOADLIB, which would lead to a -805 SQLCODE there)
- The PACKAGE MMM.MyProg is bound with qualifier QUAL1
- There are following two DB2 tables:
QUAL1.MyTab
QUAL2.MyTab

So running the test version of program MyProg with the above Plan MyTestPlan will access only DB2 tables with qualifier QUAL1, e.g. the above table QUAL1.MyTab, but NOT the table QUAL2.MyTab.

This is fact.

But how could I convince my boss that this constellation would NEVER EVER access the QUAL2.MyTab?

So far I couldn't find any special register (no, neither SCHEMA nor SQLID would give the needed info), that could give me the info

- Collection: MMM
- Packagename: MyProg
- Qualifier: QUAL1

but I just can't believe that DB2 is practicing information hiding of info that is definitely known inside DB2.

Please don't suggest "just go & set the current packageset explicitely"... that's not what I'm searching for

Thanks a lot in advance,
MMM

Hmmm.... no solution out there?

I can't believe that DB2 is really practicing "info-hiding", there must be a way to find this out?!

I really hope to get help from you experts out there!

Cheers,
MMM

Wow, 2 weeks later still no reply

Does this mean THERE IS NO SOLUTION, or does it just mean that nobody knows it?

I mean, it's "only" a technical question, no logical problem...

Uhm?

that does it. as long as you do not qualify the table in the FROM clause in your program,
you will never beable to access a table in schema QUAL2, unless you run from a plan that has the schema/qualifier QUAL2.

now to convince you boss? you need to start from scratch and teach him db2 concepts.

each db2 release provides a z/OS db2 basics pdf
this is the z/OS Solutions Info center
this is an expanded url from the above link
This is the intro to DB2 Vsn10 - expanded from above
each db2 version has its own set of manuals.

This is an excellent article (though dated) from Bonnie Parker
( this is a list of all Bonnie Parker articles )

This is an excellent Redbook DB2 9 for z/OS: Packages Revisited

and this is an article in IBM Systems Magazine which mentions the above Redbook and talks about converting to packages.

Nothing worse than working for a know-it-all who does not know anything.

Well, I surely know that this does it. But this doesn't really answer my question.

Has the program itself a chance to display this "knowledge"? To have theoretical knowledge sometimes doesn't satisfy, that's like pretending "If I enter X, only X will be processed, because it's only X, not Y". Sometimes it's nice if the program can tell "Yes, X was entered".

==> Can DB2 share this knowledge during runtime, or does it "just use" it?

Thanks again,
MMM

Maybe I'm missing something, but if you know the package name, you could look up the qualifier in SYSIBM.SYSPACKAGE. And since each program or PLAN may use multiple packages, you have to implement a way to determine the package in your code yourself.

Hi Knut,

well, that is exactly the problem. Of course - from "outside the program" - I do know the name of the package, just from my own personal DB2 know-how.

But does the program itself know which package is currently used? It surely DOES use one of the packages from the packagelist in the PLAN, but just the existence of a package in SYSIBM.SYSPACKAGE doesn't tell me which of these packages is currently used (yes, still I do know that the first fully-quailified one is used, but the program itself still doesn't).

The only way (which sounds awful to me!) would be to implement the same logic in the program, how DB2 is searching for the package, i.e. checking the plan in order of the packagelist, get the info from SYSPACKAGE, etc.
But the funny thing is, that from "inside the program" I can't even determine the currently used plan (can I?)! I can only determine that from "outside", by checking e.g. the JCL.

That's like I'm standing and walking on the street (just because somebody put me there) and I am not able to tell anybody where I stand and walk.

I just want the program to immediately display the currently used package, just by querying the "top secret" DB2 register knowing this.

What exactly do you mean with "currently used"? You can have a program that calls 2 different functions, which are implemented in 2 different modules with embedded SQL. Each module may have its own package associated in DB2. So there is no single current package. That's why I have a basic problem with your question.

I guess you would have to implement in each module separate logic that checks the module's package. One approach to do that would be to tie the package name with the module itself - if you know the build process. Another approach would be to look at the precompiled source code to see what the DB2 precompiler is adding. You should find some variables that contain the identifier/level for the package. (Maybe there is an official way to get this information during compile time, but I do not know it myself.)

OK, in this example my program doesn't use any submodules, it's just the program itself, so only one DBRM (but many packages bound with this DBRM).

Again: it's not me that you have to convince that by having a simple look at the plan and at SYSIBM.SYSPACKAGE it's easy to determine the needed info.

BUT: the requirement is that the program itself can display this info, not me.

REASON: For just one LOAD-module you could bind different plans, with differently bound packages (i.e. different qualifiers, different collections):

AA.MyProg (e.g. bound with qualifier DB1)
BB.MyProg (e.g. bound with qualifier DB2)
CC.MyProg (e.g. bound with qualifier DB3)
DD.MyProg (e.g. bound with qualifier DB4)
EE.MyProg (e.g. bound with qualifier DB5)

I could now use five different plans, one with packagelist containing AA.MyProg, one with packagelist BB.MyProg, etc.
Maybe the plans could also contain all 5 packages, in different order (which of course wouldn't make sense, it's just to show the need of my requirement as an example).

Running my program with these 5 different plans, I would want my program to display which of these packages it is really using, e.g. telling me "I have done updates on table DB3.MyTab", while the other run with the other plan should tell me "I have done updates on table DB2.MayTab".

This is especially important if I run my prog in different test environments, to be able to tell FOR SURE after my test which table I had accessed with my program.

Of course I do not want to implement DB2's logic to scan the packagelist and so on... I would rather assume that once DB2 has determined the package that it's "going to use", it is also storing this info somewhere "secretely".

And this is just the info I would want to get from DB2, to be able to display it simply.

Now clearer?

Thanks,
Michael

I'm not too familiar with DB2 on z/OS, but isn't the information you are looking for appear in the CURRENT SCHEMA special register?

Nope, that only displays my own USERID, no info about plan or package.

Thanks anyway...

It is not supposed to display "info about plan or package". I thought you were looking for the object qualifier in effect.

My request in my first post was as follows:

- Collection: MMM
- Packagename: MyProg
- Qualifier: QUAL1

But if you know a way to display "the object qualifier in effect" would make it, of course, too! That's even the most essential info of all these three!

So... is there a way to display "the object qualifier in effect"?

I'm really curious now and looking desperately forward to your answer!

Thanks in advance,
Michael

That would be CURRENT SCHEMA

No sorry, I have already answered this assumption after your first post, 3 or 4 posts above.... the CURRENT SCHEMA does NOT display the "qualifier in effect", it only displays my own USERID.

Sad but true...