Insurance for Db2 for z/OS
Log-On Software’s Total Utility Control can automate many database tasks, as the North Carolina Farm Bureau Mutual Insurance Company recently discovered
As the old saying goes, there are never enough hours in a day.
Which seems to be the case in many IT departments. When companies grow, the folks in the data center are responsible for introducing new technologies or better leveraging what they already have to ensure there are no backend hiccups that will slow or stifle company expansion.
Thankfully, most IT professionals take pride in their work and put in whatever reasonable extra effort is needed to ensure success. Jeff Boggess, database services supervisor with the North Carolina Farm Bureau Mutual Insurance Company (NCFBMIC), supports this assertion, saying, “We’ve got a small but very good team here that does the work we need, and I’m grateful for that. But I’m also not trying to not kill them.”
That’s why Boggess and his colleagues in NCFBMIC database services are introducing as much automation into their processes as they can to improve operational efficiency and team productivity.
For example, it would often take three to four days to move data from production to a test environment using the Db2 for z/OS LOAD/UNLOAD utility. But after deploying Total Utility Control (TUC) from Log-On Software, that number plummeted to hours.
Much of this had to do with TUC’s ability to automate and accelerate data movement tasks by leveraging the very fast and efficient DSN1COPY data copy tool. The team’s prior use of DSN1COPY had been hindered by its complexity. And that’s in part why the TUC solution was introduced to database services. It approaches DSN1COPY by layering complementary logic and a much easier-to-use interface on top of it, thereby hiding its native complexity and making the utility much easier to work with.
As Boggess explains, “The fastest way to move data from one system to another has always been using DSN1COPY, but that utility is so complex it was rarely used. Instead, we were forced to build our data movement processes around the LOAD/UNLOAD utility. The time-consuming nature of those processes limited us to refreshing test data only semiannually or annually. This put our developers in the position of generating test results that might not accurately reflect how an application would perform in production. This was a risk we needed to eliminate.”
Dealing With Misfortune
And eliminate it the team did, for good reason.
Since being established in 1953 by the North Carolina Farm Bureau, NCFBMIC has experienced tremendous growth. Although first opened as an insurance company that focused primarily on the insurance needs of farmers and rural communities, it has now expanded beyond that customer base and entered new markets.
In fact, it has built the largest domestic property and casualty insurance company native to North Carolina. With 1,700 employees and a network of 830 agents located throughout the state, NCFBMIC now provides insurance products for both farm and non-farm policyholders alike in every county in North Carolina.
“We’re one of the largest domestic auto insurers in the state,” Boggess notes, “coming in third behind two of the largest nationwide insurance providers with over 1,700 corporate employees, a network of 830 agents located throughout the state and around 1.2 million policies in force. Our goal is to work hard every day to maintain our financial stability and market strength to provide the security our members need when misfortunes occur.”
Unfortunately, until recently, NCFBMIC’s mainframe database support staff had to deal with their own misfortunes, including not only the above production-to-test data transfer inefficiencies—inefficiencies that consumed days of DBA time and excessive CPU—but also challenges related to executing the REORG and RUNSTATS utilities.
Although its IBM z14 ZR1, which runs four LPARs hosting 13 Db2 subsystems (three for production and ten for testing), offers ample capacity to handle the Db2 workload, performance would degrade when the health of database objects was suboptimal. In particular, running REORG and RUNSTATS against individual objects prematurely or belatedly was a setup for excess CPU consumption and/or poor database performance.
Before the implementation of Log-On Software’s TUC solution, the database team would periodically run these utilities against Db2 objects, but with only a basic understanding of which objects needed attention at a given time and which did not. The analysis required to be selective with object maintenance was simply too time-consuming.
Because of this, Db2 performance and space utilization efficiency would suffer due to inefficient object organization, or excess CPU would be consumed running these utilities against objects that weren’t in need. In the former case, the company occasionally suffered delays when Db2 objects ran out of space. These non-trivial issues were obstacles to maintaining the fast and reliable mainframe-based services needed to support the company.
Improving Database Maintenance
Knowing this couldn’t continue that way, Boggess and the rest of the Db2 team began looking for solutions that would ameliorate the problems or, better yet, get rid of them altogether.
As Boggess recalls, “We were looking for a way to improve our processes around some of the more mundane and time-consuming Db2 maintenance tasks that our DBAs must do on a regular basis—tasks that, while mundane, are very important to the health, availability and performance of our Db2 subsystems and applications. Basically, we were looking for a solution that would give the administrators the ability to do these tasks in a more efficient and automated fashion.”
As it happened, the perfect tool to meet these goals and expectations “fell into my lap,” as Boggess describes it. He had seen other tools in the past at conferences he had attended that promised to address NCFBMIC’s LOAD/UNLOAD, DSN1COPY, REORG and RUNSTATS woes. But the solutions were either too costly, didn’t offer the flexibility the team was seeking, or the provider lacked the customer-support orientation Boggess felt he would need both during and after solution deployment.
Fortunately, Boggess and Mark Schora, head of Log-On Software operations in the Americas, had a relationship reaching back 15 years or more that they’d established simply by knowing each other as part of working the industry and, more significantly, having similar interest in Db2. As Boggess further remarks, “Mark called me one day and asked me what I thought about the TUC product. I told him that it sounded like exactly what we needed.”
Shortly after this exchange, NCFBMIC’s database team entered an agreement to implement TUC in phases. The first phase focused on automating and optimizing the timing of the execution of the REORG and RUNSTATS utilities. The second phase involved setting up an automated process for moving data from test to production using TUC’s ability to leverage the DSN1COPY utility to replace the use of the LOAD/UNLOAD utilities.
A Beneficial Solution
“We began the implementation in early 2021,” Boggess says, “and we very quickly automated REORG and RUNSTATS maintenance of most of our objects. Log-On then worked with us to accommodate the outliers. For the DSN1COPY implementation, our naming conventions were different across our different databases. Log-On worked with us to accommodate the solution’s capabilities to our specific needs such as that. The implementation officially wrapped up in May of this year.”
Boggess notes that the TUC implementation would have wrapped up much sooner had the team dedicated more of their time to the effort. But the slower, albeit more deliberate, deployment may have led to some unexpected but beneficial outcomes, including a one hundred percent ROI in the first year of deployment as calculated solely in team member time saved.
Indeed, once the Db2 team got TUC working the way they needed it to, days per month of routine maintenance work morphed into mere hours. And this is no small matter. Tasks that used to tie team members up for significant amounts of time have been replaced with a let-it-run automation mindset, allowing the team members to focus more of their work on high-value projects.
“TUC has given us more confidence in the health of our databases, including assurance that Db2 is going to pick the best access path available based on up-to-date statistics, and assurance that the organization of our Db2 objects is optimal, both resulting in better-performing, more efficient workloads,” Boggess says.
Other TUC-related benefits include:
- Knowing that REORGs are happening exactly when needed for each object and not early or prematurely, thus avoiding wasted CPU and suboptimal workload performance.
- The ability to calculate a quota of objects to be REORGed in the available window based on past REORG performance, with the objects in greatest need of REORG being prioritized.
- Releasing space previously wasted due to disorganized Db2 objects. For one job, for example, the DBAs were adding four to five volumes every three months and getting calls in the middle of the night due to job failures caused by a lack of space. Thanks to TUC-based REORG optimization and space recovery, job failures have ceased and the business has eliminated unintended outages.
- Having better, more up-to-date data in the test environment. TUC’s automation of DSN1COPY leverages parallel processing to help move the very largest objects within the available window.
- The ability to identify inconsistencies across database environments and generate the DDL required to correct any inconsistencies.
Every Step of the Way
Boggess and his team haven’t finished plumbing all the capabilities Log-On Software’s TUC has to offer; capabilities that include, for example, automated data backup and recovery. While they currently have jobs to handle backup and recovery, initiating them involves manual intervention.
Boggess points out, “TUC can automate backup processes when our data has changed. In terms of recovery, TUC can tell us how best to recover. For instance, if we need to recover a table, the tool will determine appropriate recovery points, ask which point we want to recover from, and assess the impact to related tables, determining for example, that if we’re going to recover Table A, we also need to recover Table B. We haven’t gone into depth on this and other TUC functionality but plan to.”
Boggess attributes the successful TUC deployment to both his team—including NCFBMIC database administrators Anil Kale and Jodi Underwood—and Log-On Software, remarking that everyone worked hand-in-hand to drive the implementation forward.
“Log-On Software was with us every step of the way, from implementation planning to rollout, to tailoring the solution to address our unique naming conventions. Their team was willing and able to work with any environment-specific issues or requests. They were very knowledgeable about the product, and delivered site-specific enhancements where needed. They were there when we needed them, as TUC is now for us, by automating what we needed to have automated. TUC does a fantastic job.”
Learn more:
Log-On Software’s Total Utility Control (TUC) for Db2 for z/OS
The North Carolina Farm Bureau Mutual Insurance Company