how to query disk used / available on dashDB

Question

I would like to programmatically query the disk space used and remaining space. How can I do this in dashDB?

In oracle, I could perform something like this:

column dummy noprint
column  pct_used format 999.9               heading "%|Used"
column  name    format a16                  heading "Tablespace Name"
column  bytes   format 9,999,999,999,999    heading "Total Bytes"
column  used    format 99,999,999,999       heading "Used"
column  free    format 999,999,999,999      heading "Free"
break   on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report

set linesize 132
set termout off
select a.tablespace_name                                              name,
       b.tablespace_name                                              dummy,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )      bytes,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
       sum(a.bytes)/count( distinct b.file_id )                       used,
       sum(a.bytes)/count( distinct b.file_id )                       free,
       100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
               (sum(a.bytes)/count( distinct b.file_id ) )) /
       (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))    pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

How would I do similar with dashDB?

select substr(a.tabname,1,30), (a.fpages*PAGESIZE/1024) as size_k, a.card from syscat.tables a, syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID ; SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE) + SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE) + SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO group by tabschema,tabname; — Torsten Steinbach, Apr 18 '16 at 17:10

score 1 · Answer 1 · answered Apr 18 '16 at 14:50

There's currently no API call for this. (Find available API calls here: https://developer.ibm.com/clouddataservices/docs/dashdb/rest-api/) At this time, the only way to tell how much space you're using or have left is via the dashDB UI. dashDB team is exploring additional possibilities, I know. I'll post here again, if I learn more

Torsten Steinbach · Accepted Answer · 2016-04-19T05:54:38.067

A simple and fast method is to look in the catalog, which is up to date eventually (there are statistic collections done internally at certain intervals, when the catalog tables are updated with latest stats):

select substr(a.tabname,1,30), (a.fpages*PAGESIZE/1024) as size_k, a.card from syscat.tables a, syscat.tablespaces b where a.TBSPACEID=b.TBSPACEID ;

A more accurate but costly method is this:

SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE) + SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE) + SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM SYSIBMADM.ADMINTABINFO where tabschema='' and tabname='' group by tabschema,tabname;

how to query disk used / available on dashDB

2 Answers2