Query Optimizer internal error updating statistics

The query rewrite procedure using Query Optimizer may fail when the statistics collected for an Iceberg table are invalid or inconsistent.

Symptoms

The rewrite procedure fails with the following error message:
Internal error updating statistics in Db2 (see logs for details): The catalog statistic "<value>" for column "<volumn-name>" is out of range for its target column, has an invalid format, or is inconsistent in relation to some other statistic. Reason Code = "6".. SQLCODE=-1227, SQLSTATE=23521, DRIVER=4.33.32:

Resolving the problem

  1. Run the following command in Presto to obtain the actual cardinality of the Iceberg table:

    SELECT count(*) 
FROM <table-catalog>.<table-schema>.<table-name>;
  2. Use ExecuteWxdQueryOptimizer to execute the following SQL statement, which updates the card column in the sysstat.tables view with the actual cardinality obtained in step 1.
    ExecuteWxdQueryOptimizer 'update sysstat.tables set card=<value from count(*) in Presto> where TABSCHEMA='<table-catalog>.<table-schema>' AND TABNAME='<table-name>'';
  3. Run the procedure described in Enhancing statistics for synced Iceberg tables to successfully collect the enhanced statistics.