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 DATABASE command to make a full backup, and the BACKUP DATABASE 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.

You can use a byte copy utility such as dd only to copy raw data to a file system device or another raw device. Never use byte copy to move dbfiles from a file system to a raw device.

Warning! When you allocate 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, one database administrator on a single connection should manage all dbspaces.

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 SAP Sybase IQ database I/O from I/O for proxy tables in other databases, such as Adaptive Server.
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 as Adaptive Server.

If your IQ requirements are large and complex enough that you need multiple physical systems, consider using SAP 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

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