Re: PARTITION_TEXT column in QSYS2.PARTITION_STATISTICS (aka SYSPARTITIONSTAT)

Thanks Sue and Joep! That solves the mystery. My RFE referenced
QSYS2.PARTITION_STATISTICS because someone else provided me the basics of
that SQL with the lateral correlation. I failed to pay attention to the
fact that Scott Fortsie never referenced the table function in his response
in the RFE, but only the view, and I mistakenly equated the two. Thanks for
the clarification!

- Dan

On Wed, Dec 30, 2020 at 9:46 AM Sue Romano <slromano@xxxxxxxxxx> wrote:

I need to point out that you are looking at 2 different things here.

QSYS2.SYSPARTITIONSTAT is a documented view. It had the new column
PARTITION_TEXT (with a system name of LABEL) added to it as indicated in
the RFE. This is exactly as documented and described in the RFE solution.
The view is the interface intended for use by you, our end users.

QSYS2.PARTITION_STATISTICS is a table function that is used by the
SYSPARTITIONSTAT view that is NOT documented. It named the column TEXT
(for some unknown-to-me reason). We cannot change the name of this column
since there could be others (like you) who have found and are using this
table function. Changing the name of a column could break those users.
There are many table functions that we do not intend to document. They are
available for anyone to find and figure out how to use, but the names and
data types do not always align with the documented view column names.

Sue Romano
Db2 for IBM i development

Asking for a favor from the community here. Please execute the following
SQL:

( SELECT *
FROM TABLE( QSYS2.PARTITION_STATISTICS('QTEMP', 'MBR_INF') ) X
) WITH NO DATA

then run DSPFFD QTEMP/MBR_INF and search for column VOLATILE. The column
immediately following VOLATILE should be the column that either is TEXT or
PARTITION_TEXT. You might even find it called LABEL. Please let me know
how that column is defined and the OS level.

Background:

An RFE I submitted a few years ago (

http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=89070
)

was delivered for IBM i 7.1 and higher releases. Basically, the RFE asked
for a new column containing the member text for files' members be added to
the QSYS2.PARTITION_STATISTICS (aka SYSPARTITIONSTAT) catalog view.

The response from IBM in that RFE was that they added column
PARTITION_TEXT. The documentation for SYSPARTITIONSTAT (

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzcatsyspstat.htm
)

in all 7.1, 7.2, 7.3 & 7.4 renderings all reference PARTITION_TEXT.
However, on my 7.2 system, SYSPARTITIONSTAT has no column named
PARTITION_TEXT. The data is found in a column named TEXT.

I want IBM to correct this, either by changing the long column name to
PARTITION_TEXT and leaving the short (system) column name as TEXT *or*
correcting all of their documentation. Before I do that, though, I'd like
to learn from the community here on various OS levels what that column
looks like on their system.

Note: This column was added via TR for 7.1, 7.2, and 7.3. It's included

in

7.4's base. According to IBM, "Enhanced with IBM i 7.3 SF99703 Level 3

and

IBM i 7.2 SF99702 Level 14". No info given for 7.1 TR.