Is it a PLAN? Is it a PACKAGE? NO, It's a CONFUSION !!!

No matter how long programmers have worked with DB2 for z/OS , they still have the confusion on difference between a plan and a package and what the heck is that collection anyway.

In this post i will be specific on the PLAN/PACKAGE difference and the things related to it (Such as COLLECTION and PKLIST). If you want to know from basic and detailing of what is PLAN/PACKAGE and how it get formed and why we need it then ,THIS is the great place to start up with,rather i would recommend you to read it before going further with this post.

OK so here it goes.

The PLAN/PACKAGE are nothing but the container which contains the logic of how your SQL statement in the program should get executed.[Remember when we write any query in SQL, we say what we want and NOT how it is to be done (We don't care how DB2 internally does it)].So PLAN/PACKAGE contains the LOGIC how it is to be done.Then this PLAN/PACKAGE is used with load module (For COBOL) to run your application program smoothly.

Now why two things?? PLAN and  PACKAGE , if both are the same (Contains the LOGIC for executing your SQL) and what exactly is the difference between the two.


Binding DBRM to a PLAN :

  • The PLAN contains the (Logic of) one or DBRM OR one or more PACKAGES OR the combination of both.

  • The PLAN is executable.When i say executable it means ,it can be executed with COBOL load module to run your application program.

  • Now let's consider there are 10 programs with names A TO J. So there will be 10 DBRM ( 1 DBRM for each program). I bind all 10 DBRM's to a PLAN (with name 'PLANA').Now let's say i got a requirement to change one of the Program ( let's say Program 'B') then i NOT ONLY need to bind this DBRM again to plan PLANA but all remaining 9 DBRM too (even though they didn't went under any change) and that is the biggest disadvantage of a binding a DBRM to a PLAN.NOT only this, if in my system there are various other PLAN's which uses this DBRM (The one which corresponds to Program 'B') then all those PLAN'S need to rebind along with all the other existing DBRM'S they may be having.

  • There are various parameters called as Bind parameter are available, when you bind your DBRM to a PLAN. These Bind parameters helps in deciding various important aspects for you program such as when to acquire a lock for the particular resource in your program,when to release the lock,what qualifier to use for the objects refered in your application program,the owner of plan etc. So when you bind your all DBRM's (10 DBRM in this example) to PLAN you have only one set of combination of these bind parameters for all 10 DBRM (For example you can not set different isolation level for different Program/DBRM , it has to be same)

Binding DBRM to a PACKAGE:

  • The PACKAGE contains the (Logic of) ONLY one DBRM.It's a single bound DBRM.
  • The PACKAGE is not executable that means it can not be use with COBOL load module to run your Application program.In order to make your PACKAGE to be executed it has to bonded to PLAN again.So you bind your DBRM to PACKAGE and then in turn this PACKAGE to PLAN.I know what you must be thinking now,why to go this way,  isn't it better to bind DBRM to straight away to PLAN.NO IT IS NOT, i will explain you why it is so.When i say you have to bind the DBRM to PACKAGE and then this PACKAGE to PLAN again,you need not to do it for ever package. What i mean is, let's say i have 50 PACKAGES to bind to a PLAN no need to BIND it 50 times instead just bind a single package to PLAN using PKLIST(Package List) that's it ,you are done and remaining 49 PACKAGES will be automatically added to your plan (For this all your 49 Packages need to be in same PKLIST what we have bonded with PLAN earlier)

  • Let's take the same example what we use above for PLAN. I have 10 programs A TO J with 10 DBRM. Now in case of PACKAGE , I will bind each DBRM to one PACKAGE so i will have 10 Packages then include all 10 PACKAGES to single PKLIST (Package List) bind the Package list to a PLAN.Now if one of my Program changes , then i have to rebind only the DBRM corresponding to the Program changing and NOT the remaining DBRM'S/PROGRAM (Which didn't went under the change)

  • Regarding Bind parameter, since i can bind 10 DBRM to 10 different PACKAGES, I can have various combination of Bind parameters for all PACKAGES.(For example Isolation level of CS for 1 PACKAGE and RR to other etc.)

Having said that, you will wonder,you have only the advantages of binding DBRM to PACKAGE and don't have any reason to go for binding DBRM to a PLAN.

That is the reason , IBM is going to remove this facility of binding DBRM to PLAN in DB2 V9 Onwards
For more detail on the same refer HERE.

JQEUME5S4HKU

47 comments:

gaurav kumar said...

very good chunk of information. which clears the basics.

thanks a lot for such type of post

Nitin Gandhi said...

Hi gaurav Kumar,

I am glad, it helped you to understand and clear your doubts on the topic. Do read the other posts.

Unknown said...

Very nice explanation Nitin...Thanks much!

Nitin Gandhi said...

you are most welcome chinni.

Karthik said...

Hi Nitin..Thanks for ur explanation...But i have two doubts.

1. Here are u referring Collection Id as PKLIST?. IF it is different that what is PKLIST.

2. You said first bind package to plan using PKLIST and then bind package list to Plan.
But in my bind package job i am only binding to package. Who will then bind to PLAN?

Please throw some light on this...

Thanks
KARTHIK

Shailesh Singh said...

Hi Nitin,

That's a great effort. However I still have same doubts which Karthik mentioned above.

In other words, when we bind a Package, we just specify the Collection ID along with few other parameters ( like owner, isolation level, Action etc).
And then once this step is done, we directly go to our Run JCL and give the PLAN name. My question is how and when this PLAN name gets bind to package (thought we have not specified anything related to plan in our bind card).

2)Are all that package names, collection names and finally Plan name defined by DBA while creating the table definition at very first time and hence as a user, we are only allowed to use the specified collections and packages so that it can internally bind to PLAN automatically?

Nitin Gandhi said...

@karthik,shailesh..

Answering to your 1st question..

In Pklist we mention the list of packages bound in various different collection id's.

For ex: PKlist(A.*,B.*)

So here A and B are collection's and we are including all the packages which are bound to collection A and B.

2> when i say bind first to package and then package to PLAN.It's need to be done at start and then every addition of package to plan will automatically get binded.

For the existing plan , most probably DBA takes care of this initial Bind and if you are creating test plan ,then you have to take care of that.

Hope it clears your doubt.

Anonymous said...

Content is good. However there are too many grammtical mistakes which makes it hard to understand what Authoer is trying to state.

Manish

Anonymous said...

Hi,
First of all thank you for sharing this information. you have given lot of information still i have one doubt and i am serching a lot for the ans.
My question is you said every addition of package to plan will automatically binded.

In My Bind package card I use below syntax
BIND PACKAGE (SXXXXX)
MEMBER(TESTPGM)
and i do not mention anywhare plan information how this achieve internally first it bind to package then bind to plan. i am waiting for your answer.

Anonymous said...

Hi Nitin,

Your explanations are very Simple and easily understandable.. thank you for that

Any how i have a doubt here... Do we need to rebind the DBRM even if we have non-DB2 changes in the program

Thank you

Raj

Bhargava said...

Thanks a lot!!

Nitin Gandhi said...

Do we need to rebind the DBRM even if we have non-DB2 changes in the program

Yes Raj we do have to bind the plan again.Please read the post once again.It is mentioned in the post.

Nitin Gandhi said...

@Anonymous : i recommend you to read the post once again carefully.I am sure you will find the answer of your question.

praveen kumar potnuru said...

tanq nitin :)

Unknown said...

superb post...... now i am very much clear with plan and package.

Saif said...

Awesome explanation !

Unknown said...
This comment has been removed by the author.
AbhiAbhi said...

Hi Nitin,
I am new to COBOL DB2.Your JCL example shows binding DBRM to PLAN.Please give example and explain JCL for binding DBRM to package and then to plan.Also let us know if package name is given by dba or developer.

Bia Brito (IBM) said...

Tks a lot for your writing, it was clear and sharp.

Anonymous said...

hey there and thank you for your information – I have certainly picked up anything new from right
here. I did however expertise some technical issues using
this website, since I experienced to reload the web site many times previous to I could get it to load
correctly. I had been wondering if your web hosting is OK?
Not that I am complaining, but sluggish loading instances times will
very frequently affect your placement in google and can damage your high quality score
if ads and marketing with Adwords. Anyway I am adding this RSS to my email and could look out for a lot more of your respective exciting content.
Ensure that you update this again soon.

my page: weight loss

Anonymous said...

Wonderful article! This is the type of information that are meant to be shared across the
web. Disgrace on Google for not positioning this submit higher!
Come on over and seek advice from my site . Thank you =)

Feel free to surf to my web page: anctvvideos.com

k.shiva said...

Thank you Mr.Nitin Ganghi. Its really helpful to easy understand. You are doing a good job.Thank you very much.

Anonymous said...

Admiring the dedication you put into your blog and detailed information you provide.
It's great to come across a blog every once in a while that isn't the same old
rehashed information. Wonderful read! I've bookmarked your site and I'm including your RSS feeds to my Google account.


My web page: how to workout at home

Anonymous said...

This is excellent site. It quenches the thirst of many DB2 programmers. You explain the concepts very clearly. unless and until we understand we cannot explain properly to others.

Thank you,
Babu

Anonymous said...

Wгite mοгe, thаts all I hаve to saу.

Lіtеrаlly, іt ѕeems as though
you rеlied on the vidеo to mаκe youг pοіnt.
Үou οbviouѕlу knоw whаt
yourе talking about, ωhy throω аωay уour
intеllіgenсe on juѕt postіng vіdeos tο your blog whеn you
сould bе giving uѕ ѕomething іnfοrmаtive tο
read?

Feel free to surf to my ωeb blog - facebook cuenta gratis

arjun09 said...

I asked the same ques to many of them but this one cleared my query... Thanks a lot friend...

Anonymous said...

Hi fгiends, good articlе аnԁ fаstidіous argumеnts commеnted at this place, I am genuіnely enjoying by
theѕe.

Have a looκ at my blog post: Abrir Cuenta Facebook

Anonymous said...

If you dеsire to obtаin a good
deal from thiѕ post then уou haѵe to apply these techniques to your won weblog.


Stοp by my ѕite crear facebook gratis

Anonymous said...

Hоwdy! I'm at work browsing your blog from my new iphone! Just wanted to say I love reading your blog and look forward to all your posts! Keep up the excellent work!

my web page - http://diariorc.com/archivorc/hemeroteca/2533-tomar-caudales-a-prestamo.html

Anonymous said...

Gгeetings! I κnow this is kind of off topic but
I was wondering which blog platfοrm aгe you using for this wеbsite?
Ι'm getting sick and tired of Wordpress because I've had pгoblems with hackеrs and
I'm looking at options for another platform. I would be great if you could point me in the direction of a good platform.

My web site - abrir cuenta facebook

Anonymous said...

Evеrуthіng is verу open wіth a pгecіse desсrіptіon of the iѕsuеѕ.
It waѕ really informatiνe. Your ѕitе іs
very helpful. Thanκs for shaгing!


Аlѕo visit my wеb blog ... crear facebook

Anonymous said...

Excellent Stuff!!!

Anonymous said...

For newest news you have to pay a quick visit world wide web and on world-wide-web I found this web site as
a finest web site for most up-to-date updates.

Here is my homepage; dragon city cheat engine

Anonymous said...

What's up, constantly i used to check webpage posts here early in the break of day, since i like to find out more and more.

Visit my web-site :: Code Psn Gratuit :: dailymotion.Com ::

Bharani said...

Hey thanks alot.. now i clearly understood the difference

Jijo said...

http://www.theweeklypay.com/index.php?share=12257

Unknown said...

Good !!!!

Nitin D. Yelbhar. said...

Thanks Nitin, This is explained in very very simple way!!! Thanks.

Best Flight Deals said...

Thank you so much to provide the most useful topic. This is great travel explained. I love this field.
Discount Airline Tickets

Anonymous said...

Thanks a lot. It helped in clearing the concepts.

Unknown said...

Hi Sir its an very wonderful and easy Explanation about Plan and Package, After Reading this i am 100% clear with my all doubts

Anonymous said...

Hi,
I am using DB2 for ETL processing. Here the bind plan I gave different than what I should give but it error out initially with SQL -805 but then its working fine.
Wanted to know the significance of bind plan name. Wont it make impact if I use wrong bind plan name?

Unknown said...

That was very good explanation:-)
Thanks much NIthin

Gayathri said...

Thank you .. it was very useful

Prashant said...

Suppose I have only one DBRM than plan or package will be same?

rakesh said...

in 2021, i am still referring your blog. Kudos to you Nithin.
and thank you for this blog. keep posting such stuff please. !!

fazlı said...

denizli
elazığ
eskişehir
ığdır
ısparta
3LX

Post a Comment