LISTCOLUMNS view

The DVSYS.LISTCOLUMNS view displays the list of columns of the tables in a RDBMS source.

Before you begin

Note: You can only call LISTCOLUMNS as a query, and not as a PreparedStatement (i.e. SELECT * FROM DVSYS.LISTCOLUMNS WHERE CID= ?).
Consider the following limitations:
Table retrieval limit
Only the first 10,000 rows are retrieved by default. To access tables beyond this initial batch, use the FILTER predicate to narrow down the schema, table and column filter to retrieve the columns of interest. See the FILTER column in the following table for more information.

Alternatively, you can increase the number of rows retrieved by modifying the configuration property BATCHSIZE_FOR_LISTSCHEMAS_OR_LISTTABLES, although this might negatively impact the performance.

Timeout and concurrency limits
The data source API calls for LISTSCHEMAS, LISTTABLES, and LISTCOLUMNS which are subject to a configurable timeout defined by the SOURCE_CATALOG_API_GETCOLUMNS_TIMEOUT_MS property, with a default value of 5 minutes. Additionally, the maximum number of concurrent calls is controlled by the SOURCE_CATALOG_API_MAX_CONCURRENCY property, which defaults to 4. Incoming requests that exceed this concurrency limit will not be processed and will return no rows.
Note: For multiple catalog sources, the RELOADTABLES_ALLCOLS_<CID> property defines the default catalog used unless the FILTER predicate and the catalog is defined. For example: <catalog>,null,null,null).
To define the FILTER, run this view and ensure you replace <xxxx...> with CID and FILTER values.
SELECT * FROM DVSYS.LISTCOLUMNS WHERE CID='<xxxx>' and FILTER='<xxxxxx>'

The data source API returns the value in the JDBC API DatabaseMetaadata.getColumns() columns.

Table 1. DVSYS.LISTCOLUMNS
Column Type Description
CID VARCHAR(20) The connection identifier. This is a required input predicate in the form of a string constant.
FILTER VARCHAR(500) The optional input predicate you use when calling LISTCOLUMNS to enable pushdown operations. This predicate is in the form of a string constant.

Composite CSV input string with same format as used for the properties RELOADTABLES_ALLCOLS_<CID>='<CATALOG>,<SCHEMAPATTERN>,<TABLENAMEPATTERN>,<COLUMNNAMEPATTERN>.
TCATALOG VARCHAR(128) The catalog name.
TSCHEM VARCHAR(128) The schema name.
TNAME VARCHAR(128) The table name.
TCOLUMN VARCHAR(20) The name of the table column.
DATA_TYPE SMALLINT The returned data type in the form of java.sql.Types.
TYPE_NAME VARCHAR(80) The type name for the DATA_TYPE column value.
COLUMN_SIZE INT The size of the column.
  • For numeric types, it indicates the maximum precision.
  • For character types, it represents the number of characters.
  • For datetime types, it reflects the character length of the string representation, assuming maximum precision for fractional seconds.
  • For binary and ROWID types, it denotes the length in bytes.
  • If the column size doesn't apply to a particular data type, then the output returns null.
DECIMAL_DIGITS SMALLINT The number of fractional digits.
NUM_PREC_RADIX SMALLINT The radix (base) used for the numeric data type. This value can be 2, 10, or NULL.
NULLABLE SMALLINT Indicates whether NULL values are allowed.
  • columnNoNulls: NULL values might not be allowed.
  • columnNullable: NULL values are allowed.
  • columnNullableUnknown: Nullability is unknown.
REMARKS VARCHAR(2000) Comments describing the column.
COLUMN_DEF VARCHAR(254) The column definition.
CHAR_OCTET_LENGTH INT The maximum number of bytes in the column.
ORDINAL_POSITION INT The index of the column in the table.
IS_NULLABLE VARCHAR(3) Displays whether the column is nullable.
SCOPE_CATALOG VARCHAR(128) Defines the catalog name of a table that is referenced by the REF attribute.

The value returns NULL if the DATA_TYPE isn't REF.
SCOPE_SCHEMA VARCHAR(128) Defines the schema name of a table that is referenced by the REF attribute.

The value returns NULL if the DATA_TYPE isn't REF.
SCOPE_TABLE VARCHAR(128) Defines the table name of a table that is referenced by the REF attribute.

The value returns NULL if the DATA_TYPE isn't REF.
SOURCE_DATA_TYPE SMALLINT The source type of a distinct type or user-generated Ref type, the data type is in the form of java.sql.Types.

The value returns NULL if the DATA_TYPE isn't DISTINCT or a user-generated REF.
IS_AUTOINCREMENT VARCHAR(3) Indicates whether this column is auto incremented.
  • YES: The column is auto incremented.
  • NO: The column is not auto incremented.
  • "": It cannot be determined whether the column is auto incremented.
IS_GENERATEDCOLUMN VARCHAR(3) Displays whether this is a generated column. This value currently returns ““.

Example

This example calls getColumns('null','SSPD','DISTTEST%','%') on the remote Db2 source:

select * from DVSYS.LISTCOLUMNS WHERE CID='DB210001' AND FILTER='null,SSPD,DISTTEST%,%'
The example output:
CID	FILTER	TCATALOG	TSCHEM	TNAME	TCOLUMN	DATA_TYPE	TYPE_NAME	COLUMN_SIZE	BUFFER_LENGTH	DECIMAL_DIGITS	NUM_PREC_RADIX	NULLABLE	REMARKS	COLUMN_DEF	SQL_DATA_TYPE	SQL_DATETIME_SUB	CHAR_OCTET_LENGTH	ORDINAL_POSITION	IS_NULLABLE	SCOPE_CATALOG	SCOPE_SCHEMA	SCOPE_TABLE	SOURCE_DATA_TYPE	IS_AUTOINCREMENT	IS_GENERATEDCOLUMN
DB210321	null,SSPD,DISTTEST%,%		SSPD	DISTTEST	C1	4	INTEGER	10		0	10	1						1	YES						
DB210321	null,SSPD,DISTTEST%,%		SSPD	DISTTEST	C2	3	DECIMAL	10		5	10	1						2	YES						
DB210321	null,SSPD,DISTTEST%,%		SSPD	DISTTEST	C3	12	VARCHAR	10				1					20	3	YES						
DB210321	null,SSPD,DISTTEST%,%		SSPD	DISTTEST	C4	93	TIMESTAMP	29		9	10	1						4	YES

Learn more

For more information on BATCHSIZE_FOR_LISTSCHEMAS_OR_LISTTABLES, SOURCE_CATALOG_API_GETCOLUMNS_TIMEOUT_MS, SOURCE_CATALOG_API_MAX_CONCURRENCY and RELOADTABLES_ALLCOLS_<CID>, refer to SETCONFIGPROPERTY properties.