DB2 Administrative SQL Cookbook: Finding Tables in a Specific Tablespace, Without RI

This entry modified on 2/25/2016 to add some details in the “Modifications” section at the end.


To generate a list of tables in a specific tablespace that do not have RI involved, and do have primary keys. Why would I want this? I have a not-Large tablespace on DB2 9.7, and it is reaching its maximum size. I need to identify what tables to move. Because I’m on DB2 9.7, the only tables I can move online using ADMIN_MOVE_TABLE are those without RI, but with Primary Keys. I also want to list the number of rows and the recent activity on the table so I get an idea of how large a table is and how active it is (as an indicator of whether I’m likely to be able to accomplish the move online).


Should work on 9.7 and up. Tested on 9.7 and 10.5.


select substr(st.tabschema,1,12) as tabschema
    , substr(st.tabname, 1,20) as tabname
    , CARD
    , rows_read 
from syscat.tables st 
    left outer join table(mon_get_table('','',-2)) as mgt 
        on st.tabschema=mgt.tabschema 
            and st.tabname=mgt.tabname
where tbspace='IBMDB2SAMPLEXML' 
   and ( keyindexid is not NULL and keyindexid <> 0 ) 
   and (not exists (select 1 from syscat.references sr where sr.tabschema=st.tabschema and sr.tabname= st.tabname) 
            and not exists (select 1 from syscat.references sr1 where sr1.tabschema=st.tabschema and sr1.tabname= st.tabname)) 
order by card desc 
with ur;

Sample Output

TABSCHEMA    TABNAME              CARD                 ROWS_READ           
------------ -------------------- -------------------- --------------------
DB2INST1     CUSTOMER                                6                    -
DB2INST1     INVENTORY                               4                    4
DB2INST1     PRODUCT                                 4                    4
DB2INST1     PRODUCTSUPPLIER                         2                    2
DB2INST1     SUPPLIERS                               2                    2
DB2INST1     CATALOG                                 0                    0

  6 record(s) selected.


Obviously, you’ll want to change the tablespace name to the tablespace you’re working with. This query also ONLY looks for tables with their DATA in the tablespace specified. If you want to find tables with LOBs or indexes in the tablespace, the SQL would have to be modified. Also if you have range-partitioned tables, you’ll want to query SYSCAT.DATAPARTITIONS instead of SYSCAT.TABLESPACES.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 557


  1. Hi Ember, thanks for sharing the query… if your tablespace is DMS why don’t you just convert it to large (alter tablespace … convert to large) ??. You can do it without issues and don’t worry about ADM6104W on db2diag.log (we’ve converted several without rebuilding the indexes and yet we haven’t got any errors). Hope this helps. Regards

    • Good point. If regular to large is the only goal, that works. In this case, I’m also dealing with tablespaces that are not reclaimable, so I need to accomplish that conversion as well.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.