@database
@node main Chapter16
 "SBase Personal - 16. REORGANIZING THE DATABASE"

@{" 16 REORGANIZING THE DATABASE " link 0}
 @{" Restructuring a Database File " link 1}
 @{" Defining the Query Fields line " link 2}
 @{" Reindexing " link 3}
 @{" Multi-file Operations " link 4}
 @{" Reorganizing a Database " link 5}
 @{" Recovering Data by Reorganization " link 6}
 @{" Creating an Empty Duplicate File " link 7}
 @{" Splitting a Database File " link 8}
 @{" Merging Database Files " link 9}

@endnode
@node 0
16 REORGANIZING THE DATABASE


A database can be reorganized in various ways for different reasons. This
chapter covers:

  -  Restructuring a database file. From time to time, you will want to
     change more than the characteristics of individual fields or the
     layout of a Page View. With experience, you will see that the fields
     in your database need to appear in a different order, perhaps with
     extra fields added and others deleted altogether.
  -  Reorganizing a database. If you do much editing of a file, adding and
     deleting records, you will also need to reorganize it from time to
     time in order to obtain maximum efficiency. Database reorganization
     can also enable you to recover as much data as possible from a damaged
     database.
  -  Splitting a database file. SBase 4 provides facilities for using
     existing data and definition files as the basis of new database files.
  -  Merging database files. SBase 4 provides facilities for merging two
     databases together.

@endnode
@node 1
Restructuring a Database File

You restructure a database by reading all the records you need from one or
more existing database files and creating with them a new database file.
Use the Process Query Definition requester to specify the fields and files
to be read, the order and formats of the fields in the new records, and
the name of the new database file to be created.

Existing field definitions become the basis of the new SBD file. The new
file is automatically indexed on the first indexed field encountered in
the Query Fields line, unless no indexed fields are being output, in which
case the index is built on the first field.

1.      Select Process Query Edit if you want to build up the required
        Query Definition from scratch, or Process Query Open if you 
        already have the required Query Definition or one close to it.
2.      Use the Query Fields requester to define the records for the new
        database. See Chapter 14, Defining and Using Queries, and Chapter 
        17, Multi-file Applications. You may want to define a Filter to 
        include or exclude some records. - Do not define a Query Report 
        line.
3.      Be sure to click on the File button, to specify that the
        destination for the output of the query is a file.
4.      Click on OK.
5.      Supply the name for the new database file and click on OK.

@endnode
@node 2
Defining the Query Fields line

Careful use of the facilities provided can greatly enhance the usefulness
of the feature. Here we list some points which might be overlooked.

Note:
Please review the following items carefully
  -  Fields can appear in any order. Often, the need to restructure arises
     because you have added a field to the file definition, which means it
     appears at the end of the list of fields. A common use of this feature
     is simply to rearrange the file definition.
  -  Certain formatting keywords and parameters are ignored by Query when
     outputting to a File. These include:

        ON File
        EJECT
        NEWLINE
        @ Position


  -  Derived columns. The results of expressions in the Query Fields line
     will be output as field data of the appropriate type. The name defined
     with the AS Heading feature will be the field name, so ensure that it
     has no more than 15 characters, that it obeys the rules for field
     names, and that you do not use the same name more than once. See
     Chapter 3 Defining Files.
  -  Non-field data. If you include literal text or numeric values in the
     Query Fields line and give them Heading names, they will be defined
     as new fields with data in them.
  -  A Calculated field will lose its formula unless all the component
     fields for the formula have preceded it in the Query Fields line. You
     may force a calculated field to lose its formula by enclosing it in
     parentheses.
  -  Page View. SBase 4 does not recreate each field's location 
     co-ordinates. You must reposition the fields and store the new
     co-ordinates with the Project Save command.

@endnode
@node 3
Reindexing

Restructuring a database file creates only one index, as explained above.
This considerably speeds up the output process, but it does mean that you
must rebuild all other indexes with the Project New Index command.

@endnode
@node 4
Multi-file Operations

Query output from more than one file can be used to create a new database
file. A relational Filter line must be defined. See Chapter 17 Multi-File
Applications.

This command should not be used to merge two files. See Merging Database
Files at the end of this chapter.

@endnode
@node 5
Reorganizing a Database

The need to reorganize arises periodically as a result of the way SBase 4
maintains its internal file structure. Reorganization compresses the data
blocks, eliminating all deleted items.

When you delete records from a file using the Process Remove option, SBase
4 does not actually remove them from the disk. Instead it simply marks
them as deleted records and thereafter re-uses them in file operations.
(You can check this by noting the file size with the Status file option,
before and after selecting Process Remove.)

SBase 4 will store new records in the space which was occupied by the
deleted records. But as far as other programs are concerned, the records
are still there and the space is unavailable. If you have removed a large
number of records - say, over a long period of time - you may be left with
a significant amount of wasted disk space. Removing a large number of
records from one file may also mean that SBase takes longer to read
records from the file.

Reorganize works with the current file and builds a new file using the
same name, the same file definition, and the same data, but without the
deleted records. It allows you to reclaim unused disk space.

When you need to reorganize a file, follow this procedure:

1.      Open the file to be reorganized.
2.      Select Process Reorganize.
3.      Enter a name for the new file.
   -  A pathname is allowed.
   -  You can enter the same name as the original if you wish, but 
      this will cause it to be overwritten.
4.      Click OK.

SBase 4 builds up a new file from the source file, then recreates all the
indexes. Fields marked DEL are removed from the file definition.

After the reorganization, the new file is opened and made the current
file.

  -  This option should only be used periodically and not on a regular
     basis. Unless you have deleted a large number of records, there is
     only a small advantage to be gained by reorganizing a file. Before
     reorganizing a file, you should always make a backup copy of it.
  -  Check that there is enough space on the destination disk to
     accommodate the reorganized file. As a rule of thumb, this should be
     at least as much as the space occupied by the current file.
  -  You may want to reorganize files which make use of the function to
     generate unique serial numbers. In this case, you should be aware that
     the numbers in the new file continue from the highest number in the
     old file.
  -  If you reorganize a file using its original name, SBase 4 first makes
     a backup copy of it under the filename DBXREORG. The SBF, SBD, and all
     index files bear this root name during the process. DBXREORG is
     deleted after the reorganization is completed. This means you can
     recover your data if for any reason the operation is interrupted (for
     example, by a power cut): simply rename as your original file all the
     files with the name DBXREORG.
  -  File names in quotation marks that occur within calculation or
     constant formulas are not changed when you restructure a file and may
     therefore need to be updated. For example, if the original file
     contains the formula SER("Clients"), you will have to replace 
     'Clients' with the name of the new file.

@endnode
@node 6
Recovering Data by Reorganization

Database work is very disk intensive, and thus more susceptible to disk
errors than spreadsheet or word processing work. If SBase 4 starts to
display error messages which indicate the presence of damaged records in
the file, you should use Reorganize to reconstruct it immediately.

SBase 4 reads the database without using an index, which enables it to
regenerate indexes from the actual data. If any data records are damaged,
they are omitted from the new file. If this occurs, SBase 4 shows the
following information message after completing the reorganization.

New file has different record count

There may also be circumstances in which Reorganize can be used to recover
data which could not be accessed before. In this case, Reorganize rewrites
the file header so that the records are included in the new file. Then it
issues another message (as well as the one above):

Source file size incorrect in header

No user action is required following these messages.

@endnode
@node 7
Creating an Empty Duplicate File

Applications often require the creation of empty files with definitions
based on the old one. You can achieve this using the Empty Copy option in
the Project New menu. See Chapter 3 Defining New Files for more
information.

@endnode
@node 8
Splitting a Database File

You may need to generate two separate files of the same structure from a
single database file. This is achieved with the Split command on the
Process menu.

1.      Open the file that you wish to split.
2.      Select the Process Split command.
3.      Define a Filter Command Line with values that will select the set
        of records you wish to go into the TRUE destination file - where 
        all records meet the filter criteria. - The records that do not 
        match will go into the FALSE destination file.
4.      Click OK.
5.      Enter filenames for the SBase Personal 4 TRUE and FALSE files that
        are to receive the extracted data and click OK.

@endnode
@node 9
Merging Database Files

Applications often call for two database files to be merged together.
Because SBase 4's file structure is more sophisticated than those of
traditional `flat-file' databases, it is impossible simply to append one
file to another and reindex the resulting file. SBase 4 requires that new
data be inserted into an existing file and index structure. The benefit of
this approach is that all reindexing is done automatically.

For any pair of files to be merged:

1.      Decide which file is to act as the `host' file. If there are no
        other factors, make the larger file the host file.
2.      Open this as the current file.
3.      Import the other file into the host file, using the SBase 4 Merge
        option. -  If a selected fields list is active in the source file, 
        SBase 4 will use it. This allows you to select fields in any order 
        for merging into a host file.

See Chapter 21 Import for more information.

@endnode