SYSCOLUMNS2
The SYSCOLUMNS2 view contains one row for every column of each table and view in the SQL schema (including the columns of the SQL catalog).
For information related to a single table or view, a query that uses SYSCOLUMNS2 will typically perform better than querying SYSCOLUMNS. SYSCOLUMNS2 also contains a few more column attributes than SYSCOLUMNS.
The following table describes the columns in the SYSCOLUMNS2 view:
Column name | System Column Name | Data Type | Description |
---|---|---|---|
COLUMN_NAME | NAME | VARCHAR(128) | Name of the column. This will be the SQL column name if one exists; otherwise, it will be the system column name. |
TABLE_NAME | TBNAME | VARCHAR(128) | Name of the table or view that contains the column. This will be the SQL table or view name if one exists; otherwise, it will be the system table or view name. |
TABLE_OWNER | TBCREATOR | VARCHAR(128) | The owner of the table or view. |
ORDINAL_POSITION | COLNO | INTEGER | Numeric place of the column in the table or view, ordered from left to right. |
DATA_TYPE | COLTYPE | VARCHAR(8) | Type of column:
|
LENGTH | LENGTH | INTEGER | The length attribute of the column; or, in the case of a decimal,
numeric, or nonzero precision binary column, its precision:
|
NUMERIC_SCALE | SCALE |
INTEGER
Nullable
|
Scale of numeric data. Contains the null value if the column is not decimal, numeric, or binary. |
IS_NULLABLE | NULLS | CHAR(1) | If the column can contain null values:
|
IS_UPDATABLE | UPDATES | CHAR(1) | If the column can be updated:
|
LONG_COMMENT | REMARKS | VARGRAPHIC(2000) CCSID
1200 Nullable
|
A character string supplied with the COMMENT statement. Contains the null value if there is no long comment. |
HAS_DEFAULT | DEFAULT | CHAR(1) | If the column has a default value (DEFAULT clause or null capable):
|
COLUMN_HEADING | LABEL | VARGRAPHIC(60) CCSID 1200 Nullable
|
A character string supplied with the LABEL statement (column
headings) Contains the null value if there is no column heading. |
STORAGE | STORAGE | INTEGER | The storage requirements for the column:
Note: This column supplies the storage requirements for all data types.
|
NUMERIC_PRECISION | PRECISION | INTEGER Nullable
|
The precision of all numeric columns. Note: This column supplies
the precision of all numeric data types, including decimal floating-point and single-and
double-precision floating point. The NUMERIC_PRECISION_RADIX column indicates if the value in this
column is in binary or decimal digits.
Contains the null value if the column is not numeric. |
CCSID | CCSID | INTEGER Nullable
|
The CCSID value for CHAR, VARCHAR, CLOB, DATE, TIME,
TIMESTAMP, GRAPHIC, VARGRAPHIC, DBCLOB, XML, and DATALINK columns. Contains 65535 if the column is a BINARY, VARBIN, BLOB, or ROWID. Contains the null value if the column is a numeric data type. |
TABLE_SCHEMA | DBNAME | VARCHAR(128) | The name of the SQL schema containing the table or view. |
COLUMN_DEFAULT | DFTVALUE | VARGRAPHIC(2000) CCSID 1200 Nullable
|
The default value of a column, if one exists. If the default value
of the column cannot be represented without truncation, then the value of the column is the string
'TRUNCATED'. The default value is stored in character form. The following special values also exist:
Contains the null value if:
|
CHARACTER_MAXIMUM_LENGTH | CHARLEN | INTEGER Nullable
|
Maximum length of the string for binary, character, and
graphic string and XML data types. Contains the null value if the column is not a string. |
CHARACTER_OCTET_LENGTH | CHARBYTE | INTEGER Nullable
|
Number of bytes for binary, character, and graphic
string and XML data types. Contains the null value if the column is not a string. |
NUMERIC_PRECISION_RADIX | RADIX | INTEGER Nullable
|
Indicates if the precision specified in column NUMERIC_PRECISION is
specified as a number of binary or decimal digits
Contains the null value if the column is not numeric. |
DATETIME_PRECISION | DATPRC | INTEGER Nullable
|
The fractional part of a date, time, or timestamp.
Contains the null value if the column is not a date, time, or timestamp. |
COLUMN_TEXT | LABELTEXT | VARGRAPHIC(50) CCSID
1200 Nullable
|
A character string supplied with the LABEL statement (column text)
Contains the null value if the column has no column text. |
SYSTEM_COLUMN_NAME | SYS_CNAME | CHAR(10) | The system name of the column |
SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | The system name of the table or view |
SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | The system name of the schema |
USER_DEFINED_TYPE_SCHEMA | TYPESCHEMA | VARCHAR(128) Nullable
|
The name of the schema if this is a distinct type. Contains the null value if the column is not a distinct type. |
USER_DEFINED_TYPE_NAME | TYPENAME | VARCHAR(128) Nullable
|
The name of the distinct type. Contains the null value if the column is not a distinct type. |
IS_IDENTITY | IDENTITY | VARCHAR(3) | This column identifies whether the column is an identity column.
|
IDENTITY_GENERATION | GENERATED | VARCHAR(10) Nullable
|
This column identifies whether the column is GENERATED ALWAYS or
GENERATED BY DEFAULT.
Contains the null value if the column is not a ROWID, identity, row change timestamp, row begin, row end, transaction start ID, or generated expression. |
IDENTITY_START | START | DECIMAL(31,0) Nullable
|
Starting value of the identity column. Contains the null value if the column is not an IDENTITY column. |
IDENTITY_INCREMENT | INCREMENT | DECIMAL(31,0) Nullable
|
Increment value of the identity column. Contains the null value if the column is not an IDENTITY column. |
IDENTITY_MINIMUM | MINVALUE | DECIMAL(31,0) Nullable
|
Minimum value of the identity column. Contains the null value if the column is not an IDENTITY column. |
IDENTITY_MAXIMUM | MAXVALUE | DECIMAL(31,0) Nullable
|
Maximum value of the identity column. Contains the null value if the column is not an IDENTITY column. |
IDENTITY_CYCLE | CYCLE | VARCHAR(3) Nullable
|
This column identifies whether the identity column values will
continue to be generated after the minimum or maximum value has been reached.
Contains the null value if the column is not an IDENTITY column. |
IDENTITY_CACHE | CACHE | INTEGER Nullable
|
Specifies the number of identity values that may be preallocated
for faster access. Zero indicates that the values will not be preallocated. Contains the null value if the column is not an IDENTITY column. |
IDENTITY_ORDER | ORDER | VARCHAR(3) Nullable
|
Specifies whether the identity values must be generated in order of
the request.
Contains the null value if the column is not an IDENTITY column. |
COLUMN_EXPRESSION | EXPRESSION | DBCLOB(2097152)
CCSID 1200
Nullable
|
If the column is an expression, contains the expression. Contains the null value if the column is not an expression. |
HIDDEN | HIDDEN | CHAR(1) | Specifies whether the column is included in an implicit column list.
|
HAS_FLDPROC | FLDPROC | CHAR(1) | Specifies whether the column has a field procedure.
|
INLINE_LENGTH | ALLOCATE | INTEGER Nullable
|
Specifies the allocated length (ALLOCATE) for a varying length
column. Contains the null value if the column is not varying length. |
NORMALIZE | NORMALIZE | CHAR(1) Nullable
|
Specifies whether the column data should be normalized when passed from the application.
Contains the null value if the column does not contain Unicode data. |
DATALINK_LINK_CONTROL | DL_LINKC | CHAR(1) Nullable
|
Specifies whether a check will be performed to determine if the DATALINK
column's linked files exist.
Contains the null value if the data type of the column is not DATALINK. |
DATALINK_INTEGRITY | DL_INTEG | CHAR(1) Nullable
|
Specifies the level of integrity of the link between the DATALINK value and
the linked files.
Contains the null value if the data type of the column is not DATALINK or if the datalink has NO LINK CONTROL. |
DATALINK_READ_PERMISSION | DL_READP | CHAR(1) Nullable
|
Specifies how permission to read the file specified in the DATALINK value is determined.
Contains the null value if the data type of the column is not DATALINK or if the datalink has NO LINK CONTROL. |
DATALINK_WRITE_PERMISSION | DL_WRITEP | CHAR(1) Nullable
|
Specifies how permission to write to the file specified in the DATALINK value
is determined.
Contains the null value if the data type of the column is not DATALINK or if the datalink has NO LINK CONTROL. |
DATALINK_RECOVERY | DL_RECOVER | CHAR(1) Nullable
|
Specifies whether point in time recovery of the linked files of the DATALINK
column is supported.
Contains the null value if the data type of the column is not DATALINK or if the datalink has NO LINK CONTROL. |
DATALINK_UNLINK_CONTROL | DL_UNLINKC | CHAR(1) Nullable
|
Specifies the action the DataLink File Manager will take when a file is unlinked.
Contains the null value if the data type of the column is not DATALINK or if the datalink has NO LINK CONTROL. |
DDS_TYPE | DDS_TYPE | CHAR(1) Nullable
|
Specifies the Data Description Specification (DDS) data type for the column. See the following link for the list DDS data types: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzakb/ldata.htm |
SECURE | SECURE | CHAR(1) Nullable
|
Specifies whether the column contains data that should be secured in a
database monitor or plan cache.
|
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if this column is not a date. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if this column is not a date or if there is no separator value. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if this column is not a time. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if this column is not a time or if there is no separator value. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no reference information for this column. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no reference information for this column. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no reference information for this column. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no reference information for this column. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no edit code. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no edit code or if there is no fill character. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no edit word. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]()
![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no join reference value. ![]() |
![]() ![]() |
![]() ![]() |
![]() Nullable
![]() |
![]() Contains the null value if there is no internal field name. ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |