SQL5105N error when exporting a Db2 table to watsonx.data

A CREATE DATALAKE TABLE statement may fail with an SQL5105N error when exporting a Datalake table from Db2 to watsonx.data that uses a schema not defined in watsonx.data.

Symptoms

When exporting a Datalake table from Db2 to watsonx.data that uses a schema that is not defined in watsonx.data, an error will occur which causes a mismatch in the meta data information stored in Db2.

Causes

The sample scenario below illustrates the problem.

Create a table which uses a schema which does not existing in watsonx.data:

create datalake table test.t2_ice(id int, age int) stored by iceberg location  'db2remote://ibmlh-dev-iceberg//lh//t2_ice' tblproperties ('iceberg.catalog'='ibmlh-dev')

Subsequently, an error occurs indicating that the object TEST (the schema) does not exist:

SQL5105N  The statement failed because a Big SQL component encountered an 

error.  Component receiving the error: "DDL".  Component returning the error: 

"HIVE".  Log entry identifier: "[BSL-0-6192310dc]".  Reason: "Invalid Hive 

object for TEST".  SQLSTATE=58040

You can attempt to create the table again with this command:

create datalake table test.t2_ice(id int, age int) stored by iceberg location  'db2remote://ibmlh-dev-iceberg//lh//t2_ice' tblproperties ('iceberg.catalog'='ibmlh-dev')

The following error will be returned that the table already exists:

SQL0601N  The name of the object to be created is identical to the existing 

name "test.t2_ice" of type "TABLE".  SQLSTATE=42710

An attempt to drop the table with the following command will fail:

drop datalake table test.t2_ice

Returned error:

SQL0204N  "TEST.T2_ICE" is an undefined name.

Resolving the problem

To prevent this issue, the schema should be created in watsonx.data prior to exporting the Db2 table to watsonx.data.

To remove a table which has a a mismatch in the meta data information stored in Db2, the following drop table statement can be used:

drop datalake table if exists test.t2_ice