SQL0723N, -746 SELECTing from the trigger target

[aj]

DB2 LUW 8.2 FP5 (or is it 8.1 FP11?)

I am CALLing a stored procedure from a trigger in order to maintain a
column-level audit trail. Not only do I need to store a record of
the INSERT, but also the initial values of all columns as of the INSERT,
in the form of UPDATEs w/ NULL old values.

I pass (amongst other things) a table name and a primary key to the SP
and use dynamic sql and some syscat.columns magic to query the table and
get those initial column values to put into the audit trail.

I am getting a SQL0723N, SQLCODE -746.

Obviously, DB2 doesn't like me querying the trigger target in an SP
that is called from the trigger.

Is there any way around this?

Any help appreciated.

aj

[Serge Rielau]

aj wrote:

DB2 LUW 8.2 FP5 (or is it 8.1 FP11?)

I am CALLing a stored procedure from a trigger in order to maintain a
column-level audit trail. Not only do I need to store a record of
the INSERT, but also the initial values of all columns as of the INSERT,
in the form of UPDATEs w/ NULL old values.

I pass (amongst other things) a table name and a primary key to the SP
and use dynamic sql and some syscat.columns magic to query the table and
get those initial column values to put into the audit trail.

I am getting a SQL0723N, SQLCODE -746.

Obviously, DB2 doesn't like me querying the trigger target in an SP
that is called from the trigger.

Is there any way around this?

Pray you have FP11 and follow this link. :-)
http://tinyurl.com/8ndah
It's fixed in Viper by default (not in the viper test drive though).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[aj]

Thanks Serge, that did it. (I do have FP 11 on this server).

Too bad it precludes DDL in the SP, tho. Using your advice from
another post, I was using DGTT in the SP w/ ON COMMIT PRESERVE
ROWS.
In the SP, I was shoving the new audit trail rows into the DGTT
as I went, and then finally adding them all to the audit trail
w/ one fast insert from subselect.
Guess I'll have to go back to multiple inserts on the audit trail.

cheers

aj

Serge Rielau wrote:

aj wrote:

DB2 LUW 8.2 FP5 (or is it 8.1 FP11?)

I am CALLing a stored procedure from a trigger in order to maintain a
column-level audit trail. Not only do I need to store a record of
the INSERT, but also the initial values of all columns as of the INSERT,
in the form of UPDATEs w/ NULL old values.

I pass (amongst other things) a table name and a primary key to the SP
and use dynamic sql and some syscat.columns magic to query the table and
get those initial column values to put into the audit trail.

I am getting a SQL0723N, SQLCODE -746.

Obviously, DB2 doesn't like me querying the trigger target in an SP
that is called from the trigger.

Is there any way around this?

Pray you have FP11 and follow this link. :-)
http://tinyurl.com/8ndah
It's fixed in Viper by default (not in the viper test drive though).

Cheers
Serge

[Serge Rielau]

aj wrote:

Thanks Serge, that did it. (I do have FP 11 on this server).

Too bad it precludes DDL in the SP, tho. Using your advice from
another post, I was using DGTT in the SP w/ ON COMMIT PRESERVE
ROWS.

The DDL restriction is gone in DB2 Viper.
But perhaps you could define your DGTT outside the proc (at session
startup).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Kovi]

Hi!

If I set variable DB2_RESOLVE_CALL_CONFLICT are there any dangers
involved in this ? I don't know, maybe corrupting some data, ...
Do you consider setting this variable safe ?

Best regards,
Kovi

Serge Rielau wrote:

aj wrote:

Thanks Serge, that did it. (I do have FP 11 on this server).

Too bad it precludes DDL in the SP, tho. Using your advice from
another post, I was using DGTT in the SP w/ ON COMMIT PRESERVE
ROWS.

The DDL restriction is gone in DB2 Viper.
But perhaps you could define your DGTT outside the proc (at session
startup).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Kovi wrote:

Hi!

If I set variable DB2_RESOLVE_CALL_CONFLICT are there any dangers
involved in this ? I don't know, maybe corrupting some data, ...
Do you consider setting this variable safe ?

Yes, it's safe. The only cave-at is that in V8 setting this variable
will disallow any DDL inside of the called procedure including DECLARE
GLOBAL TEMPORARY TABLE.

Cheers
Serge

PS: My drive back was harmless and I arrived on time. :-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Kovi]

OK, great. Since just yesterday we tried to call procedures from
triggers and we got SQL746 error. :)

Best regards,
Kovi

P.S.: Good to hear that. I hope you wedding will be great and that you
don't break that bottle of wine. :)

Serge Rielau wrote:

Kovi wrote:

Hi!

If I set variable DB2_RESOLVE_CALL_CONFLICT are there any dangers
involved in this ? I don't know, maybe corrupting some data, ...
Do you consider setting this variable safe ?

Yes, it's safe. The only cave-at is that in V8 setting this variable
will disallow any DDL inside of the called procedure including DECLARE
GLOBAL TEMPORARY TABLE.

Cheers
Serge

PS: My drive back was harmless and I arrived on time. :-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Kovi]

Ups ... sorry for that... My mistake...
Kovi wrote:

OK, great. Since just yesterday we tried to call procedures from
triggers and we got SQL746 error. :)

Best regards,
Kovi

P.S.: Good to hear that. I hope you wedding will be great and that you
don't break that bottle of wine. :)

Serge Rielau wrote:

Kovi wrote:

Hi!

If I set variable DB2_RESOLVE_CALL_CONFLICT are there any dangers
involved in this ? I don't know, maybe corrupting some data, ...
Do you consider setting this variable safe ?

Yes, it's safe. The only cave-at is that in V8 setting this variable
will disallow any DDL inside of the called procedure including DECLARE
GLOBAL TEMPORARY TABLE.

Cheers
Serge

PS: My drive back was harmless and I arrived on time. :-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab