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 the 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 can modify most system catalog tables, you should not update, delete, or insert any rows in them. Modifying the content of system catalog tables can affect the integrity of the database. However, you can safely use the ALTER TABLE statement to modify the size of the next extent of system catalog tables. Changing the next extent size does not affect extents that already exist.

For certain catalog tables of HCL OneDB™, 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 are in these system catalog tables.