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