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.

An In-Depth Look at Buffer Pool Monitoring and Tuning

IBM recently released a Redpaper, "IBM DB2 11 for z/OS Buffer Pool Monitoring and Tuning."

While this document is intended for DB2 systems administrators, it could also be useful for those who specialize in z/OS system tuning. As someone who knows a bit about tuning buffer pools and the challenges this process entails, I'm excited to see IBM devote a Redpaper to this topic.

I've yet to read the whole thing, but the material I've been through is excellent. This isn't surprising given that the author, Jeff Berger, is one of IBM's top performance specialists.

Here's the chapter list:

            Chapter 1. Introduction
            Chapter 2. Prefetch and RID pool
            Chapter 3. Buffer pool writes
            Chapter 4. Data sharing
            Chapter 5. Workfiles
            Chapter 6. Assigning page sets to buffer pools
            Chapter 7. Improving CPU performance
            Chapter 8. Index and data organization

The first five chapters provide a great introduction. You'll learn what buffer pools are and how the tuning knobs are used to control their behavior. This information is very valuable for newcomers, or anyone in need of a buffer pool refresher course.

I believe that splitting up buffer pools is essential to effectively tune them. When I was consulting, I found that many customers solely rely upon the main buffer pool (BP0). My approach was to set up separate pools for random and sequential access -- a buffer pool for sort work files. However, in Chapter 6, Jeff makes a recommendation that I hadn't considered: assigning buffer pools based on sequential insert activity. As I said, he's the expert.

When I was tuning buffer pools, my focus was on reducing physical I/O, so I appreciated the information in Chapter 7, which covers the settings you should use to reduce CPU consumption. The final chapter examines when you should consider reorganizing index and data spaces.

I know from experience that setting up multiple buffer pools isn't simple, but the performance benefits it provides are worth the effort. This Redpaper offers valuable information to help you navigate this process.

Please post your experiences and/or questions on buffer pool tuning.

Using the New Compatibility Mode

In May when I posted a summary of new DB2 11 features, I mentioned that the latest release allows applications with SQL or XML to be run in compatibility mode. Since this is an important feature that will help customers much more quickly migrate to DB2 11, I'll provide some detail.

In DB2 11, you can set the SQL function and features available to an application package based on an application compatibility value. The values are either V10R1 or V11R1. Leaving the application value at V10R1 gives you time to evaluate any changes in behavior when switching over to V11R1. Should you experience a negative behavior, you can just switch back to V10R1. The application compatibility value can be set through a bind option, a subsystem parameter or the special register.

A new subsystem parameter, APPLCOMPAT, is used to set the application programming value. During a migration this will default to V10R1; during a new install it will default to V11R1. These default behaviors can be changed using install panel DSNTIP41. It's recommended that the default value be the same for all members of a data sharing group. 

With a value of V10R1 during a migration, all existing applications will continue to execute as if they're on a DB2 10 version subsystem -- even when running in DB2 11 new function mode.

Over time, as you ensure everything is stable in DB2 11 (with compatibility mode set to V10R1), you can start using the BIND/REBIND parameter, APPLCOMPAT, with a value of V11R1. This, obviously, enables the application to exploit the new features available in DB2 11 new function mode. Note: The BIND/REBIND command parameter APPLCOMPAT only sets the compatibility mode for static SQL statements.

For dynamic SQL, use the special register, CURRENT APPLICATION COMPATIBILITY, to specify the compatibility level. The initial value of CURRENT APPLICATION COMPATIBILITY is based on the APPLCOMPAT value used in the BIND PACKAGE:

            Example to find the current application compatibility value:
                SET :host-variable = CURRENT APPLICATION COMPATIBILITY;

            Examples to set the special register:
                 SET CURRENT APPLICATION COMPATIBILITY = :host-variable;
                 SET CURRENT APPLICATION COMPATIBILITY = ‘V10R1’;

By providing the capability to manage and reduce negative behaviors that can occur during the migration process, the new compatibility mode should make it easier and quicker for customers to move to new DB2 releases.

If you've already used the new compatibility mode during a migration to DB2 11, please share your thoughts about this feature in comments.

New Stored Procedure Simplifies EXPLAIN Maintenance

Managing EXPLAIN tables can be time-consuming. DB2 developers often create EXPLAIN tables and allow them to exist on the system for years without being upgraded to include the latest columns or support the newer table types that have been introduced in recent DB2 releases. Because these tables aren't current, they're incapable of utilizing key features that are designed to help diagnose the access paths DB2 has chosen and why.

In short, if you're a developer and you rely on EXPLAIN tables that are based on older DB2 releases, you're missing out.

A new stored procedure, SYSPROC.ADMIN_EXPLAIN_MAINT, is designed to simplify the process of upgrading existing EXPLAIN tables. It can also be used to create the new tables that are introduced with each release of DB2. (See, for example, DSN_PREDICATE_SELECTIVITY, a new table in DB2 11.) So if you have an old set of tables, this procedure will make sure they're current. And if you're missing any of the latest tables, it will create them for you.

IBM is releasing this new procedure through maintenance APAR PI05200 (NEW STORED PROCEDURE SYSPROC.ADMIN_EXPLAIN_MAINT) for DB2 11 only. It was closed for customer testing on April 25 and will now generate PTF UI17423.

To learn more about this new stored procedure and view a sample program used to call it, see the DB2 11 Managing Performance Guide (updated April 2014).

 

Excluding NULL Key Columns

I've never been a fan of setting columns to null, because this introduces complexities into the application code. I'm certainly not the only one who feels this way, either. Check out this well-written screed that explains in detail why NULL values in a database are a programmer's nightmare.

Increasingly though, applications with database designs that incorporate NULL values -- developed by SQL Server, Oracle, Sybase and others -- are being brought to DB2 for z/OS. Of course DB2 for z/OS already supported these distributed databases, but DB2 11 provides new function that makes it easier to deal with NULL settings.

DB2 11 features the capability to exclude NULL key columns, which is designed to improve performance and reduce index sizes. When NULL key columns are excluded from an index, DB2 only adds an entry when the key column is not NULL. To specify that an index excludes NULL keys, create the index using the CREATE INDEX statement.

As you can imagine, if your data has a high percentage of NULL values in key columns, then the index will be much smaller and a scan will perform much more quickly since fewer entries will be in the index.

If you're currently supporting a database with NULL key columns and have had a chance to take advantage of the new DB2 11 feature to exclude NULL keys, please share your experiences in comments.

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.

Redbook Details Key DB2 11 Enhancements

IBM just released a draft version of the Redbook, "DB2 11 for z/OS Performance Topics." The publication details key functions for those migrating from DB2 Version 10.

DB2 10 was a major release that provided out of the box CPU savings, and this direction continues with DB2 11. Most of the DB2 11 CPU reductions are built into the DB2 engine and do not require application changes. You can take advantage of these enhancements even in CM mode (after REBIND for static SQL and PREPARE for dynamic).

IBM points to these CPU reductions for customers migrating from DB2 10:

• Online transaction workloads savings of up to 10 percent.
• Update-intensive batch workload savings up to 10 percent.
• Complex reporting queries savings of 25-40 percent when processing compressed tables. 
• Queries executed on DB2 Analytics Accelerator can see up to 10 percent savings.
• zIIP eligibility can contribute to cost reduction as the majority of asynchronous SRB processing under DB2 address space as well as the RUNSTATS and LOAD utilities are now zIIP-eligible.

Of course there's far more than I can include here, but to give you a broad idea of the material covered in this publication, here's the chapter list:

Chapter 1: Introduction

Chapter 2: Subsystem

Chapter 3: Synergy with z platform

Chapter 4: Availability

Chapter 5: DB2 Analytics Accelerator

Chapter 6: Data sharing

Chapter 7: SQL

Chapter 8: Application enablement

Chapter 9: Distributed environment

Chapter 10: Utilities

Chapter 11: Installation and migration

Chapter 12: Monitoring

If a DB2 10-to-DB2 11 migration is in your near future -- or if you've already completed one -- this Redbook is well worth your time. I'd appreciate hearing about your impressions of the document, as well as your views on DB2 11's most compelling enhancements. Please share your thoughts in comments.

Plan Program Authorization

A DB2 plan is used to set runtime parameters and security when executing applications on a local DB2 system. A plan specifies run-time options as well as one or more collections of program “packages” that the plan can execute.

The list of packages that a plan can use is defined in the package list parameter at the time the plan is bound. To avoid having to rebind the plan every time it needs to be authorized to execute a package, a collection name is used with a wild card package name to provide dynamic access to any package in the collection. For example you may allow employees to view payroll information but restrict them from updating it. They would use the READPAY PLAN that uses the collection (READONLY_PAYINFO with a program package called PAY001. The syntax for the BIND is:

            BIND PLAN (READPAY)

               PKLIST(READONLY_PAYINFO.PAY001)

 

To bind this plan so program PAY002 can be added to the READONLY_PAYINFO collection without the need to rebind it with the new package name, use this syntax:

 

            BIND PLAN (READPAY)

               PKLIST(READONLY_PAYINFO.*)

 

Now, as packages are added to the collection READONLY_PAYINFO, they are available to the plan READPAY.

Of course, if a package is inadvertently added to a collection, any authorized user could then execute the plan, which could lead to unauthorized uses of the application package. To protect against these types of exposures, a new bind and rebind plan option was introduced with DB2 11. This option serves essentially as a double check that's designed to ensure, as IBM puts it, "a valid program is authorized to execute the plan."

As is often the case when new features are introduced, plan program authorization isn't tightly integrated with DB2; therefore, some manual steps must be implemented. First, you must create the DB2 table, SYSIBM.DSNPROGAUTH. The DB2 sample library SDSNSAMP(DSNTIJSG) contains both DDL to create table SYSIBM.DSNPROGAUTH and sample DML to INSERT a row into the table.

The row inserted into SYSIBM.DSNPROGAUTH contains information to authorize a PLAN to be used for a given program package. To activate plan program authorization, you must BIND or REBIND a PLAN using the new parameter, PROGAUTH. Once enabled (the system default is DISABLE), DB2 will examine table SYSIBM.DSNPROGAUTH to find a row with combination of PLAN NAME and PACKAGE NAME.

Several new reason codes are available to help determine why program authorization may fail. Here's an example:

00F3003C The program authorization is enabled by specifying PROGAUTH option when the plan was bound. The MDC value for the program does not match the program authorized to use the plan. The request to allocate a plan to the program name is denied.

If the program name exists in SYSIBM.DSNPROGAUTH table, then correct the ROGMDCVAL and PROGMDCPAD values for the program. Otherwise, the program name must be added to the SYSIBM.DSNPROGAUTH table.

To find a list of plans that have enabled program authorization:

 

      SELECT  NAME, VALID, OPERATIVE,

                      CAST(QUALIFIER AS CHAR(10)) AS QUALIFIER,

                      RELBOUND, PROGAUTH, PLENTRIES

      FROM SYSIBM.SYSPLAN

      WHERE PROGAUTH = ‘E’

      WITH UR

 

Do you ever need to lock down individual packages so they can only be used by specific plans? Personally, I've never required this level of security, and I'd worry that it could be a maintenance nightmare. If DB2 customers are really demanding this feature, I'd expect IBM to soon add the capability to insert into the DSNPROGAUTH table through a grant statement or some form of external security package. What are your thoughts on this?

Using Arrays

Last week I discussed the new user-defined ARRAY data type in DB2 11. This is the first DB2 for z/OS release to support arrays, although DB2 for Linux, UNIX and Windows has supported arrays since DB2 V9.5 (October 2007).

The DB2 11 for z/OS SQL Reference Guide lists and defines the SQL built-in functions that can be used to assign values as well as retrieve first, last and next values. It also includes numerous examples that show how to assign values to an array. I'll cite two here:

 

            Example 1: Suppose that the array variable RECENT_CALLS has the array type

            PHONENUMBERS. Assign an array of fixed numbers to RECENT_CALLS.

 

            SET RECENT_CALLS = ARRAY[9055553907, 4165554213, 4085553678];

 

            Example 2: Suppose that the array variable DEPT_PHONES has the array type             PHONENUMBERS. Assign array phone numbers that are retrieved from the  DEPARTMENT_INFO table to DEPT_PHONES.

            SET DEPT_PHONES =

              ARRAY[SELECT DECIMAL(AREA_CODE CONCAT ’555’ CONCAT EXTENSION,16)

                            FROM DEPARTMENT_INFO

                            WHERE DEPTID = 624];

 

Java developers should review the DB2 11 for z/OS Application Programming and Reference for Java manual. As you'd expect from the title, this manual provides examples usage in Java. The DB2 11 z/OS Application Programming and SQL Guide covers other languages like C, COBOL and REXX.

Another resource on arrays is the IBM Redbook, "DB2 11 for z/OS Technical Overview." Included in this publication is the following example that illustrates how to use the ARRAY_AGG function to return an array of employee phone numbers:

 

                CREATE TYPE PHONELIST AS CHAR(4) ARRAY[];

                CREATE FUNCTION PHONELIST_UDF (LOWSAL DECIMAL(9,2))

                    RETURNS PHONELIST

                    LANGUAGE SQL

                    CONTAINS SQL

                    NO EXTERNAL ACTION

                    RETURN

                    (SELECT ARRAY_AGG(PHONENO ORDER BY SALARY)
                     FROM DSN81110.EMP WHERE SALARY > LOWSAL)

 

Do you have a creative use for arrays? Please share your ideas 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.