Re: DSPPGMREF and SQLRPG

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Just to give an extreme but certainly possible example, let's say that 
the program is trying to get a count of active records in FILE_Y.  Let's 
assume that prior to the change, all records were active, but that the 
change added a flag
ACTIVE that indicated whether the record was active or not.

Now any program that counted the number of records would have to use the 
statement:

  SELECT COUNT(*) FROM FILE_Y WHERE ACTIVE = 'Y'

There is no way to programmatically determine that the dynamic SQL 
statement in the original post needs to be changed.  Well, there is a 
way, but it ain't easy.  And it gets a lot harder if, for example, you 
were to use a parameter:

  wSQL = 'SELECT COUNT ( * )  FROM ' + filename;

This isn't meant to be a knock against SQL, but just a cautionary note 
that the softer you code your programs the harder it is to perform 
impact analysis.  This doesn't just hold for SQL; you can, with some 
effort, have the same problems in non-SQL programs using overrides and 
internally-described files.  It's just a bit easier to get yourself in 
trouble with dynamic SQL.

Joe

> This particular program would not be impacted by the change physical file.
> This program in particular is just getting a record count.
>   
> >    wSQL = 'SELECT COUNT ( * )  FROM   FILE_Y'
> >    EXEC SQL
> >        PREPARE S1 FROM : wSQL;
> >
> > DSPPGMREF gives no indacation that the program uses FILE_Y.
> >