CODEWITHSUNDEEP
db2
Chris
Chris

Reputation: 1697

Exception / error handlers in DB2?

I have a bunch of SQL scripts (with shell script wrappers) to unload data like so

    EXPORT TO /tmp/out.csv OF DEL MODIFIED BY NOCHARDEL  COLDEL, DATESISO 
MESSAGES /tmp/out.msg SELECT WIDGETID
    ...

I want to add an error handler to the script the way Oracle does it:

WHENEVER SQLERROR EXIT FAILURE;
SPOOL  /tmp/out.csv;
SELECT WIDGETID...
SPOOL OFF;

According to DB2's documentation, this can be done in stored procedures, C, Perl, REXX, and nothing else...

How can this be done in SQL scripts?

I am running DB2/LINUXX8664 9.7.2.

Upvotes: 1

Views: 1709

Answers (1)

MrG
MrG

Reputation: 1623

you could use the DB2 command line command processor and get its return code. http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0010411.html

or you could use the SYSPROC.ADMIN_CMD procedure and use its return codes. http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0023573.html

you could put the stored proc calls in a script file and run something like db2 -tvf runexport.txt or put the db2 commands in a linux script file and use linux scripting foo to handle the db2 return codes.

Upvotes: 1

Related Questions