RE: Debugging Stored Procedures

["ibm" <ibm@xxxxxxxxxx>]

Elvis, I think I got it.  Since I have three overloaded TEST_DBG procs
with specific name: TEST_DBG_1, TEST_DBG_2, TEST_DBG_3, my call has to
be to TEST_DBG with the correct number of parameters.

e.g

CALL AMFLIB.TEST_DBG(1300,?);
CALL AMFLIB.TEST_DBG(1300);
CALL AMFLIB.TEST_DBG;

Thanks a lot.  This needs to be included in the revised redbook!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Tuesday, June 30, 2009 2:27 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Debugging Stored Procedures

First of all, make sure your stored procedure has debug view
information. To
accomplish that, create it by adding the keywords:
     SET OPTION DBGVIEW=*SOURCE   
just before the BEGIN keyword.

To debug it, you have to click on Run->Debugger first (or hit key combo
Ctrl-D).
Pick a program you're debugging, which in your case is specific name
TEST_DBG_3.
Put a breakpoint on first line (I usually double-click on the line of
interest).
Switch to the Run SQL Script and run the CALL ... command.
Back in the debugger there is a Resume button (Play button).
You should hit your breakpoint now.

The redbook should have clearer instructions on the steps involved (and
possibly more straightforward from what I've described).

BTW, common interpretation is that interactive SQL is the STRSQL
green-screen interface, not iNav's Run SQL Script interface. I strongly
recommend you stick with iNav for this testing as STRSQL cannot display
stored procedure result sets. There is a way to debug stored procedures
using green screen, but let's not go there unless you have to.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: Debugging Stored Procedures

Elvis,
What you suggest runs ok in iNav 'Run SQL Scripts', but *does not*
invoke the debugger.

What am I missing? Is there a way to STRDBG PGM(xxx) from iNav?
Page 45 of the below mentioned redbook states 
"return to your iSeries session and issue the following SQL CALL
statement using either RUNSQLSTM command or interactive SQL"

I am assuming that "interactive SQL" is the iNav 'Run SQL Scripts' or
any SQL-agent/client such as dotNET.


Also note that I created multiple TEST_DBG stored procedures just with
different SPECIFIC NAMES for overloading purposes.

So, calling it from 'Run SQL Script' in iNav, I can call :
SET PATH = AMFLIB;
CALL TEST_DBG(1300);

The stored proc grammar is incomplete at this point, I need to get this
debugger running 1st.