FIND_QSQSRVR_JOBS procedure

The FIND_QSQSRVR_JOBS procedure returns information about a QSQSRVR job.

Read syntax diagramSkip visual syntax diagramFIND_QSQSRVR_JOBS( job-name )

The schema is QSYS2.

job-name
A character string containing a qualified job name.

If the specified 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. It returns two SQL result sets, one containing summary information and one containing detailed SQL server mode job information.

Authorization: To invoke FIND_QSQSRVR_JOBS you need *JOBCTL special authority, QIBM_DB_SQLADM function usage, or QIBM_DB_SYSMON function usage.

The results of the procedure call are saved in two temporary tables, QTEMP.QSQSRVR_SUMMARY and QTEMP.QSQSRVR_DETAIL. When called from within IBM i Navigator Run SQL Scripts, two results sets are displayed. When called from other interfaces, you need to query the temporary tables to see the data.

The result sets that are returned or the tables that are created contain the following columns:

Table 1. FIND_QSQSRVR_JOBS result set 1
Column Name System Column Name Data Type Description
SQL_IDENTITY SQL_I00001 INTEGER Unique identifier for this row.
NUMBER_OF_ACTIVE_JOBS NUMJOBS INTEGER Number of QSQSRVR jobs active for this job.
SERVER_MODE_JOB SRVRJOB CHAR(28) The fully qualified QSQSRVR job name for an active SQL Server Mode connection established by job-name.
SERVER_MODE_CONNECTING_JOB CONNJOB CHAR(28) The fully qualified job name of the application job. This value matches what was input for job_name.
TOTAL_PROCESSING_TIME TOTALCPU BIGINT The total amount of CPU time (in milliseconds) that has been used by all server jobs.
TEMP_MEG_STORAGE TEMPMSTG INTEGER The total amount of auxiliary storage (in megabytes) that is currently allocated to all server jobs.
PAGE_FAULTS FAULTS BIGINT The total number of times an active program referenced an address that was not in main storage for all server jobs.
IO_REQUESTS IOREQS BIGINT The total number of auxiliary I/O requests performed by the job across all routing steps for all server jobs. This includes both database and non-database paging.
Table 2. FIND_QSQSRVR_JOBS result set 2
Column Name System Column Name Data Type Description
SQL_IDENTITY SQL_I00001 INTEGER Unique identifier for this row.
JOB_NAME JOBNAME CHAR(10) Job name.
USER_NAME USERNAME CHAR(10) User ID for the job.
JOB_NUMBER JOBNUM CHAR(6) Job number.
JOB_INTERNAL_IDENTIFIER JOBID CHAR(16) Internal identifer assigned to job.
CURRENT_USERNAME CURRUSER CHAR(10) The user profile that the thread is currently running under.
SUBSYSTEM_DESCRIPTION_NAME SBSNAME CHAR(10) Name of subsystem where job is running.
RUN_PRIORITY PRIORITY INTEGER The highest run priority allowed for any thread within this job.
SYSTEM_POOL_IDENTIFIER POOLID INTEGER The identifier of the system-related pool from which the job's main storage is allocated.
TOTAL_PROCESSING_TIME TOTALCPU BIGINT The amount of CPU time (in milliseconds) that has been currently used by this job.
PAGE_FAULTS FAULTS BIGINT The number of times an active program referenced an address that was not in main storage during the current routing step of the specified job.
IO_REQUESTS IOREQS BIGINT The number of auxiliary I/O requests performed by the job across all routing steps. This includes both database and non-database paging.
MEMORY_POOL_NAME POOLNAME CHAR(10) The name of the memory pool in which the job started running.
TEMP_MEG_STORAGE TEMPMSTG INTEGER The amount of auxiliary storage (in megabytes) that is currently allocated to this job.
TIME_SLICE TSLICE INTEGER The maximum amount of processor time (in milliseconds) given to each thread in this job before other threads in this job and in other jobs are given the opportunity to run.
DEFAULT_WAIT DFTWAIT INTEGER The default maximum time (in seconds) that a thread in the job waits for a system instruction to acquire a resource.
SQL_APPLICATION_LIBRARY SQLLIB CHAR(10) The library name for the SQL statement object.
SQL_APPLICATION_PROGRAM SQLPGM CHAR(10) The program, service program, or package name of the object which contains the last SQL statement executed in the job.
SQL_APPLICATION_TYPE APPTYPE CHAR(10) The object type.
SERVER_MODE_CONNECTING_JOB CONNJOB CHAR(28) The qualified job name of the job which established the SQL Server Mode connection.
SERVER_MODE_CONNECTED_THREAD CONNTHD CHAR(10) The thread identifier of the last thread to use this connection.
STATUS_OF_CURRENT_SQL_STMT STMTSTAT CHAR(10) Status of the SQL statement. Values are ACTIVE or COMPLETED.
SQL_STATEMENT SQLSTMT VARCHAR(1000) First 1000 characters of the SQL statement.