By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 901 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

.Net and DB2 triggers

P: n/a
hi Gurus,

Presently we have code that "polls" DB2 tables checking for new data.
A much more effective method, especially for real-time systems, would
be to call Dot Net code directly from within DB2 "on insert" or "on
update" triggers. I've not investigated this yet, but I suspect it is
possible. Does anybody know how this might be done?

thanks,
tom

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
to*****@yahoo.com wrote:
hi Gurus,

Presently we have code that "polls" DB2 tables checking for new data.
A much more effective method, especially for real-time systems, would
be to call Dot Net code directly from within DB2 "on insert" or "on
update" triggers. I've not investigated this yet, but I suspect it is
possible. Does anybody know how this might be done?

thanks,
tom

You can call a CLR Procedure or Function from an INSERT trigger.
Prereq is V8.2 when CLR support was added.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Thank you very much - - Is it full of potential pitfalls, dangers, etc?
This is what I am hearing from a developer -
"Seems dangerous. I went to a talk on reliable code at the .NET
conference. The steps to make absolutely sure that all memory is
reclaimed and all resources are freed (even if you receive a thread
abort in the middle of a final statement) requires a deep understanding
of CLR and garbage collection. The talk was created because of the
ability in SQL 2005 to execute C# code within SQL server. To maintain
the '5' nines of up-time requires writing very safe code. Also, for
example, what would happen if a trigger on record insert required a hit
against a web service that happens to be timing out (let's say
30sec)? Would the record be available? Table be locked? Insert
statement throw an error? Etc."

Tom

Nov 12 '05 #3

P: n/a
to*****@yahoo.com wrote:
Thank you very much - - Is it full of potential pitfalls, dangers, etc?
This is what I am hearing from a developer -
"Seems dangerous. I went to a talk on reliable code at the .NET
conference. The steps to make absolutely sure that all memory is
reclaimed and all resources are freed (even if you receive a thread
abort in the middle of a final statement) requires a deep understanding
of CLR and garbage collection. The talk was created because of the
ability in SQL 2005 to execute C# code within SQL server. To maintain
the '5' nines of up-time requires writing very safe code. Also, for
example, what would happen if a trigger on record insert required a hit
against a web service that happens to be timing out (let's say
30sec)? Would the record be available? Table be locked? Insert
statement throw an error? Etc."

Tom

Well I can't comment on SQL Server 2005 implementtion of course ;-)
In DB2 a CLR routine is FENCED. That is it runs in its own address
space. If the developer messes it up and kills it dead teh routine will
return a "Has been unexpectedly terminated" style SQLCODE/SQLSTATe. The
trigger will fail with a -723 (trigger failed with embedded said nasty
SQLCODE). A failing trigger means that teh statement will rollback.
None of that strikes me as out of the oridanry.
W.r.t. your webservice. I the CLR routine sits on its thumbs for 3 days
Db2 can't help it. If teh CLR routines hanldes teh timeout and returns
some reaasonable value or sqlcode then that will be fine.
All in all this is no different than what you woudl get calling a JAVA,
C, COBOL, FORTAN or SQL routine.
Business as usual...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.