reporting errors in a separate transaction

[Ford Desperado]

In Oracle, I would handle exceptions like this

EXCEPTION
WHEN OTHERS THEN
-- open up an autonomous transaction
-- insert SQLCODE, SQLERRM and stored procedure name into log table
-- commit the autonomous transaction
ROLLBACK;
RAISE;
END;

the idea is that I don't lose teh logged error diagnostics when I
rollback the failed transaction.
Any ideas how to accomplish that in DB2 V8.1 for LUW?

TIA

[Serge Rielau]

Ford Desperado wrote:

In Oracle, I would handle exceptions like this

EXCEPTION
WHEN OTHERS THEN
-- open up an autonomous transaction
-- insert SQLCODE, SQLERRM and stored procedure name into log table
-- commit the autonomous transaction
ROLLBACK;
RAISE;
END;

the idea is that I don't lose teh logged error diagnostics when I
rollback the failed transaction.
Any ideas how to accomplish that in DB2 V8.1 for LUW?

TIA

You have three options that I can think of:
1. Use a file
2. Use a global temporary table defined a ON ROLLBACK PRESERVE ROWS
(requires V8.2)
3. Use a message queue

Cheers
Serge

[Mark A]

"Ford Desperado" <fo************ @yahoo.com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...

In Oracle, I would handle exceptions like this

EXCEPTION
WHEN OTHERS THEN
-- open up an autonomous transaction
-- insert SQLCODE, SQLERRM and stored procedure name into log table
-- commit the autonomous transaction
ROLLBACK;
RAISE;
END;

the idea is that I don't lose teh logged error diagnostics when I
rollback the failed transaction.
Any ideas how to accomplish that in DB2 V8.1 for LUW?

TIA

How about do the ROLLBACK first, then insert SQLCODE, SQLERRM and stored
procedure name into log table, then commit.

[Ford Desperado]

> 3. Use a message queue

option 3 seems to be quite convenient, thanks. I've read some docs on
wrapping up calls to MQ Series inside a UDF. Has anyone used that in
practice?

It's such a shame DB2 does not have built in message queues like
Oracle does

[Ford Desperado]

> How about do the ROLLBACK first, then insert SQLCODE, SQLERRM and stored

procedure name into log table, then commit.

Good point, but I don't always choose to rollback right away.

[Knut Stolze]

Ford Desperado wrote:

3. Use a message queue

option 3 seems to be quite convenient, thanks. I've read some docs on
wrapping up calls to MQ Series inside a UDF. Has anyone used that in
practice?

It's such a shame DB2 does not have built in message queues like
Oracle does

Did you already have a look at the MQ functions?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena