CREATE DATABASE statement

Description

Creates a database. The database consists of several operating system files.

Syntax

CREATE DATABASE db-name
... [ [ TRANSACTION ] { LOG ON [ log-file-name ]
      [ MIRROR mirror-file-name] } ]
... [ CASE { RESPECT | IGNORE } ]
... [ PAGE SIZE page-size ]
... [ COLLATION collation-label ] [ ENCRYPTED 
ON | OFF | key-spec} ] {... [ BLANK PADDING ON ]
 ]
... [ JAVA { ON | OFF } ]
... [ JCONNECT { ON | OFF } ]
... [ PASSWORD CASE { RESPECT | IGNORE } ]
... [ IQ PATH iq-file-name ]
... [ IQ SIZE iq-file-size ]
... [ IQ PAGE SIZE iq-page-size ]
... [ BLOCK SIZE block-size ]
... [ IQ RESERVE sizeMB ]
... [ TEMPORARY RESERVE sizeMB ]
... [ MESSAGE PATH message-file-name ]
... [ TEMPORARY PATH temp-file-name ]
.... [ TEMPORARY SIZE temp-db-size ]

Parameters

Examples

Example 1

• The following Windows example creates an IQ database named mydb with its corresponding mydb.db, mydb.iq, mydb.iqtmp, and mydb.iqmsg files in the C:\s1\data directory:

CREATE DATABASE 'C:\\s1\\data\\mydb'
BLANK PADDING ON
IQ PATH 'C:\\s1\\data'
IQ SIZE 2000
IQ PAGE SIZE 65536

• The following UNIX command creates an IQ database with raw devices for IQ PATH and TEMPORARY PATH. The default IQ page size of 128KB applies.

CREATE DATABASE '/s1/data/bigdb'
IQ PATH '/dev/md/rdsk/bigdb'
MESSAGE PATH '/s1/data'
TEMPORARY PATH '/dev/md/rdsk/bigtmp'

• The following UNIX example creates a strongly encrypted IQ database using the AES encryption algorithm with the key “is!seCret.”

CREATE DATABASE 'marvin.db'
JAVA OFF
BLANK PADDING ON
CASE RESPECT
COLLATION 'ISO_BINENG'
IQ PATH '/filesystem/marvin.main1'
IQ SIZE 6400
IQ PAGE SIZE 262144
TEMPORARY PATH '/filesystem/marvin.temp1'
TEMPORARY SIZE 3200
MESSAGE PATH '/filesystem/marvin.mess1'
ENCRYPTED ON KEY 'is!seCret' ALGORITHM 'AES'

Usage

Creates an IQ database with the supplied name and attributes. The IQ PATH clause is required for creating the IQ database. Otherwise, you will create a standard Adaptive Server Anywhere database. If you omit the IQ PATH option, specifying any of the following options will generate an error: IQ SIZE, IQ PAGE SIZE, BLOCK SIZE, MESSAGE PATH, TEMPORARY PATH, and TEMPORARY SIZE.

When Sybase IQ creates an IQ database, it automatically generates four database files to store different types of data that constitute an IQ database. Each file corresponds to a dbspace, the logical name by which Sybase IQ identifies database files. The files are:

• db-name.db is the file that holds the catalog dbspace, SYSTEM. It contains the system tables and stored procedures describing the database and any standard Anywhere database objects you add. If you do not include the .db extension, Sybase IQ will add it. This initial dbspace contains the Catalog Store, and you can later add dbspaces to increase its size. It cannot be created on a raw partition.

• db-name.iq is the default name of the file that holds the main data dbspace, IQ_SYSTEM_MAIN, containing the IQ tables and indexes. You can specify a different file name with the IQ PATH clause. This initial dbspace contains the IQ Store, and you can later add dbspaces to increase its size.

• db-name.iqtmp is the default name of the file that holds the initial temporary dbspace, IQ_SYSTEM_TEMP. It contains the temporary tables generated by certain queries. The required size of this file can vary depending on the type of query and amount of data. You can specify a different name using the TEMPORARY PATH clause. This initial dbspace contains the Temporary Store, and you can later add dbspaces to increase its size.

• db-name.iqmsg is the default name of the file that contains the messages trace dbspace, IQ_SYSTEM_MSG. You can specify a different file name using the MESSAGE PATH clause.

In addition to these files, an IQ database has a transaction log file (db-name.log) and may have a transaction log mirror file.

The file names ( db-name, log-file-name, mirror-file-name, iq-file-name, message-file-name, temp-file-name) are strings containing operating system file names. As literal strings, they must be enclosed in single quotes.

• In Windows, if you specify a path, any backslash characters (\) must be doubled if they are followed by an n or an x. This prevents them being interpreted as a newline character (\n) or as a hexadecimal number (\x), according to the rules for strings in SQL. It is safer to always double the backslash. For example:

  CREATE DATABASE 'c:\\sybase\\mydb.db'
  LOG ON 'e:\\logdrive\\mydb.log'
  JCONNECT OFF
  IQ PATH 'c:\\sybase\\mydb'
  IQ SIZE 40
  

• If you specify no path, or a relative path:

  • The Catalog Store file (db-name.db) 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.

Relative pathnames are recommended.

On UNIX systems you can create symbolic links, which are indirect pointers that contain the pathname of the file to which they point. You can use symbolic links as relative pathnames. There are several advantages to creating a symbolic link for the database filename:

• Symbolic links to raw devices can have meaningful names, while the actual device name syntax can be obscure.

• A symbolic name may eliminate problems restoring a database file that was moved to a new directory since it was backed up.

• In multiplex databases, symbolic links can be used to avoid device name conflicts among multiple servers when you use raw devices for IQ Temporary storage.

To create a symbolic link, use the ln -s command. For example:

ln -s /disk1/company/iqdata/company.iq company_iq_store

Once you create this link, you can specify the symbolic link in commands like CREATE DATABASE or RESTORE instead of the fully qualified pathname.

When you create a database or a dbspace, the path for every dbspace file must be unique. If your CREATE DATABASE command specifies the identical path and filename for these two stores, you receive an error.

SET TEMPORARY OPTION AGGREGATION_PREFERENCE=-2

This option is provided for compatibility with the ISO/ANSI SQL standard. The default (RESPECT) is that all comparisons are case sensitive. CASE RESPECT provides better performance than CASE IGNORE.

DBA

SQL

When you start a database, its page size cannot be larger than the page size of the current server. The server page size is taken from the first set of databases started or is set on the server command line using the -gp command-line option.

Command line length for any statement is limited to the Catalog page size. The 4KB default is large enough in most cases; however, in a few cases a larger PAGE SIZE value is needed to accommodate very long commands, such as RESTORE commands that reference numerous dbspaces.

• Simple encryption is equivalent to obfuscation. The data is unreadable, but someone with cryptographic expertise could decipher the data. Simple encryption is achieved by specifying the ENCRYPTED clause with no KEY clause.

• Strong encryption is achieved through the use of a 128-bit algorithm and a security key. The data is unreadable and virtually undecipherable without the key.

Encryption can only be specified during database creation. (To introduce encryption to an existing database requires a complete unload, database recreation, and reloading of all data.) To create a strongly encrypted database, specify the ENCRYPTED clause with the KEY clause. As with most passwords, it is best to choose a KEY value that cannot be easily guessed. We recommend that you choose a value for your KEY that is at least 16 characters long, contains a mix of upper and lower case, and includes numbers, letters and special characters.

You will require this key each time you start the database.

Using the ALGORITHM clause in conjunction with the ENCRYPTED and KEY clauses lets you specify the encryption algorithm. Currently, the only supported algorithm is AES. If the ENCRYPTED clause is used but no algorithm is specified, the default is AES. Encryption is OFF by default.

For example, the two strings

'Smith'
'Smith   '

would be treated as equal in a database created with BLANK PADDING ON.

select db_property ( ‘BlankPadding’ )

For the best performance, Sybase recommends the following minimum IQ page sizes:

• 64KB (IQ PAGE SIZE 65536) for databases whose largest table contains up to 1 billion rows, or a total size less than 8TB. This is the absolute minimum for a new database. On 32-bit platforms, a 64KB IQ page size gives the best performance.

• 128KB (IQ PAGE SIZE 131072) for databases on a 64-bit platform whose largest table contains more than 1 billion rows and fewer than 4 billion rows, or may grow to a total size of 8TB or greater. 128KB is the default IQ page size.

• 256KB (IQ PAGE SIZE 262144) for databases on a 64-bit platform whose largest table contains more than 4 billion rows, or may grow to a total size of 8TB or greater.

Very wide tables, such as tables with multiple columns of wide VARCHAR data (columns from 255 to 32,767 bytes) may need the next larger IQ PAGE SIZE.

When IQ RESERVE is specified, the database uses more space for internal (free list) structures. If reserve size is too large, the space needed for the internal structures can be larger than the specified size, which results in an error.

When TEMPORARY RESERVE is specified, the database uses more space for internal (free list) structures. If reserve size is too large, the space needed for the internal structures can be larger than the specified size, which results in an error.

Several operating system files are created.

Standards

Permissions

The permissions required to execute this statement are set on the server command line, using the -gu command-line option. The default setting is to require DBA authority.

The account under which the server is running must have write permissions on the directories where files are created.

See also