©  2021  Mullins Consulting, Inc.   All Rights Reserved   Privacy Policy   Contact Us

DB2 9 for z/OS Roars to Life

by Craig S. Mullins

Quarter 4, 2006 Vol. 11, Issue 4

DB2 9 for z/OS, the latest version of IBM’s stalwart mainframe relational DBMS, delivers a plethora of new features and functions. Paramount among the many new features is XML support as a native data type, but that's not all there is. IBM has also added more SQL functionality, improved performance, and expanded earlier initiatives such as online schema change.

Beta testers have been trying out the new features since June. Here's your chance to get an early look at the new features coming your way.

Getting Started

First, let’s take a look at the prerequisites for running DB2 9. For the operating system you must be running either z/OS V1.7 or z/OS.e with DFSMS V1.7, Language Environment Base Services, and z/OS V1.7 Security Server (RACF). Keep in mind though, that some DB2 9 functionality (such as roles) will require z/OS V1.8. Additionally, you must be in DB2 V8's new function mode with an expanded Boot Strap Data Set (BSDS) before you can migrate to DB2 9.

DB2 9 for z/OS will operate on any processor that supports z/Architecture, including the new Business Class and Enterprise Class processors, IBM System z9, zSeries z800, z890, z900, z990, or a comparable processor.

XML Data

One of the biggest technological advances in DB2 9 is the ability to combine the management of structured and unstructured data. Like DB2 9 for Linux, Unix, and Windows (released earlier this year) DB2 9 for z/OS makes it possible to store XML data in its native format.

What’s the big deal — you might ask — can't we already use the XML Extender to store XML data in DB2? Yes, but DB2 9 changes the game. You'll be able to search and analyze structured data in a relational data repository and unstructured data in an XML repository that doesn't require you to reformat it. The approach is novel in that it will support native XML, basically enabling dual storage schemes: the traditional SQL/relational storage and new XML storage (see Figure 1).

When you want to store XML in DB2 9, you no longer have to store it as a character large object (CLOB) or shred it into tables. DB2 9 handles XML as a new data type that’s stored in a natural hierarchy — different from relational data. DB2 9 offers a hybrid data server capability that is a first in the industry. Although XML and relational data are stored separately, both are under the control of the same DB2 engine.

So, how will you support XML data in DB2 9? Think of XML as just another data type. You'll use the XML data type in a CREATE TABLE statement to define a column as type XML. Each column of type XML can hold one XML document for every row of the table. Even though the XML documents are logically associated with a row, XML and relational columns are stored differently. The relational columns are stored in the traditional structures we all know and love. The XML data is stored in hierarchical structures.

Don’t let that scare you. IBM has seamlessly integrated XML with relational data to simplify application development while optimizing search performance with highly optimized XML indexes.

DB2 9 supports XPath and includes tables to support definitions of XML schemas. The IBM DB2 utilities have been extended so that they can be used to administer XML data, too. I don’t want this to turn into a lengthy discourse on XML — suffice it to say that XML support is the biggest new feature in DB2 9. But it's far from the only one, so let's move on.

Online Schema Evolution

Online schema change was a key offering of DB2 V8. But, as the name implies, its capabilities continue to evolve. In DB2 9, online schema evolution expands to simplify more types of database definition changes and is now called Database Definition On Demand.

Online table space reorganization is significantly improved. Today, when reorganizing just a couple of partitions in a partitioned table space, the BUILD2 phase takes a long time. V8 removed the outage for Data Partitioned Secondary Indexes (DPSIs); DB2 9 removes the BUILD2 phase for all types of secondary indexes.

Another new online schema change capability supports replacing one table quickly with another via cloning. The technique can even avoid the need to rebind packages and lets you rename a column or index within your table. [Craig, is that what you meant?]

A new table space type in DB2 9 combines the attributes of segmented and partitioned. When used, DB2 automatically adds partitions as needed to support your rapidly-growing data.

You can also define SMS constructs (MGMTCLAS, DATACLASS, and STORCLAS) on a STOGROUP in DB2 9 and alter the constructs, too. You can also alter table space and index logging parameters.

DB2 9 even adds a new capability to change the DB2 early code without requiring an IPL.

New Query Capabilities

As we've come to expect with new versions, DB2 9 delivers rich new SQL functionality. There are new SQL data manipulation statements as well as new data types for DECFLOAT (decimal floating point), BIGINT (8 byte integer), BINARY, and VARBINARY types.

DB2 for Linux, Unix, and Windows has supported INTERSECT and EXCEPT in SQL SELECT statements for some time; the z/OS platform catches up in DB2 9. You can use these two set operations to simplify some SQL statements. Think of them as being similar to the UNION operation.

Use INTERSECT to match result sets. If the data is the same in both results sets, it passes through. When INTERSECT ALL is specified, the result consists of all rows that are in both result sets. If INTERSECT is specified without the ALL option, the duplicates will be removed from the results. For example, the following SQL will show all customers in the USA who are also employees (with no duplicates):

     SELECT last_name, first_name, cust_num

     FROM   CUST

     WHERE  country = 'USA'

     INTERSECT

     SELECT last_name, first_name, emp_num

     FROM   EMP

     WHERE  country = 'USA';

EXCEPT, on the other hand, combines non-matching rows from two result tables. Some other DBMS implementations refer to this as the MINUS operation. When EXCEPT ALL is specified, the result consists of all rows from the first result table that don't have a corresponding row in the second, and any duplicate rows are kept. If EXCEPT is specified without the ALL option, duplicates are eliminated. As an example, the following SQL will return only those items from TABLE1 that are not also in TABLE2:

     SELECT item FROM TABLE1

     EXCEPT

     SELECT item FROM TABLE2;

In DB2 9, the ORDER BY and FETCH FIRST clauses can be specified at the fullselect level instead of only at the SELECT statement level. This means that you can sort and limit the data selected on either side of a UNION, for example (as well as on INTERSECT and EXCEPT).

DB2 9 also supports the MERGE and TRUNCATE statements. The MERGE statement basically takes two tables and merges the data. Rows in the target that match the source are updated and rows that don't exist in the target are inserted. Of course, there are multiple options that can be used to control the specifics of how MERGE functions. The TRUNCATE statement is simply a quick way to delete all of the data from a table.

Another nice new SQL feature provides the ability to SELECT from DELETE, UPDATE, and MERGE statements. DB2 9 allows you to retrieve columns from rows that are modified via DELETE, UPDATE, and MERGE statements, thereby replacing multiple SQL calls with one.

INSTEAD OF triggers let you use a trigger in place of a view. Basically, the trigger code is run instead of the code that triggered it. DB2 9 also improves support for native SQL stored procedures by integrating stored procedures code into the DB2 engine and eliminating the conversion to C.

Additional query capabilities include OLAP extensions for RANK, DENSE_RANK, and ROW_NUMBER and enriched text handling with functions like caseless comparisons, cultural sort, and the ability to index on expressions.

Security and Regulatory Compliance

Government regulations such as Sarbanes-Oxley and HIPAA place controls on how we manage data. Complying with these regulations demands improved database security and auditing functionality.

Role-based security authorization is a significant DB2 9 improvement. Roles provide a more flexible technique than groups or users in assigning and controlling authorization. A role is a set of users sharing the same security privileges. A user belonging to a particular role can perform the set of tasks and accesses for which permissions are granted for that role.

Granting permissions using roles rather than using a particular user or group is easier to manage. For example, suppose a DBA grants permission to access several tables to a group of users. To change the number of tables, the DBA would need to grant permissions for the additional tables to the existing users. With role-based security, the DBA need only declare that the users belong to a particular role; adding any new authority is done at the role level.

DB2 9 also adds a trusted security context. Basically, this will enable you to reduce the complexity of managing passwords by identifying trusted connections (DDF, RRS, and DSN). Once defined, connections from specific users via defined attachments and source servers will allow trusted connections to DB2. The users defined in this context can also be defined to roles.

Secure Socket Layer (SSL) implementation provides encryption of data on the wire, improving upon the existing techniques used by DRDA for encrypting data.

Finally, DB2 9 offers improved audit filtering, including the ability to include or exclude based on userid, workstation, application name, package location collection or name, connection id, correlation id, and role.

Performance Improvements

DB2 9 offers many new features to improve performance. INSERT performance should improve dramatically due to a wide range of improvements including the ability to index on expressions and randomized index keys. Logging performance is improved substantially; larger index page size options (8K, 16K, and 32K) are provided to reduce the number of page splits. Page splits will be more efficient in DB2 9, too.

When index performance needs to be optimized for inserts, rather than for later retrieval, the new APPEND option can be used to inform DB2. If you need to randomize your data to avoid insert hot spots, you can use the new randomized index key capability.

DB2 9 also adds the much-requested “not logged” table spaces (table spaces in which data changes aren't logged). Using these table spaces can remove the bottleneck of the log; however, recoverability is limited. In DB2 9, logging can be toggled on and off, so you might choose to do a series of parallel high performance inserts without logging and then turn logging back on. At any rate, use these table spaces with caution. And keep in mind that they don't improve performance significantly for a single sequential insert application.

IBM has made several improvements to DB2 optimization, including a histogram for DB2 statistics so that DB2 can gather a better understanding of skewed data over ranges. The DB2 optimizer can deploy cross query block optimization instead of having to work on one query block at a time. Furthermore, sparse index and in-memory data cache techniques (once used only in special situations) have been generalized for use in more cases in DB2 9. And the ability to use multiple indexes in a star schema has been improved.

Visual EXPLAIN has been improved into an Optimization Support Center, with enhanced instrumentation for analyzing and improving query performance.

Hardware synergy with IBM System z and z/OS delivers performance gains as well. DB2 makes unique use of the z/Architecture instruction set, and recent instructions provide improvements in reliability, performance and availability. Indeed, using DB2 in conjunction with disk and channel changes (DS8000, 4 Gb per second channels, MIDAW) improves data rates substantially.

You can reduce cost of ownership by offloading work to the zIIP. In V8, SQL procedures were not eligible to run on the zIIP; however, the native SQL Procedure Language on DB2 9 will make the work eligible for zIIP processing.

DB2 9 continues the memory improvements begun in V8. DDF and DBM1 use shared memory above the bar. And additional DB2 data structures are moved above the bar in DB2 9.

Other DB2 9 performance highlights include better LOB performance and better performance of varying length data when using large numbers of varying length columns.

Utilities

DB2 9 delivers some notable utility improvements. REORG removes the BUILD2 phase for all types of secondary indexes. LOAD and REORG CPU times are projected to improve on the order of 10 to 15 percent. And DB2 9 offers an online REBUILD INDEX.

Features Removed from DB2 9

As new and improved features are introduced into DB2, older features are removed. DB2 9 no longer allows simple table spaces to be created. For now, DB2 will continue to support existing simple table spaces, but you can't create any new ones. Keep in mind that once you're on DB2 9, any simple table space that is dropped must be recreated as either a segmented or a partitioned table space.

DB2 Estimator, previously available as a free Web download, won't be available for DB2 9. And Net.data, which was stabilized at the V7 level, will no longer be supported. IBM WebSphere is IBM’s strategic solution for delivering DB2 data to Web applications.

JDBC/SQLJ Driver for OS/390 and z/OS support is removed. All Java application programs and Java routines must be modified to work with the IBM DB2 Driver for JDBC and SQLJ (formerly known as the DB2 Universal JDBC Driver).

Additionally, DB2 9 no longer supports DB2-managed stored procedures. You'll need to migrate everything to WLM-managed stored procedure spaces.

Other removed features include:

 AIV Extender

 Text Extender

 Net Search Extender.

Moving On

Use this article as a starting point for your DB2 9 learning curve—look for more details from IBM as they're available. (Bookmark this Web page to stay up to date with the latest versions of DB2 and IBM’s support: ibm.com/db2/zos/support/plc.)

DB2 9 offers many new and exciting features that will continue to make DB2 for z/OS the premier data server in terms of performance, availability, and functionality. I know I’m looking forward to using it.

Figure 1.

DB2’s New XML storage and interface.

From DB2 Magazine, Q4 2006.

© 2012 Craig S. Mullins,