Topic
  • 6 replies
  • Latest Post - ‏2014-07-02T01:07:52Z by stiruvee
stiruvee
stiruvee
29 Posts

Pinned topic Last SQL Statement for job

‏2014-06-26T07:38:07Z |

Hi,

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..

Satya...

  • krmilligan
    krmilligan
    450 Posts

    Re: Last SQL Statement for job

    ‏2014-06-30T17:07:55Z  

    That could also be from an SQL stored procedure or trigger.  Really the only other option would be to use an SQL Performance Monitor.

  • stiruvee
    stiruvee
    29 Posts

    Re: Last SQL Statement for job

    ‏2014-07-01T01:02:49Z  

     

    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
    tomliotta
    49 Posts

    Re: Last SQL Statement for job

    ‏2014-07-01T04:18:24Z  
    • stiruvee
    • ‏2014-07-01T01:02:49Z

     

    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).

    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.

    Tom

  • stiruvee
    stiruvee
    29 Posts

    Re: Last SQL Statement for job

    ‏2014-07-01T13:02:32Z  

    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
    krmilligan
    450 Posts

    Re: Last SQL Statement for job

    ‏2014-07-01T15:55:53Z  
    • stiruvee
    • ‏2014-07-01T13:02:32Z

    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...

    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');

     

     

     

     

  • stiruvee
    stiruvee
    29 Posts

    Re: Last SQL Statement for job

    ‏2014-07-02T01:07:52Z  

    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');

     

     

     

     

    Thanks for the information. Will wait for 7.1 and see how it will work. Our user exits serve the purpose for now.  

     

    Satya...