SQL5105N errors after upgrading from v11.5.9 to v12

Keystores where external metastore credentials are stored will be removed during upgrade from v11.5.9 to v12.

Symptoms

Errors may be returned when accessing Datalake tables after upgrading from v11.5.9 to v12 similar to the following:

SQL5105N The statement failed because a Big SQL component encountered an error. 
Component receiving the error: "SCHEDULER". Component returning the error: "BigSQL IO". Log entry identifier: "[SCL-0-1a47b6a19]".
Reason: "Unable to instantiate org.apache". SQLSTATE=58040

Causes

After an upgrade from v11.5.9 to v12.1, keystores where external metastore credentials are stored will be removed.

Db2 v12 uses a new version of Java which does not provide backward compatibility for older keystore formats.

Resolving the problem

Re-register the metastore with the follow steps:
  1. 1.Replace the missing jceks credentials files by calling the set metastore property's procedure.
    db2 "CALL SYSHADOOP.SET_EXT_METASTORE_PROPERTY('metastore name', 'auth.plain.credentials', 'lh-default:password', ?, ?)
  2. Un-register the metastore.
    db2 "CALL SYSHADOOP.UNREGISTER_EXT_METASTORE('metastore name', ?, ?)"
  3. Register the metastore.
    db2 "CALL REGISTER_EXT_METASTORE('metastore name', 'type=watsonx-data,uri=thrift://watsonx.data1.fyre.ibm.com:9083', ?, ?)"
  4. Set the metastore's property.
    db2 "CALL SET_EXT_METASTORE_PROPERTY('metastore name', 'use.SSL', 'true', ?, ?)"
db2 "CALL SET_EXT_METASTORE_PROPERTY('metastore name', 'ssl.cert', 'path/to/cert', ?, ?)"
db2 "CALL SET_EXT_METASTORE_PROPERTY('metastore name', 'auth.mode', 'PLAIN', ?, ?)"
db2 "CALL SET_EXT_METASTORE_PROPERTY('metastore name', 'auth.plain.credentials', 'lh-default:password', ?, ?)"