Database files

The following sections provide an overview of the type of files, including database, transaction, and temporary files, that comprise a database. The differences between the implementation of these files in SQL Anywhere and UltraLite is also discussed.

SQL Anywhere database files

All the information in a SQL Anywhere database is usually stored in a single database file, which can be copied from one computer to another. It is possible have a database made up of several files, but this is generally only required for very large databases.

In addition to the database file, SQL Anywhere uses two other files when running a database: the transaction log and the temporary file.

  • The database file   Internally, the database file is composed of pages: fixed size areas of disk. The data access layer reads and writes data one page at a time. Many pages hold the data that is in the database tables, but other pages hold index information, information about the distribution of data within the database, and so on.

  • The transaction log   The transaction log is a separate file that contains a record of all the operations performed on the database. Normally, the transaction log has the same name as the database file, except that it ends with the suffix .log instead of .db. It has three important functions:

    • Record operations on your data to enable recovery   You can recreate your database from a backup together with the transaction log if the database file is damaged.

    • Improve performance   By writing information to the transaction log, the database server can safely process your statements without writing to the database file as frequently.

    • Enable database replication   SQL Remote and MobiLink synchronization use the transaction log to synchronize changes to your other databases.

  • The temporary file   The temporary file is created when the database server starts, and is erased when the database server stops. As its name suggests, the temporary file is used while the database server is running to hold temporary information. The temporary file does not hold information that needs to be kept between sessions.

    The UltraLite temporary file is stored in the same directory as the database file.

    See TMP, TEMPDIR, and TEMP environment variables.

Inside the UltraLite database

UltraLite databases contain the same features as described above with the following exceptions:

  • UltraLite database files don't contain information about the distribution of data within the database.
  • UltraLite keeps track of its transactions internally, not in a separate log file.
  • The UltraLite temporary file is stored in the same directory as the database file.

See UltraLite transaction and state management.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0