Differences from Other Databases
There are several key differences between the sysmaster database and other databases you might create. Reminder that this is a database that points to the server's shared memory structures and not to tables that are stored on disk. Some of the differences are:
You cannot update the sysmaster database. Its purpose is to allow you to read information about the server. Trying to update its tables should generate an error message but may corrupt the server.
You cannot run dbschema on these table to get their structure. This will generate an error message. (Joe Lumbley's note: But you can massage the sysmaster.sql script to simply create the tables and views in another database, removing the statements to drop the tables and recreate them as pseudotables. This will allow you do a few neat things with the SMI database. I'll explain at the end of Lester's paper.)
You cannot drop the sysmaster database or any tables within it. Again, this should generate an error message.
The data is dynamic and may change while you are retrieving it. The sysmaster database has an effective isolation level of dirty read even though it looks like a database with unbuffered logging. This prevents your queries from locking users and slowing down their processing.
However, because the sysmaster database uses unbuffered logging, its temp tables are logged.
You can create triggers and stored procedures on the sysmaster database, but the triggers will never be executed. Again, this is because this is not a real database but pointers to shared memory.
The sysmaster database reads the same shared memory structures read by the command line utility "onstat." The statistical data is reset to zero when OnLine is shut down and restarted. It is also reset to zero when the "onstat -z" command to reset statistics is used. Individual user statistical data is lost when a user disconnects from the server.
Now, let's examine some of the more interesting tables in the sysmaster database and what else can be done with them.