1. When we bind(or create) the plan, does DB2 provides various access path or one access path which is the best, and during execution optimiser selects the best path, second part of question - Does Optimizer comes into picture during Bind
2. If it provides various access path, then Explain=Yes parameter in Bind, will it generate the explain for all the access path(I can't check it now, as I don't have a plan table with my HLQ neither do i have access to create one)
3. Does access path changes with the data provided in the where clause, as in Suppose I have a predicate to choose Student ID > '0001' and Student ID <= '10000'. Now if i change the value in the next query from 20000 to 50000, will the access path remain same.
@dbzTHEdinosauer --> Thanks for the link, it was really informative.
But I have some questions -
1. The article says - Companies rarely had COBOL license on production machine. If so, how cobol program used to run in production.
2. Bind included check for authorization and SQL syntax. When, IBM came up with concept of package, they moved all the authorization chk and SQL syntax check to package creation step, AM I right & the only work of bind step was to generate the executable and nothing else.
3. The paragraph detailing the Collection seems to be confusion. The question is, if in a program there are two tables being refernced, but both have same name, but the HLQ is different. Even Collection will not help. In the program itself, coder will have to give HLQ in query, but the paragraph seems to say, collection will solve this problem.
Besides, one of the problem with binding a DBRM to plan was, if a program is changed, it has to be bounded again, which will result in binding of other program which was not required.
To solve this, they come up with Package concept, but even in this case, the bind has to be done again, even though only one program has changed. Reason is, Latest version of package needs to be bounded.
The problem could have been solved, if the binding was dynamic, also package step would have created executable and not an non executable.
The bind step would have just stored the information and address of package, and when a SQL was referenced in program, Bind would have given the name and address of the package. So by this, even though the Package has changed, Bind was not required.
Any reason why this was not followed rather they went on creating the concept of collection ID.
Joined: 20 Oct 2006 Posts: 6970 Location: porcelain throne
1. license is for the compiler.
3. the use of collections, even different plans or different packages
removed the necessity of coding the hlq in the sql.
I have never used a hlq for a table in a program, only in spufi.
part of the bind process is to associate the program/plan to a hlq - look at bind parms.
binding dynamically is not economical.
statically bound programs execute with significantly less resources that dynamic sql programs.
bind is always required before the sql can be invoked.
either static or dynamically.