analytics/configuration/xqe/sql.properties

# Licensed Materials - Property of IBM
# IBM Cognos Products: OQP
# (C) Copyright IBM Corp. 2005, 2022
# US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM corp.

# This properties file contains default configuration attributes for all
# relational data sources.  Any data source that is different must override
# the value in their own properties file.
#
# The #define values in comments are the values used by UDA to initialize
# their dbInfo data structure.  We are using this as a starting point for
# the XQE default values.  At some point these can be removed from this file.
#
# Function parameter types are defined using the following domains/types:
#
# domain 				types
# ------				-----
# any					any data type. Use with care!
# binary				binary
# blob					blob, clob
# boolean				boolean
# collection			row, array, multiset
# datetime				date, time, time_with_time_zone, timestamp, timestamp_with_time_zone
# interval_day_time		interval_day, interval_day_to_hour, interval_day_to_minute, interval_day_to_second
#						interval_hour, interval_hour_to_minute, interval_hour_to_second
#						interval_minute, interval_minute_to_second
#						interval_second
# interval_year_month	interval_year, interval_year_to_month
#						interval_month
# numeric				decimal, double, float, integer, levelNumber, long, smallint
# exact					decimal, integer, levelNumber, long, smallint
# text					businessKey, char, currencyCode, dimensionUniqueName, externalBuffer, 
#						hierarchyUniqueName, levelLabel, levelUniqueName, memberCaption, 
#						memberDescription, memberRollup, memberUniqueName, nchar, nvarchar, 
#						parentUniqueName, queryItemModelID, string, varchar, xml
# variant				variant
#
# The natural function overloads are listed herein, but with empty patterns 
# to act as a reference when creating overrides in DB specific properties files.

#
# Product information.
#
product.name=

#
# Delimiters.
#
delimiters.catalogDelimiter=
delimiters.schemaDelimiter=
delimiters.tableDelimiter=
delimiters.columnDelimiter=
delimiters.identifierQuoteString=
delimiters.searchStringEscape=
delimiters.catalogSeparator=
delimiters.literalQuoteEscape=
delimiters.commentBegin=/*
delimiters.commentEnd=*/

#
# Keywords.
#
keywords.columnAlias=AS

#
# Various limits. A value of 0 means no limit, or the limit is unknown.
#
limits.maxBinaryLiteralLength=0
limits.maxCharLiteralLength=0
limits.maxColumnNameLength=0
limits.maxColumnsInGroupBy=0
limits.maxColumnsInIndex=0
limits.maxColumnsInOrderBy=0
limits.maxColumnsInSelect=0
limits.maxColumnsInTable=0
limits.maxConnections=0
limits.maxCursorNameLength=0
limits.maxIndexLength=0
limits.maxSchemaNameLength=0
limits.maxProcedureNameLength=0
limits.maxCatalogNameLength=0
limits.maxRowSize=0
limits.maxStatementLength=0
limits.maxStatements=0
limits.maxTableNameLength=0
limits.maxTablesInSelect=0
limits.maxUserNameLength=0
limits.defaultTransactionIsolation=0
limits.maxLengthInClause=0
limits.castClobToVarcharMaxSize=1024
limits.maxDecimalPrecision=31

#
# General settings.
#
general.nullsAreSortedHigh=false
general.nullsAreSortedLow=false
general.nullsAreSortedAtStart=false
general.nullsAreSortedAtEnd=false
general.nullsOrdering=true
general.nullsOrderingInWindowSpecification=true

#
# Cursor options - appended to end of generated SELECT statement.
general.cursorOptions=

#
# RTC 61393
# Indicates whether to set nulls ok indicator to true when processing JDBC result set metadata.
# This is set to true only if the value returned by invoking java.sql.ResultSetMetadata.isNullable() is
# possibly incorrect.
#
general.forceNullsOk=false

#
# Override sampling policy with a different one.
# 1. tablesample accepting values such as BERNOULLI or SYSTEM
# 2. rowsample accepting values such as NTH or RANDOM
#
sampling.tablesample=BERNOULLI
sampling.rowsample=NTH

#
# Various features.
#
supports.duplicateColumnsInSelectList=true
supports.duplicateColumnNamesInSelectList=true
supports.columnAliasing=true
supports.tableCorrelationNames=true
supports.expressionsInOrderBy=true
supports.expressionsInOrderByOnGroupedQuery=true
supports.distinct.WindowAggregation=true
# Determines whether the expression should be built off of the FID
# of the table expression (true), or the expression of the underlying 
# projection that the FID refers to (false)
supports.aliasInOrderByExpression=true
supports.expressionsInINPredicate=true
supports.expressionsInSelectList=true
supports.literalsInSelectList=true
supports.expressionsInLikePattern=true
supports.booleanExpressionsInSelectList=true
supports.fieldsOfComplexTypeInSelectList=true
supports.likeEscapeClause=true
supports.outerJoins=true
supports.fullOuterJoins=true
supports.limitedOuterJoins=false
supports.subqueriesInComparisons=true
supports.subqueriesInExists=true
supports.subqueriesInIns=true
supports.subqueriesInQuantifieds=true
supports.subqueriesInCase=true
supports.subqueriesInAggregate=true
supports.subqueriesInGroupBy=true
supports.correlatedSubqueries=true
supports.correlatedSubqueriesInSelectList=true
supports.correlatedSubqueriesInIns=true
supports.transactions=true
supports.withClauseInDerivedTable=true
supports.nestedWithClause=true
supports.integerDivision=true
supports.nestedOlap=false
supports.groupedWindowedTables=true
supports.derivedColumnLists=true
supports.orderByAlias=true
supports.orderByName=true
supports.orderByOrdinal=true
supports.groupByAlias=false
supports.blobsInGroupBy=false
supports.blobsInOrderBy=false
supports.scalarSubqueries=true
supports.emptyStringIsNull=false
supports.concatNullIsNull=true
supports.sqlserverWindowBehaviour=false
supports.mixedCaseIdentifiers=false
supports.mixedCaseQuotedIdentifiers=false
supports.charLiteralRoundTrip=true
supports.expressionsInGroupBy=true
supports.equiJoins=true
supports.thetaJoins=true
supports.stitchJoins=true
supports.crossProducts=true
supports.rewriteImplicitCrossJoins=false
supports.recursiveWithClause=true
supports.storedProcedureColumnUnique=true
supports.implicitTypeConversion=true
supports.orderByUnrelated=true
supports.groupByUnrelated=true
supports.schemasInDataManipulation=true
#classic stack compatibility
supports.nonStandardDatetimeComparison=false
supports.multipleDistinctAggregates=true
supports.hints=true
supports.rowNumberNoOrderBy=false
supports.parameterMarkers=true
supports.constantsInWindows=true
supports.orderByInDerivedTable=true
supports.callProcedureInDerivedTable=true
#join condition
supports.join.betweenInOnClause=true
supports.join.inPredicateInOnClause=true
supports.join.isNullInOnClause=true
supports.join.likeInOnClause=true
supports.join.notInOnClause=true
supports.join.orInOnClause=true
supports.join.subqueriesInOnClause=true
supports.join.onlyEquiWithAnd=false
supports.join.inner.limitedThetaJoins=false
supports.join.outer.thetaJoins=true
supports.join.full.thetaJoins=true
supports.join.full.distinctJoins=true
supports.castClobToVarcharWithoutSubstring=false
#isolation level support
supports.isolationLevelReadUncommitted=true
supports.isolationLevelReadCommitted=true
supports.isolationLevelRepeatableRead=true
supports.isolationLevelSerializable=true
supports.constantsInCount=true
supports.columnFiltersOnly=false
supports.hanaInputParameters=false
supports.top.distinct=true
supports.top.set=true
supports.top.0=true
#casting with formatting pattern support
supports.formatters.string_to_date=true
supports.formatters.string_to_time=true
supports.formatters.string_to_time_with_time_zone=true
supports.formatters.string_to_timestamp=true
supports.formatters.string_to_timestamp_with_time_zone=true
#preparedstatement
supports.preparedstatement=true

# APAR 121556
# If true, the db connection associated with a request is no longer
# reuseable for subsequent requests. For certain dbs, this happens
# after a cancel request is sent to the db.
supports.connectionDroppedOnCancel=false

#
# Performance properties (force certain transformations to be applied). 
#
performance.convertGroupByToDistinct=false
performance.convertHavingToWhere=false
performance.convertDistinctToGroupBy=false
performance.transitiveClosure=false
performance.predicatePushdown=false
performance.semiJoin=false

# RTC 377496
# Set this entry to F to avoid generation of predicates of the form
# T1.C1 = T2.C1 OR ( T1.C1 IS NULL AND T2.C1 IS NULL ). Care must be
# taken, however, since doing so may cause data integrity problems if
# data contains null values.
performance.generateEqualOrNull=true

#
# Command.
#
commands.Select=SELECT
commands.Call=CALL

#
# Tables.
#
tables.derived=true
tables.lateral.derived=true
tables.joined=true

#
# Constructors.
#
constructors.table=true
constructors.row=true
constructors.array=true
constructors.period=true
constructors.map=true

#
# Constructors - context overrides.
#
constructors.row.between=true
constructors.row.comparison=true
constructors.row.in=true
constructors.row.isDistinctFrom=true
constructors.row.simpleCase=true
#DB2 supports table value constructor in IN clause, but not row expression list.
constructors.row.inListToTable=false

#
# Clauses.
#
clauses.From=FROM
clauses.Where=WHERE
clauses.GroupBy=GROUP BY
clauses.Having=HAVING
clauses.With=WITH
clauses.WithRecursive=WITH RECURSIVE
clauses.OrderBy=ORDER BY
clauses.Distinct=DISTINCT
clauses.Top=FETCH FIRST %1$s ROWS ONLY
clauses.Top.Position=standard
clauses.At=AT
clauses.Window=WINDOW
clauses.TableSampleBeforeAlias=false
clauses.TableSampleSystem=TABLESAMPLE SYSTEM (%1$s)@2[ REPEATABLE (%2$s)]
clauses.TableSampleBernoulli=TABLESAMPLE BERNOULLI (%1$s)@2[ REPEATABLE (%2$s)]
clauses.ForSystemTimeAsOf=FOR SYSTEM_TIME AS OF %1$s
clauses.ForSystemTimeFrom=FOR SYSTEM_TIME FROM %1$s TO %2$s
clauses.ForSystemTimeBetween=FOR SYSTEM_TIME BETWEEN %1$s AND %2$s

#
# Joins.
#
joins.Cross=%1$s CROSS JOIN %2$s
joins.Inner=%1$s INNER JOIN %2$s ON %3$s
joins.LeftOuter=%1$s LEFT OUTER JOIN %2$s ON %3$s
joins.RightOuter=%1$s RIGHT OUTER JOIN %2$s ON %3$s
joins.FullOuter=%1$s FULL OUTER JOIN %2$s ON %3$s
joins.RightNested=%1$s
# Bracket inner join groups in order to avoid parsing error
# A LOJ
#  B INNER JOIN C ON B.x = C.x
# ON A.x = B.x
# is converted to:
# A LOJ
#    (B INNER JOIN C ON B.x = C.x)
# ON A.x = B.x
#
joins.BracketInner=false

#
# Set operators.
#
operators.set.Union=%1$s UNION %2$s
operators.set.Union.all=%1$s UNION ALL %2$s
operators.set.Intersect=%1$s INTERSECT %2$s
operators.set.Intersect.all=%1$s INTERSECT ALL %2$s
operators.set.Except=%1$s EXCEPT %2$s
operators.set.Except.all=%1$s EXCEPT ALL %2$s

#
# Logical operators.
#
operators.logical.And=%1$s AND %2$s
operators.logical.Or=%1$s OR %2$s
operators.logical.Not=NOT ( %1$s )
operators.logical.Is=( %1$s ) IS %2$s
operators.logical.IsNot=( %1$s ) IS NOT %2$s
operators.logical.IsJson=( %1$s ) IS JSON
operators.logical.IsNotJson=( %1$s ) IS NOT JSON

#
# Arithmetic operators.
#
operators.arithmetic.Add[any,any]=%1$s + %2$s
operators.arithmetic.Subtract[any,any]=%1$s - %2$s
operators.arithmetic.Multiply[any,any]=%1$s * %2$s
operators.arithmetic.Divide[any,any]=%1$s / %2$s
operators.arithmetic.Negate[any]=-%1$s
operators.arithmetic.UnaryPlus[any]=+%1$s
operators.arithmetic.Concat[any,any]=%1$s || %2$s

#
# Group By Operators
#
operators.groupBy.Rollup=ROLLUP
operators.groupBy.Cube=CUBE
operators.groupBy.GroupingSets=GROUPING SETS

#
# Comparison predicates.
#
predicates.comparison.LessThan[any,any]=%1$s < %2$s
predicates.comparison.LessThanOrEquals[any,any]=%1$s <= %2$s
predicates.comparison.Equals[any,any]=%1$s = %2$s
predicates.comparison.NotEquals[any,any]=%1$s <> %2$s
predicates.comparison.GreaterThan[any,any]=%1$s > %2$s
predicates.comparison.GreaterThanOrEquals[any,any]=%1$s >= %2$s

#
# Various predicates.
#
predicates.Between[any,any,any]=%1$s BETWEEN %2$s AND %3$s
predicates.In[any,any]=%1$s IN ( %2$s )
predicates.Overlaps[any,any,any,any]=(%1$s, %2$s) OVERLAPS (%3$s, %4$s)
predicates.IsNull=%1$s IS NULL
predicates.IsNotNull=%1$s IS NOT NULL
predicates.Like=%1$s LIKE %2$s
predicates.Like.escape=%1$s LIKE %2$s ESCAPE %3$s
predicates.LikeRegex=%1$s LIKE_REGEX %2$s
predicates.LikeRegex.flag=%1$s LIKE_REGEX %2$s FLAG %3$s
predicates.Similar=%1$s SIMILAR TO %2$s
predicates.Similar.escape=%1$s SIMILAR TO %2$s ESCAPE %3$s
predicates.Exists=EXISTS %1$s
predicates.All=ALL %1$s
predicates.Any=ANY %1$s
predicates.Some=SOME %1$s
predicates.IsDistinctFrom[any,any]=%1$s IS DISTINCT FROM %2$s
predicates.IsNotDistinctFrom[any,any]=%1$s IS NOT DISTINCT FROM %2$s

#
# Period predicates.
#
predicates.PeriodOverlaps[any,any]=%1$s OVERLAPS %2$s
predicates.PeriodEquals[any,any]=%1$s EQUALS %2$s
predicates.PeriodContains[any,any]=%1$s CONTAINS %2$s
predicates.PeriodPrecedes[any,any]=%1$s PRECEDES %2$s
predicates.PeriodSucceeds[any,any]=%1$s SUCCEEDS %2$s
predicates.PeriodImmediatelyPrecedes[any,any]=%1$s IMMEDIATELY PRECEDES %2$s
predicates.PeriodImmediatelySucceeds[any,any]=%1$s IMMEDIATELY SUCCEEDS %2$s

#
# Expressions.
#
expressions.SearchedCase=CASE
expressions.SimpleCase=CASE
# NULLIF(%1$s, %2$s) is equivalent to CASE WHEN %1$s = %2$s THEN NULL ELSE %1$s END
expressions.NullIf=NULLIF(%1$s, %2$s)
expressions.Coalesce=COALESCE(%1$s)
expressions.SearchedCase.compatibleResults=true

# Minimum number of arguments for Coalesce function.
expressions.Coalesce.minArgs=1

#
# Cast expression.
#
expressions.Cast[any,any]=CAST(%1$s AS %2$s)
expressions.Cast[any,any,any]=CAST(%1$s AS %2$s %5$s)

#
# Extract expression.
#
expressions.Extract.YEAR[any]=EXTRACT(YEAR FROM %1$s)
expressions.Extract.MONTH[any]=EXTRACT(MONTH FROM %1$s)
expressions.Extract.DAY[any]=EXTRACT(DAY FROM %1$s)
expressions.Extract.HOUR[any]=EXTRACT(HOUR FROM %1$s)
expressions.Extract.MINUTE[any]=EXTRACT(MINUTE FROM %1$s)
expressions.Extract.SECOND[any]=EXTRACT(SECOND FROM %1$s)
expressions.Extract.TIMEZONE_HOUR[any]=EXTRACT(TIMEZONE_HOUR FROM %1$s)
expressions.Extract.TIMEZONE_MINUTE[any]=EXTRACT(TIMEZONE_MINUTE FROM %1$s)
expressions.Extract.EPOCH[any]=EXTRACT(EPOCH FROM %1$s)

#
# Trim expression.
#
expressions.Trim.BOTH[any]=TRIM(BOTH FROM %1$s)
expressions.Trim.LEADING[any]=TRIM(LEADING FROM %1$s)
expressions.Trim.TRAILING[any]=TRIM(TRAILING FROM %1$s)
expressions.Trim.BOTH[any,any]=TRIM(BOTH %1$s FROM %2$s)
expressions.Trim.LEADING[any,any]=TRIM(LEADING %1$s FROM %2$s)
expressions.Trim.TRAILING[any,any]=TRIM(TRAILING %1$s FROM %2$s)

#
# Windowed aggregates (SQL/OLAP).
#
olap.Max[any]=MAX(%1$s)
olap.Min[any]=MIN(%1$s)
olap.Sum[any]=SUM(%1$s)
olap.Avg[any]=AVG(%1$s)
olap.Count[any]=COUNT(%1$s)
olap.CountStar[]=COUNT(*)
olap.StdDevSamp[any]=STDDEV_SAMP(%1$s)
olap.StdDevPop[any]=STDDEV_POP(%1$s)
olap.VarSamp[any]=VAR_SAMP(%1$s)
olap.VarPop[any]=VAR_POP(%1$s)
olap.Rank[]=RANK()
olap.DenseRank[]=DENSE_RANK()
olap.PercentRank[]=PERCENT_RANK()
olap.CumeDist[]=CUME_DIST()
olap.PercentileCont[any,any]=PERCENTILE_CONT(%1$s) WITHIN GROUP (ORDER BY %2$s)
olap.PercentileDisc[any,any]=PERCENTILE_DISC(%1$s) WITHIN GROUP (ORDER BY %2$s)
olap.Median[any]=MEDIAN(%1$s)
olap.RowNumber[]=ROW_NUMBER()
olap.FirstValue[any]=FIRST_VALUE(%1$s)
olap.LastValue[any]=LAST_VALUE(%1$s)
olap.NTile[any]=NTILE(%1$s)
olap.Tertile[]=TERTILE()
olap.RatioToReport[any]=RATIO_TO_REPORT(%1$s)
olap.Difference[any]=DIFFERENCE(%1$s)
olap.Lag[any]=LAG(%1$s)
olap.Lag[any,any]=LAG(%1$s, %2$s)
olap.Lag[any,any,any]=LAG(%1$s, %2$s, %3$s)
olap.Lag[any,any,any,any]=LAG(%1$s, %2$s, %3$s) %4$s
olap.Lead[any]=LEAD(%1$s)
olap.Lead[any,any]=LEAD(%1$s, %2$s)
olap.Lead[any,any,any]=LEAD(%1$s, %2$s, %3$s)
olap.Lead[any,any,any,any]=LEAD(%1$s, %2$s, %3$s) %4$s
olap.NthValue[any,any]=NTH_VALUE(%1$s, %2$s)
olap.NthValue[any,any,any]=NTH_VALUE(%1$s, %2$s) %3$s
olap.NthValue[any,any,any,any]=NTH_VALUE(%1$s, %2$s) %3$s %4$s
olap.Collect[any]=COLLECT(%1$s)

#
# Window clause.
#
olap.Window=OVER(%1$s)
olap.PartitionBy=PARTITION BY %1$s
olap.OrderBy=ORDER BY %1$s

#
# Window specification
# A list of windows specifications that are supported by the DB
# P = PARTITION BY
# O = ORDER BY
# F = FRAME 
# These settings are NOT consulted for the following functions:
# RANK, DENSE_RANK, PERCENT_RANK, LAG, LEAD, NTILE and ROW_NUMBER.
#
olap.Window.Specification[POF]=true
olap.Window.Specification[PO]=true
olap.Window.Specification[OF]=true
olap.Window.Specification[PF]=true
olap.Window.Specification[P]=true
olap.Window.Specification[O]=true
olap.Window.Specification[F]=true
olap.Window.Specification[]=true
olap.Window.Frame.Moving=true

#
# Olap (distinct).
#
olap.Max.distinct[any]=MAX(DISTINCT %1$s)
olap.Min.distinct[any]=MIN(DISTINCT %1$s)
olap.Sum.distinct[any]=SUM(DISTINCT %1$s)
olap.Avg.distinct[any]=AVG(DISTINCT %1$s)
olap.Count.distinct[any]=COUNT(DISTINCT %1$s)

#
# Aggregates.
#
aggregates.Max[any]=MAX(%1$s)
aggregates.Min[any]=MIN(%1$s)
aggregates.Sum[any]=SUM(%1$s)
aggregates.Avg[any]=AVG(%1$s)
aggregates.Count[any]=COUNT(%1$s)
aggregates.CountStar[]=COUNT(*)
aggregates.StdDevSamp[any]=STDDEV_SAMP(%1$s)
aggregates.StdDevPop[any]=STDDEV_POP(%1$s)
aggregates.VarSamp[any]=VAR_SAMP(%1$s)
aggregates.VarPop[any]=VAR_POP(%1$s)
aggregates.Rank[any,any]=RANK(%1$s) WITHIN GROUP (ORDER BY %2$s)
aggregates.DenseRank[any,any]=DENSE_RANK(%1$s) WITHIN GROUP (ORDER BY %2$s)
aggregates.PercentRank[any,any]=PERCENT_RANK(%1$s) WITHIN GROUP (ORDER BY %2$s)
aggregates.CumeDistH[any,any]=CUME_DIST(%1$s) WITHIN GROUP (ORDER BY %2$s)
aggregates.PercentileCont[any,any]=PERCENTILE_CONT(%1$s) WITHIN GROUP (ORDER BY %2$s)
aggregates.PercentileDisc[any,any]=PERCENTILE_DISC(%1$s) WITHIN GROUP (ORDER BY %2$s)
aggregates.Median[any]=MEDIAN(%1$s)
aggregates.XMLAgg=XMLAGG(%1$s@2[ ORDER BY %2$s])
aggregates.Grouping[any]=GROUPING(%1$s)
aggregates.ArrayAgg[any]=ARRAY_AGG(%1$s)
aggregates.ArrayAgg[any,any]=ARRAY_AGG(%1$s ORDER BY %2$s)
aggregates.Collect[any]=COLLECT(%1$s)
aggregates.ApproxCountDistinct[any]=APPROX_COUNT_DISTINCT(%1$s)

#
# Aggregates (distinct).
#
aggregates.Max.distinct[any]=MAX(DISTINCT %1$s)
aggregates.Min.distinct[any]=MIN(DISTINCT %1$s)
aggregates.Sum.distinct[any]=SUM(DISTINCT %1$s)
aggregates.Avg.distinct[any]=AVG(DISTINCT %1$s)
aggregates.Count.distinct[any]=COUNT(DISTINCT %1$s)

#
# Linear regression aggregates.
#
aggregates.Corr[any,any]=CORR(%1$s, %2$s)
aggregates.CovarPop[any,any]=COVAR_POP(%1$s, %2$s)
aggregates.CovarSamp[any,any]=COVAR_SAMP(%1$s, %2$s)
aggregates.RegrAvgX[any,any]=REGR_AVGX(%1$s, %2$s)
aggregates.RegrAvgY[any,any]=REGR_AVGY(%1$s, %2$s)
aggregates.RegrCount[any,any]=REGR_COUNT(%1$s, %2$s)
aggregates.RegrIntercept[any,any]=REGR_INTERCEPT(%1$s, %2$s)
aggregates.RegrR2[any,any]=REGR_R2(%1$s, %2$s)
aggregates.RegrSlope[any,any]=REGR_SLOPE(%1$s, %2$s)
aggregates.RegrSXX[any,any]=REGR_SXX(%1$s, %2$s)
aggregates.RegrSXY[any,any]=REGR_SXY(%1$s, %2$s)
aggregates.RegrSYY[any,any]=REGR_SYY(%1$s, %2$s)

#
# JSON aggregates.
#
aggregates.JSONArrayAgg=JSON_ARRAYAGG(%1$s@2[ ORDER BY %1$s])
aggregates.JSONObjectAgg=JSON_OBJECTAGG(%1$s, %2$s@3[ ORDER BY %3$s]])

#
# Character scalar functions.
#
functions.CharLength[any]=CHAR_LENGTH(%1$s)
functions.OctetLength[any]=OCTET_LENGTH(%1$s)
functions.BitLength[any]=BIT_LENGTH(%1$s)
functions.Upper[any]=UPPER(%1$s)
functions.Lower[any]=LOWER(%1$s)
functions.Substring[any,any]=SUBSTRING(%1$s FROM %2$s)
functions.Substring[any,any,any]=SUBSTRING(%1$s FROM %2$s FOR %3$s)
functions.Position[any,any]=POSITION(%1$s IN %2$s)
functions.Index[any,any]=INDEX(%1$s, %2$s)
functions.Ascii[any]=
functions.Translate[any,any]=TRANSLATE(%1$s USING %2$s)
functions.Normalize[any]=NORMALIZE(%1$s)
functions.Normalize[any,any]=NORMALIZE(%1$s, %2$s)
functions.Normalize[any,any,any]=NORMALIZE(%1$s, %2$s, %3$s)

#Substring function to negative START value to parse the input string from its rightmost end. 
#It's not a standard SQL function, so leave the definition empty.
functions.SubstringR[any,any]=
functions.SubstringR[any,any,any]=

#
# Regular expression functions.
#
functions.SubstringRegex[any,any,any,any,any]=SUBSTRING_REGEX(%1$s@5[ FLAG %5$s] IN %2$s@3[ FROM %3$s]@4[ OCCURRENCE %4$s])
functions.OccurrencesRegex[any,any,any,any]=OCCURRENCES_REGEX(%1$s@4[ FLAG %4$s] IN %2$s@3[ FROM %3$s])
functions.PositionRegex[any,any,any,any,any,any]=POSITION_REGEX(@1[%1$s ]%2$s@6[ FLAG %6$s] IN %3$s@4[ FROM %4$s]@5[ OCCURRENCE %5$s])

#
# Numeric scalar functions.
#
functions.Abs[any]=ABS(%1$s)
functions.Ceiling[any]=CEILING(%1$s)
functions.Exp[any]=EXP(%1$s)
functions.Floor[any]=FLOOR(%1$s)
functions.Ln[any]=LN(%1$s)
functions.Log10[any]=LOG10(%1$s)
functions.Mod[any,any]=MOD(%1$s, %2$s)
functions.Power[any,any]=POWER(%1$s, %2$s)
functions.Random[]=RANDOM()
functions.Random[any]=RANDOM(%1$s)
functions.Round[any]=ROUND(%1$s)
functions.Round[any,any]=ROUND(%1$s, %2$s)
functions.Round[any,any,any]=ROUND(%1$s, %2$s, %3$s)
functions.Sign[any]=SIGN(%1$s)
functions.Sqrt[any]=SQRT(%1$s)
functions.WidthBucket[any,any,any,any]=WIDTH_BUCKET(%1$s, %2$s, %3$s, %4$s)

#
# Array scalar functions.
#
functions.Cardinality[any]=CARDINALITY(%1$s)
functions.TrimArray[any,any]=TRIM_ARRAY(%1$s, %2$s)

#
# Trigonometric functions.
#
functions.Arccos[any]=ACOS(%1$s)
functions.Arcsin[any]=ASIN(%1$s)
functions.Arctan[any]=ATAN(%1$s)
functions.Cos[any]=COS(%1$s)
functions.Coshyp[any]=COSH(%1$s)
functions.Sin[any]=SIN(%1$s)
functions.Sinhyp[any]=SINH(%1$s)
functions.Tan[any]=TAN(%1$s)
functions.Tanhyp[any]=TANH(%1$s)

#
# Datetime value functions.
#
functions.CurrentDate[]=CURRENT_DATE
functions.CurrentTime[]=CURRENT_TIME
functions.CurrentTimestamp[]=CURRENT_TIMESTAMP
functions.LocalTime[]=LOCALTIME
functions.LocalTimestamp[]=LOCALTIMESTAMP
functions.CurrentTime[numeric]=CURRENT_TIME(%1$s)
functions.CurrentTimestamp[numeric]=CURRENT_TIMESTAMP(%1$s)
functions.LocalTime[numeric]=LOCALTIME(%1$s)
functions.LocalTimestamp[numeric]=LOCALTIMESTAMP(%1$s)

#
# XML functions.
#
functions.XMLAttributes=XMLATTRIBUTES(%1$s)
functions.XMLComment=XMLCOMMENT(%1$s)
functions.XMLConcat=XMLCONCAT(%1$s)
functions.XMLDocument=XMLDOCUMENT(%1$s)
functions.XMLElement=XMLELEMENT(NAME %1$s@2[, %2$s]@3[, %3$s]@5[, %5$s]@4[ OPTION %4$s])
functions.XMLExists=XMLEXISTS(%1$s@2[ PASSING %2$s])
functions.XMLForest=XMLFOREST(@1[%1$s, ]%2$s)
functions.XMLParse=XMLPARSE(%1$s %2$s %3$s)
functions.XMLPI=XMLPI(NAME %1$s@2[, %2$s])
functions.XMLNamespaces=XMLNAMESPACES(%1$s)
functions.XMLQuery=XMLQUERY(%1$s@3[ PASSING %3$s] %2$s)
functions.XMLSerialize=XMLSERIALIZE(%1$s %2$s AS %3$s)
functions.XMLTable=XMLTABLE(@1[$1%s ]%2$s, %3$s %4$s)
functions.XMLText=XMLTEXT(%1$s)
functions.XMLTransform=XMLTRANSFORM(%1$s, %2$s)
functions.XMLValidate=XMLVALIDATE(%1$s)
functions.XMLElement.ContentOption.NULL_ON_NULL=true
functions.XMLElement.ContentOption.EMPTY_ON_NULL=true
functions.XMLForest.ContentOption.NULL_ON_NULL=true
functions.XMLForest.ContentOption.EMPTY_ON_NULL=true
functions.XMLParse.DocumentOrContent.DOCUMENT=true
functions.XMLParse.DocumentOrContent.CONTENT=true
functions.XMLParse.WhitespaceOption.STRIP_WHITESPACE=true
functions.XMLParse.WhitespaceOption.PRESERVE_WHITESPACE=true
functions.XMLQuery.EmptyHandlingOption.NULL_ON_EMPTY=true
functions.XMLQuery.EmptyHandlingOption.EMPTY_ON_EMPTY=true
functions.XMLSerialize.DeclarationOption.INCLUDING_XMLDECLARATION=true
functions.XMLSerialize.DeclarationOption.EXCLUDING_XMLDECLARATION=true

#
# JSON functions.
#
functions.JSONObject=JSON_OBJECT(%2$s@1[ %1$s])
functions.JSONArray=JSON_ARRAY(%2$s@1[ %1$s])
functions.JSONExists=JSON_EXISTS(%1$s, %2$s)
functions.JSONQuery=JSON_QUERY(%1$s, %2$s@3[ %3$s])
functions.JSONTable=JSON_TABLE($1%s, %2$s %3$s)
functions.JSONValue=JSON_VALUE(%1$s, %2$s@3[ %3$s])

#
# Business date functions.
#
functions.AddHours[any,any]=_ADD_HOURS(%1$s, %2$s)
functions.AddMinutes[any,any]=_ADD_MINUTES(%1$s, %2$s)
functions.AddSeconds[any,any]=_ADD_SECONDS(%1$s, %2$s)
functions.AddFractionalSeconds[any,any]=_ADD_FRACTIONAL_SECONDS(%1$s, %2$s)
functions.AddDays[any,any]=_ADD_DAYS(%1$s, %2$s)
functions.AddWeeks[any,any]=_ADD_WEEKS(%1$s, %2$s)
functions.AddMonths[any,any]=_ADD_MONTHS(%1$s, %2$s)
functions.AddQuarters[any,any]=_ADD_QUARTERS(%1$s, %2$s)
functions.AddYears[any,any]=_ADD_YEARS(%1$s, %2$s)
functions.Age[any]=_AGE(%1$s)
functions.FractionalSecondsBetween[any,any]=_FRACTIONAL_SECONDS_BETWEEN(%1$s, %2$s)
functions.SecondsBetween[any,any]=_SECONDS_BETWEEN(%1$s, %2$s)
functions.MinutesBetween[any,any]=_MINUTES_BETWEEN(%1$s, %2$s)
functions.HoursBetween[any,any]=_HOURS_BETWEEN(%1$s, %2$s)
functions.DaysBetween[any,any]=_DAYS_BETWEEN(%1$s, %2$s)
functions.WeeksBetween[any,any]=_WEEKS_BETWEEN(%1$s, %2$s)
functions.MonthsBetween[any,any]=_MONTHS_BETWEEN(%1$s, %2$s)
functions.QuartersBetween[any,any]=_QUARTERS_BETWEEN(%1$s, %2$s)
functions.YearsBetween[any,any]=_YEARS_BETWEEN(%1$s, %2$s)
functions.DayOfWeek[any,any]=_DAY_OF_WEEK(%1$s, %2$s)
functions.DayOfYear[any]=_DAY_OF_YEAR(%1$s)
functions.DaysToEndOfMonth[any]=_DAYS_TO_END_OF_MONTH(%1$s)
functions.FirstOfMonth[any]=_FIRST_OF_MONTH(%1$s)
functions.LastOfMonth[any]=_LAST_OF_MONTH(%1$s)
functions.MakeTimestamp[any,any,any]=_MAKE_TIMESTAMP(%1$s, %2$s, %3$s)
functions.WeekOfYear[any]=_WEEK_OF_YEAR(%1$s)
functions.YMDIntBetween[any,any]=_YMDINT_BETWEEN(%1$s, %2$s)

#
# Table functions.
#
functions.Unnest=UNNEST(%1$s)

#
# Literals.
#
literals.binary=true
literals.blob=false
literals.clob=false
literals.boolean=true
literals.date=true
literals.time=true
literals.time_with_time_zone=true
literals.timestamp=true
literals.timestamp_with_time_zone=true
literals.interval_day=true
literals.interval_day_to_hour=true
literals.interval_day_to_minute=true
literals.interval_day_to_second=true
literals.interval_hour=true
literals.interval_hour_to_minute=true
literals.interval_hour_to_second=true
literals.interval_minute=true
literals.interval_minute_to_second=true
literals.interval_second=true
literals.interval_year=true
literals.interval_year_to_month=true
literals.interval_month=true
literals.smallint=true
literals.integer=true
literals.long=true
literals.float=true
literals.double=true
literals.decimal=true
literals.char=true
literals.nchar=true
literals.varchar=true
literals.nvarchar=true
literals.xml=false
literals.datalink=false

#
# Literal constraints.
#
literals.time.fractional_seconds=true

#
# Literal format specifications. Formats are compatible with String.format().
# Values for default behaviour are listed.
# Only char, temporal and string types can be overridden.
# Fractional seconds are presented as a string of up to 10 characters: '.' followed by 9 character 
#     0-padded string representing nanoseconds or empty. 
# Literal format in specific context: literals.format.<datatype>.<context>
# Currently only 'procedure' is supported and denotes stored procedure parameter.
#	  ex. literals.format.date.procedure={d '%1$04d-%2$02d-%3$02d'}

# 1 parameter (string)
literals.format.binary=X'%s'
# 1 parameter (string)
literals.format.clob='%s'
# colon separated values for TRUE, FALSE and UNKNOWN
literals.format.boolean=TRUE:FALSE:UNKNOWN
# 3 parameters (int year, int month, int day)
literals.format.date=DATE '%1$04d-%2$02d-%3$02d'
# 4 parameters (int hour, int minute, int seconds, string fractional seconds)
literals.format.time=TIME '%1$02d:%2$02d:%3$02d%4$.10s'
# 7 parameters (int hour, int minute, int seconds, string fractional seconds, int tz hour, int tz minute, char tz sign)
literals.format.time_with_time_zone=TIME '%1$02d:%2$02d:%3$02d%4$.10s%7$c%5$02d:%6$02d'
# 7 parameters (int year, int month, int day, int hours, int minute, int seconds, string fractional seconds)
literals.format.timestamp=TIMESTAMP '%1$04d-%2$02d-%3$02d %4$02d:%5$02d:%6$02d%7$.10s'
# 10 parameters (int year, int month, int day, int hours, int minute, int seconds, string fractional seconds, int tz hour, int tz minute, char tz sign)
literals.format.timestamp_with_time_zone=TIMESTAMP '%1$04d-%2$02d-%3$02d %4$02d:%5$02d:%6$02d%7$.10s%10$c%8$02d:%9$02d'
# 3 parameters (int day, int leading precision, char sign)
literals.format.interval_day=INTERVAL '%3$s%1$d' DAY@2[(%2$s)]
# 4 parameters (int day, int hour, int leading precision, char sign)
literals.format.interval_day_to_hour=INTERVAL '%4$s%1$d %2$02d' DAY@3[(%3$s)] TO HOUR
# 5 parameters (int day, int hour, int minute, int leading precision, char sign)
literals.format.interval_day_to_minute=INTERVAL '%5$s%1$d %2$02d:%3$02d' DAY@4[(%4$s)] TO MINUTE
# 8 parameters (int day, int hour, int minute, int seconds, string fractional seconds, int leading precision, int fractional precision, char sign)
literals.format.interval_day_to_second=INTERVAL '%8$s%1$d %2$02d:%3$02d:%4$02d%5$.10s' DAY@6[(%6$s)] TO SECOND@7[(%7$s)]
# 3 parameters (int hour, int leading precision, char sign)
literals.format.interval_hour=INTERVAL '%3$s%1$d' HOUR@2[(%2$s)]
# 4 parameters (int hour, int minute, int leading precision, char sign)
literals.format.interval_hour_to_minute=INTERVAL '%4$s%1$d:%2$02d' HOUR@3[(%3$s)] TO MINUTE
# 7 parameters (int hour, int minute, int seconds, string fractional seconds, int leading precision, int fractional precision, char sign)
literals.format.interval_hour_to_second=INTERVAL '%7$s%1$d:%2$02d:%3$02d%4$.10s' HOUR@5[(%5$s)] TO SECOND@6[(%6$s)]
# 3 parameters (int minute, int leading precision, char sign)
literals.format.interval_minute=INTERVAL '%3$s%1$d' MINUTE@2[(%2$s)]
# 6 parameters (int minute, int seconds, string fractional seconds, int leading precision, int fractional precision, char sign)
literals.format.interval_minute_to_second=INTERVAL '%6$s%1$d:%2$02d%3$.10s' MINUTE@4[(%4$s)] TO SECOND@5[(%5$s)]
# 5 parameters (int seconds, string fractional seconds, int leading precision, int fractional precision, char sign)
literals.format.interval_second=INTERVAL '%5$s%1$02d%2$.10s' SECOND@3[(%3$s]@4[, %4$s)]
# 3 parameters (int year, int leading precision, char sign)
literals.format.interval_year=INTERVAL '%3$s%1$d' YEAR@2[(%2$s)]
# 4 parameters (int year, int month, int leading precision, char sign)
literals.format.interval_year_to_month=INTERVAL '%4$s%1$d-%2$02d' YEAR@3[(%3$s)] TO MONTH
# 3 parameters (int month, int leading precision, char sign)
literals.format.interval_month=INTERVAL '%3$s%1$d' MONTH@2[(%2$s)]
# 1 parameter (string)
literals.format.decimal=%s
# 1 parameter (string)
literals.format.char='%s'
# 1 parameter (string)
literals.format.nchar=N'%s'
# 1 parameter (string)
literals.format.varchar='%s'
# 1 parameter (string)
literals.format.nvarchar=N'%s'

#
# Data types.
#
dataType.binary=true
dataType.blob=true
dataType.clob=true
dataType.boolean=true
dataType.date=true
dataType.time=true
dataType.time_with_time_zone=true
dataType.timestamp=true
dataType.timestamp_with_time_zone=true
dataType.interval_day=true
dataType.interval_day_to_hour=true
dataType.interval_day_to_minute=true
dataType.interval_day_to_second=true
dataType.interval_hour=true
dataType.interval_hour_to_minute=true
dataType.interval_hour_to_second=true
dataType.interval_minute=true
dataType.interval_minute_to_second=true
dataType.interval_second=true
dataType.interval_year=true
dataType.interval_year_to_month=true
dataType.interval_month=true
dataType.smallint=true
dataType.integer=true
dataType.long=true
dataType.float=true
dataType.double=true
dataType.decimal=true
dataType.char=true
dataType.nchar=true
dataType.varchar=true
dataType.nvarchar=true
dataType.xml=true
dataType.period=true
dataType.array=true
dataType.struct=true
dataType.map=true
dataType.json=true
dataType.datalink=true

#
# dataType.comparable
# Used to indicate that some data types that are comparable locally may not
# be supported by the DB.
#
#e.g. dataType.comparable[varchar,nvarchar]=false

#
# dataType.promotion
# Used to indicate what direction the promotion needs to occur
# <lhs> -> <rhs> these properties are not symetrical
#
#e.g. dataType.promotion[char,nvarchar]=true

#
# Collation Sequence SQL (SQL statement for retrieving the collation sequence)
# This statement returns a single row and single column containing the collation sequence
#
collation.sequence.sql=
#
# Datbase Encoding SQL. This statement retrieves the charset name for the non-unicode character data.
# This statement returns a single row and single column with the charset name for use in a java.nio.CharsetEncoder.
#
database.charset.sql=

#
# SQL DateTime Formatter validators.
# To be used in order to verify the formatting patterns that users can give when they use
# the following cognos-sql syntax: CAST('12- 8-13/1:52 PM' as TIMESTAMP FORMAT 'YYYY- MM-DD/HH12:MI AM')
#
formatters.delimiter=[-./,';: TZ]
formatters.year=Y{1,4}|R{2}|R{4}
formatters.month_number_of_year=M{1,2}
formatters.day_number_of_month=D{1,2}
formatters.day_number_of_year=D{3}
formatters.twelve_hour_clock=H{2}(12)?
formatters.twenty_four_hour_clock=H{2}24
formatters.am_pm=[AaPp][Mm]|[AaPp]\\.[Mm]\\.
#formatters.am_pm=[AP][M]
formatters.minutes_of_hour=MI
formatters.seconds_of_minute=S{2}
formatters.seconds_past_midnight=S{5}
formatters.fractional_seconds=F{2}[1-9]
formatters.time_zone_hour=TZH
formatters.time_zone_minute=TZM
#formatters.time_zone_second=TZS

formatters.string_to_date=(                                                         \
    (year)(delimiter)+(month_number_of_year)(delimiter)+(day_number_of_month)       \
    | (month_number_of_year)(delimiter)+(year)(delimiter)+(day_number_of_month)     \
    | (month_number_of_year)(delimiter)+(day_number_of_month)(delimiter)+(year)     \
    | (day_number_of_month)(delimiter)+(month_number_of_year)(delimiter)+(year)     \
    | (day_number_of_month)(delimiter)+(year)(delimiter)+(month_number_of_year)     \
    | (year)(delimiter)+(day_number_of_month)(delimiter)+(month_number_of_year)     \
    | (year)(delimiter)+(day_number_of_month)                                       \
    | (day_number_of_month)(delimiter)+(year)                                       \
    | (month_number_of_year)(delimiter)+(year)                                      \
    | (year)(delimiter)+(month_number_of_year)                                      \
    | (day_number_of_month)(delimiter)+(month_number_of_year)                       \
    | (month_number_of_year)(delimiter)+(day_number_of_month)                       \
    | (year)                                                                        \
    | (month_number_of_year)                                                        \
    | (day_number_of_year)                                                          \
)

formatters.string_to_time=(                                                                                                                                                     \
    (twelve_hour_clock)(delimiter)+(minutes_of_hour)(delimiter)+(seconds_of_minute)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))?        \
    | (minutes_of_hour)(delimiter)+(twelve_hour_clock)(delimiter)+(seconds_of_minute)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))?      \
    | (minutes_of_hour)(delimiter)+(seconds_of_minute)(delimiter)+(twelve_hour_clock)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))?      \
    | (seconds_of_minute)(delimiter)+(minutes_of_hour)(delimiter)+(twelve_hour_clock)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))?      \
    | (seconds_of_minute)(delimiter)+(twelve_hour_clock)(delimiter)+(minutes_of_hour)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))?      \
    | (twelve_hour_clock)(delimiter)+(seconds_of_minute)(delimiter)+(minutes_of_hour)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))?      \
    | (twenty_four_hour_clock)(delimiter)+(minutes_of_hour)(delimiter)+(seconds_of_minute)((delimiter)+(fractional_seconds))?                                                   \
    | (minutes_of_hour)(delimiter)+(twenty_four_hour_clock)(delimiter)+(seconds_of_minute)((delimiter)+(fractional_seconds))?                                                   \
    | (minutes_of_hour)(delimiter)+(seconds_of_minute)(delimiter)+(twenty_four_hour_clock)((delimiter)+(fractional_seconds))?                                                   \
    | (seconds_of_minute)(delimiter)+(minutes_of_hour)(delimiter)+(twenty_four_hour_clock)((delimiter)+(fractional_seconds))?                                                   \
    | (seconds_of_minute)(delimiter)+(twenty_four_hour_clock)(delimiter)+(minutes_of_hour)((delimiter)+(fractional_seconds))?                                                   \
    | (twenty_four_hour_clock)(delimiter)+(seconds_of_minute)(delimiter)+(minutes_of_hour)((delimiter)+(fractional_seconds))?                                                   \
    | (seconds_past_midnight)                           \
    | (twenty_four_hour_clock)                          \
    | (twelve_hour_clock)((delimiter)+(am_pm))?         \
    | (minutes_of_hour)                                 \
    | (seconds_of_minute)                               \
    | (twelve_hour_clock)(delimiter)+(minutes_of_hour)((delimiter)+(am_pm))?    \
    | (minutes_of_hour)(delimiter)+(twelve_hour_clock)((delimiter)+(am_pm))?    \
    | (twelve_hour_clock)(delimiter)+(seconds_of_minute)((delimiter)+(am_pm))?  \
    | (seconds_of_minute)(delimiter)+(twelve_hour_clock)((delimiter)+(am_pm))?  \
    | (twenty_four_hour_clock)(delimiter)+(minutes_of_hour)                     \
    | (minutes_of_hour)(delimiter)+(twenty_four_hour_clock)                     \
    | (twenty_four_hour_clock)(delimiter)+(seconds_of_minute)                   \
    | (seconds_of_minute)(delimiter)+(twenty_four_hour_clock)                   \
    | (minutes_of_hour)(delimiter)+(seconds_of_minute)                          \
    | (seconds_of_minute)(delimiter)+(minutes_of_hour)                          \
)

formatters.string_to_time_with_time_zone=(                                                \
    (string_to_time)(delimiter)*(time_zone_hour)(([:])(time_zone_minute))?(delimiter)*    \
)

formatters.string_to_timestamp=(                        \
    (string_to_date)(delimiter)+(string_to_time)        \
)

formatters.string_to_timestamp_with_time_zone=(                     \
    (string_to_date)(delimiter)+(string_to_time_with_time_zone)     \
)