SYSCS_DIAG.SPACE_TABLE diagnostic table function

The SYSCS_DIAG.SPACE_TABLE diagnostic table function shows the space usage of a particular table and its indexes.

You can use this diagnostic table function to determine if space might be saved by compressing the table and indexes.

All users can access this diagnostic table function, whether or not the database has authentication and SQL authorization enabled. See "Configuring user authentication" and "Configuring user authorization" in the Derby Security Guide for more information.

To access the SYSCS_DIAG.SPACE_TABLE diagnostic table function, you must use the SQL table function syntax. You can invoke the table function in the following ways:
  • If invoked with no arguments, the table function retrieves space information for all tables and indexes in the database.
  • If invoked with one argument, a tableName, the table function retrieves information for the specified table in the current schema.
  • If invoked with two arguments, a schemaName followed by a tableName, the table function retrieves information for the specified schema and table.

The returned table has the columns shown in the following table.

Table 1. Columns returned by the SYSCS_DIAG.SPACE_TABLE table function
Column Name Type Length Nullable Contents
CONGLOMERATENAME VARCHAR 128 true The name of the conglomerate, which is either the table name or the index name. (Unlike the SYSCONGLOMERATES column of the same name, table ID's do not appear here).
ISINDEX SMALLINT 5 false Is not zero if the conglomerate is an index, 0 otherwise.
NUMALLOCATEDPAGES BIGINT 20 false The number of pages actively linked into the table. The total number of pages in the file is the sum of NUMALLOCATEDPAGES + NUMFREEPAGES.
NUMFREEPAGES BIGINT 20 false The number of free pages that belong to the table. When a new page is to be linked into the table the system will move a page from the NUMFREEPAGES list to the NUMALLOCATEDPAGES list. The total number of pages in the file is the sum of NUMALLOCATEDPAGES + NUMFREEPAGES.
NUMUNFILLEDPAGES BIGINT 20 false The number of unfilled pages that belong to the table. Unfilled pages are allocated pages that are not completely full. Note that the number of unfilled pages is an estimate and is not exact. Running the same query twice can give different results on this column.
PAGESIZE INTEGER 10 false The size of the page in bytes for that conglomerate.
ESTIMSPACESAVING BIGINT 20 false The estimated space which could possibly be saved by compressing the conglomerate, in bytes.
TABLEID CHAR 36 false The id of the table to which the conglomerate belongs.
For example, use the following query to return the space usage for all of the user tables and indexes in the database:
SELECT sysschemas.schemaname, T2.*
    FROM
        SYS.SYSTABLES systabs, SYS.SYSSCHEMAS sysschemas,
        TABLE (SYSCS_DIAG.SPACE_TABLE()) AS T2
    WHERE systabs.tabletype = 'T'
    AND sysschemas.schemaid = systabs.schemaid
    AND systabs.tableid = T2.tableid;
where T2 is a user-specified table name that is any valid identifier.

Both the schemaName and the tableName arguments must be expressions whose data types map to Java strings. If the schemaName and the tableName are non-delimited identifiers, you must specify the names in uppercase.

For example:
SELECT * 
    FROM TABLE (SYSCS_DIAG.SPACE_TABLE('MYSCHEMA', 'MYTABLE')) 
    AS T2
Related reference
SYSCS_DIAG.CONTAINED_ROLES diagnostic table function
SYSCS_DIAG.ERROR_LOG_READER diagnostic table function
SYSCS_DIAG.ERROR_MESSAGES diagnostic table
SYSCS_DIAG.LOCK_TABLE diagnostic table
SYSCS_DIAG.STATEMENT_CACHE diagnostic table
SYSCS_DIAG.STATEMENT_DURATION diagnostic table function
SYSCS_DIAG.TRANSACTION_TABLE diagnostic table