Data Management Enhancements

The third grouping of DB2 V7 enhancements addresses data management and database administration issues. DB2 V7 offers extended capabilities for managing data more effectively and helping DBAs to be more productive.

Identity Columns

A common requirement of relational applications and databases is the need to store a counter that identifies rows in tables. Until V7, DB2 provided no inherent support for such functionality. DB2 V7 adds support for IDENTITY columns.

An IDENTITY column can be defined to a DB2 table such that DB2 will automatically generate a unique, sequential value for that column when a row is added to the table. For example, IDENTITY columns can be used to generate unique primary key values. DB2's implementation of IDENTITY columns avoids some of the concurrency and performance problems that can occur when application programs are used to populate sequential values for a "counter" column.

When inserting data into a table that uses an IDENTITY column, the developer or user does not provide a value to be inserted for the IDENTITY column. Instead, DB2 will calculate the appropriate value to be inserted.

Only one IDENTITY column can be defined per DB2 table. Additionally, the data type of the column must be SMALLINT , INTEGER , or DECIMAL with a zero scale, that is DECIMAL(x,0) . The data type also can be a user-defined DISTINCT type based on one of these numeric data types. The designer has control over the starting point for the generated sequential values and the number by which the count is incremented.

The following example creates a table with an IDENTITY column:

 CREATE TABLE EXAMPLE    (ID_COL INTEGER NOT NULL            GENERATED ALWAYS AS IDENTITY            START WITH 100            INCREMENT BY 10     ...); 

In this example, the IDENTITY column is named ID_COL . The first value stored in the column will be 100, and subsequent INSERT s will add 10 to the last value. The identity column values generated will be 100, 110, 120, 130, and so on.

Declared Temporary Tables

Declared temporary tables complement the existing DB2 (V5) capability to create global temporary tables, 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 a declared temporary table.

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

Declared temporary tables are much more functional than global temporary tables. An instance of a declared temporary table can be 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. 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.

The following example shows a DECLARE statement that can be issued from an application program ( assuming the temporary database and table spaces have been defined):

 DECLARE GLOBAL TEMPORARY TABLE TEMP_EMP   (EMPNO      CHAR(6)     NOT NULL,    FIRSTNME   VARCHAR(12) NOT NULL,    MIDINIT    CHAR(1)     NOT NULL,    LASTNAME   VARCHAR(15) NOT NULL,    WORKDEPT   CHAR(3),    PHONENO    CHAR(4)   ); 

Additionally, you can use the LIKE clause to DECLARE a temporary table that uses the same schema definition as another currently existing table. You can use the INCLUDING IDENTITY COLUMN ATTRIBUTES clause to copy the IDENTITY columns as well.

SQL statements that use declared temporary tables may run faster because DB2 limits the amount of logging and locking performed. Declared temporary tables can be useful in the following scenarios as well:

• When you need to retrieve data once and use it repetitively throughout a program, especially if the cost to retrieve the data is high (because the cost of retrieving it from a declared temporary table may be lower).

• When you wish to retrieve data from non-relational data sources (flat file, IMS, IDMS, and so on) and use SQL to access it or join it to other DB2 data.

It actually might be more appropriate to classify declared temporary tables as an application development enhancement, because they must be defined (declared) and used within the context of an application program. However, since they act like tables ”a database object ”declared temporary table support is grouped under Data Management enhancements.

Unicode Support

Support for an additional encoding scheme, Unicode, is added for DB2 V7. Unicode can be specified as the default on the DEF ENCODING SCHEME parameter of the DSNTIPF installation panel or when creating databases and table spaces using the CCSID parameter.

Unicode is an encoding scheme, like ASCII or EBCDIC, but it is more than that. This is so because Unicode provides a unique number for every character, no matter what the platform, program, or language. Unicode is required by modern computing standards, such as XML, Java, LDAP, and CORBA 3.0, WML, and is the official way to implement ISO/IEC 10646. The emergence of the Unicode Standard is significant in furthering a truly global computing and software environment.

Unicode is useful for multinational support, because it can be used to represent characters of virtually all languages. More information about Unicode can be found at http://www.unicode.org.

Utility Improvements

As is the case for each new release of DB2, IBM has provided numerous enhancements to the functionality and speed of the DB2 utilities. Some of the more interesting enhancements include:

• UNLOAD utility ” A true utility that provides better speed than DSNTIAUL . DSNTIAUL , until recently the primary method of unloading data from DB2 tables, is a sample program, not a true utility. As such, it lagged in features and functionality and many organizations chose to purchase unload capability from third-party ISVs like BMC Software and CDB Software. The new IBM UNLOAD utility though, is not free, but must be purchased from IBM as part of a DB2 utilities package at an extra charge.

• Parallel LOAD ” V7 provides the capability to load a partitioned table space with multiple input data sets in a single step.

• Online LOAD RESUME ” V7 provides the ability to add data to a table while the data in the table remains available.

• COPYTOCOPY utility ” This is a new utility that creates additional, registered image copies from existing image copies. The input or output can be local primary, local backup, offsite primary, or offsite backup copies. The generated copies can be used just like any other DB2 image copy backup.

• Speed ” as with each new release of DB2, IBM claims that each utility will run faster than earlier versions of the utilities.

However, the most important new development with IBM's utilities is automatic data set allocation and the ability to specify lists of objects with wildcarding. Utilities from the third-party ISVs have offered similar capabilities for several years now, and many DB2 users have been clamoring for IBM to provide similar functionality.

With automatic data set allocation, the utilities determine which data sets are required to perform the function and what size the data sets need to be. This helps, because each utility requires different data sets as they operate to save data during interim steps. Automatic allocation saves the DBA the effort of determining the information before running the utility. Additionally, "out of space" errors (for example, SB37 ) can be avoided because the size of the work data sets will be determined prior to each utility run, and need not be recalculated manually as database objects increase in size.

DB2 V7 provides the ability to create templates for utility data sets. Specifying templates to the dynamic data set allocation process provides needed data set characteristics. Both DASD and TAPE templates can be specified. Options are available to support features such as GDG generation and tape stacking.

Another new utility feature is the ability to supply lists of objects to a utility for processing. The LISTDEF parameter can be used to create these lists of objects for utility processing. The LISTDEF specification can use wildcarding to rapidly specify multiple objects without having to explicitly name each of the objects. For example, you can specify

 LISTDEF DB1 INCLUDE TABLESPACE DBXN.*             EXCLUDE TABLESPACE DBXN.TS2 REORG LIST DB1 . . . 

This will reorganize all table spaces in the database named DBXN except for the one table space exempted, namely TS2 . Furthermore, if a table space is subsequently added to DBXN , the REORG job does not need to be changed. The next time it runs, REORG will query the DB2 Catalog to determine the table spaces that exist in the list name DB1 . Because it specifies all table spaces in DBXN , any new table space added to DBXN will automatically be picked up for processing.

The LISTDEF capability is very powerful. The LISTDEF definition can be specified either in a separate data set or in the SYSIN data set preceding a utility control statement. The default DD name for a LISTDEF statement is SYSLISTD .

DB2 provides multiple wildcarding options for the LISTDEF specification. The developers tried to support both de facto wildcarding standards, such as the asterisk ( * ), as well as the wildcarding options used by the SQL LIKE predicate. Pattern-matching characters available for wildcarding include

• Both the percent sign character ( % ) and the asterisk character ( * ) to represent zero or more characters

• The question mark character ( ? ) to represent any single character

There are limits to the LISTDEF clause though. You cannot specify all-inclusive lists, such as DATABASE * or TABLESPACE *.* . But there are other powerful options such as the RI parameter that will include all tables referentially connected to the table(s) specified in the list. List generation and wildcarding can greatly simplify the creation and management of DB2 utility jobs.

And very importantly, with DB2 V7 IBM no longer ships the DB2 utilities free of charge with a DB2 license. This issue is discussed later in this appendix.

Deferred Data Set Creation

One of IBM's largest recent objectives has been to add features to DB2 to support ERP packages, such as SAP R/3 and Peoplesoft. Deferred data set creation, new as of V7, is one such feature. With deferred data set definition it is possible to create tables and not define the underlying data sets. Creation of the data sets to store data for the tables is deferred until they are used.

This is important for ERP packages where many tables are defined but never used. ERP packages are typically broken up into multiple business functions, and the customer can buy the software by functionality. But many ERP vendors simply create all of the database objects for all of the functionality of the entire package, regardless of the functionality purchased by the user. This causes many database objects to be defined but never used.

With deferred data set creation, customers deploying ERP packages on DB2 for OS/390 and z/OS can defer the physical creation of the underlying data sets for the database objects, while allowing the ERP package to create all of the database objects it requires.

Log Suspend/Resume

DB2 V7 provides better support for copying data at the storage hardware level. The new LOG SUSPEND command can be used to halt UPDATE activity and logging. Additionally, the LOG RESUME command is used to restart update activity and logging.

The log suspension and resumption commands make it easier to make external copies of the system. After issuing LOG SUSPEND , you can use a fast-disk copy facility, such as FlashCopy on IBM's Shark ESS or SnapShot on a RAMAC Virtual Array. After the fast snap is completed, LOG RESUME can be issued to re-enable database modification.

Data Sharing Enhancements

DB2 V7 provides several improvements for data-sharing environments. One such enhancement is referred to as Restart Light . To support this new light restart option, the START DB2 command has been enhanced. Restart Light allows a DB2 data-sharing member to restart with a minimal storage footprint and then to terminate normally after DB2 frees retained locks. By reducing storage requirements, restart for recovery can be possible for more resource-constrained systems.

Improved immediate write capability is another data sharing enhancement. DB2 V6 provided an option to immediately write updated group buffer pool dependent buffers. DB2 V7 enhances this capability by recording the choice in the DB2 Catalog and externalizing it on the installation panels.

A final V7 data sharing improvement is support for persistent structure size changes. As of DB2 V7, changes made to structure sizes using the SETXCF START, ALTER command will be persistent when you rebuild or reallocate a structure.

Additional Data Management Enhancements

IBM has made numerous additional improvements to the data management capabilities of DB2 in Version 7. Some of the more interesting enhancements include

• The ability to change most DSNZPARM s without first stopping and then restarting DB2.

• Support for coding UNION and UNION ALL in views. This support is added not just for CREATE VIEW , but also for inline views (where a SELECT statement is coded in the FROM clause of another SELECT statement).

• Instead of RUNSTATS always obliterating any old statistic values, a history of object statistics can be maintained in the DB2 Catalog. This way DBAs can review the historical growth and changes for database objects. Support has been added for the MODIFY STATISTICS to be able to remove historical statistics from the DB2 Catalog.

• The DB2 Control Center has been enhanced. One of the biggest enhancements is the ability to generate DDL from the DB2 Catalog using DB2 Control Center.

• Users with DBADM authority can create views for others, thereby minimizing the reasons for granting SYSADM .

• Sometime after V7 general availability (but before V8) IBM released real-time statistics. This feature brings to DB2 the ability to capture performance statistics and populate them into catalog-like tables, as DB2 runs ”without the need to run an external utility.

• The ability to issue DDF SUSPEND and DDF RESUME commands to temporarily halt activity from requesters without terminating connections. The primary reason to suspend DDF requests is to enable DDL statements issued on the server to complete.