EXT_METASTORE_SYNC stored procedure

The EXT_METASTORE_SYNC stored procedure imports Hive and Iceberg tables from watsonx.data into Db2. The table is then accessible from both systems.

Note: SYSHADOOP.EXTERNAL_CATALOG_SYNC can be used as a synonym for EXT_METASTORE_SYNC, but it is not recommended.

Authorization

EXECUTE is granted to the DASHDB_ENTERPRISE_ADMIN role.

Syntax

Read syntax diagramSkip visual syntax diagramEXT_METASTORE_SYNC(metastore ,schema,table,exist_action,error_action,options)

The schema is SYSHADOOP.

Description

metastore
A input argument containing the name under which the metastore was registered. This is a required parameter which references an external metastore which has been registered using the SYSHADOOP.REGISTER_EXT_METASTORE procedure. If the value does not match a registered external metastore, an error is returned.
schema
A required input argument containing the name of the schema that contains the objects to be imported. You can use regular expressions to match multiple schemas. To specify all schemas use the pattern '.*'.
table
A required input argument containing the name of the table to import. It is possible to use a regular expression to import multiple tables in one invocation. To specify all tables use the pattern '.*'.
exist_action
An optional input argument containing the action to take if a table with the same name already exists in the schema in the Db2 catalog. If the table does not yet exist in the Db2 catalog, this option is ignored and the table is created in Db2. Supported values are:
ERROR
Indicates that the presence of an existing object should be considered an error. This is the default. If error-action is CONTINUE, this action is the equivalent of specifying SKIP.
REPLACE
The existing table is DROPPED and replaced with the table being imported.
Note: This is a very disruptive operation. See, Dropping Tables.
SKIP
Indicates that objects that are already defined should be skipped.
error_action
An input argument of type VARCHAR(20) containing the action to take in case of an error when importing a table. Supported values are:
STOP
The processing stops and no further tables are synchronised. This is the default value.
CONTINUE
The error is logged, but processing continues if multiple tables are to be imported.
options
An input parameter of type VARCHAR(256) that is reserved for future use. A NULL value must be passed.

Usage notes

  • When the exists_action is set to REPLACE, the table will be dropped and recreated in Db2. When a table is dropped, the row in the SYSCAT.TABLES system catalog view that contains information about that table is dropped, and any other objects that depend on the table are affected. See Dropping Tables, for more implications on dropping the table in Db2.
  • It is recommended to run HCAT_CACHE_SYNC after running EXT_METASTORE_SYNC procedure to ensure that the Big SQL scheduler cache is refreshed when there are changes to the tables. For more information about the scheduler cache, see Db2 Big SQL Scheduler.
  • It is recommended to run the MSCK REPAIR TABLE statement after importing a table from watsonx.data to ensure partitioning information is properly populated into the local HMS.
  • Regular expressions can be used for specify the schema and table parameters. Java style regular expressions are used for these parameters.
  • Although NULL and empty strings are allowed values for schema and table parameters, they should not be used. In the future, these values will result in an error.
  • In some circumstances, when using the 'REPLACE' value for the exist-action parameter, the stored procedure call may fail with the following error message:
    ```ERROR DROP failed: The statement references an object that identifies an unexpected object type. Object: "LHDB2ICE.LH_ICE_SMOKE1". Object type: "DATALAKE TABLE". Expected object type: "TABLE"```

    To resolve, re-issue the call using the EXT_METASTORE_SYNC stored procedure name and the exact same parameters. For more information, see EXT_METASTORE_SYNC failed when using REPLACE as an exist action.

Examples

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)