SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table

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@

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

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".

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

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:

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

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

SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table

re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table

re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table

re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table

re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table

re: SQL0746N: Routine violated nested SQL statement rules when attemptingto perform operation "MODIFY" on table

Similar DB2 Database bytes

/bytes/development

/bytes/it

/bytes/about