473,695 Members | 2,675 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Executing procedure

Hi,

In the past I have been dealing mainly with sql server, and recently
been given the task of supporting software that uses db2 on as400.

Simple tasks such as executing a stored procedure from a query window
have proven more troublesome than I expected.

The procedure I'm trying to call has an output argument. I can't seem
to declare a variable to pass outside of a procedure or function, and
passing 0 or null throws an error:

[SQL0469] IN, OUT, or INOUT not valid for parameter 1 in procedure
PR_myproc in *N. Cause . . . . . : The IN, INOUT, or OUT attribute
specified for parameter 1 on the DECLARE PROCEDURE or CREATE PROCEDURE
statement is not valid.

Is there an OUT or IN keyword I should use when passing the parameter?

Also, can anyone tell me a good tool for writing sql scripts for db2 on
iseries? Something that provides color coding and can generate
formatted scripts from objects would be great. I spent a while looking
but didn't find anything great (toad looked promising until i realised
it wouldnt work for db2 on the as400).

Thanks for your help,

Lee.

Feb 2 '06 #1
9 15410
"Leedrick" <le******@gmail .com> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...
Hi,

In the past I have been dealing mainly with sql server, and recently
been given the task of supporting software that uses db2 on as400.

Simple tasks such as executing a stored procedure from a query window
have proven more troublesome than I expected.

The procedure I'm trying to call has an output argument. I can't seem
to declare a variable to pass outside of a procedure or function, and
passing 0 or null throws an error:

[SQL0469] IN, OUT, or INOUT not valid for parameter 1 in procedure
PR_myproc in *N. Cause . . . . . : The IN, INOUT, or OUT attribute
specified for parameter 1 on the DECLARE PROCEDURE or CREATE PROCEDURE
statement is not valid.

Is there an OUT or IN keyword I should use when passing the parameter?

Also, can anyone tell me a good tool for writing sql scripts for db2 on
iseries? Something that provides color coding and can generate
formatted scripts from objects would be great. I spent a while looking
but didn't find anything great (toad looked promising until i realised
it wouldnt work for db2 on the as400).

Thanks for your help,

Lee.


I don't know how DB2 for iSeries works, but for DB2 for LUW here is call
syntax from the command line:

db2 "call sp_name (parm1, parm2 ,parm3)"

where parm1, parm2, parm3 are the data supplied for in or inout parms. If
the data is character, use tickmarks as usual).
Feb 2 '06 #2
Leedrick wrote:
Hi,

In the past I have been dealing mainly with sql server, and recently
been given the task of supporting software that uses db2 on as400.

Simple tasks such as executing a stored procedure from a query window
have proven more troublesome than I expected.

The procedure I'm trying to call has an output argument. I can't seem
to declare a variable to pass outside of a procedure or function, and
passing 0 or null throws an error:

[SQL0469] IN, OUT, or INOUT not valid for parameter 1 in procedure
PR_myproc in *N. Cause . . . . . : The IN, INOUT, or OUT attribute
specified for parameter 1 on the DECLARE PROCEDURE or CREATE PROCEDURE
statement is not valid.

Is there an OUT or IN keyword I should use when passing the parameter?

Also, can anyone tell me a good tool for writing sql scripts for db2 on
iseries? Something that provides color coding and can generate
formatted scripts from objects would be great. I spent a while looking
but didn't find anything great (toad looked promising until i realised
it wouldnt work for db2 on the as400).

Thanks for your help,

Lee.

Maybe best explained with an example...

CREATE PROCEDURE p(IN a INT, INOUT b VARCHAR(10), OUT c INT)
......

-- Note the parameter marker (question mark) for the OUT parameter
CALL p(5, 6, ?)

In a program of course you can pass variables....
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Feb 2 '06 #3
Thanks guys,

Unfortunately when I pass ? as an argument I get the following error:

"The number of parameter values set or registered does not match the
number of parameters."

The header of the procedure is as follows:

create procedure pr_calctsrebate
(out success integer)
language sql
called on null input

and my call is:

call pr_calctsrebate (?);

Lee.

Feb 2 '06 #4
"Leedrick" <le******@gmail .com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .
Thanks guys,

Unfortunately when I pass ? as an argument I get the following error:

"The number of parameter values set or registered does not match the
number of parameters."

The header of the procedure is as follows:

create procedure pr_calctsrebate
(out success integer)
language sql
called on null input

and my call is:

call pr_calctsrebate (?);

Lee.


That stored procedure has no input parms that I can see. Try:

call pr_calctsrebate
Feb 2 '06 #5
how about the output int called "success"?

If i run without passing anything, I get:

[SQL0440] Routine PR_CALCTSREBATE in *N not found with specified
parameters. Cause . . . . . : A function or procedure with the
specified name and compatible arguments was not found.

Feb 2 '06 #6
Hello.

Unfortunately, you can't call from clp or any standard tool (iSeries
Navigator, for example) stored procedure that has an output parameter
in udb db2 for as400 like you do it for udb db2 for LUW...
You have to use some programming language.

Sincerely,
Mark B.
Hi,

In the past I have been dealing mainly with sql server, and recently
been given the task of supporting software that uses db2 on as400.

Simple tasks such as executing a stored procedure from a query window
have proven more troublesome than I expected.

The procedure I'm trying to call has an output argument. I can't seem
to declare a variable to pass outside of a procedure or function, and
passing 0 or null throws an error:

[SQL0469] IN, OUT, or INOUT not valid for parameter 1 in procedure
PR_myproc in *N. Cause . . . . . : The IN, INOUT, or OUT attribute
specified for parameter 1 on the DECLARE PROCEDURE or CREATE PROCEDURE
statement is not valid.

Is there an OUT or IN keyword I should use when passing the parameter?

Also, can anyone tell me a good tool for writing sql scripts for db2 on
iseries? Something that provides color coding and can generate
formatted scripts from objects would be great. I spent a while looking
but didn't find anything great (toad looked promising until i realised
it wouldnt work for db2 on the as400).

Thanks for your help,

Lee.


Feb 2 '06 #7
Appears you're using an old release of iSeries Navigator.

Options would be:
-Updating to a more current release
-Write a simple SQL procedure that calls the procedure using locals
variables
-Look at DB2 Development Center
Leedrick wrote:
how about the output int called "success"?

If i run without passing anything, I get:

[SQL0440] Routine PR_CALCTSREBATE in *N not found with specified
parameters. Cause . . . . . : A function or procedure with the
specified name and compatible arguments was not found.

Feb 2 '06 #8
Leedrick wrote:
Thanks guys,

Unfortunately when I pass ? as an argument I get the following error:

"The number of parameter values set or registered does not match the
number of parameters."

The header of the procedure is as follows:

create procedure pr_calctsrebate
(out success integer)
language sql
called on null input

and my call is:

call pr_calctsrebate (?);


Using Run SQL Scripts (iSeries Navigator), try using "set current
schema" and "set current path" for unqualified names. Example:

set current schema xyz ;
set current path xyz ;
create procedure parmtst (out success integer)
language sql
begin
select count(*) into success from qsys2.qsqptabl;
end ;
call parmtst(?) ;

--
Karl Hanson
Feb 2 '06 #9
I'm sorry for my previous post here.
That is right: after applying latest fixpack on iSeries Navigator I
have got this feature.
Thanks a lot!

Sincerely,
Mark B.

Feb 3 '06 #10

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

Similar topics

0
2258
by: rvdw | last post by:
Hi All, I've a serious problem with executing stored procedures (SQL2000) from an Access db (version 97). After executing a stored procedure , msaccess hangs. The whole call to the procedure is running fine, but immediatly after this msaccess hangs, when the focus goes back to the calling form ? Has anyone any idea what i can test or look at. Thanks a lot !!!!!!
0
1323
by: M Wells | last post by:
Hi All, I don't know if this is a truly esoteric question, or not, but I'm wondering how (or even if) you handle a timeout on a transaction within a stored procedure executed in a Stored Procedure? A theoretical example of the stored procedure (sadly, I'm not allowed to post the actual code):
1
3148
by: rvdw | last post by:
Hi All, I've a serious problem with executing stored procedures (SQL2000) from an Access db (version 97). After executing a stored procedure , msaccess hangs. The whole call to the procedure is running fine, but immediatly after this msaccess hangs, when the focus goes back to the calling form ? Has anyone any idea what i can test or look at. Thanks a lot !!!!!!
2
1178
by: guy | last post by:
How can I find the name of a sub/function that is currently executing this is for an error logger that takes the name of the class and name of the procedure as parameter e.g.LogError(Me.GetType.ToString,"ProcedureName" thx guy
3
3475
by: kd | last post by:
Hi All, How to debug a stored procedure? Thanks, kd
7
9707
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason for this being to stop the user thinking the application has frozen when in fact it is just waiting for a long SP to complete. Another reason for doing it like this is that I also have had a problem in the past where the SP takes longer than the...
11
4095
by: raylopez99 | last post by:
Keep in mind this is my first compiled SQL program Stored Procedure (SP), copied from a book by Frasier Visual C++.NET in Visual Studio 2005 (Chap12). So far, so theory, except for one bug (feature?) below. At some point I'm sure I'll be able to laugh about this, akin to forgeting a semi-colon in C/C++, but right now it's frustrating (time to sleep on it for a while). Problem-- For some reason I get the error when trying to save files...
13
9195
by: Neil | last post by:
Can I get the name of a procedure from within the procedure? In my error handler, I write the error to an error table. I'd like to write the name of the procedure that's writing the error. But, rather than customizing each error handler with the procedure name, it would be nice to be able to call a system variable or function that gives me the procedure name and module name. Is that possible?
2
6301
ilikesuresh
by: ilikesuresh | last post by:
Hi all, I have written athe following program to execute a procedure number of times using korn shell scripting for number in `cat $INPUT_FILE` do $SQLPLUS -S $sql_user_name/$sql_user_pwd@$sql_service <<EOF 1>>$LOG_FILE 2>>$LOG_FILE set head off; set echo off; set serveroutput on; exec number_names(&1);
0
8647
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8585
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9004
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
5842
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4351
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4592
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3024
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2288
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1986
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.