FOCUS ON command in Sysview for Db2 option '10 SQL Exec Current Stats'

Products

• SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

FOCUS ON command in Sysview Performance Management Option for Db2 for z/OS (IDB2) option '10  SQL Exec Current Stats'
with an End User's ID: USERID does not display any information with message:
 
There are no cached dynamic SQL statements to display at this time.

 
However, after EXPLAIN STMTCACHE ALL; with an empty USERID.DSN_STATEMENT_CACHE_TABLE table and then running the following SELECT statement:
 
SELECT PRIMAUTH, STAT_CPU, STMT_ID, STAT_EXEC, STMT_TEXT
  FROM USERID.DSN_STATEMENT_CACHE_TABLE
  WHERE PRIMAUTH = 'USERID'
  ORDER BY STAT_CPU DESC
  FETCH FIRST 20 ROWS ONLY;

There are several Dynamic SQL statements by USERID.

Resolution

In FOCUS ON command you specify the 'End User's ID :' (IQL Name: END-USERID-VAR, DB2 Name: QW0316XE_D)
In USERID.DSN_STATEMENT_CACHE_TABLE SQL statement you specified the PRIMAUTH (equivalent in IFCID 316 is IQL Name: USERID, DB2 Name: QW0316T3).
PRIMAUTH field is not always equal to 'End User's ID :'. That is why you see no cached dynamic SQL for userid USERID in FOCUS command.

Lets look at the description for several IBM fields from IFCID 316:

USERID-VAR -> Workstation name - Value provided on RRS signon or resignon.
END-USERID-VAR -> End User Id - Value provided on RRS signon or resignon.
TRANSNAME-VAR -> Transaction Name - Value provided on RRS signon or resignon.

All these fields could be specified on the RRS signon or resignon as an input parameters in your program. (In this case the 'End User's ID :' is not equal to the PRIMAUTH).
If these fields are not specified on the RRS signon or resignon the default is used (In this case the 'End User's ID :' is equal to the PRIMAUTH).

What you could do for now to find dynamic SQL with PRIMAUTH = 'USERID' on option '10  SQL Exec Current Stats':

1) Do the EXPLAIN STMTCACHE ALL;
2) SELECT PRIMAUTH, PROGRAM_NAME
    FROM USERID.DSN_STATEMENT_CACHE_TABLE
    WHERE PRIMAUTH = 'USERID'
    ORDER BY STAT_CPU DESC
    FETCH FIRST 20 ROWS ONLY;
3) Go to option option '10  SQL Exec Current Stats', use FOCUS ON COMMAND and specify the 'Program Name :' from the USERID.DSN_STATEMENT_CACHE_TABLE table
from the previous step.
4) Select the record from the 'Dynamic SQL Statements in Cache' to see the detailed statistics.
5) See the following fields: "End user ID", "Program name" and "Workstn name".
Especially please look into "End user ID" field it should be not equal to 'USERID' in your case.
 
To allow to enter lower case in FOCUS ON command you have to update the Profile.

From User Interface go to Drop-Down Menu Bar "Tools" -> "7 PROFILE..."
On the "Display Parameters" panel please specify "Mixed Case CORRID-Wrkstn Qual" to Y (N Upper case, Y Mixed case).