Precompiler+Bind+Plans + DBRMS + Packages + Collections + Versions = Confusion

The DBRM (Danny)

At BIND time, DB2 created run-time executable instructions for the SQL portion of the program. But where are those instructions, and what are they called now that the term DBRM no longer applies?

The truth is, you have a choice. You can BIND the instructions for the SQL that was in the DBRM into a PLAN (the old way), or you can BIND the instructions into a PACKAGE (the not-so-old-but-no-longer-new way). The reason for this choice is historical. Back when knights were bold, dragons walked the Earth, and DB2 and some of us were young, DBRMs were bound directly into PLANs. In today's DB2 (since V2R3), there are two ways of doing BINDs. You may continue to BIND DBRMs into PLANs, or you may BIND DBRMs into PACKAGEs. With the second option, you keep your PLAN but use it only as a search chain. This column explains why things changed in V2R3. A future column will explain how things have continued to change in v.7 and v.8.

In the early releases of DB2, the DBRM (SQL originally embedded in our COBOL program but separated at precompile time into a PDS member) was bound into a PLAN (PLANA). This method worked just fine as long as the program was a standalone program. You coded the JCL to execute program PGMA naming the PLAN "PLANA," and at run time the twins found each other. However, things got a bit complicated when PGMA needed to CALL PGMB. Because only one PLAN could be named in an execute statement, the PLAN had to contain run-time instructions for both PGMA and PGMB. This problem was solved by having the BIND instruction for PLANA name a memberlist; the DBRMs for both PGMA and PGMB were listed as members. And if PGMB called PGMC, then the three would be listed as members. And if C called D, which could call E, F, G, or H, which could call I, J, K, L, or M, which could .... Well, you get the idea.
Memberlists got longer and longer. What were (and still are, if you cling to the old technique) the drawbacks of having a very long list?

1. Remember that DB2 authorization and SQL syntax are checked at BIND time. Access path alternatives are also weighed, the least-cost path is chosen (based on current statistics and system resources), and run-time instructions are created for the chosen path. Well, if the PLAN contains one member, A, this process should be quick. But what if there are 20 or 50 or 500 DBRMs in the memberlist? The BIND could take hours.
2. So, the PLAN, which took more than a while to BIND, contains 500 members. What if one of the programs, PGMA, changes? When the source code changes, the program must be precompiled. And precompile changes the tattoo and creates a new DBRM. That new DBRM must be bound into the PLAN. When the PLAN is bound, all 500 DBRMs (not just the modified PGMA) will be rebound. It could take hours to BIND a PLAN, even though 499 of the 500 programs haven't changed.
3. Also, remember that BIND is an opportunity to reassess and change the access paths for not only the modified program, but also every single program in the memberlist. If one program changes, every program in the list will go through BIND.
4. What if you want to modify PGMB to call a new program, PGMZ? You must not only precompile modified B and new Z, you also must add new PGMZ to the memberlist and BIND the whole list — all 501 DBRMs.
5. You want to remove PGMT? Edit the memberlist and then BIND the PLAN again with the remaining 500 members in the list and wait impatiently while DBRMs that haven't changed go through the BIND process.
6. Okay, modified program A turns your processor over on its back, casters up. You want to fall back to the original version of PGMA. And exactly how would you do that quickly? If (and it's a big if) you have the old DBRM with its old tattoo, you could move it into the DBRMlib and BIND the entire 500-member list (even though only A had regressed) and replace the new loadlib member A with the prior loadlib member A. Or, if you have the old source code for A, you could precompile it to recreate both the modified source code and the DBRM, and then COMPILE, LINK, and BIND, which would BIND all 500 DBRMs in the member list.
7. Program Q has to run against two sets of tables, one for Corporation 1 and a second for Corporation 2. The sets of tables have identical names but different high-level qualifiers. You could use synonyms, but they're unwieldy; binding during a time when a synonym points to the wrong set of tables could cause disaster.

These (and other) quandaries faced many DBAs in the days before V2R3 and the advent of PACKAGEs. what's is PACKAGE...... chek out next