SQL for "list tablespaces show detail"?

[StarFleet]

Problem: I need to programmatically access the table space size remaining.

I know that this is given in the DB2 CLP by the command "list tablespaces show detail". What I would like is for my program to query the database and get the answer, just as if it were a normal SQL operation. I have searched the forums and the most I've seen was a reference to the above CLP command (parse the result to get your answer) or RUNSTATS and try and get the information from the system tables (The CLP does not do this nor the db2cc so why should I in this case).

If anyone knows the SQL for getting this data please post it. I am sure a lot of other developers would find it useful.

Thanks,
Paul

 

[Guest_imported]

This is not possible. Tablespace details is "internal" system information and is available only through CLP commands and not through SQL. The same applies to Container info and others. A real pain and it sure makes it difficult to easily get reports on Tspace usage etc.

 

[2742]

Hi There

I asked the same question about 3 months ago and found i could write a query to find out an eastimate of the space used but you could only use it so far because the system tables don't have that level of detail. In the end i adapted an API writen in C to find out the total spaced used in a table space. The base C program comes with DB2 it's called tabspace.sqc but if you would like to see the C program i would be happy to post. I hope this helps - let me know what you come up with.

 

[2742]

Here's a sql statement to find out how much space is actually used but not how much is allocated!!

DB2 SELECT TABNAME, COLCOUNT, TABLEID, TBSPACEID, CARD,NPAGES,FPAGES,(((FPAGES*4096)/1024)/1024) as SizeUsed,TBSPACE,INDEX_TBSPACE FROM SYSCAT.TABLES WHERE TBSPACEID IN (SELECT TBSPACEID FROM SYSCAT.TABLESPACES WHERE TBSPACETYPE = 'D')