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 than 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.
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 is 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 requires the IQ servers involved to be taken off line. When to truncate the log is really up to the DBA responsible for supporting the 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.
There are two ways to truncate the transaction log:
Using the –m database server switch, which causes the transaction log to be truncated after each checkpoint for all databases
Using the DELETE_OLD_LOGS database option
There are important considerations to take into account when choosing which of these methods to use. 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.
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 IQ for a transaction log truncation. How this is done is up to the DBA, but the following procedure provides a suggestion.
To truncate the transaction log of a non-multiplex database:
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.
Perform normal full backup procedures, including making copies of the .db and .log files.
Shutdown IQ. Verify that ‘CloseDatabase’ was written in the iq.msg file.
Restart IQ with the configuration file containing the –m option. Note that no user access or transactions should be allowed at this time.
Shut down IQ and restart using the configuration file without the –m option set.
To truncate the transaction log of a multiplex database:
Back up the database from the write server, if you have not already done so.
Set the DELETE_OLD_LOGS option on the write server:
SET OPTION Public.Delete_Old_Logs=’On’
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=JANED-PC}"
Clear the DELETE_OLD_LOGS option on the write server:
SET OPTION Public.Delete_Old_Logs=’Off’
The query server transaction log is always truncated during
synchronization, no matter when the write server log was last truncated.
