Joined: 17 May 2010
Sorry for reopening a topic that had been discussed and closed many times but I am not getting a clearer picture of this.
Below is what I already know,
Plan - an optimized access path
DBRM - Generated during compilation (SQL's), Before DB2 2.3 DBRMs are bound to plan directly
Package - DBRM's are bound to package
Collection - logical grouping of package.
Packages are bound to plan to form an executable.
Below is what I don't understand:- (I hope one of you will answer)
DBRM - Generated during compilation (SQL's), Before DB2 2.3 DBRMs are bound to plan directly and requires rebind of plan every time when there is a change to an SQL statement. Why? IF Plan are just access paths of underlying VSAMs inside DB2, why would a change in an SQL statement requires a plan rebound? Even though SQL changes, there is no change in the underlying VSAM's correct? the underlying VSAM remain the same, the modules that are updating, reading the VSAMs remain the same.. This being said, why should we rebound the plan? what happens to the VSAMs if we rebound the plan?
How did packages resolve this issue?
I understand that the collection is a logical grouping of packages. why should they be logically grouped? what is the use? Everybody explains it is based on application but what is the use of grouping them logically? why cant I have one collection which has all the packages and only one plan and bind my only collection to my only plan?
Plan - We say it is an optimized access path. DB2 know the correct access path to all the underlying VSAMs/pages correct? then why should we define something which db2 already knows? is it like a set of tables are liked to a plan and another set to another plan to Improve performance? If yes, if I have only 5 tables in the DB, I can have only one plan defined and it will not affect the performance? and more the tables and more plans I should define?
I read the articles in the forum(s) and referred books.. I was not able to find a material which explains the relation between plan/package to internal DB2 pages.