How to call a stored procedure in IBM System i Acc

Question

I would like to test a DB2 stored procedure running on an AS400 system.

I have the IBM System i Access for Windows installed and can run SQL commands against the DB2 database.

My question is: What is the syntax to execute a stored procedure that takes in a parameter and returns a result as an output parameter and print the value to the screen?

Just to clarify: I am not asking how to call the proc in code. I want to execute the proc and see the results in the gui tool (which is similar to SQL Enterprise Manager).

Answer 1

You should be able to run your SP like this:

DECLARE  
 usr_in  YOUR_TABLE.YOUR_COLM%TYPE; --Gets the correct type by looking at column type
 app_in  YOUR_TABLE.YOUR_OTHER_COLM%TYPE;

BEGIN
 usr_in:='some value';
 app_in:='another_value';

 YOUR_SP_NAME(usr_in, app_in);  
END;  

Or you can use EXECUTE, but it can't be dynamically prepared (not run in Java) and I think there's some other disadvantages.

EXECUTE myStoredProc(parm1, parm2, ?);

Answer 2

What you want is possible. I have done it myself many times. Unfortunaly, I'm not at the office right now so it must be from the top of my head.

1. Start System i Access
2. Go to your iSeries icons and log on to the one where your stored procedure lives
3. Go to the databases icons and connect to the correct one (you've one local and probably one or more remotes)
4. Only then, you will see the option "run SQL script" at the bottom of your screen
5. Start that option and you will see a SQL editor (editor on top, viewer/messages at the bottom)
6. Remember that you are already connected to the correct iSeries but your JDBC request will get the *LIBL of the userprofile of your connection. Therefore you must know the schema (iseries library) of your stored procedure
7. Enter "call YOURSCHEMA.YOURSTOREDPROCEDURE(?,?);" and use the menu or shortcut to run that statement. Notice that - depending on your JDBC settings (see menu) - the correct syntax may be "/" instead of ".". Also, notice that you can replace the first question mark with a value.

On an additional note,

• In iAccess, under every schema you will see icons for the tables, views and so on. Also an icon for stored procedures is available. You will see your SP there. Use the options to see the definition and so. This information includes detailed information about the parameters
• If you want to check that on your iSeries, use the system catalog (this can be done from the SQL editor too) with "select * from qsys2.sysprocedures where procedure_name (sorry, not sure about the name of this column right now) = 'YOURSTOREDPROCEDURE';"

VERY IMPORTANT: I was never able to test the SP with the SQL editor (STRSQL) on the iSeries itself. Only the iAccess SQL editor did work correctly.

Answer 3

use the keyword call and pass in the parameters.

call myStoredProc(parm1, parm2, ?);

for more details see here http://www.ibm.com/developerworks/data/library/techarticle/dm-0503melnyk/. The interesting part is Figure 5. Using the Command Editor to call an SQL procedure