Some Basic Information About SQL in DB2-Accessing Programs

DB2 has been around for a long time (more than 25 years), and a lot of people who work with DB2 have been doing so for a long time (myself included). Many of the younger folks I meet who are professionally engaged in DB2-related activity are developers. Some of these who came to DB2 after working with other relational database management systems might have been initially confused on hearing their DB2-knowledgeable colleagues talk about SQL as being "embedded" or "static" or "dynamic." Waters may have been further muddied when familiar words such as "package," "collection," and "plan" took on unfamiliar meanings in discussions about DB2-based applications. Throw in a few terms like "DBRM" and "consistency token," and you can really have a DB2 newbie scratching his or her head. Of late, I've seen enough misunderstanding in relation to programming for DB2 data access. My hope is that this post will provide some clarity. Although I am writing from a DB2 for z/OS perspective, the concepts are essentially the same in a DB2 for Linux/UNIX/Windows environment (some of the terminology is a little different).

First up for explanation: embedded SQL. Basically, this refers to SQL statements, included in the body of a program, that are converted into a structure, called a package, that runs in the DB2 database services address space when the program executes. The package is generated through a mechanism, known as the bind process, that operates on a file called a database request module, or DBRM. The DBRM, which contains a program's embedded SQL statements in a bind-ready form, is one of two outputs produced when the program is run through the DB2 precompiler. The other of these outputs is a modified version of the source program, in which the embedded SQL statements have been commented out and to which calls to DB2 have been added -- one call per SQL statement. Each of these DB2 calls contains a statement number, the name of the program's DB2 package, and a timestamp-based identifier called a consistency token. The statement numbers and the consistency token are also included in the aforementioned DBRM, and these serve to tie the program in it's compiled and linked form to the package into which the DBRM is bound: at program execution time, a DB2 call indicates the package to use (the match is on package name and and consistency token value), and identifies the section of the package that corresponds to the SQL statement to be executed.

The above paragraph is kind of a mouthful. Here's the key concept to keep in mind: the package associated with a program containing embedded SQL is generated before the program ever runs. To put it another way, DB2 gets to see the embedded SQL statements and prepare them for execution (by doing things like access path selection) before they are issued by the application program.

A few more items of information related to packages:

• Packages are persistent -- they are stored in a system table in the DB2 directory database and loaded into memory when needed. Once cached in the DB2 for z/OS environmental descriptor manager pool (aka the EDM pool) a package is likely to stay memory-resident for some time, but if it eventually gets flushed out of the pool (as might happen if it goes for some time without being referenced), it will again be read in from the DB2 directory when needed.
• Packages are organized into groups called collections.
• For application processes that are local to a DB2 for z/OS subsystem (i.e., that run in the same z/OS system as the target DB2 data server), packages are executed through plans. So, a batch job running in a JES initiator address space -- or a CICS transaction, or an IMS transaction -- will provide to DB2 the name of a plan, which in turn points to one or more collections that contain the package or packages associated with the embedded SQL statements that the application process will issue. Applications that are remote to the DB2 subsystem and communicate with DB2 through the Distributed Data Facility using the DRDA protocol (Distributed Relational Database Architecture) make use of packages, but they do not refer to DB2 plans.

What about dynamic versus static SQL? Plenty of people who know a lot about DB2 will tell you that "static SQL" means the same thing as "embedded SQL." In my mind, the two terms are almost equivalent (some would say that this "almost" of mine is a matter of splitting hairs). It's true that static SQL is seen by DB2 and prepared for execution before the associated program is executed. That's what I said about embedded SQL, isn't it? Yes, but there is something called embedded dynamic SQL. That would be an SQL statement that is placed in a host variable that is subsequently referenced by a PREPARE statement. The SQL statement string in the host variable is dynamic (that is to say, it will be prepared by DB2 for execution when it is issued by the program), but -- and this is the splitting-hairs part -- PREPARE itself is not a dynamic SQL statement.

Dynamic SQL statements (again, those being statements that are prepared when issued by a program, versus being prepared beforehand through the previously described bind process) can of course be presented to DB2 without the use of PREPARE -- they can, for instance, take the form of ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity) calls. They can also be issued interactively through tools such as SPUFI (part of the TSO/ISPF interface to DB2 for z/OS) and the command line processor (a component of DB2 for Linux/UNIX/Windows and of the DB2 Client).

Some DB2 people hear "dynamic SQL" and think "ad-hoc SQL." In fact, these terms are NOT interchangeable. Ad-hoc SQL is free-form and unpredictable -- it could be generated by someone using a query tool in a data warehouse environment. Ad-hoc SQL will be dynamic (prepared for execution by DB2 when issued), but dynamic SQL certainly doesn't have to be ad-hoc. There are tons of examples of applications -- user-written and vendor-supplied -- that send SQL statements to DB2 in a way that will result in dynamica statement preparation. That doesn't mean that users have any control over the form of statements so issued (users might only be able to provide values that will be substituted for parameter markers in a dynamic SQL statement). "Structured dynamic" is the phrase I use when referring to this type of SQL statement. Just remember: static SQL CANNOT change from execution to execution (aside from changes in the values of host variables). Dynamic SQL CAN change from execution to execution, but it doesn't HAVE to.

I'll close by pointing out that dynamic SQL statements are not, in fact, always prepared by DB2 at the time of their execution. Sometimes, they are prepared before their execution. I'm referring here to DB2's dynamic statement caching capability (active by default in DB2 V8 and V9 systems). When a dynamic SQL statement is prepared for execution, DB2 will keep a copy of the prepared form of the statement in memory. When the same statement is issued again (possibly with different parameter values if the statement was coded with parameter markers), DB2 can use the cached structure associated with the previously prepared instance of the statement, thereby saving the CPU cycles that would otherwise be consumed in re-preparing the statement from scratch. Dynamic statement caching is one of the key factors behind the growing popularity and prevalence of dynamic SQL in mainframe DB2 environments.

I hope that this overview of SQL in DB2-accessing programs will be useful to application developers and others who work with DB2. When all is said and done, the value of a database management system to an organization depends in large part on the value delivered by applications that interact with the DBMS. Code on!