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.
Example Invocation:
call QSYS2.FIND_QSQSRVR_JOBS('566463/EBERHARD/QP0ZSPWP ');
Procedure definition:
CREATE PROCEDURE QSYS2.FIND_QSQSRVR_JOBS( JOB_NAME VARCHAR(28) )
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
DYNAMIC RESULT SETS 2
SPECIFIC FINDSRVR
EXTERNAL NAME 'QSYS/QSQSSUDF(FINDSRVR)'
LANGUAGE C PARAMETER STYLE SQL;
Authorization:
On IBM i 6.1, to invoke QSYS2.FIND_QSQSRVR_JOBS the user needs *JOBCTL special authority.
On IBM i 7.1, to invoke QSYS2.FIND_QSQSRVR_JOBS the user needs *JOBCTL special authority, QIBM_DB_SQLADM Function usage or QIBM_DB_SYSMON Function usage.
> call QSYS2.FIND_QSQSRVR_JOBS('650261/SCOTTF/QP0ZSPWP')
SQL State: 38501
Vendor Code: -443
Message: [CPF43A4] *JOBCTL special authority, QIBM_DB_SQLADM or QIBM_DB_SYSMON Function usage is required. Cause . . . . . : The user profile is required to have *JOBCTL special authority or be authorized to either the QIBM_DB_SQLADM or QIBM_DB_SYSMON Function through Application Administration in System i Navigator. The Change Function Usage (CHGFCNUSG) command can also be used to allow or deny use of the function.
For example: CHGFCNUSG FCNID(QIBM_DB_SQLADM) USER(xxxxx) USAGE(*ALLOWED).
Recovery . . . : Have the security officer grant *JOBCTL special authority or add the QIBM_DB_SQLADM Function usage or add the QIBM_DB_SYSMON Function usage.
Usage:
The procedure can be called from any environment. The input parameter is the application qualified job name. When called from within ACS’s Run SQL Scripts, two results sets are displayed. When called from STRSQL or elsewhere, the user needs to query the temporary tables to see the data.
select * from qtemp.QSQSRVR_DETAIL order by TOTALCPU desc;
select * from qtemp.QSQSRVR_SUMMARY;
Use this query to see the summary information in the same form that is returned within the result set.
SELECT SERVER_MODE_JOB,count(*) AS "QSQSRVR JOB COUNT", SERVER_MODE_CONNECTING_JOB, SUM(TOTAL_PROCESSING_TIME) AS "CPU USED (MILLISECONDS)", SUM(TEMP_MEG_STORAGE) AS "TEMP STORAGE USED (MB)", SUM(PAGE_FAULTS) AS "PAGE FAULTS", SUM(IO_REQUESTS) AS "I/O REQUESTS" from SESSION.QSQSRVR_SUMMARY GROUP BY GROUPING SETS (SERVER_MODE_JOB , SERVER_MODE_CONNECTING_JOB) ORDER BY 1;
Example output:
Result set 1: Summary information
Result set 2: Detailed information
Result set definition:
Result set 1: Summary information
QSQSRVR_SUMMARY (
SQL_IDENTITY FOR COLUMN SQL_I00001 INTEGER NOT NULL ,
NUMBER_OF_ACTIVE_JOBS FOR COLUMN NUMJOBS INTEGER NOT NULL,
SERVER_MODE_JOB FOR COLUMN SRVRJOB CHAR(28) CCSID 37 NOT NULL ,
SERVER_MODE_CONNECTING_JOB FOR COLUMN CONNJOB CHAR(28) CCSID 37 NOT NULL ,
TOTAL_PROCESSING_TIME FOR COLUMN TOTALCPU BIGINT NOT NULL ,
TEMP_MEG_STORAGE FOR COLUMN TEMPMSTG INTEGER NOT NULL ,
PAGE_FAULTS FOR COLUMN FAULTS BIGINT NOT NULL ,
IO_REQUESTS FOR COLUMN IOREQS BIGINT NOT NULL )
Result set 2: Detailed information
QSQSRVR_DETAIL (
SQL_IDENTITY FOR COLUMN SQL_I00001 INTEGER NOT NULL ,
JOB_NAME FOR COLUMN JOBNAME CHAR(10) CCSID 37 NOT NULL ,
USER_NAME FOR COLUMN USERNAME CHAR(10) CCSID 37 NOT NULL ,
JOB_NUMBER FOR COLUMN JOBNUM CHAR(6) CCSID 37 NOT NULL ,
JOB_INTERNAL_IDENTIFIER FOR COLUMN JOBID CHAR(16) CCSID 37 NOT NULL ,
CURRENT_USERNAME FOR COLUMN CURRUSER CHAR(10) CCSID 37 NOT NULL ,
SUBSYSTEM_DESCRIPTION_NAME FOR COLUMN SBSNAME CHAR(10) CCSID 37 NOT NULL ,
RUN_PRIORITY FOR COLUMN PRIORITY INTEGER NOT NULL ,
SYSTEM_POOL_IDENTIFIER FOR COLUMN POOLID INTEGER NOT NULL ,
TOTAL_PROCESSING_TIME FOR COLUMN TOTALCPU BIGINT NOT NULL ,
PAGE_FAULTS FOR COLUMN FAULTS BIGINT NOT NULL ,
IO_REQUESTS FOR COLUMN IOREQS BIGINT NOT NULL ,
MEMORY_POOL_NAME FOR COLUMN POOLNAME CHAR(10) CCSID 37 NOT NULL ,
TEMP_MEG_STORAGE FOR COLUMN TEMPMSTG INTEGER NOT NULL ,
TIME_SLICE FOR COLUMN TSLICE INTEGER NOT NULL ,
DEFAULT_WAIT FOR COLUMN DFTWAIT INTEGER NOT NULL ,
SQL_APPLICATION_LIBRARY FOR COLUMN SQLLIB CHAR(10) CCSID 37 NOTNULL ,
SQL_APPLICATION_PROGRAM FOR COLUMN SQLPGM CHAR(10) CCSID 37 NOT NULL ,
SQL_APPLICATION_TYPE FOR COLUMN APPTYPE CHAR(10) CCSID 37 NOT NULL ,
SERVER_MODE_CONNECTING_JOB FOR COLUMN CONNJOB CHAR(28) CCSID 37 NOT NULL ,
SERVER_MODE_CONNECTED_THREAD FOR COLUMN CONNTHD CHAR(10) CCSID 37 NOT NULL ,
STATUS_OF_CURRENT_SQL_STMT FOR COLUMN STMTSTAT CHAR(10) CCSID 37 NOT NULL ,
SQL_STATEMENT FOR COLUMN SQLSTMT VARCHAR(1000) CCSID 37 NOT NULL )
Service Information:
This procedure has been added to QSYS2 after application of the following PTFs. It is in the base release of 7.2 and later.
V5R4 PTFs:
PTF ‘5722SS1 V5R4M0 SI40098’
PTF ‘5722SS1 V5R4M0 SI40084’
PTF ‘5722SS1 V5R4M0 SI40083’
6.1 PTFs:
PTF ‘5761SS1 V6R1M0 SI40100’
PTF ‘5761SS1 V6R1M0 SI40099’
PTF ‘5761SS1 V6R1M0 SI40070’
PTF ‘5761SS1 V6R1M0 SI40068’
7.1 PTFs:
PTF ‘5770SS1 V7R1M0 SI40101’
PTF ‘5770SS1 V7R1M0 SI40124’
PTF ‘5770SS1 V7R1M0 SI40125’
This week’s blog post was written by Scott Forstie. Scott is a senior software engineer at IBM, and he’s the SQL development leader for Db2 for IBM i in Rochester, Minn. Before working on Db2, he worked on UNIX enablement for the AS/400 and S/390 systems. Thanks, Scott!
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.