Column-level statistics not collected due to data type mismatch in Data Virtualization

When you use the COLLECT_STATISTICS procedure with the remote-catalog statistics collection type on a virtualized table with a data type mismatch between the virtualized table and the remote table, you might see a warning that identifies the impacted column and the incompatible data types.

Symptoms

When Data Virtualization collects statistics, you might encounter a warning that is similar to the following example. The warning message states that the HIGHKEY and LOWKEY statistics was not collected for column C2.

db2 "call dvsys.collect_statistics('BLUADMIN', 'TESTOTHER', null, 1, null, ?)"
Value of output parameters
--------------------------
Parameter Name  : DIAGS
Parameter Value : Collected statistics for table "BLUADMIN"."TESTOTHER":
Table Cardinality = 2
Column "C3" [CHAR(5)]:  colcard=1, numnulls=1, highkey="1234", lowkey="1234"
Column "C4" [VARCHAR(10)]:  colcard=1, numnulls=1, highkey="12003a", lowkey="12003a"
Column "C5" [CHAR(1)]:  colcard=2, numnulls=0, highkey="a", lowkey="0"
Column "C6" [VARCHAR(10)]:  colcard=2, numnulls=0, highkey="bbb", lowkey="aaa"
Column "C7" [VARBINARY(16000)]:  colcard=1, numnulls=1, highkey="bx'68656C6C6F'", lowkey="bx'68656C6C6F'"
Column "C8" [VARCHAR(16000)]:  colcard=1, numnulls=1, highkey="", lowkey=""
Column "C9" [VARCHAR(16000)]:  colcard=1, numnulls=1, highkey="dsfadfagfewarragagfagresttretw", lowkey="dsfadfagfewarragagfagresttretw"
Column "C1" [SMALLINT]:  colcard=2, numnulls=0, highkey="2", lowkey="1"
Column "C2" [BOOLEAN]:  colcard=2, numnulls=0, highkey="", lowkey=""

Received the following messages while collecting stats:

WARNING: Key stats for column "C2" not collected due to remote type (BIT) incompatibility with virtual type (BOOLEAN)

Return Status = 0

Causes

When you virtualize a table, a data type at the remote data source might be different than the corresponding data type on the virtualized table. This issue can happen because of a type-mapping error during virtualization, an explicit change to the virtualized table, or a change to the table definition at the remote data source. In these cases, a type mismatch error is returned. This issue affects the HIGHKEY and LOWKEY statistics only.

Resolving the problem

If the HIGHKEY and LOWKEY statistics are important for good query performance, you can resolve this problem by specifying the remote-query statistics collection type on the COLLECT_STATISTICS procedure as shown in this example.
db2 "call dvsys.collect_statistics('BLUADMIN', 'TESTOTHER', null, 2, null, ?)"

For more information, see COLLECT_STATISTICS stored procedure in Data Virtualization.