Posted: Tue Apr 10, 2007 11:15 pm Post subject: PACKAGE VS PLAN ?
I know that while executing a DB2 program plan is used. But Packages are preferred over plan when you are binding a program for various advantages over Plan. What I did not understand is if packages are preferred but we still need to bind the package into a plan in order to make the package executable. Then where is the question of packages being better than plans? Can somebody explan this please.
Assume that we have 500 pgms which are bind at the package level,and eventually these pakcage list will be part of the superset called PLAN.
When we change say 5 pgms out of these 500 pgms we can rebind only those 5 pgms to the respective packages and then the PLAN will pick the latest LOAD modules for execution.SO when when the PLAN picks the latest LOAD componenets here we are only re-evaluating the access-paths of only those 5 newly changed components.
Assume the same scenario with the 500 pgms bind at the PLAN level, even though there is a change in 5 pgms all the 500 pgms ACCESS PATHS need to be re-evaluated.
During the BIND process the transaction or transactions become unavailable and results in OUTAGE, the OUTAGE when the components are BIND at the PACKAGE LEVEL is negligable where as considerable latency time will be taken for the DB2 transactions/subsystem when the components are BIND at the PLAN level.
NOTE : SOME SYSTEM ADMIN WILL ABLE TO GIVE THE RIGHT PERSPECTIVE IN TERMS OF THE OUTAGE,LATENCY TIME or THE AVAILABILITY OF THE DB2 transactions.
we still need to bind the package into a plan in order to make the package executable.
Yes this is ineviatable as the ACCESS-PATHS can be derived only thru PLAN..to excute the QUERY on DB2 subsystems.
When you say bind package into a plan to make packages executable, only those executable gets stored in a plan.
only those executable gets refreshed and stored in a plan apart from the other executables without any change. I mean it has all the executables old and latest for which updations are done.
Can I have some programs directly bound into a plan and someprograms bound into the same plan via package?
YES, you can use a combination of both methods.
Another questions - if you have mulitple programs bound directly into a plan, why can't bind happen only on those programs which are changed and not everthing in the plan?
WHEN WE SAY BIND @ PLAN IT MEANS FOR ALL. ( ITS THE DESIGN)
Since PLAN is a superset it tries to refersh/BIND the entire listing including the packages,As per the hierarchy i do not think the PLAN will have reference or handle at each program level bound thru package, but it has @ the package level.
So even though if the programs are BOUND directly as you have mentioned it tries to BIND all since it can/may have other pakcages in which few programs are changed. WHEN WE SAY BIND @ PLAN IT MEANS FOR ALL. ( ITS THE DESIGN)
What i mean to say is the HIERARCHY and the architetural design is built to design ur application @ modular level.
So that is the reason why we build PAKCAGES for some set of PROGRAMS for a region/application.
And the BIND/REBIND depends on the significance of the values we use for RELEASE/ACQUIRE Keywords/options.
If you were dealing with a plan that had DBRMs bound into it directly as you mentioned then you could choose ACQUIRE(ALLOCATE), which tells DB2 to acquire all the resources for all the SQL statements in all the DBRMs bound into that plan when the plan?s thread is allocated. This is in a way advantage as you have everything at once and also
a kind of disadvantage as from the list PLAN may not execute few at all.
NOTE :EVEN IF WE USE ACQUIRE(USE) IT GETS EVERYTHING AT THE TIME OF EXECUTION BUT IT MEANS FOR ALL THE PROGRAMS,
PAKCAGES. NORMALLY THIS IS THE BEHAVIOR OF THE PACKAGE ACQUIRE(USE) WE CANT OVERRIDE IT.
When binding a plan, ACQUIRE(ALLOCATE) must always be paired with RELEASE(DEALLOCATE).Since from the entire listing of PGMS or PACKAGES few may never be executed in a particular plan execution. So RELEASE(DEALLOCATE) will improve the performance,but RELEASE(COMMIT) is normally used any DB2-CICS threads are long conversational and there?s probably only a single piece of work being executed anyway.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum