When I create the tablespace, I can only set the "pagesize" and the "extent size",but I can't set the tablespace physical size like oracle.
Also , the command "list tablespace containers for tablespace_id show detail" can only show the "total pages " and "used pages",they are equals。Actually,the tablespace's size is 580M when I telnet the server to see the physical tablespace file。
Please tell me how to get the actual tablespace used by db，also I want to knnow the fragment percentage in the tablespace file。
Thanks

If you use SMS (system managed storage) in your Create Tablespace, the size is indefinite and will grow as needed in the path you defined.

If you use DMS (database managed storage) the size is finite and the list tablespace command (show detail) will tell you the size.

thanks,as to the SMS tablespace,I think I can get the snapshot for tablespace physical size, do you think so? How to ?

If you do a list tablespaces show detail you will get the following information:

SMS example:
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 8083
Useable pages = 8083
Used pages = 8083
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Note that on SMS, the Total Pages is the same size Used Pages, since the tablespace increases and decreases as needed (although table/index reorgs may be needed to free up empty pages).

DMS Example (Name assigned by DBA):
Tablespace ID = 4
Name = TBSPACE1
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 768000
Useable pages = 767968
Used pages = 253440
Free pages = 514528
High water mark (pages) = 476832
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Note that on DMS, the Total Pages is the size specified by the DBA when the tablespace was created (actually the total of all the containers).

Hi

How can i get something like this

Tablepsace-Name Free Space M bytes Used-Spcae in M bytes


any syscat views that tell me this

regards
Hrishy

Since you have not mentioned the version, I would assume you are in Version 8 ...

db2 "select * from table( SNAPSHOT_TBS_CFG ('sample',-1)) as test"

and
db2 "select * from table( SNAPSHOT_CONTAINER ('sample',-1)) as test"

If you are still on Version 7 (or lower) ,

then a script in http://www.db2click.com/scripts.htm
should help

Cheers
Sathyaram