Schema Area Gotchas

The schema area should not be used to store application data. And, if you are getting ready to dump & load, certain schema attributes require special attention.

Use the code!

Run the code included with ProTop RT (Real-Time) to see if you have any schema area "gotchas." If you have not yet installed ProTop, you can download it for free here. You can read about prerequisites and find installation instructions here. Once installed, you will find the code detailed below in [PROTOPDIR]/util.

For more background information, read this Progress article on Best Practices Involving the Schema Area.

First ...

Set up your Progress environment using proenv and start a Progress session against your database.

No index? Special fields?

To check for RECID and LOB fields and tables with no index or no unique index:

run [PROTOPDIR]/util/schemachk.p

It will display the gotchas indicated above. Like this:

Then press the space bar to see tables that have no [unique] index:

Next ...

• See the articles on RECID and LOB fields. They will require special attention in the context of a dump and load.
• Be sure to define unique indexes where there are none (do not store them in the schema area!).  For instance, a unique index can help enforce data integrity by preventing the addition of duplicate data. 
• If you have tables with no indexes, defining an index that correlates with how the data is most often used will speed up queries.

SchemaUsed <> clean? Default indexes?

To see a list of "default" indexes (aka "no index") and application schema that should be moved out of the schema area:

run [PROTOPDIR]/util/chkschema.p

This will produce a report in your current working directory named chkschema.*.rpt where the * is the name of your database. For example, chkschema.sports2000.rpt:

Then...

• Tables (1 in the Type column) that have no Parent Area Name and
• Indexes (Type 2) that have a Parent Area Name of Schema Area,
• Should be moved out of the schema area to the appropriate application area.  Consider using the proutil tablemove utility to accomplish this.
• You might also see "default" indexes in the schema.  This is the same as "no index" reported by schemachk.p above.  OpenEdge creates this index placeholder in the schema area as soon as the table is created.  When the first application index is defined for the table, this default index is removed.

Get schema alerts

ProTop alerts the Portal when it finds application data stored in the schema area. It is pretty common to have an actively developed database to which new tables and indexes are being added regularly.  Commercial users of ProTop can configure the portal to send emails that call attention to this fact before it gets out of hand.

A place for everything

And that place, for application data and indexes, is not the schema area.  Define and assign storage areas to your application tables and indexes, and everything [can be] in its place!