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.

re: Executing procedure

"Leedrick" <leedrick@gmail.com> wrote in message
news:1138843766.706369.83140@o13g2000cwo.googlegro ups.com...[color=blue]
> 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.
>[/color]

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

re: Executing procedure

Leedrick wrote:[color=blue]
> 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.
>[/color]
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

re: Executing procedure

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.

re: Executing procedure

"Leedrick" <leedrick@gmail.com> wrote in message
news:1138850292.052781.301140@g47g2000cwa.googlegr oups.com...[color=blue]
> 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.
>[/color]

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

call pr_calctsrebate

re: Executing procedure

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.

re: Executing procedure

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.
[color=blue]
> 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.[/color]

re: Executing procedure

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:[color=blue]
> 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.
>[/color]

re: Executing procedure

Leedrick wrote:[color=blue]
> 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(?);
>[/color]

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

re: Executing procedure

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.