SalesLogix Technical Support Knowledge Base

Database Maintenance

InterBase

There are a number of things that can be done in the InterBase Server Manager to enhance the performance of your database and to reduce the possibility of corruption. To launch the InterBase Server Manager, select Programs from the Windows Start menu and then InterBase 4.2 > InterBase Server Manager.

Backup and Restore

The most important thing to do when maintaining an InterBase database is to perform a backup and then restore that database at least once a week. This will sweep the database to check for any incomplete logs and delete them. It will also eliminate the temporary work-areas that InterBase creates, thereby reducing the size of your database and taking a tremendous load off the database server.
You also have the option of running a separate database sweep manually. If the database is not being backed up weekly, then at least a sweep should be performed. The sweep interval should be set to zero when performing this manual database sweep.
When restoring your database after a backup, you are given the option to set the database page file size. Setting this to 2048 will decrease the size of the database and therefore, a slight speed increase is possible.

Server Configuration

The Server Configuration dialog can be opened from the InterBase Server Manager under the Tasks menu. There are four settings in the various tabs of this dialog that can help to optimize the performance of your InterBase database.
I. Database Cache:
This is the number of memory pages that are reserved for each database. You will need to know how many pages are in your database in order to modify this setting.
II. Client Map Size:
This should be set in accordance with the amount of data retrieval that is done in your database. The recommended setting is 4 KB.
III. Process Working Set: 
This sets the range of memory pages in physical RAM. A minimum setting of 50 MB is recommended. The maximum setting is dependent on the database server's configuration.
IV. Process Priority Class: 
This should be set to "High" on Windows NT machines.

Balancing Indexes

InterBase uses a modified binary tree index structure that is not self-maintaining. Therefore, the structure of an index may become unbalanced over time. This is specially true for tables that have a high rate of insert or delete activity. When indexes become unbalanced in this manner, they must be rebuilt. The following SQL commands can be run from utilities such as DB Explorer and iSQL and are used for this purpose:
alter index <index name> inactive
alter index <index name> active
The index name can be obtained by extracting the metadata for the database using the InterBase Wisql32 tool. It is also possible to completely drop and then re-add an index to ensure that it is completely clean. Use the syntax below for this purpose:
drop index <index name>
create index <index name>
Again, the index name can be obtained from the metadata for the database. If a primary index in being re-added, the syntax is as follows:
create unique index <index name>

A more in depth discussion of all the items mentioned here may be found on Borland's web site at http://www.interbase.com/TecSvcs/TecDocs/manage.html and also http://www.interbase.com/TecSvcs/TecDocs/performance.html

Repairing Sever InterBase Database Corruption

Sever database corruption is often diagnosed by errors (ex: page type) encountered when attempting to perform a backup of the InterBase database via Borland's InterBase Server Manager. Some kinds of this database corruption may be repaired using the gfix and gbak InterBase utilities. However, in some rare cases an InterBase database file can be corrupted beyond the ability of these tools to repair the damage. The most frequent cause of corruption is abrupt or catastrophic power loss on the Database Server. Shutting off power when InterBase is in the process of writing to the database file can result in erroneous or incomplete data being written, causing the database file to be corrupt.

When attempting to repair database corruption always work on a copy of the database, not the Production database. You should also make sure that users are not continuing to add information to the database as you will be replacing the Production database with the file that was repaired.

First make a copy of the InterBase database file by browsing to the gdb through Windows Explorer. Then select the gdb file (ex: SlxBlank) and select Copy from the Edit menu. Create a new folder (ex: Slx_Bkup) directly off the main drive (ex: C) and paste this copy of the database into this new folder. Make sure that the name of the folder is eight characters or less since gfix is run from the DOS prompt and requires a reference to this folder name. You should also rename the gdb file if it's name contains more than eight characters.

The following information will be used throughout this discussion as examples to more easily explain the syntax of the command line parameters used when running the gfix and gbak utilities:

• C:\Program Files\Borland\IntrBase\Bin = the Bin directory of the path that InterBase was installed in.
• SlxBlank.gdb = the name of the InterBase database file.
• Slx_Bkup = the folder containing the database file that will be worked on.
• C:\Slx_Bkup\SlxBlank.gdb = the exact path to the database file being worked on.
• sysdba = the InterBase user of the database (this is always constant).
• masterkey = the password for the sysdba user.

The gfix InterBase utility

Database corruption that can be repaired is most often cleared up with gfix. gfix is also used to check for database corruption and then determine the nature and level of corruption.

Check the Database for Corruption

Once you have a copy of the database and all the folders set up, launch the MS DOS window from the Windows Start menu. Change directories so you are in the C:\Program Files\Borland\IntrBase\Bin directory. Then enter the following command line to check the database for corruption.

gfix -v -f -user "sysdba" -pas "masterkey" C:\Slx_Bkup\SlxBlank.gdb

This should return a report on the database corruption and the gfix utility can be used to attempt a clean up. If it does not, but InterBase Server Manager was unable to successfully backup the database, continue through the various procedures outlined in the following sections and check if the gfix utility is able to clean up the corruption.

Mend the Database Corruption

Once you have executed the gfix command line above and have a report on the corruption, enter the following command line to mend the database corruption.

gfix -m -i -user "sysdba" -pas "masterkey" C:\Slx_Bkup\SlxBlank.gdb

This will run for a some time depending on the size of the InterBase database file. Once it is complete, check to see if corruption was mended by re-executing the gfix command line from the section above.

gfix -v -f -user "sysdba" -pas "masterkey" C:\Slx_Bkup\SlxBlank.gdb

If this still reports database corruption, the gbak InterBase utility must be used to attempt to clean up the database.

The gbak InterBase utility

The gbak utility is used to perform a backup and restore of the database from the command line so that different switches may be used. This procedure will create a new database with no old record versions, no page fragmentation, and newly-formed indexes and transaction log.

Backing Up the Database

Once you have attempted to run the gfix utility to diagnose and repair the database corruption, remain in the C:\Program Files\Borland\IntrBase\Bin directory of the MS DOS window and enter the following command line to create a backup of the database.

gbak -g -b -z -v -user "sysdba" -pas "masterkey" C:\Slx_Bkup\SlxBlank.gdb C:\Slx_Bkup\Backup.gbk

In cases where the corruption is being caused by a transaction in limbo, it may be necessary to include another switch in the above command line. The following command line only needs to be used in the rare case where the gbak InterBase utility run from the command line encounters errors as well due to a transaction in limbo.

gbak -g -b -z -v -l -user "sysdba" -pas "masterkey" C:\Slx_Bkup\SlxBlank.gdb C:\Slx_Bkup\Backup.gbk

Both these backup procedures will run for quite some time depending on the size of the database file involved.

Restoring the Database

Once a backup of the database has been created using the gbak InterBase utility, enter the following command line to restore the backed up database.

gbak -c -v -z -user "sysdba" -pas "masterkey" C:\Slx_Bkup\Bkup.gbk C:\Slx_Bkup\Restore.gdb

This restore procedure will run for quite some time depending on the size of the database file involved. Once it is complete, check to make sure the new database is free of corruption by using the gfix command line from the first section.

gfix -v -f -user "sysdba" -pas "masterkey" C:\Slx_Bkup\Restore.gdb

This should return nothing and indicate that the database corruption has been mended. Therefore, the final step in the process would be to replace the original Production database.

Replacing the Production Database

Once a clean, corruption-free database is obtained, close the MS DOS Window and open Windows Explorer. Browse to the folder containing the Production database (ex: SlxBlank). Rename this database file to Old.gdb. Then browse to the Slx_Bkup folder created earlier that contains the repaired database. Move the Restore.gdb database file to the folder containing the old Production database (now named Old.gdb). Then rename the Restore.gdb file to the real name of the Production database (ex: SlxBlank.gdb).

You may delete the Old.gdb file and the entire contents of the Slx_Bkup folder once verifying that the newly repaired database can be accessed and worked with by SalesLogix. Remember to perform a weekly backup and restore of the database via the InterBase Server Manager to avoid sever database corruption in the future.

Information on the procedures outlined above may also be found on Borland's InterBase web site at http://www.interbase.com/TecSvcs/TecDocs/corrupt.html

Dropping and Re-Adding a Table

It may be necessary to drop and re-add a table if the following occurs:
• It is not possible to re-build the indexes by setting them as inactive and then active or by dropping the indexes and re-adding them.
• The table contains records with duplicate key values which means they can not be deleted by conventional methods.
The instructions below explain the complete process required to re-create a table.

Preliminary Steps

Back up the database and save this in a location. Back up the database again and save this in a different location to the first backup. Extract the schema for the database. This will contain the schema for the table in question. It is very important that this schema is exact, because the order of the fields is significant. Make a copy of this schema. Delete anything that does not refer to the table in question. Make sure that you do not delete the index information that references the table in question. This information is usually at the end of the schema. Save this schema as SCHEMA_1 as you will need it later on in the process. Then modify the name of the table in this copy to something like TABLENAME_2. For example, if the Account table is the table in question, modify the schema so the name of the table is now ACCOUNT_2. Save this newly changed schema as SCHEMA_2.

Creating the Temporary Table

Run SCHEMA_2 that you modified against the database using one of the SQL tools for the database platform (ex: Wisql32 for InterBase, ISQL_w for MS SQL Server, or SQL Plus for Oracle). This will create the second table and it's indexes (ex: ACCOUNT_2). Then run the following SQL statement against this database to copy the data from the table in question to the copy of this table that was just created:
INSERT INTO TABLENAME_2 SELECT DISTINCT * FROM TABLENAME
where TABLENAME_2 would be the newly created table (ex: ACCOUNT_2) and TABLENAME would be the table in question (ex: ACCOUNT). By executing a distinct select, you would also eliminate any rows in the table in question that contained duplicate key values. This query may take some time to run, as it is copying the entire contents of one table into another. Once it is complete, browse the second table to make sure that everything in the original table appears to have been copied.

Re-Creating the Original Table

You may now drop the original table in question. Execute the following SQL statement to accomplish this: 
DROP TABLE TABLENAME
where TABLENAME would be the name of the table in question (ex: ACCOUNT).
Then find SCHEMA_1 that you had previously saved before creating the temporary table. Make a second copy of this schema titled SCHEMA_3. Open SCHEMA_1 which should contain the original name of the table (ex: ACCOUNT) and delete all the information related to indexes for the table. Run this schema against the database to re-create the original table (ex: ACCOUNT). When this is finished, execute the following SQL statement against the database to copy the data from the temporary table to the re-creation of the original table:
INSERT INTO TABLENAME SELECT * FROM TABLENAME_2
where TABLENAME_2 would be the temporary table (ex: ACCOUNT_2) and TABLENAME would be the re-creation of the table in question (ex: ACCOUNT).
This query may take some time to run, as it is copying the entire contents of one table into another. Once it is complete, browse the re-created table to make sure that everything in the temporary table appears to have been copied. Note that this recreated table will be somewhat slow since it has no indexes on it.

You now need to re-add the indexes to the re-creation of the original table. To do this, open the previously saved SCHEMA_3. Copying from this schema, run each index statement against the database individually. Make sure that the table name is that of the original table (ex: ACCOUNT) and not of the temporary table (ex: ACCOUNT_2).

Dropping the Temporary Table

To drop the temporary table, execute the following SQL statement against the database:
DROP TABLE TABLENAME_2
where TABLENAME_2 would be the name of the temporary table (ex: ACCOUNT_2).

The resulting database now has a table with completely re-built indexes and has also been purged of any records with duplicate key values. Due to all the data manipulation that has just been performed, it is an excellent idea to backup and restore the database, especially if the database platform is InterBase.