Stratum Database Maintenance Utilities

Published by

Stratum Under CoversSilvon provides a number of utilities to help our customers maintain their Stratum databases. Three utilities in particular are very valuable based on our work with customers who contact us about potential performance or data integrity problems.

Records with zero data

Running change sets in default mode creates reverse transactions which leave records with zero data in the database. Over time these zero data records can have a material effect on the size of your Stratum database and the performance of any processing being done on the database.

The DLTZROSLS command on IBM i and the GIZRECS command on Windows are used to delete zero records in the Stratum database. These commands provide the option to select the specific data source from which to remove the zero records.

The process of deleting zero records can be lengthy and can affect end user performance, so we generally recommend running these jobs on the weekends.

Data Summary Level (DSL) Counts

Accurate record counts for DSL’s defined in Stratum are extremely important for performance of daily data loads and Stratum.Planner users. Occasionally the record counts for a DSL or multiple DSL’s can become corrupt. This is most often caused by a Stratum.Server load that ended abnormally. If the DSL count for header records is incorrect it can cause the Stratum best-fit algorithm to choose the wrong DSL to satisfy a Planner inquiry. If the DSL count for the detail transaction records is incorrect, the Stratum load process can make incorrect memory calculations which can cause less than optimal load time performance. The log file generated during a Stratum load will contain a warning message(s) if any DSL record counts are inaccurate.

  • For the IBMi, the DADSLCNT command is used to update DSL header and detail record counts. The command provides the option(s) to select all DSL’s and years, a list of DSL’s and years or a single DSL and year combination. For some older versions of Stratum the command is called BLDDSLCNT. The BLDDSLCNT should only be used if your version does not also contain the DADSLCNT command.
  • For the Windows platform, the BLDSLCNT command is used to update DSL header and detail record counts . The command also provides the option(s) to select all DSL’s and years, a list of DSL’s and years or a single DSL and year combination.

The process of updating your DSL counts can be a lengthy job depending on the size of your Stratum database. If you don’t already have a feel for how long these jobs can run and you have multiple DSL’s with inaccurate counts, we generally recommend running the job for a single DSL over a weekend. This can give you a better idea for how long the process might take to correct all the DSL’s.  User’s running Stratum.Planner while a DSL count job is running may see a decrease in their query retrieval times as the best fit algorithm may not choose the optimal DSL for their request.

Corrupt Stratum transaction tables

Occasionally the Stratum warehouse tables themselves can become corrupt which can cause data integrity problems. These most often surface, but not exclusively, in the form of a user(s) questioning the data they are looking at or hierarchy ID errors which show up in the log file during the daily load process. The hierarchy ID errors are almost always caused by something we try and discourage and that is copying tables within or from/to Stratum databases.

The DADTACHK command on IBM i and the DATACHK command on Windows will perform a series of data checks on the Stratum warehouse tables that can help identify where the problem(s) exist. If you are experiencing the symptoms of corrupt transaction tables we suggest you run the DADTACHK command in report mode only. It is then best to provide a copy of the report to our support line so they can assist in determining the best course of action to try and resolve the problem(s).



Categorised in:

This post was written by Pat Hennel