ITPub博客

首页 > 数据库 > 数据库开发技术 > DB2查询表注释和列注释

DB2查询表注释和列注释

原创 数据库开发技术 作者:studyingeveryday 时间:2018-12-07 12:01:06 0 删除 编辑
--查看表中表注释
SELECT
    VARCHAR(TABSCHEMA,10) AS TABSCHEMA, --模式名
    VARCHAR(TABNAME,50)   AS TABNAME, --表名
    TYPE, --类型(T: 表, V:视图, N:昵称)
    CARD, --记录数(最新一次RUNSTATS统计)
    DEC(AVGROWCOMPRESSIONRATIO,5,2) AS COMPRESS_RATIO, --压缩比例
    LASTUSED, --最近一次访问日期(增删改查)
    CREATE_TIME, --表的创建时间
    TBSPACE, --所属表空间(非PARTITION表)
    REMARKS --表的注释
FROM
    SYSCAT.TABLES
WHERE
    TABNAME = 'TABLENAME'
AND TABSCHEMA = 'TABLESCHEMA';
--查看表中列的注释
SELECT
    T.TABSCHEMA, --模式名
    T.TABNAME, --表名
    T.COLNAME, --字段名
    T.TYPENAME, --字段类型
    T.LENGTH, --字段长度
    T.SCALE, --精度
    T.DEFAULT, --默认值
    T.NULLS, --是否为空
    T.REMARKS --用户注释
FROM
    SYSCAT.COLUMNS T
WHERE
    T.TABSCHEMA = 'TABLESCHEMA'
AND T.TABNAME = 'TABLENAME'

下面附带DB2官网表结构:

表 SYSCAT.COLUMNS

DB2 10.5 for Linux, UNIX, and Windows


Each row represents a column defined for a table, view, or nickname.

Table 1. SYSCAT.COLUMNS Catalog View
Column Name Data Type Nullable Description
TABSCHEMA VARCHAR (128)
Schema name of the table, view, or nickname that contains the column.
TABNAME VARCHAR (128)
Unqualified name of the table, view, or nickname that contains the column.
COLNAME VARCHAR (128)
Name of the column.
COLNO SMALLINT
Number of this column in the table (starting with 0).
TYPESCHEMA VARCHAR (128)
Schema name of the data type for the column.
TYPENAME VARCHAR (128)
Unqualified name of the data type for the column.
LENGTH INTEGER
Maximum length of the data; 0 for distinct types. The LENGTH column indicates precision for DECIMAL fields, and indicates the number of bytes of storage required for decimal floating-point columns; that is, 8 and 16 for DECFLOAT(16) and DECFLOAT(34), respectively.
SCALE SMALLINT
Scale if the column type is DECIMAL or number of digits of fractional seconds if the column type is TIMESTAMP; 0 otherwise.
TYPESTRINGUNITS VARCHAR (11) Y In a Unicode database, the string units that apply to a character string or graphic string data type. Otherwise, the null value.
STRINGUNITSLENGTH INTEGER Y In a Unicode database, the declared number of string units for a character string or graphic string data type. Otherwise, the null value.
DEFAULT CLOB (64K) Y Default value for the column of a table expressed as a constant, special register, or cast-function appropriate for the data type of the column. Can also be the keyword NULL. Values might be converted from what was specified as a default value. For example, date and time constants are shown in ISO format, cast-function names are qualified with schema names, and identifiers are delimited. Null value if a DEFAULT clause was not specified or the column is a view column.
NULLS CHAR (1)
Nullability attribute for the column.
  • N = Column is not nullable

  • Y = Column is nullable

The value can be 'N' for a view column that is derived from an expression or function. Nevertheless, such a column allows null values when the statement using the view is processed with warnings for arithmetic errors.
CODEPAGE SMALLINT
Code page used for data in this column; 0 if the column is defined as FOR BIT DATA or is not a string type.
COLLATIONSCHEMA VARCHAR (128) Y For string types, the schema name of the collation for the column; the null value otherwise.
COLLATIONNAME VARCHAR (128) Y For string types, the unqualified name of the collation for the column; the null value otherwise.
LOGGED CHAR (1)
Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise.
  • N = Column is not logged

  • Y = Column is logged

COMPACT CHAR (1)
Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise.
  • N = Column is not compacted

  • Y = Column is compacted in storage

COLCARD BIGINT
Number of distinct values in the column; -1 if statistics are not collected; -2 for inherited columns and columns of hierarchy tables.
HIGH2KEY 1 VARCHAR (254) Y Second-highest data value. Representation of numeric data changed to character literals. Empty if statistics are not collected. Empty for inherited columns and columns of hierarchy tables.
LOW2KEY 1 VARCHAR (254) Y Second-lowest data value. Representation of numeric data changed to character literals. Empty if statistics are not collected. Empty for inherited columns and columns of hierarchy tables.
AVGCOLLEN INTEGER
Average space in bytes when the column is stored in database memory or a temporary table. For LOB data types that are not inlined, LONG data types, and XML documents, the value used to calculate the average column length is the length of the data descriptor. An extra byte is required if the column is nullable; -1 if statistics have not been collected; -2 for inherited columns and columns of hierarchy tables. Note: The average space required to store the column on disk may be different than the value represented by this statistic.
KEYSEQ SMALLINT Y The column's numerical position within the table's primary key. The null value for columns of subtables and hierarchy tables.
PARTKEYSEQ SMALLINT Y The column's numerical position within the table's distribution key; 0 or the null value if the column is not in the distribution key. The null value for columns of subtables and hierarchy tables.
NQUANTILES SMALLINT
Number of quantile values recorded in SYSCAT.COLDIST for this column; -1 if statistics are not gathered; -2 for inherited columns and columns of hierarchy tables.
NMOSTFREQ SMALLINT
Number of most-frequent values recorded in SYSCAT.COLDIST for this column; -1 if statistics are not gathered; -2 for inherited columns and columns of hierarchy tables.
NUMNULLS BIGINT
Number of null values in the column; -1 if statistics are not collected.
TARGET_TYPESCHEMA VARCHAR (128) Y Schema name of the target row type, if the type of this column is REFERENCE; null value otherwise.
TARGET_TYPENAME VARCHAR (128) Y Unqualified name of the target row type, if the type of this column is REFERENCE; null value otherwise.
SCOPE_TABSCHEMA VARCHAR (128) Y Schema name of the scope (target table), if the type of this column is REFERENCE; null value otherwise.
SCOPE_TABNAME VARCHAR (128) Y Unqualified name of the scope (target table), if the type of this column is REFERENCE; null value otherwise.
SOURCE_TABSCHEMA VARCHAR (128) Y For columns of typed tables or views, the schema name of the table or view in which the column was first introduced. For non-inherited columns, this is the same as TABSCHEMA. The null value for columns of non-typed tables and views.
SOURCE_TABNAME VARCHAR (128) Y For columns of typed tables or views, the unqualified name of the table or view in which the column was first introduced. For non-inherited columns, this is the same as TABNAME. The null value for columns of non-typed tables and views.
DL_FEATURES CHAR (10) Y This column is no longer used and will be removed in a future release.
SPECIAL_PROPS CHAR (8) Y Applies to REFERENCE type columns only; blanks otherwise. Each byte position is defined as follows:
  • 1 = Object identifier (OID) column ('Y' for yes; 'N' for no)

  • 2 = User-generated or system-generated ('U' for user; 'S' for system)

Bytes 3 through 8 are reserved for future use.
HIDDEN CHAR (1)
Type of hidden column.
  • I = Column is defined as IMPLICITLY HIDDEN

  • S = System-managed hidden column

  • Blank = Column is not hidden

INLINE_LENGTH INTEGER
Maximum size in bytes of the internal representation of an instance of an XML document, a structured type, or a LOB data type, that can be stored in the base table; 0 when not applicable.
PCTINLINED SMALLINT
Percentage of inlined data for columns with VARCHAR, VARGRAPHIC, LOB, or XML data types. -1 if statistics have not been collected or the column data type does not support storing data outside the row. Also -1 for VARCHAR and VARGRAPHIC column if the table is organized by column or the table is organized by row and the row size of the table does not exceed the maximum record length for the page size of the table space.
IDENTITY CHAR (1)
  • N = Not an identity column

  • Y = Identity column

ROWCHANGETIMESTAMP CHAR (1)
  • N = Not a row change timestamp column

  • Y = Row change timestamp column

GENERATED CHAR (1)
Type of generated column.
  • A = Column value is always generated

  • D = Column value is generated by default

  • Blank = Column is not generated

TEXT CLOB (2M) Y For columns defined as generated as expression, this field contains the text of the generated column expression, starting with the keyword AS.
COMPRESS CHAR (1)
  • O = Compress off

  • S = Compress system default values

AVGDISTINCTPERPAGE DOUBLE Y For future use.
PAGEVARIANCERATIO DOUBLE Y For future use.
SUB_COUNT SMALLINT
Average number of sub-elements in the column. Applicable to character string columns only.
SUB_DELIM_LENGTH SMALLINT
Average length of the delimiters that separate each sub-element in the column. Applicable to character string columns only.
AVGCOLLENCHAR INTEGER
Average number of characters (based on the collation in effect for the column) required for the column; -1 if the data type of the column is long, LOB, or XML or if statistics have not been collected; -2 for inherited columns and columns of hierarchy tables.
IMPLICITVALUE 2 VARCHAR (254) Y For a column that was added to a table after the table was created, stores the default value at the time the column was added. For a column that was defined when the table was created, stores the null value.
SECLABELNAME VARCHAR (128) Y Name of the security label that is associated with the column if it is a protected column; the null value otherwise.
ROWBEGIN CHAR (1)
  • N = Not a row begin column

  • Y = Row begin column

ROWEND CHAR (1)
  • N = Not a row end column

  • Y = Row end column

TRANSACTIONSTARTID CHAR (1)
  • N = Not a transaction start ID column

  • Transaction start ID column

QUALIFIER VARCHAR (128) Y Reserved for future use.
FUNC_PATH CLOB (2K) Y Reserved for future use.
PCTENCODED SMALLINT
Percentage of values that are encoded as a result of compression for a column in a column-organized table; -1 if the table is not organized by column or if statistics are not collected; -2 for inherited columns and columns of hierarchy tables.
REMARKS VARCHAR (254) Y User-provided comments, or the null value.

Note

  1. In the catalog view, the values of HIGH2KEY and LOW2KEY are always shown in the database code page and can contain substitution characters. However, the statistics are gathered internally in the code page of the column's table, and will therefore use actual column values when applied during query optimization.

  2. Attaching a data partition is allowed unless IMPLICITVALUE for a specific column is a non-null value for both the source column and the target column, and the values do not match. In this case, you must drop the source table and then re-create it. A column can have a non-null value in the IMPLICITVALUE field if one of the following conditions is met:To avoid these inconsistencies during non-migration scenarios, it is recommended that you always create the tables that you are going to attach with all the columns already defined. That is, never use the ALTER TABLE statement to add columns to a table before attaching it.

    • The column is created as the result of an ALTER TABLE...ADD COLUMN statement

    • The IMPLICITVALUE field is propagated from a source table during attach

    • The IMPLICITVALUE field is inherited from a source table during detach

    • The IMPLICITVALUE field is set during database upgrade from Version 8 to Version 9, where it is determined to be an added column, or might be an added column. If the database is not certain whether the column is added or not, it is treated as added. An added column is a column that was created as the result of an ALTER TABLE...ADD COLUMN statement.

表 SYSCAT.TABLES

DB2 10.5 for Linux, UNIX, and Windows


Each row represents a table, view, alias, or nickname. Each table or view hierarchy has one additional row representing the hierarchy table or hierarchy view that implements the hierarchy. Catalog tables and views are included.

Table 1. SYSCAT.TABLES Catalog View
Column Name Data Type Nullable Description
TABSCHEMA VARCHAR (128)
Schema name of the object.
TABNAME VARCHAR (128)
Unqualified name of the object.
OWNER VARCHAR (128)
Authorization ID of the owner of the table, view, alias, or nickname.
OWNERTYPE CHAR (1)
  • S = The owner is the system

  • U = The owner is an individual user

TYPE CHAR (1)
Type of object.
  • A = Alias

  • G = Created temporary table

  • H = Hierarchy table

  • L = Detached table

  • N = Nickname

  • S = Materialized query table

  • T = Table (untyped)

  • U = Typed table

  • V = View (untyped)

  • W = Typed view

STATUS CHAR (1)
Status of the object.
  • C = Set integrity pending

  • N = Normal

  • X = Inoperative

BASE_TABSCHEMA VARCHAR (128) Y If TYPE = 'A', contains the schema name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise.
BASE_TABNAME VARCHAR (128) Y If TYPE = 'A', contains the unqualified name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise.
ROWTYPESCHEMA VARCHAR (128) Y Schema name of the row type for this table, if applicable; null value otherwise.
ROWTYPENAME VARCHAR (128) Y Unqualified name of the row type for this table, if applicable; null value otherwise.
CREATE_TIME TIMESTAMP
Time at which the object was created.
ALTER_TIME TIMESTAMP
Time at which the object was last altered.
INVALIDATE_TIME TIMESTAMP
Time at which the object was last invalidated.
STATS_TIME TIMESTAMP Y Time at which any change was last made to recorded statistics for this object. The null value if statistics are not collected.
COLCOUNT SMALLINT
Number of columns, including inherited columns (if any).
TABLEID SMALLINT
Internal logical object identifier.
TBSPACEID SMALLINT
Internal logical identifier for the primary table space for this object.
CARD BIGINT
Total number of rows in the table; -1 if statistics are not collected.
NPAGES BIGINT
Total number of pages on which the rows of the table exist; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
MPAGES BIGINT
Total number of pages for table metadata. Non-zero only for a table that is organized by column; -1 for a view, an alias, or if statistics are not collected; -2 for subtables or hierarchy tables.
FPAGES BIGINT
Total number of pages; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
OVERFLOW BIGINT
Total number of overflow records in the table; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
TBSPACE VARCHAR (128) Y Name of the primary table space for the table. If no other table space is specified, all parts of the table are stored in this table space. The null value for aliases, views, and partitioned tables.
INDEX_TBSPACE VARCHAR (128) Y Name of the table space that holds all indexes created on this table. The null value for aliases, views, and partitioned tables, or if the INDEX IN clause was omitted or specified with the same value as the IN clause of the CREATE TABLE statement.
LONG_TBSPACE VARCHAR (128) Y Name of the table space that holds all long data (LONG or LOB column types) for this table. The null value for aliases, views, and partitioned tables, or if the LONG IN clause was omitted or specified with the same value as the IN clause of the CREATE TABLE statement.
PARENTS SMALLINT Y Number of parent tables for this object; that is, the number of referential constraints in which this object is a dependent.
CHILDREN SMALLINT Y Number of dependent tables for this object; that is, the number of referential constraints in which this object is a parent.
SELFREFS SMALLINT Y Number of self-referencing referential constraints for this object; that is, the number of referential constraints in which this object is both a parent and a dependent.
KEYCOLUMNS SMALLINT Y Number of columns in the primary key.
KEYINDEXID SMALLINT Y Index identifier for the primary key index; 0 or the null value if there is no primary key.
KEYUNIQUE SMALLINT
Number of unique key constraints (other than the primary key constraint) defined on this object.
CHECKCOUNT SMALLINT
Number of check constraints defined on this object.
DATACAPTURE CHAR (1)
  • L = Table participates in data replication, including replication of LONG VARCHAR and LONG VARGRAPHIC columns

  • N = Table does not participate in data replication

  • Y = Table participates in data replication, excluding replication of LONG VARCHAR and LONG VARGRAPHIC columns

CONST_CHECKED CHAR (32)
  • Byte 1 represents foreign key constraint.

  • Byte 2 represents check constraint.

  • Byte 5 represents materialized query table.

  • Byte 6 represents generated column.

  • Byte 7 represents staging table.

  • Byte 8 represents data partitioning constraint.

  • Other bytes are reserved for future use.

Possible values are:
  • F = In byte 5, the materialized query table cannot be refreshed incrementally. In byte 7, the content of the staging table is incomplete and cannot be used for incremental refresh of the associated materialized query table.

  • N = Not checked

  • U = Checked by user

  • W = Was in 'U' state when the table was placed in set integrity pending state

  • Y = Checked by system

PMAP_ID SMALLINT Y Identifier for the distribution map that is currently in use by this table (the null value for aliases or views).
PARTITION_MODE CHAR (1)
Indicates how data is distributed among database partitions in a partitioned database system.
  • H = Hashing

  • R = Replicated across database partitions

  • Blank = No database partitioning

LOG_ATTRIBUTE CHAR (1)
  • Always 0. This column is no longer used.

PCTFREE SMALLINT
Percentage of each page to be reserved for future inserts.
APPEND_MODE CHAR (1)
Controls how rows are inserted into pages.
  • N = New rows are inserted into existing spaces, if available

  • Y = New rows are appended to the end of the data

REFRESH CHAR (1)
Refresh mode.
  • D = Deferred

  • I = Immediate

  • O = Once

  • Blank = Not a materialized query table

REFRESH_TIME TIMESTAMP Y For REFRESH = 'D' or 'O', time at which the data was last refreshed (REFRESH TABLE statement); null value otherwise.
LOCKSIZE CHAR (1)
Indicates the preferred lock granularity for tables that are accessed by data manipulation language (DML) statements. Applies to tables only. Possible values are:
  • I = Block insert

  • R = Row

  • T = Table

  • Blank = Not applicable

VOLATILE CHAR (1)
  • C = Cardinality of the table is volatile

  • Blank = Not applicable

ROW_FORMAT CHAR (1)
Not used.
PROPERTY VARCHAR (32)
Properties for a table. A single blank indicates that the table has no properties. The following is position within string, value, and meaning:
  • 1, Y = User maintained materialized query table

  • 2, Y = Staging table

  • 3, Y = Propagate immediate

  • 11, Y = Nickname that will not be cached

  • 13, Y = Statistical view

  • 19, Y = Statistical view for an index with an expression-based key

  • 20, Y = Column-organized table

  • 21, Y = Synopsis table

  • 23, Y = Shadow table (materialized query table maintained by replication)

STATISTICS_PROFILE CLOB (10M) Y RUNSTATS command used to register a statistical profile for the object.
COMPRESSION CHAR (1)
  • B = Both value and row compression are enabled

  • N = No compression is enabled; a row format that does not support compression is used

  • R = Row compression is enabled; a row format that supports compression might be used

  • V = Value compression is enabled; a row format that supports compression is used

  • Blank = Not applicable

ROWCOMPMODE CHAR (1)
Row compression mode for the table.
  • A = ADAPTIVE

  • S = STATIC

  • Blank = Row compression is not enabled

ACCESS_MODE CHAR (1)
Access restriction state of the object. These states only apply to objects that are in set integrity pending state or to objects that were processed by a SET INTEGRITY statement. Possible values are:
  • D = No data movement

  • F = Full access

  • N = No access

  • R = Read-only access

CLUSTERED CHAR (1) Y
  • T = Table is clustered by insert time

  • Y = Table is clustered by dimensions (even if only by one dimension)

  • Null value = Table is not clustered by dimensions or insert time

ACTIVE_BLOCKS BIGINT
Total number of active blocks in the table, or -1. Applies to multidimensional clustering (MDC) tables or insert time clustering (ITC) tables only.
DROPRULE CHAR (1)
  • N = No rule

  • R = Restrict rule applies on drop

MAXFREESPACESEARCH SMALLINT
Reserved for future use.
AVGCOMPRESSEDROWSIZE SMALLINT
Average length (in bytes) of compressed rows in this table; -1 if statistics are not collected.
AVGROWCOMPRESSIONRATIO REAL
For compressed rows in the table, this is the average compression ratio by row; that is, the average uncompressed row length divided by the average compressed row length; -1 if statistics are not collected.
AVGROWSIZE SMALLINT
Average length (in bytes) of both compressed and uncompressed rows in this table; -1 if statistics are not collected.
PCTROWSCOMPRESSED REAL
Compressed rows as a percentage of the total number of rows in the table; -1 if statistics are not collected.
LOGINDEXBUILD VARCHAR (3) Y Level of logging that is to be performed during create, re-create, or reorganize index operations on the table.
  • OFF = Index build operations on the table will be logged minimally

  • ON = Index build operations on the table will be logged completely

  • Null value = Value of the  logindexbuild  database configuration parameter will be used to determine whether or not index build operations are to be completely logged

CODEPAGE SMALLINT
Code page of the object. This is the default code page used for all character columns, triggers, check constraints, and expression-generated columns.
COLLATIONSCHEMA VARCHAR (128)
Schema name of the collation for the table.
COLLATIONNAME VARCHAR (128)
Unqualified name of the collation for the table.
COLLATIONSCHEMA_ORDERBY VARCHAR (128)
Schema name of the collation for ORDER BY clauses in the table.
COLLATIONNAME_ORDERBY VARCHAR (128)
Unqualified name of the collation for ORDER BY clauses in the table.
ENCODING_SCHEME CHAR (1)
  • A = CCSID ASCII was specified

  • U = CCSID UNICODE was specified

  • Blank = CCSID clause was not specified

PCTPAGESSAVED SMALLINT
The approximate percentage of pages saved in a row-organized table as a result of row compression.  For a column-organized table, the estimate is based on the number of data pages needed to store the table in uncompressed row organization. -1 if statistics are not collected.
LAST_REGEN_TIME TIMESTAMP Y Time at which any views or check constraints on the table were last regenerated.
SECPOLICYID INTEGER
Identifier for the security policy protecting the table; 0 for non-protected tables.
PROTECTIONGRANULARITY CHAR (1)
  • B = Both column- and row-level granularity

  • C = Column-level granularity

  • R = Row-level granularity

  • Blank = Non-protected table

AUDITPOLICYID INTEGER Y Identifier for the audit policy.
AUDITPOLICYNAME VARCHAR (128) Y Name of the audit policy.
AUDITEXCEPTIONENABLED CHAR (1)
Reserved for future use.
DEFINER 1 VARCHAR (128)
Authorization ID of the owner of the table, view, alias, or nickname.
ONCOMMIT CHAR (1)
Specifies the action taken on the created temporary table when a COMMIT operation is performed.
  • D = Delete rows

  • P = Preserve rows

  • Blank = Table is not a created temporary table

LOGGED CHAR (1)
Specifies whether the created temporary table is logged.
  • N = Not logged

  • Y = Logged

  • Blank = Table is not a created temporary table

ONROLLBACK CHAR (1)
Specifies the action taken on the created temporary table when a ROLLBACK operation is performed.
  • D = Delete rows

  • P = Preserve rows

  • Blank = Table is not a created temporary table

LASTUSED DATE
Date when the table was last used by any DML statement or the LOAD command. This column is not updated for an alias, created temporary table, nickname, or view. This column is not updated when the table is used on an HADR standby database. The default value is '0001-01-01'. This value is updated asynchronously not more than once within a 24 hour period and might not reflect usage within the last 15 minutes.
CONTROL CHAR (1)
Access control that is enforced for the table
  • B = Both row and column

  • C = Column

  • R = Row

  • Blank = No access control

TEMPORALTYPE CHAR (1)
Type of temporal table.
  • A = Application-period temporal table

  • B = Bitemporal table

  • N = Not a temporal table

  • S = System-period temporal table

TABLEORG CHAR(1)
  • C = Column-organized table

  • R = Row-organized table

  • N = Not a table

EXTENDED_ROW_SIZE CHAR(1)
Indicates whether the row size of a table that is organized by row exceeds the maximum record length for the page size of the table space in which it is defined.
  • N = Row size does not exceed the maximum record length for the page size

  • Y = Row size exceeds the maximum record length for the page size

  • blank = Not applicable

PCTEXTENDEDROWS REAL
Extended rows as a percentage of the total number of rows in the table; -1 if statistics are not collected.
REMARKS VARCHAR (254) Y User-provided comments, or the null value.

Note

  1. The DEFINER column is included for backwards compatibility. See OWNER.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29668428/viewspace-2284490/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
努力学习,并分享工作中的知识,欢迎阅读一起进步!

注册时间:2014-05-20

  • 博文量
    18
  • 访问量
    67107