Indice de contenido
   DB2 Universal Database Version 7 Family Enhancements

   Stored Procedures

   Business Intelligence Enhancements

   Data Management Enhancements

   DB2 Family Compatibility Enhancements

   Miscellaneous DB2 Family Specific Enhancements

   OLAP Enhancements

   Automated Summary Table (ASTs)

   OS/390

   Utility Improvements

   Faster Restart in Data Sharing

   Statistics Reporting

   Summary

This report is to examine DB2 Universal Database Version 7 and provide an overview of the enhancements for all DB2 platforms, including OS/390, UNIX, Windows, OS/2, and related platforms. The most notable change with Version 7 is that the family is becoming just one word: DB2. DB2 for OS/390 benefits from features that were only known on the other platforms, and UNIX, Windows, OS/2 likewise receive features that were only on the OS/390 platform. A large set of common enhancements has been added across all platforms. By necessity, other enhancements must be platform specific. All members have more features in Version 7, allowing them to achieve the kind of performance and availability on their respective platforms that one would expect from DB2, especially in the area of e-business.

The DB2 family has grown very close in the last few years with its foundation deeply rooted in being the most scalable and reliable database in the industry. With DB2 Universal Database Version 7, it has been designed from the ground up for Dot Coms.

It is very obvious with this release that IBM is targeting a set of application development tools that customers can use to develop for both DB2 for OS/390 and also DB2 for UNIX, Windows, and OS/2. SQL procedures, for example, have now been rolled out across the DB2 family on OS/390, AS/400, UNIX, Windows, and OS/2. IBM is not only bringing application development tools together, they are driving the SQL language features closer together as well. The XML extender is another prime example, as are identity values, nested stored procedures, externalized savepoints, declared global temporary tables, DCLGEN capability, and validate (RUN). SQL stored procedures can be created and maintained with the Stored Procedure Builder for any DB2 server platform. Some of the many SQL enhancements implemented across the family are searched UPDATE and DELETE allowing self-referencing subqueries and ORDER BY elements not required in the SELECT list.

IBM is also continuing to enhance common administration capabilities across the DB2 family, with Control Center available on all platforms and enhancements that include the addition of a Data Warehouse Center; the latter can manage warehouse operations across the DB2 family and beyond.

Consistent DB2 and DB2 middleware solutions across mainframe and distributed server platforms provide the opportunity for savings in application development/deployment/maintenance costs for multi-platform environments and for savings in training costs for application developers and DBAs in multi-platform IT organizations. This solution also provides a cohesive, standards-based architectural foundation for multi-platform, network computing data management environments, allowing us to better leverage our existing technology and skills.

DB2 UDB Version 7 is the only database that fully integrates e-business, business intelligence and content management. It delivers improved performance, availability, and scalability for data warehouse applications and e-business. It has been enhanced in the areas of access, management, availability, scalability and applications.

We will discuss the family enhancements first in a section that breaks the enhancements into the three groups of e-business, business intelligence, and data management. We will then examine features that have been added to each platform; these features were previously available on the other platforms. Lastly we will examine platform specific features.

DB2 Universal Database Version 7 Family Enhancements

E-business enhancements
In order to function efficiently in e-business, a database must provide functionality that allows for fast access across the network and the ability to handle a large transaction volume for web users. DB2 UDB Version 7 continues to be the leader in this technology by providing high performance enhancements to support this requirement.

XML support
An XML (Extensible Markup Language) extender has been added to the DB2 extenders. This will allow for a method to mark the meaning of the data so it can be more easily used. It is extensible in that the language itself is a meta-language that allows you to create your own language, depending on the needs of your enterprise.

For web-based businesses, DB2 Version 7 provides more native support for XML via a DB2 extender. It is now possible to combine structured XML information with traditional relational data. You can choose whether to store entire XML documents in DB2 as an XML Character Large Object (XMLCLOB), or you can map the XML content as traditional data in relational tables. The XML Extender adds the ability for powerful searching of rich data types of XML element or attribute values, even for nontraditional XML data types.

Incoming XML documents can be decomposed into traditional SQL data types and placed in columns. Traditional SQL data can also be used to compose outgoing XML documents. Mapping methods have been implemented to provide for the transformation between XML documents and relational data. This will allow for decomposing an XML document into one or more pieces for storing in tables. Data in existing relational tables can be used to compose XML documents.

By using the XML Extender and its set of defined UDTs, an application can store entire XML documents as column data in an application table. The UDTs also provide for the composition or decomposition of the contents of XML documents from or into an XML collection. These XML collections can be made up of one or more regular DB2 tables. These user-defined functions provide a long list of capabilities. Also Net.Data now has built-in exploitation to generate XML tags rather than manually entering them.

Stored Procedures

Stored Procedure Builder
The Stored Procedure Builder (SPB) is a graphical Windows application that runs on Windows, AIX, and Solaris; it supports the rapid development of DB2 stored procedures written in either Java or SQL Procedure Language (the SQL Procedural Language is part of the SQL3 standard). It can work as a Microsoft Visual Studio plug-in, or with Microsoft Visual Basic, IBM VisualAge for Java, or stand-alone. The SPB supports the entire DB2 family from a single development environment. It has been further enhanced in Version 7 to simplify the process of building, debugging and deploying stored procedures.

Nested Stored Procedures
Now we have the ability to call a stored procedure within a stored procedure. There is support for C language or the SQL Procedure Language, but stored procedures must be fenced and cataloged. Results can be passed to the calling procedure or to the client. Be careful with nested stored procedures because troubleshooting problems can be difficult if there are too many nested levels.

Stored Procedure COMMIT/ROLLBACK
In the first instantiation of stored procedures there was no COMMIT at all. Then came the ability to COMMIT ON RETURN, to reduce the network traffic. Now the ability to commit or rollback in stored procedures has been added. This will have implications only on new stored procedures or on careful enhancements to existing stored procedures.

SQL Procedures Language
SQL Procedures Language is a procedural language that was designed only for writing stored procedures. It is available across the entire DB2 family including the AS/400. Its major benefit is that users can create stored procedures very quickly using a simple, easily understood language, without the headaches of precompilers, compilers, link editors, binding, and special authorizations. This is because stored procedures written using SQL Procedures Language will be mostly managed by DB2; this automates the process and allows programmers and users to simply write the logic and pass it off to DB2. The language itself is primarily SQL (DML and DDL) with local variables, cursors, assignment statements, flow control, and signal/resignal conditions (normally used for error handling). The primary language statements that show the strength of this approach are:

IF, CASE and LEAVE LOOP, REPEAT, and WHILE FOR, CALL, and RETURN GET DIAGNOSTICS SIGNAL and RESIGNAL

The real difference with the SQL Procedures Language is how it becomes a stored procedure, since all one does is write a "CREATE PROCEDURE...LANGUAGE SQL...name: BEGIN...END name" DDL statement. All the code is in the body of the create statement.

Now that DB2 supports the SQL Procedures Language, we can all begin migrating those applications written using Informix SPL, Oracle PL/SQL, Sybase Transact SQL, and Microsoft SQL Server Transact SQL systems to DB2. There are some differences in which SQL Procedures Language features have been delivered on each of the platforms. Figure 1 below shows both the similarities and differences (source: IBM).

Figure 1

Remote Access
Many improvements have been made for DB2 Connect, JDBC, and ODBC. Support has been added for ODBC 3.0 support and initial JDBC 2.0 support. Kerberos security for Windows 2000 is also supported.

Declared Temporary Tables
The support for declared temporary tables provides a new way to temporarily hold or sort data.

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session, not only for a Unit of Work (UOW). The table description does not appear in the system catalog. It is not persistent and cannot be shared.

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can also be dynamically prepared. Each session that defines a declared global temporary table of the same name has its own unique instantiation of the temporary table. When the session terminates, the temporary table is dropped. With declared temporary tables, some of the locking, DB2 catalog updates, and DB2 restart forward and backward log recoveries that are associated with persistent base tables are avoided. No authority is required to issue the DECLARE GLOBAL TEMPORARY TABLE statement, but an authority will be required to use the new user temporary table space where the table will be materialized.

Declared Temporary Tables may be useful for applications that need to extract data from various sources and use them in SQL joins, or for data that needs to be used repetitively, or kept separate from other OLTP processes. They could also be used as staging areas for data that comes from various sources so that the data may be manipulated before storing it permanently in regular tables.

Two of examples of the syntax for Declared Temporary Tables are shown below.

 DECLARE GLOBAL TEMPORARY TABLE
SESSION.ACCOUNT
      LIKE CLAIMS.ACCOUNT
  INSERT INTO SESSION.ACCOUNT
      SELECT * FROM CLAIMS.ACCOUNT
      NOT LOGGED

  DECLARE GLOBAL TEMPORARY TABLE
SESSION.ACCOUNT
      AS
      (SELECT * FROM CLAIMS.ACCOUNT)
  DEFINITION ONLY
  NOT LOGGED

There are some differences in the way declared temporary tables have been implemented on DB2 UDB for UNIX, Windows, OS/2 versus DB2 UDB for OS/390. (These differences are temporary and are simply a matter of rollout) The differences are noted in Figure 2 (Source: IBM):

Figure 2

Business Intelligence Enhancements

Several enhancements have been made in the processes supporting business intelligence operations. Tools have been introduced to provide us with the ability to perform extensive analytic processing on data from various sources.

Control Center Enhancements
A great feature of DB2 Version 7 is that the new features are seamlessly integrated into the Control Center. This makes them accessible using the same interface that we have been using since DB2 Version 5. Many small improvements that might not be as widely advertised include: a table editor, some log enhancements, enhancements to automate summary tables, and the ability to rename a table space (the last two are non-OS/390 only).

During a recent demonstration of DB2 Version 7, the presenter casually went into the Command Center, ran a query, and then began editing the table in the result window. Using the SHOW SQL feature, this SQL was saved in a script for later processing. This feature, along with several other changes to the Control Center, provides a richer functionality. Many of these features should be evaluated to determine if they change standard DBA procedures and tools within your shop.

DB2 Warehouse Center and Warehouse Manager
The DB2 Warehouse Center and Warehouse Manager are tools that provide a way to access, manage, build, and control data warehouses built in DB2. You will have the ability to control and govern queries and manage resources used by the data warehouse through the use of Query Patroller (DB2 for UNIX, Windows, OS/2 only). It provides you with agents that will manage the flow of data between the sources and targets in the warehouse. One very nice feature is that it provides the means for cleansing data, generating keys, and pivoting data by means of Java stored procedures and user-defined functions. There are also extensive reporting capabilities. An unlimited license for QMF for Windows is included in the product. QMF gives you the ability to query the database for reporting purposes, develop applications, and protect the server resources.

These warehouse functions are managed through a new component called the Data Warehouse Center. The Data Warehouse Center is itself incorporated into the Control Center. The Data Warehouse Center includes functions to define data sources, manage data movement and transformation, populate the metadata repository, and create generic schema models including the logical star model for the DB2 OLAP Server. This functionality, which was previously available in Visual Warehouse, has been enhanced to include more transformations and an improved user interface.

A set of warehouse transformers is included for data cleansing, generating key columns and period tables, and inverting and pivoting data. A wide selection of statistical transformers performs such functions as: basic statistics, subtotals, rollups, cube calculations, moving averages, regression, correlation analysis and other functions. In all, there are over 100 built-in transformers. You can also build user-defined functions to create your own transformers.

There is an important family side benefit with the DB2 Warehouse Manager. It can be used for either OS/390 to OS/390 point-to-point data movement or point to point to any member of the DB2 family. With federated data access provided by DB2 DataJoiner integration, the warehouse center can be used to manage the process of moving data from Oracle to DB2 as well.

When this product is purchased with DB2 for OS/390, it not only includes the workstation GUI control center, but also a full set of procedures for managing the warehouse and data natively on the 390. The replication controls with Warehouse Center will also allow for heterogeneous data sourcing into a warehouse on any of the possible target platforms.

With DB2 Warehouse Manager for OS/390, the QMF family includes QMF for Windows, QMF for OS/390, and QMF High Performance Option (HPO), for query and management of your 390 warehouse.

Other helpful features
In addition to the enhancements to the Control Center, many improvements have been made to assist with the day-to-day usage of DB2. Launchpads have been added to help guide you through such tasks as installation. There are also launchpads for the Data Warehouse Center to help simplify the population of the warehouse. There is also additional help for those new to DB2 through the First Step tool and the Quick Tour presentation.

Some integration enhancements have been added for building applications with the Microsoft Visual Studio product. A Visual Studio plug-in is available for DB2 management tools and wizards that simply plug into the Visual C/C++ component of the Visual Studio IDE. This will help with the development of client applications, building of stored procedures, and assist with writing SQL. A toolbar is now available to launch DB2 development and administration tools from Visual Studio.

DB2 UDB also supports some new features of Windows 2000. Most important is the support of DB2 authentication through the sign-on facilities of Kerberos. There are some additional integrations because the DB2 Control Center can be launched from the Microsoft Management Console. DB2 services and protocol configurations will also be published in the Active Directory so that client applications can easily connect to DB2.

Data Management Enhancements

External SAVEPOINTs
Savepoints enable milestones within a transaction (unit of recovery) to be demarked. An external savepoint represents the state of data and schema at a particular point in time. Data and schema changes made by the transaction after the savepoint is set can then roll back to the savepoint, as application logic requires, without affecting the overall outcome of the transaction.

SAVEPOINT name ABC (other options)
ROLLBACK TO SAVEPOINT name

This enables milestones within a unit of recovery and represents the state of the data and schema at a particular point in time. You can use ROLLBACK to restore to a savepoint. This would be useful when you have reached a point during a UNIT-OF-WORK and need to back out without ROLLBACK over the entire UNIT-OF-WORK. There are subtle differences in the way this works on different family members. On the OS/390 platform you can name the individual SAVEPOINTs and then ROLLBACK to whichever point is required, based on the application processing requirements skipping over individual savepoints, while on the other platforms there can be only one active savepoint. This difference of course is just a matter of rollout before savepoints will operate in the same manner.

Identity columns
Identity columns provide a way for DB2 to automatically generate unique, sequential, and recoverable values for each row in a table. This new identity column is defined with the AS IDENTITY attribute provided in column definition.

CREATE TABLE MY_TABLE
(ACCOUNT INTEGER NOT NULL
  GENERATED ALWAYS AS IDENTITY
  START WITH 100000
  INCREMENT BY 100
  CACHE 20

Each table can have only one identity column defined to it. Identity columns are ideally suited for the task of generating unique primary key values such as employee number, order number, item number, or account number. The columns must be a data type that is arithmetic and exact, which means that SMALLINT, INTEGER, BIGINT, or DECIMAL with a scale of zero can be used. It is also possible to use a distinct type based on one of these types. Single and double precision floating point data types are not listed since they are considered to be approximate numeric data types, not exact.

Identity columns can also be used to alleviate concurrency problems for situations caused by application generated sequence numbers.

Self-referencing Subselect on UPDATE or DELETE
In prior releases, a searched update or delete could not contain a where clause referring to the same table. This restriction has been removed for searched updates and deletes, but does not apply to positioned updates and deletes.

ODBC/CLI Static Execution
The ability to execute an ODBC/CLI dynamic application as a static application has been introduced in Version 7. This will improve performance and increase security for applications that simply cannot make use of static (or "canned") queries. Performance gains will easily be realized if these queries are executed frequently.

Precompiler Services
The previous method of precompiling in one job step, and then passing the output to another job step to invoke the compiler has been given a new alternative. The precompiler services are now able to precompile and compile in one step. This will allow for improved integration for host language support, more consistent rules, easier portability, and greater flexibility. In the initial rollout for OS/390 this will only be available for COBOL.

DB2 Family Compatibility Enhancements

DB2 for UNIX, Windows, OS/2 VALIDATE(RUN)
DB2 UDB for UNIX, Windows, OS/2 now supports incremental binds by allowing support for VALIDATE(RUN) on a package bind. This allows for static SQL statements to be bound at run time during the execution of the application program. This can help avoid problems during the bind process for static SQL, where objects may not exist during the initial bind, or the proper authority may not be in place at that time, or there are declared temporary tables in the package.

DB2 for OS/390Scrollable cursors
Scrolling in applications has always presented problems both for the application programmer and for proper performance use. It was one issue if the scrolling was only moving forward and could be performed by walking an index or walking through a result set. Scrolling backwards posed an even more difficult situation. When scrolling forward, the only truly effective way from a performance perspective was to use an ascending index. The need to scroll in reverse required either some sophisticated programming or another index, this time a descending one. With the introduction of a scrollable cursor to DB2 for OS/390, scrollable cursors are now implemented across the family. Now we can move more of the work to perform this operation into SQL (fetch the next row, fetch the previous row). You will be able to move the cursor forward or backward for a given number of rows. In addition, this movement can be for either relative or absolute positioning in the result set.

Cursor Declaration
Scrollable cursors are defined using parameters on the DECLARE CURSOR statement. The following statements show the new cursor types in Version 7:

DECLARE cur1 INSENSITIVE SCROLL CURSOR FOR ...
DECLARE cur2 SENSITIVE STATIC SCROLL CURSOR FOR ...

Both sensitive and insensitive scroll cursors will use a fixed, declared temporary table for the result set. While the creation of the temporary table is done automatically by DB2, static cursors require a declared temp database and predefined table spaces. An insensitive scroll cursor is not updateable, and will not show changes done by the user or by other users. Sensitive static cursors will show changes made by the user and other users, except inserts. Naturally, inserts are not done via cursors.

The other keyword to explain is STATIC. Sensitive static cursors use a declared temporary table. In contrast, a sensitive dynamic cursor would directly access the base table. Sensitive dynamic cursors are not included in DB2 Version 7, but are under consideration for the future.

Static scroll cursors will have their result table kept in sync with the base table as each row is fetched. There will be a fixed number of rows in the result table, and it will be dropped when the cursor is closed.

Fetching With Scrollable Cursors
There are two new groups of keywords for the FETCH statement. The first clause for a scrollable cursor is SENSITIVE or INSENSITIVE. This value defaults to the value on the DECLARE CURSOR statement.

The next keyword is the position within the result table. Valid values are:

• NEXT (the only current value and the default in Version 7)
• PRIOR
• FIRST
• LAST
• CURRENT
• BEFORE
• AFTER
• ABSOLUTE integer constant or host variable
• RELATIVE integer constant or host variable

Visibility of updates is a key concern with scrollable cursors. Your own cursor's changes via positioned updates and deletes will be visible to you because the result is updated at the same time as the base table. These changes are not made permanent until commit time. Other people's changes, or changes you make outside of the cursor, will be visible to your cursor only if you specified FETCH SENSITIVE and the changes are committed. It is not possible to see uncommitted changes from another person.

With sensitive cursors, it is possible for data changes to the base table to create holes in the result table. A delete hole is created when a corresponding row in the base table is deleted. This row will not appear again in the result table if the row is re-inserted. An update hole is created when the values in the row no longer qualify it as part of the result table based on the predicates in the WHERE clause of the DECLARE CURSOR statement. Users will receive an SQLCODE when they attempt to retrieve the row, but the row can reappear on a subsequent sensitive fetch on the update hole.

An interesting consideration arises when updating using the isolation level of cursor stability (CS) with scrollable cursors. When a positioned update or delete (WHERE CURRENT OF) is requested, DB2 will lock the row, reevaluate the predicate, and compare by column value to determine if the update can be allowed. Repeatable Read (RR) and Read Stability (RS) lock every row or every row qualifying as a stage 1 predicate, so their use is not affected. Uncommitted Read (UR) takes no locks, so its use for updates should be discouraged.

Example
The following example shows an inquiry to a frequent flyer database from a customer checking her last five air transactions.

CREATE TABLE AEROMILES
(FLYDATE DATE,
MILES SMALLINT,
DESCRIPTION CHAR(30))
IN MILESDB.MTS;

DECLARE MILECURS STATIC SENSITIVE SCROLL CURSOR FOR
      SELECT FLYDATE, MILES, DESCRIPTION
             FROM AEROMILES
             ORDER BY FLYDATE ;

OPEN MILECURS;

FETCH LAST MILECURS;

DO I= 1 TO 5;
FETCH PRIOR MILECURS INTO :FLYDATE, :MILES, :DESCRIPTION;
       Display logic...
END;

Usage Considerations For Scrollable Cursors
Scrollable cursors will be used mostly in a cross-platform environment or in a conversational programming environment. In CICS and IMS/TM, the pseudo-conversational programming style is often used, which means a given business transaction is actually processed over several CICS or IMS/TM transactions. At the end of each transaction, locks are released and the cursor position is lost.

Fetches are still one row at a time. To retrieve a screen's worth of data, a loop is required to get the number of rows required. If data is needed once in a forward direction only, scrollable cursors are not needed. INSENSITIVE or SENSITIVE STATIC SCROLL cursors should be used if you want to work with a fixed answer set without relying on the Optimizer to choose a work file. If only the first few rows of an answer set are needed, consider using FETCH FIRST n ROWS ONLY on the DECLARE CURSOR statement.

Ensure you have sufficient table space storage in your TEMP database. If data does not need to be current, use INSENSITIVE on either the DECLARE or the FETCH. As before, isolation level CS should be used in most cases. Remember to commit changes to make them available and use cursors WITH HOLD to retain position.

Unicode Data Storage and Manipulation
Unicode support is another very important enhancement. Support for Unicode will help with support across multinational boundaries. It is an encoding scheme that will allow for the representation of codepoints and characters of virtually all languages. Unicode character encoding standard is a variable length, character encoding scheme, supporting multiple encoding schemes that include characters from most of the world's languages.

Fetch for Limited Rows
The ability to tell DB2 to fetch only the first or top ‘n' rows has been a much anticipated requirement for a long time. There are ways in SQL and the application program to do this; however, they are not exactly efficient. With this new clause DB2 added the functionality with improved performance, as the clause sets a maximum number of rows that can be retrieved through a cursor. The application will now have a way to tell DB2 that it does not want to retrieve more than some fixed number of rows, regardless of how many rows qualified for the result set.

The functionality in Version 6 and earlier releases allowed for the OPTIMIZE FOR n ROWS clause, which was meant to provide additional information to the Optimizer about the intent. This clause, while useful, could allow the entire answer set to be retrieved. In contrast, the FETCH FOR n ROWS ONLY stops processing after the specified number of rows. This provides direct control to the application programmer on the size of the result table. For example, an implementation limit on the number of rows displayed for an online screen can now be enforced.

Row Expressions
Row expressions are an extension to predicates to allow more than one set of comparisons in a single predicate using a subquery that returns more than one column and even more than one row. Row expressions will give us the ability to use the following syntax:

SELECT * FROM TABLE
WHERE (col1, col2, col3) IN (SELECT cola, colb, colc from TABLE)

This predicate will be true when all three columns on the left equal the three values in any single row returned in the result set from the subquery. This will also allow the use of quantified predicates with row expressions.

UNION Everywhere
UNION and UNION ALL will now be supported in a view, table expression, predicates, inserts and updates: hence the enhancement "UNION Everywhere." The ability to have UNION statements defined in views has been a desired feature for a long time. It has been allowed in DB2 on the other platforms for several versions, and will now become a feature on the OS/390 platform. Over the last several years, the need for UNION statements has diminished somewhat with the additions of outer joins and the CASE expression. But other features to be added in the future will require not only UNION statements, but also UNION statements defined in views. The whole concept of using common table expressions for recursive SQL will require this in order to be implemented.

Control Center
Many of the enhancements for Version 7 have been added to the Control Center in support of DB2 family enhancements for OS/390. IBM has stated many times that the Control Center will be a single point of entry for controlling DB2 family members. The Control Center now has more support for DB2 for OS/390 functions, such as utility execution, dataset allocation and DDL generation.

Utility Support
Support for utility wildcarding now gives us the ability to execute utilities against a list of objects matching a specified pattern of matching characters. Now you will be able to execute a utility for a specified list of objects. You could then create a utility procedure that would allow you to run a mixture of several utilities against several objects with one command, making maintenance much easier for the DBA. There is also support for restarting utilities from the last committed phase(phase) or the last committed point(current). This will be available only for utilities originally started in the Control Center. The restart will be accessible through the Display Utility dialog.

Support is also now included for utility IDs. This is done in the new Tools Settings notebook. It allows for a utility ID template to be created using a variety of variables such as USERID and UTILNAME. You will be able to edit a utility ID to make it more meaningful before execution of the utility.

Dataset Management
You can also manage your datasets now via the Control Center. You will be able to retrieve dataset lists, and, depending on the type of data set, you can show, rename, and delete members. Datasets can also now be dynamically allocated for utilities through the use of data set templates used to automatically generate datasets based upon a defined set of criteria.

DDL Generation
If you have ever needed the DDL to recreate an object, you will appreciate this new feature, because the original DDL for object creation was frequently lost, or the object had been altered. Until Version 7, DB2 did not have an easy way to re-engineer DDL from the catalog. With Control Center there is now a feature to recreate database objects and dependent objects. The output then can be saved to an OS/390 dataset (perhaps for input to SPUFI) or to a workstation file.

Integrated SQL Assist
Additional help is now available when you need to add SQL statements for triggers and views. SQL Assist helps you build SQL statements; it is available on Create Trigger window and Create View window. This assist helps in building a SQL SELECT statement which is needed in the AS clause of the CREATE VIEW statement. The SQL statement will then be inserted into the text area of the dialog after it is created.

Index Advisor
DB2 UDB for OS/390 users will now have use of the Index Advisor, which is a tool to assist you in choosing an optimal set of indexes for your table data. This tool was introduced to the other DB2 family members in Version 6.1. In Version 7 of DB2 for OS/390, an initial rollout of the product will occur via an informal download from the IBM DB2 for OS/390 site.

The Index Advisor reduces the need to design and define suitable indexes for your data, but certainly does not do away with all index design strategies. It will certainly help in finding the best indexes for a problem query or for a particular set of queries. It will be a real help for testing an index on a workload without having to create the index.

This utility will be able to work on a set of dynamic SQL statements (only dynamic SQL will be supported for the initial rollout; static will be delivered later) which have to be processed over a given period of time, and will handle SELECT, INSERT, UPDATE, and DELETE statements. In addition, it will use statistics from the system and user input to be able to evaluate strategies for given workloads. It will also be able to scan the catalog tables holding SQL from bound applications to make determinations and suggestions.

This process is performed by creating a modeling database for DB2 for OS/390 on a DB2 for UNIX, Windows, OS/2 workstation using configuration parameters to mimic DB2 for OS/390. One of the ways to use this tool will be through the Control Center.

Miscellaneous DB2 Family Specific Enhancements

DB2 for UNIX, Windows, OS/2

Authorities
A few new authorities have been added in Version 7 such as the LOAD and the USE OF TABLESPACE. The LOAD authority allows you to grant someone the ability to execute the LOAD utility without requiring DBABM or SYSADM authority. The USE OF TABLESPACE authority will restrict the user to only create tables in certain authorized tablespaces, without having to give database authority.

Rename Tablespace
This very helpful new feature allows you to rename a tablespace without having to drop and recreate the object. There is new, and very simple, DDL available to support this operation.

RENAME TABLESPACE mstabts TO yourts

>8 Character Userids
The USERID has been increased to allow for more than eight characters to be used. It will now allow for up to 30 characters in the majority of operating systems (Windows will also support Windows 64bit when it is available). This will be also supported for the AUTHIDs and Schema names. The new length support will also be supported by DB2 UDB replication.

Log Enhancements
The log enhancements include filtered log recovery, which is similar to DEFER processing during restart on DB2 for OS/390. Recovery of specified table, indexes, and tablespaces could be skipped during roll-forward processing. This allows a database to be brought back online without having to fix problems with broken database objects.

Logs will also be flushed when an online backup completes. This can save time spent for log management during online backups and guarantees that the images on tape at the time the backup completes are all that are required to perform a restore.

The active logs have now been increased from 4GB to 32GB to support the ability to handle longer- running transactions.

Data Links Manager
Data Links are links to files storing data such as multimedia, and they are managed via the DB2 Data Links Manager, which was developed to extend DB2's data management features to files stored in a file system outside of the database product itself. You have the choice of where to store your data; you can make this decision based upon how the data is accessed. There is a new data type of DATALINK, which gives control of the remote files to the database server so the files are treated as though they were part of the database product. With Version 7, the DB2 DataPropagator product can be used to replicate the DATALINK columns.

OLAP Enhancements

DB2 OLAP Server Starter Kit
DB2 combines the power of the Hyperion analytic engine to build and manage online analytical processing (OLAP) applications. In Version 7, IBM provides the DB2 OLAP Server Starter Kit with every WE, EE, and EEE product; this gives you a three port license to DB2 OLAP server. The starter kit includes OLAP spreadsheet plug-ins for Excel and Lotus 1-2-3 and an Administration Manager.

OLAP SQL Extensions
Before DB2 Version 7, the main two types of built-in functions were scalar and aggregate functions. Scalar functions operate at the row level and return a single value. Aggregate or column functions operate on a set of rows and return a single result. A new Version 7 function type operates on a set of rows and returns a single result per row. These are referred to generically as OLAP functions. Version 6 introduced RANK, row numbering, and cumulative functions, while Version 7 adds moving aggregates or sliding windows functions. A variety of reporting options allows totaling, subtotals, and row numbering.

Like most features within SQL, the best way to learn the new functionality is by example. One of the "all-time favorite" advanced queries has been to produce a top ten list. The coding of this query is difficult for most SQL programmers. Using the syntax with RANK, the query for the top ten salaries would look like:

WITH TT AS (
   SELECT EMPNO, DEPT, SALARY,
       RANK() OVER (ORDER BY SALARY DESC NULLS LAST) AS RSAL
       FROM EMP)
SELECT EMPNO, SALARY
   FROM TT
   WHERE RSAL <=10;

This query is readable and more likely to be understood and coded correctly. A more extensive example of the OLAP function is the sliding windows aggregate function from Version 7 demonstrated below.

select c.custid, sum(ti.amount) as sum,
count(*) over(order by sum(ti.amount) desc) as cust_count,
sum(decimal(sum(ti.amount),31,5))
 over( order by sum(ti.amount) desc )*100.00 /
  sum(sum(ti.amount)) over () as revenue_percent,
count(*) over( order by sum(ti.amount) desc )*100.00/
  count(*) over() as cust_percent
from trans t, transitem ti, acct a, cust c
where t.transid = ti.transid
 and t.acctid = a.acctid and a.custid = c.custid
group by c.custid

This query would yield the following result, for example:

Automated Summary Table (ASTs)

Automated Summary Tables
ASTs have been enhanced to allow for a "refresh immediate" replicated table. There can also be conversion between regular tables and AST; you can also refresh multiple tables concurrently (this is performed internally by looking for common groupings from the base table).

DB2 Spatial Extender
A spatial extender supports a tightly integrated geographical DBMS with the DB2 UDB product. This extender provides functionality to modeling spatial data for real-world entities (customer locations, cable locations and the like). It provides the ability to store, index, and query spatial data. This data can also be manipulated through a standard set of spatial operations and predicates that conform to the OGC (OpenGIS Consortium) and ISO standards. The spatial data is administered by a supplied set of tools and utilities.

DB2 Query Patroller Enhancements
Some significant enhancements have been added to the Query Patroller product; they will help you better manage your query workload in e-business and BI environments. Dynamic queries that come in from all platforms supported by DB2 CAE can be trapped and will be prioritized or prevented from executing if the cost is too high (as defined by the DBA). This gives you the ability to control queries that may be potentially damaging to your DB2 resources. There are also some new availability features such as the ability to have a query restart automatically if the database server is restarted after a failure.

The Query Patroller product is tightly integrated with the Control Center and has the ability to capture query history, such as who ran what queries, how long they ran, and what tables and columns are being accessed. It is now part of the DB2 Warehouse Manager product.

DB2 Net Search Extender
Does your e-business require the ability to do massive text searches with good response time? With the DB2 Net Search Extender you can do in-memory high-speed text searches. These searches can be on the entire word or on a phrase, and may be wild-carded or fuzzy. This extender has been designed to perform rapid searches and index data without locking database tables. Some of the initial stress tests with the extender showed a web site being hit 90 million times per day without degradation in performance.

DB2 Relational Connect
One of the packaging changes is the inclusion of a subset of DB2 DataJoiner functionality as part of DB2. Queries can be built to access data on DB2, Oracle and OLE DB databases within a single query. Data can be retrieved from multiple databases in real time. This will allow read-only support for DB2 and ORACLE. Other data sources can be accessed using the external table functions. Of course, DB2 DataJoiner can be used for a far wider range of data sources if necessary. This functionality will allow cross database joins within a single query for "federated database access."

OS/390

Catalog Migration
Before discussing some of the DB2 UDB for OS/390 specific enhancements, let's take a quick look at the migration process to Version 7. You do have the ability to migrate (and fallback) from Version 5 or Version 6 to Version 7. This migration will be very fast; the CATMAINT process for Version 7 occurs in three steps. The first step will do all the mandatory catalog processing, the second step will look for any unsupported objects, such as Type 2 indexes, and will issue messages for these found objects, and step three will be optional for migrating stored procedures. You will have to have the fallback SPE PQ34467 installed for a data sharing migration. Also, in a data sharing environment, only Version 5 and Version 7 can exist together or Version 6 and Version 7, not a combination of Version 5, Version 6, and Version 7.

Improved Optimization
To improve optimization we now have parallel processing supported for IN-lists and indexable correlated subqueries. Better performance will also be achieved due to the improved sort avoidance with ORDER BYs, where columns for the ordering do not have to be selected.

DB2 also now supports backwards scanning of indexes. This is useful when doing MAX or MIN functions. Prior to this improvement, you might need two indexes (an ASCending index and a DESCending index) for best performance for these functions. With this enhancement, either an ascending or descending index can be used for either MIN or MAX functions.

Online DSNZPARMs
Version 7 will introduce the infrastructure that some day will allow for approximately 60 of the most popular DSNZPARMS to be dynamically changed. Version 7 will allow for the changing of a subset of the DSNZPARMs without having to stop and restart the DB2 subsystem, causing an unwanted outage.

The advantage to changing ZPARMs dynamically is to be able to tailor parameters to the current workload. For example, an approved change to the EDM Pool size can be implemented sooner without a DB2 outage. It may also be desirable to change not only buffer pool size but also EDM pool size and checkpoint frequency for overnight batch processing.

The ZPARM member is changed dynamically in its entirety by activating a different ZPARM member. The "SET SYSPARM" command is used from an OS/390 console, a DSN session under TSO, a DB2I panel, a CICS or IMS terminal, or via an application or product using the Instrumentation Facility. The issuer must have SYSADM, SYSCTRL, or SYSOPR authority. The following forms of the SET SYSPARM statement can be used.

-SET SYSPARM LOAD (modname)

This loads the named parameter module.
The default name if not specified is DSNZPARM.

-SET SYSPARM RELOAD

The last named subsystem parameter module is loaded into storage.

-SET SYSPARM STARTUP

This loads the initial parameters from DB2 startup.

EDM Pool Considerations
Changing the size of the Environmental Descriptor Manager (EDM) pool is one of the more important items, and one of the trickiest. It is quite possible that the changes in sizes that you ask for in the DSNZPARM will result in different changes.

Size changes are dynamically allocated in 5 MB chunks. For example, if the current size is 28 MB, and you ask that the size be increased to 30 MB, DB2 will allocate 33 MB. If the current size is 48 MB, and you change the size to 39 MB, DB2 will change the allocation to 38 MB. Also, the EDM pool size cannot be reduced below the size that was specified when DB2 was started.

Change Management Considerations
The ZPARM module must be assembled and linked before it can be used by DB2. It is conceivable that changes could be made to DB2 parameters without proper documentation or change management procedures to ensure that the changes are all complete and correct. There is a certain formality involved with starting DB2, and this has caused people to be careful when updating system parameters. Caution should still be used when changing system parameters. A new sample DSNTEJ6Z provides a report of the parameter settings.

Many parameter changes are immediate, but some commands will wait for a related event to occur. RLF parameters wait for the Resource Limit Facility to be restarted. The SET LOG or SET ARCHIVE commands can override logging parameters LOGLOAD, DEALLCT, and MAXRTU, or loading a different ZPARM module can change the values. Other parameters such as STATIME and DSSTIME wait for the current interval to expire before the new value is used. Thread parameters IDFORE, IDBACK, BMPTOUT and DLITOUT do not effect currently active threads and take effect on the next create thread request.

Restart and Thread Cancellation
Restart's consistency has been improved by allowing for the ability to cancel the thread and recover, without having to wait for rollbacks of long running jobs to complete. While there has been a consistent message from IBM and many sources to commit frequently, some jobs can slip through. This new command will allow long-running jobs or statements that have not issued commits to be removed quickly from the system.

-CANCEL THREAD ... (NOBACKOUT)

Basically, this allows us to cancel a thread without a rollback occurring. The objects will be inconsistent, and they will be placed in a recover pending state. It will be necessary to recover the table space and indexes.

Utility Improvements

online REORG
For many OS/390 shops, 24x7x52 is no longer merely a desirable goal, but a do-or-die business imperative. As such, only a few seconds of database unavailability during database reorganization is simply unacceptable. Version 7 introduces the FASTSWITCH keyword for Online Reorg, the use of which will replace the approximately three second outage associated with the renaming of original and shadow dataset copies with a clever, memory-speed switch of MVS catalog entries.

online LOAD RESUME
Online LOAD RESUME will now become a reality, thereby effectively combining the speed and performance of the LOAD utility with the availability and access offered by INSERT processing. The Online LOAD (LOAD RESUME YES SHRLEVEL CHANGE) operates similar to an SQL INSERT program whereby it claims instead of draining, it attempts to maintain the clustering order of the data, and it is LOG YES only (will not require a COPY afterward). Locking problems are avoided through internal monitoring of the commit scope. It can also be run in parallel for partitioned tablespaces.

Unload Utility
A new utility, which promises to provide better performance and more flexibility than DSNTIAUL, is also available in Version 7. The UNLOAD utility will support both Tablespace and Image Copy as the Unload source. In addition, it will allow the UNLOAD-ing of multiple partitions in parallel, as well as field selection, ordering and formatting options via an SQL-like syntax.

Parallelism
There is more parallelism in the utilities; in particular, the Copy and Recover utilities have been enhanced again with more parallelism. They have been given the ability to parallel process with multiple inputs. These loads may be executed in a single step rather than multiple jobs.

The elapsed time for the BUILD2 phase of the REORG has been improved so that the logical partitions of the non-partitioning indexes will be updated using parallel subtasks. This is a badly needed availability improvement, especially for those with large partitioned tablespaces with one or more NPIs.

Support for LOAD parallelism is a nice improvement for dealing with short windows in which to load a lot of data. This has been a problem especially when NPIs were involved; this forced us to drop and recreate the NPIs in order to get the loads done. Now, using multiple tasks in a single job to load the partitions in parallel, you can submit a single job with several input files to be loaded in parallel. The performance is much faster and the contention on the NPI is eliminated. The number of parallel load tasks will be determined by the number of CPUs and virtual storage available, and the number of threads available. An example of the new syntax is shown below.

LOAD INTO TABLE tab1 PART 1 INDDN infile1
       INTO TABLE tab1 PART 2 INDDN infile2
      ...

Restart Ability
The ability to restart utilities has been added to the Control Center. Utilities that have been stopped must be able to be restarted. The display utility command will show the status of any utility that is active or stopped. You are only able to restart utilities if the status is stopped; you can only restart utilities from the Control Center that were initially started from the Control Center.

User Defined Utility ID Support
Whenever a utility is executed, you need the ability to specify Utility IDs. This enhancement allows you to specify a default Utility ID on a new OS/390 tab of the DB2 Control Center Tools Settings window; it will allow you to modify the CC390 generated Utility ID each time a utility is run.

Usability
There will be support for utility wild cards and dynamic allocation. Utilities will be easier to invoke. DB2 will also have the ability to perform dynamic allocation instead of using JCL. This will be accomplished by using established templates built with symbolics. In addition, you will be able to define a list of tablespaces for use multiple times, specify a pattern for tablespace matching, or specify a parameter for all tablespaces in a database. This is done using the new LISTDEF statement that will also allow us to INCLUDE and/or EXCLUDE certain objects from these lists.

NOTE: For more information on restart ability and user defined utility ID support and usage, refer back to the section on Control Center enhancements for OS/390.

Interface to Data Warehouse Center for OS/390
Another enhancement for utilities is the utility interface provided for the following:

• Allows you to input Load parameters which may invoke the CC390 load table dialog
• Allows you to input Runstats parameters for a table

These parameters are stored in a Java class, which can be used later to invoke the utility on the Data Warehouse Center table.

Faster Restart in Data Sharing

Otherwise known as "Restart Light," a new, small footprint DB2 arrives with Version 7. This new restart should substantially reduce the time and resources required to bring up just enough of a failed subsystem to release the retained locks and nothing more.

When a DB2 data sharing member fails, locks can be retained by the failed member. These locks are released during DB2 restart when completed and in-commit transactions have their updates applied to disk, and in-flight and in-abort transactions have their updates removed from disk. Following these updates, the locks can be safely released. At this point, DB2 can be safely stopped and started again on another member of the sysplex group.

In DB2 UDB for OS/390 Version 7, it is possible to complete "cross system" restart faster by starting DB2 in Light mode:

-START DB2,LIGHT(YES)

This causes DB2 to start a smaller footprint to perform just the recovery portion of DB2 restart. Following the recovery of the units of work as described above, the retained locks are freed, and DB2 automatically terminates. DB2 saves a lot of time normally required during restart by not initializing the EDM and RID pools, the LOB (large object) manager, RDS (relational data services) or the RLF (resource limit facility). As DB2 will not remain up, these services are not needed.

One of the keys of this function is the reduced memory requirement. The reduced number of services tasks saves on memory. In addition, only virtual buffer pools are used, each with a maximum VPSIZE of 2000. No hiperpools are used. The IRLM is started PC=YES to place the locks in private and save storage.

The net result is a quick restart of DB2 that should last only a few seconds. Of course, your applications must do their part by committing work regularly. As before, a long running job with few or no commits can substantially delay restart.

Statistics Reporting

Enhancements will be made to elapsed time reporting and statistics. We will have the ability to keep a history of statistics for better proactive performance analysis capabilities. This will help us better monitor our objects in terms of growth over time along with other information to determine if objects need to change. This will be supported via nine new catalog tables used to keep historical statistics and a new keyword of HISTORY in the RUNSTATS utility. We will also have the ability to delete old statistics from the catalog history tables using a new MODIFY STATISTICS utility.

Summary

While there are always improvements in the base DBMS in every release, the major improvements in DB2 Version 7 focus on integrating warehouse, OLAP, and federated database functions into the product. Development tools for the web and for smoother migration from other DBMSs have been improved in the product. The packaging of DB2 has been simplified to include these additional functions. Many single-user products are included to allow individuals to try and buy these products. So, regardless of the platform you choose for DB2, you will find that with Version 7's enhancements, DB2 has become the leader in database solutions for e-business.

In this paper we have touched on many of these Version 7 enhancements. For additional details refer to the appropriate "What's New" Documents from IBM at http://www-4.ibm.com/software/data/db2/udb/pdfs/ whatsnew.pdf (DB2 UDB for UNIX, Windows, OS/2) and http://www.ibm.com/software/data/db2/os390/pdf/ whatv7.pdf (DB2 UDB for OS/390).