On 02-Apr-2012 14:43 , Tom Hightower wrote:
I can probably use the embedded SQL way. I'm thinking I can set it as
once-a-day program, run after nightly IPL. If a certain threshold is
reached (say 29000 members), send an email alert. That should give us
a few days lead time to do some cleanup.

There is little reason to use the SQL catalog, or any SQL at all for the given scenario; best I can infer. Whether the desire were to test the threshold inline to some apparent run-time open processing as alluded in the OP or as part of a review\cleanup strategy as alluded in the quoted text above, the database *FILE itself will always be the target of inquiry for the number of members. The QDBRTVFD API effects that direct inquiry of the database *FILE.

While one could infer the catalogs track the members so as to avoid that direct inquiry of the *FILE, that [unless something has seriously changed] would be an incorrect inference. The SQL catalog view SYSTSTAT (SYSTABLESTAT) has the row data generated upon invocation, via an external [system] Table function, rather than simply retrieving rows of already-stored information. If more than the NUMBER_PARTITIONS (NBRPARTS) of the information from that VIEW is required, then using that VIEW may be worthwhile; and additional cleanup of a temporary output file from DSPFD would not be required either. However...

The QDBRTVFD API give a very simple means to get that same NBRPARTS information about a specific database *FILE, directly into a program, without output to a database file [as with DSPFD or features based on that] and without reading data from either a DSPFD output file or any other [e.g. catalog] file. Do not let the apparent complexity of the output from that API overwhelm; getting information from just the DBF "header" is extremely simple. Just supply the Retrieve File Description API the 400 bytes of data needed for the File Definition Header (Qdb_Qdbfh) and just directly access the Qdbfhmnum "Number of members" value when using the Format FILD0100; of course, being sure to verify the Qdbfyret and Error Code values, before trusting the value of Qdbfhmnum.
_i Retrieve Database File Description (QDBRTVFD) API i_

If being able to use the SQL for that information is desirable, then I would suggest encapsulating the API call in a UDF\UDTF. Using the catalog would probably gather significantly more data about the named database file than would be of interest; i.e. the SQL request to "select NBRPARTS into :NbrMbrs where SYS_DNAME=:LIBnm and SYS_TNAME=:DBFnm" is getting just one datum and was noted to take "quite a while to run for a large number of members in the file". To see just how much data and work [which is much like doing a DSPFD *MBRLIST and then aggregating] the VIEW entails, review the details of the catalog VIEW definition [esp. for each "in all partitions or members of the table"]:
IBM i 6.1 Information Center -> Database -> Reference -> SQL reference -> DB2 for i catalog views -> IBM i catalog tables and views
"The SYSTABLESTAT view contains one row for every table that has at least one partition or member. If the table has more than one partition or member, the statistics include all partitions and members.

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].