SYSIBM.SYSTABLES
The SYSIBM.SYSTABLES table contains one row for each table, view, or alias.
逻辑的概念，表本身是逻辑的概念，因此该catalog表也是以逻辑的概念，其中比较重要的字段有
name,tsname,dbname，DBID,OBID
需要强调的是，DBID是没有什么问题，但是OBID是该表在DB中的一个ID，与SYSTABLESPACE中的OBID是
不同的，那一个OBID是该TABLESPACE在DB中的ID，
还需要注意的字段有
recordlen表中记录的长度，为表中记录的最大长度，
STATSTIME
TIMESTAMP
NOT NULL WITH
DEFAULT
If RUNSTATS updated the statistics, the date and time
when the last invocation of RUNSTATS updated the
statistics. The default value is '0001-01-01.00.00.00.000000'.
For a created temporary table, the value of STATSTIME is
always the default value. This is an updatable column.
最后一次运行ranstat的时间或是默认值，
CARDF
FLOAT
NOT NULL WITH
DEFAULT -1
Total number of rows in the table or total number of
LOBs in an auxiliary table. The value is -1 if statistics
have not been gathered or the row describes a view, alias,
or created temporary table. This is an updatable column.
NPAGESF
FLOAT(8)
NOT NULL WITH
DEFAULT -1
Number of pages used by the table. The value is -1 if
statistics have not been gathered or this is an auxiliary
table. This is an updatable column.
AVGROWLEN （平均记录长度）
INTEGER
NOT NULL WITH
DEFAULT -1
Average length of rows for the tables in the table space. If
the table space is compressed, the value is the compressed
row length. If the table space is not compressed, the value
is the uncompressed row length. The value is -1 if
statistics have no t been gathered.
SYSIBM.SYSTABLESPACE table
The SYSIBM.SYSTABLESPACE table contains one row for each table space.对应的表空间的相关信息
NAME
VARCHAR(24)
NOT NULL
Name of the table space. G
CREATOR
VARCHAR(128)
NOT NULL
Authorization ID of the owner of the table space. G
DBNAME
VARCHAR(24)
NOT NULL
Name of the database that contains the table space. G
DBID
SMALLINT
NOT NULL
Internal identifier of the database which contains the table
space.
S
OBID（与表的ID意义是不同的）
SMALLINT
NOT NULL
PSID
SMALLINT
NOT NULL
Internal identifier of the table space page set descriptor. S
PARTITIONS（分区表的相关信息，如果是分区表则代表分区的数目，segment表的值为0）
SMALLINT
NOT NULL
Number of partitions of the table space; 0 if the table
space is not partitioned.
G
LOCKRULE（表空间的锁属性）
CHAR(1)
NOT NULL
Lock size of the table space:
A Any
L Large object (LOB)
P Page
R Row
S Table space
T Table
X implicitly created XML table space
G
PGSIZE（表空间的page大小）
SMALLINT
NOT NULL
Size of pages in the table space in kilobytes. G
NACTIVE（表空间中活跃的page的数目，该page一旦为格式化为row，则该值加1）
INTEGER
NOT NULL
Number of active pages in the table space. A page is
termed active if it is formatted for rows, even if it
currently contains none. The value is 0 if statistics have
not been gathered. This is an updatable column.
S
VARCHAR(24)
NOT NULL
Not used N
CLOSERULE（表空间的close属性）
CHAR(1)
NOT NULL
Whether the data sets are candidates for closure when the
limit on the number of open data sets is reached.
N No
Y Yes
G
SEGSIZE（segment表空间的属性，同partition属性相对应）
SMALLINT
NOT NULL WITH
DEFAULT
Number of pages in each segment of a segmented table
space. The value is 0 if the table space is not segmented.
STATSTIME（同table中列同名，同一张表的值肯定也是一样的）
TIMESTAMP
NOT NULL WITH
DEFAULT
If RUNSTATS updated the statistics, the date and time
when the last invocation of RUNSTATS updated the
statistics. The default value is '0001-01-01.00.00.00.000000'.
This is an updatable column.
NACTIVEF
FLOAT
NOT NULL WITH
DEFAULT -1
Number of active pages in the table space. A page is
termed active if it is formatted for rows, even if it
currently contains none. The value is -1 if statistics have
not been gathered. This is an updatable column.
AVGROWLEN
INTEGER
NOT NULL WITH
DEFAULT -1
Average length of rows for the tables in the table space or
part. If the table space or part is compressed, the value is
the compressed row length. If the table space or part is
not compressed, the value is the uncompressed row
length. The value is -1 if statistics have not been gathered.
SYSIBM.SYSTABLEPART table
The SYSIBM.SYSTABLEPART
table contains one row for each nonpartitioned table
space and one row for each partition of a partitioned table space.
PARTITION
SMALLINT
NOT NULL
Partition number; 0 if table space is not partitioned. G
TSNAME
VARCHAR(24)
NOT NULL
Name of the table space. G
DBNAME
VARCHAR(24)
NOT NULL
Name of the database that contains the table space. G
FARINDREF
INTEGER
NOT NULL
Number of rows that have been relocated far from their
original page. The value is -1 if statistics have not been
gathered. Not applicable if the table space is a LOB table
space.
S
NEARINDREF
INTEGER
NOT NULL
Number of rows that have been relocated near their
original page. The value is -1 if statistics have not been
gathered. Not applicable if the table space is a LOB table
space.
COMPRESS 表空间是否压缩的信息
CHAR(1)
NOT NULL WITH
DEFAULT
Indicates the following:
v For a table space partition, whether the COMPRESS
attribute for the partition is YES.
v For a nonpartitioned table space, whether the
COMPRESS attribute is YES for the table space.
Values for the column can be:
Y Compression is defined for the table space
blank No compression
STATSTIME
TIMESTAMP
NOT NULL WITH
DEFAULT
If RUNSTATS updated the statistics, the date and time
when the last invocation of RUNSTATS updated the
statistics. The default value is '0001-01-01.00.00.00.000000'.
TRACKMOD
CHAR(1)
NOT NULL WITH
DEFAULT
Whether to track the page modifications in the space map
pages:
N No
blank Yes
GCARDF
FLOAT
NOT NULL WITH
DEFAULT -1
Number of rows in the table space or partition, or if the
table space is a LOB table space, the number of LOBs in
the table space. The value is -1 if statistics have not been
gathered.
DSNUM
INTEGER
NOT NULL WITH
DEFAULT -1
Number of data sets. The value is -1 if statistics have not
been gathered. This is an updatable column.
G
CREATEDTS
TIMESTAMP
NOT NULL WITH
DEFAULT
Time when the partition was created. G
REORG_LR_TS
TIMESTAMP
NOT NULL WITH
DEFAULT
The time when the REORG or LOAD REPLACE utility
last occurred. The default value is '0001-01-
01.00.00.00.000000'
AVGROWLEN
INTEGER
NOT NULL WITH
DEFAULT -1
Average length of rows for the tables in the table space or
part. If the table space or part is compressed, the value is
the compressed row length. If the table space or part is
not compressed, the value is the uncompressed row
length. The value is -1 if statistics have not been gathered.
阅读(1792) | 评论(0) | 转发(0) |
