Restrictions for caching in Data Virtualization

Caching in Data Virtualization is subject to the following restrictions:

• You can't use the fetch first n rows clause.
• You can't use the order by clause.
• You can't use select statements that use the with clause.
• Data types that are not supported:
  • BINARY (BINARY is not supported; however, VARBINARY is supported)
  • CHAR FOR BIT DATA, VARCHAR FOR BIT DATA
  • DECFLOAT
  • GRAPHIC, VARGRAPHIC
  • LOB types (BLOB, CLOB, DBCLOB)
  • LONG VARCHAR, LONG VARGRAPHIC (These types are deprecated in Db2)
  • TIME
  • XML

Data Virtualization caching is built on Db2 Big SQL capabilities that involve Datalake MQTs. The following restrictions apply.

• You must be aware of the restrictions for the CREATE DATALAKE TABLE statement. For more information, see CREATE DATALAKE TABLE statement, Materialized query table restrictions, and Restrictions and limitations on Datalake tables.

Data Virtualization caching is also built on Db2 MQT capabilities. The following Db2 MQT restrictions apply. For more information, see Materialized query table restrictions in the Db2 product documentation.

• You must be aware of fullselect component restrictions. For more information, see fullselect in the Db2 product documentation.
• If the enforcement of IBM® Knowledge Catalog data protection rules is enabled in Data Virtualization, direct access to cache tables (MQT tables with names that start with DV* in the CACHESYS schema) is denied to all users.