CREATE DATABASE Statement

Creates a database consisting of several operating system files.

Quick Links:

Syntax

CREATE DATABASE <db-name>
   … [ [ TRANSACTION ] { LOG ON [ <log-file-name> ]
         [MIRROR <mirror-file-name> ] } ]
   … [ CASE { RESPECT | IGNORE } ]
   … [ PAGE SIZE catalog-page-size ]
   … [ COLLATION collation-label[( collation-tailoring-string ) ] ]
   … [ ENCRYPTED {algorithm-key-spec | OFF } ]
   … [ BLANK PADDING ON ]
   … [ JCONNECT { ON | 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> ]
   … [ DBA USER <userid> ]
   … [ DBA PASSWORD <password> ]
   … [ SYSTEM PROCEDURE AS DEFINER {ON | OFF} ]

catalog-page-size (bytes) - (back to Syntax)
   { 4096 | 8192 | 16384 | 32768 }

collation-label - (back to Syntax)
   <string>

collation-tailoring-string - (back to Syntax)
   <keyword=value>

algorithm-key-spec - (back to Syntax)
   ON 
   | [ ON ] KEY <key> [ ALGORITHM AES-algorithm ] 
   | [ ON ] ALGORITHM <AES-algorithm> KEY key 
   | [ ON ] ALGORITHM ‘SIMPLE’

AES-algorithm - (back to algorithm-key-spec)
   ‘AES’ | ‘AES256’ | ‘AES_FIPS’ | ‘AES256_FIPS’

key: - (back to algorithm-key-spec)
   <quoted string>

iq-page-size (bytes) - (back to Syntax)
   { 65536 | 131072 | 262144 | 524288 }

block-size (bytes) - (back to Syntax)
   { 4096 | 8192 | 16384 | 32768 }

Parameters

(back to top)

TRANSACTION LOG 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 an identical copy of a transaction log, usually maintained on a separate device, for greater protection of your data. By default, SAP 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). Tip: 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 always case-insensitive, regardless of the CASE specification. The default (RESPECT) is that all comparisons are case-sensitive. CASE RESPECT provides better performance than CASE IGNORE.

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

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, SAP 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’).

You can choose the collation from a list of supported collations. For SAP SQL Anywhere databases created on an SAP IQ server, the collation can also be the Unicode Collation Algorithm (UCA). If UCA is specified, also specify the ENCODING clause. SAP IQ does not support any of the UCA-based collations for SAP IQ databases. If a UCA-based collation is specified in the CREATE DATABASE statement for a database, the server returns the error UCA collation is not supported and database creation fails. A collation sequence cannot be changed after the database is created.

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.

Collation tailoring options for SAP IQ contains the supported keyword, allowed alternate forms, and allowed values for the collation tailoring option (<collation-tailoring-string>) for an SAP IQ database.

Table 1: Collation Tailoring Option for SAP 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, the value of respect depends on the collation itself. ignore ignore case differences between letters. UpperFirst always sort upper case first (Aa). LowerFirst always sort lowercase first (aA).

Note

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

ENCRYPTED
makes the data stored in your physical database file unreadable. Use the CREATE DATABASE ENCRYPTED keyword without the TABLE keyword to encrypt the entire database. Use the ENCRYPTED TABLE clause to enable only table encryption for SQL Anywhere tables. Table-level encryption is not supported for SAP 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 encryption: simple and strong.
- Simple encryption is equivalent to obfuscation. The data is unreadable, but someone with cryptographic expertise could decipher the data. For simple encryption, specify the CREATE DATABASE clause ENCRYPTED ON ALGORITHM ‘SIMPLE’, ENCRYPTED ALGORITHM ‘SIMPLE’, or specify the ENCRYPTED ON clause without specifying an algorithm or key.
- 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. For strong encryption, specify the CREATE DATABASE clause ENCRYPTED ON ALGORITHM with a 128-bit or 256-bit AES algorithm and use the KEY clause to specify an encryption key. You should choose a value for your key that is at least 16 characters long, contains a mix of uppercase and lowercase, and includes numbers, letters, and special characters.
  This encryption key is required each time you start the database.
You can specify encryption only during database creation. To introduce encryption to an existing database requires a complete unload, database re-creation, and reload of all data. If the ENCRYPTED clause is used but no algorithm is specified, the default is AES. By default, encryption is OFF.
Caution
Protect your encryption key! 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.

BLANK PADDING trailing blanks are ignored for comparison purposes (BLANK PADDING ON), and Embedded SQL programs pad strings that are fetched into character arrays. This option is provided for compatibility with the ISO/ANSI SQL standard. CREATE DATABASE no longer supports BLANK PADDING OFF.

JCONNECT to use the SAP jConnect for JDBC driver to access system catalog information, install jConnect support. Set JCONNECT to OFF 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 SAP IQ data. You can specify an operating system file or a raw partition of an I/O device. (The Installation and Configuration Guide guide for your platform describes the format for specifying a raw partition.) SAP 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).
If you omit the IQ PATH clause, specifying any of these options generates an error: IQ SIZE, IQ PAGE SIZE, BLOCK SIZE, MESSAGE PATH, TEMPORARY PATH, and TEMPORARY SIZE.

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 SAP IQ to use the entire raw partition; if you specify a value for IQ SIZE, the value must match the size of the I/O device or SAP IQ returns an error. For operating system files, you can specify a value from the minimum in the following table up to a maximum of 4TB.

The default size for an operating system file depends on IQ PAGE SIZE:

Table 2: 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 SAP 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 best performance, use these minimum 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 might 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 might grow to a total size of 8TB or greater.

BLOCK SIZE the I/O transfer block size, in bytes, for the SAP 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, the default value is optimum.

IQ RESERVE 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. You cannot change the reserve 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 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. You cannot change the reserve 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.
Note Reserve and mode for temporary dbspaces are lost if the database is restored from a backup.

MESSAGE PATH path name of the segment containing the SAP 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 SIZE size, in megabytes, of either the raw partition or the operating system file you specify with the TEMPORARY PATH clause. For raw partitions, always use the default by not specifying TEMPORARY SIZE, which allows SAP 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.

DBA USER user name for the default user account granted the SYS_AUTH_DBA_ROLE system role. If you do not specify this clause, SAP IQ creates a default DBA user ID.

DBA PASSWORD password for the default user account granted the SYS_AUTH_DBA_ROLE system role.

SYSTEM PROCEDURE AS DEFINER defines whether a privileged system procedure runs with the privileges of the invoker (the person executing the procedure) or the definer (the owner of the procedure). OFF (default), or not specified, means all privileged system procedures execute with the privileges of the invoker. Use sp_proc_priv() to identify the system privileges required to run a system procedure.
ON means that pre-16.0 privileged system procedures execute with the privileges of the definer. 16.0 or later privileged system procedures execute with the privileges of the invoker.

Examples

(back to top)

Example 1
this Windows example creates an SAP 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 131072
```
Example 2
this UNIX command creates an SAP 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
this Windows command creates an SAP 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 this UNIX example creates a strongly encrypted SAP IQ database using the AES encryption algorithm with the key “is!seCret.”

CREATE DATABASE 'marvin.db'
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

(back to top)

Creates a database with the supplied name and attributes. The IQ PATH clause is required for creating the SAP IQ database; otherwise, you create a standard SAP SQL Anywhere database.

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

<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 SAP SQL Anywhere database objects you add. If you do not include the .db extension, SAP IQ adds 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, which contains the IQ tables and indexes. You can specify a different file name with the IQ PATH clause. This initial dbspace contains the IQ store.
Caution
IQ_SYSTEM_MAIN is a special dbspace that contains all structures necessary for the database to open: the IQ db_identity blocks, the IQ checkpoint log, the IQ rollforward/rollback bitmaps of each committed transaction and each active checkpointed transaction, the incremental backup bitmaps, and the freelist root pages. IQ_SYSTEM_MAIN is always online when the database is open.

The administrator can allow user tables to be created in IQ_SYSTEM_MAIN, especially if these tables are small, important tables. However, it is more common that immediately after creating the database, the administrator creates a second main dbspace, revokes create privilege in dbspace IQ_SYSTEM_MAIN from all users, grants create privilege on the new main dbspace to selected users, and sets PUBLIC.default_dbspace to the new main dbspace.
<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.
<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, a database has a transaction log file (db-name.log), and might 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:\\SAP\\mydb.db'
LOG ON 'e:\\logdrive\\mydb.log'
JCONNECT OFF
IQ PATH 'c:\\SAP\\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 path names are recommended.

Caution

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-like operating 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:

Symbolic links to raw devices can have meaningful names, while the actual device name syntax can be obscure.
A symbolic name might eliminate problems restoring a database file that was moved to a new directory since it was backed up.

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

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

Specify a different extension for each file (for example, mydb.iq and mydb.iqtmp)
Specify a different file name (for example, mydb.iq and mytmp.iq)
Specify a different path name (for example, /iqfiles/main/iq and /iqfiles/temp/iq) or different raw partitions
Omit TEMPORARY PATH when you create the database. In this case, the temporary store is created in the same path as the catalog store, with the default name and extension dbname.iqtmp, where <dbname> is the database name.

Caution

To maintain database consistency on UNIX-like operating systems, you must specify file names that are links to different files. SAP IQ cannot detect the target where linked files point. Even if the file names in the command differ, make sure they do not point to the same operating system file.

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 SAP 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 this 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.

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 DATABASE 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 a problem only when the connection is to a database such as utility_db, which has a page size of 1024. This restriction may cause RESTORE DATABASE 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.

Side effects:

Automatic commit

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Database products—SAP ASE provides a CREATE DATABASE statement, but with different options.

Permissions

(back to top)

The permissions required to execute this statement are set using the -gu server command line option, as follows:

NONE No user can issue this statement.

DBA Requires the SERVER OPERATOR system privilege.

UTILITY_DB Only those users who can connect to the utility_db database can issue this statement.

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