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.
Start of changeDATE_FORMATEnd of change Start of changeDATFMTEnd of change Start of changeCHAR(3)
Nullable
End of change
Start of changeDate format for the column.

Contains the null value if this column is not a date.

End of change
Start of changeDATE_SEPARATOREnd of change Start of changeDATSEPEnd of change Start of changeCHAR(1)
Nullable
End of change
Start of changeDate separator for the column.

Contains the null value if this column is not a date or if there is no separator value.

End of change
Start of changeTIME_FORMATEnd of change Start of changeTIMFMTEnd of change Start of changeCHAR(3)
Nullable
End of change
Start of changeTime format for the column.

Contains the null value if this column is not a time.

End of change
Start of changeTIME_SEPARATOREnd of change Start of changeTIMSEPEnd of change Start of changeCHAR(1)
Nullable
End of change
Start of changeTime separator for the column.

Contains the null value if this column is not a time or if there is no separator value.

End of change
Start of changeREFERENCE_LIBRARYEnd of change Start of changeREFFLD_LIBEnd of change Start of changeVARCHAR(10)
Nullable
End of change
Start of changeLibrary for the reference field.

Contains the null value if there is no reference information for this column.

End of change
Start of changeREFERENCE_FILEEnd of change Start of changeREFFLD_FILEnd of change Start of changeVARCHAR(10)
Nullable
End of change
Start of changeFile for the reference field.

Contains the null value if there is no reference information for this column.

End of change
Start of changeREFERENCE_FORMATEnd of change Start of changeREFFLD_FMTEnd of change Start of changeVARCHAR(10)
Nullable
End of change
Start of changeRecord format name for the reference field.

Contains the null value if there is no reference information for this column.

End of change
Start of changeREFERENCE_FIELDEnd of change Start of changeREFFLDEnd of change Start of changeVARCHAR(10)
Nullable
End of change
Start of changeReference field name

Contains the null value if there is no reference information for this column.

End of change
Start of changeEDIT_CODEEnd of change Start of changeEDTCDEEnd of change Start of changeCHAR(1)
Nullable
End of change
Start of changeThe edit code for this column.

Contains the null value if there is no edit code.

End of change
Start of changeEDIT_CODE_FILLEnd of change Start of changeEDTCDEFILLEnd of change Start of changeCHAR(1)
Nullable
End of change
Start of changeContains 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.

End of change
Start of changeEDIT_WORDEnd of change Start of changeEDTWRDEnd of change Start of changeVARCHAR(65)
Nullable
End of change
Start of changeThe edit word for this column.

Contains the null value if there is no edit word.

End of change
Start of changeCOLUMN_USAGEEnd of change Start of changeUSAGEEnd of change Start of changeVARCHAR(5)End of change Start of changeUsage 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.
End of change
Start of changeJOIN_REFERENCEEnd of change Start of changeJREFEnd of change Start of changeINTEGER
Nullable
End of change
Start of changeFor 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.

End of change
Start of changeINTERNAL_FIELD_NAMEEnd of change Start of changeFIELD_IEnd of change Start of changeVARCHAR(10)
Nullable
End of change
Start of changeInternal 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.

End of change