The Toad's Riddle
A real-life DB2 puzzler
A toad sat on a lily pad in the middle of a pond, waiting for a DBA to wander by. It didn't take long for one to arrive, and the toad wasted no time in launching into his riddle.
"Would you say you are a wise DBA?"
"I'm not sure," said the DBA, "but my users seem to think I'm fairly wise."
"Well, then. Let's say that one of them comes to you with a most perplexing problem. She wants to extract the DDL for an entire schema, but encounters an SQL1092N permissions error shortly after beginning the report."
"That's easy," replied the DBA. "I'd just ask her to tell me which SQL statement failed."
"If you had that answer, then this wouldn't be much of a riddle now, would it?", the toad asked rhetorically. "We all know that those programs never reveal anything meaningful in their error messages," the toad added. Sadly, the DBA knew the toad to be correct on that point.
The DBA was a bit annoyed by the toad's coyness, but continued anyway, unable to pass up a challenge. "I'd analyze the authorization views in the system catalog to see which permissions she's missing. Then I'd grant her whatever permissions she needed and send her on her way." The DBA was rather confident with his answer.
The toad's initial response was a little noise that must have been his version of a giggle. The DBA had never heard such a noise before, but he could tell it was condescending. "You find nothing in the authorization views that would explain the failure," said the toad. "Even if you granted permissions to everything in sight, the report would still fail in the same place. All you ever see of it is Unable to read tablespace parts". The toad waited for the next volley from the DBA.
"Then I'd grant DBADM to her user just to see if the problem went away", replied the DBA. He then wondered to himself if the toad in front of him was the type that chefs chopped up and served in fancy restaurants.
"DBADM will not help you," the toad said mockingly. "Nor will it help your user succeed in running her mysterious report."
The DBA was clearly irritated now. "That's impossible! Her report couldn't be anything other than a few SELECT statements. DBADM should give her access to everything there is."
"You're half right," taunted the toad. "I'll admit that her report only contains SELECT statements, but it still fails nonetheless. Just to be nice though, I'll give you a little hint: when you run her report as a user with SYSADM authority, it works just fine."
"That's not much help," said the DBA. "I can't go around giving SYSADM to everyone just so they can run a couple of their reports." He was perturbed enough to taunt back at the toad. "You know, children in Australia use very sharp sticks to hunt toads, and the adults even pay them to do it..."
"That may be true, but we're not in Australia and I'm not that kind of toad." The toad was unflappable as he went in for the final blow. "Are you ready to admit defeat and fire up your precious event monitor?"
"Might as well," said the DBA. "It sure beats going around in circles with this silly puzzle."
"You're probably right," said the toad. "Especially since you look too tired to carry on. You lose. Thanks for playing."
The DBA, his pride wounded, would have made a move to grab the toad, but the lily pad was in the middle of a murky pond that hardly looked inviting. As the toad hopped victoriously off of the lily pad and vanished into the thicket near the far side of the pond, the DBA wondered how he could have solved the problem.
"What exactly can SYSADM do that DBADM can't do, and why would a read-only report require it?"
If you've stayed with me all the way up to this point in the story, then you definitely deserve the answer, which - believe it or not - is SYSMON. The event monitor I ultimately ran (as a last resort) revealed attempts by Toad For DB2's schema compare routine to call SYSPROC.SNAP_GET_TBSP(). Not even DBADM authority allows a user to run the snapshot UDFs, so GRANTs are not going to help you with this particular problem. As tools like Toad become more widely used in your shop, you may find the need to extend SYSMON authority to more users. If you don't have SYSMON_GROUP set on your DB2 instances already, set it now while you're thinking about it, and then it will be enabled after the next instance restart.
Thanks for reading my blog, and Happy New Year.
...and thanks to Mark for the excellent toad photo.