How to deal with the databases when it stops following its own master.

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s