“Packages?” Yes, packages! Even though packages were first delivered way back in the fall of 1991—when DB2 was still called Database 2; all the way back to the delivery of DB2 Version 2 Release 3—there are still questions and misconceptions about how packages work. There are questions such as:
- “What’s a collection?”
- “How many packages can fit in a single collection?”
- “Can a package list be too long?”
- “Should I use an explicit package list or a collection list when binding a plan?”
- “Will packages affect my performance?”
- “What effect will packages have on my Environmental Descriptor Manager (EDM) pool?”
- “I still don’t see what packages can do for me.”
- “What are packages actually doing?”
For some reason, packages have always generated some confusion. Maybe the right explanatory information wasn’t available. There was a Redbook on packages available back in October 1993 titled DB2 Packages: Implementation and Use (GG24-4001), but it’s out of print now. We used to teach a class on packages after the release of DB2 V2R3, but most of the folks working on DB2 now probably weren’t even in IT when that class was taught. Therefore, this article will re-introduce packages; it will cover a few of the basics and offer some detail on how to locate a package. Understanding the package search answers many questions about packages.
Let’s begin with what happened before we could create a package. You first needed a Database Request Module (DBRM), which is the input to the bind process and is created as one of the DB2 precompiler or SQL coprocessor outputs. However, to discuss the DBRM, we need to go back even further—to the first step in the program preparation process: the DB2 precompiler.
If your program is written in C, C++, COBOL, or PL/I, regardless of whether it uses static or dynamic SQL, that program must be “prepared” before you can execute it. The first step is determining how to handle the SQL. Many languages may not be ready to handle an SQL statement. In recent years, the option has been made available to use the SQL coprocessor in the compile step rather than running the program code through the DB2 pre-compiler. Both processes have the same effect: modified source and a DBRM.
Since DB2’s beginning, a method has been needed to “remove” SQL before the compiler could actually process source statements. The compiler had no idea how to process an SQL statement. The DB2 precompiler would replace the SQL statements with a CALL statement to the appropriate language interface in a modified source file. The actual SQL statement, in an internal format, would be added to a separate object, the DBRM. The modified source would serve as input to the compiler, and the DBRM as input to the bind.
Now, if you have the correct compiler release levels, there’s an SQL compiler option that lets the compile invoke the SQL coprocessor. This completely eliminates the precompiler step and lets the compiler “handle” an SQL statement and produce a DBRM. Both methods are acceptable and both create DBRMs. From here on, though, we’ll refer only to the precompiler step. The actual compiler step is executed, and an object module is produced as output of the compiler and passed to the linkage editor, which creates the executable representation of the application program, the load module. Now, let’s get back to that DBRM.
A DBRM is one of the outputs of the precompiler (or coprocessor) and contains all the program’s SQL in an internal format. If you’re pre-DB2 V8, the DBRM will be encoded using EBCDIC. Once you move to V8, that changes. During DB2 V8 Compatibility Mode (CM) and Enable New Function Mode (ENFM), EBCDIC is still used for the DBRM. However, once you move to New Function Mode (NFM) or set the precompiler option NEWFUN=YES, the DBRM is created using Unicode. Regardless of the encoding scheme used, the DBRM will eventually be input to the bind process. Bind creates the executable form of the SQL, similar to what the linkage editor does for the program. DBRMs can be bound into plans or packages. Our focus is on packages because no one would intentionally bind a DBRM directly into a plan anymore. There just isn’t any reason for that today.
A package is an executable representation of a single DBRM. Before the introduction of packages, the DBRMs representing the programs linked into a single load module were all bound into a single plan. Changes made to a single DBRM meant the entire plan had to be bound. If the DBRM was used in more than one plan, all plans containing that DBRM had to be bound. Depending on the number of DBRMs included in a plan, this could be a lengthy, disruptive process.
With packages, though, the process is simpler and offers some built-in fallback. Each package represents a single bound DBRM. A package is a direct, one-to-one relationship between an application program, the DBRM created by that application and used as input to the package BIND, and the eventual package itself. Even the package name is the same as the DBRM used to create it. A package name can’t even be specified at BIND time because there’s no package-name keyword on the BIND package statement. The package always assumes the name of the DBRM specified by the member or copy keyword. When a program is changed, creating a new DBRM, only that single package needs to be bound. At the next program execution, if versions aren’t being used, the plan will pick up the new package. No massive plan bind is needed.