This article was brought to you by the guys from VDF-GUIdance.
For more DataFlex targeted articles see http://www.vdf-guidance.com


Novax database maintenance

by Allan Greis Eriksen

Summary

A general maintenance application for the native dataflex database.
Size: 47 KB Download
Date Created: 06/03/2018
Date Updated: 09/03/2018
Author: Allan Greis Eriksen
Company: NOVAX A/S


NOVAX Database maintenance - open source version

Currently in dataflex 18.2 but should be able to compile and run with later versions.
This is a database maintenance tool for the native DataFlex database that I have developed and used since the year 2000.

Note about the code.



This version is a stripped down open source version, that does not include backup and restore functions as they used a commercial zip activex component. And I have changed the text from danish to english along with some comments. Also a lot of the code has been around since VDF5 so a lot of code could be refactured to newer standards.

Many parts of the code has danish words for naming variables and so. If needed I could change the names but only if some request comes for that.

Button: Auto



This button runs 3 different processes.

First it runs a check on all tables to check the header of the table to see if a repair is needed. It uses the dataflex driver for this check. If the driver returns an error indicating that the header should be repaired the repair is executed.
Please note that this repair is not the same as the Repair button does.

Second it checks all tables if any needs to have its filesize increased. This is the same as the Resize button does.

Third it runs a complete reindex on all tables. Any error while reindexing is shown. A .BAD file is also created for the table. This can be used by the Clean up function.

Button: Repair.



This gives you a dialog where you can select one or more tables to repair. The repair examines the counters that is located in the beginning of the table (in the header section) and investigate the whole table to see if the the counteres are correct. If not they are corrected.

This can solve problems like status 4 - seek to unwritten error messages.

Button: Rebuild



This gives you a dialog where you can select one or more tables to rebuild. The rebuild process starts by exporting all records in an ascii file. Then it zerofiles the table forceing a new header (.dat file) to be created. Then it imports all records from the ascii file again.

Please note that the repair can handle text and binary fields also. But this should be used with caution. If the process is stopped during the import the data is lost in the ascii file. There is currently no method to restart an import if it has failed.

Remember to make a backup of the table before using this function.

Button: Clean up



This runs though all tables and look for any .BAD file created by an reindex process. If it finds one it examines the file.
If there are reported any dublicate records and/or bad data in records a dialog is shown with the number of dublicate record chains and number of invalid records. If you proceed with the cleanup (by clicking the Yes button) the dublicate records are cleaned up. After the cleanup the table is reindex again. Any error would result in another error message and another .BAD file. The same goes for the invalid records.

Please note that if there are more than 2 chains with dublicate records in the file the repair funktion should be processed before attempting a clean up. The many chains could be a symptom for misaligned counters in the header section of the table.

Button: Resize



This runs through all tables and check if the filesize of the table is getting to low to hold data. The filesize is used for the indexes. If there are 15% or less room left for data in th table the filesize is set to an additional 40% of the current data use.

The indexes uses a B-tree style but I have chosen a simple approach to determine if the filesize is to low. There are other methods to calculate a more precise value to set for filesize but this has proven to be a good enough solution.

Button: Reindex



This gives you a dialog where you can select one or more tables to reindex. If any error occures during the reindex it is shown and a .BAD file is created.

Button: Date check



This gives you a list of tables to select and to run a date check for. The date check examines all date fields to see if the date has a year that is less that 100. If so it is corrected.

Note that this has only value for those systems that still convert from a dos system to a window system where dates where stored with only the last to digits of the year.

Button: Log



This shows the log created. It is never erased and every new function is adding to the log file.


Note that this log file can be quite big if there are a log of tables to handle. Please take care to reduce the size of the log file once in a while or delete the log file when it is not needed.

General information.



All functions are fully logged in a log file. You can select the Log button to open the log file.

Reindexing is done with 64MB memory buffer and the status progress bar is only updated for every 5,000 records and not for every 100 record that is default for many other reindexing tools. This speeds up the reindexing very much!

Also reindexing is taking the current DATA folder into account. If you have multiple data folders in your DATA path in the workspace file, the reindex process makes sure that the index files are created and update in the same location where the table is located.

Command line option



You can add the following command line option for running an automatic Auto function of a workspace:

A "(full path to workspacefile.ws)"
A "(full path to studio workspacefile.sws)"

This will automatically start an Auto function for the workspace file. In the case of the studio workspace file, it takes the first WS file registeret in the SWS ini file.

This is the same as if you selected the workspace in the application and pushed the Auto button.

A complete log is written but no errors are shown. This means that you can run this at regular intervals when no other application is using the database i.e. during the weekend.

Exclusive access to tables



All functions requires exclusive access to the tables. That means that no other application can be running at the same time. You should take care that no other application is starting to use the tables when one of the function are in progress.

If a function cannot gain exclusive access to table it is reported as an error. In the case where the function presents a dialog to select tables from the tables are made inactive. You can determine if another application is using the tables by selecting the reindex button and see if the table in question can be selected.

Download



ntdbmnt.zip

Subversion



ntdbmnt repository

Projects server link



NOVAX Database maintenance - open source version