CREATE DATABASE (SQL)

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. The following example would create a database called FLINTSTONE, but would create a directory named Fred:

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

You can specify neither, either, or both WITH options: ENCRYPTED_DB or GLOBAL_JOURNAL_STATE. These options specify whether the database is encrypted and journaled, respectively.

If you specify both, they should be separated by a space, as follows: WITH ENCRYPTED_DB GLOBAL_JOURNAL_STATE=NO.

For both CREATE DATABASE and CREATE DATABASE FROM FILE, 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 already exists, InterSystems IRIS issues an SQLCODE -341 error.

The standard CREATE DATABASE option 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 an IRIS.DAT file, an 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 CREATE DATABASE FILE option creates one database in the mgr directory with the dbname name to store the database’s globals. This directory (named dbname) contains an IRIS.DAT file, an iris.lock file, and an empty stream folder. For example, on a Windows System, CREATE DATABASE FILE Example would create the following database files:

c:\InterSystems\IRIS\mgr\example containing IRIS.DAT, iris.lock, stream folder

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 in any form, 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. This option can only be used as you would use the standard CREATE DATABASE command, not the CREATE DATABASE FROM FILE command.

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