I created my first Linux DB2 stored procedure through SQuirrel© (to a remote database) and the procedure is working. However, this was difficult and time-consuming because I couldn't see what progress was being made, in the procedure, until it failed altogether. I have a global exit handler for that.
I did research this and ended up with the following for seeing the progress of an executing stored procedure. But I still can't see the informational messages at runtime.
BEGIN
Declare Continue Handler for SQLSTATE '42704' Begin End; -- not found
Set v_MsgText = 'DROP LOC CHECK failed.';
Set v_SqlStmt = 'ALTER TABLE LIBRAT.APNVEND_RECORD_APNV_LOC_DATA DROP CONSTRAINT LIBRAT_APNVEND_RECORD_APNV_LOC_DATA_C1';
Execute Immediate v_SqlStmt;
Set v_MsgText = 'DROP LOC CHECK rc=' || SQLCODE;
Signal SQLSTATE '01000' Set Message_Text = v_MsgText;
...snip...
END
There is much more, of course, but the two statements after the EXECUTE IMMEDIATE are the ones I am using for diagnostic output without interrupting the procedure execution. Let me also note that on other platforms I would have used static SQL for the ALTER statement. But the Linux DB2 stored procedure compiler would not let me do that in this case. Is that expected?
Otherwise, where should I be seeing these types of informational messages? Or, what other technique is preferred to monitor stored procedure execution for diagnostic purposes?
Thanks
I converted over to using the UTL_FILE built-in module. I will write my diagnostic messages to a log file on the file system.
Thanks, Dave Clark