Checking for missing statistics in Data Virtualization

Last updated: Nov 26, 2024

You can use SQL to determine whether statistics for a specific virtual table are missing.

To determine whether table-level statistics are missing for a virtual table, use the following query.

select cast(TABNAME as varchar(25)), TYPE, COLCOUNT, STATS_TIME
  from SYSCAT.TABLES
  where TABSCHEMA='schema'   
    and CARD=-1;

Replace schema with the schema name of the virtual table. The statement returns a list of all the objects in the schema that have a CARD value of -1, which is an indication that table-level statistics have not been collected.

To determine whether column-level statistics are missing for a virtual table, use the following query.

select cast (COLNAME as varchar(25))
  from SYSSTAT.COLUMNS
  where TABSCHEMA='schema' 
    and TABNAME='table_name' 
    and COLCARD=-1
    and NUMNULLS=-1;

Replace schema with the schema name and table_name with the name of the virtual table. The statement returns a list of all the objects in the schema that have a COLCARD and NUMNULLS value of -1, which is an indication that column-level statistics have not been collected.

Note: Not all columns in a table require column-level statistics.