Though it is possible to have the Integration service perform all Actuate SQL operations, it is not recommended. Whenever possible, you should execute functions and other operations on your database to optimize performance. Since Actuate SQL supports many common functions, for each Actuate SQL function there is usually an expression on the database that performs the same operation.
The goal of mapping a function is to specify a database function that provides the correct result for an Actuate SQL function. Sometimes it is possible to find a function on a database that behaves exactly like an Actuate SQL function. For example, Actuate SQL has a SUBSTRING function that takes three operands: $P0 for the string to be evaluated, $P1 for the start position, and $P2 for the number of characters to retrieve. Your database may have a function called SUBSTR that has the same syntax and performs the same operation. Therefore, Actuate SQL’s SUBSTRING ($P0, $P1, $P2) maps to SUBSTR ($P0, $P1, $P2) on the database.
In most cases, however, the mapping is not so straightforward. The syntax of the database function is usually different from the Actuate SQL syntax. Perhaps the SUBSTR function on the database does not specify the length of the substring as the third operand. Instead the SUBSTR function specifies the end position of the substring. For example, if the substring begins at position 3 and has 10 characters, the value of the third operand is 12. To map the SUBSTRING function, you must create an expression using the SUBSTR function that produces the same result, for example SUBSTR ($P0, $P1, $P1 + $P2 - 1).
The mappings.xml files for preconfigured database types contain many examples of function mapping. Do not modify the mappings.xml file for a preconfigured database type.
