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
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.
Columns returned by the SYSCS_DIAG.SPACE_TABLE table function
For each column returned by the SYSCS_DIAG.SPACE_TABLE table function, this table provides its data type, its length in bytes, whether it is nullable, and a description of its contents.
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