Initialization utility (dbinit)

Creates a new database.

Syntax
dbinit [ options ] new-database-file
Option Description
@data

Reads in options from the specified environment variable or configuration file. See Using configuration files.

If you want to protect passwords or other information in the configuration file, you can use the File Hiding utility to obfuscate the contents of the configuration file. See File Hiding utility (dbfhide).

-a

Causes string comparisons to respect accent differences between letters (for example, e is less than é if the UCA (Unicode Collation Algorithm) is used for either CHAR or NCHAR data types (see -z and -zn). With the exception of Japanese databases created with a UCA collation, by default, accents are ignored (meaning e is equal to é). If all base letters (letters with accents and case removed) are otherwise equal, then accents are compared from left to right.

The default accent sensitivity of a UCA collation when creating a Japanese database is sensitive. That is, accents are respected. See Unicode Collation Algorithm (UCA).

-af

Causes string comparisons to respect accent differences between letters (for example, e is less than é) if the UCA is used for either CHAR or NCHAR data types (see -z and -zn below). By default, accents are ignored (meaning e is equal to é). If all base letters (letters with accents removed) are otherwise equal, then accents are compared from right to left, consistent with the rules of the French language.

For more information, see Unicode Collation Algorithm (UCA).

-b

Blank pads the database.

SQL Anywhere compares all strings as if they are varying length and stored using the VARCHAR domain. This includes string comparisons involving fixed length CHAR or NCHAR columns. In addition, SQL Anywhere never trims or pads values with trailing blanks when the values are stored in the database.

By default, SQL Anywhere treats blanks as significant characters. So, the value 'a ' (the character 'a' followed by a blank) is not equivalent to the single-character string 'a'. Inequality comparisons also treat a blank as any other character in the collation.

If blank padding is enabled (the dbinit -b option), the semantics of string comparisons more closely follow the ANSI/ISO SQL standard. With blank-padding enabled, SQL Anywhere ignores trailing blanks in any comparison.

In the example above, an equality comparison of 'a ' to 'a' in a blank-padded database returns TRUE. With a blank-padded database, fixed-length string values are padded with blanks when they are fetched by an application. The ansi_blanks connection option controls whether the application receives a string truncation warning on such an assignment. See ansi_blanks option [compatibility].

-c

Considers all values case sensitive in comparisons and string operations. Identifiers in the database are case insensitive, even in case sensitive databases.

With the exception of Japanese databases created with a UCA collation, the default behavior is that all comparisons are case insensitive. The default case sensitivity of a UCA collation when creating a Japanese database is sensitive.

Databases used as QAnywhere server stores should be case insensitive.

This option is provided for compatibility with the ISO/ANSI SQL standard.

-dba [ DBA-user ][ ,pwd ]

Specifies the DBA user ID and password. If you specify a new name for the DBA user for the database, you can no longer connect to the database as the user DBA. You can also specify a different password for the DBA database user. If you do not specify a password, the default password sql is used. If you do not specify this option, the default user ID DBA with password sql is created.

Either of the following commands creates a database with a DBA user named testuser with the default password sql:

dbinit -dba testuser mydb.db

dbinit -dba testuser, mydb.db

The following command uses the default user ID DBA with password mypwd:

dbinit -dba ,mypwd mydb.db

The following command changes the DBA user to user1 with password mypwd:

dbinit -dba user1,mypwd mydb.db

It is recommended that the password be composed of 7-bit ASCII characters as other characters may not work correctly if the server cannot convert from the client's character set to UTF-8.

-dbs size[ k | m | g | p ]

Pre-allocates space for the database. Pre-allocating space for the database helps reduce the risk of running out of space on the drive the database is located on. As well, it can help improve performance by increasing the amount of data that can be stored in the database before the database server needs to grow the database, which can be a time-consuming operation.

By default, the size is in bytes. You can use k, m, g, or p to specify units of kilobytes, megabytes, or gigabytes, or pages, respectively.

-ea algorithm

Specifies the encryption algorithm used for database or table encryption (-et). Specify -ea simple for simple encryption (do not specify -ek or -ep). Simple encryption is equivalent to obfuscation and is intended only to keep data hidden in the event of casual direct access of the database file, to make it more difficult for someone to decipher the data in your database using a disk utility to look at the file.

For greater security, specify AES or AES256 for 128-bit or 256-bit strong encryption, respectively. Specify AES_FIPS or AES256_FIPS for 128-bit or 256-bit FIPS-approved strong encryption, respectively. For strong encryption, you must also specify the -ek or -ep option. For more information about strong encryption, see Strong encryption.

To create a database that is not encrypted, specify -ea none, or do not include the -ea option (and do not specify -et, -ep, or -ek).

If you do not specify the -ea option, the default behavior is as follows:

  • -ea none, if -ek, -ep, or -et is not specified
  • -ea AES, if -ek or -ep is specified (with or without -et)
  • -ea simple, if -et is used without -ek or -ep

Algorithm names are case insensitive.

On Windows Mobile, the AES_FIPS and AES256_FIPS algorithms are only supported with ARM processors.

The following command creates a strongly encrypted database and specifies the encryption key and algorithm.

dbinit -ek "0kZ2o56AK#" -ea AES_FIPS "myencrypteddb.db"

File compression utilities cannot compress encrypted database files as much as unencrypted ones.

Separately licensed component required

ECC encryption and FIPS-certified encryption require a separate license. All strong encryption technologies are subject to export regulations.

See Separately licensed components.

-ek key

Specifies that you want to create a strongly encrypted database by specifying an encryption key directly in the command. The -ek option is used with an AES algorithm, optionally specified using the -ea option. If you specify the -ek option without specifying the -ea option, AES is used by default.

When specified with -et, the database is not encrypted. Instead, table encryption is enabled. See Table encryption.

Caution

For strongly encrypted databases, be sure to store a copy of the key in a safe location. If you lose the encryption key there is no way to access the data, even with the assistance of technical support. The database must be discarded and you must create a new database.

-ep

Specifies that you want to create a strongly encrypted database by inputting the encryption key in a window. This provides an extra measure of security by never allowing the encryption key to be seen in clear text.

You must input the encryption key twice to confirm that it was entered correctly. If the keys don't match, the initialization fails.

When specified with -et, the database is not encrypted. Instead, table encryption is enabled.

For more information, see Strong encryption.

-et

Enables table encryption using the encryption algorithm (and key) specified for the -ea option. Use this option when you want to create encrypted tables instead of encrypting the entire database. If you specify -et with -ek or -ep, but not -ea, the AES algorithm is used by default. When you specify only -et, simple encryption is used.

Enabling table encryption does not mean your tables are encrypted. You must encrypt tables individually, after database creation. See Encrypting a table.

When table encryption is enabled, table pages for the encrypted table, associated index pages, and temporary file pages are encrypted, and the transaction log pages that contain transactions on encrypted tables.

The following example creates the database new.db with strong encryption enabled for tables using the key abc, and the AES_FIPS encryption algorithm:

dbinit -et -ek abc -ea AES_FIPS new.db
-i

Excludes jConnect system objects from the database. If you want to use the jConnect JDBC driver to access system catalog information, you need to install jConnect catalog support (it is installed by default). When you specify this option you can still use JDBC, as long as you do not access system information. If you want, you can add jConnect support at a later time using Sybase Central or the ALTER DATABASE statement.

For more information, see Installing jConnect system objects into a database.

If you are creating a database for use on Windows Mobile, see Using jConnect on Windows Mobile.

-k Does not create the SYSCOLUMNS and SYSINDEXES views. By default, database creation generates the views SYS.SYSCOLUMNS and SYS.SYSINDEXES for compatibility with system tables that were 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.
-l Lists the recommended collation sequences and then stops. No database is created. A list of available collation sequences is automatically presented in the Sybase Central Create Database Wizard.
-le

Lists the available character set encodings and then stops. No database is created. Each character set encoding is identified by one or more labels. These are strings that can be used to identify the encoding. Each line of text that appears lists the encoding label and alternate labels by which the encoding can be identified. These labels fall into one of several common categories: SA (the SQL Anywhere label), IANA (Internet Assigned Numbers Authority), MIME (Multipurpose Internet Mail Extensions), ICU (International Components for Unicode), JAVA, or ASE (Adaptive Server Enterprise).

If you want to view a list of character set encodings that includes the alternate labels, specify the -le+ option.

When the Initialization utility reports the character set encoding, it always reports the SQL Anywhere version of the label. For example, the following command reports the CHAR character set encoding windows-1250:

dbinit -ze cp1250 -z uca test.db
-m filename Creates a transaction log 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, SQL Anywhere does not use a transaction log mirror.
-n Creates a database without a transaction log. Creating a database without a transaction log saves disk space, but can result in poorer performance because each commit causes a checkpoint. Also, if your database becomes corrupted and you are not running with a transaction log, data is not recoverable. The transaction log is required for data replication and provides extra security for database information in case of media failure or system failure.
-o filename Writes output messages to the named file.
-p page-size

Specifies the page size for the database. The page size for a database can be (in bytes) 2048, 4096, 8192, 16384, or 32768, with 4096 being the default.

Large databases can benefit from a larger page size. For example, the number of I/O operations required to scan a table is generally lower, as a whole page is read in at a time. However, there are additional memory requirements for large page sizes. It is strongly recommended that you do performance testing (and testing in general) when choosing a page size. Then choose the smallest page size that gives satisfactory results. For most applications, 16 KB or 32 KB page sizes are not recommended. You should not use page sizes of 16 KB or 32 KB in production systems unless you can be sure that a large database server cache is always available, and only after you have investigated the tradeoffs of memory and disk space with its performance characteristics. If a large number of databases are going to be started on the same server, pick a reasonable page size.

For more information, see:

-q Runs in quiet mode—messages are not displayed.
-s

Adds checksums to database pages. Checksums are used to determine whether a database page has been modified on disk. When you create a database with checksums enabled, a checksum is calculated for each page just before it is written to disk. The next time the page is read from disk, the page's checksum is recalculated and compared to the checksum stored on the page. If the checksums are different, then the page has been modified or corrupted on disk, and an error occurs. Critical database pages are always checksummed by the database server, regardless of whether -s is specified.

Checksums are automatically enabled for databases running on Windows Mobile and storage devices such as removable drives to help provide early detection if the database becomes corrupt.

-t transaction-log-name Specifies the name of the transaction log file. The transaction log is a file where the database server logs all changes, made by all users, no matter what application is being used. The transaction log plays a key role in backup and recovery (see The transaction log), and in data replication. If the file name has no path, it is placed in the same directory as the database file. If you run dbinit without specifying -t or -n, a transaction log is created with the same file name as the database file, but with extension .log.
-z coll [ collation-tailoring-string ]

Specifies the collation sequence for the database. The collation sequence is used for sorting and comparing character data types (CHAR, VARCHAR, and LONG VARCHAR). The collation provides character comparison and ordering information for the encoding (character set) being used. It is important to choose your collation carefully. It cannot be changed after the database has been created without unloading and reloading the database. If the collation is not specified, SQL Anywhere chooses a collation based on the operating system language and character set. See:

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. For example:

dbinit -c -z uca(locale=es;case=LowerFirst) spanish2.db

See Collation tailoring options.

Case and accent settings specified in the collation-tailoring-string override case and accent options for dbinit ( -c, -a, and -af), in the event that you specify both.

Note

Databases initialized with collation tailoring options cannot be started by a pre-10.0.1 database server.

-ze encoding

Specifies the encoding for the collation. Most collations specified by -z dictate both the encoding (character set) and ordering. For those collations, -ze should not be specified.

If the collation specified by -z is UCA (Unicode Collation Algorithm), then -ze can specify UTF-8 or any single-byte encoding for CHAR data types. By default, SQL Anywhere uses UTF-8. Use -ze to specify a locale-specific encoding and get the benefits of the UCA for comparison and ordering.

-zn coll [ collation-tailoring-string ]

Specifies the collation sequence used for sorting and comparing of national character data types (NCHAR, NVARCHAR, and LONG NVARCHAR). The collation provides character ordering information for the UTF-8 encoding (character set) being used. Values are UCA (the default), or UTF8BIN which provides a binary ordering of all characters whose encoding is greater than 0x7E. If the dbicu11 and dbicudt11 DLLs are not installed, then the default NCHAR collation is UTF8BIN. For more information, see Choosing collations.

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. For example:

dbinit -c -zn UCA(case=LowerFirst) sens.db

See Collation tailoring options.

Case and accent settings specified in the collation-tailoring-string override case and accent options for dbinit ( -c, -a, and -af), in the event that you specify both.

Note

Databases initialized with collation tailoring options cannot be started by a pre-10.0.1 database server.

Remarks

Several database attributes are specified at initialization and cannot be changed later except by unloading, reinitializing, and rebuilding the entire database. These database attributes include:

For example, the database test.db can be created with 8192 byte pages as follows:

dbinit -p 8192 test.db

You cannot name a database utility_db. This name is reserved for the utility database. See Using the utility database.

When specifying collation tailoring options in the initialization command, you cannot specify quaternary for the punctuation sensitivity if the database is case or accent insensitive.

In addition, the choice of whether to use a transaction log and a transaction log mirror is made at initialization. This choice can be changed later using the Transaction Log utility or the ALTER DATABASE statement.

Separately licensed component required

ECC encryption and FIPS-certified encryption require a separate license. All strong encryption technologies are subject to export regulations.

See Separately licensed components.

You can also create a database in the following ways:

Note

When you are deploying applications, the personal database server (dbeng11) is required for creating databases using the dbinit utility. It is also required if you are creating databases from Sybase Central on the local computer when no other database servers are running.

Exit codes are 0 (success) or non-zero (failure). See Software component exit codes.

Example

The following command creates a case sensitive database, spanish.db, which uses the 1262spa collation for non-NCHAR data. For NCHAR data, the UCA collation is specified, with locale es, and sorting by lowercase first.

dbinit -c -z 1252spa -zn uca(locale=es;case=LowerFirst) spanish.db