Sizing Query

Roberts Colin

Sizing Query
Morning Gurus,

We run the following SQL to get an idea of the size of an underlying
Tablespace dataset, in this case, for a partitioned Table.
The result it returns is 26gb, which we know to be a quarter of the correct
figure.
We suspect that this may be because the Tablespace is in a 16k pool, as
opposed to the, more normal, 4k pool. All the manuals, however, say that the
SPACE figure is in kilobytes, rather than pages.
We ran the STOSPACE utility prior to running the query. The Tablespace in
not compressed.
Can anyone shed any light on this apparent anomaly?

SELECT TSNAME
,(SUM(SPACE)) / 1000000 AS GB
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = 'database'
AND TSNAME = 'tablespace'
GROUP BY TSNAME

Colin Roberts
Database Administrator
Tel External: 01452 65 3892; Internal: 777 3892
Fax 01452 65 3710
[login to unmask email]



|* This e-mail, and any attachments, is confidential and for the use of the addressee only.
|* If you are not the intended recipient, please telephone +44 (0) 1506 408700

|* We do not accept legal responsibility for this e-mail or any viruses.

|* All e-mails sent and received by us are monitored.

|* Contracts cannot be concluded with us by e-mail.

|* This message has been sent from a member of the British Energy Group (the "Group").

|* The parent company of the Group is British Energy Group plc, registered number 270184, and having its registered office at
|* Systems House, Alba Campus, Livingston EH54 7EG


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

Antonis Galaios

Re: Sizing Query
(in response to Roberts Colin)
Just a thought

When you run runstats against these tablespaces ? The other is if you
are in version 7.1 you have to use the SPACEF instead of SPACE



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roberts Colin
Sent: Monday, November 27, 2006 1:02 PM
To: [login to unmask email]
Subject: [DB2-L] Sizing Query



Morning Gurus,

We run the following SQL to get an idea of the size of an underlying
Tablespace dataset, in this case, for a partitioned Table.

The result it returns is 26gb, which we know to be a quarter of the
correct figure.
We suspect that this may be because the Tablespace is in a 16k pool, as
opposed to the, more normal, 4k pool. All the manuals, however, say that
the SPACE figure is in kilobytes, rather than pages.

We ran the STOSPACE utility prior to running the query. The Tablespace
in not compressed.
Can anyone shed any light on this apparent anomaly?

SELECT TSNAME
,(SUM(SPACE)) / 1000000 AS GB
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = 'database'
AND TSNAME = 'tablespace'
GROUP BY TSNAME

Colin Roberts
Database Administrator
Tel External: 01452 65 3892; Internal: 777 3892
Fax 01452 65 3710
[login to unmask email]



|* This e-mail, and any attachments, is confidential and for the use of
the addressee only.

|* If you are not the intended recipient, please telephone +44 (0) 1506
408700



|* We do not accept legal responsibility for this e-mail or any viruses.



|* All e-mails sent and received by us are monitored.



|* Contracts cannot be concluded with us by e-mail.



|* This message has been sent from a member of the British Energy Group
(the "Group").



|* The parent company of the Group is British Energy Group plc,
registered number 270184, and having its registered office at

|* Systems House, Alba Campus, Livingston EH54 7EG



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

Disclaimer:
This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete the copy from your system.
EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, solicitation, offer or agreement or any information contained in this communication.
EFG Eurobank Ergasias S.A. cannot accept any responsibility for the accuracy or completeness of this message as it has been transmitted over a public network. If you suspect that the message may have been intercepted or amended, please call the sender.


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