Can someone tell me how to pass the db2 sql errors out to
a ksh script? I'm new to db2 and we have several scripts that
run at night - and where the unix script processes ok we are
getting some sql errors that we'd like to trap for -

Thanks

Here's a quick example of an easy way to do it:


SQLCODE=0

SQLCODE=`db2 -ec +o "update db cfg for $DBNAME using dbheap 11370"`;

if [ $SQLCODE -ne 0 ]
then
echo " "
echo "Error: Failed to Update Db Cfg for $DBNAME"
echo "Error: SQLCODE = $SQLCODE"
exit 1

Dear Sir,

I followed above script to prepare my ksh script with minor change, it always said the line "if [ $SQLCODE -ne 0 ]" is not vallid, and the return status = 0.

My ksh script as below.
db2 "set serveroutput on"
SQLCODE=0
SQLCODE=`db2 -td/ +o -f abc.sql`;
if [ $SQLCODE -eq 0 ]
then
echo "sucess"
exit 0
else
echo "failed"
exit 1
fi
-------------------------------
In abc.sql,
BEGIN
declare v_count integer;

open cursor
loop
fetch xx into xx;
.....
set v_count = v_count + 1
update abc set no_of_count = v_count where ....
call dbms_output.put_line(v_count);
end loop
close cursor
END
/

What wrong of the script ? Please kindly help.

I am not sure that
is necessarily correct. SQLCODE is equal to the standard output of the script, not the exit code.
The variable $? contains the exit code, 0 if successful.
Better to do.

Dear Kitaman,

It work, thanks ! But if I need to do some checking in sql and return false/-1 to the ksh script, how can I do it ? I tried to use command raise_error or signal, but it said it is invalid. Please kindly help.

Thanks !

Wrap the db2 command inside a script.
The $? variable is equal to the exit code from the script.
So.

$? will have a value of 2 if condition a is met, 3 if condition b is met, 0 if the script terminates normally, and 1 if it fails for any other reason.

Dear Kitaman,

Thanks for your help.
I added "exit 2" in my sql and add the return code checking as below. After testing, whatever I set the exit value to 3,4,5 for testing, why it still return 4 ? Also, how can I show message as a log file during the job is running. I tried to usd dbms_output.put_line('start...'), but it seems not work.

in ksh.
db2 "set serveroutput on"
$status=%?
if [ $status -eq 0]
then
exit 0
fi
if [ $status -eq 2]
then
echo "warnning"
exit 0
fi
if [ $status -gt 2]
then
echo "fail checking"
exit 1
else
exit 1
fi

---
in sql

if checking failed ...
exit 2
end if

Thanks !

This line is incorrect.
it should be

Sorry for typing mistake. Actually, in my ksh, it state $status=$?. Whatever I set the exit code is 2 or 3, it still show the exit code is 4 in ksh. Do you know why ? Am I wrongly use the exit code ?

In sql.
exit 2;

In ksh.
status=$?
echo status

Many Thanks !