Creating a database with SQL

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

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.

Once the database is created, you can connect to it and build the tables and other objects that you need in the 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 Utility Server as the engine that will create the database.

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

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

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:

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:

• 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.

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.

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.)

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' 

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' 

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' 

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

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