No that's not what I suggested was using the graphical debugger in the
iSeries Navigator.
For more information read the following white paper (I did not find the link
yesterday)
Graphical debugging makes procedural
SQL debugging on i5/OS even easier
http://www-03.ibm.com/servers/enable/site/education/wp/2ae2/2ae2.pdf
BTW if you want to debug an RPG Program or procedure that is wrapped as
external Stored Procedure or User Defined Function. Just do the following:
1. STRDBG for the RPG Program/Function
2. STRSQL
3. CALL the Stored Procedure or execute a SELECT Statement where the UDF
will be called.
4. Debug your RPG as usual.
For to create or register a Stored Procedure or User Defined Function also
use iSeries Navigator.
It's much more powerful than typing everything into a source member or using
STRSQL.
If you want to save the creation script into a source member (to re-execute
in future), it can be done by positioning on the generated stored procedure
or user defined function (or any other Database type), right click and
Generate SQL.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von eftimios
pantzopoulos
Gesendet: Saturday, 20. March 2010 05:07
An: midrange List
Betreff: Re: Debugging an SQL Function
Thanks for the replies
to this issue. I'm the only person at my site who is attempting this
sort of stuff, and I don't have anyone to bounce issues around with.
There's so much material in manuals and the net that finding the exact
item of information
is worse than the proverbial needle in the haystack.
The debug issue is
really a side-show as all I?m trying to do is work out how to trap a
result from the external sub-procedure so that I can test it and if
necessary generate an SQL error with the SIGNAL statement. But now I
want to get to the bottom of
the function debug problem, while still wanting to know how to trap and
process the sub-procedure return value in the body of the SQL function.
I did the R&D by
using a procedure. This is the procedure. It?s just a hack. Y was
originally used as an OUT parameter:
0000.10 drop procedure
evedev/mxpplay10;
0001.00 create procedure
evedev/mxpplay10(
0001.01 in x
char(1))
0003.00 language
sql
0003.01
BEGIN
0004.00 DECLARE SQLSTATE CHAR(5) DEFAULT
'00000';
0005.00 DECLARE SQLCODE INT DEFAULT
0;
0005.01 DECLARE Y
CHAR(2);
0005.02 CASE
X
0005.03 WHEN 'A' THEN SET Y=
'OK';
0005.04 WHEN 'B' THEN SET Y=
'OK';
0005.05 WHEN 'C' THEN SET Y=
'OK';
0005.06
ELSE
0007.00 SIGNAL SQLSTATE '70001' SET
message_text= 'Input Illegal';
0008.00 END
CASE;
0009.00 END
The procedure is just a
simple exercise to play with the debug process I mentioned in the
initial email.
When I use the
following runsqlstm:
===> RUNSQLSTM SRCFILE(EVEDEV/QRPGSRC)
SRCMBR(MXPPLAY10) COMMIT(*NONE) ERRLVL(30) DBGVIEW(*SOURCE)
I get the following in
the job log:
File QSQLSRC in library QTEMP already
exists.
Member MXPPLAY10 already exists in file
QSQLSRC in library QTEMP.
File QSQDSRC created in library
EVEDEV.
File QSQDSRC in library EVEDEV
changed.
Member MXPPLAY10 added to file QSQDSRC in
EVEDEV.
Member MXPPLAY10 added to file QSQLT00000
in QTEMP.
Ownership of object MXPPLAY10 in EVEDEV
type *MODULE changed.
Module MXPPLAY10 was created in library
EVEDEV on 20/03/10 at 13:53:38.
Ownership of object MXPPLAY10 in EVEDEV
type *PGM changed.
Program MXPPLAY10 created in library
EVEDEV.
Object MXPPLAY10 in EVEDEV type *MODULE
deleted.
I found the C source
in qtemp/qsqlsrc.
I also found the CLE
program object in evedev/mxpplay10.
All of the above seems
to fit with Chuck?s response.
I put the following
function script through the same RUNSQLSTM as above. Again this is a
hack to prove a concept, and A00720 won?t be the final name of the
function! Is Language RPGLE? I thought Language had to refer to the
language used in the root code?
Have I made some fundamental mistake in the script?:
0087.00 DROP FUNCTION
EVEDEV/A00720;
0088.00
0089.00 CREATE FUNCTION
EVEDEV/A00720(
0090.00
VARCHAR(2),
0091.00
VARCHAR(3),
0092.00
NUMERIC(7,0),
0093.00
DECIMAL(8,0),
0094.00
DECIMAL(3,0))
0095.00 RETURNS CHAR(78) CAST
FROM VARCHAR(78)
0097.01 -- SET OPTION DBGVIEW=
*SOURCE
0098.00 LANGUAGE
RPGLE
0099.00
DETERMINISTIC
0100.00 NO
SQL
0101.00 RETURNS NULL ON NULL
INPUT
0102.00 NO EXTERNAL ACTION ALLOW
PARALLEL
0102.01 PARAMETER STYLE GENERAL
0103.00 --SIMPLE CALL
0104.00 EXTERNAL NAME
'EVEDEV/#CMSVP(GETCLMAGEXS)'
RUNSQLSTM SRCFILE(EVEDEV/QRPGSRC)
SRCMBR(a00720) COMMIT(*NONE) ERRLVL(30)
DBGVIEW(*SOURCE)
I get nothing in the job log.
The spool file tells
me the function was created:
SQL7998 0 1 Position 1 DROP
FUNCTION statement complete.
SQL7997 0 3 Position 1 Function
A00720 was created in EVEDEV.
Message
Summary
Total Info Warning Error
Severe Terminal
2 2 0
0 0 0
00 level severity errors found in source
Trying to implement
Birgitta?s approach:
Never having debugged
an SQL script though Ops navigator I of course went to my browser. I
found the following guide on debugging SQL scripts through Ops
Navigator. I hope that?s what Birgitta intended:
http://www-01.ibm.com/support/docview.wss?uid=nas1783fbaed2201c16b8625738d00
022de5
I got as far as step
3, but failed on:
CRTDUPOBJ CL: CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS)
OBJTYPE(*FILE) TOLIB(MYLIB) DATA(*NO);
As I don?t have
authority under my QPGMR sign on.
QAQQINI is present in
two libraries and is owned as follows:
QSYS -
owner=QSYS
QUSRSYS -
owner=QSECOFR
Is this the way it
should be?
_________________________________________________________________
Browse profiles for FREE! Meet local singles online.
http://clk.atdmt.com/NMN/go/150855801/direct/01/
midrange.com
