I tried using "Last SQL Statement" option in iSeries Navigator to get last SQL statement executed by job, but most of the times statements like "SET : H : H = : H : H" are shown as last SQL statement executed. I understand that these statements ("SET : H : H = : H : H") are from user defined functions (SQL UDF) in SQL. Is there a way to see SQL executed by job instead of code in UDF?
We are still on V5R4 and will be moving to 7.1 soon.
Thanks in advance..
Pinned topic Last SQL Statement for job
Re: Last SQL Statement for job2014-07-01T01:02:49ZThis is the accepted answer. This is the accepted answer.
Thanks for the reply. "Last SQL Statement" option in iSeries Navigator tells you which object the SQL is from, based on that information we know that the statement is from UDF.
We use memory-resident database monitor. However, this does not capture SQLs that were cancelled. I am looking for a way to find out SQL executed by job which is currently using high amount of resources on the system before cancelling the job. We can not use detailed database monitor as it generates too much output (more than 1 GB in a minute).
tomliotta 100000BBYW53 Posts
Re: Last SQL Statement for job2014-07-01T04:18:24ZThis is the accepted answer. This is the accepted answer.
- stiruvee 2700019NCE
By using the STRDBMON RUNTHLD() parameter, you might be able to bring the volume of output way down. And by upgrading to a current release, you could also use the STGTHLD() to get possibly even less output.
Since you're especially interested in high-resource statements, threshold limits might be enough to filter normal statements out of the monitor's output.
Re: Last SQL Statement for job2014-07-01T13:02:32ZThis is the accepted answer. This is the accepted answer.
Thanks for the reply. We tried this before but it does not work. Once main SQL meets RUNTHLD criteria, monitor will start logging every thing executed by job including code/SQL in UDF. We have report SQLs which process millions of records and call UDF for every record, this will quickly generate huge amount of monitor data. We tried with RUNTHLD 600 seconds, but monitor still logs code/SQL with in UDF which take micro seconds to execute for each call to UDF.
We wrote our own user exit to log SQLs. The amount of SQL log is large but still manageable (< 100 MB/hour). I am trying to see if there is better way instead of using user exits. It is frustrating to see that something as simple as finding out Last SQL/current SQL so poorly implemented in major commercial database.
Satya...Updated on 2014-07-01T13:43:22Z at 2014-07-01T13:43:22Z by stiruvee
krmilligan 120000MDWP450 Posts
Re: Last SQL Statement for job2014-07-01T15:55:53ZThis is the accepted answer. This is the accepted answer.
- stiruvee 2700019NCE
Another option might be to use the "active statements" filter on the SQL Plan Cache to identify resource hogging SQL statements. With 7.1 release, all SQL statements should reside in the SQL Plan Cache.
Depending on the complexity of the assignment statements within the UDF, you could try rewriting those so that they can be executed with generated C code instead of as SQL statements and won't be collected by the SQL monitor. Reference the Stored Procedure Performance white paper for more details at: http://ibm.com/systems/power/software/i/db2/docs/whitepapers.html
If that's not possible when you get to the 7.1 release, you could also look at using the SQL client registers to avoid having UDF statements collected by the monitor (http://iprodeveloper.com/database/auditing-and-tracking-strsql-usage). In my test, I added this line to the beginning of the UDF
CALL sysproc.wlm_set_client_info( NULL, NULL, NULL, NULL, 'MYFUNC');
And then had my application issue this call before running a query and then stared an SQL Monitor which only collected SQL statements with a program id client register value of MYSTMT.
CALL sysproc.wlm_set_client_info( NULL, NULL, NULL, NULL, 'MYSTMT');
Re: Last SQL Statement for job2014-07-02T01:07:52ZThis is the accepted answer. This is the accepted answer.
- krmilligan 120000MDWP
Thanks for the information. Will wait for 7.1 and see how it will work. Our user exits serve the purpose for now.