Picture this scene: It is a little after 5 p.m. on a Friday and a chat message pops up from my “favorite” application programmer. Something isn’t working properly. Yes, that is the message. “Something” isn’t working properly. That’s all.
“OK,” I say. “What are you trying to do—give me a bit more detail so I can help, and how long are you going to be around to work on fixing this?”
“Oh, I’m heading home now, but I thought maybe you could take a look at things” is the reply.
This is no exaggeration of the types of situations that routinely pop up. Sure, these may not be the exact words, but I’d wager that most DBAs have been on the receiving end of this type of request. So, what should be done in this type of situation?
Well, the first thing to notice is that surely this not an urgent problem. If it were urgent, then nobody is going to be leaving for the weekend, right? The second, and probably more important takeaway, is that a more formal manner of requesting DBA services is probably required. If the requester must fill out a service ticket with details of the problem before the DBA team can help, then the likelihood of getting more useful details increases.
Notice I said “likelihood” and not “certainty.” And that brings me to another fun programmer/DBA conversation. You see, this developer was having problems getting his program working. And in fairness to him, it was a complex scenario with multiple environments, multiple DBMSs, and several programs calling other programs.
But then he shared his “trace output” with me that he was using to help resolve his issue. This output was basically a bunch of DISPLAY statements in his program. Now don’t get me wrong, I have no problem with that type of testing and debugging; it can be quite useful to track down what is actually going on. But there it was, his “output trace” staring me in the face, looking something like this:
First record read from input file: key 00001
Call to program ABC001
Yes, the “helpful” output trace displayed the word error. Oh, yes, it was in all caps so it must be a severe error, right? So, I said, “Yup, looks like you’ve got an error there.” I mean, what more could I say, right?
The takeaway here is that the actual error code or message is needed. If it were a SQL problem, then display the SQLCODE. With more details like that, maybe the developer could resolve the issue without seeking DBA help.
Which brings me to another thing DBAs frequently hear from developers: “I keep getting this SQLCODE -xxx when I run my program. What is wrong?” I didn’t put in a specific code, but there are a lot of common ones (-805, -913, -104, -305, -904). I bet every DBA knows what these SQLCODE values mean right off the top of their heads.
Here’s the thing, though. SQLCODEs are easy to look up. Just type it into a search engine in your internet browser and there it is, big as life. There is absolutely no reason for an application programmer to not first look up the SQLCODE and read the description to understand what the problem is. And then to try to resolve it themselves before reaching out for help.
As a DBA, I am more than willing to help with a smile on my face when the programmer asks something like “I got a -904 and I know that means that what I am trying to access is not available. But I don’t know why. Here is the SQL statement that I was trying to run when I got that message.” So much better than “I’m getting a -904, what should I do?”
And that brings me to another useful tactic for DBAs. Create explanation/help documents for these common SQLCODEs that pop up again and again. And then when you get a request for help with that type of problem, just point the requester to the document. That can save a lot of DBA time and effort.
Then there is the performance problem that is presented in this way: “This query has run quickly every day since we created it and it’s been around for a couple years now. But when I run it today it runs for a long time and then times out. What did you do?”
Yes, what did the DBA do, not what changed with the query. Time for the Socratic method.
“Did you change anything about the query?”
“I don’t think so.”
“What about the data? Is there more of it?”
“Could be … wait a minute … we commented out one of the WHERE clauses, but the query is exactly this same other than that.”
“Which WHERE clause?”
“The one for product.”
“OK, so you are running the query for every product in the company?”
“Yes. Could that make the query run slower?”
“You mean accessing 6 million products instead of 1? Yes, that could slow things down.”
So please be kind when you are thinking about reaching out to your DBA for help. Try to resolve the issue yourself first. Review any documentation that the DBAs have made available to help you review and remediate problems on your own before you give up. Be sure to document everything you did and have that ready to share with the DBA when you reach out. And please provide meaningful error messages.
The goal is for developers and DBAs to operate as a team. The more you can do as a developer to minimize saying the same old things to the DBA the better. When you work to provide useful, meaningful information to help diagnose a problem, you will find out that you are resolving things on your own more often. And when you do involve the DBA, you’ll find that they are generally happier, and more able to quickly help.