CREATE DATABASE statement

Description

Creates a database consisting of several operating system files.

Syntax

CREATE DATABASE db-name
… [ [ TRANSACTION ] { LOG ONlog-file-name ]
      [ MIRROR mirror-file-name ] } ]
… [ CASERESPECT | IGNORE } ]
… [ PAGE SIZE page-size ]
… [ COLLATION collation-label[( collation-tailoring-string ) ] ]
… [ ENCRYPTED [ TABLE ] {algorithm-key-spec | OFF } ]
… { … [ BLANK PADDING ON ]
… [ JAVAON | OFF } ]
… [ JCONNECTON | OFF } ]
… [ 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

db-name | log-file-name | mirror-file-name | iq-file-name | message-file-name | temp-file-name:

'file-name'

page-size:

4096 | 8192 | 16384 | 32768 }

iq-page-size:

65536 | 131072 | 262144 | 524288 }

block-size:

4096 | 8192 | 16384 | 32768 }

collation-label:

string

collation-tailoring-string:

keyword=value

algorithm-key-spec:

ON | [ ON ] KEY key [ ALGORITHM AES-algorithm ] | [ ON ] ALGORITHM AES-algorithm KEY key | [ ON ] ALGORITHM ‘SIMPLE

AES-algorithm:

AES’ | ‘AES256’ | ‘AES_FIPS’ | ‘AES256_FIPS

key:

quoted string

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

Example 2

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/bigdb.iqmsg'
TEMPORARY PATH '/dev/md/rdsk/bigtmp'

Example 3

The following Windows command creates an IQ database with a raw device for IQ PATH. Note the doubled backslashes in the raw device name (a Windows requirement):

CREATE DATABASE 'company'
IQ PATH '\\\\.\\E:'
JCONNECT OFF
IQ SIZE 40

Example 4

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
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 create a standard SQL Anywhere database. If you omit the IQ PATH option, specifying any of the following options generates 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:

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


File names

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.

Relative path names are recommended.

WARNING! The database file, temporary dbspace, and transaction log file must be located on the same physical machine as the database server. Do not place database files and transaction log files on a network drive. The transaction log should be on a separate device from its mirror, however.

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

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 path name.

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 file name for these two stores, you receive an error.

NoteTo create multiplex databases, see Using Sybase IQ Multiplex.

You can create a unique path in any of these ways:

WARNING! On UNIX platforms, to maintain database consistency, you must specify file names that are links to different files. Sybase IQ cannot detect the target where linked files point. Even if the file names in the command differ, it is your responsibility to make sure they do not point to the same operating system file.


Clauses and options of CREATE DATABASE

TRANSACTION LOG The transaction log is a file where the database server logs all changes made to the database. The transaction log plays a key role in system recovery. If you do not specify any TRANSACTION LOG clause, or if you omit a path for the file name, it is placed in the same directory as the .db file. However, you should place it on a different physical device from the .db and .iq. It cannot be created on a raw partition.

MIRROR A transaction log mirror is an identical copy of a transaction log, usually maintained on a separate device, for greater protection of your data. By default, Sybase IQ does not use a mirrored transaction log. If you do want to use a transaction log mirror, you must provide a file name. If you use a relative path, the transaction log mirror is created relative to the directory of the catalog store (db-name.db). Sybase recommends that you always create a mirror copy of the transaction log.

CASE For databases created with CASE RESPECT, all affected values are case sensitive in comparisons and string operations. Database object names such as columns, procedures, or user IDs, are unaffected. Dbspace names are case insensitive for databases created with CASE IGNORE or CASE RESPECT.

The default (RESPECT) is that all comparisons are case sensitive. CASE RESPECT provides better performance than CASE IGNORE.

Character strings inserted into tables are always stored in the case they are entered, regardless of whether the database is case sensitive or not. If the string Value is inserted into a character data type column, the string is always stored in the database with an uppercase V and the remainder of the letters lowercase. SELECT statements return the string as Value. If the database is not case sensitive, however, all comparisons make Value the same as value, VALUE, and so on. The IQ server may return results in any combination of lowercase and uppercase, so you cannot expect case sensitive results in a database that is case insensitive (CASE IGNORE).

For example, given the following table and data:

CREATE TABLE tb (id int NOT NULL,
                 string VARCHAR(30) NOT NULL);
INSERT INTO tb VALUES (1, ‘ONE’);
SELECT * FROM tb WHERE string = ‘oNe’;

The result of the SELECT can be ‘oNe’ (as specified in the WHERE clause) and not necessarily ‘ONE’ (as stored in the database).

Similarly, the result of

SELECT * FROM tb WHERE string = ‘One’;

can be ‘One’ and the result of

SELECT * FROM tb WHERE string = ‘ONe’;

can be ‘ONe’.

All databases are created with at least one user ID:

DBA

and password:

sql

In new databases, all passwords are case sensitive, regardless of the case-sensitivity of the database. The user ID is unaffected by the CASE RESPECT setting.

PAGE SIZE The page size for the SQL Anywhere segment of the database (containing the catalog tables) can be 4096, 8192, 16384, or 32768 bytes, with 4096 being the default. Normally, you should use the default, 4096 (4KB). Large databases might need a larger page size than the default and could see performance benefits as a result. Smaller values might limit the number of columns your database can support. If you specify a page size smaller than 4096, Sybase IQ uses a page size of 4096.

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. A larger page size might also be needed to execute queries involving large numbers of tables or views.

Because the default Catalog page size is 4KB, this is only a problem when the connection is to a database such as utility_db, which has a page size of 1024. This restriction may cause RESTORE commands that reference numerous dbspaces to fail. To avoid the problem, make sure the length of SQL command lines is less than the Catalog page size.

Alternatively, start the engine with -gp 32768 to increase Catalog page size.

COLLATION The collation sequence used for sorting and comparison of character data types in the database. The collation provides character comparison and ordering information for the encoding (character set) being used. If the COLLATION clause is not specified, Sybase IQ chooses a collation based on the operating system language and encoding.

For most operating systems, the default collation sequence is ISO_BINENG, which provides the best performance. In ISO_BINENG, the collation order is the same as the order of characters in the ASCII character set. All uppercase letters precede all lowercase letters (for example, both ‘A’ and ‘B’ precede ‘a’).

The collation can be chosen from a list of supported collations. For SQL Anywhere databases created on a Sybase IQ server, the collation can also be the Unicode Collation Algorithm (UCA). If UCA is specified, you should also specify the ENCODING clause. For more information on the ENCODING clause, see “CREATE DATABASE statement” in SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (A-D).

Sybase IQ does not support any of the UCA based collations for IQ databases. If a UCA based collation is specified in the CREATE DATABASE statement for an IQ database, the server returns the error “UCA collation is not supported” and database creation fails.

Carefully choosing your collation is important. The collation cannot be changed after the database is created. For information on choosing a collation, see Chapter 11, “International Languages and Character Sets” in the System Administration Guide: Volume 1.

Optionally, you can specify collation tailoring options (collation-tailoring-string) for additional control over the sorting and comparing of characters. These options take the form of keyword=value pairs, assembled in parentheses, following the collation name.

NoteSeveral collation tailoring options are supported when specifying the UCA collation for a SQL Anywhere database created on an Sybase IQ server. For all other collations and for Sybase IQ, only case sensitivity tailoring is supported. Also, databases created with collation tailoring options cannot be started using a pre-15.0 database sever.

Table 1-4 contains the supported keyword, allowed alternate forms, and allowed values for the collation tailoring option (collation-tailoring-string) for a Sybase IQ database.

Table 1-4: Collation tailoring option for Sybase IQ

Keyword

Collation

Alternate forms

Allowed values

CaseSensitivity

All supported collations

CaseSensitive, Case

  • respect    Respect case differences between letters. For the UCA collation, this is equivalent to UpperFirst. For other collations, it depends on the collation itself.

  • ignore   Ignore case differences between letters.

  • UpperFirst   Always sort upper case first (Aa).

  • LowerFirst   Always sort lowercase first (aA).

For syntax and a complete list of the collation tailoring options supported when specifying the UCA collation for a SQL Anywhere database, see “CREATE DATABASE statement” in SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (A-D).

ENCRYPTED Encryption makes the data stored in your physical database file unreadable. Use the CREATE DATABASE ENCRYPTED keyword without the TABLE keyword when you want to encrypt the entire database. Use the ENCRYPTED TABLE clause when you only want to enable table encryption for SQL Anywhere tables. Table level encryption is not supported for Sybase IQ tables. Enabling table encryption means that the tables that are subsequently created or altered using the ENCRYPTED clause are encrypted using the settings you specified at database creation.

There are two levels of database and table encryption: simple and strong.

WARNING! Protect your encryption key! Be sure to store a copy of your key in a safe location. A lost key results in a completely inaccessible database from which there is no recovery.

Encryption can be specified only during database creation. To introduce encryption to an existing database requires a complete unload, database recreation, and reload of all data.

If the ENCRYPTED clause is used but no algorithm is specified, the default is AES. Encryption is OFF by default.

BLANK PADDING By default, trailing blanks are ignored for comparison purposes (BLANK PADDING ON), and Embedded SQL programs pad strings fetched into character arrays. This option is provided for compatibility with the ISO/ANSI SQL standard.

For example, these two strings are treated as equal in a database created with BLANK PADDING ON:

'Smith'
'Smith   '

NoteCREATE DATABASE no longer supports BLANK PADDING OFF.

JAVA To use Java in your database, you must install entries for the Sybase runtime Java classes into the catalog system tables. By default, these entries are installed. If you do not need to use Java, you can specify JAVA OFF to avoid installing these entries.

JCONNECT To use the Sybase jConnect for JDBC driver to access system catalog information, you must install jConnect support. Use this option to exclude the jConnect system objects (the default is ON). You can still use JDBC, as long as you do not access system information.

IQ PATH The path name of the main segment file containing the Sybase IQ data. You can specify an operating system file or a raw partition of an I/O device. (The Installation and Configuration Guide for your platform describes the format for specifying a raw partition.) Sybase IQ automatically detects which type based on the path name you specify. If you use a relative path, the file is created relative to the directory of the catalog store (the .db file).

IQ SIZE The size in MB of either the raw partition or the operating system file you specify with the IQ PATH clause. For raw partitions, you should always take the default by not specifying IQ SIZE, which allows Sybase IQ to use the entire raw partition; if you specify a value for IQ SIZE, it must match the size of the I/O device or Sybase IQ returns an error. For operating system files, you can specify a value based on the size of your data, from the minimum in Table 1-5 up to a maximum of 128GB. The default for operating system files depends on IQ PAGE SIZE:

Table 1-5: Default and minimum sizes of IQ and temporary store files

IQ PAGE SIZE

IQ SIZE default

TEMPORARY SIZE default

Minimum explicit IQ SIZE

Minimum explicit TEMPORARY SIZE

65536

4096000

2048000

4MB

2MB

131072

8192000

4096000

8MB

4MB

262144

16384000

8192000

16MB

8MB

524288

32768000

16384000

32MB

16MB

IQ PAGE SIZE The page size in bytes for the Sybase IQ segment of the database (containing the IQ tables and indexes). The value must be a power of 2, from 65536 to 524288 bytes. The default is 131072 (128KB). Other values for the size are changed to the next larger size. The IQ page size determines the default I/O transfer block size and maximum data compression for your database.

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

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

BLOCK SIZE The I/O transfer block size in bytes for the Sybase IQ segment of the database. The value must be less than IQ PAGE SIZE, and must be a power of two between 4096 and 32768. Other values for the size are changed to the next larger size. The default value depends on the value of the IQ PAGE SIZE clause. For most applications, this default value is optimum. Before specifying a different value, see Chapter 4, “Managing System Resources” in the Performance and Tuning Guide.

IQ RESERVE Specifies the size in megabytes of space to reserve for the main IQ store (IQ_SYSTEM_MAIN dbspace), so that the dbfile can be increased in size in the future. The sizeMB parameter can be any number greater than 0. The reserve cannot be changed after the dbspace is created.

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.

TEMPORARY RESERVE clause Specifies the size in megabytes of space to reserve for the temporary IQ store (IQ_SYSTEM_TEMP dbspace), so that the dbfile can be increased in size in the future. The sizeMB parameter can be any number greater than 0. The reserve cannot be changed after the dbspace is created.

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.

NoteReserve and mode for temporary dbspaces are lost if the database is restored from a backup.

MESSAGE PATH The path name of the segment containing the Sybase IQ messages trace file. You must specify an operating system file; the message file cannot be on a raw partition. If you use a relative path or omit the path, the message file is created relative to the directory of the .db file.

TEMPORARY PATH The path name of the temporary segment file containing the temporary tables generated by certain queries. You can specify an operating system file or a raw partition of an I/O device. (The Installation and Configuration Guide for your platform describes the format for specifying a raw partition.) Sybase IQ automatically detects which type based on the path name you specify. If you use a relative path or omit the path, the temporary file is created relative to the directory of the .db file.

TEMPORARY SIZE The size in MB of either the raw partition or the operating system file you specify with the TEMPORARY PATH clause. For raw partitions, you should always take the default by not specifying TEMPORARY SIZE, which allows Sybase IQ to use the entire raw partition. The default for operating system files is always one-half the value of IQ SIZE. If the IQ store is on a raw partition and the temporary store is an operating system file, the default TEMPORARY SIZE is half the size of the IQ store raw partition.


Side effects

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

CREATE DBSPACE statement

DROP DATABASE statement

“CREATE DATABASE statement” in SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (A-D)

Chapter 11, “International Languages and Character Sets” in the System Administration Guide: Volume 1