Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 3: DBMS instances and storage objects

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

System catalog tables track database objects, such as:

• Tables, views, sequences, synonyms, and sequence objects
• Columns, constraints, indexes, and fragments
• Triggers
• 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, sysutils, and sysuser databases

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.

The sysmaster database

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.

When a 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 pool.

When a SELECT statement is executed on an SMI table, the server still reads the data dictionary information for the table listed in the SELECT statement. 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.

Because the 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).
• INSERT, UPDATE, and DELETE statements 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 SELECT statement is not relevant and it returns inconsistent results.

Sysmaster tables

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.

Back to top

Database logging mode

IDS uses logical logs to record data manipulation language (DML) entries (INSERT, UPDATE, DELETE) 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.

Using the ondblog utility to change the logging mode of a database

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

Using the ontape utility to change the logging mode of a database

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.

Back to top

Specify a logging mode when you create a database

To create a database and specify a logging mode, use the CREATE DATABASE statement.

Listing 9 provides the syntax:

>>-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:

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
                

4 of 7 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Identify and connect to Informix database servers and databases
• Create and configure database storage objects
• System databases and system catalog tables
• Conclusion
• Resources
• About the author

---

4 of 7 | Previous | Next

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers