ASA SQL Reference
SQL Statements
Use this statement to create a database. The database is stored as an operating-system file.
CREATE DATABASE db-file-name
[ [ TRANSACTION ] { LOG OFF | LOG ON } [ log-file-name-string ]
[ MIRROR mirror-file-name-string ] ]
[ CASE { RESPECT | IGNORE } ]
[ PAGE SIZE page-size ]
[ COLLATION collation-label ]
[ ENCRYPTED { ON | OFF | key-spec } ]
[ BLANK PADDING { ON | OFF } ]
[ ASE [ COMPATIBLE ] ]
[ JAVA { ON | OFF | JDK { '1.1.8' | '1.3' } } ]
[ JCONNECT { ON | OFF } ]
]
page-size :
1024 | 2048 | 4096 | 8192 | 16384 | 32768
collation-label : string
key-spec:
[ ON ] KEY key [ ALGORITHM { 'AES' | 'MDSR' } ]
File name The file names ( db-file-name-string, log-file-name-string, mirror-file-name-string) are strings containing operating system file names. As literal strings, they must be enclosed in single quotes.
If you specify a path, any backslash characters (\) must be doubled if they are followed by an n or an x. Escaping them prevents them being interpreted as new line characters (\n) or as hexadecimal numbers (\x), according to the rules for strings in SQL.
It is safer to always escape the backslash character. For example,
CREATE DATABASE 'c:\\sybase\\my_db.db' LOG ON 'e:\\logdrive\\my_db.log'
If you specify no path, or a relative path, the database file is created relative to the working directory of the server. If you specify no path for a log file, the file is created in the same directory as the database file.
If you provide no file extension, a file is created with extension .db for databases, .log for the transaction log, or .mlg for the mirror log.
TRANSACTION LOG clause 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 backup and recovery (see The transaction log), and in data replication.
MIRROR clause 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, Adaptive Server Anywhere does not use a mirrored transaction log. If you do wish to use a transaction log mirror, this option allows you to provide a filename.
CASE clause For databases created with CASE RESPECT, all values are case sensitive in comparisons and string operations.
This option is provided for compatibility with the ISO/ANSI SQL standard. The default value for the option is CASE IGNORE; that is, all comparisons are case insensitive. If you create a case sensitive database, all passwords are case sensitive. Extended characters used in passwords are case sensitive regardless of the database sensitivity setting. User IDs and other identifiers in the database are case insensitive, even in case sensitive databases.
PAGE SIZE clause The page size for a database can be 1024, 2048, 4096, 8192, 16384, or 32768 bytes. The default page size is 2048 bytes. Large databases generally obtain performance benefits from a larger page size, but there can be additional overhead associated with large page sizes.
For more information, see Information utility options.
For example,
CREATE DATABASE 'c:\\sybase\\my_db.db' PAGE SIZE 4096
Page size limitThe page size cannot be larger than the page size used by 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 option. |
COLLATION clause The collation sequence used for all string comparisons in the database.
For more information on collation sequences, see International Languages and Character Sets.
ENCRYPTED clause 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. 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 want to start the database.
Using the ALGORITHM clause in conjunction with the ENCRYPTED and KEY clauses lets you specify the encryption algorithm. You can choose either AES or MDSR. If the ENCRYPTED clause is used but no algorithm is specified, the default is AES.
| Caution Protect your KEY! Be sure to store a copy of your key in a safe location. A lost KEY will result in a completely inaccessible database, from which there is no recovery. |
BLANK PADDING clause If you specify BLANK PADDING ON, trailing blanks are ignored in comparisons. For example, the two strings
'Smith' 'Smith '
would be treated as equal in a database created with BLANK PADDING ON.
This option is provided for compatibility with the ISO/ANSI SQL standard, which is to ignore trailing blanks in comparisons. The default is that blanks are significant for comparisons (BLANK PADDING OFF).
ASE COMPATIBLE clause Do not create the SYS.SYSCOLUMNS and SYS.SYSINDEXES views. By default, these views are created for compatibility with system tables available in Watcom SQL (versions 4 and earlier of this software). These views conflict with the Sybase Adaptive Server Enterprise compatibility views dbo.syscolumns and dbo.sysindexes.
JCONNECT clause If you wish to use the Sybase jConnect JDBC driver to access system catalog information, you need to install jConnect support. Specify JCONNECT OFF if you wish to exclude the jConnect system objects. You can still use JDBC, as long as you do not access system information.
JAVA clause The default behavior is JAVA OFF.
To use Java in your database, you must install entries for the Sybase runtime Java classes into the system tables. Specifying JAVA JDK '1.1.8'or JAVA JDK '1.3' explicitly installs entries for the named version of the JDK. For JDK 1.1.8 the classes are held java\1.1\classes.zip under your SQL Anywhere directory. For JDK 1.3, they are held in java\1.3\rt.jar. The default classes are the JDK 1.3 classes.
Java in the database is a separately licensable component. For more information, see Introduction to Java in the Database.
Creates a database file with the supplied name and attributes.
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.
Not supported on Windows CE.
An operating system file is created.
Encryption Key connection parameter [DBKEY]
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Adaptive Server Enterprise provides a CREATE DATABASE statement, but with different options.
The following statement creates a database file named mydb.db in the C:\ directory.
CREATE DATABASE 'C:\\mydb' TRANSACTION LOG ON CASE IGNORE PAGE SIZE 1024 COLLATION '437' ENCRYPTED OFF BLANK PADDING OFF JAVA JDK '1.3' JCONNECT OFF
The following statement creates a database with no Sybase runtime Java classes. All database operations will execute normally, except for those involving Java classes or objects.
CREATE DATABASE 'C:\\nojava' JAVA OFF