QSQSRVR Job Considerations

The QSQSRVR prestart server jobs run in the QSYSWRK subsystem by default, but you can configure the subsystem for these jobs

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.

For example:

CALL QSYS2.FIND_QSQSRVR_JOBS('922740/QLWISVR/QINAVMNSRV')

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:

1. Run all QSQSRVR jobs in their own subsystem
2. 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.