Query help

Tom Glaser

Query help
All,

Any DB2 SQL gurus out there? I’m looking for some help on how to do produce a report. What I’m looking for is:

Select distinct(corrid, plan, package, SQL Stmt#), max(MC_CPU_HRS)
From table

Below is the original query:

SELECT
PLAN,
A.CORRID AS CORRELATION_ID,
A."PROGRAM" AS PACKAGE,
A.STMT AS SQL_STMT_#,
STATEMENT_DESC,
SUM(DECIMAL(A.DB2_CPU,18,6)*MC_CPU_FACTOR)/3600.0 AS MC_CPU_HRS,
SUM(DECIMAL(SQL_CALLS)) AS SQL_CALLS,
SUM(DECIMAL(GETPAGES))/1000000.0 AS "TOTAL GETPAGES/PER_MIL",
SUM(DECIMAL(DB2_CPU))/SUM(DECIMAL(SQL_CALLS)) AS CPUSEC_SQL,
DB2_SUBSYSTEM AS DB2_SSID,
SUBSTR(LPAR,4,1)
FROM T01.TDBQSUM A,
T01.TDBMMCF B
WHERE LPAR = B.SYSTEMID
AND DB2_SUBSYSTEM IN ('D2P1','D2P2','D2P3','D2P4','D2P5','DAP6',
'DBP6','DAP7','DBP7')
AND DATE(METRICS_TIMESTAMP) BETWEEN START_EFF_DATE AND
END_EFF_DATE
AND DATE(A.METRICS_TIMESTAMP) BETWEEN CURRENT_DATE - 9 DAYS AND
CURRENT_DATE - 3 DAYS
AND (SQL_CALLS ¬= 0 AND DB2_CPU ¬= 0)
GROUP BY
A.METRICS_TIMESTAMP,
PLAN,
A.CORRID,
A."PROGRAM",
A.STMT,
STATEMENT_DESC,
DB2_SUBSYSTEM,
LPAR
ORDER BY 6 DESC
FETCH FIRST 20 ROWS ONLY


TOP 20 DB2 QUERIES SORTED BY CPU CONSUMPTION
MC
SQL DB2
CORRELATION STMT STATEMENT CPU
ID PLAN PACKAGE # DESCRIPTION HOURS
----------- -------- -------- -------- ------------- -----
MSACCESS.EX DISTSERV SYSSH100 0 FETCH 2.89
MSACCESS.EX DISTSERV SYSSH100 0 FETCH 2.88
SAM9440D AM951010 AM951010 943 SELECT INTO 2.43
SAM9440D AM951010 AM951010 943 SELECT INTO 2.39
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.14
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.14
SIP7048D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.13
SAM9440D AM951010 AM951010 943 SELECT INTO 2.07
SAM9440D AM951010 AM951010 943 SELECT INTO 2.07
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.06
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.04
SAM9440D AM951010 AM951010 943 SELECT INTO 2.03
SAM9440D AM951010 AM951010 943 SELECT INTO 2.01
SAM9440D AM951010 AM951010 943 SELECT INTO 1.99
SAM9440D AM951010 AM951010 943 SELECT INTO 1.98
SAM9440D AM951010 AM951010 943 SELECT INTO 1.88
SAM9440D AM951010 AM951010 943 SELECT INTO 1.82

.
.
.
AVG
# CPU
OF TOTAL SECONDS
SQL GETPAGES PER DB2
CALLS PER/MILL QUERY SSID L
-------- -------- -------- ---- -
67 39.37 14.16 D2P1 A
65 39.37 14.55 D2P1 A
81499 59.40 0.01 D2P1 A
90994 59.64 0.01 D2P1 A
1 19.43 1258.00 D2P1 C
1 19.43 1257.00 D2P1 C
4 14.93 312.50 D2P1 C
78363 49.05 0.01 D2P1 A
80988 51.04 0.01 D2P1 A
1 19.43 1208.00 D2P1 C
1 19.43 1198.00 D2P1 C
75023 48.91 0.01 D2P1 A
64603 49.25 0.01 D2P1 A
60818 46.93 0.01 D2P1 A
74546 46.61 0.01 D2P1 A
70662 43.81 0.01 D2P1 A
72713 44.79 0.01 D2P1 A

Since I’m doing this in QMF (tso), I can save the result set in a table and then query that…if it would make things easier. Thanks for any ideas you might have.

Tom Glaser
[login to unmask email]
636-722-3087

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Roy Boxwell

Re: Query help
(in response to Tom Glaser)
Looks like a great case for an MQT??? Create an MQT using your query (But
remove the FETCH FIRST 20 ROWS ONLY clause) this will then be your "base"
table that you can then
simply query using SQL and make it pretty using QMF.
To do your SQL you would then simply write

Select CORRELATION_ID, PLAN, PACKAGE, SQL_STMT_#, MAX(MC_CPU_HRS)
From mymqt
GROUP BY CORRELATION_ID, PLAN, PACKAGE, SQL_STMT_#
;

Thats it!


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Tom Glaser <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
03.01.2011 23:58
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] Query help






All,

Any DB2 SQL gurus out there? I’m looking for some help on how to do
produce a report. What I’m looking for is:

Select distinct(corrid, plan, package, SQL Stmt#), max(MC_CPU_HRS)
From table

Below is the original query:

SELECT
PLAN,
A.CORRID AS CORRELATION_ID,
A."PROGRAM" AS PACKAGE,
A.STMT AS SQL_STMT_#,
STATEMENT_DESC,
SUM(DECIMAL(A.DB2_CPU,18,6)*MC_CPU_FACTOR)/3600.0 AS MC_CPU_HRS,
SUM(DECIMAL(SQL_CALLS)) AS SQL_CALLS,
SUM(DECIMAL(GETPAGES))/1000000.0 AS "TOTAL GETPAGES/PER_MIL",
SUM(DECIMAL(DB2_CPU))/SUM(DECIMAL(SQL_CALLS)) AS CPUSEC_SQL,
DB2_SUBSYSTEM AS DB2_SSID,
SUBSTR(LPAR,4,1)
FROM T01.TDBQSUM A,
T01.TDBMMCF B
WHERE LPAR = B.SYSTEMID
AND DB2_SUBSYSTEM IN ('D2P1','D2P2','D2P3','D2P4','D2P5','DAP6',
'DBP6','DAP7','DBP7')
AND DATE(METRICS_TIMESTAMP) BETWEEN START_EFF_DATE AND
END_EFF_DATE
AND DATE(A.METRICS_TIMESTAMP) BETWEEN CURRENT_DATE - 9 DAYS AND
CURRENT_DATE - 3 DAYS
AND (SQL_CALLS ¬= 0 AND DB2_CPU ¬= 0)
GROUP BY
A.METRICS_TIMESTAMP,
PLAN,
A.CORRID,
A."PROGRAM",
A.STMT,
STATEMENT_DESC,
DB2_SUBSYSTEM,
LPAR
ORDER BY 6 DESC
FETCH FIRST 20 ROWS ONLY


TOP 20 DB2 QUERIES SORTED BY CPU CONSUMPTION
MC
SQL DB2
CORRELATION STMT STATEMENT CPU
ID PLAN PACKAGE # DESCRIPTION HOURS
----------- -------- -------- -------- ------------- -----
MSACCESS.EX DISTSERV SYSSH100 0 FETCH 2.89
MSACCESS.EX DISTSERV SYSSH100 0 FETCH 2.88
SAM9440D AM951010 AM951010 943 SELECT INTO 2.43
SAM9440D AM951010 AM951010 943 SELECT INTO 2.39
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.14
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.14
SIP7048D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.13
SAM9440D AM951010 AM951010 943 SELECT INTO 2.07
SAM9440D AM951010 AM951010 943 SELECT INTO 2.07
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.06
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.04
SAM9440D AM951010 AM951010 943 SELECT INTO 2.03
SAM9440D AM951010 AM951010 943 SELECT INTO 2.01
SAM9440D AM951010 AM951010 943 SELECT INTO 1.99
SAM9440D AM951010 AM951010 943 SELECT INTO 1.98
SAM9440D AM951010 AM951010 943 SELECT INTO 1.88
SAM9440D AM951010 AM951010 943 SELECT INTO 1.82

.
.
.
AVG
# CPU
OF TOTAL SECONDS
SQL GETPAGES PER DB2
CALLS PER/MILL QUERY SSID L
-------- -------- -------- ---- -
67 39.37 14.16 D2P1 A
65 39.37 14.55 D2P1 A
81499 59.40 0.01 D2P1 A
90994 59.64 0.01 D2P1 A
1 19.43 1258.00 D2P1 C
1 19.43 1257.00 D2P1 C
4 14.93 312.50 D2P1 C
78363 49.05 0.01 D2P1 A
80988 51.04 0.01 D2P1 A
1 19.43 1208.00 D2P1 C
1 19.43 1198.00 D2P1 C
75023 48.91 0.01 D2P1 A
64603 49.25 0.01 D2P1 A
60818 46.93 0.01 D2P1 A
74546 46.61 0.01 D2P1 A
70662 43.81 0.01 D2P1 A
72713 44.79 0.01 D2P1 A

Since I’m doing this in QMF (tso), I can save the result set in a table
and then query that…if it would make things easier. Thanks for any ideas
you might have.

Tom Glaser
[login to unmask email]
636-722-3087

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it!
*
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv



_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Marcel van Tunen

Re: Query help
(in response to Roy Boxwell)
Tom

A CTE is great for this kind of queries:

With v1 as (
SELECT
PLAN,
A.CORRID AS CORRELATION_ID,
A."PROGRAM" AS PACKAGE,
A.STMT AS SQL_STMT_#,
STATEMENT_DESC,
SUM(DECIMAL(A.DB2_CPU,18,6)*MC_CPU_FACTOR)/3600.0 AS MC_CPU_HRS,
SUM(DECIMAL(SQL_CALLS)) AS SQL_CALLS,
SUM(DECIMAL(GETPAGES))/1000000.0 AS "TOTAL GETPAGES/PER_MIL",
SUM(DECIMAL(DB2_CPU))/SUM(DECIMAL(SQL_CALLS)) AS CPUSEC_SQL, DB2_SUBSYSTEM AS DB2_SSID,
SUBSTR(LPAR,4,1)
FROM T01.TDBQSUM A,
T01.TDBMMCF B
WHERE LPAR = B.SYSTEMID
AND DB2_SUBSYSTEM IN ('D2P1','D2P2','D2P3','D2P4','D2P5','DAP6',
'DBP6','DAP7','DBP7')
AND DATE(METRICS_TIMESTAMP) BETWEEN START_EFF_DATE AND
END_EFF_DATE
AND DATE(A.METRICS_TIMESTAMP) BETWEEN CURRENT_DATE - 9 DAYS AND
CURRENT_DATE - 3 DAYS
AND (SQL_CALLS ¬= 0 AND DB2_CPU ¬= 0)
GROUP BY
A.METRICS_TIMESTAMP,
PLAN,
A.CORRID,
A."PROGRAM",
A.STMT,
STATEMENT_DESC,
DB2_SUBSYSTEM,
LPAR
)
Select
CORRELATION_ID,
PLAN,
PACKAGE,
SQL_STMT_#,
STATEMENT_DESC,
max(MC_CPU_HRS)
FROM v1
Group by
PLAN,
CORRELATION_ID,
PACKAGE,
SQL_STMT_#,
STATEMENT_DESC


Marcel van Tunen

-----Original Message-----
From: Tom Glaser [mailto:[login to unmask email]
Sent: Monday, January 03, 2011 11:58 PM
Subject: Query help

All,

Any DB2 SQL gurus out there? I'm looking for some help on how to do produce a report. What I'm looking for is:

Select distinct(corrid, plan, package, SQL Stmt#), max(MC_CPU_HRS) From table

Below is the original query:

SELECT
PLAN,
A.CORRID AS CORRELATION_ID,
A."PROGRAM" AS PACKAGE,
A.STMT AS SQL_STMT_#,
STATEMENT_DESC,
SUM(DECIMAL(A.DB2_CPU,18,6)*MC_CPU_FACTOR)/3600.0 AS MC_CPU_HRS,
SUM(DECIMAL(SQL_CALLS)) AS SQL_CALLS,
SUM(DECIMAL(GETPAGES))/1000000.0 AS "TOTAL GETPAGES/PER_MIL",
SUM(DECIMAL(DB2_CPU))/SUM(DECIMAL(SQL_CALLS)) AS CPUSEC_SQL, DB2_SUBSYSTEM AS DB2_SSID,
SUBSTR(LPAR,4,1)
FROM T01.TDBQSUM A,
T01.TDBMMCF B
WHERE LPAR = B.SYSTEMID
AND DB2_SUBSYSTEM IN ('D2P1','D2P2','D2P3','D2P4','D2P5','DAP6',
'DBP6','DAP7','DBP7')
AND DATE(METRICS_TIMESTAMP) BETWEEN START_EFF_DATE AND
END_EFF_DATE
AND DATE(A.METRICS_TIMESTAMP) BETWEEN CURRENT_DATE - 9 DAYS AND
CURRENT_DATE - 3 DAYS
AND (SQL_CALLS ¬= 0 AND DB2_CPU ¬= 0)
GROUP BY
A.METRICS_TIMESTAMP,
PLAN,
A.CORRID,
A."PROGRAM",
A.STMT,
STATEMENT_DESC,
DB2_SUBSYSTEM,
LPAR
ORDER BY 6 DESC
FETCH FIRST 20 ROWS ONLY


TOP 20 DB2 QUERIES SORTED BY CPU CONSUMPTION
MC
SQL DB2
CORRELATION STMT STATEMENT CPU
ID PLAN PACKAGE # DESCRIPTION HOURS
----------- -------- -------- -------- ------------- -----
MSACCESS.EX DISTSERV SYSSH100 0 FETCH 2.89
MSACCESS.EX DISTSERV SYSSH100 0 FETCH 2.88
SAM9440D AM951010 AM951010 943 SELECT INTO 2.43
SAM9440D AM951010 AM951010 943 SELECT INTO 2.39
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.14
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.14
SIP7048D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.13
SAM9440D AM951010 AM951010 943 SELECT INTO 2.07
SAM9440D AM951010 AM951010 943 SELECT INTO 2.07
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.06
SIP7090D DSNTIAUL DSNTIAUL 3652 FETCH ROWSET 2.04
SAM9440D AM951010 AM951010 943 SELECT INTO 2.03
SAM9440D AM951010 AM951010 943 SELECT INTO 2.01
SAM9440D AM951010 AM951010 943 SELECT INTO 1.99
SAM9440D AM951010 AM951010 943 SELECT INTO 1.98
SAM9440D AM951010 AM951010 943 SELECT INTO 1.88
SAM9440D AM951010 AM951010 943 SELECT INTO 1.82

.
.
.
AVG
# CPU
OF TOTAL SECONDS
SQL GETPAGES PER DB2
CALLS PER/MILL QUERY SSID L
-------- -------- -------- ---- -
67 39.37 14.16 D2P1 A
65 39.37 14.55 D2P1 A
81499 59.40 0.01 D2P1 A
90994 59.64 0.01 D2P1 A
1 19.43 1258.00 D2P1 C
1 19.43 1257.00 D2P1 C
4 14.93 312.50 D2P1 C
78363 49.05 0.01 D2P1 A
80988 51.04 0.01 D2P1 A
1 19.43 1208.00 D2P1 C
1 19.43 1198.00 D2P1 C
75023 48.91 0.01 D2P1 A
64603 49.25 0.01 D2P1 A
60818 46.93 0.01 D2P1 A
74546 46.61 0.01 D2P1 A
70662 43.81 0.01 D2P1 A
72713 44.79 0.01 D2P1 A

Since I'm doing this in QMF (tso), I can save the result set in a table and then query that...if it would make things easier. Thanks for any ideas you might have.

Tom Glaser
[login to unmask email]
636-722-3087

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

**********************************************************************


This transmission is confidential and must not be used or disclosed by anyone other than the intended recipient. Neither Tata Steel Europe Limited nor any of its subsidiaries can accept any responsibility for any use or misuse of the transmission by anyone.

For address and company registration details of certain entities within the Tata Steel Europe group of companies, please visit http://www.tatasteeleurope.com/entities


**********************************************************************

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv