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.

In 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 IQ temporary 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 coordinator 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:

Start the database server
Start dbisql

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:

Start the database server without a database by specifying only -n enginename on the startup command.
Start dbisql from the command line, setting the Database Name to utility_db in the connection string, as in:
```
dbisqlc -c "uid=dba;pwd=sql;eng=myserver;dbn=utility_db;...
```
(You must not specify it as the Database File, because utility_db has no database file.)
In Sybase Central, in the Create Database wizard, choose Use the IQ Agent on a Remote Host to Start a Utility Server.

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.

If 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 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. See Chapter 13, “System Recovery and Database Repair.”

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.6; likewise, old servers cannot manage new databases.

Using Sybase Central to create an IQ database

To create an IQ database in Sybase Central, see “Creating databases” in Chapter 4, “Managing Databases,” in Introduction to Sybase IQ. To create multiplex databases, see Using Sybase IQ Multiplex.

Using the CREATE DATABASE statement

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

Case-sensitive (CASE RESPECT). “ABC” compares NOT EQUAL to “abc”. The default login is user ID DBA (uppercase) and password sql (lowercase). By default, passwords are case sensitive. User names are always case insensitive.

Catalog page size of 4096 bytes (PAGE SIZE 4096).

When comparing two character strings of unequal length, IQ treats the shorter one as if it were padded with blanks to the length of the longer one, so that ‘abc’ compares equal to ‘abc’ (BLANK PADDING ON).

Incompatible with Adaptive Server Enterprise.

IQ page size is 128KB (IQ PAGE SIZE 131072).

IQ message file and IQ temporary store are in the same directory as the catalog store. See also “Using relative path names.”
For a raw device, IQ SIZE and TEMPORARY SIZE are the maximum size of the raw partition. For operating system files, see the discussion of this parameter below.
IQ temporary store size is half the IQ size.
jConnect JDBC driver is enabled (JCONNECT ON).
The collation ISO_BINENG is used. The collation order is the same as the order of characters in the ASCII character set. In a case-sensitive database, all uppercase letters precede all lowercase letters (for example, both 'A' and 'B' precede 'a').
IQ RESERVE and TEMPORARY RESERVE are 0.

For details about password case-sensitivity in new databases, see “User IDs and passwords” in Appendix A, “Compatibility with Other Sybase Databases,”in Reference: Building Blocks, Tables, and Procedures.

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

Using relative path names

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

If your database is on UNIX, you can define a symbolic link for each path name, as described in CREATE DATABASE statement in Reference: Statements and Options.

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

The catalog store is created relative to the working directory of the server.
The IQ store, temporary store, and message log files are created in the same directory as, or relative to, the catalog store.
The transaction log is created in the same directory as the catalog store. (This also occurs if you do not specify any file name.) However, you should place it on a different physical device from the catalog store and IQ store, on the same physical machine.

You must start the database server from the directory where the database is located, for any database created with a relative path name. 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 shut down 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 index, table, or rows of data from one location to another.

Each operating system has its own format for raw device names. See Chapter 6, “Physical Limitations,” in Reference: Building Blocks, Tables, and Procedures for an important note about initializing raw devices on Sun Solaris.

**Table 5-2: Raw device names on UNIX**
UNIX Platform	Example
AIX	/dev/rraw121v
HP-UX	/dev/vg03/rrchee12g
Sun Solaris	/dev/rsd0c
Sun AMD	/dev/rdsk/c5t0d0s1

**Table 5-3: 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.

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:

The catalog store is in company.db, in the directory where the server was started (in this case, c:\company)
The IQ store is in c:\company\iqdata\company.iq
The temporary store is in c:\company\company.iqtmp
The IQ message log file is in c:\company\company.iqmsg

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:

The catalog store is in company.db, in the directory where the server was started (in this case, /disk1/company)
The IQ store is in /disk1/company/iqdata/company.iq
The temporary store is in /disk1/company/iqdata/company.iqtmp
The IQ message log file is in /disk1/company/iqdata/company.iqmsg

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:'