MAINFRAME > Administrator > Db2

A Db2 Utilities Migration Project

Banco do Brasil

Banco do Brasil is the second largest bank by assets in Brazil and Latin America. The bank, headquartered in Brasília, was founded in 1808 and is the oldest active bank in Brazil—even older than the country's central bank. They also have an expanding worldwide presence in 24 countries and 889 corresponding banks in 107 countries.

Banco maintains one of the largest Db2 environments in the world and the largest in Latin America, which complicated its Db2 utility migration project. Figure 1 shows the growth of the bank’s Db2 environment over the last sixteen years.

This article describes Banco’s experience migrating from an ISV’s Db2 utilities to the IBM Db2 Utilities Suite for z/OS, along with the unique challenges and requirements the bank encountered during its migration journey.

The Decision

In 2001, IBM removed Db2 utilities as a feature of the Db2 engine and sold them separately. This gave customers more flexibility on which vendor to select for Db2 utilities. Banco, for example, undertook a project to evaluate other ISV Db2 utility offerings. It took the bank 18 months to convert its IBM Db2 utility jobs and syntax to one of the ISV offerings they evaluated. Banco’s main motivation behind the move was improving performance and availability. From 2010 to 2012, Banco maintained a relationship with IBM and closely monitored the enhancements added by IBM’s Db2 utilities.

Banco considers timely Db2 for z/OS support critical to its business. The bank also helps drive its business requirements into new Db2 for z/OS features. As a result, Banco expects its software vendor to provide day one support for all new Db2 features and functions.

Starting with Db2 Version 10, Banco experienced delays in Db2 support from its current ISV. Banco considers timely Db2 for z/OS support critical to their business. Banco also noticed that IBM’s Db2 utilities were superior in day one Db2 support and were meeting or exceeding performance capabilities, unlike Banco’s current ISV.

From there, it became a question of price. Management needed a solution to avoid migration of JCL when Banco’s ISV raised the price of its utilities. For management, cost was a major factor in the decision to consolidate and use only IBM’s Db2 utilities.

Getting Started

Once management made the decision to migrate, Banco’s Db2 staff developed a comprehensive migration plan. Both IBM’s and the ISV’s utilities had many parameters, as well as other differences. For example, IBM’s utilities rely on a switch phase to implement some of the online updates in LOAD and REORG. IBM’s utilities are also more dependent on the use of Db2 buffer pools.

There have been significant changes in IBM Db2 utilities to eliminate stealing of data pages. Banco relied on some specific functions provided by the ISV utilities that weren’t immediately available from IBM. To implement these functions in IBM’s utilities, Banco submitted requests for enhancement and worked closely with IBM.

The decision on which individual ISV utility and syntax to migrate first was based on complexity and function. There were also significant JCL differences between the ISV’s utilities and IBM’s. The JCL DDNAMEs from the vendor required translation to IBM’s method of using TEMPLATES.

Banco had a few other migration challenges, too. Various development areas in Banco are responsible for utility scheduling. However, these development areas can’t schedule JCL to execute DSNUTILB directly. The Banco Db2 staff installed pre-defined JCL procedures for their development areas to use. Additional steps were often needed to customize these JCL procedures, including:

  • Modifying existing JCL procedures to dynamically change from the ISV to IBM utilities
  • Writing COBOL programs to read the ISV SYSIN and create an IBM equivalent
  • Calling SDSF to identify any DDNAME overrides
  • Updating the control tables, one update changes all JCLs between the ISV and IBM
  • Depending on the return code and the COND parameter, execute only the ISV or IBM step

The LOAD Utility

Banco presented the details of the LOAD conversion at Think 2018. The bank’s approach is being applied to all utility conversion work. It starts with the basic premise that Banco has a complete understanding of its utility needs and then matches or develops workarounds to meet the bank’s needs. From there, Banco looked at each of those specific utility jobs and ISV parameters. In summary, the workarounds consisted of:

  • Fully understanding and evaluating ISV-specific features and parameters, and then matching those findings with an IBM process that delivers Banco’s desired results
  • Matching IBM utility behavior to that of the ISV and working with IBM to address missing functions via APAR/PTF
  • Using a COBOL program developed at Banco

Banco needed LOAD REPLACE SHRLEVEL REFERENCE functionality to allow concurrent read access to the target table while new data is loaded into a set of shadow data sets. A SWITCH phase was introduced to the LOAD utility to switch access between the original and shadow data sets. IBM delivered this functionality via PI67793.

The ISV LOAD utility used a two-phase process to validate data prior to the actual LOAD operation. For Banco, this capability avoided leaving objects in a recovery pending situation when discarded records (DISCARD > 0) were encountered. The ISV LOAD utility allowed Banco to choose the DISCARD > 0 behavior they needed. One option was not loading any rows and returning with a customized return code. The second option was to load all the rows, discarding the duplicates and returning with a customized return code.

To match this function, Banco used IBM’s LOAD RESUME with BACKOUT YES or DIS=0. The bank also did a LOAD REPLACE (SHRVLEVEL REFERENCE), and wrote a COBOL program to read the SYSOUT to look for discarded rows and provide the return code.

IBM’s use of FlashCopy technology was a performance advantage that Banco implemented to save resources and improve availability. The bank was able to perform an inline copy using only FlashCopy technology. A copy statement was used after loading smaller tables. If that table had referential integrity, they forced the inline copy to execute. The copy would abend if the table was in a CHECK-PENDING status. For larger tables using inline copy, Banco created automation to detect the FlashCopy copies and start a COPYTOCOPY job. Full support for incline copy was delivered by IBM in PI81723.

LOAD Utility Results

The IBM LOAD using RESUME YES SHRLEVEL CHANGE with PARALLEL greatly reduced elapsed time and CPU usage at Banco. Figure 2 represents the LOAD utility results during the conversion from March to December 2017. The blue line represents the number of IBM LOAD utility jobs executed, the red line indicates elapsed time and the green line indicates CPU usage.

The number of IBM LOAD jobs increased as the conversion progressed. In August 2017, the number of IBM LOAD jobs significantly increased, and the overall elapsed time and CPU usage during the IBM usage decreased. The results from the IBM LOAD utility as of December 2017 are as follows:

  • Approximately 93 percent of Banco’s LOAD utility jobs were converted to IBM
  • CPU usage was reduced by 65 percent
  • Elapsed time was reduced by 25 percent

Delivering Performance, Availability and Timely Support

At the time of the Think 2018 presentation, Banco had converted many of its ISV utilities and were on course to a full conversion. Significant progress was made toward converting REORG, CHECK, UNLOAD, COPY and RUNSTATS, but Banco was still using the ISV’s Db2 utilities for COPYTOCOPY and MODIFY.

One of the biggest efforts from Banco was the addition of over 20,000 lines of COBOL to support the conversion. The programs helped with creating history tables and discovering space to automate decisions after reading and generating SYSINs. As well as converting necessary functionality, Banco took advantage of this effort to increase costs savings, especially CPU usage and elapsed time. The bank replaced many traditional COPY jobs with FlashCopy jobs. In the LOAD utility, they eliminated LOAD LOG NO without copy to force a FlashCopy to occur. For the UNLOAD utility, they ran an EXPLAIN statement on their SQL to determine when to use Db2 or when to use direct VSAM access.

Performance, availability and timely support of all Db2 functionality was important to Banco. The bank’s management provided the key directive of reducing ISV costs and overall maintenance of two sets of utilities. Throughout the entire project, IBM worked with Banco to understand the bank’s requirements and provide support.

Banco’s decision to convert Db2 utilities from an ISV to IBM Db2 utilities isn’t an uncommon one. Over the last several years, IBM has been working closely with several customers who have also decided to move from Db2 ISV utilities to IBM Db2 utilities. The size and scope of migrations differ, but a constant factor in the decision is the need to reduce costs while maintaining or improving performance and delivering business continuity.

The migration has brought good things to Banco, explains Alessandro Schneider, head of data management infrastructure, Banco do Brasil. "Despite the great risk of changing thousands of routines automatically, and the coding effort, the migration of Db2 z/OS utilities to IBM has brought great results for Banco do Brasil, partially due to removing the dependency of a single ISV and potentially allowing improvements in our processes."

Haakon Roberts is a Distinguished Engineer at the IBM Silicon Valley Laboratory in San Jose, California, and the chief technical architect for Db2 for z/OS Utilities and Tools.

M. Sueli Almeida is an IBM certified thought leader and member of Db2 for z/OS development at the IBM Silicon Valley Laboratory in San Jose, California.

comments powered by Disqus



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

An Intuitive Approach to DB2 for z/OS SQL Query Tuning

Real-world samples provide valuable lessons.


A Db2 Utilities Migration Project

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters