Table statistics are not collected in Data Virtualization

Last updated: Nov 26, 2024

When you create virtual tables, some of the table statistics are not collected correctly.

Symptoms

Table statistics are sometimes not collected during the creation of virtual tables. Missing table statistics can have serious impact on query performance.

Resolving the problem

To solve this issue, perform these steps:

Determine whether the table statistics are missing by running the following command.
```
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 command lists all the objects in the schema that have a CARD of -1, which is an indication that statistics have not been collected.
For each table with cardinality of -1, run the following command, replacing <schema> and <table_name> with the name of the virtualized object.
```
call SYSPROC.NNSTAT(NULL, 
          '<schema>',
          '<table_name>',
          '<list_of_columns_commonly_used_in_predicates>',
          '',
          2,
          '/tmp/<table_name>.log',
          ?,
          1);
```
This call collects table level statistics and column level statistics for the specified columns in <list_of_columns_commonly_used_in_predicates>. If the table has few columns, you can collect statistics for all columns in the table by passing NULL for the column list argument. The call uses the data-based statistics collection method, which can be costly to run. You can collect statistics in nonpeak times. For more information, see the SYSPROC.NNSTAT stored procedure.
Repeat step 1 to ensure that all table statistics are collected.

For more information, see Improving query performance in Data Virtualization.