Hive and Db2 Big SQL catalogs are inconsistent

If Hive and Db2 Big SQL catalog tables are out of sync, you can synchronize your system in one of three ways.


Your Db2 Big SQL and Hive catalog tables are inconsistent.

Diagnosing the problem

If a table is created through Db2 Big SQL, it is created in Db2 Big SQL catalog and then in Hive. However, the catalogs can become out of sync if, for example, a table is created or altered in Hive. This change would not be seen in Db2 Big SQL.

Additionally, the scheduler maintains in-memory cache of Hive information, which can become out of sync.

Resolving the problem

Depending on your environment and constraints, resolve this problem in one of three ways:
Table 1. Options for resolving synchronization issues
Option Explanation Resolution
HCAT_SYNC_OBJECTS Imports the definition of Hive objects into the Db2 Big SQL catalogs, enabling them to be queried. This option resolves problems with the catalog and scheduler, but it replaces table metadata, such as permissions. For more information, see the HCAT_SYNC_OBJECTS stored procedure.
CATALOG_SYNC_MODE Controls whether DDL operations keep the local Db2 Big SQL catalog in sync with Hive catalogs. If false, this option stops attempts to synchronize catalogs, enabling you to make changes to Db2 Big SQL catalogs without modifications to Hive. This option preserves your table metadata and is recommended if you have a lot of metadata or permissions. For more information, see the CATALOG_SYNC_MODE global variable.
HCAT_CACHE_SYNC Refreshes synchronization connections. This option is useful if the scheduler loses connectivity and you receive an error message when try to query the table. For more information, see HCAT_CACHE_SYNC stored procedure.