If I have 1 COBOL DB2 program which is calling 2 other COBOL DB2 sub programs, then how many DBRMs,Packages,Plans it will create? If I am changing any one of the sub program then do I need to recompile and bind all the programs?I am really confused with DBRMs,Plans and Packages.
Oh my... This is a huge topic so this answer is going to be very simplified and therefore incomplete.
The answer depends somewhat on whether you are using the DB/2 pre-compiler or co-compiler. For this answer I will assume you are using the pre-compiler. If you are using the co-compiler the principle is pretty much the same but the mechanics are a bit different.
The goal here is to generate:
Everything in between just supports the mechanics of creating an appropriate DB/2 Plan for your program to run against.
Each program and/or sub-program containing DB/2 statements needs
to be pre-processed by the DB/2 pre-compiler. The pre-compiler
creates a DBRM (Data Base Request Module). The pre-compile also alters your source program by commenting
out all the
EXEC SQL...END-EXEC statements and replaces them with specific calls to the DB/2 subsystem.
You then use the regular COBOL compiler to compile the code emitted by the pre-processor to produce an object module which you then link into an executable.
The DBRM produced by the pre-compile contains a listing of the SQL statements contained in your program plus some other information that DB/2 uses to associate these specific SQL statements to your program. The DBRM is typically written to a permanent dataset (usually a PDS) and then input to the DB/2 Binder where the specific access paths for each SQL statement in your program are compiled into a form that DB/2 can actually use. The binder does for DB/2 roughly the same function as the compiler does for COBOL. Think of the DBRM as your source code and the Binder as the compiler.
The access path information produced when the DBRM is bound needs to be stored somewhere so that it can be located and used when your program calls DB/2.
Where to put the binder output? Your options are to bind it into a Package or directly into a Plan.
The shortest route is to bind a group of DBRMs directly into a Plan. However, as with many short cuts, this may not be the most efficient thing to do (I will touch upon the reason later on).
Most larger systems will not bind DBRMs directly into Plans, they will bind into a Package. The bound Package is stored within the DB/2 sub-system (same way a Plan is). What then is a Package? A Package is a bound single DBRM. A Plan, on the other hand, typically contains the access paths for multiple DBRMs.
Now we have a set of Packages, each Package contains the SQL access paths to its respective DBRM which was derived from a given program. We need to construct a Plan from these. To do this, a set of Bind Cards is created, usually by your Data Base Administrator. Bind Cards are just a sort of "source code" to the DB/2 Binder (they are not punched cards). The Bind Cards define which Packages form a given Plan. These are then submitted to the Binder which assembles them into a Plan. Note: you may also hear mention of Collections, these are just named groupings of Packages that have been defined by your DBA.
To summarize, we have the following process:
Program -> (Pre-Compiler) -> Modified Program -> (COBOL compiler) -> Object -> (Link) -> Load Module -> DBRM -> (DB/2 Bind) -> Package Bind Cards -> (DB/2 Bind) -> DB/2 Plan Package(s) ->
The two fundamental outputs here are a Load Module (your executable COBOL program) and a DB/2 Plan. The Program and Plan come together in your JCL where you have to give the Plan name somewhere within the EXEC statement along with the program to run.
With this brief, and highly simplified, background, lets try to answer your questions:
How may DBRMs are created?
One DBRM per program containing
SQL EXEC statements
How many Packages are created?
A package is constructed from one DBRM. There is a 1:1 correspondence between Source Program and Package
How many Plans are created?
Any given Package may be included in multiple Collections and or multiple Bind Card sets. This means that a given Package may be included in multiple Plans.
If I change a program what is affected?
If you bind your DBRM directly into a Plan, then you must rebind every Plan that uses that DBRM. This can be a very time consuming and error prone proposition.
However, if you bind your DBRM into a Package, then you only need to rebind that Package. Since there is a 1:1 correspondence between Program and Package, only a single bind needs to be done. The only time the Plan needs to be rebound is when a Package or Collection is added or removed from the Bind Card set that defines it.
The advantage of using Packages should be clear from the above and this is why most shops do not bind DBRMs directly into Plans, but use Packages instead.