FIND_QSQSRVR_JOBS procedure
The FIND_QSQSRVR_JOBS procedure returns information about a QSQSRVR job.
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:
| 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. |
| 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. |