This entry modified on 2/25/2016 to add some details in the “Modifications” section at the end.
Purpose
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).
Version
Should work on 9.7 and up. Tested on 9.7 and 10.5.
Statement
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.
Modifications
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.
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.