Reference Manual
CHAPTER 9. SQL Statements
To create a database. The database is an operating system file.
CREATE DATABASE db-file-name
... [
... [ [ TRANSACTION ] LOG OFF |
[ TRANSACTION ] LOG ON [ log-file-name ]
[ MIRROR mirror-file-name ]
]
... [ CASE { RESPECT | IGNORE } ]
... [ PAGE SIZE page-size ]
... [ COLLATION collation-label ]
... [ ENCRYPTED { ON | OFF } ]
... [ BLANK PADDING { ON | OFF } ]
... [ ASE [ COMPATIBLE ] ]
... [ JAVA { ON | OFF } ]
... [ JCONNECT { ON | OFF } ]
]
db-file-name | log-file-name | mirror-file-name :
'file-name'
page-size :
1024 | 2048 | 4096
collation-label: string
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.
Not supported on Windows CE.
An operating system file is created.
Creates a database file with the supplied name and attributes.
File name The file names ( db-file-name, log-file-name, mirror-file-name) 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. This prevents them being interpreted as a new line character (\n) or as a hexadecimal number (\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 or .log for the transaction log.
TRANSACTION LOG clause The transaction log is a file where the database server logs all changes made by all users no matter what application system is being used. The transaction log plays a key role in backup and recovery (see The transaction log), and in data replication. If the filename has no path, it is placed in the same directory as the database file.
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 this option, all values are considered to be case sensitive in comparisons and string operations.
This option is provided for compatibility with the ISO/ANSI SQL standard. The default is that all comparisons are case insensitive.
User ID and password |
PAGE SIZE clause The page size for a database can be 512, 1024, 2048 or 4096 bytes, with 1024 being the default. Other values for the size will be changed to the next larger size. Large databases usually benefit from a larger page size.
For example:
CREATE DATABASE 'c:\\sybase\\my_db.db' PAGE SIZE 4096
Page size limit |
COLLATION clause The collation sequence used for all string comparisons in the database.
For more information on custom collating sequences, see Database Collations and International Languages.
ENCRYPTED clause Encryption makes it more difficult for someone to decipher the data in your database by using a disk utility to look at the file. File compaction utilities are not able to compress encrypted database files as much as unencrypted ones.
BLANK PADDING clause If you specify blank padding, trailing blanks are ignored for comparison purposes, and Embedded SQL programs pad strings fetched into character arrays. For example, the two strings
'Smith' 'Smith '
would be treated as equal in a database created with trailing blanks ignored.
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.
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 If you wish to use Java in your database, you must install entries for the Sybase runtime Java classes into the system tables. By default, these entries are installed. You can specify JAVA OFF if you are sure you will not be using Java, to avoid installing these entries.
SQL/92 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 ON 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