SYSHADOOP.HCAT_SCHEMAS view

The SYSHADOOP.HCAT_SCHEMAS view shows the schema details for all Db2 schemas that are also defined by the Hive catalogs. The contents of this view might not be available if the Hive metastore is unavailable.

When you access the SYSHADOOP.HCAT_SCHEMAS view, you are querying the Hive metastore for each table that is returned. If you filter the set of tables that you want to view by SCHEMANAME, you can improve the access time to this view.

Table 1. SYSHADOOP.HCAT_SCHEMAS view
Column	Type	Description
SCHEMANAME	VARCHAR(128)	The Db2 name of the schema.
HIVESCHEMA	VARCHAR(128)	The name of the schema as defined in the Hive metastore.
LOCATION	VARCHAR(4000)	The location in the distributed file system in which the schema is defined.
COMMENT	VARCHAR(4000)	The user-defined comment for the schema.

Examples

Find all of the Hadoop schemas:

SELECT * FROM SYSHADOOP.HCAT_SCHEMAS;

The following is sample output from the query:


+---------------+--------------+---------------------------------------------------------------------------------------------------+-----------------------+
| SCHEMANAME    | HIVESCHEMA   | LOCATION                                                                                          | COMMENT               |
+---------------+--------------+---------------------------------------------------------------------------------------------------+-----------------------+
| DEFAULT       | default      | file:/mnt/blumeta0/home/db2inst1/sqllib/bigsql/warehouse/tablespace/external/hive                 | Default Hive database |
| GOSALESDW     | gosalesdw    | file:/mnt/blumeta0/home/db2inst1/sqllib/bigsql/warehouse/tablespace/external/hive/gosalesdw.db    | [NULL]                |
| MY_SCHEMA     | my_schema    | file:/mnt/blumeta0/home/db2inst1/sqllib/bigsql/warehouse/tablespace/external/hive/my_schema.db    | [NULL]                |
| OTHER_SCHEMA  | other_schema | file:/mnt/blumeta0/home/db2inst1/sqllib/bigsql/warehouse/tablespace/external/hive/other_schema.db | [NULL]                |
+---------------+--------------+---------------------------------------------------------------------------------------------------+-----------------------+