Creates a database consisting of several operating system files.
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 ]
'file-name'
{ 4096 | 8192 | 16384 | 32768 }
{ 65536 | 131072 | 262144 | 524288 }
{ 4096 | 8192 | 16384 | 32768 }
string
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 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
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'
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 Adaptive Server 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:
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 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, 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 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:\\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 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:
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.
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 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.
Multiplex databases have a shared IQ Store, where they share all dbspaces, and a local IQ Store. The local IQ Store consists of dbspaces that are managed by only one query server and are not visible to any other query server. To create multiplex databases, use the Create Database and Create Query Server wizards in Sybase Central. See Chapter 5, “Working with Database Objects” in the Sybase IQ System Administration Guide for more information.
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.
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 file.
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 sensitive for databases created with CASE RESPECT. Password case sensitivity follows data sensitivity unless you specify the PASSWORD CASE clause of CREATE DATABASE.
When a database is created with CASE IGNORE, queries might return data in either uppercase or lowercase, depending on the type of index the optimizer chose to use. You can return all uppercase data in such a situation by using this command:
SET TEMPORARY OPTION AGGREGATION_PREFERENCE=-2
Alternatively, you can use the LOWER or UPPER functions on columns to display the column values in lowercase or uppercase.
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.
All databases are created with at least one user ID:
DBA
and password:
SQL
If you create a database requiring case-sensitive comparisons, the password must be entered in uppercase, unless you specify PASSWORD CASE IGNORE. The user ID is unaffected by the CASE RESPECT setting.
PAGE SIZE The page size for the Anywhere segment of the database (containing the catalog tables) can be 4096, 8192, 16384, or 32768 bytes, with 4096 being the default. Other values for the size are changed to the next larger size. Normally, you should use the default, 4096 (4KB). Large databases might see performance benefits from a page size larger than this default. 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.
COLLATION The collation sequence used for all string comparisons in the database. 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’ ).
For a list of available collation sequences, see “CP874toUTF8 utility” in Chapter 3, “Database Administration Utilities” in the Sybase IQ Utility Guide.
Before creating a database with a nondefault collation, or a custom collating sequence, see Chapter 11, “International Languages and Character Sets” in the Sybase IQ System Administration Guide.
ENCRYPTED Encryption makes the data stored in your physical database file unreadable. There are two levels of encryption:
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 be specified only during database creation. (To introduce encryption to an existing database requires a complete unload, database recreation, and reload 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 uppercase and lowercase, and includes numbers, letters and special characters.
You 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.
WARNING! Protect your 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.
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 '
CREATE DATABASE no longer supports BLANK PADDING OFF for new databases. This change has no effect on existing databases. You can test the state of existing databases using the BlankPadding database property:
select db_property ( ‘BlankPadding’ )
Sybase recommends that you change any existing columns affected by BLANK PADDING OFF, to ensure correct join results. Recreate join columns as CHAR data type, rather than VARCHAR. CHAR columns are always blank padded.
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. Platforms that support JAVA ON include the file “libdbjava7”, with a platform-specific suffix, in the /lib directory.
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.
PASSWORD CASE You can specify whether passwords are case sensitive in the database. The case sensitivity of passwords need not be the same as the database's case-sensitivity setting for string comparisons. If you do not specify the case sensitivity of passwords, passwords follow the case sensitivity of the database, which defaults to CASE RESPECT. Extended characters used in passwords (that is, characters above the first 128 in the code page) are case sensitive, regardless of the password case-sensitivity setting.
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 Sybase IQ 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).
See Chapter 8, “Physical Limitations” for an important note about initializing raw devices on Sun Solaris.
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, 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 6-5 up to a maximum of 128GB. The default for operating system files depends on IQ PAGE SIZE:
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:
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.
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 he 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 5, “Managing System Resources” in the Sybase IQ 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 dbspace 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 dbspace 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.
Reserve 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 Sybase IQ 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, 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.
Several operating system files are created.
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.