Wednesday, November 6, 2024

Learning about active queries

Have you ever wanted to know what is going on within an active SQL Query Engine, SQE, query?

If, like me, you do there is a table function that shows all kinds of interesting information.

The table function ACTIVE_QUERY_INFO has been around since IBM i 7.3 . It has four parameters that allow you to select the results you want returned.

  1. JOB_NAME:  This is the parameter I have found the most useful. Rather than it using the qualified job name it uses the job name part of the job name. Therefore I can just search for jobs with wildcards, for example 'DSP1*'. I can also use '*' to return results for the current job only, and '*ALL', or blank, to return the results for all active jobs.
  2. JOB_USER:  The user name from the job name. A wildcard can be used here too.
  3. JOB_NUMBER:  The job number part of the job name. '*ALL' can be used here.
  4. USER_NAME:  The user name for the job, which may not be the same as the job user name.

I always recommend that you run any Table function to return all the available columns, then you can make your own decision as to which one are important to you. In this example I am returning all of the columns for the current job:

  SELECT * FROM TABLE (QSYS2.ACTIVE_QUERY_INFO(JOB_NAME => '*'))

I am not going to show the results of the above statement, which is why I recommend you run this for yourself.

Here is the statement to retrieve only some of the columns for my current (this) job:

01  SELECT QUALIFIED_JOB_NAME,
02         JOB_NAME,
03         JOB_USER,
04         JOB_NUMBER,
05         USER_NAME,
06         QUERY_TYPE,
07         QRO_HASH,
08         PLAN_IDENTIFIER
09  FROM TABLE (QSYS2.ACTIVE_QUERY_INFO(JOB_NAME => '*'))

Lines 1 – 8: I feel I don't have to describe what data these columns contain as their names are very descriptive.

Line 9: With the Job name of '*' only the results from the current job are returned.

Two rows are returned for my job:

                                   JOB_  JOB_   USER_ QUERY  QRO_     PLAN_
QUALIFIED_JOB_NAME      JOB_NAME   USER  NUMBER NAME  _TYPE  HASH     IDENTIFIER
----------------------- ---------- ----- ------ ----- ------ -------- ----------
400534/QUSER/QZDASSINIT QZDASSINIT QUSER 400534 SIMON SQL    6003575D     138134
400534/QUSER/QZDASSINIT QZDASSINIT QUSER 400534 SIMON SQL    2947B1F1     137782

The value of 'SQL' in the Query Type column identifies that this is SQL query. Which is true as I ran this in ACS's Run SQL Scripts.

The QRO Hash and the Plan Identified allow you to uniquely identify this query when you look at plan caches.

Next up I want to retrieve the five SQE queries that have been active for the longest time in this partition. My Select statement is:

01  SELECT FULL_OPEN_TIMESTAMP,
02         QUALIFIED_JOB_NAME,
03         QUERY_TYPE,
04         LIBRARY_NAME,
05         FILE_NAME
06    FROM TABLE (QSYS2.ACTIVE_QUERY_INFO(JOB_NAME => '*ALL'))
07   ORDER BY FULL_OPEN_TIMESTAMP
08   LIMIT 5

Line 1 – 5: The columns I want to see.

Line 6: I want the results for all jobs.

Line 7: Sort by opening timestamp.

Line 8: Only return five results.

                                                      QUERY   LIBRARY
FULL_OPEN_TIMESTAMP         QUALIFIED_JOB_NAME        _TYPE   _NAME     FILE_NAME
--------------------------  ------------------------  ------  --------  ---------
2024-09-29 01:15:30.214961  151034/QSYS/QDBSRV04      SQL     QSYS2     QSQPTABL
2024-09-29 01:16:38.487756  151034/QSYS/QDBSRV04      SQL     QSYS2     SYSIXADV
2024-10-13 00:53:48.181224  378897/QUSER/QSQSRVR      SQL     QSYS2     SYSTABLES
2024-10-15 08:53:28.176737  410557/C*******/JOBNAME1  NATIVE  PRODLIB   FILE_1
2024-10-15 08:54:16.225117  410562/C*******/JOBNAME2  NATIVE  PRODLIB   FILE_2

What do the results show?

The Query Type of 'NATIVE' is for a "native" query. The file listed in the File Name column is the name of the first file referenced by the query.

Finally if I want to show all of my jobs, all use the user profile 'SIMON', I can use the User Name parameter:

01  SELECT QUALIFIED_JOB_NAME,USER_NAME,QUERY_TYPE
02    FROM TABLE (QSYS2.ACTIVE_QUERY_INFO(USER_NAME => 'SIMON'))

Line 2: I want all SQE query jobs that have the user name of 'SIMON', as I said above, this might not be the user name in the job name.

                          USER    QUERY
QUALIFIED_JOB_NAME        _NAME   _TYPE
------------------------  -----  ------
413117/QUSER/QZDASSINIT   SIMON  SQL
412229/QUSER/QZDASSINIT   SIMON  SQL
412229/QUSER/QZDASSINIT   SIMON  SQL
409200/SIMON/DSP01        SIMON  SQL

The results show that I have four jobs. Three do not have my user name in the job name.

There are other columns returned by the Table function, check them out and decide what is best for how you will use this data.

 

You can learn more about the ACTIVE_QUERY_INFO command from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.