Rules and guidelines for SQL transformations

General guidelines

• - You can't use the saved query type in an SQL transformation.
• - To enable the autocommit behavior in an SQL query and stored procedure, set the EnableSqlTxAutoCommitSP custom property value to true for the Secure Agent.

SQL query

• - You can't validate an entered query that you define in the SQL editor.
• - You can't use the following properties when you process an entered query:
• ▪ Continue on SQL Error within row
• ▪ In-out and input parameters
• ▪ Stop on error

Stored procedure or stored function

• - To process a stored function in PostgreSQL, complete the following prerequisite steps:
• ▪ Configure the JDBC driver from the latest folder for PostgreSQL Connector. For more information about configuring the JDBC driver, see Configure the JDBC driver for PostgreSQL Connector Knowledge article.
• ▪ Set the POSTGRESQL_ENABLESTOREDFUNCTION custom property in the Secure Agent properties.
To configure the custom property in the Secure Agent, perform the following steps:
1. 1Log in to Informatica® Intelligent Data Management Cloud™ (IDMC).
2. 2Select Administrator > Runtime Environments.
3. 3On the Runtime Environments page, select the Secure Agent.
4. 4Click Edit.
5. 5In the Custom Configuration Details section, select Data Integration Server as the service and Tomcat JRE as the type.
6. 6Enter POSTGRESQL_ENABLESTOREDFUNCTION in the Name field and set the value to true.
7. 7Click Save.
• - A stored function requires at least one input parameter and returns a scalar value.
• - You can't use the same name for multiple stored procedures or functions.
• - Mappings fail when the stored procedure or function contains special characters or Unicode characters.
• - You can't configure the On Stored Procedure Error or On Stored Function Error advanced property in a mapping task.
• - When you import data from PostgreSQL that contains the numeric data type, the Secure Agent imports the numeric data type with a default precision of 28 and scale of 26 irrespective of the precision you define for the numeric data type.
• - When you import data from PostgreSQL that contains the char, varchar, text, and bytea data types, the Secure Agent imports the data types with a precision of 32000 irrespective of the precision you define for the data types.
• - If you add a new stored procedure or function to the database while you have the mapping open, the new stored procedure or function doesn’t appear in the list of available stored procedures or functions. To refresh the list, close and reopen the mapping.
• - You can't use the bytea, time, boolean, citext, and timestamp with timezone data types in a stored procedure or function.
• - When you create a stored procedure or function, specify the JSONB data type as a VARCHAR data type.