IQ transaction log

[Former Member]

Hi,

what is the difference between catalog store transaction log and IQ main store transaction log?

Regards,

Santosh

[claude]

Hi,

*** Information from the document ***

-->catalog store transaction log
The transaction log file contains transaction for the catalog store only. It is used to roll back (undo) or roll forward changes as necessary.

The catalog store transaction log file does not contain any transactions for the warehouse data in the IQ store. The name of the physical file is dbname.log and it is stored in the same directory as the .db file. The catalog store transaction log is a small file whose growth over time is related to its activity. This file must exist as a component of the SAP Sybase IQ database.

Do not delete or alter this file. If this file is lost or damaged, technical support may be necessary to recover the database. To protect .log file, it may be mirrored to another directory. All transactions involving data in the catalog store are logged to this transaction log, including: •Any create, alter or drop commands associated with SAP Sybase IQ database objects
*Adding or removing users from the database
*Adding or changing user permissions
*Adding or changing a dbspace to the database
*Any DML statement for user data that is stored in the catalog

-->IQ Main Store (for data)

The IQ store holds all the user data (indexes) for the database compressed on disk.
It contains the transaction log for data in the IQ tables and structure to manage space allocation (free list). The internal transaction log manages transactions involving IQ data. The free list tracks page usage with a given dbspace.
The IQ store is a pre-allocated space (although it can be enlarged with advance planning). Initially, the IQ store has one file or raw device when the database is created, but additional files may be added to the IQ store as needed.
There is one IQ store per database, which usually consists of many files or raw partitions. When using raw partitions, the physical name will be the partition name. If setting up IQ multiplex on multiple hosts, the IQ store must be created using raw partitions.
The IQ main store consists of least two dbspaces: •IQ_SYSTEM_MAIN – The first dbspace created when creating a database, it contains system metadata. It should not be used for user data
*USER_DBSPACE – Created and named by DBA, it holds user tables and indexes. DBAs may create additional dbspaces, as needed. The database option Default_Dbspace controls where tables and indexes are stored.

I hope it helps.

Claude

[Former Member]

Thanks Claude,

Then how to manage IQ transaction log.

Thanks,

Santosh

[markmumy]

The "IQ transaction log" is stored in IQ_SYSTEM_MAIN.  This is a fixed size storage area that should be isolated and dedicated to IQ system work.  Read that as don't put user data in IQ_SYSTEM_MAIN.

If  you check out my sizing guide on this topic, it talks about setting aside 2-4% of your total system main store for system main.  That gives IQ enough space for all of its transactional workload.

Mark

[former_member333181]

As per information in IQ manual,

Transaction and Message Logs

Manage the size of the transaction and message logs to conserve disk        space.

The transaction log file contains recovery and auditing information. Place the            transaction log on a separate device or partition from the database itself to avoid            database file fragmentation and to protect against media failure.

The transaction log can consume a large amount of disk space over time. Truncate the            transaction log periodically to conserve disk space.

To truncate the log:

1. Shut down the server.
2. Start the server with the –m parameter as part of the                        start_iq command or .cfg file.
3. Shut down and restart the server without the –m                    parameter.

Do not leave the –m switch permanently set. When                –m is set, there is no protection against media failure on the            device that contains the database files. Remove the –m from the                .cfg after you restart the server. To move or rename the            transaction log file, use the transaction log utility (dblog).

Warning!              The SAP 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.

Regards

Shashi