Home / server / Questions / 615928
In Process
user1340582
Asked: 2014-07-29 01:16:10 +0800 CST

IBM DB2 9.7, force all connections of a specific database

  • 772

I have a DB2 9.7 LUW instance with several databases. I am trying to take backup of a specific database, but Data Studio client reports that there is still an active connection so it fails.

How can I force the database to remove all active connections to that specific database? Thanks!

db2

4 Answers

  1. You can't do it in a single command; you have to list the connections first:

    LIST APPLICATIONS FOR DB <databasename>

    Then, use all of the numeric application handles returned by the list above to issue the FORCE APPLICATION statement:

    FORCE APPLICATION (1, 2, 3, 4, ...)
    • 1

  2. I assume you are trying to take an OFFLINE backup. You can use db2 FORCE APPLICATION ALL. As listed in the manual:

    ALL - All applications will be disconnected from the database server.

    Though before you take the force route - I suggest taking a look at QUIESCE instead or properly stopping the application. Your backup process would look something like this:

    # Stop your application

su - db2inst1 # Or whatever instance you have
timestamp=$(date +%Y-%m-%d-%H-%M)
mkdir backup-mydb-$timestamp

# Connect to your database and check existing connections
db2 CONNECT TO MYDB;
db2 list applications for database MYDB show detail

# QUISCE the database
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
db2 CONNECT RESET

# Perform the backup
db2 BACKUP DATABASE MYDB TO "/home/db2i2/backup-mydb-$timestamp" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;

# UNQUISCE the database
db2 CONNECT TO MYDB
db2 UNQUIESCE DATABASE;
db2 CONNECT RESET;

# Test the backup
db2ckbkp -h backup-mydb-$timestamp/*

# Start your application

    You can also use db2top to monitor database connections and identify what applications you need to stop.

    • 1

  3. I use the following steps, and it works.

    db2 connect to TFBDW
db2 list application for database TFBDW
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
db2 "force application (39)"
db2 CONNECT RESET
db2 backup database TFBDW_U to "/tmp/dbbackup/"
    • 1

  4. Assuming it to be an OFFLINE backup, this can be done by either DEACTIVATE'ing the database or by QUISCE'ing the database.

    For DEACTIVATE'ing the database, follow the below steps:-

    • Confirm applications are down using LIST APPLICATIONS FOR
      DB_NAME       , and if found force them off using FORCE APPLICATIONS ALL
    • Deactivate the database using DEACTIVATE DB DB_NAME

    For QUISCE'ing the database, follow the below steps:-

    • CONNECT TO DBNAME

    • QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS

    • CONNECT RESET

    • 0