Trigger insert data through federated table

[Bruno Almeida]

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

[Lennart Jonsson]

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

[Helmut Tessarek]

> 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

[Lennart Jonsson]

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

[Bruce]

On Jan 27, 5:02 pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
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?

[Lennart Jonsson]

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

[Frederik Engelen]

On Feb 7, 9:19 pm, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:

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/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.swg.im.iis.db.repl.sqlrepl.doc%2Ftopics%2Fiiyrscapsetendsynchpt.html

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

Kind regards,

Frederik

[Lennart Jonsson]

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

[...]

[marcoc...@gmail.com]

Read this:
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.swg.im.iis.fed.query.doc/topics/cfpwrktr.html

Clearly say:
You can include SELECT statements on nicknames in the trigger body. You cannot include INSERT, UPDATE, or DELETE statements on nicknames in the trigger body

Sorry!!!