SYSHADOOP.HCAT_TABLESERDEPROPS view
The SYSHADOOP.HCAT_TABLESERDEPROPS view shows the table SerDe configuration properties for all Db2 DATALAKE tables as 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_TABLESERDEPROPS 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 TABSCHEMA and TABNAME, you can improve the access time to this view.
| Column | Type | Description |
|---|---|---|
| TABSCHEMA | VARCHAR(128) | The Db2 schema in which the table resides. |
| TABNAME | VARCHAR(128) | The name of the Db2 table. |
| HIVESCHEMA | VARCHAR(128) | The name of the Hive schema in which the table resides as defined in the Hive metastore. |
| HIVETAB | VARCHAR(128) | The name of the table as defined in the Hive metastore. |
| PROP_NAME | VARCHAR(256) | The name of a property. |
| PROP_VALUE | VARCHAR(4000) | The value of a property. |
Examples
Find the serializer/deserializer SerDe properties of the tables in the
GOSALESDW
schema:
SELECT * FROM SYSHADOOP.HCAT_TABLESERDEPROPS WHERE TABSCHEMA='GOSALESDW';
+-----------+----------------------------+------------+----------------------------+----------------------+------------+
| TABSCHEMA | TABNAME | HIVESCHEMA | HIVETAB | PROP_NAME | PROP_VALUE |
+-----------+----------------------------+------------+----------------------------+----------------------+------------+
| GOSALESDW | BURST_TABLE | gosalesdw | burst_table | serialization.format | 1 |
| GOSALESDW | BURST_TABLE2 | gosalesdw | burst_table2 | serialization.format | 1 |
| GOSALESDW | DIST_INVENTORY_FACT | gosalesdw | dist_inventory_fact | serialization.format | 1 |
| GOSALESDW | DIST_PRODUCT_FORECAST_FACT | gosalesdw | dist_product_forecast_fact | field.delim | "," |
... ... ... ... ... ...
+-----------+----------------------------+------------+----------------------------+----------------------+------------+