Popping the Stored Procedure Cherry
I’ve been a DBA for 9 years now, and wrote my first stored procedure last week. Part of this is probably due to the fact that I was at IBM and was in the narrower ‘Physical DBA’ or ‘Systems DBA’ role for quite a while. The other part is that our dev team is a bit streched for resources, and so is sending off what work they can to us, and SQL is something that we can do.
I’m actually quite proud of it. Though it has to handle 17 different combinations of input parameters, it manages to use a case statement only to set the vairable with the SQL for each case, and uses only 4 statements outside of the case statement to handle the whole thing. I was also proud of intentionally preparing the statements with the values hard-coded. I know the whole parameter marker thing to reduce prep time, but the disadvantage of that is that the preparation cannot take advantage of distribution statistics, and given the nature of the statements, I think it will be more important to be able to use distribution statistics than to eliminate prep time.
I explained every statement too, for performance and added some indexes to help. It accesses both custom and base commerce tables, so several indexes were needed. We tend to find commerce’s indexing a good starting point, but frequently have to add custom indexes to support each implementation – with each implementation needing different sets of custom indexes.
I think that other RDBMS’s use stored procedures more than DB2. Or maybe I’ve just been in an odd ‘stored procedure-less’ pocket of the world. The same lack of experience was true of my 3 colleages, though one of them does come from a very similar background to myself. I heard a colleage who works with SQL server talking about doing a “reindex” with a stored procedure, and honnestly it never occured to me to use a stored procedure for such maintenance. I’m not sure what (if any) advantages it would have over the perl scripts that I currently use for database maintenance that run on the database server anyway. Come to think of it, I don’t even know if runstats and reorg statements are allowed in a stored procedure. I’m also not sure how some of the complicated logic I use for determining what kind of reorg to do and for error checking and handling would work in pure SQL.
I actually had to spend a bit of time figuring out basic things like whether NULLs could be passed as input values, how to return multi-row results from a stored procedure and so on. I’m a bit concerned that I did not close the cursor, but the only way I could figure to return the results was to leave it open, and as I understand it, it will be closed on the next commit by the calling application. I still hate not closing it – feels like every cursor should be closed.