topic Re: ksh script to check db2 database connectivity in Operating System - HP-UX

ksh script to check db2 database connectivity

Alain Tesserot — Fri, 03 Jun 2005 06:43:53 GMT

I have a db2 database that all the sudden stops responding evey few days. How do I write a ksh script to check connection if successful then exit otherwise send email to dba.

Re: ksh script to check db2 database connectivity

harry d brown jr — Fri, 03 Jun 2005 06:48:48 GMT

You don't!!!!

You write it in PERL using db2 modules:

http://search.cpan.org/search?query=db2&mode=all

live free or die
harry d brown jr

Re: ksh script to check db2 database connectivity

Alain Tesserot — Fri, 03 Jun 2005 18:39:54 GMT

Although I know and prefer perl it's not an option to install it on this production server. My only option is the ksh.

Re: ksh script to check db2 database connectivity

Doug O'Leary — Sat, 04 Jun 2005 12:46:48 GMT

Hey;

I don't know anything about db2; however, the model that I've used for oracle should be workable for db2 as well. Effectively, you call sqlplus with a here document to query a known table. If it shows up, everything's good. If not, send the email. For instance:

---------------------------
#!/bin/ksh

. $(oracle_environment_file}

( sqlplus /nolog << eof
connect 1/2 as sysdba
select file_name
from dba_data_files
order by file_name
/
eof
) > /tmp/dba_data_files.$$

lines=$(wc -l /tmp/dba_data_files.$$ | awk '{print $1}')
if [ ${lines} -lt 10 ]
then
### send email message to pager..
fi
--------------------------

Please note that was written on the fly in a web browser, not copied from a functioning script, so it may need some tweaking even on an oracle database.

Something like that, though, should work for db2 as well.

HTH;

Doug

Re: ksh script to check db2 database connectivity

Sandman! — Fri, 10 Jun 2005 18:16:19 GMT

Alain,

Pasted below is a ksh script that does what you're looking for.

=============================================
#!/bin/ksh
#
db2 <connect to
list tables for all
disconnect
quit
EOT
#
if [ $? -ne 0 ]; then
mailx -s "DB2 down!!!" your_email_address
exit 1
else
exit 0
fi
#
=============================================

best of luck!!!

Re: ksh script to check db2 database connectivity

Alain Tesserot — Sat, 11 Jun 2005 11:39:18 GMT

Thanks Sandman
I like you're script, it's clean, simple and best of all it works.

Thanks all for you're help. This forum is great.

Re: ksh script to check db2 database connectivity

Alain Tesserot — Sat, 11 Jun 2005 11:40:14 GMT

Thanks All