Operation fails because database is currently in use

Some database operations such as dropping a database, terminating a database connection, and creating a backup copy of a database require the associated database to be inactive and the database can have no applications connected to it. When you attempt such an operation with an active database that has applications connected to it, the operation fails and an error with SQL code -1035N might be returned.

Symptoms

When you attempt certain database operations such as dropping a database, terminating a database connection, and creating a backup copy of a database, the operation fails and an error stating that the database is currently in use might be returned.

Causes

This SQL1035N error message might be returned in one of the following scenarios:
  1. There are open connections to the database preventing the attempted operation from succeeding. This can occur in the following situations:
    • Exclusive use was requested, but the database is already in use as a shared database by another user (in the same process).
    • Exclusive use was requested, but the database is already in use as an exclusive database. This means that two different processes are trying to access the same database.
    • The maximum number of connections to the database has been reached.
    • The database is being used by another user on another system.
  2. The database has been activated explicitly, preventing the operation from succeeding.
  3. The database is active because it is in the WRITE SUSPEND state.

Resolving the problem

  1. Take one of the following actions:
    • Resubmit the command later when the database is not in use.
    • Change the authorization to match the current user or wait until the database is not in use.
    • Wait until the database is not in exclusive use.
    • Wait until other users on another system have disconnected from the database.
    • Issue the QUIESCE DATABASE DEFER WITH TIMEOUT <minutes> command and a CONNECT RESET command to free connections to the database and prevent application users from reconnecting. The DEFER option will wait for applications until they commit the current unit of work instead of rolling back all running transactions.
    • Issue the LIST APPLICATIONS command to list connections to the database. Issue a FORCE APPLICATION ALL command to free connections to the database.
      Note: The FORCE APPLICATION ALL command is an asynchronous command which might return as successful even though it is still continuing to clean up connections. A varying interval of time might be required for the command to complete.
  2. Deactivate the database using the DEACTIVATE DATABASE <database-alias> command.
  3. Issue a SET WRITE RESUME FOR DATABASE command to resume write operations for the database.
  4. Restart the member where the offline operation was taking place by issuing the following command:
    db2start member <member-number>
  5. Reissue the operation that had initially failed.