The transaction log file

The transaction log file contains information that allows Sybase IQ to recover from a system failure. The default filename extension for this file is .log.

To move or rename the transaction log file, use the Transaction Log utility (dblog). For syntax and details, see Chapter 3, “Database Administration Utilities,”Sybase IQ Utility Guide.

WARNING! The Sybase IQ transaction log file is different from most relational database transaction log files. If for some reason you lose your database files, then you lose your database (unless it is the log file that is lost). However, if you have an appropriate backup, then you can reload the database.

Truncating the transaction log

Sybase IQ records in the transaction log the information necessary to recover from a system failure. Although the information logged is small for each committed transaction, the transaction log continues to grow in size. In systems with a high number of transactions that change data, over a period of time the log can grow to be very large.

Log truncation generally requires the Sybase IQ servers involved to be taken off line. When to truncate the log is really up to the DBA responsible for supporting the Sybase IQ systems, and depends on the growth profile of the log file and the operational procedures at the site. The log truncation procedure should be scheduled at least once a month or more frequently if the log file is exceeding 100MB.

Table 5-7shows methods for truncating transaction logs in Sybase IQ.

Table 5-7: Truncating transaction logs

If your database is …

Use this method …

For details, see …

Non-multiplex

The –m switch, which causes the transaction log to be truncated after each checkpoint for all databases

“Truncating the transaction log of a non-multiplex database”

Multiplex

The DELETE_OLD_LOGS database option

“Truncating the transaction log of a multiplex database”

Running

The dbbackup command line utility

Backup utility (dbbackup) in Sybase IQ Utility Guide.

Be sure to use the appropriate method. Sybase IQ database replication inherently relies on transaction log information. For this reason, only the DELETE_OLD_LOGS option should be used for a multiplex database (see “Truncating the transaction log for a multiplex database.”). Also, the transaction log provides Sybase support with valuable information for problem diagnosis and reproduction. Both methods should include archiving the existing log (keeping a copy of the log), in case Sybase support needs the log for further diagnostic work.

Truncating the transaction log for a non-multiplex database

Use the –m server startup switch to truncate the transaction log of a non-multiplex database. Note that leaving the –m server startup switch permanently set is not recommended. This switch should only be used to start Sybase IQ for a transaction log truncation. How this is done is up to the DBA, but the following procedure provides a suggestion.

StepsTruncating the transaction log of a non-multiplex database

  1. Create a copy of the server switches .cfg file with a name identifying the file as the log truncation configuration setting and edit this copy of the file to add the –m switch.

  2. Perform normal full backup procedures, including making copies of the .db and .log files.

  3. Shut down Sybase IQ. Verify that ‘CloseDatabase’ was written in the iq.msg file.

  4. Restart Sybase IQ with the configuration file containing the –m option. Note that no user access or transactions should be allowed at this time.

  5. Shut down Sybase IQ and restart using the configuration file without the –m option set.

Truncating the transaction log for a multiplex database

StepsTruncating the transaction log of a multiplex database

  1. Back up the database from the write server, if you have not already done so.

  2. Set the DELETE_OLD_LOGS option on the write server:

    SET OPTION Public.Delete_Old_Logs=’On’
    
  3. Stop the write server’s dbremote and restart it with the -x command line switch. (Create a special version of the start_dbremote.bat script, found in the write server’s database directory, to do this.) This truncates the log at the write server. For example:

    cd \Server01\mpxdb\cmd /c 
    start dbremote -q -v -x -o
    "d:\Server01\mpxdb\dbremote.log" -c
    "uid=DBA;pwd=SQL;eng=Server01;dbf=
    d:\Server01\mpxdb\mpxdb;
    links=tcpip{port=1704;host=FIONA-PC}" 
    
  4. Clear the DELETE_OLD_LOGS option on the write server:

    SET OPTION Public.Delete_Old_Logs=’Off’
    

NoteThe query server transaction log is always truncated during synchronization, no matter when the write server log was last truncated.