SQL Operations are defined by multiple SQL statements that are
pre-compiled and stored on the server for reuse, and can have parameters. SQL
Operations often contain multiple DML statements (SELECT, INSERT, UPDATE,
DELETE, etc.), and use control statements (IF, THEN, ELSE, CASE, FOR,
etc.).
Support for SQL Operations is limited to functions and stored
procedures with the following support and limitations:
o The general support for Stored Procedures is defined as follows:
- Stored procedures reading from and writing to tables (catalog objects): supported
- Stored procedures lineage support for IN, OUT, INOUT parameters: supported
- Stored procedures with cursors: supported
- Stored procedures with temporary/Local variables: supported
- Stored procedures using functions: limited support
- Stored procedures using public/private synonyms: supported
- Stored procedures using other stored procedures: limited support
- Stored procedures using temporary tables: supported
- Stored procedures with error management: not relevant for data flow linage
o Parsing of independent SQL
Stored Procedures and Functions is currently limited to the following database
technologies:
(although some statements specific to database proprietary SQL
syntax may not be supported)
- Microsoft SQL Server Database
- Oracle Database
- PostgreSQL Database
- Pivotal GreenPlum Database
- Teradata Database
o Most dynamic and chaining aspects of the operations are not supported such as:
- operations calling other operations
- operations that execute dynamic SQL (e.g. SQL text passed as parameter)
- external operations (e.g. includes)
o Some usage aspects of such operations by DI and BI tools might be limited:
- using SQL operations to read data is supported in most DI and BI tool import bridges
- using SQL operations to write data is supported only in DI tool import bridges such as Informatica PowerCenter, Microsoft SSIS and Talend