Retrieving the Current SQL Statement for a Job

IBM i makes it relatively easy to identify and review jobs that may be using a lot of CPU or driving a lot of I/O

I often get asked how to determine what’s running in a prestart job that is taking a lot of system resources. When I ask for more details, the answer often starts with “We have these QZDASOINIT jobs…” Another common scenario I hear is “We have users that run ad-hoc queries…”

You can use various interfaces, such as WRKACTJOB, Active Jobs / Server Jobs in Navigator, or System Monitors to monitor your system in real time. IBM i makes it relatively easy to identify and review jobs that may be using a lot of CPU or driving a lot of I/O. If you identify a job you think is using too many system resources, you may want to investigate further.

When you determine you have a database server job—such as QZDASOINIT, QSQSRVR and QRWTSRVR—using a lot of system resources, quite often, it will be some sort of query running in those jobs.

It’s easy to review the call stack or look at the job log to get an idea of what the job is doing.  For jobs running an SQL statement, you may want to see that SQL statement to understand why that query was expensive. 

IBM i provides several interfaces to retrieve the current SQL statement for a job.  Defining the current SQL statement is tricky since it could be the last SQL statement that ran, or it could be an SQL statement that is currently running. If a job never ran a SQL statement, a null value is returned. This support works for any job that runs an SQL statement, but is particularly interesting for the database prestart server jobs.

How you do this depends upon your release, if you are current on PTFs and your personal preferences for accessing system information.  

QSYS2.ACTIVE_JOB_INFO()

This service was enhanced in September of 2018 to return detailed data, which among other things, includes SQL information. The DETAILED_INFO parameter is required to access this additional information. Note that this enhancement applies to 7.2 or later.

Below is a simple example that returns the current SQL statement for all database server jobs, using the generic name QZDAS* so both QZDASOINIT and QZDASSINIT (secure server) jobs are included. The SQL select statement also includes some basic performance statistics for the jobs that have a current SQL statement. The results are ordered by the jobs using the most elapsed CPU time.

Note that you should reset the statistics to get accurate elapsed time information. The very first time you run this service, an implicit reset takes the statistics to 0. I recommend waiting a few moments to run the select statement again in order to update elapsed time values.

SELECT JOB_NAME, AUTHORIZATION_NAME, CLIENT_IP_ADDRESS, ELAPSED_CPU_TIME, ELAPSED_PAGE_FAULT_COUNT, ELAPSED_TOTAL_DISK_IO_COUNT, SQL_STATEMENT_TEXT

FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER =>'QZDAS*', DETAILED_INFO =>'ALL'))

WHERE SQL_STATEMENT_TEXT <>' '

ORDER BY ELAPSED_CPU_TIME DESC;

There are many fields of information you can access using this service; you can create an SQL select statement to return precisely what you want to review, organized in the way you want, for the jobs you are interested in.

QSYS2.GET_JOB_INFO()

The QSYS2.GET_JOB_INFO() service has been around for quite some time. It allows you to retrieve information for a single job and returns quite a bit of information about the SQL used in that job. When you know the job name, this is a very easy way to get at the SQL statement for that job.  You can combine the QSYS2.ACTIVE_JOB_INFO() service with the QSYS.GET_JOB_INFO() service to get at the SQL statement for a group of jobs. Many of the ACS examples use this technique.  

You need to use this service if you do not yet have the September 2018 Database Group PTF installed. If you do have the September update, you will find that QSYS2.ACTIVE_JOB_INFO() is an easier interface to get at this information for a group of jobs. 

IBM i Access Client Solutions (ACS) has several example SQL statements to review your queries. In addition, the documentation on the services also has examples.

Retrieve Job Information (QUSRJOBI) API

The Retrieve Job Information API, format JOBI0900 returns the SQL Information for an active job. There’s a great deal of SQL information you can get at with this API; the current SQL statement is just one of the fields.

Access Client Solutions has the SQL Performance Center where you can use the “Show Statements…” and “SQL Details for Jobs” features to review SQL statements that have run. I mention this only for completeness; I’ll write more on this topic in a future blog.