Popping the Stored Procedure Cherry

A first experience with creating stored proceedures.

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.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 552

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.