Informix Guide to SQL: Reference Chapter 1: System Catalog

Home

Contents

Index

Master Index

New Book

Using the System Catalog The database server accesses the system catalog constantly. You can access the information in the system catalog tables as well. Each time an SQL statement is processed, the database server accesses the system catalog to determine system privileges, add or verify table names or column names, and so on. For example, the CREATE SCHEMA block in Figure 1-1 adds the customer table, with its respective indexes and privileges, to the stores7 database. This block also adds a view, california, that restricts the information accessible in the customer table to only the first and last names of the customer, the company name, and the phone number of all customers who reside in California. Figure 1-1 Sample CREATE SCHEMA block 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 table and view names do not already exist for the specified owners.) The user has permission to create the 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 sysindices The following two new rows of information are added to the systables system catalog table after the CREATE SCHEMA block shown in Figure 1-1 is run. tabname customer owner maryl partnum 16778361 tabid 101 rowsize 134 ncols 10 nindexes 2 nrows 0 created 04/26/1994 version 1 tabtype T locklevel P npused 0 fextsize 16 nextsize 16 flags 0 site dbname tabname california owner maryl partnum 0 tabid 102 rowsize 134 ncols 4 nindexes 0 nrows 0 created 04/26/1994 version 0 tabtype V locklevel B npused 0 fextsize 0 nextsize 0 flags 0 site dbname 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 colmin colmax 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 system catalog 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 contain no entries. These two columns contain values when a column is the first key in a composite index or is the only key in the index, has no null or duplicate values, and the UPDATE STATISTICS statement has been run. The rows shown in the following example are added to the sysviews system catalog table. These rows correspond to the CREATE VIEW portion of the CREATE SCHEMA block. tabid seq viewtext 102 0 create view 'maryl'.california (customer_num, fname, lname, company 102 1 ,address1,address2,city,state,zipcode,phone) as select x0.custom 102 2 er_num, x0.fname, x0.lname, x0.company, x0.address1, x0.address2 102 3 ,x0.city, x0.state, x0.zipcode, x0.phone from 'maryl'.customer 102 4 x0 where (x0.state = 'CA'); The sysviews system catalog table contains the CREATE VIEW statement that creates the view. Each line of the CREATE VIEW statement in the current schema is stored in this table. In the viewtext column, the x0 that precedes the column names in the statement (for example, x0.fname) operates as an alias name 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. grantor grantee tabid tabauth maryl public 101 su-idx-- maryl cathl 101 SU-IDXAR maryl nhowe 101 --*----- maryl 102 SU-ID--- The tabauth column of this table specifies the table-level privileges granted to users on the customer and california tables. This column uses an 8-byte pattern-s (select), u (update), * (column-level privilege), i (insert), d (delete), x (index), a (alter), r (references)-to identify the type of privilege. In this example, the user nhowe has column-level privileges on the customer table. If the tabauth privilege code is uppercase (for example, S for select), the user who is granted this privilege can also grant it to others. If the tabauth privilege code is lowercase (for example, s for select), the user who has this privilege 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. 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-s (select), u (update), 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 sysindices system catalog table. 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 In this table, the idxtype column identifies whether the created index is unique or a duplicate. For example, the index c_num_ix that is placed on the customer_num column of the customer table is unique. Accessing the System Catalog Normal user access to the system catalog is read only. Users with the Connect or Resource privileges cannot alter the system catalog. They can, however, access data in the system catalog tables on a read-only basis using standard SELECT statements. For example, the following SELECT statement displays all the table names and corresponding table ID numbers of user-created tables in the database: SELECT tabname, tabid FROM systables WHERE tabid > 99 Warning: Although user informix and DBAs can modify most system catalog tables (only user informix can modify systables), Informix strongly recommends that you do not update, delete, or insert any rows in them. Modifying the system catalog tables can destroy the integrity of the database. Informix supports using the ALTER TABLE statement to modify the size of the next extent of system catalog tables. However, in certain cases it is valid to add entries to the system catalog tables, for instance, in the case of the syserrors system catalog table and the systracemsgs system catalog table, where a developer of DataBlade modules can add message entries that appear in these catalog tables. Updating System Catalog Data The fact that the optimizer in Informix database servers determines the most efficient strategy for executing SQL queries allows you to query the database without having to fully consider which tables to search first in a join or which indexes to use. The optimizer uses information from the system catalog table to determine the best query strategy. By using the UPDATE STATISTICS statement to update the system catalog table, you can ensure that the information provided to the optimizer is current. When you delete or modify a table, the database server does not automatically update the related statistical data in the system catalog table. For example, if you delete rows in a table using the DELETE statement, the nrows column in the systables system catalog table, which holds the number of rows for that table, is not updated. The UPDATE STATISTICS statement causes the database server to recalculate data in the systables, sysdistrib, syscolumns, and sysindices system catalog tables. After you run UPDATE STATISTICS, the systables system catalog table holds the correct value in the nrows column. If you use the medium or high mode with the UPDATE STATISTICS statement, the sysdistrib system catalog table holds the updated data-distribution data after you run UPDATE STATISTICS. Whenever you modify a table extensively, use the UPDATE STATISTICS statement to update data in the system catalog tables. For more information on the UPDATE STATISTICS statement, see Chapter 1 of the Informix Guide to SQL: Syntax.