IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 16 replies
  • Latest Post - ‏2019-02-25T07:08:17Z by stiruvee
robberendt
robberendt
84 Posts

Pinned topic How to query SQL statements used?

‏2018-11-06T15:26:50Z |

Is there some way to query SQL statements used?  Sample use:  You look through DISPLAY_JOURNAL and find that a particular table was updated outside of your normal application.  It was done by job QZDASOINIT servicing user BUBBA.  We know there's a tool from our ERP vendor to allow them to do mass uploads of GL entries.  We want to know if they've ran anything else.  I'm thinking this is stored in performance data or some such place.

  • robberendt
    robberendt
    84 Posts

    Re: How to query SQL statements used?

    ‏2018-11-06T16:43:27Z  

    One way:

    cl:  STRDBMON OUTFILE(ROB/ROBDBMON) JOB(*ALL) FTRFILE((ERPLXF/*ALL));
    select *
    from erplxf.rco;
    INSERT INTO erplxf.rco(cmpny)
        VALUES (55);
    update erplxf.rco
    set cmpny=66 where cmpny=55;
    select * from erplxf.rco where cmpny=66;
    delete from erplxf.rco where cmpny=66;
    commit;
    select * from rob.robdbmon;
    select
        qqjob as JobName,
        qquser as JobUser,
        qqjnum as JobNumber,
        qqstim as StartTime,
        qqc183 as IPAddress,
        qq1000 as SqlStatement1,
        qvc102 as CurrentUser1,
        qvc1284 as CurrentUser2,
        qvc3001 as SoftwareTitle,
        qvc3002 as CurrentUser3,
        qvc3003 as PCName,
        qvc3005 as PCOSVersion,
        qvc3006 as PCProgramandDirectory,
        qqsmint5 as sqlstatementif1,
        qq1000L as SqlStatement2,
        qqclob2 as SchemaList,
        dbmon.*
    from rob.robdbmon dbmon
    where qqsmint5=1
    order by StartTime asc
    ;
    cl:  ENDDBMON JOB(*ALL);
    SQLSTATEMENT1
    select * from erplxf.rco
    update erplxf.rco set cmpny=? where cmpny=?
    select * from erplxf.rco where cmpny=?
    delete from erplxf.rco where cmpny=?
    select pord, pline, pqrec from ERPLXF.hpol01 hpo where pqrec>? and pltdt>=? order by Pord, pline
    select * from erplxf.rco
    update erplxf.rco set cmpny=? where cmpny=?
    select * from erplxf.rco where cmpny=?
    delete from erplxf.rco where cmpny=?
    select pord, pline, pqrec  from ERPLXF.hpol01 hpo where pqrec>? and pltdt>=? order by Pord, pline
    Ok, I would rather see the actual values instead of substitution flags.
  • FPLAZAVI
    FPLAZAVI
    20 Posts

    Re: How to query SQL statements used?

    ‏2018-11-06T16:44:07Z  

    Hi Rob,

     

    Show Statements from SQL Perf center?? (sure no, because you knows this ...)

     

  • robberendt
    robberendt
    84 Posts

    Re: How to query SQL statements used?

    ‏2018-11-06T17:00:04Z  

    Another method:

     

    CALL QSYS2.DUMP_PLAN_CACHE('ROB','CACHEDUMP');

     

    SELECT *
    FROM ROB.CACHEDUMP
    WHERE QQTIME > CURRENT TIMESTAMP - 1 HOUR
    AND QQJOB='QZDASOINIT' AND QQUSER='QUSER' AND QQJNUM=352046;
  • robberendt
    robberendt
    84 Posts

    Re: How to query SQL statements used?

    ‏2018-11-06T17:01:07Z  
    • FPLAZAVI
    • ‏2018-11-06T16:44:07Z

    Hi Rob,

     

    Show Statements from SQL Perf center?? (sure no, because you knows this ...)

     

    I was kind of looking for something programmable.

  • stiruvee
    stiruvee
    48 Posts

    Re: How to query SQL statements used?

    ‏2019-01-10T13:46:20Z  

    Another method:

     

    CALL QSYS2.DUMP_PLAN_CACHE('ROB','CACHEDUMP');

     

    SELECT *
    FROM ROB.CACHEDUMP
    WHERE QQTIME > CURRENT TIMESTAMP - 1 HOUR
    AND QQJOB='QZDASOINIT' AND QQUSER='QUSER' AND QQJNUM=352046;
    Hi Rob,
     
    Interesting use of plan cache. Are there any enhancements to plan cache in 7.3 to capture more information to make your solution work?  What happens when multiple QZDASOINIT jobs execute the same SQL statement during last one hour? What happens when QZDASOINIT job used by ERP application is reassigned to another application process in last one hour? Is it possible to query SQL statements used by specific ERP application process when multiple jobs execute same SQL?
     
    I tried your solution on our system (IBM i version 7.1) and it did not work. I ran frequently used SQL from new session (iSeries Navigator - Run SQL Scripts) and tried to extract SQL statements for new session from plan cache. Plan cache dump showed no records for new session. All the records corresponding to SQL were for jobs that ran several hours ago. However, when I executed unique SQL statement that is not used by application from new session, plan cache showed record for new session.
  • robberendt
    robberendt
    84 Posts

    Re: How to query SQL statements used?

    ‏2019-01-10T14:07:01Z  
    • stiruvee
    • ‏2019-01-10T13:46:20Z
    Hi Rob,
     
    Interesting use of plan cache. Are there any enhancements to plan cache in 7.3 to capture more information to make your solution work?  What happens when multiple QZDASOINIT jobs execute the same SQL statement during last one hour? What happens when QZDASOINIT job used by ERP application is reassigned to another application process in last one hour? Is it possible to query SQL statements used by specific ERP application process when multiple jobs execute same SQL?
     
    I tried your solution on our system (IBM i version 7.1) and it did not work. I ran frequently used SQL from new session (iSeries Navigator - Run SQL Scripts) and tried to extract SQL statements for new session from plan cache. Plan cache dump showed no records for new session. All the records corresponding to SQL were for jobs that ran several hours ago. However, when I executed unique SQL statement that is not used by application from new session, plan cache showed record for new session.

    We were looking to see if people were querying a particular file over a longer time span.  If you have to have more detail and granularity you might want to consider triggers (including read triggers), journaling and other methods.

  • stiruvee
    stiruvee
    48 Posts

    Re: How to query SQL statements used?

    ‏2019-01-10T17:00:10Z  

    We were looking to see if people were querying a particular file over a longer time span.  If you have to have more detail and granularity you might want to consider triggers (including read triggers), journaling and other methods.

    My use case is same as use case mentioned in your first post in the thread. I am not looking for more detail/granularity. I am trying to find out SQL statements executed by ERP batch process running on Linux server and accessing IBM i using JDBC. As explained in my previous post, plan cache (IBM i 7.1) is not useful to find out SQL statements from particular QZDASOINIT job. I was surprised when I read your post about using plan cache to query SQL statements used by particular ERP process. Are you sure your method of using plan cache (IBM i 7.3) to query SQL statements from ERP tool works?

  • robberendt
    robberendt
    84 Posts

    Re: How to query SQL statements used?

    ‏2019-01-10T17:07:59Z  
    • stiruvee
    • ‏2019-01-10T17:00:10Z

    My use case is same as use case mentioned in your first post in the thread. I am not looking for more detail/granularity. I am trying to find out SQL statements executed by ERP batch process running on Linux server and accessing IBM i using JDBC. As explained in my previous post, plan cache (IBM i 7.1) is not useful to find out SQL statements from particular QZDASOINIT job. I was surprised when I read your post about using plan cache to query SQL statements used by particular ERP process. Are you sure your method of using plan cache (IBM i 7.3) to query SQL statements from ERP tool works?

    Yes I ran these and it found the statements.  Now if multiple QZDASOINIT jobs ran the exact same statements and if they would both appear in the cache I am not sure.

    I really did run these statements just now and got results

     

    Select * from erplxf.rco;

     

    CALL QSYS2.DUMP_PLAN_CACHE('ROB','CACHEDUMP');

     

    VALUES(QSYS2.JOB_NAME);

     

    SELECT *
    FROM ROB.CACHEDUMP
    WHERE QQTIME > CURRENT TIMESTAMP - 1 HOUR
    AND QQJOB='QZDASOINIT' AND QQUSER='QUSER';
  • robberendt
    robberendt
    84 Posts

    Re: How to query SQL statements used?

    ‏2019-01-10T17:16:46Z  

    Yes I ran these and it found the statements.  Now if multiple QZDASOINIT jobs ran the exact same statements and if they would both appear in the cache I am not sure.

    I really did run these statements just now and got results

     

    Select * from erplxf.rco;

     

    CALL QSYS2.DUMP_PLAN_CACHE('ROB','CACHEDUMP');

     

    VALUES(QSYS2.JOB_NAME);

     

    SELECT *
    FROM ROB.CACHEDUMP
    WHERE QQTIME > CURRENT TIMESTAMP - 1 HOUR
    AND QQJOB='QZDASOINIT' AND QQUSER='QUSER';

    A little more detail

    select qqtime, qqjob, qquser, qqjnum,
    qq1000 as StatementsEtc,
    qq1000L as MoreStatementsEtc,
    qqblob1
    FROM ROB.CACHEDUMP
    WHERE QQTIME > CURRENT TIMESTAMP - 5 HOUR
    AND QQJOB='QZDASOINIT' AND QQUSER='QUSER';

  • stiruvee
    stiruvee
    48 Posts

    Re: How to query SQL statements used?

    ‏2019-01-11T09:46:01Z  

    Yes I ran these and it found the statements.  Now if multiple QZDASOINIT jobs ran the exact same statements and if they would both appear in the cache I am not sure.

    I really did run these statements just now and got results

     

    Select * from erplxf.rco;

     

    CALL QSYS2.DUMP_PLAN_CACHE('ROB','CACHEDUMP');

     

    VALUES(QSYS2.JOB_NAME);

     

    SELECT *
    FROM ROB.CACHEDUMP
    WHERE QQTIME > CURRENT TIMESTAMP - 1 HOUR
    AND QQJOB='QZDASOINIT' AND QQUSER='QUSER';

    < Now if multiple QZDASOINIT jobs ran the exact same statements and if they would both appear in the cache I am not sure. >

     

    This is the main problem with method using plan cache to query SQL statements for specific job. ERP tool might have executed other SQL statements in addition to SQL statements recorded by plan cache. Some of the SQL statements executed by ERP tool might have been recorded against other jobs. You can verify this by executing same SQL statement from two 'Run SQL Scripts' windows and extracting SQL statements for both jobs from plan cache.

     

    Also, some of the SQL statements executed by ERP tool might be processed by CQE engine and will not be recorded in plan cache. Try executing following SQL statements using 'Run SQL Scripts' and check how many of them are recorded in plan cache.  

     

    create table qtemp.test1(i1 integer) ;
    insert into qtemp.test1 values (1) ;
    rollback ;
    drop table qtemp.test1 ;

  • Obelix-it
    Obelix-it
    1 Post

    Re: How to query SQL statements used?

    ‏2019-02-15T11:12:15Z  

    FWIC, you can monitor the ODBC request using ODBC exit point.

    Adding a program to the appropriate Exit Point you may trace anything they do via ODBC and keep track of it.

  • stiruvee
    stiruvee
    48 Posts

    Re: How to query SQL statements used?

    ‏2019-02-18T02:20:51Z  
    • Obelix-it
    • ‏2019-02-15T11:12:15Z

    FWIC, you can monitor the ODBC request using ODBC exit point.

    Adding a program to the appropriate Exit Point you may trace anything they do via ODBC and keep track of it.


    We started using user exits to monitor SQL when we were using V5R4. We found out that default set of tools provided by IBM are not suitable for 24x7 SQL monitoring. IBM team member claimed that plan cache records all SQL statements in 7.1. However, we found out that his statement is not accurate after upgrading to 7.1.

    As per 7.3 documentation INSERT statements (INSERT...VALUES..) are still processed by CQE engine and not recorded in plan cache.  I am still curious how Rob Berendt managed to use plan cache (7.3) for monitoring SQL statements from ERP upload tool. Either ERP upload tool is not using INSERT statements or IBM web site is not updated with latest information.

     

    Link to IBM documentation:

    https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzajq/rzajqdispatcher.htm

  • robberendt
    robberendt
    84 Posts

    Re: How to query SQL statements used?

    ‏2019-02-22T14:34:10Z  
    • stiruvee
    • ‏2019-02-18T02:20:51Z


    We started using user exits to monitor SQL when we were using V5R4. We found out that default set of tools provided by IBM are not suitable for 24x7 SQL monitoring. IBM team member claimed that plan cache records all SQL statements in 7.1. However, we found out that his statement is not accurate after upgrading to 7.1.

    As per 7.3 documentation INSERT statements (INSERT...VALUES..) are still processed by CQE engine and not recorded in plan cache.  I am still curious how Rob Berendt managed to use plan cache (7.3) for monitoring SQL statements from ERP upload tool. Either ERP upload tool is not using INSERT statements or IBM web site is not updated with latest information.

     

    Link to IBM documentation:

    https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzajq/rzajqdispatcher.htm

    I didn't say using plan cache was fool proof.  However some people want to know how to look at something after the fact, or do not have the budget for exit point tools.

    We once had exit point tools.  Truth is, we never got around to implementing them and, after awhile, got tired of paying maintenance for something we weren't using.

  • stiruvee
    stiruvee
    48 Posts

    Re: How to query SQL statements used?

    ‏2019-02-23T13:51:45Z  

    I didn't say using plan cache was fool proof.  However some people want to know how to look at something after the fact, or do not have the budget for exit point tools.

    We once had exit point tools.  Truth is, we never got around to implementing them and, after awhile, got tired of paying maintenance for something we weren't using.

    I understand that every software tool/solution has limitations. However, there is a big difference between "Yes, it works" and "sometimes/rarely works". We have process similar to what you mentioned in your first post in the thread. Bulk order upload batch process reads xml files uploaded by users and inserts order records into database using JDBC. SQL statements used by bulk order process are same as SQL statements used by online order entry web application. If I use plan cache to capture SQL statements used by bulk order batch job, it will show 0 SQL statements. SELECT statements used for validations will also not show up. All SQL statements used by batch process will be already in plan cache due to SQL statements from online  process. If you still consider plan cache as a viable tool for use case mentioned in the first post, let us agree to disagree.

     

  • B.Hauser
    B.Hauser
    320 Posts

    Re: How to query SQL statements used?

    ‏2019-02-24T10:41:20Z  
    • stiruvee
    • ‏2019-02-23T13:51:45Z

    I understand that every software tool/solution has limitations. However, there is a big difference between "Yes, it works" and "sometimes/rarely works". We have process similar to what you mentioned in your first post in the thread. Bulk order upload batch process reads xml files uploaded by users and inserts order records into database using JDBC. SQL statements used by bulk order process are same as SQL statements used by online order entry web application. If I use plan cache to capture SQL statements used by bulk order batch job, it will show 0 SQL statements. SELECT statements used for validations will also not show up. All SQL statements used by batch process will be already in plan cache due to SQL statements from online  process. If you still consider plan cache as a viable tool for use case mentioned in the first post, let us agree to disagree.

     

    Why not open a PMR at IBM?

    Birgitta

  • stiruvee
    stiruvee
    48 Posts

    Re: How to query SQL statements used?

    ‏2019-02-25T07:08:17Z  
    • B.Hauser
    • ‏2019-02-24T10:41:20Z

    Why not open a PMR at IBM?

    Birgitta

    * First, Plan Cache is working as expected (except SQL statements processed by CQE). The way IBM is working, it might take another decade for IBM to remove CQE completely. Plan Cache is not suitable tool for monitoring SQL from specific database connection (QZDASOINIT job). Same thing holds true for other databases like DB2 LUW, Oracle,etc. If Plan Cache is working as specified by Rob then it will be deviation from normal.

     

    Rob is trying to present "Plan Cache" as viable solution when it is not. I am not talking about "fool proof solution". It can not handle every day scenarios and does not capture something basic as INSERT statement.

     

    * Second, as far as SQL monitoring is considered, we already have workaround in place. I wrote user exits to monitor SQL from remote JDBC clients. Took less than one week for complete setup. We are not paying any maintenance fee to third party vendors. If you are wondering why discuss issue that was resolved, I am checking whether Rob's solution is viable and if it is viable then remove user exits once we upgrade to 7.3.

     

    * Third, I do not have access to IBM account that can open PMR for IBM i. Company IBM account for IBM i is managed by "AS400 Administrator".