B.1 Dictionary table overview
Dictionary tables store table and index definition information as well as DB area information.
HADB provides two types of dictionary tables, base tables and viewed tables.
-
Dictionary table (base table)
In this manual, a dictionary table that is a base table is called a dictionary table (base table). The schema name of a dictionary table (base table) is HADB.
-
Dictionary table (viewed table)
In this manual, a dictionary table that is a viewed table is called a dictionary table. The schema name of a dictionary table is MASTER.
The following figure shows the relationship between a dictionary table (base table) and a dictionary table.
|
|
![[Figure]](GRAPHICS/F0000947.GIF)
- Explanation:
-
Definition information for tables and indexes as well as DB area information is stored in dictionary tables (base tables). A viewed table that is created from a dictionary table (base table) for search purposes becomes a dictionary table.
HADB users can search dictionary tables, but not dictionary tables (base tables).
An HADB user can check definition information of tables and indexes and DB area information by using the SELECT statement to search dictionary tables. When you search dictionary tables, specify MASTER as the schema name.
- Organization of this subsection
(1) List of dictionary tables
The following table lists the dictionary tables.
|
No. |
Dictionary table name |
Information stored |
Table ID of the dictionary table (base table) that corresponds to the dictionary table |
Number of subqueries included in the view definition# |
Existence of external reference |
|---|---|---|---|---|---|
|
1 |
Stores table definition information of base tables, viewed tables, dictionary tables (base tables), system tables (base tables), dictionary tables, and system tables. Each row stores information for one table. |
0x00020001 |
1 |
Y |
|
|
2 |
Stores column definition information of base tables, viewed tables, dictionary tables (base tables), system tables (base tables), dictionary tables, and system tables. Each row stores information for one column. |
0x00020002 |
1 |
Y |
|
|
3 |
Stores information in locations such as DB areas that store base tables, dictionary tables (base tables), and system tables (base tables). Each row stores information for one table. |
0x00020004 |
1 |
Y |
|
|
4 |
Stores index definition information for base tables. This table also stores B-tree index definition information for dictionary tables (base tables) and system tables (base tables). Each row stores information for one index. |
0x00020003 |
1 |
Y |
|
|
5 |
Stores information about DB areas that store indexes of base tables. This table also stores information about DB areas that store B-tree indexes for dictionary tables (base tables) and system tables (base tables). Each row stores information for one index. |
0x00020005 |
1 |
Y |
|
|
6 |
Stores DB area definition information. Each row stores information for one DB area. |
0x00020006 |
0 |
N |
|
|
7 |
Stores schema-related definition information. Each row stores information for one schema. |
0x00020007 |
1 |
Y |
|
|
8 |
Stores viewed table definition information. Each row stores information for one viewed table. |
0x00020008 |
1 |
Y |
|
|
9 |
Stores the definition information of the underlying tables of a viewed table. Each row stores information for one underlying table. |
0x00020009 |
1 |
Y |
|
|
10 |
SQL_VIEW_OBJECT |
Stores information that is used by the system. There is no dictionary table. There is only a dictionary table (base table). |
0x0002000A |
-- |
-- |
|
11 |
Stores SQL statement information. Each row stores information for one SQL text. |
0x0002000B |
2 |
Y |
|
|
12 |
Stores the environment information that was in effect during view definition or when changing the view definition. Each row stores the environment information of one viewed table. |
0x0002000C |
2 |
Y |
|
|
13 |
Stores HADB user information. Each row stores information on one HADB user. |
0x0002000D |
1 |
N |
|
|
14 |
Stores constraint information related to base tables. Each row stores information on one constraint. |
0x0002000E |
1 |
Y |
|
|
15 |
Stores information related to indexed columns. Each row stores information on one indexed column. |
0x0002000F |
1 |
Y |
|
|
16 |
Stores information related to columns comprising the primary key and foreign keys. Each row stores information on one column comprising the primary key and foreign keys. |
0x00020010 |
1 |
Y |
|
|
17 |
Stores information related to referential constraints. Each row stores information on one referential constraint. |
0x00020011 |
1 |
Y |
|
|
18 |
Stores information related to access privileges for tables. Each row stores one table's worth of information on one authorization identifier granted by a privilege grantor. |
0x00020012 |
1 |
Y |
|
|
19 |
Stores information related to audit target definitions defined by the CREATE AUDIT statement. Each row stores information for one audit target definition. |
0x00020013 |
0 |
N |
- Legend:
-
Y: The dictionary table's viewed table includes a subquery that performs an external reference. Therefore, you cannot specify the corresponding dictionary table in a multiset value expression subquery.
N: The dictionary table's viewed table does not include a subquery that performs an external reference.
--: Not applicable.
- #
-
If you specified the corresponding dictionary table in a SQL statement subquery, the number that must be added as the number of nests in that subquery is displayed.
- Note
-
-
A dictionary table also stores the table definition information and index definition information of the dictionary table.
-
The data types of the dictionary tables follow HADB's SQL specification.
-
(2) Times at which a dictionary table is created
A dictionary table is automatically created at the following times:
-
When the HADB server is started for the first time following the completion of database initialization
-
When the HADB server version is upgraded
(3) Scope of information in dictionary tables that can be referenced by HADB users
The scope of information in dictionary tables that can be referenced by HADB users varies according to the privileges they have. The following table shows the dictionary table information that an HADB user can reference.
Note that even for viewed tables specified as an underlying table of the dictionary table, the information a user can reference varies according to the users' privileges. Therefore, even when searching the same viewed table, the HADB user who defined the viewed table and users granted access privileges for that viewed table might not be able to view the same information.
|
Dictionary table name |
Information being referenced |
Privilege of HADB user who references dictionary table |
|||
|---|---|---|---|---|---|
|
DBA privilege |
Audit admin privilege |
Access privilege |
CONNECT privilege |
||
|
SQL_TABLES |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
Y |
C#1 |
N |
|
|
MASTER |
Y |
Y |
Y |
Y |
|
|
SQL_COLUMNS |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1 |
N |
|
|
MASTER |
Y |
Y |
Y |
Y |
|
|
SQL_DIV_TABLE |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_INDEXES |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
Y |
C#1 |
N |
|
|
MASTER |
Y |
Y |
Y |
Y |
|
|
SQL_DIV_INDEX |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_DBAREAS |
-- |
Y |
Y |
Y |
Y |
|
SQL_SCHEMATA |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
Y |
C#1 |
N |
|
|
MASTER |
Y |
Y |
Y |
Y |
|
|
SQL_VIEWS |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1, #3 |
N |
|
|
MASTER |
Y |
Y |
Y |
Y |
|
|
SQL_VIEW_TABLE_USAGE |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
Y |
C#1, #3 |
N |
|
|
MASTER |
Y |
Y |
Y |
Y |
|
|
SQL_DEFINE_SOURCE |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1, #3 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_DEFINE_ENVIRONMENT |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1, #3 |
N |
|
|
MASTER |
Y |
Y |
Y |
Y |
|
|
SQL_USERS |
Their own |
Y |
Y |
-- |
Y |
|
Other user |
Y |
Y |
-- |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_TABLE_CONSTRAINTS |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_INDEX_COLINF |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_KEY_COLUMN_USAGE |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_REFERENTIAL_CONSTRAINTS |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C#1 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_TABLE_PRIVILEGES |
Their own |
C#2 |
C#2 |
C#2 |
C#2 |
|
Other user |
C#2 |
N |
C#1, #2 |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
SQL_AUDITS |
Their own |
N |
Y |
N |
N |
|
Other user |
N |
Y |
N |
N |
|
|
MASTER |
N |
Y |
N |
N |
|
- Legend:
-
Their own: Information related to a schema or schema object for which the HADB user is the owner (his or her own HADB user information in the case of SQL_USERS).
Other user: Information related to a schema or schema object that another HADB user owns (the HADB user information of other HADB users in the case of SQL_USERS).
MASTER: Information related to dictionary tables and system tables.
Y: Can be referenced.
C: Can be referenced but conditions apply.
N: Cannot be referenced.
--: Not applicable.
- #1
-
Users can reference information related to schema objects for which they have the access privilege, and the schema of those schema objects.
- #2
-
When the target schema object is a viewed table that has been invalidated, users cannot retrieve information from SQL_TABLE_PRIVILEGES about access privileges for the invalidated viewed table.
- #3
-
When the target schema object is a viewed table that has been invalidated, users cannot reference definition information related to the invalidated viewed table.