You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Bookmark and Share
RSS

Recent Posts

i Can ... Find SQL Server Mode Jobs

August 23, 2010

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!

Hello again IBM i users. This blog thread explains a recent DB2 for i enhancement to IBM i on V5R4, 6.1 and 7.1. 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. This procedure has been added to QSYS2 after application of PTFs (see the Service Information section for details). 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 System i Navigator'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

Pic21574
 

Result set 2: Detailed information

Pic26190

Pic30083

Pic06152

Pic30504

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:

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'

Posted August 23, 2010| Permalink

-->