Trigger insert data through federated table

Hello all,

DB2 v9.5 fixpack 5 LUW.

I need insert values on a federated table using a trigger.

Is that possible?

Actually, I´ve got this SQL ERROR on my trigger creation:

SQL30090N Operation invalid for application execution environment.
Reason
code = "22". LINE NUMBER=8. SQLSTATE=25000

Is there another way to do this?

Regards,

Bruno

On 2012-01-26 17:03, Bruno Almeida wrote:
> Hello all,
>
> DB2 v9.5 fixpack 5 LUW.
>
> I need insert values on a federated table using a trigger.
>
> Is that possible?

No, you can't update both systems within the same transaction

>
> Actually, I´ve got this SQL ERROR on my trigger creation:
>
> SQL30090N Operation invalid for application execution environment.
> Reason
> code = "22". LINE NUMBER=8. SQLSTATE=25000
>
> Is there another way to do this?

Some kind of replication would do. Other ways to break up the
transaction would be to have some kind of daemon that periodically
checks if there is something that needs dealing with.

From time to time I have had the same need as you are discussing here,
but I have never figured out a satisfactory solution. If you do come up
with something clever, I'm all ears.


/Lennart

> No, you can't update both systems within the same transaction

What about 2-phase commit?

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On 2012-01-26 20:02, Helmut Tessarek wrote:
>> No, you can't update both systems within the same transaction
>
> What about 2-phase commit?
>

Helmut, not sure I understand. The question was:

> > I need insert values on a federated table using a trigger.
> >
> > Is that possible?

and my answer was:

> No, you can't update both systems within the same transaction

how could a two-phase commit be applied here?


/Lennart

On Jan 27, 5:02*pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2012-01-26 20:02, Helmut Tessarek wrote:
>
> >> No, you can't update both systems within the same transaction
>
> > What about 2-phase commit?
>
> Helmut, not sure I understand. The question was:
>
> > > I need insert values on a federated table using a trigger.
>
> > > Is that possible?
>
> and my answer was:
>
> > No, you can't update both systems within the same transaction
>
> how could a two-phase commit be applied here?
>
> /Lennart

is he really asking to insert into two tables at the same time? or
simply to insert into a table that'd somewhere else but accessible
through federation...if the latter is the question then wouldn't an
instead-of trigger work?

On 2012-02-03 15:20, Bruce wrote:
[...]
> is he really asking to insert into two tables at the same time? or
> simply to insert into a table that'd somewhere else but accessible
> through federation...if the latter is the question then wouldn't an
> instead-of trigger work?

Why bother using a trigger in that case?


/Lennart

On Feb 7, 9:19*pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:
> On 2012-02-03 15:20, Bruce wrote:
> [...]
>
> > is he really asking to insert into two tables at the same time? or
> > simply to insert into a table that'd somewhere else but accessible
> > through federation...if the latter is the question then wouldn't an
> > instead-of trigger work?
>
> Why bother using a trigger in that case?
>
> /Lennart

I did a quick test using a 2-phase commit federation definition and
that didn't work either. Strange, given that the InfoCenter mentions a
trigger on SQLRepl capture site updating a table usually found at the
target site:

http://publib.boulder.ibm.com/infoce...ndsynchpt.html

I'd like to see this cleared out as well...

Kind regards,

Frederik

On 2012-02-08 10:22, Frederik Engelen wrote:
[...]
>
> I did a quick test using a 2-phase commit federation definition and
> that didn't work either.

Ah, ok. I was not even aware that one could define federation that way.
I'll have a look at that

/Lennart

[...]