SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table 
November 12th, 2008, 03:25 PM
| | | |
Hi, I get SQL0746N when trying to call stored procedure. In my
particular case I the message complains about operation "READ", but
I'm posting a simplified version that results in SQL0746N with
"MODIFY". I assume there is a similar reason, but I'm not quite sure
what I'm doing wrong here. Any idea?
I'm running 9.5 SP 0 on windows.
Thank you!
create table t(Id int not null primary key generated by default as
identity, name varchar(100) not null)@
Create Procedure t_Insert(
pName varchar(255)
) LANGUAGE SQL modifies sql data
BEGIN
insert into t(name) values(pName);
end @
Create Procedure t_InsertCall(
pName varchar(255)
) LANGUAGE SQL modifies sql data
BEGIN
Call t_Insert(pName);
end @
begin atomic
declare @UserTypeId int;
Call t_InsertCall( 'a name' );
Call t_InsertCall( 'another name');
end@
|
November 12th, 2008, 05:35 PM
| | | | re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table
Ion wrote: Quote:
Hi, I get SQL0746N when trying to call stored procedure. In my
particular case I the message complains about operation "READ", but
I'm posting a simplified version that results in SQL0746N with
"MODIFY". I assume there is a similar reason, but I'm not quite sure
what I'm doing wrong here. Any idea?
I'm running 9.5 SP 0 on windows.
Thank you!
>
>
create table t(Id int not null primary key generated by default as
identity, name varchar(100) not null)@
>
Create Procedure t_Insert(
pName varchar(255)
) LANGUAGE SQL modifies sql data
BEGIN
insert into t(name) values(pName);
end @
>
>
Create Procedure t_InsertCall(
pName varchar(255)
) LANGUAGE SQL modifies sql data
BEGIN
Call t_Insert(pName);
end @
>
begin atomic
declare @UserTypeId int;
Call t_InsertCall( 'a name' );
Call t_InsertCall( 'another name');
end@
| You have a mutating table conflict.
Try this one:
db2set DB2_RESOLVE_CALL_CONFLICT=ALL
and retreat DB2. No promised. It's geared towards CALL in trigger and
SQL Table functions.
But it's worth a shot.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab |
November 12th, 2008, 05:55 PM
| | | | re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table
On Nov 12, 12:26*pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
Ion wrote: Quote:
Hi, I get SQL0746N when trying to call stored procedure. In my
particular case I the message complains about operation "READ", but
I'm posting a simplified version that results in SQL0746N *with
"MODIFY". I assume there is a similar reason, but I'm not quite sure
what I'm doing wrong here. *Any idea?
I'm running 9.5 SP 0 on windows.
Thank you!
| > Quote:
create table t(Id int not null primary key generated by default as
identity, name varchar(100) not null)@
| > Quote:
Create Procedure t_Insert(
* *pName varchar(255)
) LANGUAGE SQL modifies sql data
BEGIN
* *insert into t(name) values(pName);
end @
| > Quote:
Create Procedure t_InsertCall(
* *pName varchar(255)
) LANGUAGE SQL *modifies sql data
BEGIN
* *Call t_Insert(pName);
end @
| > Quote:
begin atomic
declare @UserTypeId int;
* *Call t_InsertCall( 'a name' );
* *Call t_InsertCall( 'another name');
end@
| >
You have a mutating table conflict.
Try this one:
db2set DB2_RESOLVE_CALL_CONFLICT=ALL
and retreat DB2. No promised. It's geared towards CALL in trigger and
SQL Table functions.
But it's worth a shot.
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
| Thanks,
I tried both YES and NO with no luck though
Apparently ALL is not a valid value for that setting. Or at least for
my DB2 version
db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release
"SQL09050" with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and
Fix Pack "0". |
November 12th, 2008, 07:15 PM
| | | | re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table
Ion wrote: Quote:
On Nov 12, 12:26 pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
>Ion wrote: Quote:
>>Hi, I get SQL0746N when trying to call stored procedure. In my
>>particular case I the message complains about operation "READ", but
>>I'm posting a simplified version that results in SQL0746N with
>>"MODIFY". I assume there is a similar reason, but I'm not quite sure
>>what I'm doing wrong here. Any idea?
>>I'm running 9.5 SP 0 on windows.
>>Thank you!
>>create table t(Id int not null primary key generated by default as
>>identity, name varchar(100) not null)@
>>Create Procedure t_Insert(
>> pName varchar(255)
>>) LANGUAGE SQL modifies sql data
>>BEGIN
>> insert into t(name) values(pName);
>>end @
>>Create Procedure t_InsertCall(
>> pName varchar(255)
>>) LANGUAGE SQL modifies sql data
>>BEGIN
>> Call t_Insert(pName);
>>end @
>>begin atomic
>>declare @UserTypeId int;
>> Call t_InsertCall( 'a name' );
>> Call t_InsertCall( 'another name');
>>end@
| >You have a mutating table conflict.
>Try this one:
>db2set DB2_RESOLVE_CALL_CONFLICT=ALL
>and retreat DB2. No promised. It's geared towards CALL in trigger and
>SQL Table functions.
>But it's worth a shot.
>>
>Cheers
>Serge
>--
>Serge Rielau
>DB2 Solutions Development
>IBM Toronto Lab
| >
Thanks,
I tried both YES and NO with no luck though
Apparently ALL is not a valid value for that setting. Or at least for
my DB2 version
>
>
db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release
"SQL09050" with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and
Fix Pack "0".
>
| Hmm, maybe FP1... Can you wrap the compound into a procedure?
That'll fix the problem at the root.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab |
November 12th, 2008, 08:05 PM
| | | | re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table
On Nov 12, 2:06*pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
Ion wrote: Quote:
On Nov 12, 12:26 pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
Ion wrote:
>Hi, I get SQL0746N when trying to call stored procedure. In my
>particular case I the message complains about operation "READ", but
>I'm posting a simplified version that results in SQL0746N *with
>"MODIFY". I assume there is a similar reason, but I'm not quite sure
>what I'm doing wrong here. *Any idea?
>I'm running 9.5 SP 0 on windows.
>Thank you!
>create table t(Id int not null primary key generated by default as
>identity, name varchar(100) not null)@
>Create Procedure t_Insert(
>* *pName varchar(255)
>) LANGUAGE SQL modifies sql data
>BEGIN
>* *insert into t(name) values(pName);
>end @
>Create Procedure t_InsertCall(
>* *pName varchar(255)
>) LANGUAGE SQL *modifies sql data
>BEGIN
>* *Call t_Insert(pName);
>end @
>begin atomic
>declare @UserTypeId int;
>* *Call t_InsertCall( 'a name' );
>* *Call t_InsertCall( 'another name');
>end@
You have a mutating table conflict.
Try this one:
db2set DB2_RESOLVE_CALL_CONFLICT=ALL
and retreat DB2. No promised. It's geared towards CALL in trigger and
SQL Table functions.
But it's worth a shot.
| | > Quote: Quote:
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
| | > Quote:
Thanks,
I tried both YES and NO with no luck though
Apparently ALL is not a valid value for that setting. Or at least for
my DB2 version
| > Quote:
db2level
DB21085I *Instance "DB2" uses "32" bits and DB2 code release
"SQL09050" with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and
Fix Pack "0".
| >
Hmm, maybe FP1... Can you wrap the compound into a procedure?
That'll fix the problem at the root.
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
| Right, wrapping the compound in a stored procedure works fine.
However, I have plenty of similar occurrences in a large script, it's
a bit of a pain to do so |
November 12th, 2008, 09:25 PM
| | | | re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table
Ion wrote: Quote:
Right, wrapping the compound in a stored procedure works fine.
However, I have plenty of similar occurrences in a large script, it's
a bit of a pain to do so
| FWIW DB2 vNext will support full SQL PL in compounds.
So you can write 2MB scripts with all the nested calls and UDIs in them
you like.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab |  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 225,689 network members.
|
