Implementing 'Dirty Reads'

I was recently asked about "dirty reads." This term refers to setting an application's SELECT statement to avoid taking locks while reading and processing uncommitted data.

Of course, the benefit of doing this is to reduce CPU costs and reap potentially significant savings. As the name implies, however, dirty reads comes with a risk. It can lead to failed transactions due to discrepancies in the data. So before implementing dirty reads, you must understand how your data is being used and if discrepancies can be tolerated.

In DB2, locking behavior is controlled through the isolation level used when the statement is executed. I like this statement from the manual, "DB2 for z/OS 11.0: Managing DB2 Performance":

"Various isolation levels offer less or more concurrency at the cost of more or less protection from other application processes."

 The manual also includes a list of isolation levels with descriptions. Again, read more here:

Warning: Dirty reads -- aka the uncommitted reads (UR) isolation level -- should never be used with transactions where the data must always be accurate or up-to-date (e.g., banking transactions or inventory data). To a DBA this may seem obvious, but I've worked at places where some developers, enticed by how fast programs can run at the UR level, decided on their own to apply it to business transactions. As powerful as UR can be, it's simply an unacceptable choice outside of development. CPU savings are not worth jeopardizing customer relationships or compromising your own critical business data.

Do you use dirty reads? When do you find it beneficial? Please share your experiences in comments.

Remaining IDUG Events in 2014

I shared my thoughts about the IDUG NA conference in May, but this is just one of IDUG's three major annual events. The European and Australian conferences are both coming up in a few weeks.

The IDUG DB2 Tech Conference in Sydney, Australia, is Sept. 10-12. Here are the conference sessions.

Meanwhile, European readers of this blog should note that one of the largest and most diverse IDUG conferences is being held in Prague, Czech Republic, from Nov. 9-14. Educational tracks are dedicated to the latest releases DB2 for z/OS 11.0 and DB2 for LUW 10.5. In addition, there are general tracks on DB2 for z/OS and DB2 for LUW, and more specialized tracks on big data and analytics, along with application development.

If you'll be in attendance, be sure to catch IBMer John Campbell's in-depth two-part session, "DB2 11 for z/OS: Migration Planning and Early Experiences" (AB1).

Some of my coworkers at CA Technologies will also be presenting:

Steen Rasmussen
* "Application Development Goodies: What’s HOT in DB2 10 and 11" (F12).

* "The DB2 11 Catalog: Something new, something old and something changed" (A14).

Steve Thomas
* "Archiving data: DB2 comes to the rescue (twice)" (B5).

Zeljen Stanic
* "To Do IT(IL) or Not ... " (E13).

Denis Tronin:
* "Lost Without a Trace" (B3).

I've often discussed the value of IDUG conferences in this blog. Attendees receive invaluable education and updates on DB2 from some of the world's foremost database experts. In addition, IDUG events include many other services and features, such as free certification exams, access to one-day seminars, interaction with IBM speakers from the lab and the opportunity to network and share ideas with other DB2 professionals.

If you've attended IDUG events, please share your experiences in comments.

IBM Knowledge Center a DB2 for z/OS Resource

As I've often noted, I prefer storing DB2 for z/OS manuals in PDF format. Even though keeping them updated is a challenge, I believe that being able to access all this information from my laptop is well worth the effort.

Launched earlier this year, the IBM Knowledge Center is the online home for all DB2 for z/OS product information. This site provides links to additional information resources and alternative documentation formats, such as the PDF manuals that I like (which are available in hard copy from the IBM Publications Center). The current sets of manuals available through the Knowledge Center are for DB2 versions 9, 10 and 11. I like that the search feature now automatically limits the search to the DB2 version tab you select.

 

I also appreciate that readers can add comments to each document. (Free registration is required to use this feature.) This is a simple way to voice your opinions to IBM as well as learn how others feel about the documentation.

Identifying Locked DB2 Resources

Last week I explained how to find the cause of a deadlock or timeout. This week, I'll show you how to identify the name and type of DB2 resource being locked. We'll start with the SQLCODE -911 message:

            THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO      DEADLOCK OR TIMEOUT.
            REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME   resource-name

The reason code will be either 00C90088 (deadlock) or 00C9008E (timeout). The resource-type can be found in the DB2 for z/OS CODES manual or at the IBM Information Center.

The common resource types related to objects being locked are:

            TYPE Code   Type of Resource     Name,Content,Format

            00000100      Database                   DB

            00000200      Table space               DB.SP

            00000201      Index space              DB.SP

            00000210      Partition                     DB.SP.PT

            00000D00     DBID/OBID                RD.DI.OI

            00000D01     DBID/OBID                DI.OI

 

What you will see in the error message are codes that have to be translated. For instance, here's a sample message for a locked database where the name is DSN8D11P:

DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK ORTIMEOUT. REASON 00C9008E, TYPE OF RESOURCE 00000100,AND RESOURCE NAME DSN8D11P

And here's a sample message for a locked table where the name is 403.43:

DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK ORTIMEOUT. REASON 00C9008E, TYPE OF RESOURCE 00000D01,AND RESOURCE NAME 00000403.00000043

As noted in the DB2 for z/OS Messages Guide and explained here, when the resource type is D01, you need to look for a table name. To do so, use this query:

             SELECT CREATOR, NAME
            FROM SYSIBM.SYSTABLES
            WHERE DBID = 403 AND OBID = 43;

 

If the resource-type is X'D00' or X'D01', use this SQL statement to determine the table name:

 

            SELECT CREATOR, NAME

               FROM SYSIBM.SYSTABLES
               WHERE DBID = dbid AND OBID = obid;

 

For more information related to this topic, see my previous posts about DB2 locking and application design for concurrency:

* Batch Tuning: Checkpoint Restart

* Batch Tuning:Continuous Availability

* Understanding DB2 Locks

 

DB2 Accelerator V4.1 Reaches a New Level

In my previous post I noted how much I got out of the opening keynote session at the recent IDUG NA conference. Another presentation I enjoyed was IBM's Peter Bendel discussing the latest enhancements to the IBM DB2 Analytics Accelerator for z/OS V4.1 (DB2 Accelerator).

I've touted the benefits of DB2 Accelerator numerous times, but I was amazed by what I heard about this latest version. We were told that customers are seeing 2,000X improvement in query performance. Yes, that's 2,000, with three zeros. In one specific example, a customer query that had required three hours of processing could now be run in 6 seconds.

One feature many of us have been waiting for is static SQL support. V4.1 brings some new BIND parameters, and I find some of the options quite intriguing. The first is QUERYACCELERATION, which has values of NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE and ALL. If you're wondering how ENABLE WITH FAILBACK works, it's pretty cool. With this setting, DB2 Accelerator will rebind the statement if it fails during execution on the accelerator and will route it back to run on DB2 without returning an error to the application.

Regardless of which bind option you choose, DB2 will report total failures at the accelerator level for each subsystem using IFCID 002. If enabled, IFCID 378/379 will record any SQL request routed to the DB2 Accelerator. These records will not contain SQL statement text. My understanding is more details are coming soon through maintenance for V4.1. You can also view information about past queries or currently running queries by using the SYSPROC.ACCEL_GET_QUERIES stored procedure. If you don’t want the workload to failback and run on DB2 due to resource constraints, then use BIND option ENABLE. This will force DB2 to fail the execution when an error occurs and return an error status back to the application.

Another new bind parameter is GETACCELARCHIVE (with values of NO and YES).  When set to yes and all the requirements for accessing partitioned archived data are met, the statement will retrieve archived data.

With V4.1, the workload is now balanced across multiple accelerators running on machines connected to z/OS and DB2. The accelerators notify DB2 of its capacity and DB2 will route the query to the most optimal accelerator. This is not a smooth migration. The accelerators must be at V4.1 and the tables need to be redefined to the accelerator.

When replicating data you no longer have to stop all tables when reloading a table. The log reader no longer need to read all log records for all tables, but instead can now filter on relevant log records which reduces overall CPU usage on the mainframe.

V4.1 also includes changes to the high-performance storage server, which allows you to store full tables or table partitions on the DB2 Accelerator rather than a dedicated host. The big change in this release is integrity of the data: when the data is archived and moved to the accelerator, the partition archived is put into a PRO state which will restrict modifications such as inserts and deletes.

There's much more I could cover if I had the time and space, but what do you think? If you've had a chance to use DB2 Analytics Accelerator for z/OS with your static SQL applications, please share in comments your impressions of the new V4.1 features.

Why is DB2 Rewriting my SQL?

The EXPLAIN statement provides a view into what DB2 determines is the optimum access path for accessing the data. The valid statements that can be processed by the EXPLAIN statement are SELECT, MERGE and INSERT, as well as search forms of UPDATE and DELETE. The information obtained from the EXPLAIN statement is placed in a set of user tables known as the EXPLAIN tables. The DB2 for z/OS Managing Performance guide has details about using EXPLAIN as well as lists of tables.

With DB2 11, column changes have been made to some existing tables. New tables have also been added to the family of EXPLAIN tables. For a complete list of changes, see Chapter 12 ("EXPLAIN Table Changes in Version 11) in the What's New guide.

As IBM brings new features such as temporal and archive tables to the database, it becomes more important to your tuning efforts to know when DB2 is modifying your SQL. These modifications to the EXPLAIN tables in DB2 11 are designed help you identify these changes:

* The PLAN_TABLE includes a new column, EXPANSION_REASON, that identifies the particular global variable or special register that's used to implicitly modify the query to support temporal or archive table processing.

* The DSN_PREDICATE_TABLE is an existing table with a change to the description. The description is: The predicate table, DSN_PREDICAT_TABLE, contains information about all of the predicates in a query. It is also used as input when you issue a BIND QUERY command to override predicate selectivities for matching SQL statements.

When DB2 rewrites the SQL, the ADDED_PRED values identify the actions that DB2 performed.

A list of ADDED_PRED values follows. I've added my own notes in parentheses.

blank -- DB2 didn't add the predicate.

'B' -- For bubble up. (Note: DB2 copies predicates from sub-select or table-expression  into the outer query to help filter rows more quickly.)

'C' -- For correlation. (Note: When a sub-select is written without a correlation, the optimizer determines that a correlated sub-select will perform better than a non-correlated query.)

'J' -- For join. (Note: When the optimizer detects that related tables are being processed without providing join criteria, join criteria is added.)

'K' -- For LIKE for expression-based index. (Note: The optimizer detected an expression index and modified the SQL to take advantage of this index.)

'L' -- For localization.

'P' -- For push down. (Note: The optimizer detected a predicate from an outer query that can be used in a sub-select or table expression.)

'R' -- For page range.

'S' -- For simplification.

'T' -- For transitive closure.

Hopefully IBM will soon publish additional documentation on these values and how they're being used to improve performance.

The DB2 for z/OS Performance Handbook

I’m very excited to announce that CA Technologies (my employer) commissioned Dan Luksetich to author a document on DB2 for z/OS performance. Dan is an amazing person who has tremendous knowledge and real-world experience tuning applications that access DB2 for z/OS.

Rather than try to explain what is unique about this document, I'll instead refer you to Dan's introduction:

About The DB2 Performance Content in This Book

There is a DB2 for z/OS Managing Performance Guide, and there are DB2 for z/OS performance redbooks. These valuable and often under utilized references are extremely important tools in your day to day performance tuning efforts. This book is not a repeat or condensation of that material, nor is it a replacement for that material. The intention of this book is to complement the printed performance information that is part of the DB2 documentation and to give the reader some insight on particular situations where certain features are of benefit or detriment. Therefore, it is the reader’s responsibility to cross-reference and research each feature and technique discussed in this book. Also, remember that nothing is free. With any deployment of any technique or feature there is going to be a cost to pay. I’ll attempt to balance those choices in this book.

This book is heavily focused on application, database, and SQL design and tuning for performance. Systems tuning and performance will be secondary to application and database tuning. It’s not that system tuning is not important, and you should take system tuning seriously. I will address some of the big buttons in this book.

This is a performance book and not a reference guide. What this means is that this book should be read from start to finish, and not used to look up various features to learn something about performance. There are many features that are addressed in multiple sections of this book, as well as settings, techniques, and features that have a relationship to each other and are not necessarily addressed in one place in the book. You can use this book as a reference, but it is strongly recommended that you read it entirely first!

This book is very much slanted toward transaction processing as opposed to data warehousing and business analytics. DB2 for z/OS is an excellent data warehouse engine. Recent advancements both in query performance and the introduction of the IBM DB2 Analytics Accelerator (IDAA) make it a great and economically feasible database for data warehouses. I’m especially an advocate of running both transaction processing and decision support against the same production database. However, in my personal experience most enterprises are utilizing DB2 for z/OS for transaction processing and not data warehousing. So, the majority of design and tuning techniques presented here are specifically in support of transaction processing systems. Many of the concepts can be applied to a data warehouse, especially the information included in sections 2, 3, and 4.

There is a balance to everything. This is a performance book. It is much more about performance than flexibility and availability. The concepts of performance, flexibility, and availability are generally opposed to each other, especially when implementation and operation costs are of concern. It is your responsibility to strike a balance in your implementation that is appropriate for your enterprise and situation.

The information provided in this book is relevant through DB2 11 for z/OS. In most feature descriptions I make no distinction between versions when discussing various features. Be sure to check the DB2 manuals for the version you are working with to confirm the availability of specific features

I'll also include the table of contents:

 

            Section 1: The Basics of Performance and Performance Tuning

            Section 2: SQL Access Paths

            Section 3: Coding SQL for Performance

            Section 4: Utilizing DB2 EXPLAIN

            Section 5: Controlling SQL Access Paths

            Section 6: Table and Index Design for High Performance

            Section 7: Memory, Subsystem Parameters and Performance

            Section 8: Monitoring Subsystem and Application Performance

            Section 9: Testing Performance Designs and Decisions

            Section 10: Top Tuning Tips

            Section 11: About the Author

 

The DB2 for z/OS Performance Handbook can be downloaded from CA Technologies. Registration is required.

Static SQL Support a Key Feature of DB2 Accelerators V4.1

Last fall IBM announced DB2 Analytics Accelerator (DB2 Accelerators) for z/OS Version 4.1. In contrast to previous releases, which supported a limited SQL syntax (and only for dynamic SQL), V4.1 enables a wide array of queries with static SQL support. I've written frequently about DB2 Accelerators, and from what I've seen on recent customer visits, I believe that the enhancements in V4.1 are contributing to increased adoption of this product.

Here's a quick list of other new features:

·         Support for EBCDIC and Unicode in the same DB2 system and accelerator.

·         Support for multi-row FETCH

·         Improved scalability workload balancing through automated routing, based on workload balancing information, that evenly distributes the workload across the attached accelerators.

·         Improved high availability through automated workload balancing failover support. If, for any reason, one accelerator is unavailable, queries are automatically rerouted to available accelerators.

·         Increased number of replication sources per accelerator: It's now possible to replicate from up to 10 different subsystems into a shared accelerator.

·         Better performance for incremental update through Instrumentation Facility Interface (IFI) filtering, which is designed to lower MIPS utilization and improve performance because fewer log records need to be processed by the capture component.

·         Richer monitoring: The accelerator now provides expanded information for monitoring applications with the IFI. The IFI makes this data available to software vendors who can leverage this broader information to better inform customers about the usage, execution and concurrency of queries as they are processed in the accelerator.

·         Significant enhancements to the High Performance Storage Saver (HPSS) solution:

o    Support to update historical read-only record through a stored procedure;

o    Support to restore individual partitions without requiring any outages;

o    The capability to enforce read-only state on a partition used by HPSS; and,

o    Backup image maintenance can be simplified by marking HPSS partitions as "undeletable."

·         Simplified maintenance and lower cost of operation. V4.1 now offers an automated NZKit Install that facilitates the fully automatic installation and activation of Netezza kits, with no firmware or operating system updates required.

·         Support for DB2 for z/OS version 11.

If you’re using this technology, please share some of your experiences, pros and cons, in comments.

Array Support in DB2 11

DB2 11 brings many new SQL processing enhancements that are designed to simplify application coding. One such enhancement is support for arrays. An array is a user-defined data type that consists of an ordered set of elements of a single built-in data type. Elements can be accessed and modified by their index position. An array type is created with an SQL CREATE TYPE (array) statement. An array type can be used as a parameter of a procedure and as a variable in an SQL procedure.

Here's the syntax for creating a user-defined array:

        >>-CREATE--TYPE--array-type-name--AS--| built-in-type |--------->

 

                     .-2147483647-------.      

        >--ARRAY--[--+------------------+--]---------------------------><

                     +-integer-constant-+      

              '-| data-type2 |---'  

There are two array types: ordinary or associative. An ordinary array type is defined using the ARRAY(integer-constant) syntax. The maximum cardinality is based on the value integer-constant. The value must be an integer between 0 and positive 2147483647. The maximum value is the default size (2147483647).

Here's an example of an ordinary array user-defined type. The company-name element can be up to 100 characters in length, while the company-name array can contain up to 75 elements.

            CREATE TYPE company-name AS VARCHAR(100) ARRAY(75)

ARRAY(data-type2) is an associative array that's indexed by data-type2. The data type must be either INTEGER or VARCHAR, and the value assigned to the index must be of the same data type. Each index is unique, and as a new index is assigned, the cardinality is incremented by 1. The maximum cardinality of an array is limited by the total amount of memory available to DB2 applications.

For more information on ARRAY TYPE, see the DB2 11 for z/OS SQL Reference Guide.

Next week I'll explain how user-defined data types (ARRAY TYPE) can be used.

DB2 10-11 Migration: Application and SQL release incompatibilities

I previously noted that DB2 11 allows applications with incompatible SQL or XML to run in compatibility mode.

Here I'll examine a related question: What will cause an application to be incompatible?

IBM provides this information in the DB2 for z/OS Installation and Migration Guide, in the section titled, "Application and SQL release incompatibilities."

A list of potential incompatibilities, along with brief descriptions, follows. See the installation and migration guide to understand how these incompatibilities can impact your applications and how to deal with them.

* Change to determination of ASUTIME for dynamic statements -- In Version 11, new-function mode with application compatibility is set to V11R1, and the dynamic SQL ASUTIME limit for each routine is used by the resource limit facility.

* Automatic rebind of plans and packages created before DB2 Version 9 -- Plans and packages that were last bound before Version 9 are not supported in Version 11 conversion mode and later.

* Change in default for ODBC-limited block fetch initialization keyword -- The default for the LIMITEDBLOCKFETCH initialization keyword is changed in Version 11.

* DB2 objects created with period specifications in Version 10 -- In Version 11, views, materialized query tables and SQL table functions that were created with period specifications in Version 10 are not supported.

* Dropping columns -- In Version 11 new-function mode, columns named CLONE, ORGANIZATION or VERSIONING should be specified as a delimited identifier to be dropped from a table.

* XPath processing -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, XPath is allowed to continue processing even when errors occur on filtered results when using predicate expressions with explicit cast or operations with an invalid value.

* XML document node implicitly added on insert and update -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, if an XML document does not have a document node, then one is added during insert and update operations.

* Client information special registers length -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, special registers for client information fields might return different length values. The values in the CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNAME, CURRENT CLIENT_APPLNAME, and CURRENT CLIENT_ACCTNG special registers are determined by the application compatibility level.

* Client information results from ADMIN_COMMAND_DB2 -- In DB2 Version 11 conversion mode, the ADMIN_COMMAND_DB2 result set row returns changes in the created global temporary table SYSIBM.DB2_THREAD_STATUS when processing-type = "THD". The column data type and maximum lengths for WORKSTATION, USERID, APPLICATION and ACCOUNTING change.

* Altering limit keys blocks immediate definition changes -- In DB2 Version 11 new-function mode, if a limit key is altered for certain table space types, immediate definition changes cannot be made until the limit key changes are materialized.

* Removing the SYSPUBLIC schema from the SQL PATH routine option -- Starting in DB2 Version 11 conversion mode, SYSPUBLIC is the schema that is used for public aliases. As such, the SQL PATH routine option must not specify the SYSPUBLIC schema.

* SYSIBMADM schema added to the SQL path -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, SYSIBMADM is added to the SQL path as an implicit schema.

* Change in result for CAST(string AS TIMESTAMP) -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the result of CAST(string AS TIMESTAMP) is changed in some cases.

* New maximum lengths for values that are returned for some built-in functions -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the maximum lengths for values that are returned for some built-in functions is decreased.

* Timestamp string representations -- DB2 Version 11 new-function mode with application compatibility set to V11R1 strictly enforces valid string representations of timestamp values.

* SQL reserved words -- Version 11 has several new SQL reserved words. These are listed in reserved words.

 

* Fully qualified user-defined function names -- When using a user-defined function with the same name as a built-in function that has been added to Version 11, the function name must be fully qualified. If the function name is unqualified and SYSIBM precedes the schema used for this function in the SQL path, DB2 invokes one of the built-in functions.

* SQLCODE changes -- Some SQLCODE numbers and message text may change in DB2 Version 11. Also, the conditions under which some SQLCODEs are issued may change.

With earlier DB2 releases, addressing incompatibilities would require substantial changes and testing before the migration could take place. That's the major benefit of DB2 10 compatibility mode. It enables customers to migrate quickly to DB2 11 while maintaining current applications until they can be modified and tested on the new release.