There’s a reason why database administration or engineering remains a separate specialty. There are a large number of rabbit holes you can go down. I’m well aware that there’s probably 40% of Db2 LUW alone that I don’t know, even though I’ve spent 20 years working with it, and am an internationally recognized expert. One of the things that many RDBMS vendors struggle with is outreach and interaction with developers.
Notification: I am a DBA, not a developer, so come at this topic with a different perspective.
Inspiration
Check out this excellent developer perspective: Ideas to improve the user and developer experiences of databases by Daniel Haig. I read this article today, and wanted to address some of the ideas and suggestions from a DBA perspective. I may wander into other territories as well.
Breaking the Article Down
Please go read the article and then come back. I don’t know which RDBMS(es) the author has worked with, so I’ll address things from my perspective of experience with Db2. There are four major sections and one concept that I’d like to address.
UI
IBM has always struggled with a decent UI for Db2, and the options offered (with the possible exception of some of the OPTIM stuff) has generally been directed at DBAs who understand the underlying structures and details mores so than at developers. Wishing for anything in the UI seems likely only to launch a whole new attempt at yet another UI. Perhaps separate purpose-built UIs for DBAs and developers would be a better idea. Even better would be a cross-RDBMS UI which can hide some of the detail and show developers the common concepts.
Insights
In this section, the author talks about getting feedback from the database about what actions are good or bad for playing nice in a multi-application database. The problem with fulfilling the request is the vast array of ways and activity levels of databases. It’s hard to say that action X is bad for cooperation with other apps because in database A (an analytics DB) a query or set of actions might be entirely appropriate, but in database B (an OLTP environment), the same query would be horrendous and bring production activity to a halt. The same goes for hardware sizing. The same database that someone’s thrown SSD and a half TB of memory at wouldn’t blink an eye at handling things, but if you’re trying to run that db on 2GB and ancient disk, it’s a problem.
Perhaps this is an area where some of the AIops work that IBM and other vendors are working on could help – identifying things that are out of the ordinary or problematic.
The author also mentions in this section identifying apps by more than the ID they are using. Many tools and queries do this for Db2 and can let you break down what servers requests are coming from – but there is a more fundamental problem with this section, and that is that ANYONE is using just one ID for all of their apps! For security reasons alone, each app should have a separate id, and use the least-privilege principle on that ID’s permissions. This makes it easy, if an app is compromised, to allow other apps to continue using the database. In the scenario described, if just one app is compromised, the entire db and all apps must be shut down and the password changed at the very least before the database can be used again. Separate IDs also has the happy side effect of making the isolation of different workloads easier.
Much of what the author is talking about in this section can be accomplished with the Workload Manager(WLM) in Db2. It can be used to re-prioritize heavy queries, one application over another, and a whole host of other things. It is, however, a bit complicated to use and configure, and requires a DBA to be involved with defining thresholds and actions. It also bears the risk of stopping important activity if configured improperly.
Migrations
In this section, the author is actually talking about schema changes, and I could not more whole-heartedly agree. Keeping an application online when changes are made to the schema is challenging. Database vendors seem to see this as an application problem, while applications see it as a database problem, and so for years it just fails to be properly addressed. A tool like Liquibase can help immensely, but if a database vendor would step in here with a solution, it could be a huge competitive advantage. Writing an application appropriately also goes a long way in this space. Many RDBMSes can handle schema changes quickly and easily and in an online fashion, but the application may be written in a way that depends rigidly on the database schema. There is no need for this. This is one reason why avoiding select * is a best practice, but it goes deeper than that.
In working with WebSphere Commerce, I once tried to change a unique index. I was taking the index and adding a covering column, so it made no functional difference in how things work. I named the index with exactly the same name. WCS would not even start properly. Which is ridiculous. Why would an app do a check on the definition of an index before running? I changed the index definition back, and everything was fine.
Reducing Deadlocks
I frankly don’t get how the author’s suggestions in this area would work. Each app and each section of each app has to access tables in different orders. Defining a general order at the database layer wouldn’t make much sense. I can see how one could do this simply by defining tables with this data and making applications use them, though. One of my pet peeves as a DBA is that developers are not taught more about transaction control. When dealing with a database, knowledge of isolation levels and commit boundaries should be understood at a very fundamental level.
Lambdas
The functionality defined in this section can generally be accomplished with stored procedures, functions, and triggers. Some of which can be done in languages other than SQL in Db2. Additionally with Db2 and others offering Rest interfaces these days, it seems like the functionality defined here already exists. If the event-driven things should happen outside of the database, then I would mostly argue that the database shouldn’t be doing that work, and it’s not appropriate to shoehorn it in.
Involve database experts more
While it’s certainly a biased opinion, I think that involving database experts more thoroughly at all steps along the way would be useful. Each development team that works with databases should have a database expert embedded that can help with platform-specific details and a database way of thinking about these things. This doesn’t have to be, and probably shouldn’t be a systems DBA, but a developer focused on database interaction and functionality whose primary job is to enable their colleagues to do database things the database way.
More Developer Features
I would offer my own set of what I see as developer-focused features that Db2 would be wise offer and get better on:
- Schema change management as suggested by the author. This is a pain point that many organizations are feeling.
- Availability on RDS. On any major cloud as a service. The more widely an RDBMS is available, the more it will be picked up and having free or low-cost and easy access at the small end helps adoption exponentially.
- Production-supported deployable containers for all major architectures and orchestrators. Limiting this to RedHad Open Shift is not doing Db2 any favors.
- Installation via package managers (yum, rpm, brew, etc)
- Current drivers in most languages that are mostly bug-free. Db2 hasn’t historically done well with this over the past decade, though there have been some moves in the right direction lately
- Better SQL error messaging. While administrative errors are quite clear, the SQL
Error after 'blah'doesn’t really give me much when ‘blah’ appears 23 times in a 12-line query, much less in a 200-line query. - Education. There is no current developer certification for Db2. Resources (tutorials, videos, classes on whatever educational platform) to learn basic database concepts that just happen to use Db2 would go a long way.
Summary
Overall, I love the topic and tone of this article. The developer is saying “This is what I need”, and IBM would do well to listen to voices like this.
Originally published on DataGeek Blog