Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Chapter 31 SQL Command DROP DATABASE

Delete the database (namespace).

The outline

DROP DATABASE dbname [RETAIN_FILES]
Copy the code

parameter

  • dbname– The name of the database (namespace) to be deleted.
  • RETAIN_FILES– Optional – If specified, the physical database file will not be deleted (IRIS.DATFile). By default, delete.datFiles as well as namespaces and other database entities.

describe

The DROP DATABASE command drops the namespace and its associated DATABASE.

The specified dbname is the name of the namespace and directory containing the corresponding database files. Specify dbname as the identifier. Namespace names are case insensitive. If the specified DBNAME namespace does not exist, IRIS will issue a SQLcode-340 error.

The DROP DATABASE command is a privileged operation. Before using DROP DATABASE, you must log in as the user of the %Admin_Manage resource. The user must also have READ permission for the resources defined for routines and global databases. Failure to do so will result in a SQLCODE -99 error (permission conflict).

Assign the appropriate permissions to the user using the $system.security.login () method:

   DO $SYSTEM.Security.Login("_SYSTEM"."SYS")
   &sql(      )
Copy the code

You must have %Service_Login: Use permission to invoke the $system.security.login method.

DROP DATABASE cannot be used to DROP system namespaces, regardless of permissions. Attempting to do so results in a SQLCODE-342 error.

DROP DATABASE cannot be used to DROP namespaces that are currently in use or connected to. Attempting to do so results in a SQLCODE-344 error.

You can also delete namespaces using the management portal. Select System Administration, Configuration, System Configuration, and Namespaces to list existing Namespaces. Click the Delete button for the namespace you want to delete.

RETAIN_FILES

If this option is specified, the physical file structure is preserved; Deletes a database and its associated namespace. After this operation, subsequent attempts to use DBNAME will result in the following:

  • DROP DATABASEDon’t takeRETAIN_FILESUnable to delete this physical file structure. On the contrary, it leads toSQLCODE-340Error (database not found).
  • DROP DATABASE WITH RETAIN_FILESCan also lead toSQLCODE-340Error (database not found).
  • CREATE DATABASEUnable to create a new database with the same name. On the contrary, it leads toSQLCODE-341Error (cannot create database file for database).
  • Attempting to use this namespace results in<NAMESPACE>Error.

Server initialization and disconnection code

Server initialization code and the server code can be disconnected by $SYSTEM. SQL. Util. SetOption (” ServerInitCode.” Value) and $SYSTEM. SQL. Util. SetOption (” ServerDisconnectCode “, value) method is assigned to a namespace. You can use the corresponding $SYSTEM. SQL. Util. GetOption () method options to determine the current value.

Dropping the namespace using DROP DATABASE or another interface deletes these Server Init Code and Server Disconnect Code values. Therefore, deleting and recreating the namespace requires respecifying these values.

The sample

CREATE DATABASE DocTestDB ON DIRECTORY 'c:\InterSystems\IRIS142\mgr\DocTestDB'
Copy the code
DROP DATABASE DocTestDB RETAIN_FILES
Copy the code