Creating a Clear Window into DB2 with BMC SQL Performance for DB2

Banco BPI is one of the five largest Portuguese financial groups. We offer commercial banking services, investment banking, asset management, and insurance services within Portugal. The digital and mobile revolution has had a huge impact on the banking industry in recent years. Customers today expect online services accessible through a variety of devices including computers, smartphones, and tablets. In response to these changing customer expectations, our IT organization is focusing on three major objectives that are aimed at driving business success:

• Fulfill the needs of our customers,
• Stay one step ahead of the competition, and
• Keep costs in check.

To meet those objectives, we’re taking advantage of the latest technology while also keeping our legacy applications running at peak performance. That’s why our IT organization recently split into two groups: one dedicated to legacy applications and the other dedicated to new technologies, including mobile and internet. Gartner refers to this as bimodal IT.

The legacy team supports our mainframe environment, which runs z/OS, CICS, DB2, and COBOL programs, and handles distributed requests. Despite it being a separate group, the legacy team isn’t isolated from the new digital services that are being developed. Most of the bank’s production data resides in DB2 databases that serve both legacy systems and digital banking services. We currently have about five terabytes of production data and handle approximately 500 million SQL requests each day. Nearly one-fifth of those requests come from online transactions submitted by online banking customers.

Because of its central role in supporting the business, the production DB2 database must run reliably at peak performance around the clock. That’s where I come in as database administrator. For more than a decade, I have relied on BMC SQL Performance for DB2 to give me the visibility into DB2 that I need.

Capturing and Leveraging DB2 Performance Data

When we first implemented SQL Performance for DB2, we used it to monitor DB2 performance in the production environment, looking for suspicious behavior. We would then use the solution to help us determine if specific problems were related to DB2 or to a specific SQL query. If it was a SQL problem, we notified the appropriate developer for resolution. At this point, we would use traditional tools such as SQL traces to gather and access performance data. This was extremely difficult and time consuming and it generated substantial overhead on our mainframe. Consequently, we didn’t have a lot of meaningful forensic data to pass along to the developer to facilitate troubleshooting.

We then began to exploit the power of the performance database created by the SQL Performance for DB2 component AppTune. With this capability, it’s much easier to access performance data and we can run queries and reports to translate that data into actionable information. We’re leveraging SQL Performance for DB2 in a variety of ways. Here are a few examples:

• SQL Performance for DB2 captures host variables when errors occur, providing forensic information that speeds diagnostics and reduces mean time to resolution of production issues. For example, during a routine check of the previous night’s operations, we saw that a timeout had occurred. We notified the developer and forwarded the host metrics gathered by SQL Performance for DB2 at the time of the timeout. This information enabled the developer to quickly fix the problem and, more importantly, prevent future timeouts.

• We are identifying and correcting SQL issues in batch jobs, which improves efficiency and runtime of those jobs. As a result, we have room to run more batch jobs within the batch window without increasing our costs. We reduced the running time for one job from one-and-a-half hours to less than 30 minutes.

• We’re shrinking online transaction times, which keeps our customers happy. In one instance, we decreased processing time for a transaction from two seconds to less than one second. That kind of performance improvement has a significant impact on customer satisfaction.

• We have moved into a proactive mode by leveraging SQL Performance for DB2 to tune new applications while they are still in the quality assurance environment. We’re identifying issues and making recommendations to developers so they can make corrections before the applications are promoted to production. For example, without running a SQL trace, we can pinpoint SQL statements that are heavy resource consumers. We then provide developers with that information so they can fine tune their code to reduce consumption.

• We’re leveraging the performance database to respond faster and more effectively to audit requests. We store information about certain kinds of queries that are of interest to auditors. For example, auditors are interested in knowing what ad-hoc queries are being made on production data. We move the information from the performance database to another database where we can create reports that help our auditing team ensure that queries are properly authorized. Before SQL Performance for DB2, gathering, analyzing, and reporting on data for auditing was difficult and time consuming.

• We use the Reorg Advisor component of the BMC solution every day to evaluate DB2 object performance and determine when a reorganization may resolve a performance problem. We coordinate reorgs with the infrastructure team and forward the report generated by Reorg Advisor to guide them. Before we implemented Reorg Advisor, we didn’t have the data we needed to determine which objects needed reorganization.

• The performance database helps us weed out objects that are no longer being used. We call this “grooming” the environment. We run queries against data in the performance database to determine which objects have not been accessed by SQL for a considerable period of time. We then check with the application owner to find out if we can remove unused objects to free up storage and processing power. In one case, we discovered 100 tables that were not being used. The application owner informed us that the objects hadn’t been used for several years, but no one had thought to remove them from the system when they were no longer needed.

Meeting the Objectives

BMC SQL Performance for DB2 has enabled us to meet all three of our objectives. By keeping DB2 running at peak performance with both legacy and new applications, we keep our customers happy. By maximizing DB2 efficiency, we can match and surpass the services offered by much larger banks, and do it with much less machine power and hence lower costs.