DB2 for z/OS Version 7 -206 huh? DB2PE

Edward Long

DB2 for z/OS Version 7 -206 huh? DB2PE
I am trying to produce a set of average and max CPU times for each of 60 trace periods. My expected result set should include the date followed by the average and max cpu times.

After several hours of reviewing the reference and programming guides, consulting Craig's book, and experiments, I am about to give up and code a DSNTIAUL and send this data to SAS where this is 10 seconds of work.

The -206 is on I.IRD. All suggestions will be most appreciated. Thanks
SELECT
DATE(A.TIMESTAMP) AS ARD,
I.AVGCPU ,
I.MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL A,
TABLE (
SELECT
DATE(I.TIMESTAMP) AS IRD,
AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,
MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL AS I
WHERE
TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY I.IRD
HAVING A.ARD = I.IRD
) AS IT
WHERE PRIMAUTH = 'MANNY'
AND TIMESTAMP > '2005-11-30-23.59.00.000000'
ORDER BY A.ARD
;


Edward Long

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Hugh Lapham

Re: DB2 for z/OS Version 7 -206 huh? DB2PE
(in response to Edward Long)
If anyone is interested, I have posted a "Web Spufi" (DB2toSAS) on the
MXG listserv which essentially does exactly what Ed suggests -- allows
us to use raw base SAS to process DB2 tables as though they were SAS
data sets. I have also created the next level whereby we run DSNZSCH
to capture dynamic SQL from the cache into DB2 tables and then run
DB2toSAS to put the results on the web. A VERY cheap way to examine &
analyze dynamic SQL.

Back to Ed's question ... why do we need the subselect ... or is this a
snippet of something much larger?
SELECT DATE(TIMESTAMP) AS IRD
, AVG(CLASS1_CPU_TOTAL) AS AVGCPU
, MAX(CLASS1_CPU_TOTAL) AS MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL
WHERE PRIMAUTH = 'MANNY'
and TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY IRD

would appear to produce the intended result ... replace the "IRD" line
with some function to identify the period (e.g., hour?) if you want a
single select to do the whole thing. Much too simple, so I guess I
missed something ;-))


Thanks, Hugh
[note - try "view HTML" if this message looks strange]
----
Information Engineering & Development
Enterprise Computing Services
Platform Services

RCMP (613) 993-8866 CPIC Bldg [2-092]
Home (613) 321-5129
cell (613) 864-1670 HJLapham @ Sevenoaks Business Systems . com


>>> Ed Long <[login to unmask email]> 2006-12-05 11:38:59 >>>

I am trying to produce a set of average and max CPU times for each of
60 trace periods. My expected result set should include the date
followed by the average and max cpu times.

After several hours of reviewing the reference and programming guides,
consulting Craig's book, and experiments, I am about to give up and code
a DSNTIAUL and send this data to SAS where this is 10 seconds of work.

The -206 is on I.IRD. All suggestions will be most appreciated.
Thanks
SELECT
DATE(A.TIMESTAMP) AS ARD,
I.AVGCPU ,
I.MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL A,
TABLE (
SELECT
DATE(I.TIMESTAMP) AS IRD,
AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,
MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL AS I
WHERE
TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY I.IRD
HAVING A.ARD = I.IRD
) AS IT
WHERE PRIMAUTH = 'MANNY'
AND TIMESTAMP > '2005-11-30-23.59.00.000000'
ORDER BY A.ARD
;


Edward Long
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Miller

Re: DB2 for z/OS Version 7 -206 huh? DB2PE
(in response to Hugh Lapham)
You can't reference A.ARD in a table expression. Move the HAVING to a
WHERE on the outer expression



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Ed Long
Sent: Tuesday, December 05, 2006 9:39 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS Version 7 -206 huh? DB2PE



I am trying to produce a set of average and max CPU times for each of 60
trace periods. My expected result set should include the date followed
by the average and max cpu times.



After several hours of reviewing the reference and programming guides,
consulting Craig's book, and experiments, I am about to give up and code
a DSNTIAUL and send this data to SAS where this is 10 seconds of work.



The -206 is on I.IRD. All suggestions will be most appreciated. Thanks

SELECT

DATE(A.TIMESTAMP) AS ARD,

I.AVGCPU ,

I.MAXCPU

FROM XYZSMF.DB2PMFACCT_GENERAL A,

TABLE (

SELECT

DATE(I.TIMESTAMP) AS IRD,

AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,

MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU

FROM XYZSMF.DB2PMFACCT_GENERAL AS I

WHERE

TIMESTAMP > '2005-11-30-23.59.00.000000'

GROUP BY I.IRD

HAVING A.ARD = I.IRD

) AS IT

WHERE PRIMAUTH = 'MANNY'

AND TIMESTAMP > '2005-11-30-23.59.00.000000'

ORDER BY A.ARD

;



Edward Long
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Edward Long

Re: DB2 for z/OS Version 7 -206 huh? DB2PE
(in response to John Miller)
Here is the cut down version suggested earlier:
SELECT
DATE(I.TIMESTAMP) AS IRD,
AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,
MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU
FROM THDSMF.DB2PMFACCT_GENERAL AS I
WHERE
TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY I.IRD
ORDER BY I.IRD
;
Still gets a -206 on I.IRD.


John Miller <[login to unmask email]> wrote:
You can’t reference A.ARD in a table expression. Move the HAVING to a WHERE on the outer expression


---------------------------------

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Ed Long
Sent: Tuesday, December 05, 2006 9:39 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS Version 7 -206 huh? DB2PE


I am trying to produce a set of average and max CPU times for each of 60 trace periods. My expected result set should include the date followed by the average and max cpu times.



After several hours of reviewing the reference and programming guides, consulting Craig's book, and experiments, I am about to give up and code a DSNTIAUL and send this data to SAS where this is 10 seconds of work.



The -206 is on I.IRD. All suggestions will be most appreciated. Thanks

SELECT

DATE(A.TIMESTAMP) AS ARD,

I.AVGCPU ,

I.MAXCPU

FROM XYZSMF.DB2PMFACCT_GENERAL A,

TABLE (

SELECT

DATE(I.TIMESTAMP) AS IRD,

AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,

MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU

FROM XYZSMF.DB2PMFACCT_GENERAL AS I

WHERE

TIMESTAMP > '2005-11-30-23.59.00.000000'

GROUP BY I.IRD

HAVING A.ARD = I.IRD

) AS IT

WHERE PRIMAUTH = 'MANNY'

AND TIMESTAMP > '2005-11-30-23.59.00.000000'

ORDER BY A.ARD

;



Edward Long --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


Edward Long

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: DB2 for z/OS Version 7 -206 huh? DB2PE
(in response to Edward Long)
You also can't reference ird in a group by or order by. if you need to do
that consider using a declared gtt - moving the data you need into it then
select from the dgtt using the group by & order by.

Bud Greenman
Applications Programming Manager



Ed Long
<[login to unmask email]
NET> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] DB2 for z/OS Version 7
-206 huh? DB2PE

12/05/2006 04:21
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






Here is the cut down version suggested earlier:
SELECT
DATE(I.TIMESTAMP) AS IRD,
AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,
MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU
FROM THDSMF.DB2PMFACCT_GENERAL AS I
WHERE
TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY I.IRD
ORDER BY I.IRD
;
Still gets a -206 on I.IRD.


John Miller <[login to unmask email]> wrote:
You can't reference A.ARD in a table expression. Move the HAVING to a
WHERE on the outer expression


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Ed Long
Sent: Tuesday, December 05, 2006 9:39 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS Version 7 -206 huh? DB2PE

I am trying to produce a set of average and max CPU times for each of 60
trace periods. My expected result set should include the date followed by
the average and max cpu times.

After several hours of reviewing the reference and programming guides,
consulting Craig's book, and experiments, I am about to give up and code a
DSNTIAUL and send this data to SAS where this is 10 seconds of work.

The -206 is on I.IRD. All suggestions will be most appreciated. Thanks
SELECT
DATE(A.TIMESTAMP) AS ARD,
I.AVGCPU ,
I.MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL A,
TABLE (
SELECT
DATE(I.TIMESTAMP) AS IRD,
AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,
MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL AS I
WHERE
TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY I.IRD
HAVING A.ARD = I.IRD
) AS IT
WHERE PRIMAUTH = 'MANNY'
AND TIMESTAMP > '2005-11-30-23.59.00.000000'
ORDER BY A.ARD
;


Edward Long
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


The information transmitted is intended only for the addressee and may
contain confidential, proprietary and/or privileged material. Any
unauthorized review, distribution or other use of or the taking of any
action in reliance upon this information is prohibited. If you receive
this in error, please contact the sender and delete or destroy this
message and any copies.
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



Edward Long
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

Larry Jardine

Re: DB2 for z/OS Version 7 -206 huh? DB2PE
(in response to BudGreenman@ONGOV.NET)
There is no I.IRD. Try just IRD.

Larry Jardine
Production DBA


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Ed Long
Sent: Tuesday, December 05, 2006 4:22 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS Version 7 -206 huh? DB2PE


Here is the cut down version suggested earlier:
SELECT
DATE(I.TIMESTAMP) AS IRD,
AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,
MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU
FROM THDSMF.DB2PMFACCT_GENERAL AS I
WHERE
TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY I.IRD
ORDER BY I.IRD
;
Still gets a -206 on I.IRD.


John Miller <[login to unmask email]> wrote:

You can't reference A.ARD in a table expression. Move the
HAVING to a WHERE on the outer expression


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Ed Long
Sent: Tuesday, December 05, 2006 9:39 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS Version 7 -206 huh? DB2PE

I am trying to produce a set of average and max CPU times for
each of 60 trace periods. My expected result set should include the date
followed by the average and max cpu times.

After several hours of reviewing the reference and programming
guides, consulting Craig's book, and experiments, I am about to give up
and code a DSNTIAUL and send this data to SAS where this is 10 seconds
of work.

The -206 is on I.IRD. All suggestions will be most
appreciated. Thanks
SELECT
DATE(A.TIMESTAMP) AS ARD,
I.AVGCPU ,
I.MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL A,
TABLE (
SELECT
DATE(I.TIMESTAMP) AS IRD,
AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,
MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU
FROM XYZSMF.DB2PMFACCT_GENERAL AS I
WHERE
TIMESTAMP > '2005-11-30-23.59.00.000000'
GROUP BY I.IRD
HAVING A.ARD = I.IRD
) AS IT
WHERE PRIMAUTH = 'MANNY'
AND TIMESTAMP > '2005-11-30-23.59.00.000000'
ORDER BY A.ARD
;


Edward Long
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



The information transmitted is intended only for the addressee
and may contain confidential, proprietary and/or privileged material.
Any unauthorized review, distribution or other use of or the taking of
any action in reliance upon this information is prohibited. If you
receive this in error, please contact the sender and delete or destroy
this message and any copies.

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm




Edward Long
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



-----------------------------------------
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Miller

Re: DB2 for z/OS Version 7 -206 huh? DB2PE
(in response to Larry Jardine)
Ed,



Sorry I should have looked at it closer.



IRD is not a column in I (THDSMF.DB2PMFACCT_GENERAL). It is an alias (a
name assigned) to a selected column (in this case DATE(I.TIMESTAMP))



You can GROUP BY IRD

ORDER BY IRD

Or GROUP BY 1

ORDER BY 1



Hope that helps,

John





________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Ed Long
Sent: Tuesday, December 05, 2006 2:22 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS Version 7 -206 huh? DB2PE



Here is the cut down version suggested earlier:

SELECT

DATE(I.TIMESTAMP) AS IRD,

AVG(I.CLASS1_CPU_TOTAL) AS AVGCPU,

MAX(I.CLASS1_CPU_TOTAL) AS MAXCPU

FROM THDSMF.DB2PMFACCT_GENERAL AS I

WHERE

TIMESTAMP > '2005-11-30-23.59.00.000000'

GROUP BY I.IRD

ORDER BY I.IRD

;

Still gets a -206 on I.IRD.



John Miller <[login to unmask email]> wrote:

You can't reference A.ARD in a table expression. Move the
HAVING to a WHERE on the outer expression






---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm