Db2 for z/OS Performance Traces Part 1 - Intro, Accounting, and Audit

When it comes to Db2 for z/OS performance management, one of the first things you need to understand is the traces that are available that must be started in order to cause Db2 to track performance metrics. You can think of a Db2 trace as a window into the performance characteristics of aspects of your workload. Db2 traces record diagnostic information describing specific performance events. As Db2 operates, and workload is run against it, Db2 writes trace information based on the traces types that have been started. The information is written out in such a way that it can be read and analyzed by DBAs and performance analysts.

There are six types of Db2 traces available, each one pertaining to a different category of Db2 performance and operations. These six types of traces are:

• Accounting - performance information about the execution of DB2 application programs 
• Audit - information about Db2 DDL, security, ­utilities, and data modification activities
• Global - information for servicing Db2 
• Monitor - data useful for online monitoring of the Db2 subsystem and application programs 
• Performance - detailed data about Db2 events, enabling database and performance analysts to pinpoint the causes of performance problems 
• Statistics - information regarding the Db2 subsystem’s use of resources 

There are two ways to start most Db2 traces: you can specify the appropriate DSNZPARMs to start traces when Db2 starts up or you can issue the -START TRACE command to initiate specific traces when Db2 is already running. 

Each trace is broken down further into classes, each of which provides information about aspects of that trace. 

Accounting Trace

The accounting trace is one of the most important traces for judging the performance of Db2 application programs. Accounting trace records can contain information regarding:

• CPU and elapsed time of the program
• Usage of the EDM pools
• Locks and GETPAGE page requests, by buffer pool, issued by the program
• Number of synchronous writes
• Thread wait times
• Type of SQL issued by the program
• Number of COMMITs and ABORTs issued by the program
• Program’s use of sequential prefetch and other DB2 performance features (RLF, distributed processing, and so on)

There are ten groups, or classes, of Db2 accounting traces:

• Class 1: Standard accounting information
• Class 2: Entry or exit from DB2 events
• Class 3: Elapsed wait time in DB2
• Class 4: Installation-defined accounting record
• Class 5: Time spent processing IFI requests
• Class 6: Reserved
• Class 7: Entry or exit from event signaling package accounting
• Class 8: Wait time for a package
• Class 10: Package detail
• Class 11 through 28: Reserved
• Class 30 through 32: Local use

Most organizations start Db2 accounting classes 1, 2, and 3 together because they provide a great deal of information with only a low overhead. Furthermore, you cannot run class 2 or 3 without also running class 1.

Accounting trace classes 7 and 8 provide performance trace information at the package level. For class 7 accounting, trace information is gathered for packages executed, and for class 8 information, the amount of time an agent was suspended in Db2 for each executed package is collected. If you have already activated accounting trace classes 2 and 3, adding trace classes 7 and 8 incurs only a minimal additional cost.

Audit Trace

The audit trace is useful for installations that must meticulously track specific types of Db2 events, particularly to support regulatory and compliance requirements. Not every shop needs the audit trace. However, those wanting to audit by AUTHID, specific table accesses, and other Db2 events mayfind the audit trace invaluable. 

Of course, as of Db2 10 for z/OS, an alternate approach to auditing with audit policies is available. This requires use of SECADM, which is not pervasively implemented, though.

There are eight categories of audit information that can be provided using the audit trace:

• All instances in which an authorization failure occurs, for example, if USER1 attempts to SELECT information from a table for which he or she has not been granted the appropriate authority
• All executions of the DB2 data control language GRANT and REVOKE statements
• Every DDL statement issued for specific tables created by specifying AUDIT CHANGES or AUDIT ALL
• The first DELETE, INSERT, or UPDATE for an audited table
• The first SELECT for only the tables created specifying AUDIT ALL
• DML statements encountered by DB2 when binding
• All AUTHID changes resulting from execution of the SET CURRENT SQLID statement
• All execution of IBM DB2 utilities

Again, there are groups, or classes of trace information that can be started. In the case of the audit trace, there are eleven trace classes:

• Class 1: Attempted access denied due to lack of authority
• Class 2: GRANT and REVOKE 
• Class 3: CREATE, ALTER, and DROP statements against audited tables
• Class 4: First change made to an audited object
• Class 5: First read made against an audited object
• Class 6: BIND information for SQL statements on audited objects
• Class 7: Assignment or change of an AUTHID
• Class 8: Utility execution
• Class 9: Installation-defined audit trace record
• Class 10: Trusted Context information
• Class 11: Audit administrative authorities
• Class 12 through 29: Reserved
• Class 30 through 32: Local use

The overhead of the audit trace depends greatly on the amount of data that is being audited. Things like transaction frequency, modification frequency, DDL activity, and so on will dictate the actual amount of overhead required.

Next Time...

So far, we have only looked at two of the six types of Db2 traces. Be sure to check back later for Parts 2 and 3 of this blog series that will introduce the remaining types of traces and additional useful information on dealing with Db2 performance tracing.

The Db2 12 for z/OS Blog Series - Part 22: Function Levels 501 and 502 (Continuous Delivery)

If you have heard anything about Db2 Version 12 chances are that you have heard about continuous delivery. Instead of waiting 2 to 3 years for a new version of Db2 to be released, new functionality will be continuously delivered on a regular basis. The idea is to bring Db2 into the modern age of development practices where releases are small and quick, instead of large and slow.

So instead of waiting for the next version, Db2 professionals now wait on new Function Levels, where a Function Level identifies a set of new enhancements that can be enabled in Db2 for z/OS.

Of course, this means that a lot of internal practices and procedures had to be re-engineered and established at IBM, so there have not been many new Function Levels since Db2 12 was first released back in October 2016. There was Function Level 501 in early 2017, which basically added a simple new built-in function, LISTAGG.

The LISTAGG built-in function produces a list of all values in a group. An optional separator argument can delimit items in the result list. For example, specifying a comma as the separator produces a comma-separated list. An optional ordering can also be specified for the items within the group. So for example:

SELECT   WORKDEPT,
         LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
             AS EMPLOYEES
FROM     EMP
GROUP BY WORKDEPT;

This will return a comma-separated list of employee last names by department number.

Unless you needed the capability of LISTAGG in your applications there was no reason to migrate to Function Level 501. Except, of course, to test out moving to a new Function Level, which is the primary reason that IBM released LISTAGG as a Function Level. And that was it until recently…

Function Level 502 (FL502) was made available by IBM in late April 2018. This is the first “real” Function Level with multiple new capabilities that may entice your shop to implement it. 

Here are the capabilities introduced in FL502:

The first new feature bolsters DFSMS data set encryption (which is part of the Pervasive Encryption for IBM Z solution introduced with the z14). With FL502 we get KEYLABEL management capability for z/OS DFSMS data set encryption. You can manage the key labels for z/OS DFSMS data set encryption to transparently encrypt Db2 data sets. 

DFSMS can be used to encrypt various types of Db2 data sets including Db2-managed table space and index space data sets, data sets that are used by Db2 utilities, and sequential input and output data sets. 

After moving to FL502 an administrator (DBA, security admin, system admin or storage admin depending on your shop) can enable z/OS DFSMS data set encryption for your Db2 data sets.

Additionally, IBM offers a free tool, IBM z Systems Batch Network Analyzer (zBNA), which can be used to help estimate the costs of DFSMS data set encryption for your Db2 data sets. Additionally, the Db2 Statitistics Trace has been enhanced to report CPU time, which you can look at to help determine which data sets to encrypt.

The second enhancement enabled with FL502 is the ability to cast an explicit numeric value to a graphic string value. All of the numeric data types are supported. So you can use the GRAPHIC or VARGRAPHIC built-in functions and/or the CAST specification to cast numeric values to graphic string values. Regardless of whether CAST or GRAPHIC/VARGAPHIC functions are used, the result is Unicode (UTF-16), and the context must support Unicode data.

Implementing Function Level 502

You can activate Function Level 502 from Function Level 501, 500, 100, or as part of migration from Db2 11 (with z/OSMF only). Function level 502 requires catalog level 502, and tailoring the catalog for level 502 requires function level 500 or 501. Take care before activating any new Function level by making sure that you understand what Function Levels are, how they are delivered, and the current state of your Db2 subsystems.

You can easily view the current state of your Db2 subsystems by using the -DISPLAY GROUP command. It will show you the current Function Level, the high Function Level ever activated (which might be higher than current if you fell back), and the highest possible Function Level (based on the APARs that have been applied to your Db2 system).

What's Next?

Things are likely to speed up in terms of new Function Levels for Db2. Now that IBM has had time to implement new internal development proceudres and get them all test out appropriately, we should start seeing new capabilities more frequently than once a year... perhaps as frequently as quarterly. So make sure that you are ready to review every new Function Level as it is made available and make plans to activate the ones that deliver functionality that you need.

Another thing to keep in mind is that Function Levels are cumulative. So you cannot implment say, Function Level 502, without also getting the capabiltiies of all previous Function Levels (in this case, just 501). So be prepared and understand what activating a new Function Level means!

Welcome to the new world of continuous delivery in Db2 for z/OS… and take a look at how the new capabilities in Function Levels 501 and 502 might be useful at your shop and to your applications.

Improving Performance by Caching Select Statement Results

A significant trend in the database world these days is moving more data management and processing into memory. If you can bypass disk I/O you can achieve tremendous performance gains. There are many reasons for this, but of course, the most important reason is that disk access is much slower than memory access.

It is orders of magnitude more efficient to access data from computer memory than it is to read it from disk. Memory access is usually measured in microseconds, whereas disk access is measured in milliseconds (1 millisecond equals 1000 microseconds).

So, it makes sense to process as much data as we can in memory. This is the reason that most popular RDBMS use buffer pools to cache data in memory. But buffer pools are not always sufficient for caching everything that is needed. Just take a look at IBM Db2 12 for z/OS and the index FTB feature, which caches unique index structures in memory – not in any buffer pool.

FTBs, or Fast Traverse Blocks, provide in-memory index optimization. FTBs are stored in a separate area outside of your buffer pools. You must allocate additional real memory for it in Db2 12. This memory is an optimized structure for fast index lookups that provide random index access. The FTB contains the non-leaf structure of the index.

QuickSelect for Db2

Of course, FTBs are just one technique to optimize your memory usage. Another technique you might consider is to deploy QuickSelect for Db2, an in-memory optimization product from Log-On Software (an Israeli company that has been providing tools and services to the mainframe market for decades). QuickSelect for Db2 caches query results in memory. This is a different concept than buffer pools which cache table and index space pages or in-memory tables which cache entire tables.

By caching the results of frequently run queries in memory with QuickSelect for Db2, you can optimize the SQL statements that recur frequently in your environment. QuickSelect runs as a started task and it automatically caches repetitive query results in self-managed memory above the bar (64-bit).

QuickSelect returns the same data DB2 would – but from its cache. QuickSelect will identify and cache only frequently used data via a threshold parameter. This saves both CPU and I/O, and therefore can reduce elapsed time and improve application response times. All other requests are satisfied as usual by Db2.

Which leads to the question: which queries should be cached? QuickSelect helps you to identify ideal queries with its Survey mode which automatically finds SQL queries suitable for caching.

The results cached by QuickSelect are the actual data retrieved by Db2 queries. So the next time that same query runs (with the same host variable values) the results are quickly returned from the QuickSelect cache instead of invoking process-heavy disk I/O operations. Think of all the repetitive SQL that you are running in your shop every day – and all of the redundant I/O that is used to read that data over and over again.

But, you may be asking, what happens if the data changes? Well, QuickSelect is aware of all changes that are made, whether by a Db2 utility like Load, Reorg and Recover, or by your applications issuing Insert, Update, Delete, and Truncate statements. Data changes on the tables that are cached are detected by QuickSelect in real-time using various techniques. QuickSelect invalidates the cached data for the updated tables automatically, thus data integrity is preserved. Further queries will cause QuickSelect to cache the updated data, thereby continuing to save. QuickSelect runs in a multiple LPAR environment and supports update sensitivity by using XCF functions.

In other words, QuickSelect returns the same answer as Db2 does… only faster. And, of course, that is the most important thing.

If you were paying attention as you read through this so far you will have noticed that there are no application changes required whatsoever to enable QuickSelect. And you do not have to Rebind your programs to take advantage of QuickSelect, either!

What happens if you shut down QuickSelect? There should be no impact on your application programs; processing reverts entirely back to Db2. The data will no longer be returned from the QuickSelect cache, instead it will again be retrieved by Db2 just like before you used QuickSelect.

QuickSelect: The Latest Features

Log-On Software is not resting on its laurels; new features are added to QuickSelect on a regular basis. For example, in Febrauary 2018 auto-configuration was added to QuickSelect, thereby easing configuration in a Data Sharing environment.

With auto-configuration the QuickSelect group configuration is dynamically determined. It is no longer required to pre-configure QuickSelect groups. QuickSelect now automatically detects the active members in the Db2 group, if any, and automatically establishes a connection between all QuickSelect servers that correlate to the current Db2 configuration. This is much simpler than the previous requirement of hard-coding QuickSelect grouping using pre-defined parameters.  

The Bottom Line

QuickSelect for Db2 offers a different approach to in-memory caching by focusing on query results. QuickSelect for Db2 customers have been able to significantly reduce CPU during peak times – the times set by the rolling four-hour average that impact your monthly IBM software bill.

For example, one customer, a large European commercial Bank was able to turn off an entire CPU after installing QuickSelect in production due to the CPU savings they achieved. In this case, the customer was saving more than 10 billion SQL statements during a typical 12-hour window.

The bottom line is that in-memory processing can save CPU, improve performance and save money. Wise organizations will look into multiple ways of exploiting memory to achieve results, including potentially looking at novel software solutions like QuickSelect for Db2.

---

Catch You in Philly for the IDUG Db2 Tech Conference 2018

Well, this year’s IDUG North American DB2 Tech Conference is almost upon us and I hope you have made plans to be there. If not, it is still not too late, though. The conference is being held in Philadelphia, PA this year -- The Cradle of Liberty -- from April 29 - May 3, 2018.

I don’t know about you, but every year I mark my calendar ahead of time and then look forward to the week of IDUG because it always provides an educational and enjoyable time. If you’ve ever attended an IDUG conference before then you know all about the fantastic educational and entertainment opportunities that IDUG offers. Of course, there will be many informative technical sessions on all of the latest and greatest Db2 technologies and features. The 2018 event offers more educational opportunities and training than ever before, including:

• Five days of educational sessions
• Half and full-day workshops
• More than 100 one-hour technical sessions
• Two expert panels on z/OS and LUW

There are also two great keynote sessions scheduled this year. The first, from Michele Goetz, an analyst with Forrester Research, whose session is titled “Your Business Is Only As Fast As Your Data.” She will discuss the importance of data governance programs and how to establish sustainable governance.

And then there is the one I am most looking forward to, a visit from Andrew Flip Filipowski, IDUG co-founder, Executive Chairman and CEO SilkRoad Equity, founder and former CEO of PLATINUM technology, inc. and current co-CEO of Fluree, PBC, provider of the world’s only ACID compliant blockchain graph database system. Flip is always educational and entertaining, so I expect a great keynote session from him as he discusses a wide range of topics such as bitcoin, crypto assets, blockchain, FinTech, venture capital, entrepreneurship, and more.

I’m also looking forward to the spotlight session on “Db2 for z/OS…and Beyond” from Jeff Josten (chief architect of Db2 for z/OS) and Maureen Townsend (Db2 for z/OS Development Director).

As usual, I will be busy at this year’s IDUG. I will be delivering two sessions this year. The first is on Wednesday at 3:40 PM (Session 5028) titled Db2 Application Development for Performance: Be Early and Be Informed. This session is aimed at application developers. The general idea is to give an overview of the things that you can do as you design and code your Db2 programs with performance in mind. All too often performance is an afterthought – and that can be quite expensive. Nail down the basics by attending this session!

My second presentation, on Thursday at 9:20 AM, is titled Database Trends 2018. This session is for everybody as I run down the important trends that are impacting the jobs of modern data professionals. I’ll touch on Big Data, analytics, NoSQL, cloud, digital transformation, in-memory computing, DevOps, and more. And conclude with how it all impacts DBAs. Don’t miss this session!

Let's not forget the exhibit hall (aka Solutions Center) where vendors present and demo their products that can help you manage Db2 more effectively. It is a good place to learn about new technology solutions for Db2, but also to hang out and meet with IBMers, consultants, and your peers.

This year I'll be spending some time in the CorreLog booth (#209) in the Solutions Center. Be sure to stop by and say hello, take a look at CorreLog's great solutions for SIEM and auditing Db2 for z/OS, and register to win one of 4 of my DBA books that will be raffled off.

That is a lot for one week, but there is more. You can go to full-day education sessions on Sunday April 29^th (at an additional cost), plan for Db2 12 for z/OS and continuous delivery (half day session on May 3^rd), attend Special Interest Groups (where you can discuss the latest industry trends and topics with other interested technicians), attend Hands-On Labs (with hands-on training led by IBM), and even take complementary IBM certification exams. 

And be sure to download the mobile app for the conference to help you navigate all the opportunities available to you!

The IDUG Db2 TechConference is the place to be to learn all about Db2 from IBMers, gold consultants, IBM champions, end users, ISVs, and more. With all of this great stuff going on this year in Philadelphia, why wouldn't you want to be there!?!?

The Db2 12 for z/OS Blog Series - Part 21: New Global Variables for Continuous Delivery

One of the most important new "features" of Db2 12 for z/OS is continuous delivery. With continuous delivery more functionality will be made available more quickly than ever before. Instead of waiting for big version migrations new function levels can be applied rapidly, thereby delivering desired functionality more quickly and agilely.

Of course, this impacts DBAs and systems programmers who manage the  version of Db2 more than it impacts developers. That said, developers always need to be aware of which version and now, level, of Db2 that they are using. This is important because it dictates the features that are available to use.

As part of the continuous delivery of Db2 functionality, Db2 12 adds several built-in global variables to help. In actuality, these new variables can be read by any application in Db2 11 NFM and Db2 12 (as long as the Db2 11 subsystem has applied the Db2 12 migration SPE and executed CATMAINT).

The first global variable we will discuss is PRODUCTID_EXT, which stores the extended product identifier of the database manager that was used to invoke the function. The value is VARCHAR(30) and it is maintained by the system. The schema is SYSIBM. 

The format of the extended product identifier values is pppvvrrmmm, defined as follows: 

• ppp is a three-letter product code (such as, DSN for Db2)
• vv is the version
• rr is the release
• mmm is the modification level (such as, 100, 500, 501)

For example, DSN1201501 identifies Db2 12 after the activation of Db2 12 new function level 501. Function level 500 is the first Db2 12 function level so any level 500 or greater indicates Db2 12 new functionality is availabile. 

An application accessing PRODUCTID_EXT from a coexistent Db2 11 member of a data sharing group would see a value of DSN1101500. 

The second new global variable for continuous delivery is the CATALOG_LEVEL. Appropriately enough, this global variable contains the current catalog level. Again, the data type is VARCHAR(30) and it is maintained by the system with a schema of SYSIBM. 

The format of the catalog level values is VvvRrMmmm, defined as follows:

• vv is the version
• r is the release
• mmm is the modification level (such as 100, 500, 501)

For example, V12R1M500 identifies Db2 12 after the activation of Db2 12 and the initial CATMAINT run for Db2 12 runs. An application accessing CATALOG_LEVEL from a coexistent Db2 11 member of a data sharing group would see a value of V12R1M500 after the initial CATMAINT run for Db2 12 runs on a Db2 12 member.

The third and final new global session variable for continuous delivery is the DEFAULT_SQLLEVEL, which stores the default value of the SQLLEVEL SQL processing option (DECPSQLL). As with the others, the data type is VARCHAR(30) and it is maintained by the system with a schema of SYSIBM. 

The format of the catalog level values is V10R1, V11R1, or VvvRrMmmm, , defined as follows:

• vv is the version
• r is the release
• mmm is the modification level (such as 100, 500, 501)

For example, V12R1M501 identifies Db2 Version 12 Release 1 Function Level 501.

Keep these global variables in mind and use them as appropriate in your programs to ensure that the functionality you need is actually available to your program when it runs.

On Leaky Pipes and DBAs

How is a plumber like a DBA? 

I was working in my backyard a couple of weekends ago, trying to find the cause of a leak. You see, there was a big puddle of water in the backyard, it hadn’t rained lately, and it was larger than the sprinklers could’ve made. Down here in Texas almost everyone has a sprinkler system -- you kind of have to or your grass and plants will die and your house might come down with foundation problems. So everything has to stay somewhat "moist."

Anyway, it was obvious I had a problem. So I was turning water on and off and running the sprinkler and the hose and then I stopped. And started to think like a DBA. 

What does that mean? 

Well, one of my cardinal rules whenever confronted with a database problem is to do one thing at a time -- otherwise, if you’ve made multiple changes how do you know which helped; and which might’ve hurt? You don’t. 

I had violated this rule in my backyard. So I went back to doing one thing at a time. I figured that would give me the best chance to actually find the problem. This approach might take longer but at least it has a chance of success.

Slowly I stepped through turning on and off valves. And then I realized unless there was a huge flow of water leaking somewhere that I might not even notice the leak. It made more sense to turn everything off and let the standing water evaporate. And that takes even more time.

And then I decided it was time to call in an expert. Let them worry about my pipes and leaks -- and I’ll worry about the database!

Note: The leak was caused by an underground pipe that the experts found quickly using equipment I didn't have (and didn't even know existed). Another good reason to leave things to the experts!

Db2 Application Development for Performance: Be Early and Be Informed

The title of this blog post is also the title of one of my two IDUG Db2 Tech Conference presentations in Philadelphia this year. I chose this topic because I am in the process of writing a book on Db2 performance from a developer perspective.

There are a lot of performance-focused presentations but most of them are from the perspective of monitoring and tuning. In other words, they are about looking for and fixing problems in code that already exists. Now there is nothing wrong with this. In fact, it is necessary. Even if everything is written correctly from the get-go (right, sure it is) over time things will change and performance will decline. So we need tools to ferret out what is going wrong and help us to fix it. This is mostly the domain of the DBA to do.

From the programmer's point-of-view, though, it seems that things are lacking. I frequently see SQL and program code that seems to have been written by someone with no understanding of relational basics. This has to change.

That is the reason for my IDUG presentation... and also for the book. The idea is to give advice and direction to Db2 application developers and programmers on writing efficient, well-performing programs. If you write code and access data in a Db2 database, then this book and presentation should be helpful to you.

The material is written for all Db2 professionals. It will be useful for users of Db2 for Linux, Unix and Windows as well as for users of Db2 for z/OS. When there are pertinent differences between the two I point it out. Also, much of the material will apply to any relational database system, not just Db2; nevertheless, the book is written and intended for Db2 users.

I don't try to teach basic programming skills, other than, of course, some guidance on SQL. And even that is not basic. I assume you can figure out a basic Select, Insert, Update or Delete... and even some more complex stuff like joins. The focus of the book is on programming, coding and developing applications.

If you are a DBA, most of the material will still be of interest to you because DBAs are responsible for overall Db2 performance. Therefore, it makes sense to understand the programming aspect of performance. That said, I will not be covering DBA and system administration level performance. But if you think the material is worthy, knowing it is there to recommend to your developers (new and old) can be worthwhile!

The book and presentation focus on guidance regarding application development procedures, techniques, and philosophies. The goal is to educate developers on how to write good application code that lends itself to optimal performance. Of course, this does not mean that every program you write will perform at top speed. But it should enable you to write code that does not require remedial after-the-fact modifications by performance analysts. If you follow the guidelines I outline in this book and presentation, I can say with confidence that your DBAs and performance analysts will love you!

So if you are going to IDUG in Philadelphia this year, be sure to attend my presentation. Be sure to say "Howdy!" and that this blog post guided you to the presentation... and also, keep an eye on my web site, Mullins Consulting, Inc., for information about the book when it gets published, hopefully later in 2018.

Seasons Greetings 2017

It is that time of year again... A time to reflect on the year gone by and to enjoy time with our friends, family and loved ones.  A time to put down the work that consumes us most of the year and to celebrate and enjoy... to remember past years and to consider the upcoming year.  

No matter what holiday tradition you celebrate, I wish you an enjoyable holiday season. Seasons greeting to one and all... and I'll see you next year here on the Db2 Portal blog!

The Db2 12 for z/OS Blog Series - Part 20: Fast Insert: An Alternative INSERT Algorithm

Db2 12 offers many performance improvements that can help you to speed up your applications. The Fast Insert algorithm, also called Insert Algorithm 2, is one such improvement, but you have to understand what it is and when it can be helpful.

The general idea behind this alternative Insert algorithm is to improve performance of Insert-intensive workloads for unclustered data. This is a common requirement for applications that require an audit table or a journal of activities. New data that is inserted is appended to the end of the table with no concern for clustering. A frequent issue with such applications is when the workload is so high that rows cannot be inserted rapidly enough thereby increasing the elapsed time.

Most of these types of applications design the journal/audit table using partitioned table spaces with the MEMBER CLUSTER and APPEND attributes. This design will direct Db2 to insert all new rows at the end of the partition. Insert performance should improve because the space search algorithm can be bypassed for the most part. Nevertheless, some Db2 applications still experienced performance issues even when using this approach.

This bring us to the Db2 12 Fast Insert algorithm. This new algorithm uses an in-memory structure called an insert pipe to speed things up. The insert pipe maintains a list of pages that are available for this member to use for Insert processing for the page set partition. Each member that opens a page set gets an Insert pipe for that member to use. A system agent fills up the pipe asynchronously, making sure that pages are always available for the threads to use for inserting rows.

There is more to the process, but that is the high-level intro to how it work. Now the question is: when will the new algorithm be used?

The Fast Insert algorithm only works with Universal table spaces with the MEMBER CLUSTER option; APPEND is not required. The new algorithm is the default algorithm for this type of table space.

Settings and options are available to control use of the new algorithm. To set usage of the algorithm at a system-wide level, use the DEFAULT_INSERT_ALGORITHM subsystem parameter (DSNZPARM). There are three options:

• 0 indicates that the basic Insert algorithm is to be used no matter what (Insert algorithm 2 is disabled for the subsystem); 
• 1 indicates that the basic insert algorithm is used unless insert algorithm 2 was specified at the table space level; 
• 2 indicates that Insert algorithm 2 is used unless insert algorithm 1 was specified at the table space level.

Which brings us to the DDL options for controlling the insert algorithm at the individual table space level. To do so, use the INSERT ALGORITHM option on the CREATE TABLESPACE or ALTER TABLESPACE statement. Again, there are three options: 

• 0 indicates that the Insert algorithm to be used is as specified by the DEFAULT_INSERT_ALGORITHM subsystem parameter at the time a row is inserted; 
• 1 indicates that the basic Insert algorithm is to be used; and 
• 2 indicates that the Fast Insert algorithm is to be used.

Summing Up

The impact of using the new Fast Insert algorithm will depend on various factors, including whether the table has indexes or not and the specific makeup of your workload. If the workload has lock/latch contentions (on the space map pages and data pages) then the new Insert algorithm will probably be beneficial.

Happy Thanksgiving 2017

Today, November 23rd, in the United States of America, we celebrate Thanksgiving by gathering together with our loved ones and giving thanks for what we have. 

Typically, this involves celebrations with food, traditionally a big turkey dinner with stuffing, mashed potatoes and gravy, as we watch a parade and football games. I plan to follow this tradition to the letter this year and I wish you the same!

And with the general notion of giving thanks on this fine day, I want to also pause and thank each and every one of you for reading my blog, whether just today, or all year long. Hopefully you'll keep coming back and we can keep discussing all things Db2 here.

But for today, wherever you may be, I am thankful for you and I wish you a very happy day!

Oh... and try not to get trampled on Friday if you're going out shopping for bargains!

The Db2 12 for z/OS Blog Series - Part 19: Profile Monitoring Improvements

The ability to monitor Db2 using profile tables is a newer, though by no means brand new capability for Db2 DBAs. You can use profile tables to monitor and control various aspects of Db2 performance such as remote connections and certain DSNZPARMs.

But this blog post is not intended to describe what profile monitoring is, but to discuss the new capabilities added in Db2 12 to enhance profile monitoring.

There are four new enhancements offered by Db2 12 for the use of system profiles.

The first enhancement is the ability to automatically start profiles when you start up a Db2 subsystem. This can be accomplished using a new subsystem parameter called PROFILE_AUTOSTART. Setting the parameter to YES causes Db2 to automatically execute START PROFILE command processing. The default is NO, which means that Db2 will not initiate START PROFILE when the subsystem starts up.

The second improvement is the addition of support for global variables. As of Db2 12 you can specify the following global variables as a KEYWORDS column value in the SYSIBM.DSN_PROFILE_ATTRIBUTES table:

• GET_ARCHIVE
• MOVE_TO_ARCHIVE
• TEMPORAL_LOGICAL_TRANSACTION_TIME
• TEMPORAL_LOGICAL_TRANSACTIONS

If a profile filter matches a connection, Db2 will automatically apply the built-in global variable value to the Db2 process of that connection when the connection is initially established, and when a connection is reused.

Wildcarding support is the third enhancement for profiles in Db2 12. One row for each profile is contained in the SYSIBM.DSN_PROFILE_TABLE. Each column in the table informs Db2 which connection to monitor. Without wildcarding, handling various connections required multiple rows to be defined in the table. But with Db2 12, you can have one row representing more than one connection. Wildcarding is available for AUTHID (authorization IDs), LOCATION (IP addresses of monitored connections), and PRDID (product specific identifier, for example DSN for Db2).

The fourth and final enhancement is for managing idle threads. The MONITOR IDLE THREADS column in the SYSIBM.DSN_PROFILE_ATTRIBUTES table directs DB2 to monitor (for an approximate amount of time) an active server thread’s idle time. The ATTRIBUTE1 column, which is used to specify the type of messages and level of detail of messages issued for monitored threads, has been enhanced to allow the following values: 

• EXCEPTION_ROLLBACK
• EXCEPTION_ROLLBACK_DIAGLEVEL1
• EXCEPTION_ROLLBACK_DIAGLEVEL2 

Note: This particular change to idle threads
for EXCEPTION_ROLLBACK was made available
in Db2 11 after general availability, and will be
available on a Db2 12 system after new function
is activated.

For more details on any of these capabilities, or indeed, on profile monitoring in general, refer to the IBM Db2 12 for z/OS Managing Performance manual, SC27-8857.

Db2 Social Advocate of the Year

A great big thank you to IBM and IDUG for naming me Db2 Social Advocate of the Year for 2017 at the Db2 Awards ceremony at IDUG in Lisbon this year.

I received a nice crystal award and recognition on stage at the event with Surekha, Daniel and Namik.

It is always great to be recognized for my contributions to the Db2 community, but it is something that I do because I love Db2... and have ever since I first used Version 1 back in the 1980's! 

So a big thank you to everyone involved and hopefully I can continue to warrant such accolades throughout the remainder of this year and for many years to come.

Long live Db2!

The Db2 12 for z/OS Blog Series - Part 18: Adaptive Indexes

Have you ever had one of those tough queries that was always a challenge to keep performing well? This type of query usually experiences fluctuating filtering. By that I mean that the filtering can change, sometimes dramatically, between executions of the query.

Some of the things that can cause fluctuating filtering are predicates with ranges that vary, sometimes returning a small subset of rows and sometimes returning everything. You know the type, perhaps there is a BETWEEN clause that can be set and sometimes it is set as BETWEEN 3 AND 5, whereas other times it is set as BETWEEN 0 and 999999. And maybe even sometimes it is set to BETWEEN 3 AND 3 to just search for equality... Or perhaps it is a LIKE clause that sometimes starts with a wildcard ('%').

Well, Db2 12 offers execution time adaptive indexes that allows list-prefetch plans to quickly determine filtering and adjust at execution time as needed. Db2 can do this for static SQL queries even if REOPT(ALWAYS) is not specified. 

Execution time adaptive indexes are not limited to search screening, as described in the previous paragraph. Indeed, any query with a high uncertainty in the optimizer’s estimate can benefit. This includes range predicates, JSON, Spatial, and index on expression queries.

A quick evaluation is performed by looking done at the literals used in the query. Further costlier evaluation of filtering is deferred until after one RID block is retrieved from all participating indexes. This offers a better optimization opportunity while at the same time minimizing overhead for short running queries.

How about some examples of how execution time adaptive indexes work? For an access path that uses list prefetching or a multi-index OR the query can fall back to a table space scan if a large percentage of the data is going to be read. For an access path that uses multi-index AND Db2 can reorder index legs from most to least filtering, as well as providing an early-out for non-filtering legs and fallback to a table space scan if there is no filtering.

If you are interested in tracking when adaptive index processing is utilized, IFCID 125 has been enhanced to track this feature.

The Db2 12 for z/OS Blog Series - Part 17: A New Privilege for UNLOAD

Db2 12 for z/OS introduces a new privilege that, when granted, enables a user to be able to unload data using the DB2 IBM UNLOAD utility. In past releases, the SELECT privilege (or other higher level admin privileges) was required to unload data using the UNLOAD utility. But this was less than desirable.

Why? Well, one reason is that it created a potential security gap. Consider the situation where a table has column masks or row permissions. In such as case, a user with SELECT privilege against the table still might not be able to access all of the rows and columns because of the masks/permissions that are defined. However, the same user with the same privilege set could execute the UNLOAD utility and be able to read all of the data in the table. Such as situation is not ideal and would not pass an audit.

To remove this gap IBM has introduced a new privilege, the UNLOAD privilege. After you move to Db2 12 for z/OS, SELECT authority is no longer enough to be able to unload data. In order to unload data the user must be granted the UNLOAD privilege on that table. The UNLOAD privilege can only be granted on a table; it cannot be granted on an auxiliary table or a view. The UNLOAD privilege is required after you have moved to function level V12R1M500 or higher.

Of course, there is a workaround if you still want to allow users with the SELECT privilege to be able to unload using the UNLOAD utility. This requires setting a DSNZPARM named AUTH_COMPATIBILITY to "SELECT_FOR_UNLOAD". The default for this DSNZPARM is NULL, which means that the UNLOAD privilege is required. 

Regardless of the privilege, keep in mind that tables with multilevel security impose restrictions on the output of your UNLOAD jobs. A row will be unloaded only if the security label of the user dominates the security label of the row. So it is possible that an unload may not actually unload every row in the table. If security label of the user does not dominate the security label of the row, the row is not unloaded and DB2 does not issue an error message.

See You in Lisbon for the EMEA IDUG DB2 Tech Conference!

This year the IDUG EMEA DB2 Tech Conference is being held in Lisbon, Portugal from October 1st through October 5th. Lisbon is a beautiful city with a lot to see and do, so you can plan for a great week of DB2 and analytics education... and then follow it up with a visit to truly remarkable city.

Whether or not you care about vacationing is irrelevant, though, because a week at an IDUG event is always an educational and enjoyable time. If you’ve ever attended an IDUG conference before then you know all about the fantastic educational and entertainment opportunities that IDUG offers. Of course there will be a ton of informative technical sessions on all of the latest and greatest DB2 technologies and features. 

There are a couple of great keynote sessions scheduled this year. Daniel Hernandez, offering leader with IBM Analytics, will discuss Data Without Limits. He’ll talk about how IBM is changing Db2 like never before including the latest news on Db2, Db2 for z/OS and the Db2 Analytics Accelerator, as well as providing demonstrations of new solutions. And the closing keynote is from Jonathan Adam, vice president and general manager of ZSolutions at BMC Software. Jonathan will present Continuing Digital Business Challenges — Finding Value in your data..., an overview of how to achieve business advantage in the digital age.

And then there are those technical presentations delivered by IBMers, consultants, and your colleagues. These are the heart and soul of an IDUG conference. Where else can you find four days and six tracks of data and DB2-related education? Check out the agenda to see what is on tap!

I will be busy this year at IDUG spending time with the DB2 gold consultants, many of whom will be at the conference to present, mingle, and learn.  I will also be delivering a vendor-sponsored presentation for LogOn Software with intriguing title of How to Accelerate DB2 SQL Workloads... Without DB2. Whatever could that mean? You'll have to join me on Monday at 2:00 PM in Room VITIS!

And don't forget to download the mobile app for the conference to help you navigate all the opportunities available to you!

The IDUG DB2 Tech Conference is the place to be to learn all about DB2 from IBMers, gold consultants, IBM champions, end users, and more. With all of this great stuff going on why wouldn't you want to be there!?!?

DB2 for z/OS Coupling Facility Sizing

Just a very brief blog post today to inform you about something that I ran across doing some web research today... and that is a free, web-based Coupling Facility sizing tool from IBM named CFSIZER. 

The CFSIZER tool will connect to your live server to get information. You will need to input some values and some estimation of the type of workload for your system and CFSIZER will output its recommendations.

If you are struggling with your Data Sharing environment this might be a good place to start if you don't have any tools to help you. Here is the link to CFSIZER.

Thanks IBM!

The Db2 12 for z/OS Blog Series - Part 16: Db2 Catalog Availability Improvements

IBM has improved the availability of accessing Db2 Catalog objects when maintenance is being run in Db2 12 for z/OS. This impacts access during CATMAINT and online REORG.

This change is largely being driven by dynamic SQL, which is more prevalent but can cause problems. When dynamic SQL statement is executed, Db2 must dynamically prepares the SQL to determine access paths in order to run it. During this dynamic SQL preparation process, Db2 acquires read claims on a handful of Db2 Catalog table spaces and their related indexes. Additionally, a DBD lock is acquired on the Db2 Catalog database. The DBD lock is needed to serialize catalog operations with CATMAINT and other DDL that may execute against the catalog, because CATMAINT might be making structural changes to the catalog.

Prior to Version 12, the DBD lock and the read claims were released at COMMIT points. All well and good, but for transactions issuing dynamic SQL but not committing frequently, CATMAINT and online REORG on the Db2 Catalog were blocked during that period period of time.

As of Db2 12, DBD locks on the Db2 Catalog and read claims against catalog objects are released as soon as PREPARE statement execution is complete. This will improve availability for CATMAINT and online REORG of Db2 Catalog objects.