DB2 V7 B4 V8

By Craig S. Mullins

A lot of excitement has been generated the past few months since IBM announced Version 8 of DB2 for z/OS. While this excitement is warranted, those of us here in the trenches still have to get production work done. V8 is not expected to become generally available until sometime late in 2003 or early in 2004.  And many of us are just now coming up to speed on V7. So let’s take a few moments to highlight a few V7 features that we should be adopting before we become too giddy about V8.

DB2 V7 offers several quite useful programming improvements. For example, consider the great strides that have been made to improve how temporary data is handled. Declared temporary tables, new to V7, complement the capability to create global temporary tables (introduced in DB2 V5). But declared temporary tables differ from global temporary tables in many significant ways:

·         Declared temporary tables do not have descriptions in the DB2 Catalog. They are defined in the program, instead of prior to program execution.

·         Declared temporary tables can have indexes and CHECK constraints defined on them.

·         You can issue UPDATE statements and positioned DELETE statements against them.

·         You can implicitly define the columns of a declared temporary table and use the result table from a SELECT.

An instance of a declared temporary table is created using the DECLARE GLOBAL TEMPORARY TABLE statement. That instance of the table is known only to the process that issues the DECLARE statement. Multiple concurrent programs can be executing using the same declared temporary table name because each program will have its own copy of the declared temporary table. Before you can declare temporary tables you must create a temporary database and table spaces for them to use. This is accomplished by specifying the AS TEMP clause on a CREATE DATABASE statement (also new to DB2 V7). Then, you must create segmented table spaces in the temporary database. Only one temporary database for declared temporary tables is permitted per DB2 subsystem.

When a DECLARE GLOBAL TEMPORARY TABLE statement is issued, DB2 will create an empty instance of the temporary table in the temporary table space. INSERT statements are used to populate the temporary table. Once inserted, the data can be accessed, modified, or deleted. When the program completes, DB2 will drop the instance of the temporary table.

SQL statements that use declared temporary tables may run faster because DB2 limits the amount of logging and locking performed.

Probably the most significant new application development enhancement is scrollable cursors. A scrollable cursor provides the ability to scroll forward and backward through the data once the cursor is open. This can be achieved using nothing but SQL – no host language code (COBOL, C, etc.) is required to achieve scrolling. A scrollable cursor makes navigating through SQL result sets much easier. There are two types of DB2 scrollable cursors: SENSITIVE (where data can be changed) and INSENSITIVE (not updateable; will not show changes made).

To use scrollable cursors you must use declared temporary tables, which are used to hold and maintain the data returned by the scrollable cursor. Scrollable cursors allow developers to move through the results of a query in multiple ways. You can fetch the next or previous row; the first or last row; the same row again; or reposition the cursor anywhere within the results set.

But as helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so.

There are quite a few additional SQL enhancements made to V7 including row expressions (which allow SQL statements to be coded using more than one set of comparisons in a single predicate using a subquery), limited FETCH (to limit the amount of data returned by a SELECT statement), external SAVEPOINTs (to establish stability points within a unit of work), UNIONs in views, and Unicode support (which is needed when you get to V8). Not to mention the utility decision (IBM or third-party)… but we are just about out of space for this issue.

Summary

These are just some of the highlights of DB2 V7. Many of these features form the basis for future enhancements to be delivered with V8, so learning these features and adopting them today will help to position you well when V8 eventually sees the light of day.