SQL Optimization Question

Dan Michaelis

SQL Optimization Question
Brian,

I'll take a shot... I don't know whether this would be faster or not, but
would it be possible to have your list of several hundred values inserted
into a temp table, and then join the temp table and your real table to get
the results? If you're returning a majority of the rows in your 5M row
table, the "in" clause might force you to use an index where you don't want
to; it might be faster to do a hash join on the temp table than it is to do
the indexed lookup through the values.

It might not be any faster (and might well be slower), but it's a different
approach.

Dan Michaelis

Database Administrator/Developer
eOriginal
351 West Camden Street
Suite 800
Baltimore, MD 21201

410.625.5187 (phone)
410.659.9799 (fax)


-----Original Message-----
From: Brian Stewart [mailto:[login to unmask email]
Sent: Wednesday, November 17, 2004 12:33 PM
To: [login to unmask email]
Subject: SQL Opitimization Question

Hello -

I have been asked to optimize a query that is bit unusual, and other
than indexes, I'm not sure what else can be done. Can anybody offer a
suggestion on how to improve the folowing so that it runs faster?

select A, B from table1 where C = 123 and D in (x,y,z) and E in (**a
list of several hundred values**)

Its the domain of several hundred that causes this query to run slow
where table1 has 5 million plus rows.

Much thanks in advance -

Brian

----------------------------------------------------------------------------
-----
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

bob riehle

sql question
(in response to Dan Michaelis)
Is it possible to group by a 'named' column? This user is getting and error in qmf when trying the group by.

How do I use the alias LD_Date in the group_by? I tried a whole bunch of things.

SELECT DISTINCT A.PU_DT,A.EXPTD_DLVR_DT,

DATE(MIN(TIMESTAMP(B.DISP_DT,B.DISP_TM))) AS "LD_DATE",
Count(A.ARBL_NBR) as Awbs



FROM D2PROD.SPA01_SHPT AS A LEFT JOIN D2PROD.SPA02_SHPT_DISP AS B
ON A.ARBL_NBR = B.ARBL_NBR
AND A.PU_DT = B.PU_DT
AND A.ORGN_TRF_STA = B.ORGN_TRF_STA
AND B.DISP_CD IN ('LD','RF','AD','PA','HA','PT')


WHERE A.PU_DT BETWEEN &PUDATE1 AND &PUDATE2
AND A.DLVR_SVC_CD = 'SDS'
AND A.DEST_ZIP_CLR_CD ='R'
AND (A.ABX_FORM_TYP_CD = 'A' OR A.ABX_FORM_TYP_CD IS NULL)
AND ORGN_CTL_STA NOT IN ('ANC', 'HNL','SJU','STT')



GROUP BY A.PU_DT,A.EXPTD_DLVR_DT, "LD_DATE"



FOR FETCH ONLY
WITH UR

Thanks
Bob Riehle

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Raquel Rodriguez
Sent: Thursday, December 02, 2004 10:58 AM
To: [login to unmask email]
Subject: Re: DSNZPARM basic doubt ....


Thanks to Rob Crane, Cathy, Avram, Jeff Frazier,
Venkatesh, Tina Hilton,
Bob Jeandron and Robin for your extremely helpful
insights. I was able
to generate the new DSNZPARM.

The Sysprog will not be here for the next one month. I
needed to change
the CACHEDYN parameter from NO to YES. The reason for
this urgent
change
request is because lately, our developers are
performing QMF queries
almost round the clock and as a result, the DBAs are
not able to run
any
DDLs against any tablespace on those databases. With
CACHEDYN=YES, for
the most part, QMF queries do not lock DBD in Shared
mode and so, we
(DBAs) are hopeful that we will be able to run the
DDLs. And yes, I
will
monitor EDMPOOL usage to see if this has any untoward
influence.

One related question. As suggested by esteemed
listers, I changed
CACHEDYN using the Panels and this resulted in a new
DSNTID1A member in
DSN710.SDSNSAMP. Then I compared this newly generated
DSNTID1A member
with the 'original' DSNTID1A member (DSNTID1A member
before the
change).
As expected, the comparison showed that CACHEDYN was
changed from NO to
YES, but in addition, it showed the following
differences:

I - OVEDMDS CHAR M NONE NONE NULL

D - OVEDMDS CHAR M NONE NONE 0

I - OVEDMDSM CHAR M NONE NONE NULL

D - OVEDMDSM CHAR M NONE NONE 0


OVEDMDS and OVEDMDSM were NULL in the original
DSNTID1A and these
changed to 0 in the newly generated DSNTID1A. Does
anyone know what
OVEDMDS and OVEDMDSM are? No entries for OVEDMDS and
OVEDMDSM were
found
in the generated DSNTIJUZ members; also couldn't find
any references
for
these parameters in the DB2 bookshelf.

TIA
Raquel.

--- Robin WIlloughby <[login to unmask email]>
wrote:

> and just to add to all the belt and braces good
> advice you may want to run
> something like stored procedure DSNWZP or have a
> look in your DB2 monitor
> and record your current running Zparms for the
> subsystem....(there's
> guarantee what you think is the source for your
> Zparms actually is the
> source of what is running )
>
> cheers,
>
> Robin
>
>
---------------------------------------------------------------------------------
> 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
>


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------------------------------------------------------------
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

Mike Polley

Re: sql question
(in response to bob riehle)
Bob, here's something from the Application Programming and SQL Guide that
might do it for you. HTH

GROUP BY clause using a expression: The following statement
| lists, for each department, the lowest and highest education level within
| that department and groups the results by the highest education level:




| SELECT WORKDEPT, MIN(EDLEVEL), MAX(EDLEVEL)
| FROM DSN8810.EMP
| GROUP BY MAX(EDLEVEL);




-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Robert Riehle (DHL US)
Sent: Monday, December 06, 2004 12:49 PM
To: [login to unmask email]
Subject: sql question


Is it possible to group by a 'named' column? This user is getting and error
in qmf when trying the group by.

How do I use the alias LD_Date in the group_by? I tried a whole bunch of
things.

SELECT DISTINCT A.PU_DT,A.EXPTD_DLVR_DT,

DATE(MIN(TIMESTAMP(B.DISP_DT,B.DISP_TM))) AS "LD_DATE",
Count(A.ARBL_NBR) as Awbs



FROM D2PROD.SPA01_SHPT AS A LEFT JOIN D2PROD.SPA02_SHPT_DISP AS B
ON A.ARBL_NBR = B.ARBL_NBR
AND A.PU_DT = B.PU_DT
AND A.ORGN_TRF_STA = B.ORGN_TRF_STA
AND B.DISP_CD IN ('LD','RF','AD','PA','HA','PT')


WHERE A.PU_DT BETWEEN &PUDATE1 AND &PUDATE2
AND A.DLVR_SVC_CD = 'SDS'
AND A.DEST_ZIP_CLR_CD ='R'
AND (A.ABX_FORM_TYP_CD = 'A' OR A.ABX_FORM_TYP_CD IS NULL)
AND ORGN_CTL_STA NOT IN ('ANC', 'HNL','SJU','STT')



GROUP BY A.PU_DT,A.EXPTD_DLVR_DT, "LD_DATE"



FOR FETCH ONLY
WITH UR

Thanks
Bob Riehle

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Raquel Rodriguez
Sent: Thursday, December 02, 2004 10:58 AM
To: [login to unmask email]
Subject: Re: DSNZPARM basic doubt ....


Thanks to Rob Crane, Cathy, Avram, Jeff Frazier,
Venkatesh, Tina Hilton,
Bob Jeandron and Robin for your extremely helpful
insights. I was able
to generate the new DSNZPARM.

The Sysprog will not be here for the next one month. I
needed to change
the CACHEDYN parameter from NO to YES. The reason for
this urgent
change
request is because lately, our developers are
performing QMF queries
almost round the clock and as a result, the DBAs are
not able to run
any
DDLs against any tablespace on those databases. With
CACHEDYN=YES, for
the most part, QMF queries do not lock DBD in Shared
mode and so, we
(DBAs) are hopeful that we will be able to run the
DDLs. And yes, I
will
monitor EDMPOOL usage to see if this has any untoward
influence.

One related question. As suggested by esteemed
listers, I changed
CACHEDYN using the Panels and this resulted in a new
DSNTID1A member in
DSN710.SDSNSAMP. Then I compared this newly generated
DSNTID1A member
with the 'original' DSNTID1A member (DSNTID1A member
before the
change).
As expected, the comparison showed that CACHEDYN was
changed from NO to
YES, but in addition, it showed the following
differences:

I - OVEDMDS CHAR M NONE NONE NULL

D - OVEDMDS CHAR M NONE NONE 0

I - OVEDMDSM CHAR M NONE NONE NULL

D - OVEDMDSM CHAR M NONE NONE 0


OVEDMDS and OVEDMDSM were NULL in the original
DSNTID1A and these
changed to 0 in the newly generated DSNTID1A. Does
anyone know what
OVEDMDS and OVEDMDSM are? No entries for OVEDMDS and
OVEDMDSM were
found
in the generated DSNTIJUZ members; also couldn't find
any references
for
these parameters in the DB2 bookshelf.

TIA
Raquel.

--- Robin WIlloughby <[login to unmask email]>
wrote:

> and just to add to all the belt and braces good
> advice you may want to run
> something like stored procedure DSNWZP or have a
> look in your DB2 monitor
> and record your current running Zparms for the
> subsystem....(there's
> guarantee what you think is the source for your
> Zparms actually is the
> source of what is running )
>
> cheers,
>
> Robin
>
>
----------------------------------------------------------------------------
-----
> 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
>


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

----------------------------------------------------------------------------
-----
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



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
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

Dave Nance

Re: sql question
(in response to Mike Polley)
Bob,
Shouldn't the grouping just be A.PU_DT,A.EXPTD_DLVR_DT? Since you
are doing a min function that column should not be included in the
grouping. But to answer your question you can not use a named value or
the number of the column as you can in an order by clause. So, in your
case, if there wasn't a min being performed on the "LD_DATE", your
grouping would be A.PU_DT,A.EXPTD_DLVR_DT, B.DISP_DT,B.DISP_TM.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/6/04 12:49:17 PM >>>
Is it possible to group by a 'named' column? This user is getting and
error in qmf when trying the group by.

How do I use the alias LD_Date in the group_by? I tried a whole bunch
of things.

SELECT DISTINCT A.PU_DT,A.EXPTD_DLVR_DT,

DATE(MIN(TIMESTAMP(B.DISP_DT,B.DISP_TM))) AS "LD_DATE",
Count(A.ARBL_NBR) as Awbs



FROM D2PROD.SPA01_SHPT AS A LEFT JOIN D2PROD.SPA02_SHPT_DISP AS B
ON A.ARBL_NBR = B.ARBL_NBR
AND A.PU_DT = B.PU_DT
AND A.ORGN_TRF_STA = B.ORGN_TRF_STA
AND B.DISP_CD IN ('LD','RF','AD','PA','HA','PT')


WHERE A.PU_DT BETWEEN &PUDATE1 AND &PUDATE2
AND A.DLVR_SVC_CD = 'SDS'
AND A.DEST_ZIP_CLR_CD ='R'
AND (A.ABX_FORM_TYP_CD = 'A' OR A.ABX_FORM_TYP_CD IS NULL)
AND ORGN_CTL_STA NOT IN ('ANC', 'HNL','SJU','STT')



GROUP BY A.PU_DT,A.EXPTD_DLVR_DT, "LD_DATE"



FOR FETCH ONLY
WITH UR

Thanks
Bob Riehle

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Raquel Rodriguez
Sent: Thursday, December 02, 2004 10:58 AM
To: [login to unmask email]
Subject: Re: DSNZPARM basic doubt ....


Thanks to Rob Crane, Cathy, Avram, Jeff Frazier,
Venkatesh, Tina Hilton,
Bob Jeandron and Robin for your extremely helpful
insights. I was able
to generate the new DSNZPARM.

The Sysprog will not be here for the next one month. I
needed to change
the CACHEDYN parameter from NO to YES. The reason for
this urgent
change
request is because lately, our developers are
performing QMF queries
almost round the clock and as a result, the DBAs are
not able to run
any
DDLs against any tablespace on those databases. With
CACHEDYN=YES, for
the most part, QMF queries do not lock DBD in Shared
mode and so, we
(DBAs) are hopeful that we will be able to run the
DDLs. And yes, I
will
monitor EDMPOOL usage to see if this has any untoward
influence.

One related question. As suggested by esteemed
listers, I changed
CACHEDYN using the Panels and this resulted in a new
DSNTID1A member in
DSN710.SDSNSAMP. Then I compared this newly generated
DSNTID1A member
with the 'original' DSNTID1A member (DSNTID1A member
before the
change).
As expected, the comparison showed that CACHEDYN was
changed from NO to
YES, but in addition, it showed the following
differences:

I - OVEDMDS CHAR M NONE NONE NULL

D - OVEDMDS CHAR M NONE NONE 0

I - OVEDMDSM CHAR M NONE NONE NULL

D - OVEDMDSM CHAR M NONE NONE 0


OVEDMDS and OVEDMDSM were NULL in the original
DSNTID1A and these
changed to 0 in the newly generated DSNTID1A. Does
anyone know what
OVEDMDS and OVEDMDSM are? No entries for OVEDMDS and
OVEDMDSM were
found
in the generated DSNTIJUZ members; also couldn't find
any references
for
these parameters in the DB2 bookshelf.

TIA
Raquel.

--- Robin WIlloughby <[login to unmask email]>
wrote:

> and just to add to all the belt and braces good
> advice you may want to run
> something like stored procedure DSNWZP or have a
> look in your DB2 monitor
> and record your current running Zparms for the
> subsystem....(there's
> guarantee what you think is the source for your
> Zparms actually is the
> source of what is running )
>
> cheers,
>
> Robin
>
>
---------------------------------------------------------------------------------
> 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
>


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------------------------------------------------------------
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



"MMS <fhmail.firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
=====

---------------------------------------------------------------------------------
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

Brian Goldberg

Re: sql question
(in response to Dave Nance)
When I need to do stuff like this, I create all the named columns in a
table expression, along with any other columns I need, and then you can
group by them.

This is also real handy if you need to group by something that doesn't
exist, like the year/month of a timestamp column, etc.

Brian



"Robert Riehle
(DHL US)" To: [login to unmask email]
<[login to unmask email] cc: (bcc: BRIAN GOLDBERG/QVC)
OM> Subject: sql question
Ext:
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
ORG>


12/06/2004 12:49
PM
Please respond to
DB2 Database
Discussion list
at IDUG






Is it possible to group by a 'named' column? This user is getting and error
in qmf when trying the group by.

How do I use the alias LD_Date in the group_by? I tried a whole bunch of
things.

SELECT DISTINCT A.PU_DT,A.EXPTD_DLVR_DT,

DATE(MIN(TIMESTAMP(B.DISP_DT,B.DISP_TM))) AS "LD_DATE",
Count(A.ARBL_NBR) as Awbs



FROM D2PROD.SPA01_SHPT AS A LEFT JOIN D2PROD.SPA02_SHPT_DISP AS B
ON A.ARBL_NBR = B.ARBL_NBR
AND A.PU_DT = B.PU_DT
AND A.ORGN_TRF_STA = B.ORGN_TRF_STA
AND B.DISP_CD IN ('LD','RF','AD','PA','HA','PT')


WHERE A.PU_DT BETWEEN &PUDATE1 AND &PUDATE2
AND A.DLVR_SVC_CD = 'SDS'
AND A.DEST_ZIP_CLR_CD ='R'
AND (A.ABX_FORM_TYP_CD = 'A' OR A.ABX_FORM_TYP_CD IS NULL)
AND ORGN_CTL_STA NOT IN ('ANC', 'HNL','SJU','STT')



GROUP BY A.PU_DT,A.EXPTD_DLVR_DT, "LD_DATE"



FOR FETCH ONLY
WITH UR

Thanks
Bob Riehle

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Raquel Rodriguez
Sent: Thursday, December 02, 2004 10:58 AM
To: [login to unmask email]
Subject: Re: DSNZPARM basic doubt ....


Thanks to Rob Crane, Cathy, Avram, Jeff Frazier,
Venkatesh, Tina Hilton,
Bob Jeandron and Robin for your extremely helpful
insights. I was able
to generate the new DSNZPARM.

The Sysprog will not be here for the next one month. I
needed to change
the CACHEDYN parameter from NO to YES. The reason for
this urgent
change
request is because lately, our developers are
performing QMF queries
almost round the clock and as a result, the DBAs are
not able to run
any
DDLs against any tablespace on those databases. With
CACHEDYN=YES, for
the most part, QMF queries do not lock DBD in Shared
mode and so, we
(DBAs) are hopeful that we will be able to run the
DDLs. And yes, I
will
monitor EDMPOOL usage to see if this has any untoward
influence.

One related question. As suggested by esteemed
listers, I changed
CACHEDYN using the Panels and this resulted in a new
DSNTID1A member in
DSN710.SDSNSAMP. Then I compared this newly generated
DSNTID1A member
with the 'original' DSNTID1A member (DSNTID1A member
before the
change).
As expected, the comparison showed that CACHEDYN was
changed from NO to
YES, but in addition, it showed the following
differences:

I - OVEDMDS CHAR M NONE NONE NULL

D - OVEDMDS CHAR M NONE NONE 0

I - OVEDMDSM CHAR M NONE NONE NULL

D - OVEDMDSM CHAR M NONE NONE 0


OVEDMDS and OVEDMDSM were NULL in the original
DSNTID1A and these
changed to 0 in the newly generated DSNTID1A. Does
anyone know what
OVEDMDS and OVEDMDSM are? No entries for OVEDMDS and
OVEDMDSM were
found
in the generated DSNTIJUZ members; also couldn't find
any references
for
these parameters in the DB2 bookshelf.

TIA
Raquel.

--- Robin WIlloughby <[login to unmask email]>
wrote:

> and just to add to all the belt and braces good
> advice you may want to run
> something like stored procedure DSNWZP or have a
> look in your DB2 monitor
> and record your current running Zparms for the
> subsystem....(there's
> guarantee what you think is the source for your
> Zparms actually is the
> source of what is running )
>
> cheers,
>
> Robin
>
>
---------------------------------------------------------------------------------

> 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
>


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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

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

---------------------------------------------------------------------------------
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

Terry Purcell

Re: sql question
(in response to Brian Goldberg)
Bob,

I agree with Dave. This SQL is logically incorrect.

In V8 you can code an expression in your GROUP BY, but in V7 you need a
nested table expression (with the expression inside, and the GROUP BY
outside) as Brian mentioned.

But if you put the MIN inside the NTE, you will also require a GROUP BY
inside the NTE.......so basically it is not possible.

Once the column is included in the GROUP BY, each occurrence of the group
is unique, so applying a MIN to a unique value within the group is
redundant.

A few other points, the DISTINCT is also redundant, and the user is
converting a date & time into a timestamp, obtaining the minimum and then
converting it back to a date.

I have to admit I am not really sure what this user is trying to do with
this SQL.

Regards
Terry

On Mon, 6 Dec 2004 14:04:23 -0500, David Nance <[login to unmask email]> wrote:

>Bob,
> Shouldn't the grouping just be A.PU_DT,A.EXPTD_DLVR_DT? Since you
>are doing a min function that column should not be included in the
>grouping. But to answer your question you can not use a named value or
>the number of the column as you can in an order by clause. So, in your
>case, if there wasn't a min being performed on the "LD_DATE", your
>grouping would be A.PU_DT,A.EXPTD_DLVR_DT, B.DISP_DT,B.DISP_TM.
>
>Dave Nance
>First Health Services, Corp.
>(804)527-6841
>
>
>>>> [login to unmask email] 12/6/04 12:49:17 PM >>>
>Is it possible to group by a 'named' column? This user is getting and
>error in qmf when trying the group by.
>
>How do I use the alias LD_Date in the group_by? I tried a whole bunch
>of things.
>
>SELECT DISTINCT A.PU_DT,A.EXPTD_DLVR_DT,
>
>DATE(MIN(TIMESTAMP(B.DISP_DT,B.DISP_TM))) AS "LD_DATE",
>Count(A.ARBL_NBR) as Awbs
>
>
>
>FROM D2PROD.SPA01_SHPT AS A LEFT JOIN D2PROD.SPA02_SHPT_DISP AS B
> ON A.ARBL_NBR = B.ARBL_NBR
> AND A.PU_DT = B.PU_DT
> AND A.ORGN_TRF_STA = B.ORGN_TRF_STA
> AND B.DISP_CD IN ('LD','RF','AD','PA','HA','PT')
>
>
>WHERE A.PU_DT BETWEEN &PUDATE1 AND &PUDATE2
>AND A.DLVR_SVC_CD = 'SDS'
>AND A.DEST_ZIP_CLR_CD ='R'
>AND (A.ABX_FORM_TYP_CD = 'A' OR A.ABX_FORM_TYP_CD IS NULL)
>AND ORGN_CTL_STA NOT IN ('ANC', 'HNL','SJU','STT')
>
>
>
>GROUP BY A.PU_DT,A.EXPTD_DLVR_DT, "LD_DATE"
>
>
>
>FOR FETCH ONLY
>WITH UR
>
>Thanks
>Bob Riehle

---------------------------------------------------------------------------------
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

Haldur Johansen

Re: sql question
(in response to Terry Purcell)
Hello Bob,

One way to solve the problem could be:

SELECT T.PU_DT, T.EXPTD_DLVR_DT, T.LD_DATE, T.Awbs
FROM
(
SELECT DISTINCT A.PU_DT,A.EXPTD_DLVR_DT,
DATE(MIN(TIMESTAMP(B.DISP_DT,B.DISP_TM))) AS "LD_DATE",
Count(A.ARBL_NBR) as Awbs



FROM D2PROD.SPA01_SHPT AS A LEFT JOIN D2PROD.SPA02_SHPT_DISP AS B
ON A.ARBL_NBR = B.ARBL_NBR
AND A.PU_DT = B.PU_DT
AND A.ORGN_TRF_STA = B.ORGN_TRF_STA
AND B.DISP_CD IN ('LD','RF','AD','PA','HA','PT')


WHERE A.PU_DT BETWEEN &PUDATE1 AND &PUDATE2
AND A.DLVR_SVC_CD = 'SDS'
AND A.DEST_ZIP_CLR_CD ='R'
AND (A.ABX_FORM_TYP_CD = 'A' OR A.ABX_FORM_TYP_CD IS NULL)
AND ORGN_CTL_STA NOT IN ('ANC', 'HNL','SJU','STT')
) AS T



GROUP BY T.PU_DT, T.EXPTD_DLVR_DT, T.LD_DATE


Best Regards,

Haldur Johansen
- - - - - - - - - - - - - - - - - -
System Programmer DB2
Elektron




"Robert Riehle
\(DHL US\)"
<[login to unmask email] To
OM> <[login to unmask email]>
Sent by: "DB2 cc
Data Base
Discussion List" Subject
<[login to unmask email] sql question
ORG>


06-12-2004 17.49


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






Is it possible to group by a 'named' column? This user is getting and error
in qmf when trying the group by.

How do I use the alias LD_Date in the group_by? I tried a whole bunch of
things.

SELECT DISTINCT A.PU_DT,A.EXPTD_DLVR_DT,

DATE(MIN(TIMESTAMP(B.DISP_DT,B.DISP_TM))) AS "LD_DATE",
Count(A.ARBL_NBR) as Awbs



FROM D2PROD.SPA01_SHPT AS A LEFT JOIN D2PROD.SPA02_SHPT_DISP AS B
ON A.ARBL_NBR = B.ARBL_NBR
AND A.PU_DT = B.PU_DT
AND A.ORGN_TRF_STA = B.ORGN_TRF_STA
AND B.DISP_CD IN ('LD','RF','AD','PA','HA','PT')


WHERE A.PU_DT BETWEEN &PUDATE1 AND &PUDATE2
AND A.DLVR_SVC_CD = 'SDS'
AND A.DEST_ZIP_CLR_CD ='R'
AND (A.ABX_FORM_TYP_CD = 'A' OR A.ABX_FORM_TYP_CD IS NULL)
AND ORGN_CTL_STA NOT IN ('ANC', 'HNL','SJU','STT')



GROUP BY A.PU_DT,A.EXPTD_DLVR_DT, "LD_DATE"



FOR FETCH ONLY
WITH UR

Thanks
Bob Riehle

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Raquel Rodriguez
Sent: Thursday, December 02, 2004 10:58 AM
To: [login to unmask email]
Subject: Re: DSNZPARM basic doubt ....


Thanks to Rob Crane, Cathy, Avram, Jeff Frazier,
Venkatesh, Tina Hilton,
Bob Jeandron and Robin for your extremely helpful
insights. I was able
to generate the new DSNZPARM.

The Sysprog will not be here for the next one month. I
needed to change
the CACHEDYN parameter from NO to YES. The reason for
this urgent
change
request is because lately, our developers are
performing QMF queries
almost round the clock and as a result, the DBAs are
not able to run
any
DDLs against any tablespace on those databases. With
CACHEDYN=YES, for
the most part, QMF queries do not lock DBD in Shared
mode and so, we
(DBAs) are hopeful that we will be able to run the
DDLs. And yes, I
will
monitor EDMPOOL usage to see if this has any untoward
influence.

One related question. As suggested by esteemed
listers, I changed
CACHEDYN using the Panels and this resulted in a new
DSNTID1A member in
DSN710.SDSNSAMP. Then I compared this newly generated
DSNTID1A member
with the 'original' DSNTID1A member (DSNTID1A member
before the
change).
As expected, the comparison showed that CACHEDYN was
changed from NO to
YES, but in addition, it showed the following
differences:

I - OVEDMDS CHAR M NONE NONE NULL

D - OVEDMDS CHAR M NONE NONE 0

I - OVEDMDSM CHAR M NONE NONE NULL

D - OVEDMDSM CHAR M NONE NONE 0


OVEDMDS and OVEDMDSM were NULL in the original
DSNTID1A and these
changed to 0 in the newly generated DSNTID1A. Does
anyone know what
OVEDMDS and OVEDMDSM are? No entries for OVEDMDS and
OVEDMDSM were
found
in the generated DSNTIJUZ members; also couldn't find
any references
for
these parameters in the DB2 bookshelf.

TIA
Raquel.

--- Robin WIlloughby <[login to unmask email]>
wrote:

> and just to add to all the belt and braces good
> advice you may want to run
> something like stored procedure DSNWZP or have a
> look in your DB2 monitor
> and record your current running Zparms for the
> subsystem....(there's
> guarantee what you think is the source for your
> Zparms actually is the
> source of what is running )
>
> cheers,
>
> Robin
>
>
---------------------------------------------------------------------------------

> 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
>


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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

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

---------------------------------------------------------------------------------
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