Hitachi

Hitachi Advanced Database Setup and Operation Guide

B.22 Searching a dictionary table

You can use the SELECT statement to search a dictionary table. The authorization identifier for a dictionary table is MASTER.

Once you have searched a dictionary table, immediately execute the COMMIT statement to terminate the transaction. If you do not execute the COMMIT statement, the lock on the dictionary table will not be released. Note that when you terminate the adbsql command after having searched a dictionary table, the lock on the dictionary table is released and you do not need to execute a COMMIT statement.

The information that can be searched depends on whether the HADB user who is searching the dictionary table has the DBA privilege. For details, see (3) Scope of information in dictionary tables that can be referenced by HADB users in B.1 Dictionary table overview.

The follow subsections provide specification examples of using the SELECT statement to search dictionary tables.

Organization of this subsection

(1) When identifying the table name from a table ID

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME"
  FROM "MASTER"."SQL_TABLES"
    WHERE "TABLE_ID"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter a table ID.

To Page Top

(2) Finding a list of row store tables

Use the following SELECT statement to search a dictionary table:

Specification example
SELECT "TABLE_SCHEMA", "TABLE_NAME"
  FROM "MASTER"."SQL_TABLES"
    WHERE "STORAGE_FORMAT" = 'ROW'

To Page Top

(3) Finding a list of column store tables

Use the following SELECT statement to search a dictionary table:

Specification example
SELECT "TABLE_SCHEMA", "TABLE_NAME"
  FROM "MASTER"."SQL_TABLES"
    WHERE "STORAGE_FORMAT" = 'COLUMN'

To Page Top

(4) When identifying the index name from an index ID

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","INDEX_NAME"
  FROM "MASTER"."SQL_INDEXES"
    WHERE "INDEX_ID"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter an index ID.

To Page Top

(5) When identifying the name of a table for which an index is defined from an index ID

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME"
  FROM "MASTER"."SQL_INDEXES"
    WHERE "INDEX_ID"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter an index ID.

To Page Top

(6) When identifying the name of a column for which an index is defined from an index ID

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME","COLUMN_NAME"
  FROM "MASTER"."SQL_INDEX_COLINF" WHERE "INDEX_ID" = ?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter an index ID.

To Page Top

(7) When identifying the index name of a range index

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","INDEX_NAME"
  FROM "MASTER"."SQL_INDEXES"
    WHERE "INDEX_TYPE"='R'

To Page Top

(8) When identifying all index names defined in a table from the table name

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","INDEX_NAME"
  FROM "MASTER"."SQL_INDEXES"
    WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request.

To Page Top

(9) When identifying viewed table names of all viewed tables that use tables from the table names

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "VIEW_SCHEMA","VIEW_NAME"
  FROM "MASTER"."SQL_VIEW_TABLE_USAGE"
    WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request.

To Page Top

(10) When identifying the table names of all defined tables from the schema names

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME","TABLE_TYPE"
  FROM "MASTER"."SQL_TABLES"
    WHERE "TABLE_SCHEMA"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter a schema name.

Note that the value of TABLE_TYPE indicates the type of the table. The value 'R' means a base table, and the value 'V' means a viewed table.

To Page Top

(11) When identifying the index names of all defined indexes from the schema names

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","INDEX_NAME","INDEX_TYPE"
  FROM "MASTER"."SQL_INDEXES"
    WHERE "TABLE_SCHEMA"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter a schema name.

Note that the value of INDEX_TYPE indicates the type of the index. The value 'B' means a B-tree index, the value 'T' means a text index, and the value 'R' means a range index.

To Page Top

(12) When identifying the name of the DB area that stores indexes from the index names

Use the following SELECT statement to search dictionary tables.

Specification example
SELECT "MASTER"."SQL_DBAREAS"."DBAREA_NAME"
  FROM "MASTER"."SQL_DBAREAS","MASTER"."SQL_DIV_INDEX"
    WHERE "MASTER"."SQL_DIV_INDEX"."TABLE_SCHEMA"=?
      AND "MASTER"."SQL_DIV_INDEX"."INDEX_NAME"=?
      AND "MASTER"."SQL_DBAREAS"."DBAREA_ID"="MASTER"."SQL_DIV_INDEX"."DBAREA_ID"

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and an index identifier for the second input request.

To Page Top

(13) When identifying the maximum number of chunks created in all multi-chunk tables

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_NAME","N_CHUNK_RESERVED"
  FROM "MASTER"."SQL_TABLES"
    WHERE "IS_CHUNK" = 'Y'
      ORDER BY "TABLE_NAME"

To Page Top

(14) When identifying the table name of a table stored in a DB area

Use the following SELECT statement to search dictionary tables.

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME" FROM "MASTER"."SQL_DIV_TABLE"
  WHERE "DBAREA_ID"=
   (SELECT "DBAREA_ID" FROM "MASTER"."SQL_DBAREAS" WHERE "DBAREA_NAME"=?)

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter a DB area name.

To Page Top

(15) When identifying the index name of an index stored in a DB area

Use the following SELECT statement to search dictionary tables.

Specification example
SELECT "TABLE_SCHEMA","INDEX_NAME" FROM "MASTER"."SQL_DIV_INDEX"
  WHERE "DBAREA_ID"=
   (SELECT "DBAREA_ID" FROM "MASTER"."SQL_DBAREAS" WHERE "DBAREA_NAME"=?)

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter a DB area name.

To Page Top

(16) When determining whether a viewed table is updatable

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "IS_UPDATABLE" FROM "MASTER"."SQL_VIEWS"
  WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and enter a table identifier of the viewed table for the second input request.

If the search result is 'Y', the viewed table is updatable. If the search result is 'N', the viewed table is read-only and therefore cannot be updated.

To Page Top

(17) When checking the user privileges and schema operation privilege that an HADB user has

Use the following SELECT statement to search the dictionary table.

An HADB user who has the DBA privilege can check the information on the user privileges and schema operation privileges that all HADB users have. An HADB user (with the authorization identifier that was used for the current connection to the HADB server) who does not have the DBA privilege can only check the information on his or her own user privileges and schema operation privilege.

Specification example
SELECT "DBA_PRIVILEGE","CONNECT_PRIVILEGE","SCHEMA_PRIVILEGE"
  FROM "MASTER"."SQL_USERS" WHERE "USER_NAME"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter an HADB user authorization identifier for the input request.

If the value of a column in the search result is 'Y', the HADB user has the relevant user privilege or schema operation privilege. If the value is 'N', the HADB user does not have the relevant user privilege or schema operation privilege.

To Page Top

(18) When determining the index name of the index that corresponds to the primary key

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "TABLE_SCHEMA","INDEX_NAME" FROM "MASTER"."SQL_INDEXES"
  WHERE "TABLE_SCHEMA" = ? AND "TABLE_NAME" = ? AND "IS_PRIMARY_KEY" = 'Y'

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and enter a table identifier for the second input request.

To Page Top

(19) When checking whether the index is a range index that can skip chunks

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "IS_CHUNK_SKIP" FROM "MASTER"."SQL_INDEXES"
  WHERE "TABLE_SCHEMA" = ? AND "INDEX_NAME" = ?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and enter an index identifier for the second input request.

If the search result is 'Y', the index is a range index that can skip chunks. If the search result is the null value, the index is either a range index that cannot skip chunks or not a range index.

To Page Top

(20) When checking the names of the columns comprising a primary key and foreign keys

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "COLUMN_NAME"
  FROM "MASTER"."SQL_KEY_COLUMN_USAGE"
    WHERE "CONSTRAINT_SCHEMA" = ? AND "CONSTRAINT_NAME" = ?
      ORDER BY "COLUMN_ORDER" ASC

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. For the first input request, enter a schema name that includes a constraint; for the second input request, enter a constraint name.

To Page Top

(21) When checking foreign keys that reference a primary key

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "CONSTRAINT_SCHEMA","CONSTRAINT_NAME"
  FROM "MASTER"."SQL_REFERENTIAL_CONSTRAINTS"
    WHERE "PRIMARY_CONSTRAINT_SCHEMA" = ?
      AND "PRIMARY_CONSTRAINT_NAME" = ?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. For the first input request, enter a schema name that includes a constraint; for the second input request, enter a constraint name.

To Page Top

(22) When checking whether a viewed table has been invalidated

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "IS_INVALID" FROM "MASTER"."SQL_VIEWS"
  WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. For the first input request, enter the schema name of the viewed table; for the second input request, enter the table identifier of the viewed table.

If the search result is 'Y', the viewed table has been invalidated. If the search result is the null value, the viewed table has not been invalidated.

To Page Top

(23) When checking the access privileges for a table

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "GRANTOR",
       "SELECT_PRIVILEGE","INSERT_PRIVILEGE","UPDATE_PRIVILEGE",
       "DELETE_PRIVILEGE","TRUNCATE_PRIVILEGE",
       "REFERENCES_PRIVILEGE","IMPORT_TABLE_PRIVILEGE",
       "REBUILD_INDEX_PRIVILEGE","GET_COSTINFO_PRIVILEGE",
       "EXPORT_TABLE_PRIVILEGE","MERGE_CHUNK_PRIVILEGE",
       "CHANGE_CHUNK_COMMENT_PRIVILEGE","CHANGE_CHUNK_STATUS_PRIVILEGE",
       "ARCHIVE_CHUNK_PRIVILEGE","UNARCHIVE_CHUNK_PRIVILEGE"
  FROM "MASTER"."SQL_TABLE_PRIVILEGES"
    WHERE "GRANTEE" IN ('PUBLIC', ?)
      AND "TABLE_SCHEMA"=? AND "TABLE_NAME"=?

If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. For the first input request, enter the authorization identifier of the HADB user to whom the access privilege was granted; for the second input request, enter the schema name of the table. For the third input request, enter the table identifier of the table.

Under the column GRANTOR, the authorization identifier of the HADB user who granted the access privilege is output.

For other columns, the system outputs whether the HADB user has each access privilege. If the search result is 'G', the HADB user has access privileges with a grant option for the table. If the search result is 'Y', the HADB user has access privileges (without a grant option) for the table. If the search result is 'N', the HADB user does not have access privileges for the table.

To Page Top

(24) Finding out whether a base table is a multi-chunk table, archivable multi-chunk table, or column store table

By using the SELECT statement in the specification example to search dictionary tables, you can find out the following information:

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME","IS_CHUNK","IS_ARCHIVABLE","STORAGE_FORMAT"
  FROM "MASTER"."SQL_TABLES"
    WHERE "TABLE_SCHEMA" = ? AND "TABLE_NAME" = ?

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier.

Example search results
TABLE_SCHEMA  TABLE_NAME  IS_CHUNK  IS_ARCHIVABLE  STORAGE_FORMAT
------------  ----------  --------  -------------  --------------
ADBUSER01     T1                 Y              *  COLUMN

In this example, the type of the base table ADBUSER01.T1 (hereafter Table T1) is displayed.

  • The IS_CHUNK column shows whether Table T1 is a multi-chunk table. If 'Y' is displayed, Table T1 is a multi-chunk table. If 'N' is displayed, Table T1 is a single-chunk table.

  • The IS_ARCHIVABLE column shows whether Table T1 is an archivable multi-chunk table. If 'Y' is displayed, Table T1 is an archivable multi-chunk table. If a null value is displayed, Table T1 is not an archivable multi-chunk table. In this example, * represents a null value.

  • The STORAGE_FORMAT column shows whether Table T1 is a row store table or a column store table. If 'COLUMN' is displayed, Table T1 is a column store table. If 'ROW' is displayed, Table T1 is a row store table.

In this example, the output shows that Table T1 is a regular multi-chunk table and a column store table.

To Page Top

(25) Finding out the archive directory for an archivable multi-chunk table

Search the dictionary table by using the following SELECT statement.

Specification example
SELECT "ARCHIVE_DIRECTORY_PATH" FROM "MASTER"."SQL_TABLES"
  WHERE "TABLE_SCHEMA" = ? AND "TABLE_NAME" = ?

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier.

To Page Top

(26) Finding out the name of the archive range column in an archivable multi-chunk table

Search the dictionary table by using the following SELECT statement.

Specification example
SELECT "COLUMN_NAME" FROM "MASTER"."SQL_COLUMNS"
  WHERE "TABLE_SCHEMA" = ? AND "TABLE_NAME" = ? AND "IS_ARCHIVE_RANGE_COLUMN" = 'Y'

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier.

To Page Top

(27) Finding out the range indexes defined for the archive range column in an archivable multi-chunk table

Search the dictionary table by using the following SELECT statement.

Specification example
SELECT "IX"."TABLE_SCHEMA","IX"."INDEX_NAME"
  FROM "MASTER"."SQL_INDEXES" "IX"
      ,"MASTER"."SQL_INDEX_COLINF" "IC"
      ,"MASTER"."SQL_COLUMNS" "CL"
  WHERE "IX"."TABLE_SCHEMA" = ?
    AND "IX"."TABLE_NAME" = ?
    AND "IX"."INDEX_TYPE" = 'R'
    AND "IX"."INDEX_ID" = "IC"."INDEX_ID"
    AND "IC"."TABLE_SCHEMA" = "CL"."TABLE_SCHEMA"
    AND "IC"."TABLE_NAME" = "CL"."TABLE_NAME"
    AND "IC"."COLUMN_NAME" = "CL"."COLUMN_NAME"
    AND "CL"."IS_ARCHIVE_RANGE_COLUMN" = 'Y'

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier.

To Page Top

(28) Finding out base table definition information

When redefining a base table, if you do not know the specifications for the CREATE TABLE statements, execute the SELECT statements in specification examples 1 and 2, and search the dictionary table.

In addition, when executing a SELECT statement by executing the adbsql command, specify the -s option. The search results of the SELECT statement can be output to standard output in CSV format.

Note

For information about the adbsql command, see adbsql (Execute SQL Statements) in the manual HADB Command Reference.

Specification example 1
SELECT "MT"."TABLE_SCHEMA"                       AS "schema-name"
      ,"MT"."TABLE_NAME"                         AS "table-identifier"
      ,"MT"."FREE_AREA"                          AS "unused-area-of-table-specification"
      ,"MT"."FIX_TABLE"                          AS "FIX-specification"
      ,"MT"."IS_BRANCH_ALL"                      AS "BRANCH ALL-specification"
      ,"MT"."IS_CHUNK"                           AS "chunk-specification"
      ,"MT"."N_CHUNK_RESERVED"                   AS "chunk-maximum-value"
      ,"MT"."IS_ARCHIVABLE"                      AS "chunk-archive-specification"
      ,"MT"."ARCHIVE_DIRECTORY_PATH"             AS "archive-directory"
      ,"MT"."STORAGE_FORMAT"                     AS "table-data-storage-format"
      ,"MR"."DBAREA_NAME"                        AS "name-of-DB-area-storing-table"
      ,"MC"."COLUMN_ID"                          AS "column-ID"
      ,"MC"."COLUMN_NAME"                        AS "column-name"
      ,CONVERT("MC"."DATA_TYPE_CODE", CHAR(9), '0XXXXXXX')
                                                 AS "data-type-code"
      ,CONVERT("MC"."DATA_LENGTH", CHAR(9), '0XXXXXXX')
                                                 AS "data-lenght"
      ,"MC"."IS_NULLABLE"                        AS "whether-null-value-is-permitted"
      ,"MC"."BRANCH"                             AS "branch specification"
      ,"MC"."DEFAULT_VALUE"                      AS "default-value"
      ,"MC"."IS_DEFAULT_COLUMN"                  AS "DEFAULT-clause-specification"
      ,"MC"."PRIMARY_KEY_COLUMN_SEQUENCE_NUMBER" AS "primary-key-configuration-sequence"
      ,"MC"."IS_ARCHIVE_RANGE_COLUMN"            AS "archive-range-column-specification"
      ,"MC"."COMPRESSION_TYPE"                   AS "column-data-compression-type"
FROM "MASTER"."SQL_TABLES"    "MT"
    ,"MASTER"."SQL_DIV_TABLE" "MD"
    ,"MASTER"."SQL_DBAREAS"   "MR"
    ,"MASTER"."SQL_COLUMNS"   "MC"
WHERE "MT"."TABLE_SCHEMA" = ?
  AND "MT"."TABLE_NAME" = ?
  AND "MT"."TABLE_TYPE" = 'R'
  AND "MT"."TABLE_ID" = "MD"."TABLE_ID"
  AND "MD"."DBAREA_ID" = "MR"."DBAREA_ID"
  AND "MT"."TABLE_SCHEMA" = "MC"."TABLE_SCHEMA"
  AND "MT"."TABLE_NAME" = "MC"."TABLE_NAME"
ORDER BY "MT"."TABLE_SCHEMA" ASC
        ,"MT"."TABLE_NAME"   ASC
        ,"MC"."COLUMN_ID"    ASC

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier.

Specification example 2
SELECT "MT"."TABLE_SCHEMA"            AS "schema-name"
      ,"MT"."TABLE_NAME"              AS "table-identifier"
      ,"MT"."CONSTRAINT_SCHEMA"       AS "schema-name-including-constraint"
      ,"MT"."CONSTRAINT_NAME"         AS "constraint-name"
      ,"MT"."CONSTRAINT_TYPE"         AS "constraint-type"
      ,"MC"."COLUMN_NAME"             AS "column-name"
      ,"MC"."COLUMN_ORDER"            AS "definition-order"
      ,"MR"."REFERENCED_TABLE_SCHEMA" AS "referenced-table-schema-name"
      ,"MR"."REFERENCED_TABLE_NAME"   AS "referenced-table-table-identifier"
      ,"MR"."IS_DISABLE"              AS "referential-constraint-check-suppression-specification"
FROM "MASTER"."SQL_TABLE_CONSTRAINTS" "MT"
INNER JOIN "MASTER"."SQL_KEY_COLUMN_USAGE" "MC"
  ON "MT"."CONSTRAINT_SCHEMA" = "MC"."CONSTRAINT_SCHEMA"
 AND "MT"."CONSTRAINT_NAME" = "MC"."CONSTRAINT_NAME"
LEFT OUTER JOIN "MASTER"."SQL_REFERENTIAL_CONSTRAINTS" "MR"
  ON "MT"."CONSTRAINT_SCHEMA" = "MR"."CONSTRAINT_SCHEMA"
 AND "MT"."CONSTRAINT_NAME" = "MR"."CONSTRAINT_NAME"
WHERE "MT"."TABLE_SCHEMA" = ?
  AND "MT"."TABLE_NAME" = ?
ORDER BY "MT"."TABLE_SCHEMA"      ASC
        ,"MT"."TABLE_NAME"        ASC
        ,"MT"."CONSTRAINT_SCHEMA" ASC
        ,"MT"."CONSTRAINT_NAME"   ASC
        ,"MC"."COLUMN_ORDER"      ASC

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier.

To Page Top

(29) Finding out index definition information

When redefining indexes, if you do not know the specifications for the CREATE INDEX statements, search the dictionary table by using the following SELECT statement.

In addition, when executing a SELECT statement by executing the adbsql command, specify the -s option. The search results of the SELECT statement can be output to standard output in CSV format.

Note

For details about the adbsql command, see adbsql (Execute SQL Statements) in the manual HADB Command Reference.

Specification example
SELECT "MI"."TABLE_SCHEMA"            AS "schema-name"
      ,"MI"."TABLE_NAME"              AS "table-identifier"
      ,"MI"."INDEX_NAME"              AS "index-identifier"
      ,"MC"."INDEX_ORDER"             AS "indexed-column-order"
      ,"MC"."COLUMN_NAME"             AS "column-name"
      ,"MC"."ASC_DESC"                AS "ascending-or-descending"
      ,"MI"."UNIQUE_TYPE"             AS "UNIQUE"
      ,"MI"."FREE_AREA"               AS "index-unused-area-specification"
      ,"MI"."INDEX_TYPE"              AS "index-type-specification"
      ,"MI"."IS_EXCLUDE_NULL_VALUES"  AS "null-value-exclusion-specification"
      ,"MI"."IS_TEXT_CORRECTION_RULE" AS "notation-correction-search text-index-specification"
      ,"MI"."IS_TEXT_WORDCONTEXT"     AS "text-index-word-context-search-specification"
      ,"MI"."TEXT_DELIMITER_TYPE"     AS "text-index-delimiter-specification"
      ,"MR"."DBAREA_NAME"             AS "name-of-DB-area-storing-indexes"
FROM "MASTER"."SQL_INDEXES"      "MI"
    ,"MASTER"."SQL_DIV_INDEX"    "MD"
    ,"MASTER"."SQL_DBAREAS"      "MR"
    ,"MASTER"."SQL_INDEX_COLINF" "MC"
WHERE "MI"."TABLE_SCHEMA" = ?
  AND "MI"."TABLE_NAME" = ?
  AND "MI"."INDEX_ID" = "MD"."INDEX_ID"
  AND "MI"."INDEX_ID" = "MC"."INDEX_ID"
  AND "MD"."DBAREA_ID" = "MR"."DBAREA_ID"
ORDER BY "MI"."TABLE_SCHEMA" ASC
        ,"MI"."TABLE_NAME"   ASC
        ,"MI"."INDEX_NAME"   ASC
        ,"MC"."INDEX_ORDER"  ASC

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier.

To Page Top

(30) Investigating whether range indexes are defined in the column specified as the archive range column

When changing a regular multi-chunk table into an archivable multi-chunk table by using ALTER TABLE statements, you need to investigate whether range indexes are defined in the column specified as the archive range column.

When investigating whether range indexes are defined in the column specified as the archive range column, search the dictionary table by using the following SELECT statement.

Specification example
SELECT "TABLE_SCHEMA","INDEX_NAME" FROM "MASTER"."SQL_INDEXES" "IX"
  WHERE "TABLE_SCHEMA" = ?
    AND "TABLE_NAME" = ?
    AND "INDEX_TYPE" = 'R'
    AND EXISTS(SELECT * FROM "MASTER"."SQL_INDEX_COLINF" "IC"
                 WHERE "IX"."TABLE_SCHEMA" = "IC"."TABLE_SCHEMA"
                   AND "IX"."INDEX_NAME" = "IC"."INDEX_NAME"
                   AND "IC"."COLUMN_NAME" = ?)

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters.

To Page Top

(31) Finding out viewed table definition information

When redefining viewed tables, if you do not know the specifications for the CREATE VIEW statements, search the dictionary table by using the following SELECT statement.

In addition, when executing a SELECT statement by executing the adbsql command, specify the -s option. The search results of the SELECT statement can be output to standard output in CSV format.

Note

For details about the adbsql command, see adbsql (Execute SQL Statements) in the manual HADB Command Reference.

Specification example
SELECT "MV"."TABLE_SCHEMA"         AS "schema-name"
      ,"MV"."TABLE_NAME"           AS "table-identifier"
      ,"MS"."DEFINE_SOURCE"        AS "viewed-table-statement"
      ,"ME"."DELETE_RESERVED_WORD" AS "reserved-word-for-deletion"
      ,"ME"."DEFINE_VR"            AS "definition-version"
FROM "MASTER"."SQL_VIEWS" "MV"
    ,"MASTER"."SQL_DEFINE_SOURCE" "MS"
    ,"MASTER"."SQL_DEFINE_ENVIRONMENT" "ME"
WHERE "MV"."TABLE_SCHEMA" = ?
  AND "MV"."TABLE_NAME" = ?
  AND "MV"."DEFINE_SOURCE_ID" = "MS"."SOURCE_ID"
  AND "MV"."VIEW_ID" = "ME"."RESOURCE_ID"

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name of the viewed table. The second time, enter the table identifier of the viewed table.

Example search results

[Figure]

The information displayed as the view definition statement is the information specified in the CREATE VIEW statements for the viewed table V11 (schema name ADBUSER01)

Note the following points when redefining a viewed table after searching the dictionary table by using the preceding SELECT statement:

To Page Top

(32) Finding a list of dependent viewed tables, and the definition information of each of the viewed tables

To find a list of dependent viewed tables, and the definition information of each of the viewed tables, search the dictionary table by using the following SELECT statement.

In addition, when executing a SELECT statement by executing the adbsql command, specify the -s option. The search results of the SELECT statement can be output to standard output in CSV format.

Note

For details about the adbsql command, see adbsql (Execute SQL Statements) in the manual HADB Command Reference.

Specification example
SELECT "MV"."TABLE_SCHEMA"         AS "schema-name"
      ,"MV"."TABLE_NAME"           AS "table-identifier"
      ,"MV"."IS_INVALID"           AS "invalid-or-not-invalid"
      ,"MV"."VIEW_LEVEL"           AS "view-level"
      ,"MS"."DEFINE_SOURCE"        AS "view-definition-statement"
      ,"ME"."DELETE_RESERVED_WORD" AS "reserved-word-for-deletion"
      ,"ME"."DEFINE_VR"            AS "definition-version"
 FROM  "MASTER"."SQL_VIEW_TABLE_USAGE" "MVU"
     ,"MASTER"."SQL_VIEWS" "MV"
     ,"MASTER"."SQL_DEFINE_SOURCE" "MS"
     ,"MASTER"."SQL_DEFINE_ENVIRONMENT" "ME"
 WHERE "MVU"."TABLE_SCHEMA"=? 
   AND "MVU"."TABLE_NAME" = ?
   AND "MV"."TABLE_SCHEMA"     = "MVU"."VIEW_SCHEMA"
   AND "MV"."TABLE_NAME"       = "MVU"."VIEW_NAME"
   AND "MV"."DEFINE_SOURCE_ID" = "MS"."SOURCE_ID"
   AND "ME"."RESOURCE_TYPE"    = 'V'
   AND "MV"."VIEW_ID"          = "ME"."RESOURCE_ID"
 ORDER BY "view-level" ASC

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the table schema name. The second time, enter the table's table identifier.

The list of dependent viewed tables, and the definition information of each of the viewed tables, is displayed.

Example search results

[Figure]

The preceding viewed tables (V12, V11, and V3) are viewed tables that depend on the table specified in the dynamic parameters.

The information displayed in the viewed table statements is the information specified in the CREATE VIEW statements for each viewed table.

Note the following points when redefining a viewed table after searching the dictionary table by using the preceding SELECT statement:

To Page Top

(33) Finding a list of dependent viewed tables

To find a list of dependent viewed tables, search the dictionary table by using the following SELECT statement.

Specification example
SELECT "MV"."TABLE_SCHEMA"         AS "schema-name"
      ,"MV"."TABLE_NAME"           AS "table-identifier"
      ,"MV"."IS_INVALID"           AS "invalid-or-not-invalid"
FROM  "MASTER"."SQL_VIEW_TABLE_USAGE" "MVU"
     ,"MASTER"."SQL_VIEWS" "MV"
WHERE "MVU"."TABLE_SCHEMA"=? 
   AND "MVU"."TABLE_NAME" = ?
   AND "MV"."TABLE_SCHEMA"     = "MVU"."VIEW_SCHEMA"
   AND "MV"."TABLE_NAME"       = "MVU"."VIEW_NAME"

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the table schema name. The second time, enter the table's table identifier.

The list of viewed tables that are dependent on the specified table is displayed.

Example search results

[Figure]

The preceding viewed tables (V12, V3, and V11) are viewed tables that depend on the table specified in the dynamic parameters.

To Page Top

(34) Checking index options specified for text indexes

To find out which index options are specified for a text index, use the following SELECT statement to retrieve data from the dictionary table:

Specification example
SELECT "TABLE_SCHEMA"
      ,"INDEX_NAME"
      ,"INDEX_TYPE"
      ,"IS_TEXT_CORRECTION_RULE"
      ,"IS_TEXT_WORDCONTEXT"
      ,"TEXT_DELIMITER_TYPE"
  FROM "MASTER"."SQL_INDEXES"
    WHERE "TABLE_SCHEMA" = ?
      AND "INDEX_NAME" = ?

If you execute the preceding SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the index identifier.

To Page Top

(35) Checking which base tables and viewed tables are underlying tables of a viewed table

To identify the base tables and viewed tables that are the underlying tables of a viewed table, use the following SELECT statement to search a dictionary table:

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME"
  FROM "MASTER"."SQL_VIEW_TABLE_USAGE"
    WHERE "VIEW_SCHEMA"=? AND "VIEW_NAME"=? AND "IS_DIRECT"='Y'

If you execute the preceding SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name of the viewed table. The second time, enter the table identifier of the viewed table.

To Page Top

(36) Checking the authorization identifiers and audit privileges of auditors

Use the following SELECT statement to search a dictionary table.

Specification example
SELECT "USER_NAME","AUDIT_ADMIN_PRIVILEGE","AUDIT_VIEWER_PRIVILEGE"
  FROM "MASTER"."SQL_USERS" 
    WHERE "AUDIT_ADMIN_PRIVILEGE"='Y' OR "AUDIT_VIEWER_PRIVILEGE"='Y'
Example search results
USER_NAME      AUDIT_ADMIN_PRIVILEGE  AUDIT_VIEWER_PRIVILEGE
-------------  ---------------------  ----------------------
ADBAUDITADMIN                      Y                       N
ADBAUDITOR                         N                       Y

A list of auditors is displayed.

The USER_NAME column displays the authorization identifier of the auditor.

If 'Y' appears in the AUDIT_ADMIN_PRIVILEGE column, that auditor has the audit admin privilege.

If 'Y' appears in the AUDIT_VIEWER_PRIVILEGE column, that auditor has the audit viewer privilege.

To Page Top

(37) Checking the page size of the work table DB area specified when the adbinit command was executed

Use the following SELECT statement to search the dictionary table.

Specification example
SELECT "PAGE_SIZE"
  FROM "MASTER"."SQL_DBAREAS"
    WHERE "DBAREA_NAME"='ADBWRK'

The page size of the work table DB area that was specified when the adbinit command was executed is output in the PAGE_SIZE column in bytes.

To Page Top

(38) Checking the tables subject to the updated-row columnizing facility (checking the column store tables for which no B-tree indexes are defined)

Use the following SELECT statement to search the dictionary table.

Specification example
SELECT "TABLE_SCHEMA","TABLE_NAME"
  FROM "MASTER"."SQL_TABLES"
    WHERE "STORAGE_FORMAT" = 'COLUMN'
      AND "TABLE_ID"
           NOT IN (
                   SELECT "TABLE_ID" 
                     FROM "MASTER"."SQL_INDEXES"
                       WHERE "INDEX_TYPE" = 'B'
                   )

A list of tables subject to the updated-row columnizing facility (column store tables for which no B-tree indexes are defined) is displayed.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.