473,322 Members | 1,781 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,322 software developers and data experts.

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

Ion
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@
Nov 12 '08 #1
5 3518
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.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Nov 12 '08 #2
Ion
On Nov 12, 12:26*pm, Serge Rielau <srie...@ca.ibm.comwrote:
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.

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

Nov 12 '08 #3
Ion wrote:
On Nov 12, 12:26 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>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.

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
Nov 12 '08 #4
Ion
On Nov 12, 2:06*pm, Serge Rielau <srie...@ca.ibm.comwrote:
Ion wrote:
On Nov 12, 12:26 pm, Serge Rielau <srie...@ca.ibm.comwrote:
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.
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
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
Nov 12 '08 #5
Ion wrote:
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
Nov 12 '08 #6

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

Similar topics

2
by: Askari | last post by:
Hi, How do for do a "select()" on a CheckButton in a menu (make with add_checkbutton(....) )? I can modify title, state, etc but not the "check state". :-( Askari
6
by: Anon | last post by:
I have a table with a cell. The cell's ID is created using a unique name that is held in m_UniqueCellName and the cell is created like so... document.write( "<TD ID="' + m_UniqueCellName +...
2
by: Jozef | last post by:
I'm using Access XP. I'm trying to create a table, modify a table, create in index, and create a relationship in a remote database in code from the program database. I know how to create and...
13
by: baumann.Pan | last post by:
when define char *p = " can not modify"; p ='b' ;is not allowed, but if you declare p as char p = "can modify"; p = 'b'; is ok? why?
2
by: John Bokma | last post by:
I am quite new to MS SQL, and I want to read rows from a todo table, and when a row has been processed, I want to delete that row. Ages ago in MySQL I would probably have locked the table,...
4
by: J Rice | last post by:
I have been experimenting with some thread programming, but as I'm doing this on my own I am worried I might be making a major mistake. Here's a brief rundown of what I am working on. Multiple...
3
by: ken | last post by:
I have a field in table "Jobs" called "JobNo" consisting of 6 numbers such as 405043 I need to somehow delete the first 3 numbers to have field just be 043 I could delete the first 3 numbers by...
1
by: Cryptographic_ICE | last post by:
Hello, I have a table that contains the name and location of backup tapes. The first Column has the tape ID (a four digit number) and the second column has a drop down box of possible locations...
1
by: hgarcia | last post by:
Hi everybody, I need to modify the "execution path" in orther to instal a new library. How I can do this, actually I don't even know what an execution path is? Thanks, H
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.