BIGSQL_ENV table function

The BIGSQL_ENV table function returns environment variable key/value pairs for the current Db2® Big SQL environment.

This function enables you to identify which environment variables have been set for your cluster and to determine the values of certain system properties without having to directly access the cluster. The BIGSQL_ENV table function can be called by any user.

Syntax

Description

The schema is SYSHADOOP. The function returns a table that contains the Db2 Big SQL environment variables and system properties. Each row contains the following columns:

Table 1. Information returned by the BIGSQL_ENV table function
Column name	Data type	Description
TYPE	VARCHAR(20)	Identifies the environment variable type, which is one of the following values: ENV (system environment variables) SYSPROP (current system properties) HIVEPROP (Hive configuration properties, which are a superset of Db2 Big SQL and Hive properties) BIGSQL (Db2 Big SQL-specific environment variables)
NAME	VARCHAR(256)	Identifies the name of the environment variable. If the name is longer than 255 characters, it is truncated.
VALUE	VARCHAR(32672)	Shows the value of the environment variable. If the value is longer than 32671 characters, it is split across multiple rows with the same name and type values.

Usage

If a particular VALUE exceeds 32671 characters, it is split across multiple rows. An appended ellipsis indicates that the value continues in the next row, and a prepended ellipsis indicates that the value continues from the previous row. The following example, which is unrealistically short for demonstration purposes, illustrates this behavior:


TYPE                 NAME                           VALUE
-------------------- ------------------------------ ----------------------------------------
ENV                  EXAMPLE                        1234567890123456789012345 ...
ENV                  EXAMPLE                        ... 6789012345678901234567890 ...
ENV                  EXAMPLE                        ... 12345678901234567890

By default, the table is first sorted by TYPE in the following order: "ENV", "SYSPROP", "HIVEPROP", "BIGSQL". It is then sorted alphabetically by NAME. Unless a different sort order is required, there is no need to include an ORDER BY clause in your query.

Examples

SELECT
    TYPE,
    VARCHAR(NAME, 30) AS NAME,
    VARCHAR(VALUE, 40) AS VALUE
  FROM TABLE(SYSHADOOP.BIGSQL_ENV());

TYPE                 NAME                           VALUE
-------------------- ------------------------------ ----------------------------------------
ENV                  BIGSQL_DIST_HOME               /home/bigsql/sqllib/bigsql
ENV                  BIGSQL_DIST_VAR                /home/bigsql/sqllib/db2dump
ENV                  BIGSQL_HOME                    /home/bigsql/sqllib/bigsql/dfs
ENV                  BIGSQL_ODP_CURRENT_VERSION     7.1.3-1
ENV                  BIGSQL_ODP_STACK_NAME          CDH
...

SELECT
    TYPE,
    VARCHAR(NAME, 50) AS NAME,
    VARCHAR(VALUE, 10) AS VALUE
  FROM TABLE(SYSHADOOP.BIGSQL_ENV())
  WHERE NAME = 'hive.metastore.batch.retrieve.table.partition.max';

TYPE                 NAME                                               VALUE
-------------------- -------------------------------------------------- ----------
HIVEPROP             hive.metastore.batch.retrieve.table.partition.max  1000

  1 record(s) selected.