Accessing the System Catalog

Normal user access to the system catalog is read-only. Users with Connect or Resource privileges cannot alter the catalog, but they can 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 tabid codes of user-created tables in the database:

SELECT tabname, tabid FROM systables WHERE tabid > 99

When you use DB–Access, only the tables that you created are displayed. To display the system catalog tables, enter the following statement:

SELECT tabname, tabid FROM systables WHERE tabid < 100

You can use the SUBSTR or SUBSTRING function to select only part of a source string. To display the list of tables in columns, enter the following statement:

SELECT SUBSTR(tabname, 1, 18), tabid FROM systables

Although user informix and DBAs can modify most system catalog tables (only user informix can modify systables), it is strongly recommended that you do not update, delete, or insert any rows in them. Modifying system catalog tables can destroy the integrity of the database. The ALTER TABLE statement cannot modify the size of the next extent of system catalog tables.

For certain catalog tables of Dynamic Server, however, 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, a DataBlade module developer can directly insert entries that appear in these system catalog tables.