DBRM, Package, Collection and Plan

DBRM:

DataBase Request Module has the SQL statements extracted from the host language program by the SQL precompile. The COBOL-DB2 program is preprocessed by DB2 to produce a DBRM that will be used to bind against a particular DB2 subsystem. The Db2 system uses the DBRM to optimize application program interaction.

PACKAGE:

A package contains control structures that DB2 uses when it runs SQL statements. It contains executable forms of SQL statements. Packages are stored in the database system catalog tables.

COLLECTION:

A group of bound PACKAGES. It’s there mainly to simplify the management of sets of packages.

PLAN:

The plan contains the bound, operational, form of the SQL statements from the DBRMs that were built from the application programs. It is created during the BIND process. It has the executable code for the SQL statements in the DBRM. The plan is marked as invalid if the index used by it is dropped. The next time the plan is invoked, it is recreated. An application plan allows application programs to access DB2 data at execution time. It also relates the whole application process to the local instance of DB2 where it will be used.

Object Type: DBRM
Contents: SQL for a source program
Input: Source Program  
Command: DB2 Precompile Program (dependent on source language)
Output: DBRM
Authority needed (if not SYSADM): None

Object Type: PACKAGE
Contents: Bound SQL for a source program
A PACKAGE is initially created with the BIND PACKAGE. The actual name of the package is a 4 part name:
location name.collection id.DBRM member name. version where version is determined by precompiler options but is typically a timestamp.Input: DBRMCommand:BIND PACKAGE (location name.collection id)
OWNER (authid of package owner)
QUALIFIER (for unqualified SQL)
MEMBER (DBRM member name)
LIBRARY (DBRM PDS name)
+ additional parameters
Output:
Package residing in the specified collection
Note: Location name does not need to be specified if BIND command executed on same DB2 subsystem as where BIND is taking place

To BIND a PACKAGE

Complicated, but simplistically, the OWNER must have all:
· SQL authority for all statements in the package
· BINDADD (for new package) or BIND (for the existing package)
· CREATE IN or PACKADM for specified collection
Also, if the Binder is not equal to OWNER, then Binder must be in an ACF2 Source Group containing OWNER

To execute a PACKAGE for a program:
EXECUTE on the PLAN containing the PACKAGE for the program. If executing through DRDA when no PLAN exists on the local DB2 system, then EXECUTE on the PACKAGE or on collection id.* containing the PACKAGE

Object Type: COLLECTION
Contents: A group of bound PACKAGES

A collection is NOT explicitly created (There is no CREATE COLLECTION SQL statement). It is implicitly created the first time a BIND PACKAGE into a collection with that name is executed.
A package is added or replaced in a COLLECTION via the BIND PACKAGE command.
To bind  a package in a collection: CREATE IN for the specific collection (+ whatever authorities are needed for the package itself)

Object Type: PLAN
A group of bound DBRMS and/or PACKAGES
A PLAN is initially created via the BIND PLAN statement

DBRMs specified in the BIND PLAN command must already exist
PACKAGES specified in the BIND PLAN command do not need to exist
Input: DBRMs and/or a list of PACKAGES. This list of packages can be wildcarded as described below
Command:
Similar to BIND PACKAGE with the important addition of PKLIST
BIND PLAN (plan name)
OWNER (authid of plan owner)
QUALIFER (for unqualified SQL)
MEMBER (DBRM member name, …)
LIBRARY (DBRM PDS name)
PKLIST (location name.collection id.package id, …) additional parameters
Output:
The BIND PLAN command (for a new PLAN) does two things:

1. Any DBRMs specified are bound in the same way that BIND PACKAGE binds DBRMs
2. Creates a PLAN object contains all DBRMs specified and the list of packages specified.
   Notes on PKLIST (location name.collection id.package id) parameter:
3. Any or all of location name, collection id, and package id may be wildcarded with *. This allows future BIND PACKAGE (location name.collection id) commands to bind additional packages into the PLAN without needing additional BIND PLANs
4. Typically at NEES, BIND PLAN specifies PKLIST (collection id.). This results in location name defaulting to the local location (DB2A or DSNP) and includes ALL packages that are or will ever be bound into a specific collection. Example: The DBA first issues BIND PLAN (PSFTDPLB) PKLIST (PSFTDCOL.) to create a DEV Plan PSFTDPLB to include ALL packages which are or will ever be bound into the DEV Collection PSFTDCOL via
   BIND PACKAGE (PSFTDCOL) MEMBER (DBRM member name) LIBRARY (DBRM PDS name) commands
   The developer then issues a BIND PACKAGE (PSFTDCOL) MEMBER (PIP250) LIBRARY(TESTPSFT.HRDEV.DRMLIB). This creates a package named DB2A.PSFTDCOL.PIP250.version where version = a timestamp.

To BIND a PLAN

Complicated, but simplistically, the OWNER must have all:

· SQL authority for all statements in DBRMs specified (this authority is NOT necessary for packages included in the PKLIST since they are already bound)
· BINDADD (for a new plan) or BIND (for the existing plan)
· For explicitly specified packages: EXECUTE authority (or PACKADM) on each explicitly specified package or on collection id.* containing an explicitly specified package
· For collection id.: EXECUTE authority (or PACKADM) on collection id.

Also, if the Binder is not equal to OWNER, then Binder must be in an ACF2 Source Group containing OWNER

To execute a PLAN for a program EXECUTE on the PLAN containing the bound DBRM or PACKAGE for the program.

Read DB2 blogs : Click Here IBM DB2 Manual : Click Here