Thank you. With the example, it made much more sense and I saw what I had
wrong. However, since I am not authorized to QADBXREF, that part crashed
and burned and I am back at square 1.
Is there a good reason to have QADBXREF locked down so it can't be used in a
query?
I'm about ready to give up and do a DSPFD *MBRLIST to an outfile in QTEMP
like I used to do before the fancy stuff showed up that I can't seem to make
work. I would have been done days ago if I had gone that way to begin with.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Thursday, July 21, 2016 1:54 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Question about syspartitionstat table
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]
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
