On 21-Jul-2016 11:53 -0500, John R. Smith, Jr. wrote:
OK, I'm obviously having a brain fart or something and my best
friend Google isn't being very helpful.
The VIEW definition for SYSPSTAT might be the simplest and most
direct model available; though, I have discussed the UDTF by name, here
on midrange, including giving examples of invocations. I have also
discussed about how, despite apparently accepting VARCHAR so as to
presumably avoid the CHAR casting I alluded earlier, the implementation
is flawed -- so despite describing incorrectly the origin for the
effective requirement to cast, AFaIK the requirement remains, either to
cast or using other means to circumvent the defect. See:
[
http://archive.midrange.com/midrange-l/201605/msg00964.html]
Can you please send me the select statement that will show me all of
the details for the files in library "IIAENC"?
What is probably most important to understand is that the arguments
that have to be provided to the UDTF are both library-name and
file-name. Thus with just two literal values provided, only the
partitions [aka members] for one file will be presented as the resultant
derived TABLE. So to get a complete list of members across a library,
requires first having|obtaining a list of files in that library.
Here is an example that would get the members for just one file:
select ps.*
from table
( partition_statistics( char('IIAENC' , 10)
, char('SOMEDBFILE', 10) ) ) as ps
That of course is hardly of value, when the desire is to cover the
list of database files in that named library, rather than just the one
database file named "SOMEDBFILE". So …
Here is _an_ example of how that specific inquiry could be handled:
select jd.*
from qadbxatr as xr /* use QADBXREF, if authorized */
join lateral
( select ps.*
from table
( partition_statistics( xr.dbxlib, xr.dbxfil ) ) as ps
) as jd
on xr.dbxlib = 'IIAENC'
-- where dbxatr='PF' /* if interested only in PF */
-- and dbxtyp='S' /* if only interested in PF-SRC */
-- order by xr.dbxfil, PARTITION_NUMBER
For some links to other discussions that might have examples or
further related discussion:
[
https://www.google.com/search?q=%22crpence%22+%22partition_statistics%22]
midrange.com
