System Properties and Environment Variables

Some of Teiid’s low-level behavior can be configured via system or env properties, rather than through configuration files.

A typical place to set system properties for WildFly launches is in the <install>/bin/<mode>.conf. A property setting has the format -Dproperty=value.

With 13.0 environment variables will be checked after the corresponding system property. This allows for Teiid client and server code running in Docker or on OpenShift to be easily configured. The environment property key will be checked by converting it first to upper snake case - which replaces lower case with upper case, any period with _ and separates words with _. For example org.teiid.allowNanInfinity would check the environment key ORG_TEIID_ALLOW_NAN_INFINITY.


Setting Description Default Value


Set to true to allow numeric functions to return NaN (Not A Number) and +-Infinity. Note that these values are not covered by the SQL specification.



Set to true to enable the canonical value cache. Value caching is used dynamically when buffer memory is consumed to reuse identical values and thus reduce the memory consumed by Teiid. There is a computation cost associated with the cache lookup, so enabling this setting is not appropriate for installations handling large volumes of dissimilar data.



Set to false to emulate Teiid 6.x and prior behavior of treating double quoted values without leading identifier parts as string literals, which is not expected by the SQL specification.



If true, the optimizer will aggressively unnest subqueries in WHERE predicates. If possible the predicate will be unnested to a traditional join and will be eligible for dependent join planning.



Target size in bytes of the ODBC results buffer. This is not a hard maximum, lobs and wide rows may use larger buffers.



Set to true to parse exact fixed point literals, e.g. 1.0, as double values rather than as decimal/BigDecimal values and to return a double value from the AVG function for integral values in the same way as releases earlier than 8.0.



Set to true to allow blob and clob column values to be comparable in Teiid. Source type metadata will determine if the comparison can be pushed down.



Set to true to allow object column values to be comparable in Teiid. Source type metadata will determine if the comparison can be pushed down. The object instances are expected to correctly implement java.lang.Comparable.compareTo. If the instance object is not Comparable, then ClassCastExceptions may the thrown.



Set to true to disable the engine enforcement of the OData specification of null ordering. You may want to do this for performance reasons if you do not need conformance and are connecting to a source, such as Postgresql, that has a different default null order than the OData specification.



Set to true to compare strings as if PAD SPACE collation is being used, that is strings are effectively right padded to the same length for comparison. If this property is set, it is not necessary to use the trimStrings translator option.



Set to a Java locale string language[_country[_varient]], where language, country, and variant are two letter codes - see java.util.Locale for more on valid codes.  Note that even if org.teiid.comparableLobs is set, clob values will not be compared using the locale collator.

Not set by default, which means that Java’s natural (UTF-16) string comparison will be used.


The default amount of time a client (currently only local clients) will wait to make a connection to an active VDB before throwing an exception. Clients may override this setting via the loginTimeout connection property.

5 minutes


Set to true to use English month and day names for the system function dayName and monthName, rather than returning names from the Java default locale.  Prior to 8.2 dayName and monthName always returned English names.



Set to true to mimic 8.1 and prior release behavior of pushing the Teiid’s default null order of nulls low if the source has a different default null order and supports explicit null ordering.



Set to true to force all sorts to be in Teiid’s collation (see org.teiid.collationLocale).



Set to false to disable Teiid 8.2 and prior release behavior of implicitly partitioning joins between multi-source tables. When set to false and explicit predicate such as tbl1.source_name = tbl2.source_name is required to partition the results of the join.



Sets the nominal maximum length of strings in Teiid - most operations in Teiid will truncate strings that are larger than this value. Setting this value can also adjust the max size of lob bytes held in memory. NOTE: sources may not appropriately handle string values that are larger than the source supports.


Strings are always fully held in memory. Do not set this value too high as you may experience out of memory errors.


If false and a translator does not specify a collationLocale, then a sort involving character data for a sort/merge join will not be pushed. Teiid defaults to the Java UCS-2 collation, which may not match the default collation for sources, particular tables, or columns. You may set the system property org.teiid.assumeMatchingCollation to true to restore the old default behavior or selectively update the translators to report a collationLocale matching org.teiid.collationLocale (UCS-2 if unset).



Set to false to use the Teiid 8.2 and old computation of timestampdiff. note that: using the old behavior can result in differing results between pushed and non-pushed versions of timestampdiff for intervals greater than seconds as sources use date part and not approximate interval differences.



Set to true to have Teiid keep the buffer files more compact (minimizing sparse regions).



The maximum size of messages in bytes that are allowed from clients. Increase only if clients routinely use large queries and/or non-lob bind values.



The maximum size of lobs in bytes that are allowed to be streamed as part of the message from clients.



The number of independent rows or less that can automatically trigger a dependent join. Increase when tables typically only have cardinality set and more dependent joins are desired.



Can be set to false to disable ping checking for remote JDBC connections. Ping checking should only be disabled in specific circumstances, such as when using an external load balancer and not utilizing the Teiid default load balancing logic. Deprecated as of Teiid 10.2.



Can be one of LOW, FIRST, HIGH, LAST. Sets the default null order for the Teiid engine. This will not be used for source ordering unless org.teiid.pushdownDefaultNullOrder is also set.



Set to true to use ISO 8601 rules for week calculations regardless of the locale. When true the week function will require that week 1 of a year contains the year’s first Thursday. Pushdown week values will be calculated as ISO regardless of this setting.



Set to true to enable widening of values to string in comparisons, which was the default behavior prior to Teiid 9. For example with this setting as false timestamp_col < 'a' will produce an exception whereas when set to true it would effectively evaluate cast(timestamp_col as string) < `a'.



Set to false to not aggressively group joins (typically allowed if there exists an explicit relationship) against the same source for pushdown and rely more upon a cost based ordering.



Set to the desired size in bytes to limit the amount of buffer resources (heap and disk) consumed by a single session’s tuple buffers and table structures. This is based upon the heap memory footprint estimate and may not correspond exactly to heap and especially to disk consumption. In general data in serialized from, whether on disk or in the fixed memory buffer, is between 3 and 8 times smaller than its heap representation which includes overhead such as additional object wrappers, lists, and less compact strings.

2^63 - 1


The system will determine an upper limit from all available memory for a single set of managed batches/pages - which could be a table, result set, or intermediate result - from all of the available buffer manager memory and disk. When this property is true an exception will be thrown when the limit is exceeded. When this property is false a TEIID31292 warning will be logged, which can be a good indicator of a query or environment that should be reviewed.



Set to true to allow a RESULT parameter to appear at in position in a procedure parameter list.



Set to false to allow the pre-10.1 behavior of allowing qualified names in create to be used. For example 'create foreign table x.y …​', rather than 'create foreign table "x.y" …​'



If _x escaping should be used for invalid characters in SQL/XML names. Set to false to use the older behavior of an _u escape.



Set to false to always generate OpenTracing information even if no Span is active.



Set to true to have the ranking functions RANK, DENSE_RANK, and ROW_NUMBER return long instead of integer.



Set to true to have XPath PATH values beginning with / and // in XMLTABLE always be relative to the context item (the same behavior as Oracle). Set to false to have / and // PATH values to be evaluated from the root of the context item (the same behavior as PostgreSQL).



Setting Description Default Value


If true alter and (sysdamin.setProperty) will be allowed at runtime to alter possibly ephemerally the metadata. If false those metadata alterations will not be allowed.



Set to true to use the pre-8.0 behavior of allowing any authenticated user to create temp tables without an explicit permission.



Set to true to use the pre-8.0 behavior of allowing any authenticated user to call any non-system function without an explicit permission.



If true pg/JDBC objects under a hidden schema are still resolvable if fully qualified. If false objects under a hidden schema are never directly resolvable by an end user.



If true unauthorized columns (as determined by data role checking) are not part of select all or qualified select all expansion. If false, the client may set the session variable ignore_unauthorized_asterisk to true to achieve the same behavior.



If true metadata will only be visible in SYS/SYSADMIN tables if the user is permissioned in some way for the given object. If false the non-hidden schema metadata will be visible to any authenticated user.



If true setting the SSL config to login or enabled will require clients to connect appropriately with either a GSS login or SSL respectively. Setting the property to false will allow client to use any authentication and no SSL (which was the behavior of the pg transport prior to 8.9 CR2).



If true query related exception and warnings will have their messages replaced with just the Teiid code. Server side stacktraces will also be removed when sent to the client. This should be enabled if there is a concern about SQL or values being present in the exception/logs. If the log level is increased to debug for the relevant logger, then the sanitizeMessages setting will have no effect.


PostgreSQL Compatibility

These affect Teiid globally, and not just through the ODBC transport.
Setting Description Default Value


When set to false, the VDB will not include Postgresql based system metadata.



Set to true to use '\' as the default escape character for LIKE and SIMILAR TO predicates when no escape is specified. Otherwise Teiid assumes the SQL specification compliant behavior of treating each non-wildcard character as an exact match character.



When false the wrapping begin/commit of a UseDeclareFetch cursor will be ignored as Teiid does not require a transaction.



Is the value that will be reported by the server_version function.

"PostgreSQL 8.2"


System properties can also be set for client VMs. See Additional Socket Client Settings.

results matching ""

    No results matching ""