id="catalogodbc">ODBC and JDBC catalog views

The catalog includes the following views and tables in the SYSIBM library:

View Name	Description
SQLCOLPRIVILEGES	Information about privileges granted on columns
SQLCOLUMNS	Information about column attributes
SQLFOREIGNKEYS	Information about foreign keys
SQLPRIMARYKEYS	Information about primary keys
SQLPROCEDURECOLS	Information about procedure parameters
SQLPROCEDURES	Information about procedures
SQLSCHEMAS	Information about schemas
SQLSPECIALCOLUMNS	Information about columns of a table that can be used to uniquely identify a row
SQLSTATISTICS	Statistical information about tables
SQLTABLEPRIVILEGES	Information about privileges granted on tables
SQLTABLES	Information about tables
SQLTYPEINFO	Information about the types of tables
SQLUDTS	Information about built-in data types and distinct types

 

SQLCOLPRIVILEGES

The SQLCOLPRIVILEGES view contains one row for every privileges granted on a column. Note that this catalog view cannot be used to determine whether a user is authorized to a column because the privilege to use a column could be acquired through a group user profile or special authority (such as *ALLOBJ). Furthermore, the privilege to use a column is also acquired through privileges granted on the table. The following table describes the columns in the view:

Table 146. SQLCOLPRIVILEGES view

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	Relational database name.
TABLE_SCHEM	VARCHAR(128)	Name of the SQL schema that contains the table.
TABLE_NAME	VARCHAR(128)	Table name.
COLUMN_NAME	VARCHAR(128)	Column name.
GRANTOR	VARCHAR(128) Nullable	Reserved. Contains the null value.
GRANTEE	VARCHAR(128)	The user profile to which the privilege is granted.
PRIVILEGE	VARCHAR(10)	The privilege granted: UPDATE The privilege to update the column. REFERENCES The privilege to reference the column in a referential constraint.
IS_GRANTABLE	VARCHAR(3)	Indicates whether the privilege is grantable to other users. NO The privilege is not grantable. YES The privilege is grantable.
DBNAME	VARCHAR(8) Nullable	Reserved. The column contains the null value.

 

SQLCOLUMNS

The SQLCOLUMNS view contains one row for every column in a table, view, or alias. The following table describes the columns in the view:

Table 147. SQLCOLUMNS view

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	Relational database name.
TABLE_SCHEM	VARCHAR(128)	Name of the SQL schema that contains the table.
TABLE_NAME	VARCHAR(128)	Table name.
COLUMN_NAME	VARCHAR(128)	Column name.
DATA_TYPE	SMALLINT	The data type of the column: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 40 CLOB –95 GRAPHIC –96 VARGRAPHIC –350 DBCLOB –2 BINARY –3 VARBINARY 30 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK –100 ROWID 17 DISTINCT
TYPE_NAME	VARCHAR(260)	The name of the data type of the column: BIGINT BIGINT INTeger INTEGER SMALLINT SMALLINT DECIMAL DECIMAL NUMERIC NUMERIC FLOAT DOUBLE PRECISION REAL REAL CHARacter CHARACTER CHARacter FOR BIT DATA CHARACTER FOR BIT DATA VARCHAR VARCHAR VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA CLOB CLOB GRAPHIC GRAPHIC VARGRAPHIC VARGRAPHIC DBCLOB DBCLOB BINARY BINARY VARBINARY VARBINARY BLOB BLOB DATE DATE TIME TIME TIMESTAMP TIMESTAMP DATALINK DATALINK ROWID ROWID Qualified Type Name DISTINCT
COLUMN_SIZE	INTEGER	The length of the column.
BUFFER_LENGTH	INTEGER	Indicates the length of the column in a buffer.
DECIMAL_DIGITS	SMALLINT Nullable	Indicates the number of digits for a numeric column. Contains the null value if the object is not numeric.
NUM_PREC_RADIX	SMALLINT Nullable	Indicates the radix of a numeric column. Contains the null value if the object is not numeric.
NULLABLE	SMALLINT	Indicates whether the column can contain the null value. 0 The column does not allow nulls. 1 The column does allow nulls.
REMARKS	VARCHAR(2000) Nullable	A character string supplied with the COMMENT statement. Contains the null value if there is no long comment.
COLUMN_DEF	VARCHAR(2000) Nullable	The default value of the column. Contains the null value if there is no default value.
SQL_DATA_TYPE	SMALLINT	Indicates the SQL data type of the column.
SQL_DATETIME_SUB	SMALLINT Nullable	The datetime subtype of the data type: 1 DATE 2 TIME 3 TIMESTAMP Contains the null value if the column is not a datetime data type.
CHAR_OCTET_LENGTH	INTEGER Nullable	Indicates the length in characters of the column. Contains the null value if the column is not a string.
ORDINAL_POSITION	INTEGER	Indicates the ordinal position of the column in the table.
IS_NULLABLE	VARCHAR(3)	Indicates whether the column can contain the null value. NO The column is not nullable. YES The column is nullable.
JDBC_DATA_TYPE	SMALLINT	Indicates the JDBC data type of the column. –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 2005 CLOB 1 GRAPHIC 12 VARGRAPHIC 1111 DBCLOB –2 BINARY –3 VARBINARY 2004 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK 1111 ROWID 2001 DISTINCT
SCOPE_CATALOG	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_SCHEMA	VARCHAR(128) Nullable	Reserved. Contains the null value.
SCOPE_TABLE	VARCHAR(128) Nullable	Reserved. Contains the null value.
SOURCE_DATA_TYPE	SMALLINT Nullable	The source data type if the data type of the column is a distinct type. Contains the null value if the data type is not a distinct type.
DBNAME	VARCHAR(8) Nullable	Reserved. Contains the null value.
PSEUDO_COLUMN	SMALLINT	Indicates whether this is a ROWID or identity column. 1 The column is not a ROWID or identity column. 2 The column is a ROWID or identity column.
COLUMN_TEXT	VARCHAR(50) Nullable	The text of the column. Contains the null value if the column has no column text.
SYSTEM_COLUMN_NAME	CHAR(10)	The system name of the column.
I_DATA_TYPE	SMALLINT	Indicates the i5/OS™ CLI data type of the column. 19 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 14 CLOB 95 GRAPHIC 96 VARGRAPHIC 15 DBCLOB –2 BINARY –3 VARBINARY 13 BLOB 91 DATE 92 TIME 93 TIMESTAMP 16 DATALINK 1111 ROWID 2001 DISTINCT

 

SQLFOREIGNKEYS

The SQLFOREIGNKEYS view contains one row for every referential constraint key on a table. The following table describes the columns in the view:

Table 148. SQLFOREIGNKEYS view

Column Name	Data Type	Description
PKTABLE_CAT	VARCHAR(128)	Relational database name
PKTABLE_SCHEM	VARCHAR(128)	Name of the SQL schema containing the parent table.
PKTABLE_NAME	VARCHAR(128)	Parent table name.
PKCOLUMN_NAME	VARCHAR(128)	Parent key column name.
FKTABLE_CAT	VARCHAR(128)	Relational database name
FKTABLE_SCHEM	VARCHAR(128)	Name of the SQL schema containing the dependent table of the referential constraint.
FKTABLE_NAME	VARCHAR(128)	Dependent table name of the referential constraint.
FKCOLUMN_NAME	VARCHAR(128)	Dependent key name.
KEY_SEQ	SMALLINT	The position of the column within the key.
UPDATE_RULE	SMALLINT	Update Rule. 1 RESTRICT 3 NO ACTION
DELETE_RULE	SMALLINT	Delete Rule: 0 CASCADE 1 RESTRICT 2 SET NULL 3 NO ACTION 4 SET DEFAULT
FK_NAME	VARCHAR(128)	Name of the referential constraint
PK_NAME	VARCHAR(128)	Name of the unique constraint
DEFERRABILITY	SMALLINT	Indicates whether the constraint checking can be deferred. Will always be 7.
UNIQUE_OR_PRIMARY	CHAR(7)	Indicates the type of parent constraint: PRIMARY The parent constraint is a primary key. UNIQUE The parent constraint is a unique constraint.

 

SQLPRIMARYKEYS

The SQLPRIMARYKEYS view contains one row for every primary constraint key on a table. The following table describes the columns in the view:

Table 149. SQLPRIMARYKEYS view

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	Relational database name
TABLE_SCHEM	VARCHAR(128)	Name of the schema containing the table with the primary key.
TABLE_NAME	VARCHAR(128)	Name of the table with the primary key.
COLUMN_NAME	VARCHAR(128)	Name of a primary key column.
KEY_SEQ	SMALLINT	The position of the column within the key.
PK_NAME	VARCHAR(128)	Name of the primary key constraint.

 

SQLPROCEDURECOLS

The SQLPROCEDURECOLS view contains one row for every parameter of a procedure. The following table describes the columns in the view:

Table 150. SQLPROCEDURECOLS view

Column Name	Data Type	Description
PROCEDURE_CAT	VARCHAR(128)	Relational database name
PROCEDURE_SCHEM	VARCHAR(128)	Schema name of the procedure instance.
PROCEDURE_NAME	VARCHAR(128)	Name of the procedure instance.
COLUMN_NAME	VARCHAR(128) Nullable	Name of a procedure parameter. Contains the null value if the parameter does not have a name.
COLUMN_TYPE	SMALLINT	Type of the parameter: 1 IN 2 INOUT 4 OUT
DATA_TYPE	SMALLINT	The data type of the parameter: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 40 CLOB –95 GRAPHIC –96 VARGRAPHIC –350 DBCLOB –2 BINARY –3 VARBINARY 30 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK –100 ROWID 17 DISTINCT
TYPE_NAME	VARCHAR(260)	The name of the data type of the parameter: BIGINT BIGINT INTeger INTEGER SMALLINT SMALLINT DECIMAL DECIMAL NUMERIC NUMERIC FLOAT DOUBLE PRECISION REAL REAL CHARacter CHARACTER CHARacter FOR BIT DATA CHARACTER FOR BIT DATA VARCHAR VARCHAR VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA CLOB CLOB GRAPHIC GRAPHIC VARGRAPHIC VARGRAPHIC DBCLOB DBCLOB BINARY BINARY VARBINARY VARBINARY BLOB BLOB DATE DATE TIME TIME TIMESTAMP TIMESTAMP DATALINK DATALINK ROWID ROWID Qualified Type Name DISTINCT
COLUMN_SIZE	INTEGER Nullable	Length of the parameter.
BUFFER_LENGTH	INTEGER Nullable	Indicates the length of the parameter in a buffer.
DECIMAL_DIGITS	SMALLINT Nullable	Scale of numeric or datetime data. Contains the null value if the parameter is not decimal, numeric, binary, time or timestamp.
NUM_PREC_RADIX	SMALLINT 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 parameter is not numeric.
NULLABLE	SMALLINT	Indicates whether the parameter is nullable. 0 The parameter does not allow nulls. 1 The parameter does allow nulls.
REMARKS	VARCHAR(2000) Nullable	A character string supplied with the COMMENT statement. Contains the null value if there is no long comment.
COLUMN_DEF	VARCHAR(1) Nullable	The default value for the column. Contains the null value if there is no default value.
SQL_DATA_TYPE	SMALLINT	The SQL data type of the parameter: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA –99 CLOB –95 GRAPHIC –96 VARGRAPHIC –350 DBCLOB –2 BINARY –3 VARBINARY –98 BLOB 9 DATE 10 TIME 11 TIMESTAMP 70 DATALINK –100 ROWID 17 DISTINCT
SQL_DATETIME_SUB	SMALLINT Nullable	The datetime subtype of the parameter: 1 DATE 2 TIME 3 TIMESTAMP Contains the null value if the data type is not a datetime data type.
CHAR_OCTET_LENGTH	INTEGER Nullable	Indicates the length in characters of the parameter. Contains the null value if the column is not a string.
ORDINAL_POSITION	INTEGER	Numeric place of the parameter in the parameter list, ordered from left to right.
IS_NULLABLE	VARCHAR(3)	Indicates whether the parameter is nullable. NO The parameter does not allow nulls. YES The parameter does allow nulls.
JDBC_DATA_TYPE	INTEGER	The JDBC data type of the parameter: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 2005 CLOB 1 GRAPHIC 12 VARGRAPHIC 1111 DBCLOB –2 BINARY –3 VARBINARY 2004 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK 1111 ROWID 2001 DISTINCT
I_DATA_TYPE	INTEGER	Indicates the i5/OS CLI data type of the column. 19 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 14 CLOB 95 GRAPHIC 96 VARGRAPHIC 15 DBCLOB –2 BINARY –3 VARBINARY 13 BLOB 91 DATE 92 TIME 93 TIMESTAMP 16 DATALINK 1111 ROWID 2001 DISTINCT

 

SQLPROCEDURES

The SQLPROCEDURES view contains one row for every procedure. The following table describes the columns in the view:

Table 151. SQLPROCEDURES view

Column Name	Data Type	Description
PROCEDURE_CAT	VARCHAR(128)	Relational database name
PROCEDURE_SCHEM	VARCHAR(128)	Name of the schema of the procedure instance.
PROCEDURE_NAME	VARCHAR(128)	Name of the procedure.
NUM_INPUT_PARAMS	SMALLINT	Identifies the number of input parameters. 0 indicates that there are no input parameters.
NUM_OUTPUT_PARAMS	SMALLINT	Identifies the number of output parameters. 0 indicates that there are no output parameters.
NUM_RESULT_SETS	SMALLINT	Identifies the maximum number of result sets returned. 0 indicates that there are no result sets.
REMARKS	VARCHAR(2000) Nullable	A character string supplied with the COMMENT statement. Contains the null value if there is no long comment.
PROCEDURE_TYPE	SMALLINT	Reserved. Contains 0.
NUM_INOUT_PARAMS	SMALLINT	Identifies the number of input/output parameters. 0 indicates that there are no input/output parameters.

 

SQLSCHEMAS

The SQLSCHEMAS view contains one row for every schema. The following table describes the columns in the view:

Table 152. SQLSCHEMAS view

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	Relational database name
TABLE_SCHEM	VARCHAR(128)	Name of the schema.
TABLE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
TABLE_TYPE	VARCHAR(128) Nullable	Reserved. Contains the null value.
REMARKS	VARCHAR(2000) Nullable	Reserved. Contains the null value.
TYPE_CAT	VARCHAR(128) Nullable	Reserved. Contains the null value.
TYPE_SCHEM	VARCHAR(128) Nullable	Reserved. Contains the null value.
TYPE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
SELF_REF_COL_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
REF_GENERATION	VARCHAR(128) Nullable	Reserved. Contains the null value.
DBNAME	VARCHAR(8) Nullable	Reserved. Contains the null value.
SCHEMA_TEXT	VARCHAR(50) Nullable	A character string that describes the schema. Contains the empty string if there is no text.

 

SQLSPECIALCOLUMNS

The SQLSPECIALCOLUMNS view contains one row for every column of a primary key, unique constraint, or unique index that can identify a row of the table. The following table describes the columns in the view:

Table 153. SQLSPECIALCOLUMNS view

Column Name	Data Type	Description
SCOPE	SMALLINT	Reserved. Contains 0.
COLUMN_NAME	VARCHAR(128)	Column name
DATA_TYPE	SMALLINT	The data type of the column: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 40 CLOB –95 GRAPHIC –96 VARGRAPHIC –350 DBCLOB –2 BINARY –3 VARBINARY 30 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK –100 ROWID 17 DISTINCT
TYPE_NAME	VARCHAR(260)	The name of the data type of the column.
COLUMN_SIZE	INTEGER	The length of the column.
BUFFER_LENGTH	INTEGER	Indicates the length of the column in a buffer.
DECIMAL_DIGITS	SMALLINT Nullable	Indicates the number of digits for a numeric column. Contains the null value if the column is not numeric.
PSEUDO_COLUMN	SMALLINT	Indicates whether this is a ROWID or identity column. 1 The column is not a ROWID or identity column. 2 The column is a ROWID or identity column.
TABLE_CAT	VARCHAR(128)	Relational database name
TABLE_SCHEM	VARCHAR(128)	Name of the SQL schema that contains the table.
TABLE_NAME	VARCHAR(128)	Name of the table.
NULLABLE	SMALLINT	Indicates whether the column can contain the null value. 0 The column is not nullable. 1 The column is nullable.
JDBC_DATA_TYPE	SMALLINT	Indicates the JDBC data type of the column. –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 2005 CLOB 1 GRAPHIC 12 VARGRAPHIC 1111 DBCLOB –2 BINARY –3 VARBINARY 2004 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK 1111 ROWID 2001 DISTINCT
I_DATA_TYPE	SMALLINT	Indicates the i5/OS CLI data type of the column. 19 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 14 CLOB 95 GRAPHIC 96 VARGRAPHIC 15 DBCLOB –2 BINARY –3 VARBINARY 13 BLOB 91 DATE 92 TIME 93 TIMESTAMP 16 DATALINK 1111 ROWID 2001 DISTINCT

 

SQLSTATISTICS

The SQLSTATISTICS view contains statistic information on a table. The following table describes the columns in the view:

Table 154. SQLSTATISTICS view

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	Relational database name
TABLE_SCHEM	VARCHAR(128)	Name of the SQL schema of the table.
TABLE_NAME	VARCHAR(128)	Name of the table.
NON_UNIQUE	SMALLINT Nullable	Indicates whether an index prohibits duplicate keys on the table. Contains the null value if the TYPE is 0.
INDEX_QUALIFIER	VARCHAR(128) Nullable	Name of the schema of the index. Contains the null value if the TYPE is 0.
INDEX_NAME	VARCHAR(128) Nullable	Name of the index. Contains the null value if the TYPE is 0.
TYPE	SMALLINT	Indicates the type of information returned: 0 The number of rows in the table. 3 An index on the table.
ORDINAL_POSITION	SMALLINT Nullable	Indicates the ordinal position of the key in the index. Contains the null value if the TYPE is 0.
COLUMN_NAME	VARCHAR(128) Nullable	Name of the column for a key in the index. Contains the null value if the TYPE is 0.
ASC_OR_DESC	CHAR(1) Nullable	Order of the column in the key: A Ascending D Descending Contains the null value if the TYPE is 0.
CARDINALITY	INTEGER Nullable	Reserved. Contains the null value.
PAGES	INTEGER Nullable	Reserved. Contains the null value.
FILTER_CONDITION	VARCHAR(128) Nullable	Indicates whether the index is a select/omit index. empty-string This is a select/omit index. Contains the null value if the TYPE is 0 or this is not a select/omit index.

 

SQLTABLEPRIVILEGES

The SQLTABLEPRIVILEGES view contains one row for every privilege granted on a table. Note that this catalog view cannot be used to determine whether a user is authorized to a table or view because the privilege to use a table or view could be acquired through a group user profile or special authority (such as *ALLOBJ). The following table describes the columns in the view:

Table 155. SQLTABLEPRIVILEGES view

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	Relational database name
TABLE_SCHEM	VARCHAR(128)	Name of the SQL schema of the table.
TABLE_NAME	VARCHAR(128)	Name of the table.
GRANTOR	VARCHAR(128) Nullable	Reserved. Contains the null value.
GRANTEE	VARCHAR(128)	The user profile to which the privilege is granted.
PRIVILEGE	VARCHAR(10)	The privilege granted: ALTER The privilege to alter the table. DELETE The privilege to delete rows from the table. INDEX The privilege to create an index on the table. INSERT The privilege to insert rows into the table. REFERENCES The privilege to reference the table in a referential constraint. SELECT The privilege to select rows from the table. UPDATE The privilege to update the table.
IS_GRANTABLE	VARCHAR(3)	Indicates whether the privilege is grantable to other users. NO The privilege is not grantable. YES The privilege is grantable.
DBNAME	VARCHAR(8) Nullable	Reserved. Contains the null value.

 

SQLTABLES

The SQLTABLES view contains one row for every table, view, and alias. The following table describes the columns in the view:

Table 156. SQLTABLES view

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	Relational database name
TABLE_SCHEM	VARCHAR(128)	Name of the schema containing the table.
TABLE_NAME	VARCHAR(128)	Name of the table.
TABLE_TYPE	VARCHAR(24)	Indicates the type of the table: ALIAS The table is an alias. MATERIALIZED QUERY TABLE The object is a materialized query table. TABLE The table is an SQL table or physical file. VIEW The table is an SQL view or logical file.
REMARKS	VARCHAR(2000) Nullable	A character string supplied with the COMMENT statement. Contains the null value if there is no long comment.
TYPE_CAT	VARCHAR(128) Nullable	Reserved. Contains the null value.
TYPE_SCHEM	VARCHAR(128) Nullable	Reserved. Contains the null value.
TYPE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
SELF_REF_COL_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
REF_GENERATION	VARCHAR(128) Nullable	Reserved. Contains the null value.
DBNAME	VARCHAR(8) Nullable	Reserved. Contains the null value.
TABLE_TEXT	VARCHAR(50)	A character string provided with the LABEL statement.

 

SQLTYPEINFO

The SQLTYPEINFO table contains one row for every built-in data type. The following table describes the columns in the table:

Table 157. SQLTYPEINFO table

Column Name	Data Type	Description
TYPE_NAME	VARCHAR(128)	Name of the built-in data type: BIGINT BIGINT INTeger INTEGER SMALLINT SMALLINT DECIMAL DECIMAL NUMERIC NUMERIC FLOAT DOUBLE PRECISION REAL REAL CHARacter CHARACTER CHARacter FOR BIT DATA CHARACTER FOR BIT DATA VARCHAR VARCHAR VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA CLOB CLOB GRAPHIC GRAPHIC VARGRAPHIC VARGRAPHIC DBCLOB DBCLOB BINARY BINARY VARBINARY VARBINARY BLOB BLOB DATE DATE TIME TIME TIMESTAMP TIMESTAMP DATALINK DATALINK ROWID ROWID
DATA_TYPE	SMALLINT	The data type of the column: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 40 CLOB –95 GRAPHIC –96 VARGRAPHIC –350 DBCLOB –2 BINARY –3 VARBINARY 30 BLOB 9 DATE 10 TIME 11 TIMESTAMP 70 DATALINK –100 ROWID
COLUMN_SIZE	INTEGER Nullable	The maximum length of the data type.
LITERAL_PREFIX	VARCHAR(128) Nullable	Indicates the prefix for a string literal. Contains the null value if the data type is not a string.
LITERAL_SUFFIX	VARCHAR(128) Nullable	Indicates the suffix for a string literal. Contains the null value if the data type is not a string.
CREATE_PARAMS	VARCHAR(128) Nullable	Indicates the parameters supported with the data type. length The parameter is a length. Returned for all string data types and DATALINK. precision,scale The parameters include precision and scale. Returned for the DECIMAL and NUMERIC data types. Contains the null value for all other data types.
NULLABLE	SMALLINT Nullable	Indicates whether the data type is nullable. 0 The data type does not allow nulls. 1 The data type does allow nulls.
CASE_SENSITIVE	SMALLINT Nullable	Indicates whether the data type is case sensitive. 0 The data type is not case sensitive. 1 The data type is case sensitive.
SEARCHABLE	SMALLINT Nullable	Indicates whether the data type can be used in a predicate. 0 The data type cannot be used in predicates. 2 The data type can be used in all predicates except the LIKE predicate. 3 The data type can be used in all predicates including the LIKE predicate.
UNSIGNED_ATTRIBUTE	SMALLINT Nullable	Indicates whether the numeric data type is signed or unsigned. 0 The data type is signed. 1 The data type is unsigned. Contains the null value if the data type is not numeric.
FIXED_PREC_SCALE	SMALLINT	Indicates whether the data type has a fixed precision and scale. 0 The data type does not have a fixed precision and scale. 1 The data type does have a fixed precision and scale.
AUTO_UNIQUE_VALUE	SMALLINT Nullable	Indicates whether the numeric data type is auto-incrementing: 0 The data type is not auto-incrementing. 1 The data type is auto-incrementing. Contains the null value if the data type is not numeric.
LOCAL_TYPE_NAME	VARCHAR(128) Nullable	Reserved. Contains the null value.
MINIMUM_SCALE	SMALLINT Nullable	Indicates the minimum scale of numeric data types. Contains the null value if the data type is not numeric.
MAXIMUM_SCALE	SMALLINT Nullable	Indicates the maximum scale of numeric data types. Contains the null value if the data type is not numeric.
SQL_DATA_TYPE	SMALLINT Nullable	Indicates the SQL data type value of the data type: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA –99 CLOB –95 GRAPHIC –96 VARGRAPHIC –350 DBCLOB –2 BINARY –3 VARBINARY –98 BLOB 9 DATE 10 TIME 11 TIMESTAMP 70 DATALINK –100 ROWID
SQL_DATETIME_SUB	SMALLINT Nullable	The datetime subtype of the data type: 1 DATE 2 TIME 3 TIMESTAMP Contains the null value if the data type is not a datetime data type.
NUM_PREC_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 parameter is not numeric.
INTERVAL_PRECISION	SMALLINT Nullable	Reserved. Contains the null value.
JDBC_DATA_TYPE	SMALLINT	The JDBC data type value of the data type: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 2005 CLOB 1 GRAPHIC 12 VARGRAPHIC 1111 DBCLOB –2 BINARY –3 VARBINARY 2004 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK 1111 ROWID
I_DATA_TYPE	SMALLINT	Indicates the i5/OS CLI data type of the column. 19 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 14 CLOB 95 GRAPHIC 96 VARGRAPHIC 15 DBCLOB –2 BINARY –3 VARBINARY 13 BLOB 91 DATE 92 TIME 93 TIMESTAMP 16 DATALINK 1111 ROWID 2001 DISTINCT

 

SQLUDTS

The SQLUDTS view contains one row for every distinct type. The following table describes the columns in the view:

Table 158. SQLUDTS view

Column Name	Data Type	Description
TYPE_CAT	VARCHAR(128)	Relational database name
TYPE_SCHEM	VARCHAR(128)	Name of the schema containing the user-defined type.
TYPE_NAME	VARCHAR(128)	Name of the user-defined type.
CLASS_NAME	VARCHAR(20)	Java™ class name of the user-defined type. java.math.BigInteger BIGINT java.lang.Integer INTEGER java.lang.Short SMALLINT java.math.BigDecimal DECIMAL java.sql.BigDecimal NUMERIC java.lang.Double DOUBLE PRECISION java.lang.Float REAL java.lang.String CHARACTER byte[] CHARACTER FOR BIT DATA java.lang.String VARCHAR byte[] VARCHAR FOR BIT DATA java.sql.Clob CLOB java.lang.String GRAPHIC java.lang.String VARGRAPHIC java.sql.Clob DBCLOB byte[] BINARY byte[] VARBINARY java.sql.Blob BLOB java.sql.Date DATE java.sql.Time TIME java.sql.Timestamp TIMESTAMP java.net.URL DATALINK byte[] ROWID
DATA_TYPE	SMALLINT	Reserved. Contains 2001.
BASE_TYPE	SMALLINT	The source data type of the user-defined data type: –5 BIGINT 4 INTEGER 5 SMALLINT 3 DECIMAL 2 NUMERIC 8 DOUBLE PRECISION 7 REAL 1 CHARACTER –2 CHARACTER FOR BIT DATA 12 VARCHAR –3 VARCHAR FOR BIT DATA 2005 CLOB 1 GRAPHIC 12 VARGRAPHIC 1111 DBCLOB –2 BINARY –3 VARBINARY 2004 BLOB 91 DATE 92 TIME 93 TIMESTAMP 70 DATALINK 1111 ROWID
REMARKS	VARCHAR(2000) Nullable	A character string supplied with the COMMENT statement. Contains the null value if there is no comment.

---

[ Top of Page | Previous Page | Next Page | Contents |
Index ]