During my experience in testing, I have worked on multiple databases, and I must say if you are working on database direct interaction then you must have encountered issues now and then over various areas.
One of the major issue is when you are dealing with databases having multiple tables and these tables are connected with each other with constraints like foreign keys, primary keys, triggers, stored procedures etc.
Other being, when a database server is used by various team members then you might see issue related to concurrent users. Today in this piece of write-up I will share how can we deal with multiple databases when multiple user is using one database server. In other words, how can we drop the connections in different databases, how can we make it happen via scripts like Shell scripts, etc.
Database name – Postgres
SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'YOUR_DATABASE'
AND procpid <> pg_backend_pid();
Database name – DB2
ps -ef | grep db2
kill -9 PID
Db2stop force
OR
Db2 force applications all
You can also apply Shell Script
cd ~/sqllib/bin
SESSIONS_TO_KILL=`db2 list application | grep -i "$dbname" | awk '{print $3}'`
echo $SESSIONS_TO_KILL
for SESSION in $SESSIONS_TO_KILL; do
cmd="db2 force application( ${SESSION} )"
echo $cmd
echo "Killing session ${SESSION}"
$cmd
done
./db2 drop database $dbname
./db2 create database $dbname pagesize 32768
./db2 connect to $dbname
./db2 CREATE TABLESPACE YOURDATABASE NOT LOGGED;
./db2 -vf ~/your-tables-db2.sql -t
echo "successfully created Schema for Database "$dbname
Database name – Oracle
ps -ef | grep oracleSID | grep LOCAL
kill -9 PID