Using the system catalog

HCL OneDB™ automatically generate the system catalog tables when you create a database. You can query the system catalog tables as you would query any other table in the database. The system catalog tables for a newly created database are located in a common area of the disk called a dbspace. Every database has its own system catalog tables. All tables and views in the system catalog have the prefix sys (for example, the systables system catalog table).

Not all tables with the prefix sys are true system catalog tables. For example, the syscdr database supports the Enterprise Replication feature. Non-catalog tables, however, have a tabid >= 100. System catalog tables all have a tabid < 100. See later in this section and SYSTABLES for more information about tabid numbers that the database server assigns to tables, views, synonyms, and (in HCL OneDB) sequence objects.

Tip: Do not confuse the system catalog tables of a database with the tables in the sysmaster, sysutils, syscdr, or (for HCL OneDB) the sysadmin and sysuser databases. The names of tables in those databases also have the sys prefix, but they contain information about an entire database server, which might manage multiple databases. Information in the sysadmin, sysmaster, sysutils, syscdr, and sysuser tables is primarily useful for database server administrators (DBSAs). See also the HCL® OneDB Administrator's Guide and HCL OneDB Administrator's Reference.

The database server accesses the system catalog constantly. Each time an SQL statement is processed, the database server accesses the system catalog to determine system privileges, add or verify table or column names, and so on.

For example, the following CREATE SCHEMA block adds the customer table, with its indexes and privileges, to the stores_demo database. This block also adds a view, california, which restricts the data of the customer table to only the first and last names of the customer, the company name, and the telephone number for all customers who reside in California.

CREATE SCHEMA AUTHORIZATION maryl
CREATE TABLE customer (customer_num SERIAL(101), fname CHAR(15), 
   lname CHAR(15), company CHAR(20), address1 CHAR(20), address2 CHAR(20),
   city CHAR(15), state CHAR(2), zipcode CHAR(5), phone CHAR(18))
GRANT ALTER, ALL ON customer TO cathl WITH GRANT OPTION AS maryl
GRANT SELECT ON customer TO public
GRANT UPDATE (fname, lname, phone) ON customer TO nhowe
CREATE VIEW california AS
   SELECT fname, lname, company, phone FROM customer WHERE state = 'CA'
CREATE UNIQUE INDEX c_num_ix ON customer (customer_num)
CREATE INDEX state_ix ON customer (state)

To process this CREATE SCHEMA block, the database server first accesses the system catalog to verify the following information:

The new table and view names do not already exist in the database. (If the database is ANSI-compliant, the database server verifies that the new names do not already exist for the specified owners.)
The user has permission to create tables and grant user privileges.
The column names in the CREATE VIEW and CREATE INDEX statements exist in the customer table.

In addition to verifying this information and creating two new tables, the database server adds new rows to the following system catalog tables:

systables
syscolumns
sysviews
systabauth
syscolauth
sysindexes
sysindices

Rows added to the systables system catalog table

The following two new rows of information are added to the systables system catalog table after the CREATE SCHEMA block is run.


Column name	First row	Second row
tabname	customer	california
owner	maryl	maryl
partnum	16778361	0
tabid	101	102
rowsize	134	134
ncols	10	4
nindexes	2	0
nrows	0	0
created	01/26/2007	01/26/2007
version	1	0
tabtype	T	V
locklevel	P	B
npused	0	0
fextsize	16	0
nextsize	16	0
flags	0	0
site
dbname

Each table recorded in the systables system catalog table is assigned a tabid, a system-assigned sequential number that uniquely identifies each table in the database. The system catalog tables receive 2-digit tabid numbers, and the user-created tables receive sequential tabid numbers that begin with 100.

Rows added to the syscolumns system catalog table

The CREATE SCHEMA block adds 14 rows to the syscolumns system catalog table. These rows correspond to the columns in the table customer and the view california, as the following example shows.


colname	tabid	colno	coltype	collength
customer_num	101	1	262	4
fname	101	2	0	15
lname	101	3	0	15
company	101	4	0	20
address1	101	5	0	20
address2	101	6	0	20
city	101	7	0	15
state	101	8	0	2
zipcode	101	9	0	5
phone	101	10	0	18
fname	102	1	0	15
lname	102	2	0	15
company	102	3	0	20
phone	102	4	0	18

In the syscolumns table, each column within a table is assigned a sequential column number, colno, that uniquely identifies the column within its table. In the colno column, the fname column of the customer table is assigned the value 2 and the fname column of the view california is assigned the value 1.

The colmin and colmax columns are empty. These columns contain values when a column is the first key (or the only key) in an index, has no NULL or duplicate values, and the UPDATE STATISTICS statement has been run.

Rows added to the sysviews system catalog table

The database server also adds rows to the sysviews system catalog table, whose viewtext column contains each line of the CREATE VIEW statement that defines the view. In that column, the x0 that precedes the column names in the statement (for example, x0.fname) operates as an alias that distinguishes among the same columns that are used in a self-join.

Rows added to the systabauth system catalog table

The CREATE SCHEMA block also adds rows to the systabauth system catalog table. These rows correspond to the user privileges granted on customer and california tables, as the following example shows.


grantor	grantee	tabid	tabauth
maryl	public	101	su-idx--
maryl	cathl	101	SU-IDXAR
maryl	nhowe	101	--*-----
	maryl	102	SU-ID---

The tabauth column specifies the table-level privileges granted to users on the customer and california tables. This column uses an 8-byte pattern, such as s (Select), u (Update), * (column-level privilege), i (Insert), d (Delete), x (Index), a (Alter), and r (References), to identify the type of privilege. In this example, the user nhowe has column-level privileges on the customer table. A hyphen ( - ) means the user has not been granted the privilege whose position the hyphen occupies within the tabauth value.

If the tabauth privilege code is in uppercase (for example, S for Select), the user has this privilege and can also grant it to others; but if the privilege code is lowercase (for example, s for Select), the user cannot grant it to others.

Rows added to the syscolauth system catalog table

In addition, three rows are added to the syscolauth system catalog table. These rows correspond to the user privileges that are granted on specific columns in the customer, table as the following example shows.


grantor	grantee	tabid	colno	colauth
maryl	nhowe	101	2	-u-
maryl	nhowe	101	3	-u-
maryl	nhowe	101	10	-u-

The colauth column specifies the column-level privileges that are granted on the customer table. This column uses a 3-byte, pattern such as s (Select), u (Update), and r (References), to identify the type of privilege. For example, the user nhowe has Update privileges on the second column (because the colno value is 2) of the customer table (indicated by tabid value of 101).

Rows added to the sysindexes or the sysindices table

The CREATE SCHEMA block adds two rows to the sysindexes system catalog table (the sysindices table for HCL OneDB). These rows correspond to the indexes created on the customer table, as the following example shows.


idxname	c_num_ix	state_ix
owner	maryl	maryl
tabid	101	101
idxtype	U	D
clustered
part1	1	8
part2	0	0
part3	0	0
part4	0	0
part5	0	0
part6	0	0
part7	0	0
part8	0	0
part9	0	0
part10	0	0
part11	0	0
part12	0	0
part13	0	0
part14	0	0
part15	0	0
part16	0	0
levels
leaves
nunique
clust
idxflags

In this table, the idxtype column identifies whether the created index requires unique values (U) or accepts duplicate values (D). For example, the c_num_ix index on the customer.customer_num column is unique.