Export variable, as a background process

Have a script where I need to wait for a process to run, but from that process I want to capture the row count.

Script I have so far (easier than explanation):

echo "Start"
export NUMROWS=`td "select * from dbc.database" 2> /dev/null | 
    grep "Query completed" | sed -e 's/.*Query completed. //g' -e 's/row.*found.*//g'` & 
WAITPID=$!
echo $NUMROWS
ps -ef | grep $WAITPID
wait $WAITPID
echo $NUMROWS

What I need is the export to run, in the background, the script to wait for that to run and then return the row count (this is the start of a script which will kill the WAITPID and processes associated if it runs for longer than a specified time.

Would be easy if I didn't need the export... ideas?

I could use temp files, but would rather not.

What's the problem with exporting the variable?

I can't, the "td" command connects to the database and returns the rows. Initially I had:

export NUMROWS=`td "select * from dbc.database" 2> /dev/null | 
    grep "Query completed" | sed -e 's/.*Query completed. //g' -e 's/row.*found.*//g'`

That works. What I need is to run that in the background as it can take minutes or hours to run the SQL, if after, say in our test, 30minutes, this process is still running I want to kill it. So need the & to run in background. But without writing the results to a file can't see any obvious way of doing it...

Slightly off topic. A shell environment variable is not really suitable to receive an open-ended report. It would be better as a file.

To reduce the size of the report have you considered using:

select count (*)

@methyl this is a perf test suite of scripts and this "test" is to simulate Teradata, and network performance to the ETL box.

So currently have a script to run X predefined queries for Y minutes. Queries are re-submitted after they complete until Y minutes is up. Any running at this point stay running and have to be manually forced off. So am trying to re-code this process.

The reports are then generated from the number of queries run, run time for each, a seperate report is done from the log tables in the database. All this is then put in a report database available to managers after any major changes.

Think the solution is going to be pipe the rows to awk, generate the print statement into a temp file, (export NUMROWS=402) and then run that file to get that figure. bit more hassle but easy to implement.

---------- Post updated at 08:23 AM ---------- Previous update was at 08:14 AM ----------

Crappy code but works:

/tmp/td "select * from dbc.databases" 2> /dev/null | grep "Query completed" | sed -e 's/.*Query completed. //g' -e 's/row.*found.*//g' | awk ' { print "export NUMROWS=" $0 } ' > tmp_rpt &
WAIT_PID=$!
 
while [ "`ps -ef | grep ${WAIT_PID} | grep -v grep`" != "" ]
do
    sleep 10
done
 
. ./tmp_rpt
rm ./tmp_rpt
 
echo "1: " $NUMROWS

So in my sleep 10 will have a if time = Y then kill the $WAIT_PID. Now to add this into the main script

What shell you're using? ksh93 and zsh support coprocesses.

This is with dtksh (/usr/dt/bin/dtksh)on Solaris:

$ unset v
$ sqlplus -s / as sysdba<<-! |&
  set feed off pages 0
  exec dbms_lock.sleep(5);
  select sysdate from dual;
!> > > >
[1]     20027
$ read -p v
$
[1] +  Done    sqlplus -s / as sysdba<<-! |& ;  set feed off pages 0;  exec dbms_lock.sleep(5);;  select sysdate from dual;;!
$ echo "$v"
24-AUG-10

---------- Post updated at 03:34 PM ---------- Previous update was at 03:32 PM ----------

Another option is to use expect as a wrapper, to set a timeout for the process.

Or we can use the basic shell "jobs" command. Obviously we would need to build a counter into the loop to allow us to stop after a given amount of time.

while [ "`jobs`" ]
do
        echo "still running"
        sleep 10
done

Tip. The "ps -ef" command sequence posted assumes unique matches for the "grep". You are very likely to get false matches.
Confining the search to one pid with "ps -fp<pid>" would be safer and quicker.
Actually for anything mission critical I would never rely on "ps -ef" because it occasionally misses processes (or does not reply at all) when the kernel is busy.

Thank(ed)s for the replies..

@radoulov
no expect, the other shells do exist will investigate - didn't realise we even had those installed lol

@methyl
Yeah ps -ef is not my favourite. In the real script I am using:

 while [ -d /proc/${WAIT_PID} ]

along with proctree to get the process I am after which I need to kill, in this case bteq for Teradata. Figure it must be better, and quicker than a ps -ef....