Hello all,

I have a batch file as follows:

echo on
echo >C:\Jobs\DB2_customer.log
C:\Progra~1\IBM\SQLLIB\BIN\db2cmd.exe /c /i db2 -tvwf C:\Jobs\DB2_unload_customer.txt -es -s -z C:\Jobs\DB2_customer.log
echo errorlevel
exit ERRORLEVEL

The content of is a simple extract:

CONNECT TO POC_SEC;
EXPORT TO "c:\jobs\customer.txt" OF DEL MESSAGES "c:\jobs\customer.msg" SELECT * FROM USUJAR0.CUSTOMER;
CONNECT RESET;

I have run this natively under my authority and it runs fine. The problem is I am trying to run this with a sceduling package (we have Tidal). The user id that is running this through Tidal does not have authority purposely i.e. I am trying to test a failure. Unfortunately, as far as Tidal is concerned this script returns a zero return code. If I look in the DB2_customer.log I can clearly see the bad SQL message for the connect statement. Is there something that needs to change in my job or something that needs to be set up in Tidal to get the bad SQL code? Thanks in advance for your help.

You're returning the errorlevel value after db2cmd.exe while what you want apparently is the errorlevel value after db2.exe

Try like this. Write two batch files. In first one just inicialize db2cmd environment and run second batch file.
In second batch file use db2 commands (note: each command must begin with 'db2' command and must be in one line, but without comma at the end of the line). Now execute (schedule) run.bat file. File sql.bat will return error if something goes wrong. You can also write error to log file with redirecting the output to file e.g:
echo "Error" > output.log


======= run.bat ========
db2cmd /i /c sql.bat
========================

======= sql.bat ========
db2 CONNECT TO POC_SEC
if not %errorlevel% == 0 goto error
EXPORT TO "c:\jobs\customer.txt" OF DEL MESSAGES "c:\jobs\customer.msg" SELECT * FROM USUJAR0.CUSTOMER
if not %errorlevel% == 0 goto error
db2 CONNECT RESET
if not %errorlevel% == 0 goto error
goto exit

:error
echo "Error"
goto exit

:exit

========================


Hope this helps,
Grofaty

How do I do that? As you can see I've got the DB2 command embedded in the DB2CMD. I've tried both the echo errorlevel and exit errorlevel to no avail.

Thanks Grofaty. I'll give it a try.