Restrictions and limitations on Datalake tables

• ARRAY
• FIXED
• LIST
• MAP
• ROW
• STRUCT
• TMESTAMPZ
• UNION
• UNIONTYPE
• UUID

• BINARY. BINARY is a synonym for VARBINARY when used with Datalake tables.
• DECFLOAT
• GRAPHIC / VARGRAPHIC
• LOB types (CLOB, BLOB, DBCLOB)
• NCHAR
• NVARCHAR
• Spatial data types, such as:
  • ST_Point
  • ST_LineString
  • ST_Polygon
  • ST_GeomCollection
  • ST_MultiPolygon
  • ST_MultiLineString
  • ST_MultiPoint
• TIME
• User-defined type (UDT)
• XML

• OCTETS, CODEUNITS16, CODEUNITS32, FOR BIT DATA, CCSID ASCII, CCSID UNICODE, CCSID 1208
• WITH DEFAULT clause
• INLINE LENGTH
• SYSPROC.DB2SECURITYLABEL
• Update, delete, or merge statements
• Text indexes cannot be created on Datalake tables
• Triggers cannot be created on or be a reference to Datalake tables
• Section actuals are not available for Datalake tables

• Range-partitioned table
• Multi-dimensional clustered table
• Typed table
• Materialized query table
• Staging table
• System period temporal table, application period temporal table, bi-temporal table, or a history table

• DATASLICEID
• DBPARTITIONNUM()
• EXPORT command
• IMPORT command
• INGEST command
• LOAD command
• REORG TABLE command
• RID()
• RID_BIT()
• ROWID
• RUNSTATS
• Cannot ALTER a Datalake table to DROP a column
• Cannot ALTER a Datalake table that has been imported from an external metastore
• Cannot create triggers (with CREATE TRIGGER statement)
• Cannot create indexes (with CREATE INDEX statement)
• Cannot TRUNCATE a Datalake table

• The following are not supported for ALTER DATALAKE TABLE:
  • Adding a column
  • Changing column names
  • Changing column types
  • Exceptions to this are that we allow the following operations for Iceberg Datalake tables only:
    • Changing STRING to VARCHAR
    • Changing the length of a VARCHAR
  • Renaming the table
  • Setting table properties
  • Setting the SerDe or SerDe properties
  • Setting the table location
  • Setting the file format
  • Add a partition
  • Dropping a partition
• The table data cannot be deleted as part of DROP DATALAKE TABLE.
• Dropping an imported/exported table will only remove the meta data from the Db2 catalog and the internal metastore, but it will not remove the table data. The table meta data will still exist in the external metastore and the table data also still exist. An EXT_METASTORE_SYNC with REPLACE mode will re-fetch the table meta data and its from external metastore to Db2 catalog and the table data will be visible.
• For information on externally managed tables see, Terminology.

• SELECT or INSERT statements fail when they run concurrently with the the ALTER Datalake TABLE…DROP…data-partition statement against the same table. For more information, see SELECT or INSERT fails when run concurrently with DROP PARTITION.
• The ALTER Datalake TABLE…ADD…data-partition statement does not retain the partition in the cache when it runs concurrently with the SELECT statement against the same table. For more information, see Incorrect results when querying recently refreshed or loaded partitioned tables.

CREATE Datalake TABLE "Foo" (c1 INT);

CREATE Datalake TABLE "FOO" (c1 INT);