Aspirations for Being a Lazy DBA? Maybe DevOps is the Answer!

By Julia Carter posted Oct 01, 2019 01:02 AM


Aspirations for Being a Lazy DBA? Maybe DevOps is the Answer!

Julia Carter

When I was much younger, even more naïve and less cynical than I am now, I was amazed by one of my colleagues who created some ISPF panels with some REXX and Cobol behind them to automate the process to accomplish one of our common DBA tasks. They told me that it’s only because they’re so lazy that they implemented it, which prompted a “huh?!” from me.

They explained how they loath having to repeat the same task over and over, and if they know they’re going to need to do something more than once or twice or tell more than one person the same thing, they’ll find a way to make it simpler and quicker. Genius.

This was the theme among many of my more experienced colleagues who all said they were lazy, but in reality, they were providing neat solutions for us and for our customers (developers, operations, change management and more) to make all of our lives easier when dealing with common tasks and requests related to Db2.

My REXX, Cobol and ISPF panel building skills are sketchy (and I’m being generous), so I was the one who wrote a few instructions on how to use the panels (although they always came with built in help, of course), and helped our team’s customers to start using them and see the benefits (anyone ever come across a grumpy DBA…?). We were all quietly smug about what we delivered, but we didn’t want to shout about it too much, because it was all about us facilitating our own laziness.

Now things are changing, there are less people writing REXX programs and ISPF panels (kudos to all those who still are), and possibly even fewer people who want to use them (kudos to those that do).

Away from the mainframe, DevOps is seeing rapid and agile developments take off with small iterative changes and improvements being made and delivered to applications on a frequent yet low-risk basis. The well-known stat about Amazon releasing code every 11.7 seconds still makes me screw my face up in disbelief, I order a lot from Amazon and it never seems to be broken!

Code changes are often packaged and deployed to their target servers automatically and within seconds or minutes. When the application changes result in Db2 structural changes, e.g. adding a column to a table, or completely new Db2 objects, the process can often slow down or even stop while forms are filled in, approvals from various parties are sought and received (or rejected) and then the DBAs get to work on preparing the changes, sometimes in a very manual way. SLAs for this piece of work will vary from company to company, but from the time that someone has established what the change needs to be, to getting it implemented is likely to be at least 1 day or even days in a development or test environment, even for what could be considered a very simple or insignificant change.

Some of the reasons that it could take so long are due a sometimes “special level” of bureaucracy (possibly resulting from a fear of the unknown i.e. the dreaded mainframe!) or archaic change processes, sometimes it could be that DBAs are worried about delivering too quickly and the developers therefore assuming that the tasks are always quick and easy, and expect the same turn around for a new column at the end of an empty table in development and a Page Size or DSSIZE change of a monster tablespace in the live environment. There are probably a hundred other reasons why the Db2 changes could take longer than people might want them to. To get around this particular issue, DBAs might decide to invest a little time in educating developers, and the other teams they need to interface with to highlight some of the steps and checks that need to happen for different types of changes to take place. This ties in nicely with the DevOps ethos of collaboration between teams and with this extra understanding, it can help to expedite changes as the developers have a better idea of what is expected of them in order to facilitate a faster rollout.

If we can piggyback the principals and infrastructure that are being used for the code deployments, then we can help to reduce the time, without increasing the risk, of deploying Db2 changes. By using our favourite object compare tool (or home-grown masterpiece), we can initiate the Db2 changes in a really controlled way:

  • The DDL can be syntax checked, automatically
  • Adherence to site DDL standards can be checked, automatically
  • A baseline and backup can be taken, automatically
  • The DDL can be stored in a source code management repository, you get the picture
  • All activity is logged, which management and the auditors will love
  • Security controls can be used to ensure that only the right people can review, approve and allow changes to be implemented
  • Scheduling of changes can be easily achieved and at the right time in the overall process

Setting this sort of thing up isn’t a five-minute job, but it’s not that difficult and companies that are doing it are enjoying seeing some of their more straightforward Db2 changes being implemented in minutes rather than hours or even days. Db2 11 and 12 (and 3rd party software) enhancements help make this even slicker e.g. drop column, faster utility execution, and more and more support for online schema changes.

The hardest part might be changing the culture. There might be a mismatch in the pace of DevOps adoption between developers and DBAs, and this might be tricky to overcome. The reality is that by automating manual tasks, you might need less people involved to achieve the same results. However, how many DBAs really want to be spending hours or days working on DDL changes, when there are so many more interesting and rewarding work items that they could be involved with? DBAs can become heroes when they can find and fix poorly performing SQL or code, or when they identify and close loopholes. By automating some of the more regular and straightforward tasks, DBA time is freed up to focus on these other areas which can directly contribute to big savings for their company or by preventing big losses (reputational and/or financial).

Having said all of that, and considering the fairly complex implementation of automated schema changes, where can us normal folk get started?

If we speak to our developers or the operations team, there might be little things that they would like to be able to do themselves (particularly in dev and test environments) without bothering us, but maybe because they don’t have Db2 or mainframe skills, or the privileges to carry out the tasks, they do always have to ask a DBA, or go without. Simple things that we could automate and have invoked from an automated deployment tool, or another sort of interface, are:

  • Image Copy a table or set of tables on demand
    • We might want to control the time and objects that this can be performed against which our automation can take care of
  • Run RUNSTATS on demand
    • Probably only in dev and test environments to avoid nasty surprises in Live
  • Split a mass/multi-row update or delete SQL statement into singleton statements with commits to avoid locking when it executes
  • Generate to-site-standard JCL for Db2 utilities based on some input parameters
  • Displaying utilities or object statuses
  • Test object cloning/generation
  • Bind or Rebind avoidance or automation

Anything you can imagine doing with Db2 could probably be added to that list. There could be plenty of people in our organisations who have the automated deployment tool skills and would be keen to help us marry up our mainframe batch jobs or commands with an interface where people can request a service, provide their inputs which get validated and then execute the task.

The old role of a DBA might be changing a little. Maybe there will be more service provisioning that DBAs will need to proactively manage. But this feels like a positive change and is likely to be in line with our company’s mission.

Not jumping on this bandwagon is quickly becoming not an option, it’s happening with or without us. But, it’s an exciting challenge. The opportunity to learn about very new and cool things, save our companies a good chunk of money, and free up some of our valuable time to focus on areas which really require our experience and expertise, like performance improvements, ensuring our backup and recovery strategies are solid, working to ensure better database design, optimising storage and reviewing and testing new features of Db2 and associated software that our companies can exploit.