Changing the Checkpoint time in SQL Anywhere

Product/Release:LANSA For Windows
Abstract:How to change the period between checkpoints in the SQL Anywhere database
Submitted By:LANSA Technical Support

Sybase's SQL Anywhere performs a Checkpoint as standard every 30 minutes. The checkpoint, which is the same as a commit, can take anywhere between 2 and 10 minutes, depending on the size of the database. If when performing a lot of compiles and full function checks, the checkpoint starts during the compilation, it makes a compile (or full function check) look as if it has crashed and the user may be tempted to stop the compiles prematurely.

If this situation occurs it is suggested that the period between checkpoints is lengthened. This can be done easily by creating a simple batch file. This batch file starts the database and should be left running all the time. As well as starting the database it also points to a text file that sets up the new checkpoint period.

Name the batch file anything you wish. Either place it in your autoexec.bat to make sure it is run at startup or simply run it from the command line at any time.

The format of the batch file is as follows:

e.g. DBSETUP.BAT would contain the following one line:

dbeng50 @<drive>:\lansa\dbcfg.txt (<drive> is where your LANSA directory is)

In <drive>:\LANSA directory create a dbcfg.txt text file with the following parameter :

-gc 120 (where 120 is the number of minutes)


This will give the LX_LANSA database a new checkpoint of 120 minutes. The number of minutes can be increased to whatever value is necessary.

Other parameters can also be included in this dbcfg.txt. For example to speed up the performance increase the cache memory available from 2Mb to 4Mb. Just add another parameter to the dbcfg.txt file as follows:

-gc 120

-c 4096


Another way of setting the checkpoint to a certain time is to change the checkpoint time permanently in the LX_LANSA database. Using this method the database can be started by LANSA rather than having it running all the time (which would be the case if the batch file starts the database in your autoexec.bat). The syntax of the command to permanently set the checkpoint to a certain time in the LX_LANSA database is:

SET OPTION PUBLIC.Checkpoint_time=120 (where 120 is the number of minutes)

For a full list of parameters that can be specified when using the batch file method and when the SET OPTION… command is used as well as a comprehensive description of the Checkpoint and how it works please refer to the SQL Anywhere Online documentation.