Report: Schema Area Cleanliness

Best practice is for the Schema Area
to hold only the Progress metaschema and nothing else; anything user-
defined that lives there is a left-over from a default build, an
incomplete dump-and-load, or a missed area assignment in a delta .df. The report is produced by util/chkschema.p.

One file is written per database, named <shortname>.chkschema.rpt (or
<ldbname(1)>.chkschema.rpt when pt_shortname is unset) and placed in
$RPTDIR (or the current directory if RPTDIR is unset).

A clean database produces a one-line “The schema area is clean.” report
and nothing else.

Invocation

mbpro <dbname> -p util/chkschema.p

Standalone — chkschema.p is not wired into the dba_report event
chain, so it has to be run explicitly when wanted.

What it checks

The program walks _storageobject and looks for any row where:

• _DB-Recid = recid( _db ) — belongs to the connected database
• _area-number = <Schema Area's _area-number> — lives in the schema area
• _object-num > 7 — skips the first eight slots, which are reserved for
  Progress’s own metaschema objects (anything ≤ 7 is expected to be there)

Each surviving row is then categorized by _object-type and joined to
the relevant schema table for a human-readable name:

For indexes and LOBs, the parent table’s actual area is also looked up
(via a second _storageobject/_area find), so the report shows where
the parent table lives — useful when an index ends up orphaned in the
schema area while its table is correctly placed elsewhere.

Names beginning with _ are filtered out as a belt-and-suspenders check
on top of the _object-num > 7 cutoff, so genuine metaschema objects
are never flagged even if Progress reuses a low slot.

Layout

The first line is a timestamp + database name:

<today> <hh:mm:ss>  Schema Check for DB: <pdbname(1)>

Then a single down-frame with these columns:

  Obj#  Type Object Name           Parent Object         Parent Area Name
------ ----- -------------------- -------------------- --------------------

Columns:

• Obj# — _storageobject._object-number, the object’s storage-object
  number.
• Type — _storageobject._object-type: 1 = table, 2 = index, 3
  = LOB. (Numeric, not decoded — the row’s column layout tells you which
  is which: tables have only an Object Name; indexes and LOBs also fill
  Parent Object and Parent Area Name.)
• Object Name —
  • For type 1: _file._file-name (the table that’s stranded in the
    schema area).
  • For type 2: _index._index-name (the orphaned index).
  • For type 3: _field._field-name (the LOB column whose storage object
    is in the schema area).
• Parent Object — only filled for indexes and LOBs: the
  _file._file-name that owns the object.
• Parent Area Name — only filled for indexes and LOBs: the area the
  parent table lives in (looked up via _storageobject →_area). When
  this is not “Schema Area” you have an index or LOB that’s split off
  from its table — usually a dump-and-load that missed the index/LOB
  assignment in the .st file.

If no qualifying objects are found, the body is replaced by:

The schema area is clean.

…and that’s the entire useful payload of the report.

Side effects

• None beyond writing the report file. Unlike most other ProTop reports,
  chkschema.p does not call lib/uploader.p or lib/fileviewer.p
  — it just writes and returns.

Reading the report

• Empty / “clean” report → the schema area holds only the
  metaschema. Best practice met; nothing to do.
• Tables flagged (Type = 1 with only Object Name filled) → those
  tables were never moved out of the schema area. Plan a dump-and-load
  with a corrected .st to relocate them into proper Type II areas.
• Indexes flagged (Type = 2) and Parent Area Name ≠ “Schema
  Area” → the index is orphaned: its table was moved but the index
  assignment was missed. proutil ... -C idxmove (or a fresh
  dump-and-load with the right .st) puts it back where it belongs.
• LOBs flagged (Type = 3) → a LOB column whose storage object lives
  in the schema area, often because a .df delta added a LOB without an
  area clause. Same fix path as orphan indexes — relocate to a proper
  area.
• A growing chkschema.rpt between runs is a strong sign that schema
  changes are being applied without area assignments; review the
  team’s .df apply process.