Skip to main content

CREATE DATABASE (SQL)

Creates a database (namespace).

Synopsis

CREATE DATABASE dbname [ON DIRECTORY pathname]
  [WITH [ENCRYPTED_DB] [GLOBAL_JOURNAL_STATE [=] {YES | NO}] ]

Description

The CREATE DATABASE command creates a namespace and two associated databases. This allows you to create a namespace within SQL.

The specified dbname is the name of the created namespace and the directory that contains the corresponding database files. Namespace names are not case-sensitive. A dbname follows the naming conventions for an SQL identifier, with the following additional restrictions:

  • An underscore (_) character is not permitted as the first character of dbname (but may be used elsewhere within the name). The @, #, and $ characters are not permitted in dbname. Attempting to include these invalid characters in dbname generates an SQLCODE -343 error.

  • A hyphen (-) character is not permitted in dbname (hyphen is not a valid SQL identifier character). However, a namespace name created by other means can include a hyphen character.

  • A dbname cannot be longer than 63 characters; specifying a longer dbname generates an SQLCODE -400 fatal error with the appropriate %msg.

If the specified dbname namespace already exists, InterSystems IRIS issues an SQLCODE -341 error.

You can specify neither, either, or both WITH options: ENCRYPTED_DB and/or GLOBAL_JOURNAL_STATE. If you specify both, they are separated by a space, as follows: WITH ENCRYPTED_DB GLOBAL_JOURNAL_STATE=NO.

By default, CREATE DATABASE creates two databases in the mgr directory with the dbname name subdirectory containing two subdirectories, C (code) and D (data). Each of these subdirectories contains a IRIS.DAT file, a iris.lck file, and an empty stream folder. For example, on a Windows system, CREATE DATABASE Barney would create the namespace BARNEY and the following database files:

c:\InterSystems\IRIS\mgr\Barney\C containing IRIS.DAT, iris.lck, stream folder c:\InterSystems\IRIS\mgr\Barney\D containing IRIS.DAT, iris.lck, stream folder

The C (code) directory is used for the namespace routines database. The D (data) directory is used for the namespace globals database. To return the location of the mgr directory, use the %SYSTEM.Util.ManagerDirectory()Opens in a new tab method.

The optional ON DIRECTORY pathname clause allows you to specify a different location for the database files, rather than a directory with the same name as the namespace. For example:

CREATE DATABASE Flintstone ON DIRECTORY 'c:\InterSystems\IRIS\mgr\Fred'

If you specify a pathname that already exists, InterSystems IRIS issues an SQLCODE -341 error.

The CREATE DATABASE command is a privileged operation. Prior to using CREATE DATABASE, it is necessary to be logged in as a user with the %Admin_Manage resource. Failing to do so results in an SQLCODE -99 error (Privilege Violation).

Use the $SYSTEM.Security.Login()Opens in a new tab method to assign a user with appropriate privileges:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, see %SYSTEM.SecurityOpens in a new tab.

You can also create a namespace from the Management Portal. Select System Administration, Configuration, System Configuration, Namespaces to list the existing namespaces. At the top of this table of existing namespaces you can click Create New Namespace.

The maximum number of namespaces on a single InterSystems IRIS instance is 2048.

Arguments

dbname

The name of the database (namespace) to be created.

pathname

An optional argument that denotes the root pathname location for the databases, specified as a quoted string. The C and D directories are created as subdirectories of this root path. The default is to create the database in the mgr directory.

WITH ENCRYPTED_DB

An optional argument that specifies whether or not the database is encrypted. The default is not encrypted.

WITH GLOBAL_JOURNAL_STATE

An optional argument that specifies whether or not the database is journaled. YES specifies that the database is journaled (which is recommended). NO specifies that the database is not journaled. The equal sign (=) is optional. The default is not journaled.

See Also

FeedbackOpens in a new tab