Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Informatica Data Quality 10.5
  • All Products

Table of Contents

Search

  1. Preface
  2. Introduction to Transformations
  3. Transformation Ports
  4. Transformation Caches
  5. Address Validator Transformation
  6. Aggregator Transformation
  7. Association Transformation
  8. Bad Record Exception Transformation
  9. Case Converter Transformation
  10. Classifier Transformation
  11. Comparison Transformation
  12. Consolidation Transformation
  13. Data Masking Transformation
  14. Data Processor Transformation
  15. Decision Transformation
  16. Duplicate Record Exception Transformation
  17. Expression Transformation
  18. Filter Transformation
  19. Hierarchical to Relational Transformation
  20. Java Transformation
  21. Java Transformation API Reference
  22. Java Expressions
  23. Joiner Transformation
  24. Key Generator Transformation
  25. Labeler Transformation
  26. Lookup Transformation
  27. Lookup Caches
  28. Dynamic Lookup Cache
  29. Macro Transformation
  30. Match Transformation
  31. Match Transformations in Field Analysis
  32. Match Transformations in Identity Analysis
  33. Normalizer Transformation
  34. Merge Transformation
  35. Parser Transformation
  36. Python Transformation
  37. Rank Transformation
  38. Read Transformation
  39. Relational to Hierarchical Transformation
  40. REST Web Service Consumer Transformation
  41. Router Transformation
  42. Sequence Generator Transformation
  43. Sorter Transformation
  44. SQL Transformation
  45. Standardizer Transformation
  46. Union Transformation
  47. Update Strategy Transformation
  48. Web Service Consumer Transformation
  49. Parsing Web Service SOAP Messages
  50. Generating Web Service SOAP Messages
  51. Weighted Average Transformation
  52. Window Transformation
  53. Write Transformation
  54. Appendix A: Transformation Delimiters

Developer Transformation Guide

Developer Transformation Guide

Back Next

SQL Transformation Advanced Properties

SQL Transformation Advanced Properties

You can change the SQL transformation properties at any time. The default database type is Oracle. If the database you need to connect to is another database type, change the database type before you add ports to the transformation.
Configure the following properties on the
Advanced
 tab:
Tracing Level
Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal. When you configure the SQL transformation tracing level to Verbose Data, the Data Integration Service writes each SQL query it prepares to the mapping log.
Connection type
Describes how the Data Integration Service connects to the database. The connection type is static. The Data Integration Service connects one time to the database. Select a database connection object in the SQL transformation. Read only.
DB Type
Type of database that the SQL transformation connects to. Choose a database type from the list. You can choose Oracle, Microsoft SQL Server, or IBM DB2. Ignore the ODBC database type for the SQL transformation. The database type affects the datatypes that you can assign on the
Ports
 tab. When you change the database type, the Developer tool changes the port datatypes for input, output, and pass-through ports.
Continue on Error Within Row
Continues processing the remaining SQL statements in a query after an SQL error occurs.
Include Statistics as Output
Adds a NumRowsAffected output port. The port returns the total number of database rows that INSERT, DELETE, and UPDATE query statements update for an input row.
Max Output Row Count
Defines the maximum number of rows the SQL transformation can output from a SELECT query. To configure unlimited rows, set Max Output Row Count to zero.
Query Description
Description of the SQL query that you define in the transformation.
SQL Mode
Determines whether the SQL query is an external script or whether the query is defined in the transformation. The SQL Mode is Query. The SQL transformation runs a query that you define in the SQL Editor. Read only.
SQL Query
Displays the SQL query that you configure in the SQL Editor.
Has Side Effects
Indicates that the SQL transformation performs a function besides returning rows. The SQL transformation has a side effect when the SQL query updates a database. Enable
Has Side Effects
when the SQL query contains a statement such as as CREATE, DROP, INSERT, UPDATE, GRANT, or REVOKE.
The SQL transformation also has a side effect if the transformation returns NULL rows for SELECT statements that return no results. The rows might contain pass-through port values, SQL error information, or the NUMRowsAffected field.
Disable the
Has Side Effects
 property in order to allow push-into optimization or early selection optimization. Default is enabled.
Return Database Output Only
The SQL transformation does not generate rows for SELECT statements that return 0 results, rows for other statements such as INSERT,UPDATE, DELETE, or COMMIT, or null rows.
Enable Push-Into Optimization
Enables the Data Integration Service to push the logic from a Filter transformation in the mapping to the SQL in the SQL transformation.
Maintain Row Order
Maintain the row order of the input data to the transformation. Select this option if the Data Integration Service should not perform any optimization that can change the row order.
When the Data Integration Service performs optimizations, it might lose an order established earlier in the mapping. You can establish order in a mapping with a sorted flat file source, a sorted relational source, or a Sorter transformation. When you configure a transformation to maintain row order, the Data Integration Service considers this configuration when it performs optimizations for the mapping. The Data Integration Service performs optimizations for the transformation if it can maintain the order. The Data Integration Service does not perform optimizations for the transformation if the optimization would change the row order.
Partitionable
The transformation can be processed with multiple threads. Clear this option if you want the Data Integration Service to use one thread to process the transformation. The Data Integration Service can use multiple threads to process the remaining mapping pipeline stages.
Disable partitioning for an SQL transformation when the SQL queries require that the transformation be processed with one thread. Or, you might want to disable partitioning for an SQL transformation so that only one connection is made to the database.
0 COMMENTS
We’d like to hear from you!