The overall performance of Db2 z/OS depends highly on its I/O performance, which is in turn mainly influenced by the behavior of the buffer pools. Whereas SQL tuning efforts might be labor-intensive and costly, the Buffer Pool tuning with the aide of tuning tools is something that can easily be done by system DBA’s. Interaction with application development is rarely necessary and tuning success is achieved really fast. Very often only small changes lead to significant improvements.

The main purpose of the bufferpools is to save I/O, hence to save CPU work. Performance degrades when we fail to keep the right pages as long as possible in the pools. The right pages are those that are accessed again and again. What can be a relatively simple task can turn out to be a huge non-ending task when you have to work with inappropriate tools. Don’t be left to the fate of Greek mythological King Sisyphus, buffer pool tools are a requirement today.

What is to be gained from Bufferpool Tuning?
Inappropriate bufferpool settings can cause ugly problems from temporary system hold to loss of an entire Db2 data sharing group. Does it really have to come to that? Ample evidence of problems appears way before, a lot of CPU was already burned before things crash. Reckless CPU burning involves a great deal of expense. The primary issue with buffer pool tuning is to avoid unnecessary I/O and the cost involved. The best thing we can do against bottlenecks at peak times is to avoid impediments. An ongoing search method for avoidable I/O and their prevention keeps systems healthy, saves on machine resources, and prevents unpleasant surprises.

What can be expected from Bufferpool Tuning?
A precise answer to a simple question: Do we have tuning potential? In other words: Do the systems already work efficiently or can we achieve measurable improvements? Don’t use a tool that attempts to improve things that are OK or already optimal, what a complete waste of your valuable time. A helpful bufferpool tool is pro-active and warns when things are about to turn bad and leaves me alone as long as everything is in good shape.

The key feature is “ongoing surveillance” according to quality criteria that I can set. For example, send me a warning if more than 10% avoidable IO appears with these bufferpools.

Clear Briefing – Recommendation of Tuning Measures
Let’s assume tuning potential is detected, performance is suboptimal, what next? In order to save me the pain of lengthy analysis, the best buffer pool tool should provide recommendations and predict their outcome. Preferably, it will already deliver the required ALTER statements, especially when many objects are involved. It is uncommon that only the size or the thresholds of a single pool has to be changed, more often the assignment of objects to pools must be reworked. A tool that helps me do this, that helps me to deliver system performance, is what I need.

You have more and more growing databases and Db2 systems to administer, but no additional help to get your work done? This is the normal case these days, but the good news is that there are ways to automate many of the necessary tasks to monitor and to control your Db2 systems and databases.

Enterprises want their application systems to be highly available and to perform well on an ongoing basis, but verifying the health of their systems seems a daunting task. On occasion, certain non-availability of applications can remain undetected. Sometimes problems develop over long periods of time, noticing trends and their timely detection would simplify resolution and avoid downtime. Performance degradation normally evolves slowly, adverse effects on users can be easily avoided, provided the first signs are recognized and countermeasures are taken early enough. New challenges continue, for example the growing flood of dynamic SQL. Automatic surveillance and assessment of the Dynamic Statement Cache can produce relief.

Normally your clients do not ask why the application failed, it just failed and it should not have. It doesn’t matter whether a tablespace became full or entered a restricted state, or whether a thread or procedure failed, or the system came to hold due to log offload difficulties, or a sharing group went down because the SCA was full. There are many error sources and as many suggested workarounds. Much better than a conglomeration of various scripts and procedures is a new unified and integrated approach. It is not enough to receive a list of the current restricted states, truly valuable information is provided only when the restricted states are immediately related with current utility executions or other system wide events.

A tool based implementation of continuous measurement and analysis helps to avoid the most frequently occurring problems in daily operations. Archived problem data also supports problem analysis, “Did this problem occur ever before and how was it solved?” A data warehouse of problem descriptions of all the problems that occurred in the past simply shows the common, most frequent issues for which general provisions can be made and automatically applied. An elegant solution offers its findings on what happened as a set of parameters for immediate deployment of counter measures: Copy Pending – copy it, problem with a thread, restart it, etc.

A big advantage of a unified solution is in the field of messages and warnings. Too many messages and warnings are annoying, elusive, or ignored. Only serious issues should be reported, and they should only be reported once. A solved problem should automatically vanish from the screen and enter the data warehouse of past problems.

What can be automatically monitored and analyzed by an appropriate tool? In principle everything, a tool I have in mind should detect conceivable lack of space, ensure timely recoverability according SLAs, monitor BSDS/log activities, check the quality of dynamic SQL statements, monitor and assess I/O performance and buffer pools, monitor threads, DDF, plans, packages, objects in restricted state and utility executions, SCA structures, group buffer pools, just to mention a few. Do you want to know what tool I am thinking of…. See XM4Db2™.

Errors, problems, bottlenecks are inevitable in ever-changing, complex environments, budgets are always tight, data centers are short-staffed and service level agreements are ambitious. The only way out is automation. Good is good, but better carries the day.

Automatic problem detection and problem treatment is long overdue. Many if not most problems make themselves known early enough, it is all about recognizing the first indicators. A tool is able to do that, emotionless, tenacious, ongoing. Many problems occur repeatedly. What did we do with this last month? The database of an appropriate problem management tool provides information.

Automatic problem detection is one thing, automatic reaction is even better. The tool that detected and measured an unacceptable situation has all the necessary parameters available to generate a predefined job to remedy the situation.

A problem management tool should monitor each Db2 subsystem on every LPAR, but keep all the information gathered in one database. The problem management tool? XM4DB2™.

Performance with Dynamic SQL
People have long tended to avoid dynamic SQL as much as possible. The reasons given were usually: additional CPU overhead for the statements’ prepare, no or little capacity for quality assurance, and lack of transparency: “You don’t know what or when something will be executed, and what the consequences will be.”

As more and more licensed standard products make use of dynamic SQL, this attitude is becoming less and less viable. And another challenge presents itself. The z/OS platform has rivals, and its superiority in reliability and performance rests among other things on established quality assurance processes. The question therefore is not whether or not to use dynamic SQL, wisdom lies again in a bit of both. How can we approve dynamic SQL, profit from dynamic SQL, and assure quality – in other words, guarantee performance?

DSCA detects, corrects, and avoids the problems that go hand in hand with the execution of growing volumes of dynamic SQL statements. What does that mean in detail? Dynamic statements are usually generated during runtime. The result of this generation is often very uniform, which means it is highly likely that statements that have appeared once will reappear again in the same or similar form. This is where DSCA comes in. It monitors the DSC and collects and categorizes statements, and in doing so builds up a “data warehouse of SQL statements.” An image of the workload is created, highlighting weaknesses as well as pointing out the potential for improvement.

Quality assurance for dynamic SQL is therefore quite feasible, although no longer before execution (as is normally done with static SQL) but rather after the data warehouse of SQL statements has been compiled. This source is then analyzed in order that countermeasures can be taken. Typical problems are easy to spot with the help of this amassed data: access path problems, optimizer errors, DSC trashing (statements without parameter markers), CPU-burning SQL, statements with lock problems.
Since they usually have very limited control over the SQL in licensed products, people often say they can’t change anything anyway and end up neglecting the products they purchase. However, this is not necessarily correct. It is these products especially that demand a clear overview of problematic issues. As soon as you’re aware of them (usually only a few statements or statement groups are involved) you can move quickly to resolution:

• Optimize access paths using new or other indexes
• Adjust the buffer pool design in the case of IO problems
• Change table clustering
• Special runstats for complex queries

Static SQL QS is now standard in most settings, problems arise because dynamic SQL is not as carefully monitored. Comprehensive quality assurance for the entire workload is, however, the order of the day. Standard monitors hardly support this process, if at all. The DSCA component in Exception Master for Db2 (XM4DB2) is specially designed for this task. It compiles the SQL data warehouse, evaluates the statement groups according to your quality criteria, and suggests improvements.

Businesses that are not in a position to acquire a dedicated tool can order the Service Pack DSCA. UBS analyzes the DSC and recommends concrete tuning measures for a fixed price. We tell you the causes of any problems, detect wastage, highlight negative developments, and recommend countermeasures – such as those involving indexes, runstats, and parameter markers. Targeted QS brings together what belongs together: dynamic SQL with performance, transparency, resource conservation, uninterrupted operation, and high availability levels.