List all empty tables for SAP schema in a database

[valeriocicchiel]

Hello everyone,

I would need to list all empty tables in a database for SAP schema (SAPSR3).

Any idea for a SQL query or how to retrive this information from DBACOCKPIT?

Best Regards,

Valerio

[martin_mikala · Accepted solution]

Hi Valerio,

In syscat.tables is info how much pages are used by table.

db2 "select count(*) from syscat.tables where TYPE='T' and STATUS='N' and NPAGES = '0'"

or for listing

db2 "select TABNAME from syscat.tables where TYPE='T' and STATUS='N' and NPAGES = '0'"

There is description for syscat.tables IBM Knowledge Center

See MPAGES and FPAGES too.

Martin

[valeriocicchiel · Accepted solution]

Hello,

so DB2 doesn't keep track of the row count of every table, for performance reason. So there is no "easy" method to extract this information with a direct query, but you have to make the assumption that an empty table does not fill any page.

Thank you for your reply,

Valerio

[Frank-Martin]

Hi Valerio,

DBACOCKPIT does not provide a list of empty tables but something very close.

Are you using virtual tables in your system? In DBACOCKPIT unter Space -> Virtual Tables you will find a list of virtual tables and a list of candidates for virtual tables.

(1) Virtual tables are always empty since technically they are a special kind of view that can not contain data.

(2)  DBACOCKPIT determines candidates for virtual tables by looking for tables that only occupy a minimum size in the tablespace.So the candidates for virtual tables may or may not be empty since some rows fit into the table without the need to increase the minimum size. When converting empty tables DBACOCKPIT executes a "SELECT COUNT(*)" on each canditate before converting it to a virtual table.

Regards

                Frank