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