DROP DATABASE statement

Use the DROP DATABASE statement to delete an entire database, including all system catalog tables, objects, and data.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

^{(explicit id drodb001)
drodb001} DROP DATABASE [IF EXISTS] <Database Name>^[]

Usage

The DROP DATABASE statement is an extension to the ANSI/ISO standard, which does not provide syntax for the destruction of a database.

The following statement drops the stores_demo database:

DROP DATABASE IF EXISTS stores_demo;

You must have the DBA privilege or be user informix to run the DROP DATABASE statement successfully. Otherwise, the database server issues an error message and does not drop the database.

Restrictions on the DROP DATABASE statement

You cannot drop the current database or a database that is currently being used by another user. All the current users of the database must first execute the CLOSE DATABASE statement before DROP DATABASE can be successful.

You cannot drop a tenant database with the DROP DATABASE statement. You must drop a tenant database by running the admin() or task() SQL administration API function with the tenant drop argument.

The DROP DATABASE statement attempts to create an implicit connection to the database that you intend to drop. If a previous CONNECT statement has established an explicit connection to another database, and that connection is still your current connection, the DROP DATABASE statement fails with error -1811. In this case, you must first use the DISCONNECT statement to close the explicit connection before you can execute the DROP DATABASE statement.

If you include the optional IF EXISTS keywords, the database server takes no action (rather than returning an error to the application) if no database of the specified name is managed by the database server instance to which you are connected.

The DROP DATABASE statement cannot appear in a multistatement PREPARE statement, nor within an SPL routine.

In a DROP DATABASE operation, the database server acquires a lock on each table in the database and holds the locks until the entire operation is complete. Configure your database server with enough locks to accommodate this fact.

For example, if the database to be dropped has 2500 tables, but fewer than 2500 locks were configured for your database server, the DROP DATABASE statement fails. For more information on how to configure the number of locks available to the database server, see the discussion of the LOCKS configuration parameter in your Informix® Administrator's Reference.

In DB-Access, use the DROP DATABASE statement with caution. DB-Access does not prompt you to verify that you want to delete the entire database.

In ESQL/C, you can use an unqualified database name in a program or host variable, or you can specify the fully-qualified database@server format. For example, the following statement drops the stores_demo database of a database server called gibson95:

EXEC SQL DROP DATABASE stores_demo@gibson95;

If this statement executes successfully, the gibson95 database server instance continues to exist, but the stores_demo database of that database server no longer exists. For more information, see Database Name.

¹ See Database Name