Thursday, April 3, 2008

DB2 SQL Stored Procedure Recursion Error

I was creating a DB2 SQL stored procedure and wanted to have it be able to test itself. So I had it accept a parameter "instr" - if this parameter was "TEST" then it would call itself with some different values checking to make sure the values were correct and then rollback everything before returning "PASS" or "FAIL". However I kept getting an error:

Create stored procedure returns SQLCODE: -440, SQLSTATE: 42884."

DB2ADMIN.PRG_NAME: 42: No authorized routine named "PROG_NAME" of type "PROCEDURE" having compatible arguments was found.

This one had me for awhile and I tried various ways to get around it...finally it struck me. The stored procedure is being dropped when I deploy. Then while compiling it checks to see if the procedure being called (itself) exists. But of course it doesn't and it's not smart enough to check if it itself is the needed procedure so it fails. So far I haven't found a way around this so I just made a new procedure with my tests in it...which is probably cleaner anyway.