Hi Experts,

How to kill all process of a particular database?

TIA

ratheeshknair, Assuming you are using LUW, try:

DB2 FORCE APPLICATION ALL

This should disconnect all applications from the database.

FORCE APPLICATION (APPLICATION_HANDLE)-->KILLS THAT PARTICULAR CONNECTION

FORCE APPLICATION ALL --> KILLS ALL CONNECTION IN THE SERVER(HOPE I AM RIGHT)

My requirement is to kill only applications related to a particular DB

TIA

This is from the COMMAND REFERENCE manual:

From what I read, this indicates it only disconnects from the database.

That is assuming that you only have one database. If there is more than one DB under the instance and they all have connections, then FORCE APPLICATIONS ALL will kill all the connections to all of the DBs.

This exact question was asked in this forum just a short while ago. Search the forum for the answer.

Andy

suppose if "db2inst1" is your db2 instance ID.

1) db2stop force
2) ipcs -ma | grep db2inst1
3) Is there something left?
4) If so - db2_kill or db2kill
5) Check 2).
6) If still something left - try to remove the segments with ipcrm -m
<segment ID>

Or use below scripts:

- kill all db2 processes

ps -ef | grep db2 | awk '{print "kill -9",$2}' > out.file
chmod 755 out.file
out.file - this will kill all db2 processes

- remove db2 shared memory

ipcs -a | grep db2 | awk '{print "ipcrm -"$1,$2}' > out.ipcs
chmod 755 out.ipcs
edit out.ipcs removing extra fields if necessary
out.ipcs (removes shared memory)

---------------
Eric Xu (许 昌)

If you have to kill only appls for one DB. Write a script that collects applid for appls that are connected to that db. Then using loop kill only those appls. Problem solved.

Question is why do you need to kill all connections? If it is in order to bring that db down. before you do the kill, you must revoke access. Other wise if you have a persistent applications they will make connections right after the kill command is done.

If any of you had bothered to look up the other post like I suggested, you would have found that you can do a QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS that will force the applications only on the DB desired.

Andy

Thanks ARWinner

Andy, will this work if 6 different apps are connected as same id as myself ??


I may need to get an array of app_ids to force them. I did not succeed to do that so far in a single statement.

DBFinder

This is from the manual:

[Text]
QUIESCE DATABASE results in all objects in the database being in the quiesced mode. Only the allowed user/group and sysadm, sysmaint, dbadm, or sysctrl will be able to access the database or its objects.

Authorization

One of the following:

For database level quiesce:

* sysadm
* dbadm

[/Text]

If you have 6 different applications that connect to the database and they have any of the authorities listed above, then they will not be disconnected.

Normal users should not have these authorities. It gives them too much access to the DB and to DB2.

Andy

Thanks,

So,
I do need to set up some sql to get list of app_ids and force these using a loop.

Thanks again.

That could be one way. How often do you anticipate doing this, and does it need to be automatic or just manually started?

Andy

Automatic,

Every night ( MirrorNightlyRestore ) job which is pure DOS batch file on Win 2k3 Server currently DB2V9.5 FP 4, last month it was DB2 V8.1.5.

So far it failed only once in last 2 months because of an application that was not disconnected.

I assume you want to perform an offline backup since you want to force the users off. If that is the case, then you will have to do a "LIST APPLICATIONS" command and capture the IDs of the user on the DB in question and force them off. I cannot help you with the DOS scripting to do this.

Can you do the "MirrorNightlyRestore" using an online backup? It should be easier to script that.

Andy