IBM DB2 for i: Tips

Database monitor analysis queries

For database monitor analysis queries to run against data collected on IBM i servers with pre-V5R4M0 releases installed.

*) Identify the most time consuming jobs
*) (can use QVC102 instead of QQUSER to group on current user):
SELECT SUM(qqi6) "Total Time", COUNT(*) "Total SQL Requests", 
qqjnum,qqjob,qquser FROM ??File Name
WHERE qqrid=1000 AND qqc21 <> 'MT'
GROUP BY qqjob,qquser,qqjnum ORDER BY 1 DESC


*) Identify which type of SQL operations account for 
the most run time:
SELECT SUM(qqi6) "Total Time", COUNT(*) "Nbr of Requests", 
qqc21 "Operation Type" FROM ??File Name
WHERE qqrid=1000 AND qqc21 <> 'MT' AND qqjnum=??'xxxxxx'
GROUP BY qqc21 ORDER BY 1 DESC


*) Which SQL statements account for the most run time:
SELECT SUM(qqi6) "Total Time" , COUNT(*) "Nbr Times Run", qq1000 
FROM ??File Name 
WHERE qqjnum=??'xxxxxx' AND qqrid=1000 AND qqucnt<>0 
 AND qqc21<>'MT' 
 GROUP BY qq1000 ORDER BY 1 DESC
 
 
 *) Which SQL statements are the longest running
 *) (include Fetch & Close time with SELECT statement):
 WITH ExecTime AS (SELECT qqjfld, sum(qqi6) 
 AS exectot FROM ??File Name 
 WHERE qqrid=1000 AND
 (qqc21 IN ('SI','OP','FE','CL','IN','UP','DL')
 OR (qqucnt>0 AND qqc21 IN('SV','VI') )) GROUP BY qqjfld),
 StmtText AS (SELECT DISTINCT qqjfld, qq1000L FROM ??File Name 
 WHERE qqrid=1000 AND (qqc21 IN ('SI','OP','IN','UP','DL')
 OR (qqucnt>0 AND qqc21 IN('SV','VI') )) )
 SELECT sum(x.exectot) "Total Time", s.qq1000L
 FROM ExecTime x, StmtText s
 WHERE x.qqjfld = s.qqjfld GROUP BY s.qq1000L ORDER BY 1 DESC
 
 
 *) Which SQL statements are the longest running and how many 
 times have they been 
 *) executed (include Fetch & Close time with SELECT statement): 
 WITH ExecTime AS (SELECT qqjfld, sum(qqi6) AS exectot FROM 
 ??File Name WHERE qqrid=1000 AND 
 (qqc21 IN ('SI','OP','FE','CL','IN','UP','DL')
 OR (qqucnt>0 AND qqc21 IN('SV','VI') )) GROUP BY qqjfld) ,
 StmtText AS (SELECT DISTINCT qqjfld, qq1000L FROM 
 ??File Name WHERE qqrid=1000 AND (qqc21 IN 
 ('SI','OP','IN','UP','DL') 
 OR (qqucnt>0 AND qqc21 IN(‘SV','VI')))),
 StmtCnt AS (SELECT qq1000L, count(*) AS cntr FROM 
 ??File Name WHERE qqrid=1000 AND 
 (qqc21 IN ('SI','OP','FE','CL','IN','UP','DL')
 OR (qqucnt>0 AND qqc21 IN('SV','VI') ))
 GROUP BY qq1000L) SELECT sum(x.exectot)
 "Total Time", max(cntr) "Nbr Times Run", s.qq1000L
 FROM ExecTime x, StmtText s, StmtCnt c 
 WHERE x.qqjfld = s.qqjfld AND s.qq1000L = c.qq1000L 
 GROUP BY s.qq1000L ORDER BY 1 DESC
 
 
 *) Which queries involve table scans and show the 
 "Estimated rows
 *) selected" versus "Total rows in tables":
 WITH tablescans AS (SELECT DISTINCT qqjfld, qqucnt,
 qqrest,qqtotr FROM ??File Name 
 WHERE qqrid=3000)
 SELECT SUM(qqi6) "Total Time", COUNT(*) "Times Run", 
 a.qqucnt, integer(avg(b.qqrest)) "Est Rows Selected",
 integer(avg(b.qqtotr)) "Total Rows in Table", qq1000
 FROM ??File Name a, tablescans b WHERE
 qqrid=1000 AND a.qqjfld = b.qqjfld AND qqc21 IN 
 ('OP','SI','SV','UP','IN','DL')
 GROUP BY a.qqucnt, qq1000 ORDER BY 1 DESC
 
 
 *) Which indexes are advised the most often?
 SELECT qqucnt, qvqtbl "Table Name", qvqlib "Schema", 
 qqi2 "Nbr of Primary Keys", SUBSTR(qqidxd, 1,100) "Keys Advised"
 FROM ??File Name
 WHERE qqrid IN (3000, 3001, 3002) and qqidxa='Y' ORDER BY 5,2
 
 
 *) Which index builds are done the most often?
 SELECT qqucnt, qqc16 "Index Reused" qvptbl "Table Name", 
 qvplib "Schema", qqtotr "Rows in Table",  
 qqridx "Entries in Index", 
 qq1000L "Key Fields" FROM ??File Name
 WHERE qqrid=3002 AND qqjnum=??'xxxxxx' 
 ORDER BY qqridx DESC
 
 
 *) Which queries had access plans rebuilt?
 WITH rebuilds AS (SELECT DISTINCT qqjfld, qqucnt, qqrcod
 FROM qgpl.snapshot1 WHERE qqrid=3006 )
 SELECT a.qqucnt, b.qqrcod "Rebuild Reason",
 qvc24 "Plan Saved Status", qq1000 FROM qgpl.snapshot1 a, 
 rebuilds b WHERE a.qqjfld=b.qqjfld AND qqrid=1000 
 AND qqc21 NOT IN ('MT','FE','CL','HC') ORDER BY 4, 1
 
 
 *) Which queries were processed by SQE vs CQE? 
 (QQC16='Y' implies SQE, 'N'= CQE)
 SELECT qqc16, COUNT(*) FROM ??File Name 
 WHERE qqrid=3014 GROUP BY qqc16
 
 
 *) Which column statistics have been advised by SQE?
 SELECT qqucnt, qvqtbl "Table", qvqlib "Schema", 
 qqc11"Reason Stat Advised", SUBSTR(qq1000,1,100) "Column name"
 FROM ??File Name
 WHERE qqrid=3015
 ORDER BY 2,5
 
 
 *) Analyze I/O activity for most time consuming SQL 
 statements: WITH retrieved AS 
 (SELECT qqjfld, qqi3, qqi5 FROM ??File Name 
 WHERE qqrid=3019 ) SELECT SUM(qqi6) "Total Time" , 
 COUNT(*) "Nbr Times Run", 
 SUM(b.qqi3) "Sync DB Reads", SUM((b.qqi5) "ASync DB Reads", 
 qq1000 FROM ??File Name a, retrieved b 
 WHERE a.qqjfld=b.qqjfld AND qqrid=1000 
 AND qqucnt<>0 AND qqc21<>'MT' 
 GROUP BY qq1000 ORDER BY 1 DESC
 
 
 *) Which SQL requests are significantly affected by Full Opens:
 SELECT SUM(qqi6) "Total Time" , COUNT(*) "Nbr Full Opens", 
 qq1000 FROM ??File Name
 WHERE qqjnum=??'xxxxxx' AND qqrid=1000 AND qqi5=0 
 AND (qqc21 IN ('OP','SI','DL','IN','UP') 
 OR (qqucnt>0 AND qqc21 IN('SV','VI')))
 GROUP BY qq1000 ORDER BY 1 DESC
 
 
 *) What are the reasons causing the Full Opens:
 SELECT qqc21, qqc15 "HC Reason", qqc23 "HC Subcode",
 COUNT(*) "HC Count"   FROM ??File Name 
 WHERE qqrid=1000 AND qqc21 IN ('HC','IN','UP','DL') AND 
 qqc15>'' AND qqc23>'' AND qqjnum= ??'xxxxxx' 
 GROUP BY qqc21, qqc15, qqc23 ORDER BY 1
 
 
 *) How long are the stored procedure calls running and how many
 *) SQL operations are being performed during the calls 
 (Replace QQC103 with QVC1282 to return the "long" 
 SQL Procedure Name)?
 SELECT qqc104 AS "Proc Schema", qqc103 AS "Procedure" , 
 SUM (qqi6) "Total Time" , COUNT (*) "Nbr of Requests" ,
 qqc21 "Operation Type" FROM ??File Name
 WHERE qqrid=1000 AND qqc21<>'MT' AND qqc103<> ' '
 GROUP BY qqc104, qqc103, qqc21 ORDER BY 1,2,5
 
 
 *) Which SQL instances (QQUCNT) account for the most run time ?
 SELECT SUM(qqi6) "Total Time" , COUNT(*) "Nbr Times Run", qqucnt
 FROM ??File Name 
 WHERE qqjnum=??'xxxxxx' AND qqrid=1000 AND qqucnt<>0 
 AND qqc21<>'MT' 
 GROUP BY qqucnt ORDER BY 1 DESC
 
 
 *) Which SQL instances (QQUCNT & text) account for 
 the most run time ?
 SELECT SUM(qqi6) "Total Time" , COUNT(*) "Nbr Times Run", 
 qqucnt, qq1000
 FROM ??File Name 
 WHERE qqjnum=??'xxxxxx' AND qqrid=1000 AND qqucnt<>0 
 AND qqc21<>'MT' 
 GROUP BY qqucnt, qq1000 ORDER BY qqucnt, 1 DESC
 
 
 *) Which queries involve use of a query sort?
 WITH sorts AS (SELECT qqjfld, qqucnt FROM ??File Name 
 WHERE qqrid=3003 )
 SELECT SUM(qqi6) "Total Time" , COUNT(*) "Nbr Times Run", 
 a.qqucnt, qq1000 
 FROM ??File Name a, sorts b
 WHERE qqrid=1000 AND a.qqjfld=b.qqjfld
 GROUP BY a.qqucnt,qq1000 ORDER BY 1 DESC
 
 
 *) Analyze I/O & CPU activity for most frequently 
 run SQL statements:
 WITH listSQL AS (SELECT DISTINCT qqjfld, qq1000L
 FROM ??File Name WHERE qqrid=1000 AND
 (qqc21 IN ('SI','OP','IN','UP','DL') 
 OR (qqucnt>0 AND qqc21 IN('SV','VI') )) )
 SELECT COUNT(*) timesrun, SUM(a.qqi1) cpums, 
 SUM(a.qqi2) elpms, SUM(a.qqi3) synrds, 
 SUM(a.qqi4) synwrts, SUM(a.qqi5) asyrds, 
 SUM(a.qqi6) asywrts, SUM(a.qqi7) rowsread, 
 SUM(a.qqi8) as readcalls, 
 qq1000L FROM ??File Name a, listSQL b 
 WHERE a.qqjfld=b.qqjfld AND a.qqrid=3019 
 GROUP BY b.qq1000L ORDER BY 1 DESC
 
 
 *) Which non-SQL queries are the most time consuming?
 WITH retrieved AS (SELECT qqjfld, qqucnt, qqi2,qqi7
 FROM ??File Name WHERE qqrid=3019 )
 SELECT (qqi1+b.qqi2) "Total Query Time", 
 b.qqi7 "Number Rows Retrieved", qqc101 "Open ID", qquser
 FROM ??File Name a, retrieved b 
 WHERE a.qqjfld=b.qqjfld AND qqrid=3014 
 ORDER BY 1 DESC

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

IBM and IBM Business Partner solutions exploit key benefits in IBM Power Systems’ unique design combined with systems software to provide support for next generation applications for big data and analytics and cognitive computing to providing unprecedented insights into opportunities, threats and efficiencies to your organization.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information