DataBase Testing, Databases, DB2, Oracle, Postgres

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
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}'`
cmd="db2 force application( ${SESSION} )"
echo $cmd
echo "Killing session ${SESSION}"

./db2 drop database $dbname
./db2 create database $dbname pagesize 32768
./db2 connect to $dbname
./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

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s