Database: creating a new database

Before creating a database display the list of devices available. Take a look at device names, sizes and options. When database is being created nothing should happen incidentally.

To create a database use command like this one:

CREATE DATABASE ALFA
ON AlfaDataDev = '50M'
LOG ON AlfaLogDev

This command will create a database named ALFA and it will reside on two devices: AlfaDataDev and AlfaLogDev. Not all device space needs to be consumed by the database. As it is shown from the first device only 50 MB was taken.  In the case of the second device all space will be used by the database.
Why have we used two devices? What the syntax "ON" and "LOG ON" mean?

Each database consists of data part and log part. Data part is the space where the data is finally stored. But the process of storing the data is not so easy as just simple write to the disk! Data is sent to the database in transactions and those transactions need to be saved first on the disk on the log device. Moreover - after saving a transaction on the disk in the log, the transaction finishes and the client is informed about successful finishing of data modification. As you see till now, the data file was even not modified. The current data may still not be there! But no worries. If the current value of the record would be needed, than it can be taken from the cache, as it is still there. From time to time also additional process takes all such transactions from transaction log and updates the data file with correct values. Why have someone decided to do it that way? There are two reasons:

• Performance - writing the data immedietaly to the data file would make transactions slower as we first would need to find the correct page on the disk and next update it
• Durability of transactions - even in case of the crush on the server we can recover the database basing on data stored in transaction logs, One could even say that transaction log is more important than the database itself, as having a backup and log file, database can be restored in such a way that no one transaction will be lost!

However we could create the database and log on the same device, but this is not recommended. If you like short commands you could shorten the command just to:

CREATE DATABASE ALFA

but this would create the database on a default device what also not recommended.

The output of the first create database command may look like this:

CREATE DATABASE: allocating 12800 logical pages (50.0 megabytes) on disk 'AlfaDataDev' (12800 logical pages requested).
CREATE DATABASE: allocating 1280 logical pages (5.0 megabytes) on disk 'AlfaLogDev' (1536 logical pages requested).
Warning: The database 'ALFA' is using an unsafe virtual device 'AlfaDataDev'. The recovery of this database can not be guaranteed.
Database 'ALFA' is now online.
Execution time: 0.164 seconds

Create database command may have much more parameters (full help on: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1600/doc/html/san1393050910896.html):

create [inmemory] [temporary] database database_name
[use database_name as template]
[on {default | database_device} [= size] 
[, database_device [= size]]...] 
[log on database_device [= size] 
[, database_device [= size]]...]
[with {dbid = number, default_location = "pathname", override}]
| [[,]durability = { no_recovery
| at_shutdown
| full} ]
[, [no] async_init]
[ [,] compression = {none | row | page}]
[ [,] lob_compression = {compression_level | off}]
[ [,] inrow_lob_length = value ] }...
[for {load | proxy_update}]
    [ encrypt with key_name]

This is the meaning of the most important of them:

• inmemory - database will be not disk based but memory based (more details shortly)
• temporary - database is being used as a temporary database
• override - allows to perform log dumps in case log and data would be stored on the same device (by default is such a case this is not allowed)
• durability - that option determines if transactions from transaction log will be sent to the database (and when). Value full means that every transaction needs to be saved in the database. This is a default setting. Value at_shutdown means that such synchronization should be done only when server is shutdown in proper manner. If there is a crush on the system, than the data will be lost. The last option is no_recovery and it means that after database server restart, data will be always lost, ans it is not important if this is a graceful or unexpected shutdown.
• use [database_name] as template - allows to use another database as a template. By default model database is used as a template. Note that this option could be combined with no_recovery value of durability option
• default location and proxy_update - allows to create a database as a proxy of another remote database. Tables will be physically stored on remote server, but for a local user will be visible as local tables in local database. This requires feature named Component Integration Services (CIS)
• [no]asyncinit - when database is created engine needs initialize it's internal structures in the database. This can be done in synchronous mode, where database cannot be used until initialization finishes or in asynchronous mode, where database can be used immediately but initialization still runs.
• compression, lob_compression - determines the way compression should be done if at all. Compression can be se to none,  what means no compression will be done, row compression, what means each record can be compressed separately or page compression, what means that all rows saved on one page can be compressed as a whole. Lob compression sets level of compression applied to LOBs
• inrow_lob_length - determines the size of a LOB that can be stored within a record (on the page). A natural limit of this value is the logical size of the page. All LOB objects greater than that value will be stored outside the row. By default this value equals 0, meaning all LOB data needs to be stored outside the record structure
• for load - creates the database but does not initialize it. This causes the database can be only used for dump being restored. This option is very useful as it speeds up restore process. During the restore all internal database structures created during the initialization phase will be destroyed, so all the time spent on initialization would be lost.