Importing tables from watsonx.data

After registering a watsonx.data metastore in Db2, it is possible to import Hive and Iceberg tables from watsonx.data into Db2. The table is then accessible from both systems.

To import a table created in a watsonx.data metastore into Db2, you can run the following statement:
CALL EXT_METASTORE_SYNC('metastore-name', 'schema-name', 'table-name',
'exist-action', 'error-action', NULL)
with the following parameters:
metastore-name
The name under which the metastore was registered.
schema-name
The name of the schema to which the table to import belongs. The imported table is created in a Db2 schema with the same name, which will be created if it does not exist. Note that if the schema already exists, the imported tables are added to it, regardless of whether it contains other tables.
table-name
The name of the table to import. It is possible to use a regular expression to import multiple tables in one invocation.
exist-action
A string indicating the action to take if a table with the same name already exists in the schema in the Db2 catalog. Supported values are:
  • ERROR: Raises an error. See the error-action parameter.
  • REPLACE: The existing table is DROPPED and replaced with the table being imported.
  • SKIP: Skip importing the table.
error-action
A string indicating the action to take in case of an error when importing a table. Supported values are:
  • CONTINUE: The error is logged, but processing continues if multiple tables are to be imported.
  • STOP: The processing stops and no further tables are synchronized.

When importing a table from watsonx.data, there must exist a storage alias for the storage container where the imported table is located and to which the user running the import procedure has access (as set by the "grantee" parameter when creating the storage alias). The imported table location must be in a child path of the path specified in that storage alias (using the "object" parameter).

If a table defined in watsonx.data uses features or data types that are not supported by Db2, the table will not be synchronized. In addition, tables containing an identifier for schema names, table names, and column names that are not supported by Db2 will not be synchronised.

It is possible to SELECT from and INSERT to an imported table from Db2, subject to SQL access. It is not possible to modify or ALTER an imported table. If a table was previously imported, but its schema has changed in watsonx.data, it can be updated in Db2 by re-running the import procedure for the table.

For partitioned Datalake tables, an INSERT operation will implicitly create a new table partition when a value that was not present in the table is provided for a partitioning column. If the table is shared by Db2 Warehouse and watsonx.data, the partition list must be manually refreshed in the catalog of the system where the INSERT statement was not run:
  • If inserting in Db2 Warehouse, this is achieved by running the sync_partition_metadata procedure for the table in watsonx.data as documented at HIVE CONNECTOR PROCEDURES.
  • If inserting in watsonx.data, this is achieved by running the MSCK REPAIR TABLE statement for the table in Db2 Warehouse as documented in MSCK REPAIR TABLE.
The following is an example showing how to import all tables existing in a schema named iceberg in a watsonx.data metastore registered in Db2 as watsonxdata:
CALL EXT_METASTORE_SYNC('watsonxdata', 'iceberg', '.*', 'SKIP', 'CONTINUE', NULL)
The following is an example showing how to update in Db2 the definition of an already imported table iceberg.mytable, whose definition has been changed in watsonx.data. By using the REPLACE exist-action parameter, ensure that the old definition is replaced with the new one:
CALL EXT_METASTORE_SYNC('watsonxdata', 'iceberg', 'mytable', 'REPLACE', 'CONTINUE', NULL)