I am writing this blog after so long as has been busy eith 2 of my financial clients. One of them , has lots of databases under one instance, I cannot force all the applications or run db2stop force as that would stop all the databases. Cutting paste so many application handlers annoys me, so thought of writing a SQL , which came out to be very short and simple. Here is the code for that :
case when ROW_NUMBER() OVER() = 1 then ‘force applications(‘||APPLICATION_HANDLE||’,’
when ROW_NUMBER() OVER() > 1 and ROW_NUMBER() OVER() < AS_TOT_COUNT – 0 then APPLICATION_HANDLE ||’,’
when ROW_NUMBER() OVER() = AS_TOT_COUNT then APPLICATION_HANDLE|| ‘);’
from sysibmadm.MON_CURRENT_UOW ,
(select count(*) AS AS_TOT_COUNT from sysibmadm.MON_CURRENT_UOW) cnt
order by ROW_NUMBER() OVER()
Boom!!! it generates force applications for all active connections.
TIP OF THE WEEK: Before restoring the database , after deactivatiing the database delete all the stale logs from active log dierctory, mirror directory and overflow log path.