469,306 Members | 1,872 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

.Net and DB2 triggers

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
3 1541
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
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
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.

Similar topics

11 posts views Thread by raulgz | last post: by
1 post views Thread by jason_s_ford | last post: by
4 posts views Thread by Mark Flippin | last post: by
1 post views Thread by tim.pascoe | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
4 posts views Thread by --CELKO-- | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.