Database cleaning

 

Database cleaning

For performance reasons, the productive database should be cleaned up in terms of content and the number of records posted. Only the most recent shipping data from a specific period in the database.
Access to older data for information research purposes is guaranteed by prior storage of a copy of the database in an archive directory.
 

Method

All steps must be performed on the server 

• Check available disk space with the HVS32 directory. Temporarily three times the current database size (HVS32.FDB) should be available as free storage space.
• Terminate all running HVS32 dispatch programs, if available also the currently running monitoring instances
• Rename the HVS32.FDB file in the HVS32\DATABASE directory to HVS32AKT.FDB as a test. If this is possible, no more processes are accessing the database and you can continue
• Copy the content of "clear.zip" into the \DATABASE directory
• Copy the HVS32AKT.FDB file to the HVS32\ARCHIV folder and rename it to HVS32_jjjjmmdd.FDB. For the sake of clarification, the intended cleansing date should be used here for naming (i.e. the date by which the shipping data should later be cleaned)
• Rename the original file in the HVS32\DATABASE folder to HVS32.FDB and restart the Firebird services
• Switch to the DOS prompt and navigate to the HVS32\DATABASE directory
• The clear program must be called with 4 parameters:
  clear <Starting date-Cleanup limit Format yyyy.mm.tt> <Print date-print date f. Storage Format yyyy.mm.tt> <Database user> <Database password>Bsp: clear 2006.01.01 2006.01.01 dbUser dbPassword

Parameter explanation

• Starting Date Purge Limit: Date by which all booked packages with an older starting date are deleted
• Print date-Cleanup Limit: Date by which all booked packages without an exit date (i.e. with the status Stored) with an older print date are deleted.
• Data base user: Please ask support
• Data base password: Please ask support

Program Sequence

The previous call is the last step that must be performed interactively by the user. The following operations are automatic. However, the screen messages should be used to check whether it is possible to log on to the database with the entered user/password.

• Cleanup of the pre-package table
• Cleanup of the reservation delivery table
• Run through the booked shipping data and delete process based on the entered date limits, the entries can be made here in particular: 
  „writing data for table VERSANDEINHEIT”
  „writing data for table ABRECHNUNGSEINHEIT”
  „writing data for table LIEFERUNG”
  it may take some time with no visible progress. This is not an error in the process, but skipping the deleted records requires a certain amount of time here.
• Backup swapping to a temporary swap file with the GBAK extension
• Creation of a new temporary database RESTORED_HVS32.FDB by restoring the
• Backup swapping (to achieve a reduction in the size of the database.)
• After successful restore renaming of HVS32.FDB in HVS32.FDB.tt_mm_jj_hh_nn_ss.BAK
• Remaining of RESTORED_HVS32.FDB in HVS32.FDB

At the end of the clear program, the database is renamed. This step sometimes doesn't work for very large databases. If this is the case, the database (HVS32.fdb) still has the same size as before after the cleanup.

 

 

Data bank cleanup (automatic)

An explanation of the add-on module/expansion module HVS32 Database archiving service can be found here.