473,287 Members | 1,426 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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
May 24 '06 #1
7 4242
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
May 24 '06 #2
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

May 24 '06 #3
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
May 25 '06 #4
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


Jun 2 '06 #5
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
Jun 2 '06 #6
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


Jun 3 '06 #7
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


Jun 3 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: urban.widmark | last post by:
Hello We are having some problems with triggers, sequences and union all in V8 on code that worked fine in V7. Was wondering if someone else has seen this and/or knows what to do. A trigger...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.