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_
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/apis/qdbrtvfd.htm
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
_i SYSTABLESTAT i_
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcatsyststat.htm
"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
midrange.com
