Tableau JDBC Capability Customisations Reference
You can set the following capability customisations in the Tableau Datasource Customisation (TDC) file to define which Tableau capabilities are supported by the JDBC connection.
Many of these customisations influence the type of SQL queries that Tableau issues. For settings that are not defined, Tableau attempts to determine the proper values for each capability by issuing various forms of SQL queries to experimentally verify which forms are supported.
CAP_CREATE_TEMP_TABLES | Set to 'yes' if Tableau can create temporary tables needed for certain complex or optimised queries. See also: CAP_SELECT_INTO. |
CAP_CONNECT_STORED_PROCEDURE | Set to 'yes' to allow support for connecting to a stored procedure. |
CAP_FAST_METADATA | Set to 'yes' if you have small to moderate size schemas. This capability controls whether Tableau should enumerate all of the objects immediately when you connect. Set the value to ‘yes’ to enable this capability for better performance when creating new connections. Disable this capability to allow search for specific schemas or tables instead of retrieving all objects. You can search for all objects by using an empty string. This capability is available in 9.0 and later. |
CAP_ISOLATION_LEVEL_READ_COMMITTED | Set to 'yes' to force the transaction isolation level to Read Committed if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL. |
CAP_ISOLATION_LEVEL_READ_UNCOMMITTED | Set to 'yes' to force the transaction isolation level to Read Uncommitted if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. This capability can improve speed by reducing lock contention, but may result in partial or inconsistent data in query results. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL. |
CAP_ISOLATION_LEVEL_REPEATABLE_READS | Set to 'yes' to force the transaction isolation level to Repeatable Reads if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL. |
CAP_ISOLATION_LEVEL_SERIALIZABLE | Set to 'yes' to force the transaction isolation level to Serializable if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. This is a very conservative setting that may improve stability at the expense of performance. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL. |
CAP_JDBC_BIND_DETECT_ALIAS_CASE_FOLDING | Set to ‘yes’ to allow Tableau to detect and recover from a JDBC data source that reports the field names in a result set using only uppercase or lowercase characters, instead of the expected field names. |
CAP_JDBC_EXPORT_DATA_BATCH | Set to 'no' to disable the use of JDBC batch operations for data insert. |
CAP_JDBC_METADATA_GET_INDEX_INFO | Set to ‘no’ to disable reading index information. |
CAP_JDBC_METADATA_READ_FOREIGNKEYS | Set to ‘no’ to disable reading foreign key metadata. |
CAP_JDBC_METADATA_READ_PRIMARYKEYS | Set to ‘no’ to disable reading primary key metadata. |
CAP_JDBC_METADATA_USE_RESULTSET_FOR_TABLE | Set to 'yes' to get column metadata from the result set of a select * query. Available in Tableau 2020.4 and newer. |
CAP_JDBC_QUERY_ASYNC | Set to ‘yes’ to run queries on another thread. |
CAP_JDBC_QUERY_CANCEL | Set to ‘yes’ if driver can cancel queries. |
CAP_JDBC_QUERY_DISABLE_AUTO_COMMIT | Set to 'yes' to disable the default auto-commit mode when running query. Available in Tableau 2020.4 and newer. |
CAP_JDBC_QUERY_FORCE_PREPARE | Set to 'yes' to always prepare the query before execution. Available in Tableau 2020.4 and newer. |
CAP_JDBC_SUPPRESS_EMPTY_CATALOG_NAME | Set to 'yes' to ignore missing catalogue. |
CAP_JDBC_SUPPRESS_ENUMERATE_DATABASES | Set to ‘yes’ to disable database enumeration. |
CAP_JDBC_SUPPRESS_ENUMERATE_SCHEMAS | Set to ‘yes’ to disable schema enumeration. |
CAP_JDBC_METADATA_SUPPRESS_PREPARED_QUERY | If CAP_JDBC_METADATA_USE_RESULTSET_FOR_TABLE is enabled, set this capability to 'yes' to disable preparing the query used for reading the table metadata. We will execute the query wrapped with a where-false clause. |
CAP_JDBC_USE_ADAPTIVE_FETCH_SIZE | Set to 'yes' to use ResultSet metadata to determine optimal fetch size. May require CAP_JDBC_QUERY_FORCE_PREPARE to be enabled to work properly. Available in Tableau 2020.4 and newer. |
CAP_MULTIPLE_CONNECTIONS_FROM_SAME_IP | Set to 'no' to prevent Tableau from creating more than one active connection to the database. This is a conservative setting that may increase stability at the expense of performance. |
CAP_QUERY_BOOLEXPR_TO_INTEXPR | Set to 'yes' if Tableau must coerce any Boolean expressions to an integer value in order include in a result set. |
CAP_QUERY_FROM_REQUIRES_ALIAS | Set to 'yes' if the FROM clause must provide an alias for the given table. |
CAP_QUERY_GROUP_ALLOW_DUPLICATES | Set to 'no' if SQL queries cannot contain duplicate expressions in the GROUP BY clause (this is uncommon). |
CAP_QUERY_GROUP_BY_ALIAS | Set to 'yes' if SQL queries with aggregations can reference the grouping columns by their corresponding alias in the SELECT list, e.g. GROUP BY ‘none_ShipCountry_nk. |
CAP_QUERY_GROUP_BY_DEGREE | Set to 'yes' if SQL queries with aggregations can reference the grouping columns by the ordinal position of each column, e.g. GROUP BY 2, 5. See also: CAP_QUERY_SORT_BY_DEGREE |
CAP_QUERY_HAVING_REQUIRES_GROUP_BY | Set to 'yes' if Tableau must use an artificial grouping field for any query which has a HAVING clause but no grouping columns. |
CAP_QUERY_HAVING_UNSUPPORTED | Set to 'yes' if the SQL syntax for HAVING is unsupported. Tableau may be able to work around this using subqueries. See also: CAP_QUERY_SUBQUERIES. |
CAP_QUERY_INCLUDE_GROUP_BY_COLUMNS_IN_SELECT | Set to 'yes' to require all GROUP BY expressions to also appear in the SELECT expression list. |
CAP_QUERY_JOIN_ACROSS_SCHEMAS | Set to 'yes' if SQL queries can express joins between tables located in different schemas. |
CAP_QUERY_JOIN_ASSUME_CONSTRAINED | Set to ‘yes’ to cull inner joins even if the database tables does do not have FK-PK relationships. |
CAP_QUERY_JOIN_PUSH_DOWN_CONDITION_EXPRESSIONS | Set to 'yes' to rewrite joins to simplify the ON clause conditions to simple identifier comparisons. |
CAP_QUERY_JOIN_REQUIRES_SCOPE | Set to 'yes' if SQL queries must scope each join clause within parentheses to ensure a proper order of evaluation. |
CAP_QUERY_JOIN_REQUIRES_SUBQUERY | Set to ‘yes’ to force join expressions involving more than two tables to be composed with subqueries. |
CAP_QUERY_NULL_REQUIRES_CAST | Set to 'yes' if the data source requires that all NULL literals are cast to an explicit data type. |
CAP_QUERY_SELECT_ALIASES_SORTED | Set to 'yes' if Tableau must impose a deterministic order on the SELECT expressions (sorted by alias) to ensure that query results can be properly matched with each field in the Tableau visualisation. This is only required for data sources which do not preserve the aliases of the SELECT expressions when returning metadata with the query results. |
CAP_QUERY_SORT_BY_DEGREE | Set to 'yes' if SQL queries can reference the sorting columns by the ordinal position of each column, e.g. ORDER BY 2, 5. See also: CAP_QUERY_GROUP_BY_DEGREE. |
CAP_QUERY_SUBQUERIES | Set to 'yes' if the data source supports subqueries. |
CAP_QUERY_SUBQUERIES_WITH_TOP | Set to 'yes' if the data source supports a TOP or LIMIT row-limiting clause within a subquery. |
CAP_QUERY_SUBQUERY_DATASOURCE_CONTEXT | Set to 'yes' to use subquery filtered query context to implement data source filters. This capability is available in Tableau 8.0 through Tableau 9.3 only. |
CAP_QUERY_SUBQUERY_QUERY_CONTEXT | Set to 'yes' to force Tableau to use a subquery for context filters instead of a temporary table or locally cached results. |
CAP_QUERY_TOP_N | Set to 'yes' if the data source supports any form of row-limiting clause. The exact forms supported are described below. |
CAP_QUERY_TOPSTYLE_LIMIT | Set to 'yes' if the data source uses LIMIT as the row-limiting clause. |
CAP_QUERY_TOPSTYLE_ROWNUM | Set to 'yes' if the data source supports an Oracle-style filter on ROWNUM as the row-limiting clause. |
CAP_QUERY_TOPSTYLE_TOP | Set to 'yes' if the data source uses TOP as the row-limiting clause. |
CAP_QUERY_USE_QUERY_FUSION | Set to ‘no’ to prevent Tableau from combining multiple individual queries into a single combined query. Turn off this capability for performance tuning or if the database is unable to process large queries. This capability is enabled by default and is available in Tableau 9.0 and later for all data sources except Tableau data extracts. Support for this capability in Tableau data extracts is available in Tableau 9.0.6. |
CAP_SELECT_INTO | Set to 'yes' if Tableau can create a table on the fly from the resultset of another query. See also: CAP_CREATE_TEMP_TABLES. |
CAP_SELECT_TOP_INTO | Set to 'yes' if Tableau can use a TOP or LIMIT row-limiting clause when creating a table from a query result set. |
CAP_SET_ISOLATION_LEVEL_VIA_SQL | Set to 'yes' to force Tableau to set the transaction isolation level for the data source using a SQL query. CAP_SET_ISOLATION_LEVEL_VIA_SQL must be set to 'yes' when any one of the four CAP_ISOLATION_LEVEL capabilities has been set to 'yes'. |
CAP_STORED_PROCEDURE_PREFER_TEMP_TABLE | Set to 'yes' to use a temporary table to support remote queries over the stored procedure result set. |
CAP_STORED_PROCEDURE_REPAIR_TEMP_TABLE_STRINGS | Set to 'yes' to attempt to compute actual string widths if metadata indicates no width or non-positive width. |
CAP_STORED_PROCEDURE_TEMP_TABLE_FROM_BUFFER | Set to 'yes' to populate the temporary table from a result set buffered in entirety. |
CAP_STORED_PROCEDURE_TEMP_TABLE_FROM_NEW_PROTOCOL | Set to ‘yes’ to populate the temporary table from a separate protocol created for just this operation. |
CAP_SUPPRESS_DISCOVERY_QUERIES | Set to 'yes' to prevent Tableau from detecting the supported SQL syntax for a variety of clauses. |
CAP_SUPPRESS_DISPLAY_LIMITATIONS | Set to 'yes' to suppress displaying any warnings about limitations for this data source. |
See also
Other Databases (JDBC) – Describes how to connect to your data using the Other Databases (JDBC) connector.