Checking for missing statistics in Watson Query

Important: IBM Cloud Pak® for Data Version 4.6 will reach end of support (EOS) on 31 July, 2025. For more information, see the Discontinuance of service announcement for IBM Cloud Pak for Data Version 4.X.

Upgrade to IBM Software Hub Version 5.1 before IBM Cloud Pak for Data Version 4.6 reaches end of support. For more information, see Upgrading IBM Software Hub in the IBM Software Hub Version 5.1 documentation.

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.