V5R3 Advances DB2 UDB for iSeries

June 16, 2004 David Morris

When you look at reliability, security, and ease of use, DB2 Universal Database for iSeries is ahead of any other database and is the main reason to have an iSeries. The V5R3 enhancements to DB2 UDB for iSeries keep it ahead and add lots of neat new features. Along with SQL enhancements, V5R3 delivers all kinds of other improvements. For example, you can reorganize a file while it is being used, automatically set up journaling of all tables in a library, or set up partitioned tables.

With V5R1, IBM began rewriting the underlying support for DB2 UDB on the iSeries to make it easier to share code between platforms. To minimize risk, IBM has been gradually cutting over to the new code base. With V5R3, the new code is used much more frequently, resulting in faster queries without sacrificing reliability.

STATISTICS AND OPTIMIZATION

The query engine that runs queries on the iSeries has undergone some radical changes that began on V5R2 and continue on V5R3. Major parts of DB2 now use a more flexible object-oriented design. The new design makes it easier for IBM to share code across the various platforms that DB2 supports and provides a better foundation for future enhancements.

The SQL Query Engine (SQE) is the newly redesigned object-oriented version of the query engine. The SQE is replacing the Current Query Engine, also known as the Classic Query Engine, in a staged release. Along with a better design, the SQE takes advantage of statistics that allow it to make better choices when implementing a query resulting in faster queries. The SQE Statistics Manager makes recommendations based on information gathered in background processes. Unlike the Classic Query Engine, the SQE builds lightweight memory resident structures instead of temporary indexes to further improve performance.

SQL-based queries originating from ODBC, JDBC, CLI, Query Manager, and SQL embedded in a program or entered interactively can use the SQE. The Query Dispatcher looks at SQL-based queries and decides whether to pass the query to the SQE or Classic Query Engine. Queries originating from OPNQRYF, Query/400, and the QQQQry API always use the Classic Query Engine. In some cases, the SQE will not be able to run a query, so it will invoke the Classic Query Engine.

Another component of the SQE is the Plan Cache. The Plan Cache is a repository of access plans from queries that were optimized by the SQE. The Plan Cache allows reuse of a query access plan when executing similar queries. Access plans in the Plan Cache are not static and can be adjusted during execution to take advantage of new information and indexes. This eliminates the need to re-optimize queries, resulting in faster processing times.

MANAGE DATABASES MORE EFFECTIVELY

Many iSeries shops can’t afford to shut down operations to reorganize data in physical files. The Reorganize Physical File (RGZPFM) command has been enhanced to allow reorganization of files without obtaining an exclusive lock, and also to support starting, stopping and resuming. You control locking level with the LOCK keyword, and you specify whether a reorganize can be stopped and restarted with the ALWCANCEL keword. Go to Reorganize Physical File Member, in the Information Center, to learn more about these new keywords.

With V5R3, DB2 UDB for iSeries supports partitioned tables. You need to have DB2 Multisystem installed on your system to use partitioned tables. Partitioning allows data to be stored in more than one member. Partitioning will allow you to have larger tables. Without partitioning, there is a maximum of 4,294,967,288 rows in a table, or a maximum size of 1.7 terabytes. Each partition of a partitioned table is still restricted to this size; however you can have many partitions in a table.

Each partition can be saved, restored, exported from, imported to, dropped, or reorganized independently. You can also use partitions to delete sets of records grouped in a partition, instead of processing individual rows. Deleting a partition can be significantly faster than deleting rows using search criteria. Partitioned tables also allow you to split a file between more than one logical partition on a single iSeries system and also to split a file between several iSeries systems.

For the most part, a partitioned table created on an iSeries server looks like a database file with more than one member. This allows you to use standard CL commands to work with partitioned tables.

It is easy to forget to start journaling when you create or change a physical file. You can avoid such problems by creating a data area named QDFTJRN in any library where you want to automatically start journaling. The new data area contains the name of a journal, and as long as the person creating the physical file has adequate authority, journaling will be started to the journal named in the data area. You specify the journal library in the first 10 positions, and the second 10 positions contain the journal, and the last five positions contain *FILE. You can also specify *NONE in the last five positions to prevent journaling from starting.

If you use constraints, you have probably had a constraint enter a check pending status. This happens when data in a table doesn’t meet the criteria imposed by a new constraint. The constraint goes into a check pending status until the data is corrected or the constraint is changed. Operations Navigator now has a facility to review check pending constraints and allows you to identify and either correct or remove rows that don’t meet the constraint criteria. To view constraints that have been placed in a check pending state, start Operations Navigator and expand the system name and databases. Right-click the database you want to use, and select “manage check pending constraints.”

If you deal with data that uses a varying-length encoding scheme, you will be pleased to learn that DB2 UDB for iSeries now supports UTF-8 and UTF-16 encoding. Specify a CCSID of 1208 for UTF-8 and 1200 for UTF-16.

SQL ENHANCEMENTS

One of the best new features of V5R3 is support for materialized query tables. A materialized query table is a table whose definition is based on the result of a query. You can use materialized tables to store summary information and use that information in other queries. Data in a materialized query is updated in real time or on demand, depending on how you define the materialized query table.

You use CREATE TABLE to create a materialized query and supply a select statement instead of a list of field definitions. The INITIALLY DEFERRED and DEFERRED clauses determine whether data is updated on demand or as data in the underlying tables changes. You use REFRESH TABLE to update data on demand that is represented in a deferred materialized query. See “creating and altering a materialized query table,” on IBM’s Information Center Web site, to learn more about materialized queries.

A new SQL diagnostics area provides information about the last SQL statement run in a program. The SQL diagnostics area contains all of the information in the SQL communications area (SQLCA), along with connection information and a much longer message area. The old message limit was 70 characters, which made it almost impossible to return meaningful messages and led many developers to use cumbersome workarounds.

Programs that use an SQL diagnostics need to declare a stand-alone six-character SQL state variable. You also need to declare a variable to hold the SQL diagnostics area that can be as large as 32767 bytes, which is quite a bit bigger than the 136 byte limit for an SQL communications area. You use the new GET DIAGNOSTICS SQL instruction supplying your variable to load information about a statement that has just run. You can find more information on the SQL diagnostics area in the Embedded SQL Programming manual (in PDF format).

Other databases, like Oracle, provide support for sequences. A sequence returns unique values that are managed by the database. On V5R2, DB2 UDB for iSeries added support for identity columns, which have a similar purpose to sequences; however, an identity column is part of a table where a sequence is an independent object. You use a CREATE SEQUENCE statement to create a sequence and supply starting and increment by values.

With V5R3, you can return a result set directly from a stored procedure. Now, in addition to returning output parameters, stored procedures have the capability to return a result set associated with a cursor opened in the stored procedure. The calling application can then issue fetch requests to read the rows of the result set cursor.

Also new with V5R3 is the capability to automatically commit transactions when returning from a stored procedure. This should make it a lot easier to use commitment control in applications that use stored procedures. If you specify COMMIT ON RETURN YES when you create a stored procedure, the database manager issues a commit if the procedure returns successfully. If the procedure returns with an error, a commit is not issued. The commit operation includes the work that is performed by the calling application process and the procedure.

CONTINUED COMMITMENT

With V5R3, IBM once again demonstrated its commitment to DB2 UDB for iSeries. New features were added and existing features were updated. There are too many enhancements to cover in detail, but once you start using this new version of DB2 UDB for iSeries, I am sure you will be impressed.