Select Page

Quick Thoughts on DB2 Performance

Craig Mullins | | November 9, 2017

DB2 performance

Database performance problems are not caused by magic. Indeed, all performance problems are always caused by change. That statement flies in the face of what I normally say, which is “Almost never say always or never…” but in this case, it is true.

Think about it for a moment. If everything remains stable and unchanging in your environment, then why would performance vary? That’s right, it wouldn’t.

Something tangible must change before a performance problem can be experienced. The challenge of performance tuning is to find the source of the change, gauge its impact, and formulate a solution.

Change can take many forms, including the following:

  • Physical changes to the environment, such as a new CPU, new disk devices, or different tape drives.
  • Changes to system software, such as a new release of a product (for example, WebSphere, CICS, or even z/OS), the alteration of a product (for example, the addition of more or fewer CICS regions or an IMS SYSGEN), or a new product (for example, implementation of DFHSM). Also included is the installation of a new release or version of DB2, which can result in changes in access paths as well as utilization of new features.
  • Changes to the DB2 engine from maintenance releases and PTFs, which can change the optimizer (and sometimes introduce other new functionality).
  • Changes in system capacity. More or fewer jobs could be executing concurrently when the performance problem occurs. Or additional users may be banging away at your transactions.
  • Environmental changes, such as the implementation of client/server programs, the adoption of SOA, or other new technologies.
  • Database changes. This involves changes to any DB2 object, and ranges from adding a new column or an index to dropping and re-creating an object.
  • Changes to the application development methodology, such as usage of check constraints instead of application logic or the use of stored procedures.
  • Changes to application code, both SQL and host language code (COBOL, C, Java, etc.).

Although the majority of your performance problems are likely to be application-oriented, you must be prepared to explore any and all of these other areas when application tuning has little effect.

My advice is to be sure that you institute strict change control tracking across all areas of your IT infrastructure. That way, whenever you experience a performance problem, you will be able to track what has changed recently, along with who changed it and why. This is important because every DBA knows what the answer to the question “What changed?” will be… right?

It is always “nothing!”

And that cannot be true. Oh, it does not mean that the person answering is lying. He or she may not have changed anything. And it is not necessarily reasonable to expect an application developer to know what all could have changed…especially when what can impact DB2 performance spans so many areas of the IT infrastructure.

So, do yourself… and your company a favor: be sure that you meticulously track each and every change to any aspect of your systems. Then – and this is where many shops break down – make sure that you have methods of tying all of the change information together in such a way that it can be queried and examined in the face of a performance problem.

Only then can you reasonably expect your DBAs rapidly to be able to track down and remedy DB2 performance problems… because only then will they have the pertinent information at their disposal.

This post was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2009/12/quick-thoughts-on-db2-performance.html

For additional resources please download my white paper: “The Many Different Types of DBAs.”

 

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS