DB2 for z/OS - sql query required

Jim McAlpine

DB2 for z/OS - sql query required
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 *
* 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 Jim McAlpine)
A query on Systables, grouping by DBNAME, TSNAME where type='T' having count(*) > 1? Pseudo-SQL, obviously... ;o)


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

Cuneyt Goksu

Re: DB2 for z/OS - sql query required
(in response to Raymond Bell)
SELECT * FROM SYSIBM.SYSTABLESPACE WHERE NTABLES > 1;

Regards,
Cuneyt

-----Original Message-----
*From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Jim McAlpine
*Sent:* Friday, November 26, 2010 4:11 PM
*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 >

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

Philip Sevetson

Re: DB2 for z/OS - sql query required
(in response to Cuneyt Goksu)
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

________________________________

[ 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 Philip Sevetson)
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

Phil Grainger

Re: DB2 for z/OS - sql query required
(in response to Phil Grainger)
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

Jay Reavill

Re: DB2 for z/OS - sql query required
(in response to Phil Grainger)
There's also a column on SYSTABLESPACE called NTABLES that contains the
number of tables in a tablespace.



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Friday, November 26, 2010 9:30 AM
To: [login to unmask email]
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



________________________________

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 >


________________________________

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 >

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________

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

Sam Baugh

Re: DB2 for z/OS - sql query required
(in response to Jay Reavill)
Select name, ntables fron sysibm.systablespace where ntables > 1 order by name with ur

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Friday, November 26, 2010 8:30 AM
To: [login to unmask email]
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

Jim McAlpine

Re: DB2 for z/OS - sql query required
(in response to Sam Baugh)
On Fri, Nov 26, 2010 at 2:30 PM, Phil Grainger
<[login to unmask email]>wrote:

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

Excellent, thanks Phil.

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

Daniel Luksetich

Re: DB2 for z/OS - sql query required
(in response to Jim McAlpine)
Jim,

Try this untested query.



Select dbname, name

From sysibm.systablespace a

Where 1 <

(select count(*)

From sysibm.systables b

Where a.dbname = b.dbname

And a.name = b.tsname

And b.type in ('T','M','C'));





Daniel L Luksetich

IBM Information Champion

IBM Certified Database Administrator - DB2 10 for z/OS

IBM Certified System Administrator - DB2 9 for z/OS

IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database
Administration for UNIX, Windows, and OS/2

IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development

IBM Certified Advanced Technical Expert - DB2 Data Replication



Vice President of Global Database Operations

YL&A, Inc.

Database Performance Professionals

http://www.ylassoc.com

http://www.db2expert.com

http://www-01.ibm.com/software/data/champion/profiles/luksetich.html









From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Friday, November 26, 2010 8:11 AM
To: [login to unmask email]
Subject: [SPAM] 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 > Independent, not-for-profit, User Run - the IDUG
difference!

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

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3277 - Release Date: 11/26/10
01:34:00


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