I have a third-party application that runs some automated processes on a schedule. I get to define the SQL statement that runs. In this particular case, it runs a procedure. I can run the procedure manually, and although it takes maybe 10 minutes to complete, it does complete perfectly every time. However, when the automated process runs it, I always get back a CLI0111E Numeric value out of range. SQLSTATE-22003 (i.e. as the only response from the third-party application, and then only after the fact when I check to see the results). But other procedures that run via the automated process do not fail this way.
I understand the meaning of the error; the problem is that I cannot identify which particular SQL statement in my procedure is generating it. And since I cannot reproduce it when running the procedure manually, I am kind of stuck.
Is there any monitor (this is DB2 9.7 FP9, so I have the Control Center, not the DataStudio) that I can configure beforehand and leave running the Control Center that will show me which SQL statement generated the error? I can be as specific as filtering to the specific user and the procedure, but it would need to generate a result that I could see later, since I cannot sit and watch for the 20 minutes this procedure can take--in the middle of the night.
Look at the EVENT MONITOR FOR STATEMENTS with an event-condition equal to the specific AUTH_ID, for example.
If you can modify the script running your SP, then it's better to try EVENT MONITOR FOR ACTIVITIES calling WLM_SET_CONN_ENV before (turning on the collection) and after (turning off it) the SP call.