[DB2-L] DB2 for z/OS - sql query required

Walter Janißen

[DB2-L] DB2 for z/OS - sql query required
Or try

SELECT NAME, NTABLES
FROM SYSIBM.SYSTABLESPACE
WHERE NTABLES > 1

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Phil Grainger
Gesendet: Freitag, 26. November 2010 15:30
An: [login to unmask email]
Betreff: Re: [DB2-L] DB2 for z/OS - sql query required

Sorry

Try

SELECT TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY TSNAME
HAVING COUNT(*) > 1
ORDER BY TSNAME

Got the HAVING clause in the wrong place
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: Phil Grainger
Sent: 26 November 2010 14:21
To: 'IDUG DB2-L'
Subject: RE: [DB2-L] DB2 for z/OS - sql query required

SELECT TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY TSNAME
ORDER BY TSNAME
HAVING COUNT(*) > 1

I don't have access to DB2 right now so the clauses may not be in the right order, but you'll get the picture I'm sure

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

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: 26 November 2010 14:11
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS - sql query required

What is the easiest way to identify tablespaces that contain more than 1 table.

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 >

________________________________

[ 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

Phil Grainger

Re: DB2 for z/OS - sql query required
(in response to Walter Janißen)
Yeah yeah - I showed "my" alternative as it has always stuck in my mind as the first (and just about only) SQL statement I ever wrote with a "HAVING" clause :)

Happy Friday/Thanksgiving
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Cuneyt Goksu
Sent: 26 November 2010 14:16
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS - sql query required



SELECT * FROM SYSIBM.SYSTABLESPACE WHERE NTABLES > 1;

Regards,
Cuneyt
-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email]<mailto:[login to unmask email]>] On Behalf Of Jim McAlpine
Sent: Friday, November 26, 2010 4:11 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] DB2 for z/OS - sql query required
What is the easiest way to identify tablespaces that contain more than 1 table.

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 >

________________________________

[ 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

Jim McAlpine

Re: DB2 for z/OS - sql query required
(in response to Phil Grainger)
OK people, I've got the message now. Thanks to all.

Jim McAlpine

On Fri, Nov 26, 2010 at 2:18 PM, Sevetson, Phil <[login to unmask email]>wrote:

> SELECT DBNAME, NAME FROM SYSIBM.SYSTABLESPACE WHERE NTABLES > 1
>
>
> ------------------------------
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Jim
> McAlpine
> *Sent:* Friday, November 26, 2010 9:11 AM
>
> *To:* [login to unmask email]
> *Subject:* [DB2-L] DB2 for z/OS - sql query required
>
>
>
> What is the easiest way to identify tablespaces that contain more than 1
> table.
>
>
>
> Jim McAlpine
>
>
> ------------------------------
>
> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < 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 >
>
> ------------------------------
>
> [image: Independent, not-for-profit, User Run - the IDUG difference! ] < 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

Phil Grainger

Re: DB2 for z/OS - sql query required
(in response to Jim McAlpine)
2nd correction (Thanks Raymond at BMC)

What I meant to say was

SELECT DBNAME, TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY DBNAME, TSNAME
HAVING COUNT(*) > 1
ORDER BY DBNAME, TSNAME

DBNAME.TSNAME is unique - TSNAME alone isn't

Sorry
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic


From: Phil Grainger
Sent: 26 November 2010 14:30
To: 'IDUG DB2-L'
Subject: RE: [DB2-L] DB2 for z/OS - sql query required

Sorry

Try

SELECT TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY TSNAME
HAVING COUNT(*) > 1
ORDER BY TSNAME

Got the HAVING clause in the wrong place
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: Phil Grainger
Sent: 26 November 2010 14:21
To: 'IDUG DB2-L'
Subject: RE: [DB2-L] DB2 for z/OS - sql query required

SELECT TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY TSNAME
ORDER BY TSNAME
HAVING COUNT(*) > 1

I don't have access to DB2 right now so the clauses may not be in the right order, but you'll get the picture I'm sure

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

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: 26 November 2010 14:11
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS - sql query required

What is the easiest way to identify tablespaces that contain more than 1 table.

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

Raymond Bell

Re: DB2 for z/OS - sql query required
(in response to Phil Grainger)
I taught him everything I know. It was a short discussion...


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: 26 November 2010 15:51
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for z/OS - sql query required

2nd correction (Thanks Raymond at BMC)

What I meant to say was

SELECT DBNAME, TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY DBNAME, TSNAME
HAVING COUNT(*) > 1
ORDER BY DBNAME, TSNAME

DBNAME.TSNAME is unique - TSNAME alone isn't

Sorry
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: Phil Grainger
Sent: 26 November 2010 14:30
To: 'IDUG DB2-L'
Subject: RE: [DB2-L] DB2 for z/OS - sql query required

Sorry

Try

SELECT TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY TSNAME
HAVING COUNT(*) > 1
ORDER BY TSNAME

Got the HAVING clause in the wrong place
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk <blocked:: http://www.cogito.co.uk >

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: Phil Grainger
Sent: 26 November 2010 14:21
To: 'IDUG DB2-L'
Subject: RE: [DB2-L] DB2 for z/OS - sql query required

SELECT TSNAME, COUNT(*)
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
GROUP BY TSNAME
ORDER BY TSNAME
HAVING COUNT(*) > 1

I don't have access to DB2 right now so the clauses may not be in the right order, but you'll get the picture I'm sure

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

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America < http://www.idug.org/na > , 2-6 May, Anaheim California
IDUG EMEA < http://www.idug.org/emea > , 14-18 November, Prague Czech Republic

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: 26 November 2010 14:11
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS - sql query required

What is the easiest way to identify tablespaces that contain more than 1 table.

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 >

________________________________

[ 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

Brenda Boshoff

Re: DB2 for z/OS - sql query required
(in response to Raymond Bell)
Hi,
I normally check SYSIBM.SYSTABLESPACE’s NTABLES column.
select * from sysibm.systablespace
where ntables > 0

I would add more filter factors, but basically look at column NTABLES.

Regards,
Brenda Boshoff

What is the easiest way to identify tablespaces that contain more than 1 table.

Jim McAlpine

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** 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