SQL0723N, -746 SELECTing from the trigger target

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aj - 24 May 2006 02:37 GMT

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

Reply to this Message

Serge Rielau - 24 May 2006 09:58 GMT

> DB2 LUW 8.2 FP5 (or is it 8.1 FP11?)
>
[quoted text clipped - 13 lines]
>
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

aj - 24 May 2006 14:23 GMT

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

>> DB2 LUW 8.2 FP5 (or is it 8.1 FP11?)
>>
[quoted text clipped - 19 lines]
> Cheers
> Serge

Reply to this Message

Serge Rielau - 25 May 2006 10:52 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Kovi - 02 Jun 2006 12:58 GMT

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

> > Thanks Serge, that did it. (I do have FP 11 on this server).
> >
[quoted text clipped - 7 lines]
> Cheers
> Serge

Reply to this Message

Serge Rielau - 02 Jun 2006 15:28 GMT

> 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. :-)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Kovi - 03 Jun 2006 08:16 GMT

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. :)

> > Hi!
> >
[quoted text clipped - 9 lines]
>
> PS: My drive back was harmless and I arrived on time. :-)

Reply to this Message

Kovi - 03 Jun 2006 08:18 GMT

Ups ... sorry for that... My mistake...

> OK, great. Since just yesterday we tried to call procedures from
> triggers and we got SQL746 error. :)
[quoted text clipped - 22 lines]
> > DB2 Solutions Development
> > IBM Toronto Lab

Reply to this Message