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]

Post by Bruno Almeida
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

Post by Bruno Almeida
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]

Post by Lennart Jonsson
No, you can't update both systems within the same transaction

What about 2-phase commit?

[Lennart Jonsson]

Post by Helmut Tessarek

Post by Lennart Jonsson
No, you can't update both systems within the same transaction

What about 2-phase commit?

Post by Lennart Jonsson
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

how could a two-phase commit be applied here?


/Lennart

[Bruce]

Post by Lennart Jonsson

Post by Helmut Tessarek

Post by Lennart Jonsson
No, you can't update both systems within the same transaction

What about 2-phase commit?

Post by Lennart Jonsson
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

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?

[Lennart Jonsson]

On 2012-02-03 15:20, Bruce wrote:
[...]

Post by Bruce
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]

Post by Lennart Jonsson
[...]

Post by Bruce
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/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:
[...]

Post by Frederik Engelen
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

[...]

[m***@gmail.com]

Post by Bruno Almeida
Hello all,
DB2 v9.5 fixpack 5 LUW.
I need insert values on a federated table using a trigger.
Is that possible?
SQL30090N Operation invalid for application execution environment.
Reason
code = "22". LINE NUMBER=8. SQLSTATE=25000
Is there another way to do this?
Regards,
Bruno

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!!!