QSQSRVR Job Considerations
The QSQSRVR prestart server jobs run in the QSYSWRK subsystem by default, but you can configure the subsystem for these jobs.
By Dawn May02/20/2019
The QSQSRVR jobs handle database requests for jobs with SQL Server Mode enabled. With SQL Server Mode, the SQL statements are not run directly in the requesting job, but rather by a QSQSRVR prestart server job. SQL Server Mode is automatically enabled when using the Native JDBC driver that is part of the IBM Developer Kit for Java (5770-JV1), and it can also be enabled for Db2 call level interface (CLI) requests.
IBM i has the ability to link the SQL Server Mode job to the requesting job to help you understand where the SQL statement originated. The QSQSRVR prestart server jobs run in the QSYSWRK subsystem by default, but you can configure the subsystem for these jobs. I wrote about these topics before, but this blog pulls these considerations together in a single article.
SQL Server Mode Jobs and Requesting Jobs
IBM i provides the ability to associate the QSQSRVR job with the job that initiated the SQL statement. This association makes it possible to understand the relationship between the jobs in two ways:
- You may want to review active QSQSRVR jobs, the system resources that are being consumed, and identify the jobs associated with those QSQSRVR jobs.
- You may want to identify which QSQSRVR job is handling work for a specific job.
The QSYS2.ACTIVE_JOB_INFO can be used to get information about the desired QSQSRVR jobs. Then, for each active QSQSRVR job, use the QSYS2.JOBLOG_INFO service to retrieve the CPF9898 message, which identifies the requesting job for the SQL Server Mode request. I also reviewed this example in the Prestart Job Messages article.
IBM i Access Client Solutions provides an example:
-- category: IBM i Services
-- description: Work Management - Active Job info - SQL Server Mode study
-- Find active QSQSRVR jobs and the owning application job
WITHtt(authorization_name, job_name, cpu_time, total_disk_io_count)
AS (SELECTauthorization_name, job_name, cpu_time, total_disk_io_count
FROM TABLE(qsys2.active_job_info(SUBSYSTEM_LIST_FILTER =>'QSYSWRK', JOB_NAME_FILTER =>'QSQSRVR')) x)
SELECTauthorization_name, ss.message_text, job_name, cpu_time, total_disk_io_count
FROMtt, TABLE(qsys2.joblog_info(job_name)) ss
WHEREmessage_id = 'CPF9898'ANDfrom_program = 'QSQSRVR'
ORDER BY CPU_TIME DESC;
This service gives results similar to the following, where you can see the message text for the CPF9898 message, which includes the job name that requested the SQL statement for the QSQSRVR job that executed it.
The above example is easily customized. Perhaps your QSQSRVR jobs run in a different subsystem or maybe you are interested in only those jobs for a specific current user. In addition, QSYS2_ACTIVE_JOB_INFO has a large number of columns of information that can be returned for each job. A simple extension would be to add the current SQL statement to the returned results.
If you’re interested in finding the SQL Server Mode job for a specific requesting job, you can use the QSYS2.FIND_QSQSRVR_JOBS() procedure. This procedure takes the job name for which you want to find the associated SQL Server Mode job(s). This procedure returns some basic performance information as well as the current SQL statement.
You could create variations on both of the above examples by combining the QSYS2.ACTIVE_JOB_INFO() service to find all the active jobs that have the SQL Server Mode flag set, then use the QSYS2.FIND_QSQSRVR_JOBS procedure to find the QSQSRVR job handling that SQL server mode request.
Subsystem Configuration for QSQSRVR jobs
By default, the QSQSRVR prestart server jobs run in the QSYSWRK subsystem. This default is problematic as these jobs are actually doing work for a user request, not system work. As such, it’s a good idea to configure the subsystem where the QSQSRVR jobs run. You have two choices:
- Run all QSQSRVR jobs in their own subsystem
- Run the QSQSRVR jobs in the same subsystem as the client job initiating the SQL Server Mode request.
I recently summarized how to do this in the Subsystem Configuration for Prestart Jobs article, so refer to that blog for the details.
These topics were also previously written about in the blogs Find SQL Server Mode Jobs and Subsystem Configuration for SQL Server Mode Jobs.
About the author
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.
See more by Dawn May