Performance Tools That Operate on Databases and Database Objects

In our last blog post here, we covered DB2 system performance management tools - that is, tools that look at the performance at a  system or subsystem level. Today, we turn our attention to the database objects...

Most DBMSs do not provide an intelligent database analysis capability. Instead, the DBA or performance analyst must use system catalog views and queries, or a system catalog tool, to keep watch over each database and its objects. This is not an optimal solution because it relies on human intervention for efficient database organization, opening up the possibility for human error.

DB2 for z/OS, however, does provide Real Time Statistics that can be used to drive database optimization and maintenance. What are Real Time Statistics (or RTS)?

Well, RTS are similar to traditional database statistics that are accumulated using a utility programs (RUNSTATS), but the RTS are accumulated by DB2 “on the fly” as the database management system and its applications are running. That is to say, without having to run a utility program.

RTS are stored in two tables in the DB2 Catalog:

• SYSIBM.SYSTABLESPACESTATS: Contains statistics on table spaces and table space partitions
• SYSIBM.SYSINDEXSPACESTATS: Contains statistics on index spaces and index space partitions

But since this post is supposed to be talking about database-performance tools, I don’t want to get into a full blown discussion of RTS… after all, RTS are a built-in component of DB2. That said, the ability of DB2 to generate and store RTS enables database performance tools to make decisions based on actual, up-to-date performance metrics. Of course, DB2 is not the only DBMS with such metrics, but since this is a blog about DB2, I won’t get into any details of the other database systems.

Database Analysis Tools

At any rate, database analysis tools are available that can proactively and automatically monitor your database environment. These database analysis tools typically can: 

• Collect statistics for tables and indexes: standard statistical information from the DBMS, extended statistics capturing more information (for example, data set extents), or a combination of both.
• Read the underlying data sets for the database objects to capture current statistics, read the database statistics from the system catalog, read tables unique to the tool that captured the enhanced statistics, or any combination thereof.
• Set thresholds based on database statistics whereby the automatic scheduling of database reorganization and other maintenance tasks can be invoked.
• Provide a series of canned reports detailing the potential problems for specific database objects.

Database Utilities

Another category of performance tool that operates at the database (or database object) level are database utilities. Usually there are some number of rudimentary utilities that ship for free with the DBMS. These are usually simple, no-frills programs that are notorious for poor performance, especially on very large tables. However, these utilities are required to populate, administer, and organize your databases. The typical utilities that are provided are LOAD, UNLOAD, REORG, RUNSTATS, BACKUP, and RECOVER, as well as utilities for integrity checking.

Although I suppose it is possible to make an argument, at some level, for any and all of these utilities to have a performance aspect to them, REORG and RUNSTATS are the ones that definitely impact database performance.

RUNSTATS is used to gather statistics on the composition of the database and REORG is used to organize table space data optimally.

There are third-party vendors that provide support tools that replace the database utilities and provide the same or more functionality in a more efficient manner. For example, it is not unheard of for third-party vendors to claim that its utilities execute anywhere from four to ten times faster than the native DBMS utilities. These claims must be substantiated for the data and applications at your organization (but such claims are believable). Before committing to any third-party utility, the DBA should be sure that the product provides all of the basic functionality required.

When testing utility tools from different vendors, be sure to conduct fair tests. For example, always reload or recover prior to testing REORG utilities, or you may skew your results due to different levels of table organization. Additionally, always run the tests for each tool on the same object with the same amount of data, and make sure that the data cache is flushed between each test run. Finally, make sure that the workload on the system is the same (or as close as possible) when testing each product because concurrent workload can skew benchmark test results.

Yet another category of database-focused tool is the Utility management tool. This type of tool provides administrative support for the creation and execution of database utility jobstreams. These utility generation and management tools:

• Automatically generate utility parameters, JCL, or command scripts.
• Monitor the database utilities as they execute.
• Automatically schedule utilities when exceptions are triggered.
• Restart utilities with a minimum of intervention. For example, if a utility cannot be restarted, the utility manager should automatically terminate the utility before resubmitting it.

Space Management Tools

Most DBMSs provide basic statistics for space utilization, but the in-depth statistics required for both space management and performance tuning are usually inadequate for heavy duty administration. For example, most DBMSs lack the ability to monitor the requirements of the underlying files used by the DBMS. When these files go into extents or become defragmented, performance can suffer. Without a space management tool, the only way to monitor this information is with arcane and difficult-to-use operating system commands. This can be a tedious exercise.

Additionally, each DBMS allocates space differently. The manner in which the DBMS allocates this space can result in inefficient disk usage. Sometimes space is allocated, but the database will not use it. A space management tool is the only answer for ferreting out the amount of used space versus the amount of allocated space.

Space management tools often interface with other database and systems management tools such as operating system space management tools, database analysis tools, system catalog query and management tools, and database utility generators.

Compression Tools

A standard tool for reducing storage costs is the compression utility. This type of tool operates by applying an algorithm to the data in a table such that the data is encoded in a more compact area. By reducing the amount of area needed to store data, overall storage costs are decreased. Compression tools must compress the data when it is added to the table and subsequently modified, then expand the data when it is later retrieved.

In the earlier days of DB2, compression tools that used an exit routine were common. But ever since DB2 Version 3, which introduced the built-in, hardware-assisted compression capability of DB2, compression duties are handled quite efficiently with out-of-the-box DB2 functionality.

Additionally, some tools are available that compress database logs, enabling more log information to be retained on disk before it is offloaded to another medium.

Synopsis

So, there are a number of different categories of performance tools that function at the database or database object level that are worth considering. These differ from system performance tools (covered in the last blog post) and application performance tools (which will be covered in the next blog post).

Database System Performance Tools

System performance tools examine the database server, its configuration, and usage. The most commonly used system performance tool is the performance monitor. Database performance monitoring and analysis tools support many types of performance-oriented requests in many ways. For example, system performance tools can operate:

• In the background mode as a batch job that reports on performance statistics written by the DBMS trace facility
• In the foreground mode as an online monitor that either traps trace information or captures information from the DBMS control blocks as applications execute
• By sampling the database kernel and user address spaces as the program runs and by capturing information about the performance of the job, independent of database traces
• By capturing database trace information and maintaining it in a history file (or table) for producing historical performance reports and for predicting performance trends
• As a capacity planning device that gives statistical information about an application and the environment in which it will operate
• As an after-the-fact analysis tool on a workstation, that analyzes and graphs all aspects of application performance and system-wide performance

Each database performance monitor supports one or more of these features. The evaluation of database performance monitors is a complex task. Sometimes more than one performance monitor is used at a single site—perhaps one for batch reporting and another for online event monitoring. Maybe an enterprise-wide monitoring solution has been implemented and one component of that solution is a database module that monitors your DBMS, but it lacks the details of a more sophisticated DBMS monitor. So, another performance monitor is purchased for daily DBA usage, while the module of the enterprise-wide monitoring solution is used for integrated monitoring by system administrators.

Modern database performance tools can set performance thresholds that, once reached, will alert the DBA, perform another task to report on, or actually fix the problem. These tools are typically agent-based. An agent is a piece of independent code that runs on the database server looking for problems. It interacts with, but does not rely on, a console running on another machine that is viewed by the DBA. This agent architecture enables efficient database monitoring because the agent is not tied to a workstation and can act independently. The agent sends information to the DBA only when required.

Additionally, some system performance tools are available that focus on a specific component of the DBMS such as the buffer pools (data cache). Such a tool can be used to model the memory requirements for database caching, to capture data cache utilization statistics, and perhaps even to make recommendations for improving the performance of the buffers.

Another type of performance optimization tool enables database configuration parameters to be changed without recycling the DBMS instance, subsystem, or server. These tools are useful when the changes require the DBMS to be stopped and restarted. Such tools can dramatically improve availability, especially if configuration parameters need to be changed frequently and the DBMS does not support dynamic parameter modification.

A few ISVs provide invasive system performance tools that enhance the performance of databases by adding functionality directly to the DBMS and interacting with the database kernel. Typically, these products take advantage of known DBMS shortcomings.

For example, products are available that enhance the performance of reading a database page or block or that optimize data caching by providing additional storage and control over buffers and their processing. Care must be taken when evaluating invasive performance tools. New releases of the DBMS may negate the need for these tools because functionality has been added or known shortcomings have been corrected. However, this does not mean that you should not consider invasive database performance tools. They can pay for themselves after only a short period of time. Discarding the tool when the DBMS supports its functionality is not a problem if the tool has already paid for itself in terms of better performance.

One final caution: Because invasive performance tools can interact very closely with the database kernel, be careful when migrating to a new DBMS release or a new release of the tool. Extra testing should be performed with these tools because of their intrusive nature.

DB2 Performance Tuning Tools

Well, as I promised a post or two ago, in this and the next couple of posts we will take a look at database performance tools...

Database tools are helpful to enable organizations to effectively manage the performance of applications that access database data... and to help manage the DBMS itself. Some DBMS vendors provide embedded options and bundled tools to address database performance management. However, these tools are frequently insufficient for large-scale or heavily used database applications. Fortunately, many third-party tools will effectively manage the performance of mission-critical database applications. Tools that enable DBAs to tune databases fall into two major categories: performance management and performance optimization.

Many different types of performance management tools are available.

• Performance monitors enable DBAs and performance analysts to gauge the performance of applications accessing databases in one (or more) of three ways: real time, near real time (intervals), or based on historical trends. The more advanced performance monitors are agent-based.
• Performance estimation tools provide predictive performance estimation for entire programs and SQL statements based on access paths, operating environment, and a rules or inference engine.
• Capacity planning tools enable DBAs to analyze the current environment and database design and perform “what-if” scenarios on both.
• SQL analysis and tuning tools provide graphical and/or textual descriptions of query access paths as determined by the relational optimizer. These tools can execute against single SQL statements or entire programs.
• Advisory tools augment SQL analysis and tuning tools by providing a knowledge base that provides tips on how to reformulate SQL for optimal performance. Advanced tools may automatically change the SQL (on request) based on the coding tips in the knowledge base.
• System analysis and tuning tools enable the DBA to view and change database and system parameters using a graphical interface (e.g., cache and/or bufferpool tuning, log sizing).

In the performance optimization category, several tools can be used to tune databases.

• Reorganization tools automate the process of rebuilding optimally organized databases. Databases can cause performance problems due to their internal organization (e.g., fragmentation, row ordering, storage allocation).
• Caching tools work to buffer frequently used data in memory which can be accessed faster than secondary disk storage. These tools can augment the performance of the DBMS cache or, more commonly, integrate with the disk storage subsystem.
• Compression tools enable DBAs to minimize the amount of disk storage used by databases, thereby reducing overall disk utilization and, possibly, elapsed query/program execution time, because fewer I/Os may be required. (Caution: Compression tools can also increase CPU consumption due to the overhead of their compress/decompress algorithms.)
• Sorting tools can be used to sort data prior to loading databases to ensure that rows will be in a predetermined sequence. Additionally, sorting tools can be used in place of ORDER BY or GROUP BY SQL. Retrieving rows from a relational database is sometimes more efficient using SQL and ORDER BY rather than SQL alone followed by a standalone sort of the SQL results set.

The DBA will often need to use these tools in conjunction with one another—integrated and accessible from a central management console. This enables the DBA to perform core performance-oriented and database administration tasks from a single platform.

Many DBMS vendors provide solutions to manage their databases only; for example, Oracle provides Oracle Enterprise Manager, IBM offers Data Studio for DB2, and Microsoft provides SQL Server Management Studio for this purpose. Third-party vendors provide more robust options that act across heterogeneous environments such as multiple different database servers or operating systems. One example is Dell's Toad product family (there are others).

In general, it is only a good idea to use the DBMS vendor solution as your only management tool if your shop has just a single DBMS. Organizations with multiple DBMS engines running across multiple operating systems should investigate the third-party tool vendors with heterogeneous support (perhaps in addition to the single solution tools).

We will take a closer look at some of these types of tools, with a focus on DB2 for z/OS, in upcoming blog posts.

Rules for an Effective DB2 Monitoring Strategy

DB2, and relational databases in general, have a reputation of being (relatively) easy for users to understand; users specify what data to retrieve, not how to retrieve it. The layer of complexity removed for the users, however, had to be relegated elsewhere: to the code of DB2. And that means you sometimes have to dig into technical details of the DB2 optimizer or other arcane details to uncover performance issues.

DB2 also has a reputation as a large resource consumer. This reputation is largely because of DB2’s complexity. Because DB2 performance analysts must understand and monitor this complexity, they require an array of performance monitoring tools and techniques.

But I do not want to get into all of the potential tools and techniques in today’s short post. I plan to talk about the various types of DB2 performance and monitoring solutions that are available in upcoming posts.

Instead, today’s post just covers the high-level components of what is needed for an effective DB2 performance management strategy... An effective monitoring strategy includes the following:

• Scheduled batch performance reports on the recent performance of DB2 applications and the DB2 subsystem; a history of these reports would be useful, too.
• An online monitor that executes when DB2 executes to enable quick monitoring of performance problems as they occur.
• Online monitors for all teleprocessing environments in which DB2 transactions execute (for example, CICS, IMS/TM, or TSO).
• A monitoring solution that can track and report on dynamic distributed traffic.
• End-to-end transaction monitoring capability, sometimes called Application Performance Management.
• SQL query monitoring and explain analysis.
• Regular monitoring of z/OS for memory use and VTAM for network use.
• Scheduled reports from the DB2 Catalog and queries run against the RTS tables
• Access to the DB2 DSNMSTR address space to review console messages.
• Use of the DB2 -DISPLAY command to view databases, threads, and utility execution.

As I mentioned, I will cover the various types of performance tools and product offerings in upcoming posts. But for now, if you are interested in uncovering more information about third-party performance tools take a look at this link on my web site. 

Plan to Attend IBM Insight 2014

The IBM Insight conference is just around the corner and if you care about DB2, Big Data, Analytics, data warehousing, or really, anything at all about enterprise data, then Las Vegas is the place to be the week of October 26 thru 30, 2014.

But what is IBM Insight? Well, you may remember it as the IBM Information on Demand conference, or IOD for short, Yes, IBM has renamed the conference yet again. I'm sure a lot of you can remember when there were a bunch of different "Technical Conferences" like the IMS Tech Conference or the DB2 Tech Conference. Those all (and several others) got rolled up into IOD... which is now IBM Insight.

IBM claims to have changed the name because "It's no longer just about information; it's what you can do with the information."  And that kinda makes some sense... or at least it does to me!

With that bit of confusion out of the way, why should you attend the IBM Insight 2014 conference? Well, there is something for everybody in a data-related profession. Over 1500 presentations that run the gamut from DB2 to IMS to Cognos to BI to Big Data to analytics to... well, you get the idea. And there are five fast track groups that highlight several of IBM's important initiatives covering:

• Watson and Cognitive Computing
• Cloud
• Security
• Infrastructure
• Mobile and Social Engagement

And with over 13,000 attendees the opportunity to network with your peers is unmatched! Add to that the over 350 exhibitors at the Expo hall and you will be able to view, review, and examine all kinds of interesting software to help you manage your enterprise data.

Not to mention the fact that I'll be presenting there on the Top 10 DB2 Things You Need to Know (Wednesday, October 29, at 3:00 pm). Well, OK, I guess I mentioned it.

So what are you waiting for? Register today... and there is still time (expires September 19, 2014) to get the early bird discount of $300 off!

Submit an Abstract for IDUG NA 2015 in Philadelphia

Yes, it is time to start thinking about next year's IDUG DB2 Tech Conference already, especially if you are hoping to deliver a presentation there. The conference will be in the Philadelphia area in 2015, a first for IDUG... well, actually, the conference will be held at the Radisson Hotel Valley Forge in King of Prussia, PA - but that might as well be Philadelphia. I was born and raised in Pittsburgh, and we always thought that entire side of the state might as well be New Jersey, so it is all the same to me!

The conference information can be found at this link and you can either follow the Call for Presentations link at that page, or click here to submit your abstract.

Now why should you consider speaking at IDUG? If you have in the past, I'm sure you are wondering why somebody would even ask such a question. First of all, if you are accepted as a speaker, you get a free conference pass. And everybody can appreciate the benefit of some free education. But by putting together a presentation and preparing to speak in front of your peers you will learn more than you think! Sometimes the "teacher" learns more than the "students"... if you have never done it before, give it a try. Sure, it can be scary at first, but don't let that stop you. Learning how to present and speak in public can, and will, further your career!

Think about it, the number one fear of most people is public speaking... even more than the fear of death! You know what that means? If you are at a funeral, most people would rather be in the coffin than delivering the eulogy. That's just nuts!

And by going to IDUG you'll get a chance to network with IBMers, gold consultant, IBM champions, DBA, programmers, and more. Trust me... you don't want to miss out on this opportunity.

The Importance of SLAs and RTOs

Assuring optimal performance is one of the most frequently occurring tasks for DB2 DBAs. Being able to assess the effectiveness and performance of various and sundry aspects of your DB2 systems and applications is one of the most important things that a DBA must be able to do. This can include online transaction response time evaluation, sizing of the batch window and determining whether it is sufficient for the workload, end-to-end response time management of distributed workload, and so on. 

But in order to accurately gauge the effectiveness of your current environment and setup, Service Level Agreements, or SLAs, are needed. SLAs are derived out of the practice of Service-level management (SLM), which is the “disciplined, proactive methodology and procedures used to ensure that adequate levels of service are delivered to all IT users in accordance with business priorities and at acceptable cost.”

In order to effectively manage service levels, a business must prioritize its applications and identify the amount of time, effort, and capital that can be expended to deliver service for those applications.

A service level is a measure of operational behavior. SLM ensures that applications behave accordingly by applying resources to those applications based on their importance to the organization. Depending on the needs of the organization, SLM can focus on availability, performance, or both. In terms of availability, the service level might be defined as “99.95 percent uptime from 9:00 a.m. to 10:00 p.m. on weekdays.” Of course, a service level can be more specific, stating that “average response time for transactions will be 2 seconds or less for workloads of 500 or fewer users.”

For an SLA to be successful, all parties involved must agree on stated objectives for availability and performance. The end users must be satisfied with the performance of their applications, and the DBAs and technicians must be content with their ability to manage the system to the objectives. Compromise is essential to reach a useful SLA.

In practice, though, many organizations do not institutionalize SLM. When new applications are delivered, there may be vague requirements and promises of subsecond response time, but the prioritization and budgeting required to assure such service levels are rarely tackled (unless, perhaps, if the IT function is outsourced). It never ceases to amaze me how often SLAs simply do not exist. I always ask for them whenever I am asked to help track down performance issues or to assess the performance of a DB2 environment.

Let's face it, if you do not have an established agreement for how something should perform, and what the organization is willing to pay to achieve that performance, then how can you know whether or not things are operating efficiently enough? The simple answer is: you cannot.

It may be possible for a system assessment to offer up general advice on areas where performance gains can be achieved. But in such cases -- where SLAs are non-existent -- it you cannot really deliver guidance on whether the effort to remediate the "problem areas" is worthwhile. Without the SLAs in place you simply do not know if current levels of performance are meeting agreed upon service levels, because there are no agreed-upon service levels (and, no, "subsecond respond time" is NOT a service level! Additionally, you cannot know what level of spend is appropriate for any additional effort needed to achieve the potential performance, because no budget has been agreed upon.

Another potential problem is the context of the service being discussed. Most IT professionals view service levels on an element-by-element basis. In other words, the DBA views performance based on the DBMS, the SysAdmin views performance based on the operating system or the transaction processing system, and so on. SLM properly views service for an entire application. However, it can be difficult to assign responsibility within the typical IT structure. IT usually operates as a group of silos that do not work together very well. Frequently, the application teams operate independently from the DBAs, who operate independently from the SAs, and so on.

To achieve end-to-end SLM, these silos need to be broken down. The various departments within the IT infrastructure need to communicate effectively and cooperate with one another. Failing this, end-to-end SLM will be difficult to implement.

The bottom line is that the development of SLAs for your batch windows, your transactions and business processes is a best practice that should be implemented at every DB2 shop (indeed, you can remove DB2 from that last sentence and it is still true).

Without SLAs, how will the DBA and the end users know whether an application is performing adequately? Not every application can, or needs to, deliver subsecond response time. Without an SLA, business users and DBAs may have different expectations, resulting in unsatisfied business executives and frustrated DBAs—not a good situation.

With SLAs in place, DBAs can adjust resources by applying them to the most-mission-critical applications as defined in the SLA. Costs will be controlled and capital will be expended on the portions of the business that are most important to the business. Without SLAs in place, an acceptable performance environment will be ever elusive. Think about it; without an SLA in place, if the end user calls up and complains to the DBA about poor performance, there is no way to measure the veracity of the claim or to gauge the possibility of improvement within the allotted budget.

Recovery Time Objectives (RTOs)

Additionally, the effectiveness of backup and recovery should be a concern to all DB2 DBAs. This requires that RTOs (Recovery Time Objectives) be established. An RTO is basically an SLA for the recovery of your database objects. Without RTOs, it is difficult (if not impossible) to gauge the state of recoverability and the efficacy of image copies being taken. 

Each database object should have an RTO assigned to it. The RTO needs to take into account the same type of things that an SLA considers. In other words, the business must prioritize its applications, DBAs must map database objects to the applications, and together they must identify the amount of time, effort, and capital that can be expended to assure the minimization of downtime for those applications.

Again, we are measuring operational behavior. The RTO ensures that, when problems occur requiring database recovery, the application outage is limited to what has been defined as tolerable for the business (in terms of uptime and cost to provide that uptime).

Again, as with an SLA, for the RTO to be successful, all parties involved must agree on stated objectives for downtime and time to recovery. The end users must be satisfied with the potential duration of their application’s downtime, and the DBAs and technicians must be content with their ability to recover the system to the objectives. And again, cost is a contributing factor. The RTO cannot simply be I need my application up in 5 minutes and I can’t spend any more money to do that, because that is not reasonable (or possible).

Without written RTOs, DBAs can provide due diligence to make sure that database objects are backed up and recoverable, but cannot really provide any guarantee in terms of how quickly the data can be recovered (or perhaps, to what point in time) when an outage occurs. Of course, the DBA can create and review backup policies and procedures to encourage a recoverable environment. But there won't be any way to ensure with any consistency that the backup plan can deliver the time-to-recovery needed by the business.

So why don't organizations create SLAs and RTOs as a regular course of business? 

And if your organization does create SLAs and RTOs, please share with us how doing so became a standard at your shop...

DB2 Health Checks - Part 3

In parts one and two of this series on DB2 health checks, we discussed the importance of regularly checking the health of your DB2 subsystems and applications. We also looked at some of the issues involved in a health check including figuring out the scope of what is to be involved and some of the considerations to ponder as you approach assessing the health of your DB2 environment.

Of course, it is not really feasible to cover all of the components that you might need to address in your health checks in a series of posts in a blog. My true intent here is to get you to understand the importance of regularly checking DB2's health, instead of just plodding along and only making changes when someone complains!

But even though DB2 health checks are important and crucial to the on-going stability of your systems, they can be costly, time-consuming, and valid only for the point in time(s) that you review. But maybe there is something else you can do to attack this problem?

DB2 Offline Analysis

Instead of relying on outside experts to conduct your DB2 health checks you can instead rely on expert system software to provide a reliable, impartial analysis of your DB2 databases and applications. Such as solution is offered by Data Kinetics’ InnovizeIT Offline Analyzer for DB2 for z/OS.

How does InnovizeIT work? Well, similar to a  DB2 health check, the product deploys a two-step process to check the health of your DB2 databases and applications:

1. Collect data about your DB2 environment and ship it to your personal computer
2. Analyze the data and identify issues and potential problems

InnovizeIT is a planning and analysis tool that identifies mainframe DB2 bottleneck and performance degradation problems. DB2 performance and availability metadata is collected on the mainframe and downloaded to a Windows workstation. All of the analysis is performed offline, on the workstation, so there is no use of mainframe resources and no effect on mainframe performance.

Runing the analysis on a PC workstation instead of the mainframe is an important feature in today’s world of cost-cutting and resource management. Most organizations are looking for ways to reduce their mainframe MSU consumption and would not really look too kindly on a big analysis job consuming a lot of mainframe CPU to analyze your DB2 environment. PC resources are frequently idle during off hours, so it makes a lot of sense to run the analysis on those under-utilized resources.

The offline analysis process uses weighted analysis results with targeted and prioritized recommendations for fixing performance problems. The guided assistance InnovizeIT provides enables you to plan corrective actions and protect your budget regardless of static or dynamic SQL use, or variable workload processing.

The results of the analysis are categorized and reported using an easy-to-navigate GUI. You can scan and review the problems identified by the analysis all on your PC workstation. There is no need to go back and forth between the mainframe and the PC because all of the relevant information is captured to allow the DBA to review the results of the analysis. 

The information displayed is context-sensitive depending upon the issue you are investigating and the report you are viewing. You can combine performance metrics from your DB2 performance monitor to add more detail to the analysis and reports. And you can send all of the reports to a spreadsheet for posterity and distribution to all of the DB2 DBAs, developers and, indeed,  anyone interested.

Summary
DB2 health checking should be a standard component of your DB2 database management procedures. Regularly examining your DB2 environment for problematic issues makes good business sense because it can improve performance and reduce costs. And InnovizeIT for DB2 for z/OS is a useful and cost-effective mechanism for conducting regular health checks.

Consider taking a look at it today at http://dkl.com/innovizeit.html

Join the Transaction TweetChat

Today's blog post is an invitation to join me -- and several of my esteemed colleagues -- on Twitter on August 20, 2014 for a TweetChat on transactions.

Now that sentence may have caused some of you to have a couple questions. First of all, what is a TweetChat? Well, a TweetChat is a pre-arranged conversation that happens on Twitter. It is arranged by an organizer (for this one, that would be IBM) and features several invited "experts" to discuss the topic at hand.

The featured guests for this TweetChat are:

• Scott Hayes – @srhayes
• Craig Mullins  @craigmullins
• Kelly Schlamb  @KSchlamb

But everybody can participate. All that you need is a Twitter account and the hashtag, which for this event is #Transactions. You can search for the #Transactions hashtag, and all of the tweets using that hashtag will show up. You can participate in the TweetChat simply by including the hashtag #Transactions in your tweets.

So if you are interested in the conversation topic -- transactions -- be sure to join us and participate in the discussion... or at least just listen in to hear what folks think...

A Short Report from SHARE in Pittsburgh

Today’s blog post will be a short review of SHARE posted directly from the conference floor in Pittsburgh!

What is SHARE

For those of you who are not aware of SHARE, it is an independent, volunteer run association providing enterprise technology professionals with continuous education and training, valuable professional networking and effective industry influence. SHARE has existed for almost 60 years. It was established in 1955 and is the oldest organization of computing professionals.

The group conducts two conferences every year. Earlier in 2014 the first event was held in Anaheim, and this week (the week of August 3^rd) the second annual event is being held in my original hometown, Pittsburgh, PA. Now I’ve been attending SHARE, more regularly in the past than lately, since the 1990’s. But with the event being held in Pittsburgh I just had to participate!

The keynote (or general) session today started up at 8:00 AM. It was titled “Beyond Silicon: Cognition and Much, Much More”  and it was delivered by Dr. Bernard S.Meyerson, IBM Fellow and VP, Innovation.  Meyerson delighted the crowd with his entertaining and educational session.

Next up was “Enterprise Computing: The Present and the Future”, an entertaining session that focused on what IBM believes are the four biggest driving trends in IT/computing: cloud, analytics, mobile, and social media. And, indeed, these trends are pervasive and interact with one another to create the infrastructure of most modern development efforts. Bryan Foley Program Director, System z Strategy at IBM delivered the presentation and unloaded a number of interesting stats on the audience, including:

• Mainframe is experiencing 31 percent growth
• Mainframes process 30 billion business transactions daily
• The mainframe is the ultimate virtualized system
• System z is the most heavily instrumented platform in the world
• The mainframe is an excellent platform for analytics because that’s where the data is

Clearly, if you are a mainframer, there is a lot to digest… and a lot to celebrate. Perhaps the most interesting tidbit shared by Foley is that “PC is the new legacy!” He backed this up with a stat claiming that mobile Internet users are projected to surpass PC Internet users in 2015. Interesting, no?

Now those of you that know me know that I am a DB2 guy, but I have not yet attended much DB2 stuff. I sat in on an intro to MQ and I’m currently prepping for my presentation this afternoon – “Ten Breakthroughs That Changed DB2 Forever.”

The presentation is based on a series of articles I wrote a couple years ago, but I am continually tweaking it to keep it up to date and relevant. So even if you’ve read the article, if you are at SHARE and a DB2 person, stop by Room 402 at 3:00PM… and if you’re not here, the articles will have to do!

That's all for now... gotta get back to reviewing my presentation... hope to see you at SHARE this week... or, if not, somewhere else out there in DB2-land!

DB2 Health Checks - Part Two

In the first part of this series on DB2 health checks, DB2 Health Checks - Part One, I discussed the general concept of a health check and their basic importance in terms of maintaining a smooth-running DB2 environment.

Today, I want to briefly look at how DB2 health checks are usually done... if they are done at all.

The Scope of a DB2 Health Check

Some people mistakenly view a DB2 Health Check as being performance-focused only. Yes, performance is an important aspect of a health check -- and I admit that performance is generally the area that causes an organization to undergo the health check process. But the overall health of the DB2 environment needs to be addressed by the health check. In addition to performance-related issues (system, database and application), this can include:

• availability
• fault tolerance
• recoverability
• use of automation
• process review
• documentation
• people skills (DBA, sysprog, development, etc.)

Considerations Before Undergoing a DB2 Health Check

DB2 health checks are important and crucial to the on-going stability of your systems, but there are issues:

• Health checks can be costly (consulting engagements)
• When a consulting company conducts a health check the analysis usually is done off-site, so your DBAs do not learn the techniques used by the consultants as they massage and analyze the data
• Health checks generally are valid for a specific point-in-time and can become obsolete quickly

Conducting DB2 Health Checks

DB2 health checks typically are conducted by IBM personnel, a DB2 consultant, or a larger services firm. The engagement begins with experts/consultants interviewing the DBAs, submitting questionnaires as needed and collecting data from DB2. After collecting the data the consulting team goes off site and analyzes the reams of collected data. There may be intermittent communication between the consulting team and the on-site DBAs to clear up any lingering questions or to clarify things during the analysis phase. After some time (usually a week or more), a report on the health of your DB2 environment, perhaps with some recommendations to implement, is delivered.

What happens next is all up to you. After reading the report you can ignore it, implement some or all of the recommendations, conduct further in-house investigation for the feasibility of implementing the recommendations, or send it along to management for their perusal. But there is a deadline involved. After all, your systems are not static. So the health check report is only as good as the point-in-time for which it was delivered. Time, as it always does, will creep up on you. If you wait too long, the recommendations become stale and you might not be doing the proper thing for your environment by implementing changes based on old information.

Of course, when too much time has gone by after the health check, you could always engage with the services company and consultants again, requiring additional spending.

Is another way? 

Stay tuned, as we'll look at some other options in upcoming installments of this blog series on DB2 health checking...

Happy DBA Day!

Hey everybody, time to celebrate... today, July 25, 2014 is SysAdmin day! For the past 15 years, the last Friday in June has been set aside to recognize the hard work done by System Administrators. This is known as System Administrator Appreciation Day. 

As a DBA, I have regularly co-opted the day to include DBAs because, after all, we are a special type of system administrator -- the system we administer is the DBMS!

So if you are a SysAdmin, DBA, Network Admin, etc. have an extra cup of Joe and a donut or two. Hang up a sign on your cubicle telling people it is SysAdmin Day. And hopefully get a little respect and appreciation for all you do every day of the year!

DB2 Health Checks - Part 1

Left to their own devices, DB2 databases and applications will accumulate problems over time. Things that used to work, stop working. This can happen for various reasons including the addition of more data, a reduction in some aspect of business data, different types of data, more users, changes in busy periods, business shifts, software changes, hardware changes… you get the idea.

And there is always the possibility of remnants from the past causing issues with your DB2 environment. Some things may have been implemented sub-optimally from the start, perhaps many years ago… or perhaps more recently. Furthermore, DB2 is not a static piece of software; it changes over time with new versions, features and functionality. As new capabilities are introduced, older means of performing similar functionality become suboptimal, and in some cases, even obsolete. Identifying these artifacts can be troublesome and is not likely to be something that a DBA will do on a daily basis.

Nonetheless, the performance and availability of your DB2 environment – and therefore the business systems that rely on DB2 – can suffer if you do not pay attention to the health and welfare of your DB2 databases and applications.

Health Checking Your DB2

The general notion of a health check is well known in the IT world, especially within the realm of DB2 for z/OS. The purpose of a DB2 health check is to assess the stability, performance, and availability of your DB2 environment. Health checks are conducted by gathering together all of the pertinent details about your DB2-based systems and reviewing them to ascertain their appropriateness and effectiveness. You may narrow down a health check to focus on specific aspects of your infrastructure, for example, concentrating on just availability and performance, or on other aspects such as recoverability, security, and so on.

At any rate, scheduling regular independent reviews of your DB2 environment is an important aspect of assuring the viability and robustness of your implementation. Simply migrating DB2 applications to production and then neglecting to review them until or unless there are complaints from the end users is not a best practice for delivering good service to your business. Just like a car requires regular maintenance, so too does your DB2 environment. Regular analysis and health check with an overall goal should of identifying weaknesses and targeting inefficiencies, can save your organization time and money, as well as reduce the daily effort involved in implementing and maintaining your DB2 applications.

Think about the health of your DB2 system the same way you think about your health. A regular health check helps to identify and eliminate problems. And it helps you to perform the daily operational tasks on your DB2 databases and applications with the peace of mind that only regular, in-depth, knowledgeable analysis can deliver.

Check Back Soon
Later in this series we'll uncover more aspects of health checking and look at some software that might be able to assist. So stay tuned...

DB2 Application Performance Management

Assuring optimal performance for database applications can be a tricky thing. In today's blog I ruminate on the high-level issues involved in optimizing your DB2 for z/OS applications.

Applications that access databases are only as good as the performance they achieve. And every user wants their software to run as fast as possible. As such, performance tuning and management is one of the biggest demands on the DBA’s time. When asked what is the single most important or stressful aspect of their job, DBAs typically respond "assuring optimal performance."  Indeed, a Forrester Research survey indicates that performance and troubleshooting tops the list of most challenging DBA tasks.

But when you are dealing with data in a database management system there are multiple interacting components that must be managed and tuned to achieve optimal performance. That is, every database application, at its core, requires three components to operate:

·  the system (that is, the DBMS itself, the network, and the O/S),

·  the database (that is, the DDL and database schema), and

·  the application (that is, the SQL and program logic).

Each of these components requires care and attention, but today I want to focus on the high-level aspects of performance management from the perspective of the application. Furthermore, I will discuss this in terms of DB2 for z/OS.

So where do we begin? For DB2, a logical starting point is with BIND Parameters. There are many parameters and values that must be chosen from and specified when you bind a DB2 application program. The vast array of options at our disposal can render the whole process extremely confusing -- especially if you don’t bind on a daily basis. And even if you do, some of the options still might be confusing if you rarely have to change them. You know what I’m talking about, parameters like ACQUIRE, RELEASE, VALIDATE, and DEGREE.

I will not delve into the myriad bind options and give you advice on which to use when. There are many articles and books, as well as the IBM DB2 manuals that you can use to guide you along that path. Suffice it to say, that there are some standard parameters and values that should be chosen most of the time in most situations. As such, a wise DBA group will set up canned routines for the programmers to use for compiling and binding their applications. Choices such as: CICS transaction, DB2 batch, and BI/analytical query can be presented to the developer and then, based on which of the various types of programs and environments that are available, the canned script can choose the proper bind options. Doing so can greatly diminish the problems that can be encountered when the "wrong" parameters or values are chosen at Bind time.

Before concluding this short section on Bind parameters I want to give one important piece of advice: In production, always Bind your plans and packages specifying EXPLAIN YES. Failing to do so means that access paths will be generated, but you will not know what they are. This is akin to blinding yourself to what DB2 is doing and it makes application performance tuning much more difficult.

Access Path Management

Bind and Rebind are important components to achieve optimal DB2 application performance. This is so because these commands are what determine the access paths to the data requested by your program. So it is vitally important that you create a strategy for when and how to Rebind your programs. There are several common approaches. The best approach is to Rebind your applications over time as the data changes. This approach involves some form of regular maintenance that keeps DB2 statistics up to date and formulates new access paths as data volumes and patterns change. More on this in a moment.

Other approaches include Rebinding only when a new version of DB2 is installed, or perhaps more ambitious, whenever new PTFs are applied to DB2. Another approach is to Rebind automatically after a regular period of time, whether it is days, weeks, months, or whatever period of time you deem significant. This approach can work if the period of time is wisely chosen based on the application data – but it still can pose significant administrative issues.

The final approach -- the worst of the bunch -- comes from the if it ain’t broke don’t fix it school of thought. Basically, it boils down to (almost) never rebinding your programs. This approach penalizes every program for fear that a single program (or two) might experience a degraded access path. Oh, the possibility of degraded performance is real and that is why this approach has been adopted by some. And it can be difficult to find which statements may have degraded after a Rebind. The ideal situation would allow us to review the access path changes before hand to determine if they are better or worse. But DB2 itself does not provide any systematic method of administering access paths that way. There are third party tools that can help you achieve this though.

Anyway, let’s go back to the best approach again, and that is to Rebind regularly as your data changes. This involves what is known as the three Rs: REORG, RUNSTATS, and Rebind. At any rate, your goal should be to keep your access paths up-to-date with the current state of your data. Failing to do this means that DB2 is accessing data based upon false assumptions.

By Rebinding you will generally improve the overall performance of your applications because the access paths will be better designed based on an accurate view of the data. And as you apply changes to DB2 (new releases/PTFs) optimizer improvements and new access techniques can be used. If you never Rebind, not only are you forgoing better access paths due to data changes but you are also forgoing better access paths due to changes to DB2 itself.

To adopt the Three R’s you need to determine when to REORG. This means looking at either RUNSTATS or Real-Time Statistics (RTS). So, perhaps we need 4 R’s:

1. RUNSTATS or preferably, RTS
2. REORG
3. RUNSTATS
4. REBIND

But is this enough? Probably not because we need to review the access paths after rebinding to make sure that there are no rogue access paths. So, let’s add another R to Review the access paths generated by the REBIND. As we mentioned, the optimizer can make mistakes. And, of course, so can you. Users don't call you when performance is better (or the same). But if performance gets worse, you can bet on getting a call from irate users.

So we need to put in place best practices whereby we test Rebind results to compare the before and after impact of the optimizer’s choices. Only then can we assure that we are achieving optimal DB2 application performance.

Tuning the Code

Of course, everything we’ve discussed so far assumes that the code is written efficiently to begin with -- and that is a big assumption. We also need to make sure that we are implementing efficient application code. The application code consists of two parts: the SQL code and the host language code in which the SQL is embedded.

SQL is simple to learn and easy to start using. But SQL tuning and optimization is an art that takes years to master. Some general rules of thumb for creating efficient SQL statements include:

• Let SQL do the work instead of the program. For example, code an SQL join instead of two cursors using program logic to join.
• Simpler is generally better, but complex SQL can be very efficient.
• Retrieve only the columns required, never more.
• Retrieve the absolute minimum number of rows by specifying every WHERE clause that is appropriate.
• When joining tables, always provide join predicates. In other words, avoid Cartesian products.
• Favor using Stage 1 and Indexable predicates.
• But favor Stage 2 predicates over application logic.
• Avoid sorting (if possible) by creating indexes for ORDER BY and GROUP BY operations.
• Avoid black boxes -- that is, avoid I/O routines that are called by programs instead of using embedded SQL.
• Minimize deadlocks by updating tables in the same sequence in every program.
• Issue data modification statements (INSERT, UPDATE, DELETE) as close as possible to the COMMIT statement as possible.
• Be sure to build a COMMIT strategy into every batch program that changes data. Failing to COMMIT can cause locking problems.

Even if you follow the guidelines in this bulleted list, there will still be numerous opportunities for you to tune SQL for performance. To tune SQL you must be able to interpret the output of the access paths produced by EXPLAIN. This information is encoded in the plan tables. IBM offers Data Studio (as a free download) with a visual explain capability that can simplify this process. But you will also have to accumulate experience as to which SQL formulations work more efficiently than others. This skill will come with time and on-the-job learning.

Finally, some attention must be paid to the host language code. Host language code refers to the application programs written in C, COBOL, Java, Visual Basic or the programming language du jour. SQL statements are usually embedded into host language code and it is quite possible to have finely tuned SQL inside of inefficient host language code. And, of course, that would cause a performance problem.

Bottom Line

Although DBAs must understand all three aspects of database performance management concentrating on the application aspects of performance will most likely provide the most bang-for-the-buck. Of course, we have only touched the tip of the DB2 application performance iceberg today. But even this high-level view into application performance can serve as a nice starting place for tuning your DB2 applications.

Good luck with DB2 for z/OS and happy performance tuning! 

Database Versus DBMS

What is a database? I bet most people reading this blog post think that they know the answer to that question. But many of them would be wrong. DB2 is not a database, it is a DBMS, or Database Management System. You can use DB2 to create a database, but DB2, in and of itself, is not a database. Same goes for Oracle (which is a DBMS and a company) and SQL Server (just a DBMS).

So what is a database? A database is an organized store of data wherein the data is accessible by named data elements (for example, fields, records, and files). It does not even have to be computerized to be a database. The phone book is a database (Why do they still send out phone books? Does anyone even use them any more? Now I’m way off topic, so let’s get back on track.)

A DBMS is software that enables end users or application programmers to share data. It provides a systematic method of creating, updating, retrieving and storing information in a database. DBMSs also are generally responsible for data integrity, data access control, and automated rollback, restart and recovery.

In layman’s terms, you can think of a database as a filing system. You can think of the filing cabinet itself along with the file folders and labels as the DBMS. A DBMS manages databases. You implement and access database instances using the capabilities of the DBMS.

So, DB2 and Oracle and SQL Server and MySQL are database management systems. Your payroll application uses the payroll database, which may be implemented using DB2 or Oracle or…

Why is that important? If we do not use precise terms when we write, speak, and work confusion can result. And confusion leads to over budget projects, improperly developed systems, and lost productivity. So precision must be important to us.

Blog Recommendation: Essential SQL

Hello, regular readers... just a short post today with a blog recommendation for anybody who uses SQL or wants to learn how to use SQL.

The name of the blog is Essential SQL by Kris Wenzel.

I happened upon the blog a couple of weeks ago and it offers up some nice, educational content on SQL. It is not specific to DB2, but the material is high-level and easily convertible to a DB2 environment.

The material on the blog starts out very basic with no assumption of any prior SQL knowledge... and builds up over time adding on details. Learn as much, or as little as you'd like.

Hope you find the blog to be useful (either for yourself, or to pass along to others)...

Happy SQL coding!