SET_EXT_METASTORE_PROPERTY stored procedure

The SYSHADOOP. SET_EXT_METASTORE_PROPERTY stored procedure sets, updates, or unsets a configuration property for a registered external metastore.

Authorization

EXECUTE is granted to the DASHDB_ENTERPRISE_ADMIN role.

Syntax

The schema is SYSHADOOP.

Description

'external-metastore-name'

An input argument of type VARCHAR(256) indicating the name of the registered external metastore whose configuration property is to be set, updated, or unset.

'property-name'

An input argument of type VARCHAR(256) indicating the name of the configuration property to set, update, or unset. For a list of supported properties, see the Table 1 table below.

'property-value'

An input argument of type VARCHAR(1024) indicating the value of the configuration property. If the value is NULL, the property is unset. Otherwise, it is either set or updated with the value.

'status-code'

An output argument of type INTEGER indicating whether the operation was successful. 0 indicates success, a non-zero value indicates an error.

'status-message'

An output argument of type VARCHAR(1024) indicating whether the operation was successful.

Supported properties

Important: Starting with watsonx.data 2.2.2, Hive and Iceberg tables are managed using two different metastore server types. This differs from earlier versions where both table types were managed using a single metastore server type. This is reflected in the properties supported and in the value those properties take, in particular, the “type” property which specifies what kind of metastore server is being connected to (metastore for watsonx.data in version earlier than 2.2.2, Hive metastore for watsonx.data in version 2.2.2 or later, or Iceberg metastore for watsonx.data in version 2.2.2 or later).

The following properties are supported:

Table 1. Supported properties
Name	Required	Description
type	Yes	The type of metastore to which you are connecting. Supported values are: `watsonx-data`, for connecting to a watsonx.data metastore The type of metastore to which you are connecting. The value you should use depends on the type of table you are accessing. Depending on the table you are connecting to, use one of the following: `watson-data`, for connecting to watsonx.data 2.2.1 or earlier, regardless of the type of table to be access. `watsonx-data-hive`, for connecting to watsonx.data 2.2.2 or later to access watsonx.data Hive tables `iceberg-rest`, for connecting to watsonx.data 2.2.2 or later to access watsonx.data Iceberg tables (Apache Iceberg REST Catalog).
uri	Yes	The URI of the metastore. If connecting to watsonx.data, it can be found in the watsonx.data console Infrastructure manager, in the Catalog details page: To access Hive tables, use the Metastore External Thrift endpoint. To access Iceberg tables, use the Metastore External REST endpoint.
catalog.name	For Iceberg on watsonx.data 2.2.2 or later	Indicates the name of the watsonx.data Iceberg catalog being accessed. It is the name of the catalog as displayed in the watsonx.data console Infrastructure manager.
use.SSL	No	"`true`" if the metastore requires an SSL connection
ssl.cert	No	The certificate to use to validate the SSL connection. Can be a PEM file name or a string representing the cert in the PEM format. It is not necessary to pass a certificate if the SSL connection is established using a certificate issued by a well-known CA such as DigiCert or "Let’s Encrypt".
auth.mode	No	If the metastore requires authentication, indicates the mode of authentication to use. The only supported value for now is "PLAIN".
auth.plain.credentials	No	If the metastore requires PLAIN authentication, provides the credentials as a string in the form "username:password". The password is stored securely in a software keystore.

Examples

Setting the use.SSL property of the registered metastore watsonxdata to true to indicate that the connection is to be encrypted using SSL.

CALL SYSHADOOP.SET_EXT_METASTORE_PROPERTY('watsonxdata', 'use.SSL', 'true', ?, ?)

Unsetting the use.SSL property of the registered metastore watsonxdata to indicate that the connection is not to be encrypted.

CALL SYSHADOOP.SET_EXT_METASTORE_PROPERTY('watsonxdata', 'use.SSL', NULL, ?, ?)

Connecting to watsonx.data version 2.2.1 or earlier to access Hive or Iceberg tables.

db2 "call register_ext_metastore('watsonx-data', 'type=watsonx-data,uri=thrift://watsonx.ibm.com:8380,use.SSL=true,ssl.cert=/tmp/cacert.pem,auth.mode=plain,auth.plain.credentials=ibmlhadmin:password', ?, ?)"

Connecting to watsonx.data version 2.2.1 or later to access Iceberg tables.

db2 "call register_ext_metastore('iceberg_data','type=iceberg-rest,uri=https://iceberg-rest1.fyre.ibm.com:8180/mds/iceberg,catalog.name=iceberg_data,ssl.cert=/tmp/cacert.pem,auth.mode=basic,auth.plain.credentials=ibmlhadmin:password', ?, ?)"

Connecting to watsonx.data version 2.2.2 or later to access Hive tables.

db2 "call register_ext_metastore('hive_data','type=watsonx-data-hive,uri=https://watsonx.ibm.com:8381,use.SSL=true,ssl.cert=/tmp/cacert.pem,auth.mode=plain,auth.plain.credentials=ibmlhadmin:password', ?, ?)"