How to call stored procedure from terminal

[soup_o...@yahoo.com]

Hello All

I am calling a stored procedure from ISERIES/Terminal on AS400 that
interacts directly with DB2.

CALL XYZ/WABRACADABRA (81508, 46396, '024582348', '', '', 'I')

The proc takes 6 inputs and has 1 input/output variable. I get the
error message:
"Routine ABRACADABRA not found with specified parameters"

I am able to run other queries like SELECT meaning the DB2 is
interacting directly (without
ODBC/JDBC) with the terminal.

I have also tried: CALL USING but no luck.

Can someone please help me out?

Thanks

[soup_o...@yahoo.com]

On Dec 10, 9:39 am, "souporpo...@gmail.com" <soup_or_po...@yahoo.com>
wrote:

I have tried the following:

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

Now I get the error: "Keyword END not expected. Valid tokens: FOR WITH
FETCH ORDER UNION....."

Please help

[soup_o...@yahoo.com]

On Dec 10, 10:26 am, "souporpo...@gmail.com" <soup_or_po...@yahoo.com>
wrote:

By removing the semi-columns at the end of the lines except for the
SQL resulted in success.
I.e. the stored procedure parmtst is now in the DB.

Now when I call the procedure as:

call parmtst(?)

I get the error "Use of parameter marker not valid"

Please help

[Karl Hanson]

I created this proc on a V5R4 system (leaving off the trailing ';') and
tried calling it via STRSQL. I believe the problem is that when a parm
is declared OUT or INOUT, some "write capable" storage must be supplied
for it on the CALL - eg a host variable in embedded SQL or a parameter
in for dynamic SQL. In essence running a statement interactively via
STRSQL is like EXECUTE IMMEDIATE, so no host variables or parameter
markers are allowed in that environment.

> call parmtst(2)
IN, OUT, or INOUT not valid for parameter 1 in procedure PARMTST in

Press F1 w/cursor over message, or on a System i command line:
===> wrkmsgd SQL0469 qsqlmsg
Take option 5 then option 30

Part of 2nd level text from message SQL0469:
Cause . . . . . : The IN, INOUT, or OUT attribute specified for
parameter 1
when the procedure was defined is not valid. The parameter name is
SUCCESS.
One of the following errors occurred:

-- The attribute is not consistent with the parameter on the CALL

statement. If the parameter was declared INOUT or OUT, the parameter
on the
CALL statement must be specified as a host variable.

--
Karl Hanson

[soup_o...@yahoo.com]

On Dec 10, 10:45 am, Karl Hanson <kchan...@youess.ibm.com> wrote:

Hi Karl

Thanks for the clarification. I need help with a couple of things:
a) How can I list all of the available forms (with stored procedures
in the back end)
b) How can I run a stored procedure? Is there another way ?
c) I saw some of my office mates using "GO CLP6." When I do that at
the
AS400 prompt, I get the error "Object CLP6 in Library *LIBL not
found".
I am too shy to bother by co-workers with questions.

Thanks

[Karl Hanson]

soupo...@gmail.com wrote:
>
> Hi Karl
>
> Thanks for the clarification. I need help with a couple of things:
> a) How can I list all of the available forms (with stored procedures
> in the back end)
> b) How can I run a stored procedure? Is there another way ?
> c) I saw some of my office mates using "GO CLP6." When I do that at
> the
> AS400 prompt, I get the error "Object CLP6 in Library *LIBL not
> found".
> I am too shy to bother by co-workers with questions.
>
> Thanks

a) Not sure what you mean, but the SYSPROCS catalog can be queried to
find SQL procedures:
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp?topic=/db2/rbafzcatsysprocs.htm&tocNode=int_108798

b) If you have System i Navigator, SPs can be run via the Run SQL
Scripts facility. It supports supplying parameter markers for OUT
parameters. Google found this, that seems related:
http://bytes.com/groups/ibm-db2/452148-executing-procedure
Here is a link to some iNav info:
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp?topic=/rzatc/rzatcrunscript.htm

c) CLP6 may be a local application menu (not part of the OS). It may be
that the library containing it is just not part of your job's library
list. On a Command Entry screen, try this to find which library has it:
DSPOBJD OBJ(*ALL/CLP6) OBJTYPE(*MENU)
If that shows the library then try using the ADDLIBLE command to add it
to your job's library list.

--
Karl Hanson

[soup_o...@yahoo.com]

On Dec 11, 11:35 am, Karl Hanson <kchan...@youess.ibm.com> wrote:

> souporpo...@gmail.com wrote:
>
> > Hi Karl
>
> > Thanks for the clarification. I need help with a couple of things:
> > a) How can I list all of the available forms (with stored procedures
> > in the back end)
> > b) How can I run a stored procedure? Is there another way ?
> > c) I saw some of my office mates using "GO CLP6." When I do that at
> > the
> > AS400 prompt, I get the error "Object CLP6 in Library *LIBL not
> > found".
> > I am too shy to bother by co-workers with questions.
>
> > Thanks
>
> a) Not sure what you mean, but the SYSPROCS catalog can be queried to

> find SQL procedures:http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp...

>
> b) If you have System i Navigator, SPs can be run via the Run SQL
> Scripts facility.  It supports supplying parameter markers for OUT
> parameters. Google found this, that seems related:http://bytes.com/groups/ibm-db2/452148-executing-procedure

> Here is a link to some iNav info:http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp...

>
> c) CLP6 may be a local application menu (not part of the OS). It may be
> that the library containing it is just not part of your job's library
> list.  On a Command Entry screen, try this to find which library has it:
>    DSPOBJD OBJ(*ALL/CLP6) OBJTYPE(*MENU)
> If that shows the library then try using the ADDLIBLE command to add it
> to your job's library list.
>
> --
> Karl Hanson

Thank you so much Karl. Almost all of our call center uses forms to
retrieve from and update the DB2.
I don't see any forms in iSeries/Navigator

Office Tasks > Decision Support > Interactive SQL > Work with Query
Management forms > Query Manager Report Forms

Please let me know how I can view all of the form-based queries. I.e.
run a query and display the results in a form
for update or view.

Thanks

[Karl Hanson]

Sorry but I am not familiar with Query Manager. However I think there
are folks who frequent the comp.sys.ibm.as400.misc newsgroup who know it
well. If you post your question about forms there, someone may know the
answer.

--
Karl Hanson

[soup_o...@yahoo.com]

Hi Karl: I have taken my SP question to comp.sys.ibm.as400.misc as you
have kindly suggested.

Now I want to develop a form with some fields like name, address,
tel#, etc. The purpose of the form is
to accept some input from me on the green-black/black-white terminal
and store it in a table. I know how to create a table. But don't know
how to create a form and attach it to a stored procedure. I know you
or someone can help me. So please keep in touch.

Thanks