Database process triggered from Unix Shell script is moving or not?

Hi Friends

Many times we doubt whether the Shell script which is designed to do some database activities is actually doing its work or not. Of course, this situation would have been faced many times by people who work in support based projects.

Let me give you a simple suggestion to check the database movement of a process (Assuming the database you use is Sybase).

Here we go...

Step 1 – First and foremost, Get the process id of the Shell script which triggers the database activity. Do you know how?

ps –ef  |grep <Shell script name>

Example:

$ps –ef |grep run_shell1.ksh

primuni 28763  6490  0 23:12:35 pts/1    0:00 grep run_shell1.ksh

primuni 17555 17549  0 23:06:03 ?        0:01 /bin/ksh /homedirectory/scripts/run_shell1.ksh

Step 2 – Now the process id 17555 definitely would have triggered the child process to perform the database activity. So you need to find the process which actually communicates with the database. Either you need to do it manually or use ptree command to get those details.

Here I am going to use ptree command to get the details.

$ptree 17555

2330  /usr/sbin/inetd -s

  17549 auto_remote

    17555 /bin/ksh /homedirectory/scripts/run_shell1.ksh

      10275 /homedirectory/bin/dumpit -S MY_SERVER -D my-dbase -U primuni -P Hell0123 -T in

Wow! now you got the process id which actually does the database stuff for you….

Step 3 – Use the process id and check the physical IO Movement in database. Do you know how to do that? No worries even if you don’t.

Here it is…

select physical_io  from master..sysprocesses where hostprocess in ('10275')

physical_io

457123

Run the query at regular intervals and make sure that you see some change in value in output. If it’s not moving for quite some time then your process is in trouble. So In this case you have to check for database block or lock etc…

Will meet you all soon with a new post!!!