Post reply

Question: SQL 2005 triggering a DB2 stored proceudre

  • September 7, 2005 at 11:59 am

    #69635

    Is there away to trigger a db2 stored procedure from a SQL server 2005 stored procedure?  If so can someone point me to an example or some documentation.

  • September 7, 2005 at 12:05 pm

    #588197

    Have you checked openrowset/openquery?

  • September 12, 2005 at 11:08 am

    #589122

    Calling the DB2 procedure directly based on openrowset/openquery can lead to performance issues not to mention the limitations that you might encounter because of data-types, error handling etc..  One way of achieving this would be to create a trigger on SQL Server that upon an action (for which the trigger is written), inserts a record in the DB2 table via pass-through query (openquery for example)...then have a trigger on the DB2 table that fires off a stored procedure upon that insert into the table.

    However, evaluate why you need this to occur at real time and why can't it be changed to be a batch operation that occurs every x minutes.  That might be a better way of addressing it otherwise depending upon how much time the actual stored procedure logic takes, your initial transaction will wait till that finishes -- that is because there is no "pragma autonomous transactions concept in SQL Server or DB2 UDB"  - that feature is there only in Oracle which allows the trigger to execute as a separate transaction scope rather than being part of the same transaction.

    So, yes it can be done - whether you want to do it is something you have to weigh.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply