Database Packages and Query Performance

To understand how all applications communicate with DB2, a short discussion of how packages are bound in embedded SQL is necessary. The popularity of embedded SQL on Intel and UNIX platforms has decreased with the emergence of more portable programming interfaces. However, embedded SQL in COBOL is the prevalent database programming method for host systems such as DB2/390, and there are still many legacy applications on common server platforms as well. In addition, embedded SQL forms the foundation for DB2 UDB programming since even driver-based interfaces use embedded SQL source modules at the base level.

The reason for this is best understood by returning to the discussion about database objects known as packages, introduced in the first chapter. We explained that packages store the compiled set of instructions that constitute the access plan chosen by the DB2 optimizer. However, this is not the complete picture. Only statically bound statements in source modules contain an access plan. Statically bound means that the SQL statement is known prior to run time, and is directly embedded into the application. Furthermore, this embedded statement has a one-to-one correspondence with a package section containing an access plan in the system catalog tables.

This is different from the dynamically bound statement lifecycle that we explored. Dynamically bound statements first undergo a PREPARE operation to generate the query access plan. Each PREPARE operation references a package section within the system catalog tables, just like statically bound statements. However, these package sections are empty, because the statement's access plan is dynamically generated at run time and is never stored on disk. This is why a dynamic (run time) SQL interface such as the DB2 CLI/ODBC driver has packages that must be bound against each database being accessed. The developers who wrote the DB2 CLI/ODBC driver have integrated their own dynamic SQL source modules into the DB2 CLI driver library. These modules are referenced every time an application executes a statement using the driver.

Why do dynamically bound statements have a package at all? The reason for this is because of how DB2 caches statements to improve performance. Each database within an instance has a package cache, which is referenced using the package and section ID. This requires a source module for every dynamic SQL application because only source modules can be used to produce a bound package. This is not necessarily the easiest way to handle dynamic statements, however, this design predates DB2 UDB, and goes back to the earliest versions of the DB2 product, when interfaces such as ODBC were in their infancy.

If you are developing your applications with a dynamic SQL interface, consider adding static SQL to your code as well. For many programming languages, you have the option of mixing your dynamic SQL code with static SQL for specific queries. Although this might seem like an additional maintenance headache, you will see major performance improvements using this strategy.