RevMedia

Indexing on Symbolics is one of the most powerful features of AREV indexing - creating a symbolic which concatenates three fields together and BTREEing this can dramatically improve the performance of three level sorts when the required three levels are known in advance.

In an effort to implement databases in a relational manner it is customary to store foreign keys in data records pointing to data records in an external table where repeating data can be kept. A classic example of this would be an employee record containing a departmental ID. Rather than store all details about the department in the employee record, the department id is stored and all additional information about the department is XLATEd as required.

This works in a wholly satisfactory manner until fast reporting is required on a derived field (for example, sorting the Employees by Department Name). If a Btree is established on the Department Name symbolic in the Employee file, the report will run correctly until a department name is changed in the Department file. When the department name is changed, the Employee record Department name symbolic is not updated (the only write is that of the department record so the employee department symbolic is not reevaluated), and the Employee index becomes out of sync. Thus if there were three employees in departments as follows, and the list was sorted by a btreed dept. name

          BROWN A   100  BOUGHT LEDGER
          SMITH J   200  PERSONNEL
          JONES A   300  STAFF ACCOUNTING

and the STAFF ACCOUNTING department was changed to PAYROLL the employee list would now display in an incorrectly sorted order.

          BROWN A   100  BOUGHT LEDGER
          SMITH J   200  PERSONNEL
          JONES A   300  PAYROLL

What is required is a method whereby whenever a department name is changed, all symbolics referring to that field in other files are reevaluated and the corresponding indexes updated. To do this several conditions, must be met

          System must know which records reference the changed record

          System must know of existence of indexes on external records

          System must detect change and reevaluate external symbolics

An undocumented feature permits the system to keep track of these details for the user, subject to the usual restrictions on relational indexes. (Sticking to the example given above, one department could not have more than 64K of employees keys related to it).

The steps involved in setting up this feature are straightforward. Using the example given above

          Add BTREE to Department Name symbolic in Employee file and build
          the index

          As a relational index is to be set up between the Employee file
          and the Departments file, add a multivalued field definition (EG
          EMP.KEYS) to the Department file to store the relationally indexed
          keys from the Employee file.

          Define a relational index from the Department Number in the
          Employees files to the EMP.KEYS field in the Department file.
          (This is most easily accomplished from Shift-F1 in the DICT window
          rather than the indexing menu).

          At TCL, edit the symbolic which XLATES the description (in this
          case the Department Name Symbolic in the Employees file).  Ensure
          that no extra lines are inserted or deleted by turning Ins Line
          off.  Move the cursor to line 21 (the "depends on" field) and
          define this item as related to the department file by inserting
          information in the format FILE*FIELD, in this case
          DEPARTMENTS*EMP.KEYS.  Save this symbolic.  (Try a LISTINDEX on
          Employees to see "Depens").

The link is now established and the symbolic on the btree will now be automatically updated.

Note that if more than one independent link is required, Field 21 can be multivalued.

Caveats

This method can occasionally "hang" in a networked environment. To consider the scenario - when you write a record to disk and an index update is required, SI.MFS locks record 0 in the !INDEXING file, then evaluates the necessary index updates and writes the transactions to !INDEXING. After the transactions are written, the lock on !INDEXING is released. Whilst one station has the lock, other stations cannot save transactions but must wait for the transaction lock. This is evidenced by the "Waiting for Index Transaction Lock" message.

If you change a master record which has, for example, 2,000 associated records, the system must evaluate the results of the change for all 2,000 associated records. The !INDEXING lock will be kept for some time, and other stations trying to save their records will be delayed. Normally the message will disappear if left, and is a small price to pay for the convenience.

This problem seems to be worse in releases prior to 2.0.