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.