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:

Table 1. 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: BIGINT Big number INTEGER Large number SMALLINT Small number DECIMAL Packed decimal NUMERIC Zoned decimal FLOAT Floating point; FLOAT, REAL, or DOUBLE PRECISION DECFLOAT Decimal floating-point CHAR Fixed-length character string VARCHAR Varying-length character string CLOB Character large object string GRAPHIC Fixed-length graphic string VARG Varying-length graphic string DBCLOB Double-byte character large object string BINARY Fixed-length binary string VARBIN Varying-length binary string BLOB Binary large object string DATE Date TIME Time TIMESTMP Timestamp DATALINK Datalink ROWID Row ID XML XML DISTINCT Distinct type
LENGTH	LENGTH	INTEGER	The length attribute of the column; or, in the case of a decimal, numeric, or nonzero precision binary column, its precision: 8 bytes BIGINT 4 bytes INTEGER 2 bytes SMALLINT Precision of number DECIMAL Precision of number NUMERIC 8 bytes FLOAT, FLOAT(n) where n = 25 to 53, or DOUBLE PRECISION 4 bytes FLOAT(n) where n = 1 to 24, or REAL 8 bytes DECFLOAT(16) 16 bytes DECFLOAT(34) Length of string CHAR Maximum length of string VARCHAR or CLOB Length of graphic string GRAPHIC Maximum length of graphic string VARGRAPHIC or DBCLOB Length of string BINARY Maximum length of binary string VARBIN or BLOB 4 bytes DATE 3 bytes TIME The integral part of ((p+1)/2)+7 where p is the precision of the timestamp TIMESTAMP Maximum length of datalink URL and comment DATALINK 40 bytes ROWID 2147483647 bytes XML Same value as the source type DISTINCT
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: N No Y Yes
IS_UPDATABLE	UPDATES	CHAR(1)	If the column can be updated: N No Y Yes
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): N No Y Yes A The column has a ROWID data type and the GENERATED ALWAYS attribute. D The column has a ROWID data type and the GENERATED BY DEFAULT attribute. E The column is defined with the FOR EACH ROW ON UPDATE and the GENERATED ALWAYS attribute. F The column is defined with the FOR EACH ROW ON UPDATE and the GENERATED BY DEFAULT attribute. I The column is defined with the AS IDENTITY and GENERATED ALWAYS attributes. J The column is defined with the AS IDENTITY and GENERATED BY DEFAULT attributes. Q The column is defined with the GENERATED AS ROW BEGIN attribute. R The column is defined with the GENERATED AS ROW END attribute. X The column is defined with the GENERATED AS TRANSACTION START ID attribute. a The column is defined as a generated expression using a special register. c The column is defined as a generated expression using a global variable. d The column is defined as a generated expression using DATA CHANGE OPERATION. If the column is for a view, N is returned.
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: 8 bytes BIGINT 4 bytes INTEGER 2 bytes SMALLINT (Precision/2) + 1 DECIMAL Precision of number NUMERIC 8 bytes FLOAT, FLOAT(n) where n = 25 to 53, or DOUBLE PRECISION 4 bytes FLOAT(n) where n = 1 to 24, or REAL 8 bytes DECFLOAT(16) 16 bytes DECFLOAT(34) Length of string CHAR or BINARY Maximum length of string + 2 VARCHAR or VARBIN Maximum length of string + 29 CLOB or BLOB Length of string * 2 GRAPHIC Maximum length of string * 2 + 2 VARGRAPHIC Maximum length of string * 2 + 29 DBCLOB 4 bytes DATE 3 bytes TIME The integral part of ((p+1)/2)+7 where p is the precision of the timestamp TIMESTAMP Maximum length of datalink URL and comment + 24 DATALINK 42 bytes ROWID 2147483647 bytes XML Same value as the source type DISTINCT 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: CURRENT_DATE The default value is the current date. CURRENT_TIME The default value is the current time. CURRENT_TIMESTAMP The default value is the current timestamp. NULL The default value is the null value and DEFAULT NULL was explicitly specified. USER The default value is the current job user. special-register When column HAS_DEFAULT contains the value 'a', the name of the special register. global-variable When column HAS_DEFAULT contains the value 'c', the qualified name of the global variable. DATA CHANGE OPERATION When column HAS_DEFAULT contains the value 'd'. Contains the null value if: The column has no default value. For example, if the column has an IDENTITY attribute, is a row ID, or is a row change timestamp, row begin, row end, or transaction start ID column; or A DEFAULT value was not explicitly specified.
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 2 Binary; floating-point precision is specified in binary digits. 10 Decimal; all other numeric types are specified in 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. 0 For DATE and TIME data types 0-12 For TIMESTAMP data types (number of fractional seconds). 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. NO The column is not an identity column. YES 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. ALWAYS The column value is always generated. BY DEFAULT The column value is 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. NO Values will not continue to be generated. YES Values will continue to be generated. 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. NO Values do not need to be generated in order of the request. YES 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. P Partially hidden. N Not hidden.
HAS_FLDPROC	FLDPROC	CHAR(1)	Specifies whether the column has a field procedure. N Column does not have a field procedure. Y 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. 0 Column should not be normalized. 1 Column should be normalized. 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. 0 No check will be performed. 1 A check will be performed. 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. 0 ALL 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. 0 FS 1 DB 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. 0 FS 1 BLOCKED 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. 0 NO 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. 0 RESTORE 1 DELETE 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. 0 The column does not contain data that needs to be secured in a database monitor or plan cache. 1 The column contains data that needs to 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. BOTH The column can be used for both input and output. INPUT The column can be used for input only. This means the data can be read but not changed.
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.