FOCUS ON command in option '10 SQL Exec Current Stats'
Article ID: 130050
CA Bind Analyzer for DB2 for z/OSCA SQL-Ease for DB2 for z/OSCA Sysview Performance Management Option for DB2 for z/OSCA Plan Analyzer for DB2 for z/OSCA Subsystem Analyzer for DB2 for z/OS
FOCUS ON command in option '10 SQL Exec Current Stats' with an End User's ID: USERID does not display any information, just got 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.
Release: Component: IDB2
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).