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.
|
DATE_FORMAT | DATFMT | CHAR(3) Nullable
|
Date format for the column. Contains the null value if this column is not a date. |
DATE_SEPARATOR | DATSEP | CHAR(1) Nullable
|
Date separator for the column. Contains the null value if this column is not a date or if there is no separator value. |
TIME_FORMAT | TIMFMT | CHAR(3) Nullable
|
Time format for the column. Contains the null value if this column is not a time. |
TIME_SEPARATOR | TIMSEP | CHAR(1) Nullable
|
Time separator for the column. Contains the null value if this column is not a time or if there is no separator value. |
REFERENCE_LIBRARY | REFFLD_LIB | VARCHAR(10) Nullable
|
Library for the reference field. Contains the null value if there is no reference information for this column. |
REFERENCE_FILE | REFFLD_FIL | VARCHAR(10) Nullable
|
File for the reference field. Contains the null value if there is no reference information for this column. |
REFERENCE_FORMAT | REFFLD_FMT | VARCHAR(10) Nullable
|
Record format name for the reference field. Contains the null value if there is no reference information for this column. |
REFERENCE_FIELD | REFFLD | VARCHAR(10) Nullable
|
Reference field name Contains the null value if there is no reference information for this column. |
EDIT_CODE | EDTCDE | CHAR(1) Nullable
|
The edit code for this column. Contains the null value if there is no edit code. |
EDIT_CODE_FILL | EDTCDEFILL | CHAR(1) Nullable
|
Contains an * if the edit code uses asterisk fill. Any other character is the
floating currency symbol. Contains the null value if there is no edit code or if there is no fill character. |
EDIT_WORD | EDTWRD | VARCHAR(65) Nullable
|
The edit word for this column. Contains the null value if there is no edit word. |
COLUMN_USAGE | USAGE | VARCHAR(5) | Usage for the column.
|
JOIN_REFERENCE | JREF | INTEGER Nullable
|
For columns whose names are specified in more than one physical file, this
value identifies which physical file contains the field. Contains the null value if there is no join reference value. |
INTERNAL_FIELD_NAME | FIELD_I | VARCHAR(10) Nullable
|
Internal field name. The name of the physical format field. If this is a
logical format, the name of the physical field on which the logical field is based. Contains the null value if there is no internal field name. |