Banco do Brasil (BB) is one of the biggest and most profitable banks since the 2000 bank in Brazil and Latin America. BB is present in 26 foreign countries, provides a variety of services and is controlled by the Brazilian government
Banco do Brasil faced problems concerning the quality of SQL and DB2 databases in the mainframe environment at their centers in Brasília and Rio de Janeiro. Álvaro Peres, manager of the performance analysis and planning capacity division of the Banco do Brasil says:
“Applications degrade naturally – imagine this deterioration in a structure that processes 410 million transactions daily. We use DB2 version 8, data totals 260 billion rows and allocates 66 TB of storage.
Our largest table has eight billion rows, up to 800 new modules or changes are transferred into our production systems every day; 90% of which with SQL. In short, it was impossible to verify quality without the help of a tool”, affirms the executive.
Furthermore, the processing of the bank’s transactions accessing DB2 consumes 67% of the total processing capacity, which reaches 109,410 MIPS. According to Peres, the job of pursuing the consuming “villains” was largely carried out manually, with results often arriving too late. “The tools that we had gave results, but over a very long period. This is what motivated us to seek something that automated the process”, he recalls.
DB/IQ was the best answer we could bring to Banco do Brasil. DB/IQ is marketed exclusively in Brazil by RSI Informática, which was responsible for implementing the software in the institution.
So far, two DB/IQ components have been implemented: QA and IA. The first one ensured the SQL standards, while the second is responsible for checking and optimizing indexes. “Our main focus is to hinder SQL instructions coming into production, which would otherwise violate standards. For this, we set some goals: optimize disk space, improve performance, identify and solve problems”, explains Peres.
Feedback from the client
After just a few months of use, DB/IQ has regained disk space used by DB2 databases and indexes and improved the processing capacity of the institution’s mainframes. Peres proudly states “There are several measurable gains, but I believe the main one is the fact that the gain of CPU will allow us to postpone the purchase of new equipment for some time”.
The division’s manager remembers the initial DB/IQ reports produced, containing information which would have been impossible to raise in so short a time in any institution the size of the Banco do Brasil. Some examples: the analysis of the bank’s “acceptance” environment presented 1,012 indexes unused, 210 duplicated identical indexes and 6,788 indexes smaller than ten pages. The unused indexes themselves occupied 340 GB of disk space. Another analysis, made in the production environment, unveiled 8,609 unused indexes and 209 duplicate indexes. In this case, the unused indexes occupied 5 TB of disk space.
“The analysis reports allowed us to take corrective action and standardize our environment” said Peres, remembering that only the exclusion of indexes guaranteed more disk space and increased the processing capacity.
The manager affirms that the next steps should include the implementation of the QA module in the evaluation of new SQL programs, the adjustment of the process of creating indexes and tables and introduction of features such as DB/IQ’s trace monitor, which will analyze dynamic queries.