473,320 Members | 2,161 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,320 software developers and data experts.

Store Procedure failure

Hi ,

I am getting the below error while i am invoking a Java stored
procedure from another java stored procedure.

[IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
attempted to execute a statement that is not allowed.

The Main SP say A ,calls SP1 reads the data and in a while loop
calls the SP2 to insert the data in that is retrieved in the
resultset.
Failure happens when the executeUpdate() is called for SP2 in the
first time itself.

I am using DB2 v8.1.5.449, and developing using the DB2 SPB.

Has anyone have any idea or ever faced this problem please do let me
know the solution ..I am stuck in the middle of development with this
issue.

Thanks much in advance
Regards,
george
Nov 12 '05 #1
7 2827
Check the sql levels you created the routines with...you can't call a
modifies sql data routine from a reads sql data routine for example...

George wrote:
Hi ,

I am getting the below error while i am invoking a Java stored
procedure from another java stored procedure.

[IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
attempted to execute a statement that is not allowed.

The Main SP say A ,calls SP1 reads the data and in a while loop
calls the SP2 to insert the data in that is retrieved in the
resultset.
Failure happens when the executeUpdate() is called for SP2 in the
first time itself.

I am using DB2 v8.1.5.449, and developing using the DB2 SPB.

Has anyone have any idea or ever faced this problem please do let me
know the solution ..I am stuck in the middle of development with this
issue.

Thanks much in advance
Regards,
george

Nov 12 '05 #2
For all the procedures in the context it is given as the "stored
procedure modifies sql data".
Nov 12 '05 #3
I don't understand what this means. Do you mean you catalog this with
"modifies SQL data" if so, your best bet is probably a statement event
monitor to see exactly which SQL is being rejected.

George wrote:
For all the procedures in the context it is given as the "stored
procedure modifies sql data".

Nov 12 '05 #4
Hi Sean,

Thanks for your reply..The procedure that are developed has "Modifies
SQL data" set in as the config parameter.

Using SPB of DB2 ,if i right click on Properties -->Options-->Store
Procedure-->
"Modifies SQL data"..

The problem is < I Have 2 procedures >

Main procedure -->That invokes SP1 --->>This invokes a set of
procedures-->>SP2,SP3

It fails at the first level itself ...by throwing the below exception

IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
attempted to execute a statement that is not allowed.

Thanks

Rgds,george
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
I don't understand what this means. Do you mean you catalog this with
"modifies SQL data" if so, your best bet is probably a statement event
monitor to see exactly which SQL is being rejected.

George wrote:
For all the procedures in the context it is given as the "stored
procedure modifies sql data".

Nov 12 '05 #5
Hi Sean,

Thanks for your reply..The procedure that are developed has "Modifies
SQL data" set in as the config parameter.

Using SPB of DB2 ,if i right click on Properties -->Options-->Store
Procedure-->
"Modifies SQL data"..

The problem is < I Have 2 procedures >

Main procedure -->That invokes SP1 --->>This invokes a set of
procedures-->>SP2,SP3

It fails at the first level itself ...by throwing the below exception

IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
attempted to execute a statement that is not allowed.

Thanks

Rgds,george
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
I don't understand what this means. Do you mean you catalog this with
"modifies SQL data" if so, your best bet is probably a statement event
monitor to see exactly which SQL is being rejected.

George wrote:
For all the procedures in the context it is given as the "stored
procedure modifies sql data".

Nov 12 '05 #6
To be sure that your routines are cataloged correctly, look at
SQL_DATA_ACCESS in SYSROUTINES. If they all show up with "M" then you
need to find exactly which sql statement is being rejected (statement
evmon or good sp error handling should find this)...another idea would
be to try invoking sp1 directly to see if it's really a problem with
routine interaction, or something lower down...

The only reason I can think of for a CALL statement to fail is if you're
calling a dari style (or uncataloged) sp, or if the incorrect SQL access
levels are set.

George wrote:
Hi Sean,

Thanks for your reply..The procedure that are developed has "Modifies
SQL data" set in as the config parameter.

Using SPB of DB2 ,if i right click on Properties -->Options-->Store
Procedure-->
"Modifies SQL data"..

The problem is < I Have 2 procedures >

Main procedure -->That invokes SP1 --->>This invokes a set of
procedures-->>SP2,SP3

It fails at the first level itself ...by throwing the below exception

IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
attempted to execute a statement that is not allowed.

Thanks

Rgds,george
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
I don't understand what this means. Do you mean you catalog this with
"modifies SQL data" if so, your best bet is probably a statement event
monitor to see exactly which SQL is being rejected.

George wrote:
For all the procedures in the context it is given as the "stored
procedure modifies sql data".

Nov 12 '05 #7
To be sure that your routines are cataloged correctly, look at
SQL_DATA_ACCESS in SYSROUTINES. If they all show up with "M" then you
need to find exactly which sql statement is being rejected (statement
evmon or good sp error handling should find this)...another idea would
be to try invoking sp1 directly to see if it's really a problem with
routine interaction, or something lower down...

The only reason I can think of for a CALL statement to fail is if you're
calling a dari style (or uncataloged) sp, or if the incorrect SQL access
levels are set.

George wrote:
Hi Sean,

Thanks for your reply..The procedure that are developed has "Modifies
SQL data" set in as the config parameter.

Using SPB of DB2 ,if i right click on Properties -->Options-->Store
Procedure-->
"Modifies SQL data"..

The problem is < I Have 2 procedures >

Main procedure -->That invokes SP1 --->>This invokes a set of
procedures-->>SP2,SP3

It fails at the first level itself ...by throwing the below exception

IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
attempted to execute a statement that is not allowed.

Thanks

Rgds,george
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
I don't understand what this means. Do you mean you catalog this with
"modifies SQL data" if so, your best bet is probably a statement event
monitor to see exactly which SQL is being rejected.

George wrote:
For all the procedures in the context it is given as the "stored
procedure modifies sql data".

Nov 12 '05 #8

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

Similar topics

3
by: dinesh prasad | last post by:
I'm trying to use a servlet to process a form, then send that data to an SQL server stored procedure. I'm using the WebLogic 8 App. server. I am able to retrieve database information, so I know my...
2
by: Brian | last post by:
Hi there, I am converting a large PL/SQL project into Transact-SQL and have hit an issue as follows: I have a PL/SQL procedure that converts a string to a date. The procedure does not know...
4
by: Paul | last post by:
Hi, In SQL Books Online in the section on @@Error it gives the following example: -- Execute the INSERT statement. INSERT INTO authors (au_id, au_lname, au_fname, phone, address, city,...
2
by: Jose Perez | last post by:
Dear All, I have 2 SP's, one (let's call it sp_A) which returns a list of files and another (let's call it sp_B) which recursively looks through a menu table to give me the location of the file....
4
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will...
8
by: N.V.Dev | last post by:
Hi, I am trying to use the db2load API using C. During run-time below is the error message thrown call utils.load_table('util.temp') SQL1131N DARI (Stored Procedure) process has been...
6
by: c676228 | last post by:
Hi everyone, I wrote a store procedure that fetch one row data in the database based on the parameter value I entered. After I created the store procedure, the store procedure code looks like...
0
by: rxding | last post by:
Hello, Performance reason we need to move some of our code into database. Java Store Procedure is given the first choice. However, while investigating some sample code of Java store procedure, ...
11
by: c676228 | last post by:
Hi everyone, I am just wodering in asp program, if there is anybody writing store procedure for inserting data into database since there are so many parameters need to be passed into store...
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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: 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...
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)...
0
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...
0
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: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.