SYSMEMBERSTAT view

The SYSMEMBERSTAT view contains one row for every table partition or table member, including rows for program described files.

Authorization: The caller must have:
  • *EXECUTE authority to the library containing the file
  • *OBJOPR authority to the file.
If the caller does not have some data authority other than *EXECUTE to the file, partial data for the member is returned.

The following table describes the columns in the SYSMEMBERSTAT view:

Table 1. SYSMEMBERSTAT view
Column name System Column Name Data Type Description
TABLE_SCHEMA TABSCHEMA VARCHAR(128) Name of the SQL schema that contains the table.
TABLE_NAME TABNAME VARCHAR(128) Name of the table.
SYSTEM_TABLE_SCHEMA SYS_DNAME VARCHAR(10) System schema name.
SYSTEM_TABLE_NAME SYS_TNAME VARCHAR(10) System table name.
SYSTEM_TABLE_MEMBER SYS_MNAME VARCHAR(10) System member name.
SOURCE_TYPE SRCTYPE VARCHAR(10)
Nullable
 Source type of a source member.

Contains the null value if the table is not a source file.
LAST_SOURCE_UPDATE_TIMESTAMP LASTSRCUPD TIMESTAMP
Nullable
 Last source change timestamp to a source member.

Contains the null value if the table is not a source file.
TEXT_DESCRIPTION TEXT VARGRAPHIC(50) CCSID 1200
Nullable
 Text description for the member or partition.

Contains the null value if there is no text description for the member.
CREATE_TIMESTAMP CREATED TIMESTAMP Create timestamp of the member or partition.
LAST_CHANGE_TIMESTAMP LASTCHG TIMESTAMP Timestamp of the last change that occurred to the member or partition.
LAST_SAVE_TIMESTAMP LASTSAVE TIMESTAMP
Nullable
 Timestamp of the last save of the member or partition.

Contains the null value if the member or partition has never been saved.
LAST_RESTORE_TIMESTAMP LASTRST TIMESTAMP
Nullable
 Timestamp of the last restore of the member or partition.

Contains the null value if the member or partition has never been restored.
LAST_USED_TIMESTAMP LASTUSED TIMESTAMP
Nullable
 Timestamp of the last time the member or partition was used directly by an application for native record I/O or SQL operations.

Contains the null value if the member or partition has never been used.
DAYS_USED_COUNT DAYSUSED INTEGER The number of days the member or partition was used directly by an application for native record I/O or SQL operations since the last time the usage statistics were reset. If the member or partition has never been used since the last time the usage statistics were reset, contains 0.
LAST_RESET_TIMESTAMP LASTRESET TIMESTAMP
Nullable
 The timestamp of the last time the usage statistics were reset for the table. For more information see the Change Object Description (CHGOBJD) command.

Contains the null value if the member or partition's last used timestamp has never been reset.
TABLE_PARTITION TABPART VARCHAR(128) Name of the table member or partition.
PARTITION_TYPE PARTTYPE CHAR(1) The type of the table partitioning:
blank
The table is not partitioned.
H
This is data hash partitioning.
R
This is data range partitioning.
D
This is distributed database hash partitioning.
PARTITION_NUMBER PARTNBR INTEGER
Nullable
 The partition number of this partition.

Contains the null value if the table is a distributed table.
NUMBER_DISTRIBUTED_PARTITIONS DSTPARTS INTEGER
Nullable
 If the table is a distributed table, contains the total number of partitions.

Contains the null value if the table is not a distributed table.
NUMBER_PARTITIONING_KEYS NBRPKEYS INTEGER
Nullable
 The number of partitioning keys.

Contains the null value if the table is not partitioned.
PARTITIONING_KEYS PARTKEYS VARCHAR(2880)
Nullable
 The list of partitioning keys.

Contains the null value if the table is not partitioned.
LOWINCLUSIVE LOWINCL CHAR(1)
Nullable
 Indicates whether the low key value for the partition is inclusive.
N
The low key value is not inclusive.
Y
The low key value is inclusive.

Contains the null value if the table is not partitioned by range.
LOWVALUE LOWVALUE VARGRAPHIC(1024) CCSID 1200
Nullable
 A string representation of the low key value for a range partition.

Contains the null value if the table is not partitioned by range.
HIGHINCLUSIVE HIGHINCL CHAR(1)
Nullable
 Indicates whether the high key value for the partition is inclusive.
N
The high key value is not inclusive.
Y
The high key value is inclusive.

Contains the null value if the table is not partitioned by range.
HIGHVALUE HIGHVALUE VARGRAPHIC(1024) CCSID 1200
Nullable
 A string representation of the high key value for a range partition.

Contains the null value if the table is not partitioned by range.
NUMBER_ROWS CARD BIGINT Number of valid rows in the table member or partition.
NUMBER_PAGES FPAGES BIGINT Number of 64K pages in the partition's data.
OVERFLOW OVERFLOW BIGINT The estimated number of rows that have overflowed to variable length segments. If the table does not contain variable length or LOB columns, contains 0.
AVGROWSIZE AVGROWSIZE BIGINT Average length (in bytes) of a row in this table. If the table has variable length or LOB columns, contains -1.
NUMBER_DELETED_ROWS DELETED BIGINT Number of deleted rows in the table member or partition.
DATA_SIZE SIZE BIGINT Total size (in bytes) of the data space in the member or partition.
VARIABLE_LENGTH_SIZE VLSIZE BIGINT Size (in bytes) of the variable-length data space segments in the member or partition.
VARIABLE_LENGTH_SEGMENTS VLSEGMENTS BIGINT The number of variable-length data space segments in the member or partition.
COLUMN_STATS_SIZE CSTATSSIZE BIGINT Size (in bytes) of the column statistics in the member or partition.
MAINTAINED_TEMPORARY_INDEX_SIZE MTISIZE BIGINT Size (in bytes) of all maintained temporary indexes over the member or partition.
NUMBER_DISTINCT_INDEXES DISTINCTIX INTEGER The number of distinct indexes built over the member or partition. This does not include maintained temporary indexes.
OPEN_OPERATIONS OPENS BIGINT Number of full opens of the member or partition since the last IPL.
CLOSE_OPERATIONS CLOSES BIGINT Number of full closes of the member or partition since the last IPL.
INSERT_OPERATIONS INSERTS BIGINT Number of insert operations for the member or partition since the last IPL.
BLOCKED_INSERT_OPERATIONS BLKIOPS BIGINT Number of blocked insert operations for the member or partition since the last IPL.
BLOCKED_INSERT_ROWS BLKIROW BIGINT Number of rows inserted with blocked insert operations for the member or partition since the last IPL.
UPDATE_OPERATIONS UPDATES BIGINT Number of update operations for the member or partition since the last IPL.
DELETE_OPERATIONS DELETES BIGINT Number of delete operations for the member or partition since the last IPL.
CLEAR_OPERATIONS DSCLEARS BIGINT Number of clear operations (CLRPFM operations) for the member or partition since the last IPL.
COPY_OPERATIONS DSCOPIES BIGINT Number of data space copy operations (certain CPYxxx operations) for the member or partition since the last IPL.
REORGANIZE_OPERATIONS DSREORGS BIGINT Number of data space reorganize operations (non-interruptible RGZPFM operations) for the member or partition since the last IPL.
INDEX_BUILDS DSINXBLDS BIGINT Number of creates or rebuilds of indexes that reference the member or partition since the last IPL. This does not include maintained temporary indexes.
LOGICAL_READS LGLREADS BIGINT Number of logical read operations for the member or partition since the last IPL.
PHYSICAL_READS PHYREADS BIGINT Number of physical read operations for the member or partition since the last IPL.
SEQUENTIAL_READS SEQREADS BIGINT Number of sequential read operations for the member or partition since the last IPL.
RANDOM_READS RANREADS BIGINT Number of random read operations for the member or partition since the last IPL.
NEXT_IDENTITY_VALUE NEXTVALUE DECIMAL(31,0)
Nullable
 The next identity value. In some cases, this value may be an estimate.

Contains the null value if the table does not have an identity column.
KEEP_IN_MEMORY KEEPINMEM CHAR(1) Indicates whether the partition should be kept in memory:
0
No memory preference.
1
The partition should be kept in memory, if possible.
MEDIA_PREFERENCE MEDIAPREF SMALLINT Indicates the media preference of the partition:
0
No media preference.
255
The partition should be allocated on Solid State Drive (SSD), if possible.
VOLATILE VOLATILE CHAR(1) Indicates whether the table is volatile.
0
Table is not volatile.
1
Table is volatile.
PARTIAL_TRANSACTION PARTIALTX CHAR(1) Indicates whether the partition contains a partial transaction:
N
The partition does not contain a partial transaction.
Y
The partition was saved while active with a partial transaction.
A subsequent restore of the partition contains the partial transaction. The user should apply changes from the journal to complete the transaction.
R
A rollback abnormally ended prior to completion.
This left the partition with a partial set of rolled back rows.
APPLY_STARTING_RECEIVER
_LIBRARY
 APYRCVLIB VARCHAR(10)
Nullable
 The library containing the starting journal receiver.

Contains the null value if APPLY_STARTING_RECEIVER is null.
APPLY_STARTING_RECEIVER APYRCVNAME VARCHAR(10)
Nullable
 Indicates that the partition was saved and subsequently restored. If the table was journaled when the partition was saved, the starting journal receiver name will indicate the journal receiver to start with if APYJRNCHG is then used.

Contains the null value if PARTIAL_TRANSACTION has a value of R. Once an APYJRNCHG is performed, the apply information is cleared.

Example

Examine some statistical information for all members of all files in APPLIB1.

SELECT TABLE_NAME, SYSTEM_TABLE_MEMBER, NUMBER_ROWS, NUMBER_DELETED_ROWS, LAST_USED_TIMESTAMP 
  FROM QSYS2.SYSMEMBERSTAT 
  WHERE TABLE_SCHEMA = 'APPLIB1';