Creating a database with SQL

When you create a database, the database server creates the following four dbspaces:

Dbspace name

Purpose

Default operating system file name

IQ_SYSTEM_MAIN

Main (permanent) IQ Store file

dbname.iq

IQ_SYSTEM_MSG

Message log file

dbname.msg

IQ_SYSTEM_TEMP

Temporary IQ Store file

dbname.iqtmp

SYSTEM

Catalog Store file

dbname.db

The SYSTEM dbspace contains the system tables, which hold the schema definition as you build your database. It also holds a separate checkpoint log, rollback log, and optionally a write file, transaction log, and transaction log mirror, for the Catalog Store.

NoteIn addition to these database files, the database server also uses a temporary file to hold information needed during a session. This temporary file is not the same as the Temporary IQ Store, and is not needed once the database server shuts down. The file has a server-generated name with the extension .tmp. Its location is determined by the TEMP environment variable, or the ASTMP environment variable on UNIX.

Once the database is created, you can connect to it and build the tables and other objects that you need in the database.

Before you create your database

In order to create a database using SQL statements, you must:

To create a database in DBISQL, you need to connect to an existing database, or else start the utility database, a phantom database with no database files and no data. You must start the utility database before creating new databases if no databases are built yet.

You can start the utility database in any of these ways:

For more information on the utility database and its security, see“Utility database server security”.

If you are creating an IQ database for the first time, see the Introduction to Sybase IQ for assistance.

NoteIf the server is started with the -m server option, you cannot create a database.

Locating and moving database files

When you create a database, you specify its location. Before you do so, consider whether you will ever need to move the database.

The IQ Catalog (.db) and transaction log (.log) files can be safely moved. Never attempt to copy a running database. If you use relative pathnames 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) pathnames 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. See Chapter 14, “Data Backup, Recovery, and Archiving” for more information.

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. This leads to poor I/O performance, can overload the local area network, and can lead to problems in the dbspace file. On UNIX platforms, avoid Network File System (NFS) mounted file systems. On Windows platforms, do not place dbspace files on Network Drives owned by another node.

If your IQ requirements are large and complex enough that you need multiple physical systems, consider using Sybase IQ multiplex functionality. See “Multiplex capability” for an overview.

Database file compatibility

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

Using Sybase Central to create an IQ database

To create an IQ database in Sybase Central, select the Sybase IQ plug-in, click the Utilities tab, then double-click Create Database. The Create Database Wizard leads you through the process. For a multiplex database, see “Creating databases with multiplex functionality”. For a nonmultiplex database, see “Creating databases” in Chapter 4, “Managing Databases,” in Introduction to Sybase IQ.

Using the CREATE DATABASE statement

You can use the CREATE DATABASE statement to create IQ databases. You must specify the filename for Catalog Store and the IQ PATH. All other parameters are optional. If you use all of the defaults, your database has these characteristics:

NoteUnless CREATE DATABASE commands include CASE IGNORE or PASSWORD CASE IGNORE, connections to newly created databases must be made with case-sensitive user ID/password combinations.

For a full description of all parameters, see CREATE DATABASE statement in the Sybase IQ Reference Manual. Following are several examples of creating an IQ database.

Using relative pathnames

You can create a database using a relative or fully qualified pathname for each of the files for the database. Sybase recommends that you create databases with relative pathnames. If you specify absolute pathnames, you will not be able to move files to a different pathname without backing up and restoring the database.

If your database is on UNIX, you can define a symbolic link for each pathname, as described in the Sybase IQ Reference Manual.

If you omit the directory path, Sybase IQ locates the files as follows:

NoteYou must start the database server from the directory where the database is located, for any database created with a relative pathname. Using a configuration file to start the server ensures that you start the server from a consistent location.

Specifying an IQ PATH

The required IQ PATH parameter tells Sybase IQ that you are creating an IQ database, not an Anywhere database. You specify the location of your IQ Store in this parameter.

Choose a location for your database carefully. Although you can move an IQ database or any of its files to another location, to do so you must shutdown the database and you may have to perform a backup and restore.

You can add space on a different drive, as described in “Adding dbspaces” but you can only use this additional space for new data. You cannot readily move a particular table, index, or rows of data from one location to another. You would need to drop the table or index, recreate it, and reload it; or you would need to delete those rows, and reinsert them.

Each operating system has its own format for raw device names. See Chapter 8, “Physical Limitations” in the Sybase IQ Reference Manual for an important note about initializing raw devices on Sun Solaris.

Table 5-1: Raw device names on UNIX

UNIX Platform

Example

AIX

/dev/rraw121v

HP-UX

/dev/vg03/rrchee12g

Sun Solaris

/dev/rsd0c

Table 5-2: Raw device names on Windows

Device type

Name format required

Example

Partitioned

Letter assigned to that partition

\\.\C: in Sybase Central, \\\\.\\C: in SQL

Not partitioned

PhysicalDriveN, where N is a number starting with 0 and going as large as needed. You can find the physical drive numbers by running Disk Administrator in Administrative Tools.

\\.\ PhysicalDrive32 in Sybase Central, \\\\.\\ PhysicalDrive32 in SQL

On Windows systems, when you specify device names that include a backslash, you must double the backslash to keep the system from mistaking a backslash/letter combination for an escape sequence such as tab or newline command. (This behavior results when the ESCAPE_CHARACTER option is set ON for the database.)

You must always double the backslash when naming raw devices on Windows in SQL statements. See Example 4.

Example 1

The following statement creates an IQ database called company.db. This database consists of four Windows files:

CREATE DATABASE 'company.db' 
IQ SIZE 200
IQ PATH 'c:\\company\\iqdata\\company.iq' 

Example 2

The following statement creates an IQ database called company.db. This database consists of four UNIX files:

CREATE DATABASE 'company.db' 
IQ SIZE 2000
IQ PATH '/disk1/company/iqdata/company.iq' 

Example 3

The following UNIX example creates an IQ database called company with a raw partition for IQ PATH.

CREATE DATABASE 'company' 
IQ PATH '/dev/rdsk/c0t0d0s0' 

Example 4

The following Windows example creates an IQ database called company with a raw partition for IQ PATH.

CREATE DATABASE 'company' 
IQ PATH '\\\\.\\D:'