How the Sysmaster Database is Created

The sysmaster database keeps track of information about the database server just like the system tables keep track of information in each database.

This database is automatically created when you initialize OnLine. It includes tables for tracking two types of information: the System Monitoring Interface (SMI) tables, and the On-Archive catalog tables. This chapter will focus on the SMI tables. There is a warning in the documentation not to change any information in these tables as it may corrupt your database server. Also there is a warning that OnLine does not lock these tables, and that all selects from this database will use an isolation level of dirty read. This means that the data can change dynamically as you are retrieving it. This also means that selecting data from the sysmaster tables does not lock any of your users from processing their data. As mentioned above, the SMI tables are described as pseudo-tables which point directly to the shared memory structures in OnLine where the data is stored. That means they are not actually on disk. However, because many of the SMI tables are really views, selecting from them does create temporary tables and generate disk activity.

A script located in your directory $INFORMIXDIR/etc. named sysmaster.sql contains the SQL statements to create the sysmaster database. The process of creating it is interesting and is outlined as follows:

• First the script creates real tables with the structures of the pseudo tables.

• Then the table structures of the real tables are copied to temp tables.

• The real tables are then dropped.

• The column in systables that contains partnum is updated to indicate they point to pseudo tables in shared memory.

• The flags_text table is created which has the interpretations for all the text descriptions and flags used in the SMI tables.

• The stored procedures are created that are used to create the views, two of which may be interesting:

  • bitval() is a stored procedure for getting the Boolean flag values

  • 12date() is a stored procedure for converting UNIX time() long values to dates

• Finally the script creates the SMI views.

• After the sysmaster script is run the system will execute another script to create the on-archive tables and views in the sysmaster database.

Warning: The sysmaster database is created the first time you go into online mode after you first initialize your system. Do NOT start creating any other database until this process is complete or you may corrupt your sysmaster database. You will need 2000 KB of logical log space to create the sysmaster database. If there are problems creating the sysmaster database, shut your OnLine server down and restart it. This will re-create the sysmaster database. Monitor your online.logfile until you see the messages showing the successful completion of building the sysmaster database in the online.log (Figure 4).