Re: Viewing the resultant SQL in an RPG program

["Gene Burns" <burns.gene@xxxxxxxxx>]

I created a copy of source file QSQLTEMP1 in my library and changed the
CRTSQLRPGLI command TOSRCFILE option to point to that source file when
compiling an SQLRPGLE program.  I also changed the DBGVIEW option to
*SOURCE, *LIST also works.  Debug can then pick up this source and you can
see the SQL fields which are inserted by the precompiler.  Debug can then be
used to view the values used in or retrieved by the SQL statements.

Here is an example that I am currently working on.

     c*exec sql
     c* Insert into CLM520P2 (
     c* Select trcmyr, trcmyc, trlcvg, trlsyy, trrctp, trpsmm, trpsyy,
tramnt,
     c*        'L',
     c*   Case trpctp
     c*     When 'AD' Then 'AA'
     c*     When 'BA' Then 'AA'
     c*     Else trpctp
     c*     end
     c* From hclmtran, hlcvgcd
     c* Where trlsyy between :stryear and :endyear and
     c*       (trpsyy between :stryear and (:endyear - 1) or
     c*       (trpsyy = :endyear and trpsmm <= :cutoff)) and
     c*       trpctp in ('AD','BA','CP','GK','PA','PP','P2','TP','T8') and
     c*       trlcvg = lccd2g and gpcd2g in ('A','L'))
     c*end-exec
     C                   EVAL      SQL_00005  =
STRYEAR                         SQL
     C                   EVAL      SQL_00006  =
ENDYEAR                         SQL
     C                   EVAL      SQL_00007  =
STRYEAR                         SQL
     C                   EVAL      SQL_00008  =
ENDYEAR                         SQL
     C                   EVAL      SQL_00009  =
ENDYEAR                         SQL
     C                   EVAL      SQL_00010  =
CUTOFF                          SQL
     C                   Z-ADD     -4
SQLER6                         SQL   3
     C                   CALL
'QSQROUTE'                                   SQL
     C                   PARM
SQLCA                          SQL
     C                   PARM
SQL_00000                      SQL


Gene

On 9/6/07, Pete Helgren <Pete@xxxxxxxxxx> wrote:
> How are folks debugging SQL statements that have host variables that are
> resolved at runtime?  Is there a way to debug a RPG program with
> embedded SQL so you can see the "resolved" statement as it is passed to
> DB2/400?  I have an RPG program with embedded SQL that is returning no
> rows but when I run a statement that should be similar (the "should be"
> is the catch!) in interactive SQL I do get rows returned.
>
> I'd like to "see" what DB2/400 sees when the SQL statement is
> processed.  Any way to do this ?
>
> Pete Helgren
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.