Finding the reason behind a gradual slowdown, though, can be much more difficult. Because the slowdown takes place over a long period of time, pinpointing the actual problem takes more research. You'll have to consider many changes that may have occurred in that timeframe, including hardware upgrades, application changes, and third-party software changes. Sometimes sheer volume will degrade performance; other times a new component causes the difficulty. One day, you notice that your application just isn't as fast as it was.
The complexity of the systems in most companies makes finding the cause of the gradual slowdown difficult. But working through the five areas I suggest here will help ensure that DB2 Universal Database runs optimally. These five areas apply to both mainframe and distributed platforms. However, I've drawn most of my examples from DB2 for z/OS and OS/390. (Read "Tuning Up for OLTP and Data Warehousing" for a Linux, Unix, and Windows-specific discussion.) Regardless of which platform you use, this information is only the tip of the iceberg. (See Resources for more information.) The topics are in no particular order, and you may not need all five. Choosing the ones that apply to your situation may come down to gut instinct.
An application encompasses more than SQL and database objects. Often, the performance culprit is outside of DB2 — either in the I/O subsystem (which I'll cover later) or in the application.
When considering the whole application, you need to think beyond the application code. Unless you only work with mainframe CICS/DB2 or IMS Transaction Manager/DB2 transactions, your requests for information usually come from somewhere else. You may be dealing with inbound transactions from Web clients, WebSphere MQ transactions from legacy systems, or transactions from a nonmainframe client. Even if you're totally mainframe-bound, other applications probably use the same mainframe. Or, your application could be using data sharing.
Remember that queries coming from another place frequently use DB2 Connect to get information from DB2 on OS/390. Parameters in DB2 Connect work with DSNZPARMs in DB2 to obtain the best performance characteristics for transferring information between the two. Then, there are all those wires that connect your application. Gigabit or Ethernet? TCP/IP or SNA? Routers? Token Ring?
ResourcesalphaWorks Emerging Technologies DB2 Developer Domain DB2 for z/OS and OS/390 Library IBM Redbooks and Redpieces DB2 for z/OS and OS/390 Support |
So, beginning from the center and moving out, here are some non-DB2 considerations for performance improvement:
DISPLAY
GROUPBUFFERPOOL command helps you determine if you're sizing the
coupling facility correctly because the group buffer pool has the greatest
impact on the size. (See the Data Sharing: Planning and Administration manual
for more information.) The statistics reports also note group buffer pool
and data-sharing activity.
MAXDBAT and CONDBAT are
set effectively (check Global DDF Activity in the performance monitor
reports). Also, make sure block fetch is enabled. Some poor coding and
binding techniques remain hidden until the number of concurrent users
increases, so check how application programmers code and bind their programs
( CURRENTDATA , ISOLATION , and FOR FETCH
ONLY are important factors).
It's also important to know the characteristics of the application as it relates to DB2. You may need to tinker with database design to improve database performance. (Of course, if at all possible, you should be involved in the design phase of any new application. It's easier to change diagrams than databases.) You'll want to focus on general access characteristics. Are there many concurrent users or long-running batch jobs? A mixture of both? Do you know which tables will experience significant growth? Where are the hot tables? The hot spots on tables? Look for a group of small tables (typically code tables) that are frequently referenced and rarely updated.
Once you've analyzed the application, these DB2 considerations may help solve or prevent performance problems:
PCTFREE and FREEPAGE on tablespaces and
indexes. Are they sufficient? Don't forget that variable-length records
can grow or shrink. Accurate use of PCTFREE and FREEPAGE can
reduce the need to reorganize the object.
DSNZPARM s to ensure that NUMLKTS and
NUMLKUS are set correctly for your application.
DSNZPARM s. Several of these parameters can affect
subsystem performance (and they have an even greater affect in a distributed
environment), including AUTHCACH , CACHEDYN , CONDBAT
, CONSTOR , CTHREAD , DSMAX , EDMDSPAC
, EDMPOOL , IRLMRWT , IRLMSWT ,
LOGLOAD , MAXDBAT , MAXKEEPD , OPTHINTS
, PCLOSEN , PCLOSET , and SRTPOOL .
When I talk about the I/O subsystem, I mean everything from when the GETPAGE
request is made to when the requested page is returned. Despite
significant improvements in storage devices over the last decade, I/O remains
one of the most costly features of a DB2 application.
Buffer pools are perhaps the most important I/O consideration. Buffer pool assignment, size, and thresholds can seriously affect performance.
Make sure that only the DB2 catalog and directory are in buffer pool zero. Sometimes third-party products sneak in and can reduce performance levels. Check that your tablespaces and indexes are in separate buffer pools. Then you can begin the analysis of tablespace and index categories I mentioned. Common categorization techniques include large objects with random access, small objects with random access, large objects with sequential access, and small objects with sequential access. You could separate the objects accessed most frequently from those rarely accessed. When you complete your analysis, spread these objects across some of the 60 available buffer pools. And always make sure that you have a separate sort buffer pool.
To check buffer pool sizes and thresholds, use a monitoring tool (such as DB2 Performance Monitor or the new DB2 Performance Expert) and look at the buffer pool statistics section. Low buffer pool hit ratios are generally an indication that your buffer pool is too small. A number greater than zero for the data management threshold or a high number in the prefetch disabled buffer counter is another indication that your buffer pool is too small or that one of your key thresholds is set incorrectly. In a sort buffer pool, remember that increasing the size goes hand in hand with adding more data sets to the storage.
There are six key thresholds that you can modify: sequential steal ( VPSEQT
), hiperpool sequential steal ( HPSEQT ), virtual buffer
pool parallel sequential ( VPPSEQT ), virtual buffer pool assisting
parallel sequential ( VPXPSEQT ), deferred write threshold ( DWQT
), and vertical deferred write threshold ( VDWQT ). Check
the DB2 Administration Guide for information about how to set these
thresholds to optimize them for particular types of workloads.
If the page isn't in the buffer pool, DB2 checks the hiperpool or data space (if you have them — the use of data spaces isn't recommended without 64-bit implementation). Remember that hiperpools are read-only pools; they can actually decrease your performance if your data is updated.
If the data isn't in any of the pools, then DB2 must do an I/O, one of the most costly operations. But you can minimize this cost. First, consider the connections you're using. Using FICON (IBM's implementation of fiber optic technology on the S/390) rather than ESCON may result in improved performance in certain situations. The first implementation of this multiplex technology has shown a one gigabit per second transmission rate during performance studies compared to a 17MB transmission rate for ESCON technology. Companies that do remote disk access can also benefit from the newer FICON technology.
Finally, the storage device and its layout can have a great impact on performance. Fortunately, with system managed storage, the days of hand-placing thousands of DB2 data sets are gone. However, the number of Parallel Access Volumes can influence performance, as can overworked channels with heavily accessed data sets that are affected by DB2 operations such as logging. And, you are keeping DB2 objects such as catalogs separate from your user data, aren't you?
All too often, a performance problem results from poorly tuned SQL or a lack of understanding of the impact of newer SQL features such as large objects (LOBs), temporary tables, and different types of joins. It's better to be proactive about finding problem SQL instead of waiting until your cell phone rings at midnight.
You can use one of several available tools to help identify poorly performing
SQL, or you can become very familiar with the PLAN_TABLE and EXPLAIN
process. (Actually, even if you have tools, understanding the contents of
the PLAN_TABLE can help you.) Here are some "tried and
true" items to look out for:
DEGREE must equal ANY ) for that
kind of query. Turning on parallelism across the board is not a good idea,
however, as it can lead to performance degradation in some cases.
If you find SQL that could use improvement, there are a few ways to tackle
the potential problems. The first is to ensure that the objects the SQL uses are
in good health. Are statistics updated? Does anything need a REORG ?
Are all the proper indexes defined? Are all objects available and not in a
"pending" state?
The second is to work with the programmer to see if the SQL can be changed to
improve performance. Sometimes, eliminating unnecessary columns, changing the
order of the tables as they are joined (remember that only the WHERE clause
or table expression has influence), or changing the type of join ( LEFT/RIGHT
, OUTER , INNER ) can help. Also, watch out for
columns and host variables that are mismatched in type and length. Some of the
poor performance resulting from this type of mismatch has been eliminated, but
not all.
Finally, remember that indexes aren't free. Checking to see if you can drop any nonunique, unused indexes can improve your insert and delete processing (and, potentially, your update processing).
With complex architectures, it's important to have a plan for finding performance problems when they occur and the tools and knowledge to analyze the problems. Knowing who in your company is the expert on the different aspects of the system is a good place to start. These folks should be available to you whenever a performance problem arises.
Good tools alert you when a problem is occurring (examples include selections from Tivoli, BMC Patrol, and others). You can use DB2 Performance Monitor and RMF to trace trends. It's helpful to try to spot the problem before it becomes a crisis.
Off the mainframe, VMSTATS can give you an idea of how much CPU the Unix machines are using. DB2 CLI Trace, DDCS Trace, OS/390 TCP/IP Packet Trace, Network Analyzer Trace (sniffer), DB2 Performance Expert, and the DB2 Control Center Performance Monitor are also useful tools. Although you probably won't know all these tools, you should get to know someone who does.
Educating yourself may seem like a luxury (and odd advice for improving performance), but it's critical. Learning in the middle of a crisis is never a pleasant thing.
Start with the basics. If you don't understand the principles and architecture behind the version of DB2 you're using, you'll have a hard time with problem analysis. DB2 has changed dramatically in the last few versions. New features include a Control Center, Java database connectivity, and WLM-managed stored procedures. More and more of our customers are using WLM-managed stored procedures and LOBs for the first time.
You can find information in the Release Planning Guide or What's New documentation books, available at the IBM Web site . Redbooks delve into more specific topics, including performance topics for each version. Finally, if you have a specific question, you can check IBM's eSupport (see Resources).
Have a clear understanding of the utilities, whether IBM's or a third party's. Know what features are available with these utilities, and have sample jobs on hand that you can run when the pressure is on.
DB2 professionals can't live by DB2 alone. In order to effectively untangle performance problems, you need to understand (at least conceptually) how the queries are getting to the mainframe. This understanding includes a nodding acquaintance with Web application servers and DB2 Connect.
For advanced technologies, check out the alphaWorks Web site. The DB2 Developer Domain site has information pertinent to every DB2 developer or DBA, including information about certification.
In a recent article for DB2 Developer Domain, SQL expert and consultant Sheryl Larsen wrote, "I have a profound statement to make about SQL skills — on average most DB2 developers are seven years behind." If that statement is true for you — it's time to get educated.