Reducing the database contents

The Trimble 4D Control Server technical database has some in-built procedures, which all make sure that the database entries do not sum up infinitely. These procedures all use time thresholds, which are stored in the TableLifeTime table of the database. When a Trimble 4D Control Server module creates a new table to store its data there, it automatically creates an entry for this table with default life time values in the TableLifeTime table. Thereafter, you can edit these values at any time.

The database controlling procedures run in the following order:

1. Deletion of the oldest data after predefined periods.

   Twice a day, data older than a certain age are deleted automatically.

   The maximum age of data kept in the database depends on the table the data is written to. The maximum data age for each table is specified in its LifeTime field in the TableLifeTime table. The time units are hours.

2. The second method applied to reduce the database size is to compute mean values for specified periods for data older than a specified age.

   Some of the Trimble 4D Control Server modules store measurement or processing data, such as positions, very frequently. Storing may happen as often as 20 times a second.

   These data can be reduced automatically. Everytime after it has deleted the data older than the defined maximum life time (that is, every fifteen minutes), the SQL Server runs stored procedures for these database tables. We can call these procedures AutoReduction procedures. When a Trimble 4D Control Server module creates a database table that stores huge amounts of data, it also does the following:

   • The module automatically stores a procedure for this table specifying how to reduce this table in the database. For more details on the stored procedure you can go to the Object Explorer of the SQL Server Management Studio and view the items in <database name>\Programmability\Stored Procedures.
   • The module creates an entry for the table in the TableLifeTime table and fills in the fields referring to the AutoReduction procedure with default values.

   This procedure reads the values for a specified period, computes the mean values for those data, stores them with a new time tag into the same database, and then deletes the entries for the original values.

   Using the TableLifeTime table you have several options to modify the automatic data reduction for each data table.

   UseAutoReduction field

   This TableLifeTime field enables the automatic data reduction for each table, if it is set to 1. To disable the feature for this table, set it to 0. For tables, for which no AutoReduction procedure exists, is is not possible to change the default value.

   ReductionRegionSizeHours1, ReductionRegionSizeHours2, ReductionRegionSizeHours3, ReductionRegionSizeHours4 fields

   Four fields define the regions in the database table in the unit of hours, for which the same data reduction method applies. The following rules apply for these values:

   • If UseAutoReduction is set to 1, at least ReductionRegionSizeHours1 must be defined.
   • You can define one, two, three, or a maximum of four regions.
   • ReductionRegionSizeHours1 contains the newest data, ReductionRegionSizeHours2 the following, and so on.
   • If the size of the region is 0, it always includes the oldest entries.
   • A region with size 0 is always the last valid region. If, for example, ReductionRegionSizeHours3 is set to 0, then three different regions exist for this table. The value in ReductionRegionSizeHours4 is then of no consequence.
   • At least one region with size 0 must exist.
   • The summed up sizes of all valid regions must not exceed the value given in the matching LifeTime field for this table.
   • The value -1 indicates an undefined region. It always follows a region with value 0 or value -1.

   ReductionDataSizeSeconds1, ReductionDataSizeSeconds2, ReductionDataSizeSeconds3, ReductionDataSizeSeconds4,

   These values define the period, for which one mean value is computed. Typical periods are 60 seconds or 3600 seconds. The following rules apply:

   • A value of -1 does not reduce the values of that region.
   • A value of 0 is invalid.
   • The value must not be larger than the value of its matching ReductionRegionSizeHours times 3600.
     For example, if the ReductionRegionSizeHours2 value is 2, the maximum reasonable ReductionDataSizeSeconds2 value is 3600, since 7200 has the same meaning as -1.
   • The matching ReductionRegionSizeHours value times 3600 must be a multiple of the ReductionDataSizeSeconds value.
     For example, if the ReductionRegionSizeHours1 value is 2, valid ReductionDataSizeSeconds1 values include 1, 2, 3, 4, 5, 6, 8, 10, 12, 15, ... .
   • Typically, data reduction for regions with higher number should be higher than those of the previous region. Thus, older data are more reduced than younger ones.

   Note - The values refer to seconds, not to measurement epochs. That means that with, for example, an update rate of 5 Hz and a value of 3600 seconds, the mean is computed from 18000 measurements.

3. Deletion of the oldest data, if a specified maximum of disk space is reached.

   If the disk space a database table uses has increased up to a specified threshold, Trimble 4D Control Server automatically deletes the oldest data of the table.

   The amount of data for each table to be deleted depends on the specification in the MaxSize_MB fields of the TableLifeTime table. Typical values are NULL (that means, unlimited table size) or 10, 50, 100, or 200 MB.

Example for a typical data reduction set in the TableLifeTime table - The column headers are abbreviated for getting a better overview. For more information on the fields, see the explanation in Step 2.

Tip - You can edit the values at any time. To return to the default values for a specific table, however, you have to add the module creating it to the system under a new configuration name. This will create a new entry into the TableLifeTime table with default values that you can copy to the previously modified values.