Some of Teiid's low-level behavior can be configured via system properties, rather than through configuration files. A typical place to set system properties for JBoss AS launches is in the <jboss-install>/bin/<mode>.conf. A property setting has the format -Dproperty=value

• org.teiid.allowNanInfinity - defaults to false. 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.

• org.teiid.useValueCache - defaults to false. 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.

• org.teiid.ansiQuotedIdentifiers - defaults to true. 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.

• org.teiid.subqueryUnnestDefault - defaults to false. Set to true to aggressively unnest subquery IN and EXISTS predicates. If possible the predicate will be unnested to a traditional join and will be eligible for dependent join planning. If a traditional join is not possible (such as with NOT IN) a merge join version of the semijoin or antijoin will be considered by upon the costing information available.

• org.teiid.ODBCPacketSize - defaults to 307200. Target size in bytes of the ODBC results buffer. This is not a hard maximum, lobs and wide rows may use larger buffers.

• org.teiid.decimalAsDouble - defaults to false. 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.

• org.teiid.comparableLobs - defaults to false. 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.

• org.teiid.comparableObject - defaults to false. 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.

• org.teiid.padSpace - defaults to false. 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.

• org.teiid.collationLocale - not set by default, which means that Java's natural (UTF-16) string comparison will be used.  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.

• org.teiid.clientVdbLoadTimeoutMillis - defaults to 5 minutes. 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 waitForLoad connection property.

• org.teiid.enDateNames - defaults to false. 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.

• org.teiid.pushdownDefaultNullOrder - defaults to false. 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.

• org.teiid.requireTeiidCollation - defaults to false. Set to true to force all sorts to be in Teiid's collation (see org.teiid.collationLocale).

• org.teiid.implicitMultiSourceJoin - defaults to true. 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.

• org.teiid.maxStringLength - defaults to 4000. 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.

• org.teiid.calendarTimestampDiff - defaults to true. Set to false to use the Teiid 8.2 and old computation of timestampdiff. NOTE: 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.

• org.teiid.compactBufferFiles - defaults to false. Set to true to have Teiid keep the buffer files more compact (minimizing sparse regions).

• org.teiid.maxMessageSize - defaults to 2097152. 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.

• org.teiid.maxStreamingLobSize - defaults to 4294967296. The maximum size of lobs in bytes that are allowed to be streamed as part of the message from clients.

• org.teiid.defaultIndependentCardinality - defaults to 10. 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.

• org.teiid.dependentJoinPushdownThreshold - defaults to 0. The data width ratio of all pushdown columns to just the equi-join columns that will be considered for a full dependent join pushdown without a hint. A setting less than 1 effectively disables allowing the optimizer to choose to fully push a dependent join. A setting of 1 will only enable full pushdown of joins that only require the use the equi-join columns. And a setting greater than 1 will allow wider data sets to be considered.

• org.teiid.checkPing - defaults to true. 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.

• org.teiid.defaultNullOrder - defaults to LOW. 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.

• org.teiid.iso8601Week - defaults to true. 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.

Security

• org.teiid.allowCreateTemporaryTablesByDefault - defaults to false. Set to true to use the pre-8.0 behavior of allowing any authenticated user to create temp tables without an explicit permission.

• org.teiid.allowFunctionCallsByDefault - defaults to false. 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.

• org.teiid.ignoreUnauthorizedAsterisk - defaults to false. 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.

• org.teiid.sanitizeMessages - defaults to false. 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.

• org.teiid.ODBCRequireSecure - defaults to true. 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).

PostgreSQL Compatibility

These affect Teiid globally, and not just through the ODBC transport.

• org.teiid.backslashDefaultMatchEscape - defaults to false. 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.