System databases and system catalog tables
The first time the database server is brought online, the sysmaster, sysutils, sysuser, and sysadmin databases are built.
These are system or internal databases used by IDS.
These databases contain system catalog tables as do user-created databases.
System catalog tables track database objects, such as:
- Tables, views, sequences, synonyms, and sequence objects
- Columns, constraints, indexes, and fragments
- Procedures, functions, routines, and associated messages
- Authorized users and privileges
- User-defined routines
- Data types and casts
- Aggregate functions
- Access methods and operator classes
- Inheritance relationships
- External optimizer directives
One of the system catalog tables within a database is the systables table. This table lists all tables within the database. Each table listed in the systables table is uniquely identified by a tabid. System catalog tables each have a tabid that is less than 99.
To view all system catalog tables within a database, you can run the following SQL statement:
database stores_demo; select * from systables where tabid < 99;
The sysadmin database contains tables that store task properties. It allows a database administrator to schedule the running of administrative tasks.
The sysutils database contains tables used by the ON-Bar backup and restore utility.
The sysuser database is used for PAM (Pluggable Authentication Module) authentication in server-to-server communication.
If the sysmaster database cannot be created, the database server attempts to create it every time it is brought online.
The sysmaster database contains data dictionary information that points to shared memory structures. The tables in the sysmaster database are called SMI (system-monitoring interface) tables.
The system-monitoring interface (SMI) tables are special tables managed by the database server that contain dynamic information about the state of the database server.
Most of the SMI tables do not hold any data; instead, the data dictionary structures for that table point to structures in shared memory.
SELECT statement is executed on a
regular table, the server reads the data dictionary information for
the table to find the partition number and other information about the
table. Then it accesses the data from disk if it is not in the buffer
SELECT statement is executed on an
SMI table, the server still reads the data dictionary information for
the table listed in the
The SMI tables have a special partition number (the dbspace number
within the partition number is 0). When the server detects the special
partition number, it knows to read a specific set of data in shared
memory to satisfy the query.
SELECT statement is accessing
real-time data in shared memory, the data between one SMI table and
another might not be synchronized.
All users have permission to query the supported tables in the sysmaster database, except for the tables used for audit operations.
The following restrictions apply when using SMI tables:
- You cannot lock the non-permanent SMI tables or use isolation levels. Because these tables are just shared-memory structures, the traditional SQL locking mechanisms are ineffective (and not desired).
DELETEstatements are not allowed against non-permanent SMI tables.
- The dbschema and dbexport utilities cannot be used for the
sysmaster database. You will receive the following error:
Database has pseudo tables - can't build schema.
- The use of rowid in a
SELECTstatement is not relevant and it returns inconsistent results.
The sysmaster database consists of over 50 tables. IBM Informix supports and documents only a few of these tables and some of the views that use these tables. For your protection, use only the supported tables and views in your queries, as the unsupported tables could change between releases. The supported tables and views are:
- The sysdatabases table, which lists databases, owners, and database characteristics.
- The systabnames table, which contains the names of all tables in the server.
- The syscheckpoint and sysckptinfo tables, which are new in Version 11 and list checkpoint information.
- The syslogs view, which contains information about the logical logs. You can use syslogs to determine if the logs need to be backed up.
- The sysdbspaces view, which contains information about dbspaces.
- The syschunks view contains the chunks in the server. The nfree column shows the number of pages in the chunk that are free.
- The syslocks view, which lists all active locks.
- The sysvpprof view, which contains all the active virtual processors.
- The syssessions view, which lists information about each session.
- The syssesprof view, which contains more information about each session.
- The sysextents view, which lists extents allocated in the server.
- The syschkio view, which contains I/O statistics by chunk.
- The sysptprof view, which lists information about the tblspaces at any one point in time. Only tables currently being used are listed in this view. When the last user closes the table, the tblspace structure in shared memory is freed, and, subsequently, any profile statistics are lost.
- The sysprofile view, which lists certain events in the server, such as disk reads, disk writes, roll backs, checkpoints, and so on. Each row contains one profiled event and its value.
- The sysadtinfo table, which contains information about the auditing configuration for the server. You must be user informix to retrieve information from this table.
- The sysaudit table, which contains the hexadecimal representation of each defined audit mask. To list, modify, or add an audit mask, you must use the onaudit utility. You must be user informix to retrieve information from the sysaudit table.
- The sysconfig table, which describes the effective, original, and default values of the configuration parameters.
- The sysdri table, which provides information on the data-replication status of the database server.
- The sysseswts table, which provides information on the amount of time users wait for various database objects.
IDS uses logical logs to record data manipulation language (DML)
for logged databases, as well as data definition language (DDL)
statements and checkpoint activity for all databases.
If you do not enable database logging, the server cannot fully recover the database in the event of a failure, and you cannot use transactions.
You can use the ondblog utility to change the logging mode for one or more databases.
If you are changing the logging mode of a database, you must perform a level-0 backup before the change takes effect.
Use the following command to change the buffering status on the stores7 logged database:
ondblog unbuf stores7 ondblog buf stores7
To end logging, use the following command:
ondblog nolog stores7
To end logging for a list of databases in the file "mydbfile", use the following command:
ondblog nolog -f mydbfile
Use the following command to make a database ANSI-compliant:
ondblog ansi stores7
You can add logging to a database with ontape at the same time that you create a level-0 backup.
For example, to add buffered logging to a database called stores_demo with ontape, execute the following command:
ontape -s -B stores_demo
To add unbuffered logging to a database called stores_demo with ontape, execute the following command:
ontape -s -U stores_demo
In addition to turning on transaction logging, these commands create full-system storage-space backups. When ontape prompts you for a backup level, specify a level-0 backup.
Note: With ontape, you must perform a level-0 backup of all storage spaces.
To end logging for a database called stores_demo with ontape, execute the following command:
ontape -N stores_demo
To change the buffering mode from buffered to unbuffered logging on a database called stores_demo using ontape without creating a storage-space backup, execute the following command:
ontape -U stores_demo
To change the buffering mode from unbuffered to buffered logging on a database called stores_demo using ontape without creating a storage-space backup, execute the following command:
ontape -B stores_demo
To make a database called stores_demo, which already uses transaction logging (either unbuffered or buffered), into an ANSI-compliant database with ontape, execute the following command:
ontape -A stores_demo
To make a database called stores_demo, which does not already use transaction logging, into an ANSI-compliant database with ontape, execute the following command:
ontape -s -A stores_demo
In addition to making a database ANSI compliant, this command also creates a storage-space backup at the same time. Specify a level-0 backup when you are prompted for a level.
Note: After you change the logging mode to ANSI-compliant, you cannot easily change it again. To change the logging mode of ANSI-compliant databases, unload the data, re-create the database with the new logging mode, and reload the data.
To create a database and specify a logging mode, use the
Listing 9 provides the syntax:
Listing 9. Syntax to create a database and specify a logging mode
>>-CREATE DATABASE--database--+-------------+-------------------> '-IN--dbspace-' >--+-----------------------------+----------------------------->< '-WITH--+-+----------+--LOG-+-' | '-BUFFERED-' | '-LOG MODE ANSI-----'
The following example command creates a database called my_db in dbspace2 with unbuffered logging:
create database my_db in dbspace2 with log;
The command in Listing 10 verifies the logging status of the database and queries the sysdatabases table in the sysmaster database:
Listing 10. Verify the logging status by querying the sysdatabases table
database sysmaster; select * from sysdatabases where name='my_db'; name my_db partnum 1049131 owner informix created 07/20/2009 is_logging 1 is_buff_log 0 is_ansi 0 is_nls 0 flags -12287