DB2 for z/OS - sql select question

Jim McAlpine

DB2 for z/OS - sql select question
I've inherited some DSNREXX which contains the following select -

SQLSTMT = "SELECT",
" 'II' ",
" ,T.DBNAME",
" ,T.TSNAME",
" ,CHAR(T.NAME < http://t.name/ > )",

" FROM SYSIBM.SYSTABLES T ",
" ,SYSIBM.SYSTABLEPART P ",
" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
" AND T.CREATOR = 'Z30D' ",
" AND T.DBNAME BETWEEN 'Z30DDA01' AND 'Z30DDZ01' ",
" AND T.NAME < http://t.name/ > LIKE
'T%' ",
" AND T.TYPE = 'T' ",
" GROUP BY T.DBNAME ",
" ,T.TSNAME ",
" ,T.CARD ",
" ,T.NAME < http://t.name/ >
",
" ORDER BY T.DBNAME, T.TSNAME, T.CARD DESC, T.NAME < http://t.name/ >
",
" WITH UR "


and I'm trying to understand what the following clauses are trying to do -

" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
Any ideas why they are in there. If I remove them I get exactly the same
results.

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Raymond Bell

Re: DB2 for z/OS - sql select question
(in response to Jim McAlpine)
Are they trying to limit the query to where there's > 1 table in the tablespace? Or do you already have SQL for that?


Sorry, it is (Black) Friday after all. :o)

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: 26 November 2010 15:43
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS - sql select question

I've inherited some DSNREXX which contains the following select -

SQLSTMT = "SELECT",
" 'II' ",
" ,T.DBNAME",
" ,T.TSNAME",
" ,CHAR(T.NAME < http://t.name/ > )",
" FROM SYSIBM.SYSTABLES T ",
" ,SYSIBM.SYSTABLEPART P ",
" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
" AND T.CREATOR = 'Z30D' ",
" AND T.DBNAME BETWEEN 'Z30DDA01' AND 'Z30DDZ01' ",
" AND T.NAME < http://t.name/ > LIKE 'T%' ",
" AND T.TYPE = 'T' ",
" GROUP BY T.DBNAME ",
" ,T.TSNAME ",
" ,T.CARD ",
" ,T.NAME < http://t.name/ > ",
" ORDER BY T.DBNAME, T.TSNAME, T.CARD DESC, T.NAME < http://t.name/ > ",
" WITH UR "


and I'm trying to understand what the following clauses are trying to do -

" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
Any ideas why they are in there. If I remove them I get exactly the same results.

Jim McAlpine



________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Joe Geller

Re: DB2 for z/OS - sql select question
(in response to Raymond Bell)
You may get the same results, but I bet it runs a whole lot
longer.
Try removing " ,SYSIBM.SYSTABLEPART P and the Group BY
You should also get the same results and it will be a lot faster.

Basically, the two predicates you removed are the join criteria
between the two tables. But, you never reference any
columns in SYSTABLEPART (either in the select, where or group by
clauses) - so you don't need it at all.

Removing the join criteria joined each selected row in SYSTABLES to
every row in SYSTABLEPART (bad!), but the group by eliminated all
result rows except one per table - that's why you got the same
results.

Joe




I've inherited some DSNREXX which contains the following select -

SQLSTMT = "SELECT",
" 'II' ",
" ,T.DBNAME",
" ,T.TSNAME",
" ,CHAR(T.NAME)",
" FROM SYSIBM.SYSTABLES T ",
" ,SYSIBM.SYSTABLEPART P ",
" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
" AND T.CREATOR = 'Z30D' ",
" AND T.DBNAME BETWEEN 'Z30DDA01' AND 'Z30DDZ01' ",
" AND T.NAME LIKE 'T%' ",
" AND T.TYPE = 'T' ",
" GROUP BY T.DBNAME ",
" ,T.TSNAME ",
" ,T.CARD ",
" ,T.NAME ",
" ORDER BY T.DBNAME, T.TSNAME, T.CARD DESC, T.NAME ",
" WITH UR "


and I'm trying to understand what the following clauses are trying to do -

" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
Any ideas why they are in there. If I remove them I get exactly the same results.

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Phil Grainger

Re: DB2 for z/OS - sql select question
(in response to Joe Geller)
EXCEPT it's a Cartesian join if there is more than 1 row in SYSTABLEPART so you'd get multiple result rows (one per part)

Not sure that's what was wanted though

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Joe Geller
Sent: 26 November 2010 16:47
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS - sql select question

You may get the same results, but I bet it runs a whole lot
longer.
Try removing " ,SYSIBM.SYSTABLEPART P and the Group BY
You should also get the same results and it will be a lot faster.

Basically, the two predicates you removed are the join criteria
between the two tables. But, you never reference any
columns in SYSTABLEPART (either in the select, where or group by
clauses) - so you don't need it at all.

Removing the join criteria joined each selected row in SYSTABLES to
every row in SYSTABLEPART (bad!), but the group by eliminated all
result rows except one per table - that's why you got the same
results.

Joe




I've inherited some DSNREXX which contains the following select -

SQLSTMT = "SELECT",
" 'II' ",
" ,T.DBNAME",
" ,T.TSNAME",
" ,CHAR(T.NAME)",
" FROM SYSIBM.SYSTABLES T ",
" ,SYSIBM.SYSTABLEPART P ",
" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
" AND T.CREATOR = 'Z30D' ",
" AND T.DBNAME BETWEEN 'Z30DDA01' AND 'Z30DDZ01' ",
" AND T.NAME LIKE 'T%' ",
" AND T.TYPE = 'T' ",
" GROUP BY T.DBNAME ",
" ,T.TSNAME ",
" ,T.CARD ",
" ,T.NAME ",
" ORDER BY T.DBNAME, T.TSNAME, T.CARD DESC, T.NAME ",
" WITH UR "


and I'm trying to understand what the following clauses are trying to do -

" WHERE T.DBNAME = P.DBNAME ",
" AND T.TSNAME = P.TSNAME ",
Any ideas why they are in there. If I remove them I get exactly the same results.

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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 *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

teldb2kals

SQL question
(in response to Phil Grainger)
Hi listers,

Merry XMas to all of you.

Now, I am looking at a query that does two different grouping functions on the same table for the same criteria. To give a simplified example,

SELECT 1 FROM SYSIBM.SYSDUMMY1
WHERE 100 < (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')
AND 1 < (SELECT COUNT(DISTINCT TSNAME) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')
;

(Basically return me '1' if I have more than 100 tables for database DSNDB06 and more than 1 distinct tablespace for that database).

Can we combine the two subqueries so that it accesses SYSTABLES only once ?

This is a simplistic example of my actual problem where I have a much more complicated query, and I would like to avoid executing two subqueries which are very similar except for the grouping.

Thanks,

Regards,
Kals

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
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

Philip Sevetson

Re: SQL question
(in response to teldb2kals)
Kals,

Will this help?

WITH NEWCOUNTS AS
(
SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'DSNDB06'
)
SELECT 1 FROM NEWCOUNTS
WHERE NTABLES > 100 AND NTABLESPACE > 1

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

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
Sent: Monday, December 20, 2010 7:25 PM
To: [login to unmask email]
Subject: [DB2-L] SQL question

Hi listers,

Merry XMas to all of you.

Now, I am looking at a query that does two different grouping functions on the same table for the same criteria. To give a simplified example,

SELECT 1 FROM SYSIBM.SYSDUMMY1
WHERE 100 < (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')
AND 1 < (SELECT COUNT(DISTINCT TSNAME) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')
;

(Basically return me '1' if I have more than 100 tables for database DSNDB06 and more than 1 distinct tablespace for that database).

Can we combine the two subqueries so that it accesses SYSTABLES only once ?

This is a simplistic example of my actual problem where I have a much more complicated query, and I would like to avoid executing two subqueries which are very similar except for the grouping.

Thanks,

Regards,
Kals

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
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

teldb2kals

Re: SQL question
(in response to Philip Sevetson)
Thanks, Phil. I knew I should have included another caveat. I don't have too much control over the full SQL. I can only input the portion from the WHERE clause, as this is a vendor application that has got the SELECT 1 FROM SYSIBM.SYSDUMMY1 hardcoded as the first line.

Kals

On Mon, 20 Dec 2010 19:31:23 -0500, Sevetson, Phil <[login to unmask email]> wrote:

>Kals,
>
>Will this help?
>
>WITH NEWCOUNTS AS
>(
> SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
> FROM SYSIBM.SYSTABLES
> WHERE DBNAME = 'DSNDB06'
>)
>SELECT 1 FROM NEWCOUNTS
>WHERE NTABLES > 100 AND NTABLESPACE > 1
>
>****************************************************************
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
>Sent: Monday, December 20, 2010 7:25 PM
>To: [login to unmask email]
>Subject: [DB2-L] SQL question
>
>Hi listers,
>
>Merry XMas to all of you.
>
>Now, I am looking at a query that does two different grouping functions on the same table for the same criteria. To give a simplified example,
>
>SELECT 1 FROM SYSIBM.SYSDUMMY1
>WHERE 100 < (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')
>AND 1 < (SELECT COUNT(DISTINCT TSNAME) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')
>;
>
>(Basically return me '1' if I have more than 100 tables for database DSNDB06 and more than 1 distinct tablespace for that database).
>
>Can we combine the two subqueries so that it accesses SYSTABLES only once ?
>
>This is a simplistic example of my actual problem where I have a much more complicated query, and I would like to avoid executing two subqueries which are very similar except for the grouping.
>
>Thanks,
>
>Regards,
>Kals
>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
>* If you are going to attend only one conference this year, this is it! *
>_____________________________________________________________________
>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
>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
>* If you are going to attend only one conference this year, this is it! *
>_____________________________________________________________________
>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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
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

Dave Nance

Re: SQL question
(in response to teldb2kals)
Then what Phil gave you would work perfect. excuse the formatting, just cut and
pasted from your mails.

SELECT 1 FROM SYSIBM.SYSDUMMY1  
where 1 = (  Select 1 from
                                                        
( SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
 FROM SYSIBM.SYSTABLES                                      
WHERE DBNAME = 'DSNDB06'                                 
)   as newcounts                                                      
WHERE NTABLES > 100 AND NTABLESPACE > 1  )
 
David Nance




________________________________
From: Teldb2kals <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, December 20, 2010 8:16:10 PM
Subject: Re: [DB2-L] SQL question

Thanks, Phil. I knew I should have included another caveat. I don't have too
much control over the full SQL. I can only input the portion from the WHERE
clause, as this is a vendor application that has got the SELECT 1 FROM
SYSIBM.SYSDUMMY1 hardcoded as the first line.


Kals

On Mon, 20 Dec 2010 19:31:23 -0500, Sevetson, Phil <[login to unmask email]>
wrote:

>Kals,
>
>Will this help?
>
>WITH NEWCOUNTS AS                                         
>(                                                         
> SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
> FROM SYSIBM.SYSTABLES                                     
> WHERE DBNAME = 'DSNDB06'                                 
>)                                                         
>SELECT 1 FROM NEWCOUNTS                                   
>WHERE NTABLES > 100 AND NTABLESPACE > 1                   
>
>****************************************************************
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
>Sent: Monday, December 20, 2010 7:25 PM
>To: [login to unmask email]
>Subject: [DB2-L] SQL question
>
>Hi listers,
>
>Merry XMas to all of you.
>
>Now, I am looking at a query that does two different grouping functions on the
>same table for the same criteria. To give a simplified example,
>
>SELECT 1 FROM SYSIBM.SYSDUMMY1                           
>WHERE 100 < (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')   
>                           
>
>AND    1 < (SELECT  COUNT(DISTINCT TSNAME) FROM SYSIBM.SYSTABLES WHERE DBNAME =
>'DSNDB06')                             
>
>;                                                       
>
>(Basically return me '1' if I have more than 100 tables for database DSNDB06 and
>more than 1 distinct tablespace for that database).
>
>Can we combine the two subqueries so that it accesses SYSTABLES only once ?
>
>This is a simplistic example of my actual problem where I have a much more
>complicated query, and I would like to avoid executing two subqueries which are
>very similar except for the grouping.
>
>Thanks,
>
>Regards,
>Kals
>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA
>*
>*  If you are going to attend only one conference this year, this is it!  *
>_____________________________________________________________________
>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
>
>_____________________________________________________________________
>* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA
>*
>*  If you are going to attend only one conference this year, this is it!  *
>_____________________________________________________________________
>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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA
*
*  If you are going to attend only one conference this year, this is it!  *
_____________________________________________________________________
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




_____________________________________________________________________
* 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
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Bayard Tysor

Re: SQL question
(in response to Dave Nance)
Hi Kals,

This is a similar solution to Phil's, but uses a nested table expression
rather than a common table expression.

SELECT 1
FROM
SYSIBM.SYSDUMMY1
,(SELECT COUNT(*) AS TBCOUNT
,COUNT(DISTINCT TSNAME) AS TSCOUNT
FROM SYSIBM.SYSTABLES
) AS T
WHERE
100 < T.TBCOUNT AND 1 < T.TSCOUNT
;
Hope this helps,

Tink
On Mon, Dec 20, 2010 at 8:16 PM, Teldb2kals <[login to unmask email]> wrote:

> Thanks, Phil. I knew I should have included another caveat. I don't have
> too much control over the full SQL. I can only input the portion from the
> WHERE clause, as this is a vendor application that has got the SELECT 1 FROM
> SYSIBM.SYSDUMMY1 hardcoded as the first line.
>
> Kals
>
> On Mon, 20 Dec 2010 19:31:23 -0500, Sevetson, Phil <[login to unmask email]>
> wrote:
>
> >Kals,
> >
> >Will this help?
> >
> >WITH NEWCOUNTS AS
> >(
> > SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
> > FROM SYSIBM.SYSTABLES
> > WHERE DBNAME = 'DSNDB06'
> >)
> >SELECT 1 FROM NEWCOUNTS
> >WHERE NTABLES > 100 AND NTABLESPACE > 1
> >
> >****************************************************************
> >
> >-----Original Message-----
> >From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
> >Sent: Monday, December 20, 2010 7:25 PM
> >To: [login to unmask email]
> >Subject: [DB2-L] SQL question
> >
> >Hi listers,
> >
> >Merry XMas to all of you.
> >
> >Now, I am looking at a query that does two different grouping functions on
> the same table for the same criteria. To give a simplified example,
> >
> >SELECT 1 FROM SYSIBM.SYSDUMMY1
> >WHERE 100 < (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE DBNAME =
> 'DSNDB06')
> >AND 1 < (SELECT COUNT(DISTINCT TSNAME) FROM SYSIBM.SYSTABLES WHERE
> DBNAME = 'DSNDB06')
> >;
> >
> >(Basically return me '1' if I have more than 100 tables for database
> DSNDB06 and more than 1 distinct tablespace for that database).
> >
> >Can we combine the two subqueries so that it accesses SYSTABLES only once
> ?
> >
> >This is a simplistic example of my actual problem where I have a much more
> complicated query, and I would like to avoid executing two subqueries which
> are very similar except for the grouping.
> >
> >Thanks,
> >
> >Regards,
> >Kals
> >
> >_____________________________________________________________________
> >* IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA < http://idug.org/NA > *
> >* If you are going to attend only one conference this year, this is it!
> *
> >_____________________________________________________________________
> >http://www.IDUG.org/mentor < 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-Lis the home of IDUG's Listserv
> >
> >_____________________________________________________________________
> >* IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA < http://idug.org/NA > *
> >* If you are going to attend only one conference this year, this is it!
> *
> >_____________________________________________________________________
> >http://www.IDUG.org/mentor < 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-Lis the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA < http://idug.org/NA > *
> * If you are going to attend only one conference this year, this is it!
> *
> _____________________________________________________________________
> http://www.IDUG.org/mentor < 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
>



--
B.L. "Tink" Tysor
Bayard Lee Tysor, Inc.
[login to unmask email]
401-965-2688

_____________________________________________________________________
* 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
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

teldb2kals

Re: SQL question
(in response to Bayard Tysor)
Thanks, Dave. I had a feeling I was not thinking clearly. That works perfectly well.

Regards,
Kals
On Tue, 21 Dec 2010 03:47:43 -0800, Dave Nance <[login to unmask email]> wrote:

>Then what Phil gave you would work perfect. excuse the formatting, just cut and
pasted from your mails.

SELECT 1 FROM SYSIBM.SYSDUMMY1??
where 1 = (??Select 1 from
????????????????????????????????????????????????????????
( SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
?FROM SYSIBM.SYSTABLES??????????????????????????????????????
WHERE DBNAME = 'DSNDB06'?????????????????????????????????
)???as newcounts??????????????????????????????????????????????????????
WHERE NTABLES > 100 AND NTABLESPACE > 1??)
?
David Nance




________________________________
From: Teldb2kals <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, December 20, 2010 8:16:10 PM
Subject: Re: [DB2-L] SQL question

Thanks, Phil. I knew I should have included another caveat. I don't have too
much control over the full SQL. I can only input the portion from the WHERE
clause, as this is a vendor application that has got the SELECT 1 FROM
SYSIBM.SYSDUMMY1 hardcoded as the first line.


Kals

On Mon, 20 Dec 2010 19:31:23 -0500, Sevetson, Phil <[login to unmask email]>
wrote:

>Kals,
>
>Will this help?
>
>WITH NEWCOUNTS AS? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
>(? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
> SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
> FROM SYSIBM.SYSTABLES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
> WHERE DBNAME = 'DSNDB06'? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
>)? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
>SELECT 1 FROM NEWCOUNTS? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
>WHERE NTABLES > 100 AND NTABLESPACE > 1? ? ? ? ? ? ? ? ? ?
>
>****************************************************************
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
>Sent: Monday, December 20, 2010 7:25 PM
>To: [login to unmask email]
>Subject: [DB2-L] SQL question
>
>Hi listers,
>
>Merry XMas to all of you.
>
>Now, I am looking at a query that does two different grouping functions on the
>same table for the same criteria. To give a simplified example,
>
>SELECT 1 FROM SYSIBM.SYSDUMMY1? ? ? ? ? ? ? ? ? ? ? ? ? ?
>WHERE 100 < (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE DBNAME = 'DSNDB06')? ?
>? ? ? ? ? ? ? ? ? ? ? ? ? ?
>
>AND? ? 1 < (SELECT? COUNT(DISTINCT TSNAME) FROM SYSIBM.SYSTABLES WHERE DBNAME =
>'DSNDB06')? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
>
>;? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
>
>(Basically return me '1' if I have more than 100 tables for database DSNDB06 and
>more than 1 distinct tablespace for that database).
>
>Can we combine the two subqueries so that it accesses SYSTABLES only once ?
>
>This is a simplistic example of my actual problem where I have a much more
>complicated query, and I would like to avoid executing two subqueries which are
>very similar except for the grouping.
>
>Thanks,
>
>Regards,
>Kals
>

_____________________________________________________________________
* 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
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

teldb2kals

Re: SQL question
(in response to teldb2kals)
Thanks, Tink. But I have control only after the WHERE clause, not before. So I would not be able to code the NTE there. But it is something to keep in mind.

Regards,
Kals

On Tue, 21 Dec 2010 07:53:36 -0500, B. L. "Tink" Tysor <[login to unmask email]> wrote:

>Hi Kals,
>
>This is a similar solution to Phil's, but uses a nested table expression
>rather than a common table expression.
>
>SELECT 1
>FROM
> SYSIBM.SYSDUMMY1
> ,(SELECT COUNT(*) AS TBCOUNT
> ,COUNT(DISTINCT TSNAME) AS TSCOUNT
> FROM SYSIBM.SYSTABLES
> ) AS T
>WHERE
> 100 < T.TBCOUNT AND 1 < T.TSCOUNT
>;
>Hope this helps,
>
>Tink
>On Mon, Dec 20, 2010 at 8:16 PM, Teldb2kals <[login to unmask email]> wrote:
>
>> Thanks, Phil. I knew I should have included another caveat. I don't have
>> too much control over the full SQL. I can only input the portion from the
>> WHERE clause, as this is a vendor application that has got the SELECT 1 FROM
>> SYSIBM.SYSDUMMY1 hardcoded as the first line.
>>
>> Kals
>>
>> On Mon, 20 Dec 2010 19:31:23 -0500, Sevetson, Phil <[login to unmask email]>
>> wrote:
>>
>> >Kals,
>> >
>> >Will this help?
>> >
>> >WITH NEWCOUNTS AS
>> >(
>> > SELECT COUNT(*) NTABLES, COUNT(DISTINCT TSNAME) NTABLESPACE
>> > FROM SYSIBM.SYSTABLES
>> > WHERE DBNAME = 'DSNDB06'
>> >)
>> >SELECT 1 FROM NEWCOUNTS
>> >WHERE NTABLES > 100 AND NTABLESPACE > 1
>> >
>> >****************************************************************
>> >
>> >-----Original Message-----
>> >From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
>> >Sent: Monday, December 20, 2010 7:25 PM
>> >To: [login to unmask email]
>> >Subject: [DB2-L] SQL question
>> >
>> >Hi listers,
>> >
>> >Merry XMas to all of you.
>> >
>> >Now, I am looking at a query that does two different grouping functions on
>> the same table for the same criteria. To give a simplified example,
>> >
>> >SELECT 1 FROM SYSIBM.SYSDUMMY1
>> >WHERE 100 < (SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE DBNAME =
>> 'DSNDB06')
>> >AND 1 < (SELECT COUNT(DISTINCT TSNAME) FROM SYSIBM.SYSTABLES WHERE
>> DBNAME = 'DSNDB06')
>> >;
>> >
>> >(Basically return me '1' if I have more than 100 tables for database
>> DSNDB06 and more than 1 distinct tablespace for that database).
>> >
>> >Can we combine the two subqueries so that it accesses SYSTABLES only once
>> ?
>> >
>> >This is a simplistic example of my actual problem where I have a much more
>> complicated query, and I would like to avoid executing two subqueries which
>> are very similar except for the grouping.
>> >
>> >Thanks,
>> >
>> >Regards,
>> >Kals

>B.L. "Tink" Tysor
>Bayard Lee Tysor, Inc.
>[login to unmask email]
>401-965-2688
>

_____________________________________________________________________
* 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
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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