Trigger insert data through federated table

#1

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

#2

On 2012-01-26 17:03, Bruno Almeida wrote:

Quote:

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

Quote:

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

#3

Quote:

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

#4

On 2012-01-26 20:02, Helmut Tessarek wrote:

Quote:

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

What about 2-phase commit?

Helmut, not sure I understand. The question was:

Quote:

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

Is that possible?

and my answer was:

Quote:

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

how could a two-phase commit be applied here?

/Lennart

#5

On Jan 27, 5:02*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

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?

#6

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

Quote:

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

#7

On Feb 7, 9:19*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

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