Re: ALIASING all source members for SQL Access - Good or Bad ?

On 1/28/11 1:02 PM, Richard Schoen wrote:

I saw that a vendor package we had on our system for testing had
created an SQL ALIAS for each source member within a source file.

I'm assuming this helps them do SQL analysis on a source member as
well as access the last changed time info via an SQL query against
SYSTABLES

Looks like an interesting technique.

However, is that the best way of determining if a member has had
activity or changes or is there a better way such as running DSPOBJD
against each library source file ?

SYSTABLES seems cool because a single SQL statement can get me
access to all changed source members if they are ALIASed.

I would be interested to hear thoughts on whether this is a good
technique for accessing system-wide source member change info.

AFaIK the only "changed" date\time tracking available from SYSTABLES is the LAST_ALTERED_TIMESTAMP "FILE CHANGE TIMESTAMP" [AKA ALTEREDTS or "Altered Time Stamp"] which just provides access to the data of the DBXATS TimeStamp field from the system database cross-reference file QADBXREF which tracks the "Alter Time Stamp" of the file; only a "change" for which database cross-reference tracking is effected, such as CHGOBJOWN, CHGPF, SQL ALTER, etc.. Changes such as CHGPFM and STRSEU to "edit" the source will not see the change timestamp for the ALIAS updated in SYSTABLES; as well "source change" is different than "member change".? Thus I am not clear on how the ALIAS might be providing "last changed" information for a member.? And so I infer likely, that the intended use of each ALIAS by the vendor [software] is different than presumed\described.

There is both DSPFD TYPE(*MBR) [TYPE(*MBRLIST) also, but its use presents some potential concurrency issues] and an equivalent API QUSLMBR, or for any specific member there is both RTVMBRD and an equivalent API QUSRMBRD. There is also the "directory" provided by the database *FILE object; see 5=Display on the entry provided for WRKLNK '/QSYS.LIB/QGPL.LIB/QCLSRC.FILE' and related APIs for what information is available about those members as "files" in the directory. While there is a catalog for members [partitions], I would avoid using that file or its UDTF and write a more specific [UDTF or UDF] using the CL or some API for whatever the intended purpose, if availability via SQL is desirable.

Regards, Chuck