How to call a stored procedure in IBM System i Access for Windows GUI Tool

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

Expiration

Answer 1

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

Waken

? is an output parameter – Jag

Downvote because no explanation and the link doesn't talk about SPs. How would I get return values? – Tinderbox

@PhilipRego Did you look at Figure 5 and the paragraph right before it? It specifically tells you how to call a stored procedure from the GUI tool. – Waken

Doubt most people can even get access to that UI tool. You can't call them that way without a UI. You need to declare your variables. – Tinderbox

@PhilipRego if you have a fully licensed IBM System i (sometimes referred to as iSeries or AS400) sitting around, chances are you also can get IBM System i Access for Windows installed on you system. If you don't like that tool, you can always download IBM Data Studio (free tool) from the IBM webpage. That tool is able to connect to the iSeries. – Waken

I have the full install of IBM i Access for Windows 7.1 installed on my PC, but it doesn't include that GUI. :( I normally use System i Navigator to run SQL queries, but it doesn't like call myStoredProc(parm1, parm2, ?); syntax. – Enter

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.

Dilemma

Thanks - I'm new to DB2, and have been trying to figure this out for three weeks. I hadn't thought to use ? (was trying to use declare variable as I would in SQL Server). – Astern

Answer 3

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, ?);

Tinderbox