Sample SQL queries

As with the STRDBMON monitor, it is up to the user to extract the information from the tables in which all of the monitored data is stored. This can be done through any query interface that the user chooses.

If you are using Operation Navigator with the support for the SQL Monitor, you have the ability to analyze the results direct through the GUI interface. There are a number of shipped queries that can be used or modified to extract the information from any of the tables.

The sample query listed below will give the user the Basic Statement Information about all of the statements that were monitored.

SELECT 
 /* Database Performance Monitor Basic Statement Information */ 
 
 /* Time */ 
a.QQTIME as "Time",
 
 /* Costs */ 
DECIMAL(QQMAXT/1000,18,3) as "Maximum Runtime",
DECIMAL(QQAVGT/1000,18,3) as "Average Runtime",
DECIMAL(QQMINT/1000,18,3) as "Minimum Runtime",
DECIMAL(QQOPNT/1000,18,3) as "Maximum Open Time",
DECIMAL(QQFETT/1000,18,3) as "Maximum Fetch Time ",
DECIMAL(QQCLST/1000,18,3) as "Maximum Close Time",
DECIMAL(QQOTHT/1000,18,3) as "Maximum Other Time ",
QQMETU as "Most Expensive Use",
QQLTU  as "Last Use",
 
 /* Statement Identification */ 
CASE QQSTOP 
WHEN 'AL' THEN 'ALTER TABLE' 
WHEN 'CA' THEN 'CALL' 
WHEN 'CD' THEN 'CREATE DISTINCT TYPE' 
WHEN 'CF' THEN 'CREATE FUNCTION' 
WHEN 'CL' THEN 'CLOSE' 
WHEN 'CO' THEN 'COMMENT ON' 
WHEN 'CM' THEN 'COMMIT' 
WHEN 'CN' THEN 'CONNECT' 
WHEN 'CC' THEN 'CREATE COLLECTION' 
WHEN 'CI' THEN 'CREATE INDEX' 
WHEN 'CP' THEN 'CREATE PROCEDURE' 
WHEN 'CS' THEN 'CREATE ALIAS' 
WHEN 'CT' THEN 'CREATE TABLE' 
WHEN 'CV' THEN 'CREATE VIEW' 
WHEN 'DC' THEN 'DECLARE CURSOR' 
WHEN 'DD' THEN 'DELETE...DELETE' 
WHEN 'DE' THEN 'DESCRIBE' 
WHEN 'DF' THEN 'DELETE...FETCH' 
WHEN 'DI' THEN 'DISCONNECT'
WHEN 'DK' THEN 'DELETE...CLOSE'
WHEN 'DL' THEN 'DELETE'
WHEN 'DP' THEN 'DECLARE PROCEDURE' 
WHEN 'DR' THEN 'DROP' 
WHEN 'DT' THEN 'DESCRIBE TABLE' 
WHEN 'DU' THEN 'DELETE...UPDATE' 
WHEN 'EX' THEN 'EXECUTE' 
WHEN 'EI' THEN 'EXECUTE IMMEDIATE' 
WHEN 'FC' THEN 'FETCH...CLOSE' 
WHEN 'FD' THEN 'FETCH...DELETE' 
WHEN 'FE' THEN 'FETCH' 
WHEN 'FF' THEN 'FETCH...FETCH' 
WHEN 'FL' THEN 'FREE LOCATOR' 
WHEN 'FU' THEN 'FETCH...UPDATE' 
WHEN 'GR' THEN 'GRANT' 
WHEN 'IC' THEN 'INSERT' 
WHEN 'IN' THEN 'INSERT' 
WHEN 'LO' THEN 'LABEL ON' 
WHEN 'LK' THEN 'LOCK' 
WHEN 'OC' THEN 'OPEN...CLOSE' 
WHEN 'OD' THEN 'OPEN...DELETE' 
WHEN 'OF' THEN 'OPEN...FETCH' 
WHEN 'OP' THEN 'OPEN' 
WHEN 'OU' THEN 'OPEN...UPDATE' 
WHEN 'PR' THEN 'PREPARE' 
WHEN 'RE' THEN 'RELEASE' 
WHEN 'RO' THEN 'ROLLBACK' 
WHEN 'RT' THEN 'RENAME' 
WHEN 'RV' THEN 'REVOKE' 
WHEN 'SC' THEN 'SET CONNECTION' 
WHEN 'SI' THEN 'SELECT INTO'
WHEN 'SK' THEN 'SELECT INTO' 
WHEN 'SP' THEN 'SET PATH' 
WHEN 'SR' THEN 'SET RESULTS' 
WHEN 'ST' THEN 'SET TRANSACTION' 
WHEN 'SV' THEN 'SET VARIABLE' 
WHEN 'UC' THEN 'UPDATE...CLOSE'
WHEN 'UD' THEN 'UPDATE...DELETE'
WHEN 'UF' THEN 'UPDATE...FETCH'
WHEN 'UP' THEN 'UPDATE'
WHEN 'UU' THEN 'UPDATE...UPDATE'
ELSE QQSTOP 
END as "Operation",
QQCNT as "Statement Usage Count ",
varchar(b.QQSTTX,20000) as "Statement Text",
varchar(qqhvar,500) as "Host Variable Values",
 
 /* Opens */ 
 QQFULO as "Full Opens",
 QQPSUO as "Pseudo Opens",
 
 /* Row Sizes */ 
 QQTOTR as "Table Rows",
 QQRROW as "Result Rows",
 QQARSS as "Average Result Size",
 
 /* Implementation */ 
CASE QQODPI 
WHEN 'R' THEN 'Reusable' 
WHEN 'N' THEN 'Non-Reusable' 
ELSE QQODPI 
END as "ODP Implementation",
 
CASE QQHVI 
WHEN 'I' THEN 'ISV' 
WHEN 'V' THEN 'V2' 
WHEN 'U' THEN 'UP' 
ELSE QQHVI 
END as "Host Variable Implementation",
 
CASE QQDACV 
WHEN 'N' THEN NULL 
WHEN '0' THEN NULL 
WHEN '1' THEN 'Different Lengths' 
WHEN '2' THEN 'Different Numeric Types' 
WHEN '3' THEN 'C NUL-terminated Variable' 
WHEN '4' THEN 'Varying Length Fixed Length' 
WHEN '5' THEN 'CCSID Conversion' 
WHEN '6' THEN 'DRDA Mapping Required' 
WHEN '7' THEN 'Datetime Column' 
WHEN '8' THEN 'Too Many Host Variables' 
WHEN '9' THEN 'Target Table Is Not A SQL Table' 
ELSE QQDACV 
END as "Data Conversion",
QQCTS as "Table Scan Count",
 
 /* Index Information */ 
 QQCIU  as "Index Use Count",
 QQCIC  as "Index Create Count",
 QQCIA  as "Index Advised Count",
 
 /* Copy of data */ 
 QQCTF  as "Temporary Table Count",
 QQCSO  as "Sort Count",
 
 /* Access Plan Rebuild */ 
QQAPRT as "Last Access Plan Rebuilt",
QQCAPR as "Access Plan Rebuild Count",
 
CASE QQAPR 
WHEN 'A1' THEN 'Different Table Or Member' 
WHEN 'A2' THEN 'Reusable Plan to Non-Reusable Plan Change' 
WHEN 'A3' THEN 'Non-Reusable To Reusable Plan Change' 
WHEN 'A4' THEN 'More Than Ten Percent Change In Number Of Rows' 
WHEN 'A5' THEN 'New index Found' 
WHEN 'A6' THEN 'index No Longer Found Or Valid' 
WHEN 'A7' THEN 'System Programming Change' 
WHEN 'A8' THEN 'Different CCSID' 
WHEN 'A9' THEN 'Different Date Or Time Format' 
WHEN 'AA' THEN 'Different Sort Sequence Table' 
ELSE QQAPR 
END as "Access Plan Rebuild Reason",
 
 /* Job-user-program identification */ 
QQJOB  as "Job",
QQUSER as "Job User",
QQJNUM as "Job Number",
QQTHID as "Thread ID",
QQPLIB as "Program Library",
QQPNAM as "Program",
 
 /* Statement attributes */ 
QQUDEF as "User Defined column",
QQCNAM as "Cursor",
QQSNAM as "Statement Name"
 
FROM <> a left join <> b on a.qqkey=b.qqkey 
left join <> c on a.qqkey=c.qqkey 
 
ORDER BY "Maximum Runtime" DESC

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]