Flexlines Volume VIII Number 6 (a Data Access publication)

Rebuilding Your Database

by Michael Steffano & Lynn Wheeler

It has probably happened to you. Suddenly your program starts flashing strange errors
at the bottom of the screen, or records are no longer being found, or DataFlex can no
longer OPEN your database.
   
Your database has become corrupted! This article explores several procedures designed
to get your database and indices back in sync going from the easiest and most common
problems to the infrequent and more severe types.
   
While these errors can happen on any PC, they tend to be more common on networked
computers due to the greater number of factors affecting data integrity. On a standalone
PC you might have a user pressing the dreaded CTRL ALT DEL key combination while the
databases are open, whereas on a network you not only have that possibility, but also
transmission errors, network noise, other users, and server problems.
   
The following procedures have stood us in good stead, both from fixing the problem and in
the efficiency of our time useage.
   
First and foremost, the most likely to happen:
   
Your indices have become corrupted. This can lead to strange finds or record not founds 
occurring. DFINDEX should be your first choice in this case. If your are on a network you
must lock all other users from using your files while the reindex is occurring. There is also 
a product produced by Vinga Systems in Sweden called VSDFR that performs reindexing
at a fraction of the time that DFINDEX takes. We've seen 75% speed improvements on 
some large databases! You may want to consider this if you have large database corruption 
on a regular basis (obviously, the best answer is to fix the problem). You may want to consider
changing the DFSetup system option so that writes are not buffered to disk. Nowadays, with
high speed hard disks and network buffering, you won't see the performance degradations
as in the past. This will help reduce the window of opportunity for errors to occur. On a network
make sure your files (.DAT, K??) are flagged read/write before reindexing.
   
Next most likely to happen (and not very often):
   
Your .DAT file has become corrupted somehow. This leads to several methods for fixing it, 
ranging from easy to the complex. Before you begin using the following methods take a moment
to check the number of records the database thinks it has in it. You can do this through DFFILE
and look at the Parameters screen, or through DFQUERY. If you have a feel for the number of 
records the database is supposed to contain then this number can be a valuable clue as to 
where the problem lies. If the number is wrong, such as being negative, then you probably
have a header corruption. More on this later.
   
First, do you have a backup? And, if you do, will much data entry time be required to bring it
back up to the current time stamp? If you are being paid by the hour this is definitely a consideration.
   
Second, write a program (or use DFQUERY) to read through the file by RECNUM and write out all 
the data to an ASCII file. Then, use DFFILE to erase the contents of the database, generate
a READ program and load the data back in to the original file. If your program or DFQUERY
can not read through the database by record number then most likely the file's header is
corrupted and you will need to go the next step.
   
Third: this method requires you to use some mechanism for reading the database in a block oriented
byte stream. You can use the READ_BLOCK command within DataFlex or one of the several data
conversion programs on the market. Tools & Techniques, Inc. produces a product called Data Junction
that is designed to convert files from one format to another (DataFlex is supported). Whatever
method you use, you will have to offset your initial read by the number of bytes in the header record.
In 2.3 that number is 1535 (5FF). While 3.0 has a different header format the byte offset is still the
same as 2.3. Data Junction makes all of this a snap. You will need a listing of your .DEF file so that
you can figure out the record and field offsets. If you are doing this programmatically (what a great 
word) then you will need to perform MIDs on your READ_BLOCK data to move the data into appropriate
variables for outputting to an ASCII file.  Once you have an ASCIIfile created you can go back to the
READ utility as outlined above. If this seems too bothersome and you are the adventurous type, then 
move on to the next procedure.
   
Fourth: this method is very dangerous and could lead to all kinds of unexpected errors if mistakes are 
made. However, as a method of last resort it has worked where all other attempts have failed. You 
will be doing a direct edit on the header record itself. Make sure you have made a backup of the file!
   
Use your favorite disk editor (Debug, Norton Disk-Edit, etc.) and list the first 6 bytes of the database.
Replace the first 6 bytes with 'FF FF FF'. This in effect tells DataFlex that the database contains the
maximum number of records possible. Now, save the file and go backto DFQUERY where you can once
again try and write out all the records by record number. DFQUERY will only write the actual number
of records in the database and not the number of records indicated in the header. Note, if you use a
value different than 'FF FF FF' you run the possibility of not reading all the records. If there are more
records in the file than are indicated by the header record count then DataFlex will not read
the remaining records. If you know the exact number of records and want to use that remember that 
they are stored in LSB first format (Least Significant Byte).  For example, if the header reads 9B 48 00,
then you should interpret it as 00 48 9B (18537). Realize that this number is actually 1+ the number of 
records in the database. 
   
An enhancement to the above procedure is to make a duplicate of the original file through DFFILE by 
reading the .DEF or by reentering the file description by hand (you should rename the damaged file
first to something else). Now, using a disk editor list the first 1535 bytes. Use this listing to compare 
to your damaged header and fix any discrepancies (except the record count).
   
If these methods don't work then you are basically out of luck and will need to create the database 
from scratch. Have fun.
   
Remember, before attempting any type of fix, you should always make a backup copy of the corrupted
database. And always perform regular backups of your data files (you knew that already, right?). 
   
Of course, if these problems happen on a frequent basis, then your time and the user's money are
better spent on solving the root of the problem. It could be user training, bad programming (no way!),
operating system or hardware related.
   
If any other DataFlex'rs out there have additional techniques I would love to hear about them.
   
Michael Steffano has been working with DataFlex since 1982.
   
VSDFR and other replacement DF products by
Vinga System AB
Gotabergsgatan 28
S-411 34 Goteborg
Sweden
ph +46-31-819850
fax +46-31-812139
   
Data Junction by
Tools & Technologies, Inc.
1620 West 12th Street, Austin, Texas 78703
(512) 482-0824