What is the command to get the name of tablespace of a given table in DB2 database ?
1 Answer
On Db2 for LUW you can use this query. You get one row per table data partition (1 row for a non range partitioned table).
SELECT
SD.TBSPACE AS DATA_SPACE
, SL.TBSPACE AS LONG_SPACE
, SI.TBSPACE AS INDEX_SPACE
FROM
SYSCAT.DATAPARTITIONS P
JOIN SYSCAT.TABLESPACES SD ON SD.TBSPACEID = P.TBSPACEID
LEFT JOIN SYSCAT.TABLESPACES SL ON SL.TBSPACEID = P.LONG_TBSPACEID
LEFT JOIN SYSCAT.TABLESPACES SI ON SI.TBSPACEID = P.INDEX_TBSPACEID
WHERE
TABSCHEMA = 'YOUR_SCHEMA'
AND TABNAME = 'YOUR_TABLE'