WHITE PAPER

     DB2 Version 9 - A Perspective  

By Bill Arledge, BMC Data Management Strategist

DB2 Version 9 for z/OS fundamental messages

Infrastructure changes

More 64-bit exploitation

Universal table space

> Range-partitioned
> Partitioned-by-growth

Reordered row format

Index compression

Index on expression

“Not Logged”table spaces

Rename indexes and columns

Clone table support

APPEND

STOGROUP SMS constructs

Data sharing support

Infrastructure summary

PureXML

Application-related enhancements

SQL extensions and enhancements

> New data types
> INSTEAD OF triggers
> TRUNCATE statement
> MERGE statement

Native SQL stored procedures

Application enablement

> Optimistic concurrency control
> Automatic object creation
> Skip locked data
> REOPT (AUTO)

Summary

DB2 Version 9 for z/OS fundamental messages

IBM has a number of goals for DB2 Version 9; first and foremost is to make DB2 for z/OS the preferred database platform for all enterprise applications. To accomplish this goal, DB2 V9 delivers:

This is the high-level marketing message for DB2 V9; the individual goals are accomplished with a broad range of new features that affect many moving parts. Enhancements include:

New features and enhancements often have broad impact across DB2, providing improvements in performance, availability, application enablement, and IT productivity. Let’s take a look at some of these enhancements and discuss their potential impact on your applications and environment.

Infrastructure changes

Infrastructure includes a number of areas within DB2, including address space architecture, database structures, schema management, and others. Infrastructure changes in DB2 V9 deliver benefits including scalability, availability, and performance. Many of these enhancements and changes build on architectural changes introduced in DB2 V8.

More 64-bit exploitation

DB2 V8 introduced support for 64-bit addressing. This support moved much of the DBM1 address space above the bar, providing significant virtual storage constraint relief (VSCR). Buffer pools, much of the EDM pool, and other storage areas moved. Locks held in the IRLM address space moved above the bar as well. Some virtual storage constraints still exist in DB2 V8. The 64-bit support in DB2 V9 should largely eliminate any virtual storage constraints.

The 64-bit Distributed Data Facility (DDF) runs in the DIST address space; in DB2 V9 the DIST address space is 64-bit. This enhancement delivers the following benefits:

No changes to applications are required to realize these benefits.

DB2 V9 builds on changes made in DB2 V8 related to virtual storage for the EDM pool. Reducing virtual constraints below the bar allows more concurrent work within DB2. A new skeleton pool is created above the bar and contains the skeleton cursor table (SKCT) and skeleton package table (SKPT) structures; this further reduces virtual storage constraints below the bar. The remaining runtime structures are the Package and Cursor tables, which are split with parts above the bar and others below it.

Universal table space

The Universal Table Space (UTS) introduces a new table space type that combines the space management characteristics of segmented and partitioned table spaces into a single structure type. A UTS can be defined explicitly or implicitly. You can have two types of universal table spaces: range-partitioned and partitioned-by-growth.

Universal table spaces provide a number of benefits, including:

Prior to DB2 V9, three basic types of table spaces could be created: simple, segmented, and partitioned. A CREATE TABLESPACE statement with no SEGSIZE parameter or PARTITION specification was a simple table space. If a SEGSIZE parameter was specified, the table space would be defined as segmented. If PARTITION definitions were provided, a partitioned table space was created.

With DB2 V9, simple table spaces can no longer be created. Existing table spaces will continue to function, but if they are dropped they cannot be recreated. If the table space is recreated with no SEGSIZE, MAXPARTITIONS, or NUMPARTS specification, the table space is created as a segmented table space. A table space that is created implicitly will default to a universal table space (partitioned-by-growth) in a DB2 New Function Mode (NFM) environment.

Range-partitioned

The range-partitioned universal table space is similar to a traditional partitioned table space; however, segmented space map pages are used, improving space management within each partition for variable length rows and for mass delete operations. This type of table space combines the benefits of partitioned table spaces with the space management advantages of the segmented table space.

To create a range-partitioned universal table space, specify NUMPARTS and SEGSIZE on the CREATE TABLESPACE statement.

Partitioned-by-growth

While partitioned-by-growth universal table spaces manage space similar to a traditional segmented table space, partitioned-by-growth table spaces automatically increase the number of partitions as existing partitions fill up. As an existing partition fills up, DB2 automatically allocates a new partition to handle the growth up to a maximum number of partitions specified. This type of table space is most useful if the tables in the table space don’t have a good partitioning key and you expect a table space to exceed 64 GB.

Partitioned-by-growth universal table spaces provide productivity benefits for the DBA by letting DB2 automatically grow the table space as the need for additional space occurs. This improves availability by minimizing the chances of a resource being unavailable because the table space is full.

The MAXPARTITIONS on the CREATE TABLESPACE statement specifies the maximum number of partitions you want to allow. Be sure to specify a reasonable number to protect from a situation where a batch program goes awry in an infinite loop. If you need additional partitions, use the ALTER TABLESPACE statement to increase the MAXPARTITIONS.

Reordered row format

Prior to DB2 V9, physical database designers were concerned with the placement of variable length columns in a DB2 table. If a variable-length column in the middle of a row was followed by fixed-length columns, more CPU cycles were required to select columns from the row; this also increased the amount of data that was logged during update of the row. DB2 V9 changes the way DB2 manages a row with variable length columns:

Performance of SQL statements with variable-length columns should improve because DB2 can locate a variable length column faster with this approach, reducing the CPU cost for accessing the column. Access to tables that contain only fixed-length columns will not be affected.

Another benefit of reordered row format is that physical design of tables with variable length columns is simplified. In earlier DB2 releases, variable length columns provided the best performance at the end of the row, and you had to consider this when creating the table. With DB2 V9, you no longer need to be concerned about placement of individual columns in the row.

Index compression

DB2 V9 supports index compression, which differs from table space compression. For example, index compression does not use a compression dictionary and index leaf pages are compressed on disk only. When the index leaf pages are retrieved, they are automatically decompressed into an 8K, 16K, or 32K buffer pool (specified on a CREATE or ALTER INDEX statement that specifies COMPRESS). If you specify COMPRESS on an ALTER INDEX statement, the index will be put into Rebuild Pending (RBPD) status.

The benefits of index compression are clear:

Index on expression

DB2 V9 introduces an extended index called an “index on expression” where a DB2 expression can be used in the definition of an index. A DB2 expression is a way of specifying a value and can include many constructs, including columns, special registers, numeric literals, and arithmetic operators. A DB2 expression could be something like: a) COLUMN1 * 10; or b) ORDER-DATE – 180 DAYS. The DDL for a DB2 V9 index that includes an expression would look something like this: 



CREATE INDEX RNDWDA.CR_INDIV_IX1    

  ON MKTCWR.CR_INDIVIDUAL           

   (PRIMARY_KEY_A ASC,              

   (FIRST_ORDER_DATE – 180 DAYS) ASC)   

     USING STOGROUP SYSDEFLT  

     ………….

In this example, the second index key (expression key) is an expression that subtracts 180 days from the column called FIRST_ORDER_DATE. An index entry would include the column value for the first column concatenated with the results of the expression defined in the second index key. So, the result of the expression is physically stored in the index entry.

“Not Logged”table spaces

DB2 V9 allows you to turn off logging for all tables (and all indexes on those tables) in an individual table space. It seems obvious why you might want to do this; however, logging does not usually cause performance problems for most applications. Do not expect a measurable performance benefit for jobs updating or inserting rows to “not logged” table spaces. Consider the impact on recoverability for these table spaces and index spaces. If the data can be easily rebuilt, you could use “not logged” table spaces.

To turn off logging for a table space, specify NOT LOGGED on the CREATE TABLESPACE statement. LOGGED is the default. To alter an existing table space, use the ALTER TABLESPACE statement.

Rename indexes and columns

DB2 V8 introduced the Online Schema Evolution, allowing you to make changes to DB2 table and index definitions on the fly. DB2 V9 changes the terminology to Database Definition on Demand and allows online changes to more object definitions. You can rename existing indexes and columns.

The benefit of renaming an index is that you do not need to drop and rebuild the index, thus improving availability. The rename operation has minimal impact. Bound plans and packages depend on the OBID. The OBID remains the same during the rename operation, so no rebind is required. However, any SQL in your application that directly references an index is affected by the name change. Specify RENAME INDEX to use this feature.

The benefit of changing the name of a column is that you do not need to drop and rebuild a table, thus improving availability. This function has restrictions, such as when a column is referenced in a view. Specify RENAME COLUMN to use this feature.

Clone table support

With DB2 V9, you can create a replica (clone table) of your DB2 objects while the base objects remain available for normal processing. You can create an exact duplicate of an existing table, including all the dependent objects created on that table. The clone table is created in the same table space with the base table, but using a separate data set for the clone. This is similar (in concept) to an online load replace. Once the clone is loaded, you can switch the base table with the clone, and the clone becomes the base table.

Clone table implementation has limitations. For example, the base table must be defined in a universal table space and must be the only table defined in that table space. Be sure and review available documentation that will describe other requirements in more detail.

The EXCHANGE SQL statement facilitates the switch between the base and clone tables. No data is exchanged between the base and the clone; the DB2 catalog is manipulated to indicate which structure is the clone and which is the base.

To create clone tables, use the ALTER TABLE ADD CLONE syntax. To remove the clone, use the DROP CLONE syntax.

APPEND

The APPEND option optimizes performance for insert processing rather than for later retrieval. The APPEND option instructs DB2 to ignore clustering during SQL INSERT and online LOAD processing (and significantly improve performance). This causes data to be appended to the end of the table or appropriate partition. Faster load processing decreases any outage associated with a utility process.

The APPEND option has limitations; for example, you cannot specify APPEND for LOB or XML tables.

Specify APPEND YES/NO on the CREATE or ALTER TABLE statement to use this option.

STOGROUP SMS constructs

DB2 has two ways to manage initial data set creation:

STOGROUP is highly recommended, and many DB2 V9 features require STOGROUP definition for table spaces and index spaces. To use STOGROUP, issue a CREATE statement that specifies USING STOGROUP. DB2 handles the IDCAMS DEFINE under the covers.

For user-defined data sets, you must generate and execute the IDCAMS DEFINE to create the table space and index space data sets. This gives you more control, but it is a manual process.

STOGROUP is the best way to manage your data set allocation. One drawback is that in prior DB2 releases, the STOGROUP definition didn’t include any of the SMS parameters that are critical for effectively managing data set placement using SMS, including DATACLAS, MGMTCLAS, and STORCLAS. This meant that Automatic Class Selection (ACS) routines controlled placement of DB2 data sets when STOGROUP-defined objects were used. ACS routines can be complex and are generally not managed by DB2 DBAs.

With DB2 V9, the STOGROUP definition includes columns for DATACLAS, MGMTCLAS, and STORCLAS. You can populate these columns as part of the CREATE or ALTER STOGROUP statement. An example of the CREATE STOGROUP syntax is provided below. The VOLUMES parameter is optional. 

CREATE STOGROUP MKTCWRSG

   VOLUMES (SYSBXX)

   VCAT DSNDIA

   DATACLAS DCLASS01

   MGMTCLAS MCLASS02

   STORCLAS SCLASS03
Data sharing support

DB2 V9 introduces a number of enhancements in the data sharing area, including:

Most of these enhancements are implemented automatically by DB2, so you do not need to take any action to see the benefits.

The –ACCESS command removes a specific DB2 object from group buffer pool dependency, which is useful when work against a DB2 object is being done by one member of the data sharing group. The command syntax looks like this: 

-ACCESS DATABASE (CRBMCDDB) SPACENAM (CRINDDTS) MODE (NGBPDEP)

Removing the group buffer pool dependency reduces the data sharing overhead for this object. Subsequent access from other group members automatically makes the object group buffer pool dependent again.

Infrastructure summary

DB2 V9 introduced many infrastructure changes to improve availability, performance, and scalability. To learn more, see the IBM publication DB2 9 for z/OS Technical Overview (SG24-7330-00). DB2 for z/OS manuals are always a good read. DB2 V9 information from sources other than IBM is available in many forms, including the IDUG DB2-L list server and www.idug.org.

PureXML

XML is a key technology for data exchange, data integration, and service-oriented architectures. PureXML is IBM’s implementation of native XML support in DB2 V9. PureXML transforms DB2 on z/OS into what IBM calls a hybrid data server that supports native XML as well as traditional relational data. Customer interest in XML is high and will be a key driver for migration to DB2 V9.

What’s the value of PureXML in DB2 V9? Most organizations manage XML in some form on some platform. If you need to store your XML data on the mainframe, DB2 V9 provides a powerful platform for managing the data while leveraging all the power of the underlying DB2 on z/OS engine, including backup and recovery. XML data can be integrated with your relational data because DB2 V9 provides relational extensions for manipulating XML data with your applications.

DB2 V9 delivers a range of capabilities for managing XML data. A new XML column data type allows for manipulation of XML data with traditional SQL statements or new SQL extensions for XML (SQL/XML). DB2 V9 provides the infrastructure for managing this data so that it is transparent to the application and uses a physical structure similar to what is used with LOB data.

DB2 V9 XML support includes an XML indexing capability that improves the efficiency of queries on XML documents. These indexes are structured differently from a traditional DB2 index that is based on the contents of the entire column included in the index. The XML index is based on subsets of the data stored in the XML columns. Queries can utilize these indexes to more rapidly access data stored in these columns.

XML implementation will require learning new concepts related to object design and implementation as well as application SQL considerations. Migrating existing XML data into these new structures could be a complex and time-consuming task.

Application-related enhancements

We have discussed some of the infrastructure changes in DB2 V9, but the infrastructure is just part of the story. Some of the most exciting changes are related to applications.

SQL extensions and enhancements

DB2 V9 introduces a number of enhancements in the SQL area, including new data types and SQL language extensions that add functionality and increase SQL consistency across the various DB2 platforms.

New data types

We’ve already discussed the XML data type. DB2 V9 introduces four additional column data types that offer increased flexibility for managing different data types on the DB2 for z/OS platform:

These data types increase DB2 cross-platform consistency by supporting data types already supported on the LINUX, UNIX, and Windows (LUW) DB2 platform, and they make it easier to migrate applications developed on other DB2 platforms to the mainframe and vice-versa.

Introducing new data types to an existing table may require a DROP/CREATE process – which requires unloading and reloading of the application data. If you are altering an existing column, you could use the DB2 ALTER TABLE statement. In general, the rules for altering an existing column remain the same as in DB2 V8, where it’s possible to modify an existing column’s data type in some cases. For example, modifying an existing numeric data type (SMALLINT) to a new data type (BIGINT) is allowed because both are numeric.

INSTEAD OF triggers

From basic DB2 relational training, we know that views simplify SQL access to DB2 tables and provide an additional layer of security between the user and the underlying base table. The view provides a way to create a subset of the contents of a DB2 table, horizontally by selecting specific columns, and/or vertically by qualifying column values that determine the rows included in the view’s result set. The user of the view sees only the columns and rows of interest. You can create complex views that join multiple tables or create new representations of data columns by performing calculations on them.

In the best case, all SQL DML statements are able to use the view to access and update data in the table; however, a complex set of rules determines whether a view is updateable. For example, a view that includes a derived column is a read-only view and is not updateable. A SQL SELECT statement can use the view for access, but any updating SQL statement (UPDATE, INSERT, and DELETE) would need to access the base table directly. This negates some of the benefit of view by requiring the developer or end user of the data to be aware of the underlying base table.

INSTEAD OF triggers allow an application to execute SQL DML statements using a non-updateable view. Under the covers, the INSTEAD OF trigger performs the operation against the underlying base table. The trigger is defined on the view instead of the base table, which is the norm for other types of triggers.

To use INSTEAD OF triggers, you must modify existing object definitions and SQL statements to access the view rather than the base table. You must implement an INSTEAD OF trigger for each updating SQL statement (SELECT, UPDATE, DELETE) that use the non-updateable view. In most cases, the applications have already dealt with the problem, so consider the benefits and the costs of modifying your applications.

Of course, new applications can take advantage of INSTEAD OF triggers during initial application development and implementation.

New relational operators

DB2 V9 provides SQL language extensions including INTERSECT and EXCEPT set operators. These new operators are similar to the UNION set operator and add new capabilities for combining data from multiple objects.

The INTERSECT relational set operator allows you to find all rows that are in the result set of both SQL statements defined in the query. INTERSECT syntax is shown here: 

SELECT * FROM ORDER 

    WHERE QUANTITY BETWEEN 1 and 100



INTERSECT



SELECT * FROM ORDER

    WHERE QUANTITY BETWEEN 50 and 200;

The final result set for this query would include any rows where QUANTITY is between 50 and 100. Additional syntax is available to deal with duplicate rows in the result set.

The EXCEPT relational set operator allows you to identify rows in the result set for the first SQL SELECT statement that are not in the result set for the second SELECT statement. You can use additional syntax to handle duplicate rows, either excluding or including them in the final result set. EXCEPT syntax looks like this: 

SELECT * FROM ORDER

    WHERE QUANTITY BETWEEN 1 and 100



EXCEPT

      

SELECT * FROM ORDER 

    WHERE QUANTITY BETWEEN 50 and 75;

Most of this functionality can be achieved using complex SQL. You can specify EXCEPT using NOT EXISTS with subselect, and INTERSECT using the EXISTS with subselect where no null values are involved in either case.

This new SQL benefits end-users by simplifying the syntax required to get the desired results. These operators simplify SQL coding for new applications. For embedded SQL statements, these operators will not provide immediate benefit to the application but will incur the cost required to change the existing SQL, including testing and deployment. In the long term, application maintenance cost should go down because the SQL will be easier to maintain.

INTERSECT and EXCEPT are currently available in other relational platforms like Oracle and DB2 UDB on LUW (Linux, UNIX, Windows). This helps IBM meet its goal to provide SQL consistency across different relational platforms.

TRUNCATE statement

The TRUNCATE statement allows you to do a mass delete of all the rows in a specified table. Before DB2 V9, you could do a mass delete with a SQL DELETE statement with no WHERE clause. This works; however, the TRUNCATE statement provides more options and flexibility, including the ability to ignore Delete Triggers and retain any storage allocated to the deleted table. You can specify that the TRUNCATE should be committed immediately. The syntax of the TRUNCATE statement is shown below:

TRUNCATE TABLE MKTCWR.CR_INDIVIDUAL

     IGNORE DELETE TRIGGERS DROP STORAGE

MERGE statement

The MERGE statement allows you to insert data when the row doesn’t already exist in the target table and update the row if it already exists. You can specify what happens when a matching row is found or not found. For example, you could specify to update the existing values using an expression in the statement if the row is already present. If the row is not found, you can specify that an INSERT be performed. The syntax for the MERGE statement is shown below:

MERGE INTO TABLE MKTCWR.CR_INDIVIDUAL A

    USING (VALUES(‘0297517’,……,……,……) 

  AS B (CNUM,………,…………,………)

    ON (CUST_NUMBER = CNUM)

  WHEN MATCHED THEN UPDATE SET ……………

  WHEN NOT MATCHED THEN INSERT (CUST_NUMBER,………,………,………)

    VALUES (CNUM,………,………,…………)

The MERGE statement is useful for applications that need to conditionally update or insert table rows. Many applications have to code logic that deals with this issue, but the logic requires multiple SQL statements and can be complex. In a distributed environment, this complexity could cause more trips across the wire between the application requester and server. The MERGE statement simplifies this logic.

To use the MERGE statement, you will need to modify existing applications with embedded SQL. Balance this up-front cost with the long-term reduced cost of maintenance.

This concludes the discussion on most of the major SQL-related enhancements. You may want to investigate other SQL enhancements, including built-in functions and additional syntax including ORDER BY and FETCH FIRST in subselects.

Native SQL stored procedures

DB2 V9 includes a wealth of enhancements that make it easier to manage stored procedures and provide significant performance benefits for native SQL stored procedures. Native SQL stored procedures contain only native SQL procedure language (PL) statements; external stored procedures can include SQL and procedural language statements like C or COBOL.

With DB2 V8, the native SQL stored procedure is converted to C, which must then be compiled with the resulting load module stored external to the DB2 catalog. This requires the C compiler. When the procedure is called, the module is loaded into a WLM address space, where it executes.

With DB2 V9, native SQL stored procedures are converted to a native representation and stored in the DB2 catalog. This eliminates the requirement for the C compiler. Native SQL stored procedures no longer run under WLM control; they run in the DBM1 address space, improving performance. The DB2 Stored Procedure Address Space no longer exists.

Native SQL stored procedures that are invoked by a distributed requester through DDF are eligible to run on the zIIP processors in DB2 V9.

Before DB2 V9, you could support versioning of stored procedures, but it was not easy. DB2 V9 provides the VERSION keyword for the CREATE and ALTER PROCEDURE statements. One of the versions is always considered the active version and will be used as a default if the version is not identified. This makes it easy to manage multiple versions of a procedure that could be used for testing.

Implementing the changes to native SQL stored procedure requires dropping and recreating the stored procedure on DB2 V9. The results should be worth the efforts.

Application enablement

DB2 V9 provides a number of enhancements that improve application processing and management. They improve performance and availability and should increase the productivity of developers and DBAs.

Optimistic concurrency control

Optimistic concurrency control, also known as optimistic locking, is a significant change in the way DB2 handles locking. The new locking strategy decreases the time locks are held between consecutive fetch operations or between fetch operations and positioned update and delete operations. Let’s compare.

With no optimistic locking:

1. Application fetches row n.

2. DB2 locks row n.

3. Application fetches row n+1.

4. DB2 unlocks row n.

5. DB2 locks row n+1.

With optimistic locking:

1. Application fetches row n.

2. DB2 locks row n and returns to user.

3. DB2 unlocks row n.

4. Application fetches row n+1.

5. DB2 locks row n+1 and returns to user.

6. DB2 unlocks row n+1.

With optimistic locking, the locks held on rows are released immediately after the fetch. If the application then issues a positioned update or delete for that row, DB2 reevaluates the predicates that define the cursor to make sure the row still qualifies. Optimistic concurrency control can be used for plans and packages using updateable static scrollable cursors and bound with cursor stability.

DB2 V9 introduced a ROW CHANGE TIMESTAMP expression in a column definition as part of optimistic concurrency control. This column is managed by DB2 and updated when a row is loaded, inserted, or updated. This time stamp can be used to make sure a row has not been updated since it was last accessed by the application. This column could be used in the WHERE clause of an updating SQL statement to make sure the row was not updated subsequent to initial access. The ROW CHANGE TIMESTAMP can also be used for other purposes, like identifying all rows updated during a specific time frame. With this syntax, this column would contain the time stamp when the operation occurred: 

CR_INDIV_CHANGED_TS NOT NULL GENERATED ALWAYS

    FOR EACH ROW ON UPDATE

      AS ROW CHANGE TIMESTAMP

Optimistic locking decreases the duration of locks and improves availability and performance, helping eliminate -911 and -913 locking problems.

Automatic object creation

Previous releases of DB2 provided for automatic table space creation when tables were created without explicit reference to a table space in the CREATE TABLE statement. By default, these table spaces were created in DSNDB04 and were created as simple table spaces.

In DB2 V9 NFM, a CREATE TABLE statement with no IN clause implicitly creates a table space and database for you. The database name is derived using a counter that keeps track of temporary databases used. The database name structure is DSNnnnnn, where nnnnn is the next sequence to be allocated. So, the first default database created would be DSN00001, followed by DSN00002. If the new database name already exists, DB2 uses the next number in the sequence, in this case DSN00003.

The table space name is generated from the table name being created. In DB2 V8, implicit table spaces are created as simple table spaces. Simple table spaces can’t be created in DB2 V9, so implicit table spaces are allocated as partitioned-by-growth table spaces. You can set default table space and index space characteristics by using DSNZPARMs.

DB2 V9 goes further by allowing additional objects to be implicitly created by the CREATE TABLE, including:

Generally, using defaults for allocation of DB2 objects is not considered good practice. Most DBAs generate DB2 objects explicitly to make sure standards are being followed. This will continue to be the case for production applications.

Automatic object creation delivers a productivity benefit for end users who create tables using various query tools. End-users simply identify the table to be created and then DB2 automatically creates the additional required objects. The DBA can identify default values (in DSNZPARM) for objects being created implicitly to provide control over the characteristics of the objects that are created implicitly.

Skip locked data

With DB2 V9, you can allow application SQL to bypass requested pages when those pages are held by an incompatible lock. The application sees only unlocked committed rows. This differs from the Uncommitted Read (UR) bind option, where the application sees data that may ultimately be rolled back.

The benefits of skipping locked data are performance and availability improvements. Because the application does not have to wait for held locks to be released, locking problems are largely eliminated. The major drawback is that the result set for this statement could be logically inconsistent because some qualifying rows may not be returned. For example, a SELECT statement that returns summary data for a specific set of rows could be inaccurate. The application must be aware and accept this potential inconsistency.

Skipping locked data is valid only for Cursor Stability (CS) and Read Stability (RS) isolation levels. If any other isolation level is in use for the plan or package, the SKIP LOCKED DATA option is ignored.

Specify this option by using SKIP LOCKED DATA on the SQL statement being executed, including SELECT, PREPARE, searched UPDATE and DELETE. You can also use the option on the UNLOAD utility.

REOPT (AUTO)

REOPT (AUTO), a variation on the REOPT bind parameter, controls when dynamic or static SQL statements are reoptimized during execution. REOPT (AUTO) could benefit SQL queries where statement performance varies widely based on parameter marker values provided at execution time. REOPT (AUTO) does not apply to static SQL. For dynamic SQL, the REOPT option values include:

Implementing this option requires a change to the bind parameters for the package or plan containing this statement. You can specify REOPT(AUTO) in the BIND and REBIND commands.

For REOPT (AUTO) to work, you must set the REOPTEXT DSNZPARM to YES. If this DSNZPARM REOPTEXT is set to NO, REOPT (AUTO) will be ignored.

Summary

As you can see, DB2 V9 is very large indeed. We’ve discussed a number of new features that provide potential benefit to your organization. Many of these enhancements are automatic, while others will require changes to your existing environments, including subsystem definitions, object DDL, and SQL changes in your applications. It is important to understand the new features and how they can benefit your existing applications as you start your DB2 V9 implementation.

BMC Software has been developing innovative DB2 solutions since the 1980s, and we remain committed to the DB2 on z/OS environment. For more information, see www.bmc.com/db2.

© Copyright 2008 BMC Software, Inc.

BMC, BMC Software, and the BMC Software logo are the exclusive properties of BMC Software, Inc., are registered with the U.S. Patent and Trademark Office, and may be registered or pending registration in other countries. All other BMC trademarks, service marks, and logos may be registered or pending registration in the U.S. or in other countries. All other trademarks or registered trademarks are the property of their respective owners.

DB2, IBM, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

March 2008

89662