PLAN and Package.

[Shahid]

Hi,

When we had a concept of PLAN in DB2 already in place, why a PACKAGE was needed? This always confuses me can someone please explain in a layman language. And why we need a PLAN to start with?

Thank you

[Anuj Dhawan]

You put up an interesting question. Trust me, I've compiled the answer multiple times and then deleted as I was not satisfied. I'll try again with a new answer, however, this time I'll post it in and see if that works; it might be a long story though!

To answer this

nd why we need a PLAN to start with?

we'd need to go back in to the History. And to be technically correct we'd need to talk about DB2 release before V2R3.

Other thing which we need to consider is that DB2 is a Database and as such it does not talk with the language in which you code, to get the data from it. For this discussion let's assume our language of choice is COBOL. So to say, COBOL, can not directly talk to the DB2 and it needs an interface which is provided in the from of SQL. However, this necessitated to "prepare" the program first before you can execute it. COBOL compiler, for example, does not understand SQL statements - so in the process to 'prepare' program it was need to segregate the SQL part of the program - a programmer can use pre-processor or co-processor to do that and the output of it would be DBRM.

This DBRM is input to the BIND process - using BIND you can get "package" or you can get "plan". Understand BIND as what compile-link-edit does to a source code from COBOL. You know, 'object module' is not executable so is DBRM and you need to BIND it - initially it was called PLAN and that's why you needed PLAN.

I'll come to your other question after a while, Thanks for the patience...

[Shahid]

Thanks Anuj - this is a great help and descriptive answer.

I'll wait for the other answer of yours...

[Anuj Dhawan]

When we had a concept of PLAN in DB2 already in place, why a PACKAGE was needed?

At the time of BIND, DB2 authorization and SQL syntax are checked and access path alternatives are also weighed to choose the least-cost path. Also, run-time instructions are created for the chosen path - so quite a good amount of work is done. Now if the PLAN contains one member, let's call it ForPgm1, this process is (or can be safly assumed that it should be) quick. However, in practical world - you might have 5, 10, 20, 50 or 500 DBRMs in the memberlist and then the BIND might take much more time than expected.

And another question which usually pops up is - what if the PLAN, which has got more than a Member and so took more time to BIND, contains 200 members. And one of the programs,Pgm1, changes? As the source code is changed, the program must be pre-compiled. And pre-compile will change the timestamp and thus will create a new DBRM. With old method, that new DBRM must be bound into the PLAN. When the PLAN is bound, all 200 DBRMs and not just the one modified, will be rebound. Well, even though 199 of the 200 programs haven't changed.

To solve such problems, concept of PACKAGE was introduced, wherein one PACKAGE is enough to rebound and not the rest of the PACKAGEs. Well there are some more considerations about it, however, I'll limit the discussion to these two aspects - and in the process, I believe, it should help you to get your answer.

[Shahid]

Sorry for a late reply Anuj - but I really enjoy reading your reply. Thanks for your time.

[Anuj Dhawan]

You're welcome! Hope we had been helpful.