ksh script to check db2 database connectivity

[Alain Tesserot]

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.

[harry d brown jr]

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

[Alain Tesserot]

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

[Doug O'Leary]

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

[Sandman! · Accepted solution]

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!!!

[Alain Tesserot]

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.

[Alain Tesserot]

Thanks All