Preface
Working with Transformations
- Transformations Overview
- Creating a Transformation
- Configuring Transformations
  - Renaming Transformations
- Transformation Ports
- Multi-Group Transformations
- Working with Expressions
- Local Variables
- Default Values for Ports
- Configuring Tracing Level in Transformations
- Reusable Transformations
Address Validator Transformation
- Address Validator Transformation Overview
- Address Reference Data
  - Types of Address Reference Data
- Modes and Templates
- Port Groups and Port Selection
- Address Validator Transformation Input Port Groups
- Address Validator Transformation Output Port Groups
- Multiple-Instance Ports
- Address Validation Projects
- Formatted Addresses and Mail Carrier Standards
- Partial Address Completion
- Address Validator Status Ports
  - Element Status Code Definitions
  - Address Resolution Code Output Port Values
  - Element Input Status Output Port Values
  - Element Relevance Output Port Values
  - Element Result Status Output Port Values
  - Extended Element Result Status Output Port Values
  - Mailability Score Output Port Values
  - Match Code Output Port Values
  - Geocoding Status Output Port Values
- Address Validator Transformation General Settings
- Address Validation Properties in the Preferences Window
  - Address Validation Data Properties
  - Address Validation License Properties
  - Address Validation Engine Properties
- Address Validation Advanced Properties
  - Alias Locality
  - Alias Street
  - Casing Style
  - Country of Origin
  - Country Type
  - Default Country
  - Dual Address Priority
  - Element Abbreviation
  - Execution Instances
  - Flexible Range Expansion
  - Geocode Data Type
  - Global Max Field Length
  - Global Preferred Descriptor
  - Input Format Type
  - Input Format With Country
  - Line Separator
  - Matching Alternatives
  - Matching Extended Archive
  - Matching Scope
  - Max Result Count
  - Mode
  - Optimization Level
  - Output Format Type
  - Output Format With Country
  - Preferred Language
  - Preferred Script
  - Ranges To Expand
  - Standardize Invalid Addresses
  - Tracing Level
- Certification Reports
  - AMAS Report Fields
  - CASS Report Fields
  - SendRight Report
  - SERP Report Fields
- Configuring an Address Validator Transformation
- Adding Ports to the Address Validator Transformation
- Creating User-Defined Templates
- Defining Address Validator Models
- Defining a Certification Report
- Address Validator Transformation in a Non-native Environment
  - Address Validator Transformation on the Blaze Engine
  - Address Validator Transformation on the Spark Engine
    - Address Validator Transformation in a Streaming Mapping
  - Address Validator Transformation on the Databricks Spark Engine
Aggregator Transformation
- Aggregator Transformation Overview
- Components of the Aggregator Transformation
  - Configuring Aggregator Transformation Properties
  - Configuring Aggregator Transformation Ports
- Configuring Aggregate Caches
- Aggregate Expressions
- Group By Ports
  - Non-Aggregate Expressions
  - Default Values
- Using Sorted Input
  - Sorted Input Conditions
  - Sorting Data
- Creating an Aggregator Transformation
- Tips for Aggregator Transformations
- Troubleshooting Aggregator Transformations
Association Transformation
- Association Transformation Overview
- Memory Allocation
- Association Transformation Advanced Properties
Bad Record Exception Transformation
- Bad Record Exception Transformation Overview
- Bad Record Exception Output Record Types
- Bad Record Exception Management Process Flow
- Bad Record Exception Mappings
  - Bad Record Exception Quality Issues
  - Human Tasks
- Bad Record Exception Ports
  - Bad Record Exception Transformation Input Ports
  - Bad Record Exception Transformation Output
- Bad Record Exception Configuration View
  - Generating the Bad Records Table and the Issues Table
- Bad Record Exception Issue Assignment
  - Assigning Ports to Quality Issues
- Exception Transformation Advanced Properties
- Configuring a Bad Record Exception Transformation
- Bad Record Exception Mapping Example
Case Converter Transformation
- Case Converter Transformation Overview
- Case Strategy Properties
- Configuring a Case Converter Strategy
- Case Converter Transformation Advanced Properties
- Case Converter Transformation in a Non-native Environment
Classifier Transformation
- Classifier Transformation Overview
- Classifier Models
- Classifier Algorithms
- Classifier Transformation Options
- Classifier Strategies
- Classifier Transformation Advanced Properties
- Configuring a Classifier Strategy
- Classifier Analysis Example
- Classifier Transformation in a Non-native Environment
Cleanse transformation
- Cleanse transformation configuration
  - Cleanse asset considerations
  - Synchronizing data quality assets
- Cleanse transformation field mappings
- Cleanse transformation output fields
- Advanced properties
Comparison Transformation
- Comparison Transformation Overview
- Field Matching Strategies
- Identity Matching Strategies
- Configuring a Comparison Strategy
- Comparison Transformation Advanced Properties
- Comparison Transformation in a Non-native Environment
Custom Transformation
- Custom Transformation Overview
- Creating Custom Transformations
  - Rules and Guidelines for Custom Transformations
  - Custom Transformation Components
- Working with Groups and Ports
- Working with Port Attributes
  - Editing Port Attribute Values
- Custom Transformation Properties
  - Setting the Update Strategy
  - Working with Thread-Specific Procedure Code
- Working with Transaction Control
- Blocking Input Data
- Working with Procedure Properties
- Creating Custom Transformation Procedures
Custom Transformation Functions
- Custom Transformation Functions Overview
  - Working with Handles
- Function Reference
- Working with Rows
  - Rules and Guidelines for Row-Based and Array-Based Data Access Mode
- Generated Functions
- API Functions
- Array-Based API Functions
Consolidation Transformation
- Consolidation Transformation Overview
- Consolidation Mappings
- Consolidation Transformation Ports
- Consolidation Transformation Views
- Simple Strategies
- Row-Based Strategies
- Advanced Strategies
- Simple Consolidation Functions
- Row-Based Consolidation Functions
- Consolidation Mapping Example
- Configuring a Consolidation Transformation
- Consolidation Transformation in a Non-native Environment
Data Masking Transformation
- Data Masking transformation
- Masking Properties
  - Locale
  - Masking Types
  - Repeatable Output
  - Seed
  - Mapping Parameters
  - Associated O/P
- Key Masking
  - Masking String Values
  - Masking Numeric Values
  - Masking Datetime Values
- Substitution Masking
  - Dictionaries
  - Storage Tables
    - Encrypting Storage Tables for Substitution Masking
  - Substitution Masking Properties
  - Relational Dictionary
  - Connection Requirements
  - Rules and Guidelines for Substitution Masking
- Dependent Masking
  - Dependent Masking Example
  - Repeatable Dependent Masking
- Random Masking
  - Masking Numeric Values
  - Masking String Values
  - Masking Date Values
- Applying Masking Rules
  - Mask Format
  - Source String Characters
    - Source String Example
  - Result String Replacement Characters
    - Result String Replacement Characters Example
  - Range
  - Blurring
    - Blurring Numeric Values
    - Blurring Date Values
- Expression Masking
  - Repeatable Expression Masking
  - Rules and Guidelines for Expression Masking
- Format Preserving Encryption
- Special Mask Formats
- Social Security Number Masking
  - Social Security Number Format
  - Area Code Requirement
  - Repeatable Social Security Number Masking
- Credit Card Number Masking
- Phone Number Masking
- Email Address Masking
  - Advanced Email Masking
    - Configuration Parameters for an Advanced Email Address Masking Type
    - Expressions for the Advanced Email Address Masking Type
- Social Insurance Number Masking
  - SIN Start Digit
  - Repeatable SIN Numbers
- IP Address Masking
- URL Address Masking
- Default Value File
- Data Masking Transformation Session Properties
- Rules and Guidelines for Data Masking Transformations
Data Masking Examples
- Name and Address Lookup Files
- Substituting Data with the Lookup Transformation
- Masking Data with an Expression Transformation
Decision Transformation
- Decision Transformation Overview
- Decision Transformation Functions
- Decision Transformation Conditional Statements
- Decision Transformation Operators
- Decision Transformation NULL Handling
- Configuring a Decision Strategy
- Decision Transformation Advanced Properties
- Decision Transformation in a Non-native Environment
  - Decision Transformation on the Spark Engine
  - Decision Transformation on the Databricks Spark Engine
Duplicate Record Exception Transformation
- Duplicate Record Exception Transformation Overview
- Duplicate Record Exception Process Flow
- Duplicate Record Exceptions
- Duplicate Record Exception Configuration View
  - Generating a Duplicate Records Table
- Ports
- Duplicate Record Exception Transformation Advanced Properties
- Duplicate Record Exception Mapping Example
- Creating a Duplicate Record Exception Transformation
Dynamic Lookup Cache
- Dynamic Lookup Cache Overview
- Dynamic Lookup Properties
- Lookup Transformation Values
- Dynamic Lookup Cache Updates
  - Insert Else Update
  - Update Else Insert
- Mappings with Dynamic Lookups
- Conditional Dynamic Cache Updates
  - Session Processing
  - Configuring a Conditional Dynamic Cache Lookup
- Dynamic Cache Update with Expression Results
- Synchronizing Cache with the Lookup Source
  - NewLookupRow
  - Configuring Dynamic Cache Synchronization
- Dynamic Lookup Cache Example
- Rules and Guidelines for Dynamic Lookup Caches
Expression Transformation
- Expression Transformation Overview
- Expression Transformation Components
- Configuring Ports
  - Calculating Values
- Creating an Expression Transformation
External Procedure Transformation
- External Procedure Transformation Overview
- Configuring External Procedure Transformation Properties
- Developing COM Procedures
- Developing Informatica External Procedures
- Distributing External Procedures
  - Distributing COM Procedures
    - Distributing a COM Visual Basic Procedure
    - Distributing a COM Visual Basic Procedure Manually
  - Distributing Informatica Modules
- Development Notes
- Service Process Variables in Initialization Properties
- External Procedure Interfaces
Filter Transformation
- Filter Transformation Overview
- Filter Transformation Components
  - Configuring Filter Transformation Ports
- Filter Condition
  - Filtering Rows with Null Values
- Steps to Create a Filter Transformation
- Tips for Filter Transformations
HTTP Transformation
- HTTP Transformation Overview
  - Authentication
  - Connecting to the HTTP Server
- Creating an HTTP Transformation
- Configuring the Properties Tab
- Configuring the HTTP Tab
- Examples
Identity Resolution Transformation
- Identity Resolution Transformation Overview
- Create and Configure the Transformation
- Identity Resolution Transformation Tabs
- Groups and Ports
  - Input Groups and Ports
  - Output Groups and Ports
Java Transformation
- Java Transformation Overview
- Using the Java Code Tab
- Configuring Ports
  - Creating Groups and Ports
  - Setting Default Port Values
    - Input and Output Ports
    - Input/Output Ports
- Configuring Java Transformation Properties
  - Working with Transaction Control
    - Transformation Scope
    - Generate Transaction
  - Setting the Update Strategy
- Developing Java Code
- Configuring Java Transformation Settings
- Compiling a Java Transformation
- Fixing Compilation Errors
  - Locating the Source of Compilation Errors
    - Locating Errors in the Code Entry Tabs
    - Locating Errors in the Full Code Window
  - Identifying the Source of Compilation Errors
    - User Code Errors
    - Non-User Code Errors
Java Transformation API Reference
- Java Transformation API Methods Overview
- commit
- defineJExpression
- failSession
- generateRow
- getInRowType
- getMetadata
- incrementErrorCount
- invokeJExpression
- isNull
- logError
- logInfo
- resetNotification
- rollBack
- setNull
- setOutRowType
- storeMetadata
Java Expressions
- Java Expressions Overview
  - Expression Function Types
- Using the Define ExpressionDefine Function Dialog Box to Define an Expression
- Working with the Simple Interface
  - invokeJExpression
  - Simple Interface Example
- Working with the Advanced Interface
- JExpression Class API Reference
Java Transformation Example
- Java Transformation Example Overview
- Step 1. Import the Mapping
- Step 2. Create Transformation and Configure Ports
- Step 3. Enter Java Code
- Import Packages Tab
- Helper Code Tab
- On Input Row Tab
- Step 4. Compile the Java Code
- Step 5. Create a Session and Workflow
  - Sample Data
Joiner Transformation
- Joiner Transformation Overview
  - Working with the Joiner Transformation
- Joiner Transformation Properties
- Defining a Join Condition
- Defining the Join Type
- Using Sorted Input
- Joining Data from a Single Source
- Blocking the Source Pipelines
  - Unsorted Joiner Transformation
  - Sorted Joiner Transformation
    - Caching Master Rows
- Working with Transactions
- Creating a Joiner Transformation
- Tips for Joiner Transformations
Key Generator Transformation
- Key Generator Transformation Overview
- Soundex Strategy
  - Soundex Strategy Properties
- String Strategy
  - String Strategy Properties
- NYSIIS Strategy
- Key Generator Output Ports
- Configuring a Grouping Strategy
- Key Creation Properties
- Key Generator Transformation Advanced Properties
- Key Generator Transformation in a Non-native Environment
Labeler Transformation
- Labeler Transformation Overview
- When to Use a Labeler Transformation
- Reference Data Use in the Labeler Transformation
- Labeler Transformation Strategies
  - Character Labeling Operations
  - Token Labeling Operations
- Labeler Transformation Ports
- Character Labeling Properties
- Token Labeling Properties
- Configuring a Character Labeling Strategy
- Configuring a Token Labeling Strategy
- Labeler Transformation Advanced Properties
- Labeler Transformation in a Non-native Environment
Lookup Transformation
- Lookup Transformation Overview
- Lookup Source Types
- Connected and Unconnected Lookups
  - Connected Lookups
  - Unconnected Lookups
- Lookup Components
- Lookup Properties
  - Configuring Lookup Properties in a Session
- Lookup Query
- Lookup Condition
- Lookup Caches
- Return Multiple Rows
  - Rules and Guidelines for Returning Multiple Rows
- Configuring Unconnected Lookup Transformations
- Database Deadlock Resilience
- Creating a Lookup Transformation
  - Creating a Reusable Pipeline Lookup Transformation
  - Creating a Non-Reusable Pipeline Lookup Transformation
- Tips for Lookup Transformations
Lookup Caches
- Lookup Caches Overview
  - Cache Comparison
- Building Connected Lookup Caches
  - Sequential Caches
  - Concurrent Caches
- Using a Persistent Lookup Cache
- Working with an Uncached Lookup or Static Cache
- Sharing the Lookup Cache
  - Sharing an Unnamed Lookup Cache
    - Guidelines for Sharing an Unnamed Lookup Cache
  - Sharing a Named Lookup Cache
    - Guidelines for Sharing a Named Lookup Cache
- Tips for Lookup Caches
Match Transformation
- Match Transformation Overview
- Match Analysis
- Match Score Calculations
- Master Data Analysis
  - Mapping Reuse
- Identity Match Analysis and Persistent Index Data
  - Rules and Guidelines for Persistent Index Data
- Match Mapping Performance
  - Viewing Match Cluster Analysis Data
  - Viewing Match Performance Analysis Data
- Match Performance in Identity Analysis
  - Creating a Data Store for Identity Index Data
  - Using the Index Data Store in Single-Source Analysis
- Match Transformation Views
- Match Transformation Ports
- Match Mapplets
  - Creating a Match Mapplet
  - Using a Match Mapplet
- Configuring a Match Analysis Operation
- Match Transformation in a Non-native Environment
Match Transformations in Field Analysis
- Field Match Analysis
- Process Flow for Field Match Analysis
- Field Match Type Options
- Field Match Strategies
  - Field Match Algorithms
  - Field Match Strategy Properties
- Field Match Output Options
  - Match Output Types
  - Match Output Properties
- Field Match Advanced Properties
- Field Match Analysis Example
Match Transformations in Identity Analysis
- Identity Match Analysis
- Process Flow for Identity Match Analysis
- Identity Match Type Properties
  - Index Directory and Cache Directory Properties
  - Persistence Method Parameters
- Identity Match Strategies
  - Identity Match Algorithms
  - Identity Match Strategy Properties
- Identity Match Output Options
  - Match Output Types
  - Match Output Properties
    - Match Property Configuration
    - Output Property Configuration
- Identity Match Advanced Properties
- Persistent Index Case Study
- Identity Match Analysis Example
Merge Transformation
- Merge Transformation Overview
- Configuring a Merge Strategy
- Merge Transformation Advanced Properties
- Merge Transformation in a Non-native Environment
Normalizer Transformation
- Normalizer Transformation Overview
- Normalizer Transformation Components
- Normalizer Transformation Generated Keys
  - Storing Generated Key Values
  - Changing the Generated Key Values
- VSAM Normalizer Transformation
- Pipeline Normalizer Transformation
- Using a Normalizer Transformation in a Mapping
  - Generating Key Values
- Troubleshooting Normalizer Transformations
Parser Transformation
- Parser Transformation Overview
- Parser Transformation Modes
- When to Use a Parser Transformation
- Reference Data Use in the Parser Transformation
- Token Parsing Operations
- Token Parsing Ports
- Token Parsing Properties
- Pattern-Based Parsing Mode
  - Pattern-Based Parsing Ports
- Configuring a Token Parsing Strategy
- Configuring a Pattern Parsing Strategy
- Parser Transformation Advanced Properties
- Parser Transformation in a Non-native Environment
Rank Transformation
- Rank Transformation Overview
- Ports in a Rank Transformation
  - Rank Index
- Defining Groups
- Creating a Rank Transformation
Router Transformation
- Router Transformation Overview
- Working with Groups
- Working with Ports
- Connecting Router Transformations in a Mapping
- Creating a Router Transformation
Sequence Generator Transformation
- Sequence Generator Transformation Overview
- Sequence Generator Ports
- Sequence Generator Transformation Properties
- Sequence Generator Advanced Properties
  - Reset
  - Maintain Row Order
- Sequence Data Object
  - Creating a Sequence Data Object
- Creating a Sequence Generator Transformation
- Creating a Sequence Generator Transformation
- Frequently Asked Questions
Sorter Transformation
- Sorter Transformation Overview
- Sorting Data
- Sorter Transformation Properties
- Creating a Sorter Transformation
Source Qualifier Transformation
- Source Qualifier Transformation Overview
- Source Qualifier Transformation Properties
- Default Query
  - Viewing the Default Query
  - Overriding the Default Query
- Joining Source Data
- Adding an SQL Query
- Entering a User-Defined Join
- Outer Join Support
- Entering a Source Filter
- Using Sorted Ports
- Select Distinct
  - Overriding Select Distinct in the Session
- Adding Pre- and Post-Session SQL Commands
- Creating a Source Qualifier Transformation
  - Creating a Source Qualifier Transformation Manually
  - Configuring Source Qualifier Transformation Options
- Troubleshooting Source Qualifier Transformations
SQL Transformation
- SQL Transformation Overview
- Script Mode
  - Example
  - Rules and Guidelines for Script Mode
- Query Mode
- Connecting to Databases
- Session Processing
- Input Row to Output Row Cardinality
- SQL Transformation Properties
- SQL Statements
- Creating an SQL Transformation
Using the SQL Transformation in a Mapping
- SQL Transformation Example Overview
- Dynamic Update Example
- Dynamic Connection Example
Stored Procedure Transformation
- Stored Procedure Transformation Overview
- Using a Stored Procedure in a Mapping
- Writing a Stored Procedure
  - Sample Stored Procedure
- Creating a Stored Procedure Transformation
- Configuring a Connected Transformation
- Configuring an Unconnected Transformation
  - Calling a Stored Procedure From an Expression
  - Calling a Pre- or Post-Session Stored Procedure
- Error Handling
- Stored Procedure Options
- Expression Rules
- Tips for Stored Procedure Transformations
- Troubleshooting Stored Procedure Transformations
Standardizer Transformation
- Standardizer Transformation Overview
- Standardization Strategies
- Standardization Properties
- Configuring a Standardization Strategy
- Standardizer Transformation Advanced Properties
- Standardizer Transformation in a Non-native Environment
Transaction Control Transformation
- Transaction Control Transformation Overview
- Transaction Control Transformation Properties
  - Properties Tab
  - Example
- Using Transaction Control Transformations in Mappings
  - Sample Transaction Control Mappings with Multiple Targets
- Mapping Guidelines and Validation
- Creating a Transaction Control Transformation
Union Transformation
- Union Transformation Overview
  - Rules and Guidelines for Union Transformations
  - Union Transformation Components
- Working with Groups and Ports
- Creating a Union Transformation
- Using a Union Transformation in a Mapping
Unstructured Data Transformation
- Unstructured Data Transformation Overview
- Configuring the Unstructured Data Option
  - Configuring the Data Transformation Repository Directory
- Data Transformation Service Types
- Unstructured Data Transformation Components
- Unstructured Data Transformation Ports
- Unstructured Data Transformation Service Names
- Relational Hierarchies
  - Exporting the Hierarchy Schema
- Mappings
- Creating an Unstructured Data Transformation
Update Strategy Transformation
- Update Strategy Transformation Overview
  - Setting the Update Strategy
- Flagging Rows Within a Mapping
- Setting the Update Strategy for a Session
  - Specifying an Operation for All Rows
  - Specifying Operations for Individual Target Tables
- Update Strategy Checklist
Weighted Average Transformation
- Weighted Average Transformation Overview
- Configuring a Weighted Average Transformation
- Weighted Match Scores Example
- Weighted Average Transformation Advanced Properties
- Weighted Average Transformation in a Non-native Environment
XML Transformations
- XML Source Qualifier Transformation
- XML Parser Transformation
- XML Generator Transformation

Transformation Guide

Back Next

Creating the Database Tables

For this example, you need to create three Oracle databases, a United States database, a United Kingdom database, and a Canadian database. Each database has a Cust table that contains the customer data for the region.

The following SQL statements create a Cust table on an Oracle database:

Create table Cust (CustomerId number,CustomerName varchar2(25),PhoneNumber varchar2(15),Email varchar2(25));

This example includes three databases to illustrate dynamic database connections. If the tables are in the same database, use a static database connection to improve performance.

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Creating the Database Tables

Creating the Database Tables