Re: DSPPGMREF and SQLRPG

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.