3

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!

user1340582
  • 165
  • 1
  • 2
  • 5

4 Answers4

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, ...)
Community
  • 1
Ian Bjorhovde
  • 481
  • 2
  • 2
1

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.

Criveti Mihai
  • 151
  • 2
1

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/"
techraf
  • 4,163
  • 8
  • 27
  • 44
Kate YAN
  • 11
  • 1
0

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

ultimatum
  • 11
  • 2