Database File Placement

When you create a database, consider whether you will ever need to move the database. The location of files for dbspaces may also affect performance.

The IQ catalog (.db) and transaction log (.log) files can be safely moved. Never attempt to copy a running database. If you use relative path names to create the database, then you can move the files by shutting down the server and using the operating system copy file command. If you use absolute (fully qualified) path names to create the database, then you must move the files by using the BACKUP command to make a full backup, and the RESTORE command with the RENAME option to restore the backup.

IQ dbspaces on raw partitions can be moved to other partitions while the database is shut down. The new partition must be at least as large as the current dbspace size. The new partition must also have the same path in order for the dbspace to start.

Warning! When you allocate file system files for dbspaces (System, IQ Main or IQ Temporary), do not place the files on a file system that is shared over a local area network. Doing so can lead to poor I/O performance and other problems, including overloading the local area network and problems in the dbspace file. On UNIX and Linux platforms, avoid Network File System (NFS) mounted file systems. On Windows, do not place dbspace files on network drives owned by another node. These file placement recommendations also apply to log files.

To avoid conflicts, Sybase recommends that dbspace management be performed by a single database administrator on a single connection.

Performance related to randomly accessed files, including the System, IQ Main, and IQ Temporary dbfiles, can be improved by increasing the number of disk drives devoted to those files. Performance related to sequentially accessed files, including the transaction log and message log files, can be improved by locating these files on dedicated disk drives.

Suggestions to reduce file placement impact on performance:

Keep random disk I/O away from sequential disk I/O.
Place the database file, temporary dbspace, and transaction log file on the same physical machine as the database server.
Isolate Sybase IQ database I/O from I/O for proxy tables in other databases, such as Adaptive Server Enterprise.
Place the transaction log and message log on separate disks from the IQ store, catalog store, and temporary store, and from any proxy databases such Adaptive Server Enterprise.

If your IQ requirements are large and complex enough that you need multiple physical systems, consider using Sybase IQ multiplex functionality.

Raw Device Permissions

Make sure that all raw devices have read and write permissions before you create a database or add a dbspace. Check to see that /dev/rawctl has read permission. Raw device names on Linux use /dev/raw/rdevname. For example, /dev/raw/raw10.

Database File Compatibility

Sybase IQ servers cannot manage databases created with versions prior to Sybase IQ 12.6; likewise, old servers cannot manage new databases.