Using the System Catalog

Informix database servers 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 reside 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 Dynamic Server) sequence objects.

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 (IDS)

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

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.

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.

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, and has no NULL or duplicate values, and the UPDATE STATISTICS statement has been run.

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.

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.

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. Where a hyphen ( - ) appears, the user has not been granted the privilege whose position the hyphen occupies within the tabauth value.

If the tabauth privilege code appears 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.

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.

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).

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

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.