REXX provides very good parsing capabilites, and since the SQL is very formalized in its allowed JOIN syntax [yet each type appearing only one way in the TEXT], it requires recognizing the SCHEMA.TABLE after any of those JOIN variations. And it is relatively easy to ignore tokens like INNER, OUTER, etc. Still, not a path to take, esp. given alternatives.

There should be nothing more than a PREPARE of the statement and then an EXECUTE, run in *SQL naming, to perform the CREATE VIEW statement from the TEXT field.?

My preference would be that all files, just like for DDS, would have their matching Source Member from which to be created; to RUNSQLSTM on the source member versus CRTLF. When created using RUNSQLSTM, the Source Member, File, and Library are identified in the Object Description details, just as with DDS created files.

Regards, Chuck

David FOXWELL wrote:
Well, thanks! Although I wouldn't say that having to parse the text
was trivial as there can be multiple joins and multiple FROM clauses.

I did want to be able to automatically recreate views when modifying
a physical file. I can go from DSPDBR to SYSVIEWDEP to SYSVIEW to get
the source to create the view, but executing that does not seem to be
an obvious matter either.

I think it would be best to store the instructions in a source file
or program and use the view's system name to identify which program
or source to use to create the view.

CRPence wrote:

Although parsing the TEXT is somewhat trivial for the TABLE names
referenced in the FROM clause, I believe what you are looking for
is SYSVIEWDEP instead of SYSVIEWS to see the relationships.

David FOXWELL wrote:
There doesn't seem to be anyway of finding the table used for the
view without looking at the TEXT field.

Maybe DSPDBR first and then join on SYSVIEWS. Is that reasonable?

Return to Archive home page | Return to MIDRANGE.COM home page