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.
By Dawn May01/21/2019
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.
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.
Dawn May is an IBM i consultant. She owns Dawn May Consulting, LLC in the Greater Boston area. Dawn is a former IBM senior technical staff member.