The HTML Version of the Sysmaster Documentation

Earlier, I make a parenthetical comment in Lester's article about a method that allows the user to make a "fake" copy of the sysmaster database. You may have wondered why I did that. Since the dbschema program will not report on pseudotables, in order to get a schema generated for the sysmaster database, you have to massage the sysmaster.sql script that is used by Informix to generate the sysmaster database in the first place. Be sure and work on a copy.

If you haven't looked at this sysmaster.sql script, you might find it interesting anyway. It has documentation for most of the fields that are found in the sysmaster tables. Anyway, to create the "fakesmi.sql" script, change the "create database sysmaster with log" command in the first section of the script to something like "create database fakesmiwith log." Then go down to the statement that says "select tabid from systables". It's on line 1278 in the NT 7.30.UC1 sysmaster.sql script file. Beginning here is where they start doing something tricky that turns normal tables into pseudotables. Delete everything from here down until where it drops all of the temp tables it created and before it creates the flags_text table (Line 1448 on NT 7.30). Before you run the script, do a text search to make sure that the word "systables" is not used in any SQL statement. Wouldn't want to destroy your sysmaster database, would you? If you run the new script, you'll have a real database with real tables that looks just like sysmaster. I'll include the "doctored" script on the CD in case you feel a need for it.

So why would you want to butcher up a creative, well documented script to create a database that you don't need and that might actually damage a database that you really need?

A year or so ago a company called Technology Investments came out with a group of ingenious PERL scripts that did all sorts of trick things with Informix databases. The tools were called "ztools," and many of the denizens of comp.databases.informix served as beta testers for them. The ztools product line was subsequently either sold or licensed to BMC Software, the makers of the Patrol line of monitoring tools, and BMC now sells the ztools under another name. Many of the ztools based their work on a program called "zschema" that ran a dbschema against an Informix database and massaged it in various ways.

One of the ztools was a program called "zweb," which generated marvelous data dictionaries from an existing Informix database and converted them into HTML which could be viewed and managed from any frames-capable Web browser. Zweb needed to be able to actually see the database and run "zschema," which ran dbschema against the database. Thus, since dbschema could not run a schema against the sysmaster database, none of the ztools would work with sysmaster.

The output from zweb allows you to search the schema of the sysmaster database in many ways, including cross-reference searches like "which tables have a partnum column." I've included the Web based data dictionary generated by zweb in the CD in the HTML subdirectory. Enjoy.

I have to include a "Kids, don't try this at home" disclaimer on this creation of a fake SMI database. Due to the risk of destroying your sysmaster database, I recommend that you do not try this on a production machine and that you try it only if you are very sure of what you are doing. Besides, I've included the HTML output and there is really no reason for you to try this yourself.