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.

General

Setting Description Default Value

org.teiid.allowNanInfinity

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.

false

org.teiid.useValueCache

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.

false

org.teiid.ansiQuotedIdentifiers

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.

true

org.teiid.subqueryUnnestDefault

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.

false

org.teiid.ODBCPacketSize

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

307200

org.teiid.decimalAsDouble

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.

false

org.teiid.comparableLobs

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.

false

org.teiid.comparableObject

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.

false

org.teiid.padSpace

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.

false

org.teiid.collationLocale

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.

org.teiid.clientVdbLoadTimeoutMillis

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

org.teiid.enDateNames

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.

false

org.teiid.pushdownDefaultNullOrder

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.

false

org.teiid.requireTeiidCollation

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

false

org.teiid.implicitMultiSourceJoin

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.

true

org.teiid.maxStringLength

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.

4000

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

org.teiid.assumeMatchingCollation

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).

false

org.teiid.calendarTimestampDiff

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.

true

org.teiid.compactBufferFiles

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

false

org.teiid.maxMessageSize

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.

2097152

org.teiid.maxStreamingLobSize

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

4294967296

org.teiid.defaultIndependentCardinality

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.

10

org.teiid.checkPing

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.

true

org.teiid.defaultNullOrder

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.

LOW

org.teiid.iso8601Week

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.

true

org.teiid.widenComparisonToString

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'.

false

org.teiid.aggressiveJoinGrouping

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.

true

org.teiid.maxSessionBufferSizeEstimate

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

org.teiid.enforceSingleMaxBufferSizeEstimate

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.

false

org.teiid.resultAnyPosition

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

false

org.teiid.requireUnqualifiedNames

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" …​'

true

org.teiid.useXMLxEscape

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.

true

org.teiid.tracingWithActiveSpanOnly

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

true

org.teiid.longRanks

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

false

org.teiid.relativeXPath

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).

true

Security

Setting Description Default Value

org.teiid.allowAlter

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.

true

org.teiid.allowCreateTemporaryTablesByDefault

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

false

org.teiid.allowFunctionCallsByDefault

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.

false

org.teiid.hiddenMetadataResolvable

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.

true

org.teiid.ignoreUnauthorizedAsterisk

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.

false

org.teiid.metadataRequiresPermission

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.

true

org.teiid.ODBCRequireSecure

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).

true

org.teiid.sanitizeMessages

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.

false

PostgreSQL Compatibility

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

org.teiid.addPGMetadata

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

true

org.teiid.backslashDefaultMatchEscape

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.

false

org.teiid.honorDeclareFetchTxn

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

false

org.teiid.pgVersion

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

"PostgreSQL 8.2"

Client

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

results matching ""

    No results matching ""