QSYS2.FIND_QSQSRVR_JOBS() procedure

Anyone responsible for administering, tuning or explaining the SQL Server Mode (e.g. QSQSRVR jobs) activity might find the QSYS2.FIND_QSQSRVR_JOBS() procedure a useful tool. The procedure is passed a single parameter, the qualified job name of an application job. If the target job is active and is set up to use SQL Server Mode, the procedure determines which QSQSRVR jobs are being used by the application, in the form of active SQL Server Mode connections. The procedure collects and returns work management, performance, and SQL information and returns two SQL result sets: (1) Summary information and (2) Detailed SQL Server Mode job information.

How is this procedure useful? When you have an important application instance (job) that uses QSQSRVR jobs, it can be quite difficult to determine the "total system impact" of the application. How many SQL Server Mode jobs are in use at that moment? Is this application responsible for a QSQSRVR job that is consuming a lot of CPU or holding onto object locks? The FIND_QSQSRVR_JOBS() procedure provides some of these answers by tying together the application and its SQL Server Mode job use.

See IBM Documentation for details: FIND_QSQSRVR_JOBS

Example output:

Result set 1: Summary information

Result set 2: Detailed information