Skip to content

Personal tools
You are here: Home » DB2 » DB2 Distributed (UDB) Articles Archive » Database Technology Leaps Ahead - Part 1
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 4455

Database Technology Leaps Ahead - Part 1

by Philip Gunning

Part 1  |  Part 2

The culmination of years of research by IBM Labs, DB2 Universal Database (UDB) V8.1 is getting a lot of attention for the advances it makes toward autonomic computing with new Self Management and Resource Tuning (SMART) features. And those features, which promise to help DBAs manage more data with less effort, deserve notice. But DBAs aren’t the only ones who will benefit from V8.1’s changes and enhancements. Changes in XML and Web services support, integration with new development environments, integrated business intelligence features, and performance, scalability, and availability enhancements promise to benefit application developers, systems analysts, business users, and DBAs.


DB2 V8.1 contains many enhancements in ease of maintenance. Self-management features are evident throughout the product. Autonomic computing enhancements include new and improved wizards and advisors and a complete suite of database management tools. These enhancements are in response to the ever-increasing business need to do more with less. In an environment where highly skilled DBAs are hard to come by, self-management will help companies meet demanding business challenges.

I’ll touch on some of the SMART technology and other manageability features in V8.1. For more information about SMART technology, see the article “A SMARTer DB2.”


  DB2 UDB V8.1
  IBM Manuals

Load enhancements

Online load enables load operations to take place at the table level, allowing concurrent access to other tables in a multiple table tablespace. This change particularly benefits ERP and CRM applications, which often have many tables per tablespace. If you specify the COPY NO option for a recoverable database, the tablespace will be placed in a backup-pending tablespace state when the load operation completes. With the READ ACCESS option of the load command, preexisting data can be queried while new data is loaded. The new option LOCK WITH FORCE lets you force applications to release locks on a table so that a load operation can proceed. Autoloader functions have been incorporated into the load utility, and the autoloader control file is no longer needed.

The V8.1 load utility now generates column values for generated columns and no longer requires the SET INTEGRITY statement on a table that has generated columns and no other constraints. The LOAD QUERY command now returns the status of the target table in addition to the status information previously included. LOAD QUERY can also query table states whether or not a load operation is in progress. You can select a Load Wizard from a list of Wizards in the Tools menu.

Load wizard

After launching the Load Wizard, you can choose from a list of instances, databases, and tables. Make your instance, database, and table selection and the wizard presents you with an option dialog for specifying performance and statistics collection options.

Select the options you want, then simply click Finish to complete the operation. You can launch almost all the new wizards in this way.

In addition to the Load Wizard, IBM added eight wizards to DB2 V8.1:

      • Memory Visualizer
      • Redistribute Data Wizard
      • Backup and Restore Wizard
      • Configure Database Logging Wizard
      • Add Partition Wizard
      • Alter Database Partition Group
      • Storage Management View
      • Design Advisor (an enhanced form of the previous Index Advisor).

Flush package cache

A new FLUSH PACKAGE CACHE SQL statement lets you remove cached dynamic SQL statements by invalidating them. Those who currently use the dynamic SQL statements can continue to do so; however, any new request for the same statement will be prepared and a new statement entry cached. This capability will be useful in conjunction with the new online update of database manager (DBM CFG) and database configuration (DB CFG) parameters.

Logging enhancements

DB2 V8.1 supports dual logging on all 8.1 platforms, which means you can now specify the logpath through the MIRRORLOGPATH DB CFG parameter. This new parameter replaces the previous registry variable DB2NEWLOGPATH2.

The maximum amount of log space increases to 256GB from 32GB. Infinite logging allows a current unit of work to span primary and archive logs. Previously, log records for a unit of work had to fit into the primary log space. This change will accommodate large units of work that require more log space than that allocated for the primary logs. The BLK_ON_LOG_DISK_FUL registry variable has been replaced with the new database configuration parameter BLK_LOG_DSK_FUL, which tells DB2 not to fail on a log full condition. Instead, DB2 will retry the write to the log every five minutes, giving you time to resolve the disk full condition.

Backup and recovery enhancements

A new tablespace history file identifies log files needed for a particular table space recovery. Log files that aren’t needed are skipped, resulting in faster tablespace recovery. You no longer have to specifying Coordinated Universal Time (CUT) for rolling forward to a point-in-time. Instead, you can now specify local time, eliminating any confusion from converting local time to CUT time.

The DB2DIAG.LOG has been split into two files. The Administration Notification Log will contain messages for use by DBAs and systems administrators. Additional information will supplement SQLCODE error information. The level of information is controlled by the new NOTIFYLEVEL database configuration parameter. Detailed diagnostic information will still be written to the DB2DIAG.LOG file. The DIAGLEVEL configuration parameter will still control the level of information (primarily for DB2 Support) written to that file.

New database maintenance mode

This new feature is akin to the ACCESS(MAINT) command available for some time on DB2 for z/OS and OS/390. You can use the DB2 V8.1 QUIESCE command to force all users off an instance or database and place these objects in quiesced mode so maintenance can be performed. This feature solves the problem of application servers or transaction monitors immediately reconnecting to the database after a FORCE APPLICATI/ONS ALL command has been issued. The UNQUIESCE command takes the database or instance out of maintenance mode so users can connect to the database without requiring a shutdown and restart.

REORGCHK enhancements

REORGCHK now includes an ON SCHEMA option to specify a particular schema.

RUNSTATS enhancements. RUNSTATS can now collect statistics on column combinations, on prefetching statistics on a table, and on index and index-to-table relationships. RUNSTATS can now also accept lists of indexes and columns on which statistics are to be collected. See “Guidelines for collecting and updating statistics” in the Administration Guide: Performance for more details.

Management by exception monitoring

The new Health Monitor is a server-side tool that can raise alerts when predefined thresholds are exceeded or when it detects that an instance is down. Based on the alert generated, the Health Monitor can send an email or issue a page to a predefined email or pager address, trigger a script, or run a task through the new Task Center.

A health indicator is tied to a database configuration or database manager configuration parameter and a threshold setting. The Health Monitor checks the state of the system against these health indicators when determining when to issue an alert. Health Beacons are located on all Control Center windows. You simply click on a Beacon to access the Health Center GUI for configuring and interfacing with the Health Monitor.

Figure 1 shows an alert generated by the Health Monitor and recorded by the Health Center. Looking at the figure, you can see that the monitor sent the alert because the catalog_cache threshold of 70 percent is below the 80 percent warning threshold. Figure 2 gives a detailed description of the parameter involved and shows the recommended settings.

Figure 1: An alert sent by the new Health Monitor.

Figure 2 : A Health Center recommendation.

Simply click on the Launch button to start the Memory Visualizer and analyze how catalog cache memory is being used.

Event monitor and snapshot enhancements

You can create event monitors that write to tables instead of to files or pipes. This enhancement makes it easier for DBAs to correlate event monitor data. You can write customized queries to query tables containing event monitor data and produce reports for your items of interest. And, you can now take snapshots through SQL table functions, the output of which can be joined to other tables or written to a file. Previously, that output could only be embedded in programs that used the Administrative API.

What’s in a Name?

The new release brings a new alphabet of DB2 editions.
DB2 Enterprise Server Edition (ESE) combines the former DB2 Enterprise Edition and DB2 Enterprise-Extended Edition. ESE includes a new optional DB2 Database Partitioning Feature for creating multiple database partitions on a single hardware (SMP) server or across a cluster of hardware (MPP) servers. DB2 Workgroup Server Edition (WSE), formerly called Workgroup Edition, is designed for departmental or small business environments with a small number of internal users. Workgroup Server Unlimited Edition offers a simplified per-processor licensing model for departmental or small business environments that have Intranet users or that find per-processor licensing more suited to their needs. DB2 Personal Edition (PE), now merged with DB2 Satellite Edition, provides a single-user database engine ideal for deployment to PC users. PE, which enables remote management, is designed for occasionally connected or remote office implementations that don’t require multi-user capability.

Application Development

DB2 V8.1 takes existing XML support to new levels. The XML Extender in V8.1 supports Web services with the Web Services Object Runtime Framework (WORF) tool set. Web services are XMLbased functions that can be started from the Internet. The new XML Extender also lets users send or retrieve XML documents from MQSeries message queues.

The DB2 V8.1 Universal Developer’s Edition incorporates WebSphere Studio and WebSphere Application Server. WebSphere Studio makes it easy for developers to create, publish, and maintain dynamic Web applications. And with WebSphere Application Server, developers can create enterprise Java applications that access DB2 databases.

Other enhancements for application development include:

SQL in external UDFs and methods

External UDFs can now contain read-only SQL statements. Both static and dynamic SQL are supported. PROGRAM TYPE MAIN stored procedures no longer have the limit of 90 parameters.

CALL statement

The new CALL statement is fully compiled, which means that it can be dynamically prepared in command line interface, open database connectivity, embedded SQL, Java database connectivity, and SQLJ. Don’t use host variables for procedure names when invoking a stored procedure with the CALL statement if you want to use package cache efficiently and preclude additional compilation and reads to the catalog.

Development center

The new Development Center contains tightly integrated application development tools and add-ins that enable rapid application development. Developers can easily create applications that use DB2 stored procedures and UDFs. The Development Center fully integrates WebSphere application development.

With the Development Center you can:

      • Create, build, and deploy Java and SQL stored procedures
      • Create, build, and deploy SQL table and scalar UDFs and UDFs that read MQSeries messages, access OLE DB data sources, or extract data from XML documents
      • Deploy stored procedures using the integrated debugger
      • Export and import routines and project information.

The Development Center also provides an add-in for each of the following development environments:

      • Microsoft Visual C++
      • Microsoft Visual Basic
      • Microsoft Visual InterDev
      • Borland Kylix
      • Borland C++Builder.

You can launch the Development Center as a stand-alone application or from the Control Center, Command Center, or Task Center.

SQL enhancements

The new INSTEAD OF trigger extends the ability to update views. Using an INSTEAD OF trigger, the update operation against the view gets replaced by the trigger logic, which performs the operation on behalf of the view.

New Informational Constraints are rules that can be used in query rewrite but aren’t enforced by the database manager. DB2 can use the constraints for query optimization when applications have verified the data. New options on the ALTER TABLE statement ( ENABLE QUERY OPTIMIZATI/ON or DISABLE QUERY OPTIMIZATI/ON ) instruct DB2 whether or not to use this new capability.

SQL Assist in V8.1 includes an easy-to-use interface, help for writing table joins, SQL syntax checking, and the ability to copy and paste SQL statements.


Philip Gunning started Gunning Technology Solutions, LLC in 2003. He is a Principal Consultant specializing in DB2 with Gunning Technology Solutions. You might even call him a DB2 Evangelist. Phil has over 17 years experience in the Information Technology industry. Prior to starting his own company, Phil held senior positions with DGI and others,and has consulted with Fortune 500 companies and with government. Phil has presented at IDUG North America, IDUG Europe, and DB2 User Groups all over North America. You can reach him at

Contributors : Philip Gunning
Last modified 2006-07-14 02:04 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone