Sequence generator may be a passive and connected transformation, and it generates numeric sequence values like 1, 2, 3, and so on. It doesn’t affect the amount of input rows.

The Sequence Generator transformation is employed to make unique primary key values and replace missing primary keys.

For example, if we would like to assign sequence values to the source records, then we’d like to use a sequence generator.

The sequence generator transformation consists of two output ports. We cannot edit or delete these ports, such as:

CURRVAL
NEXTVAL

NEXTVAL

The NEXTVAL port is employed to get sequence numbers by connecting it to a change or target. The generated sequence numbers are supported the present Value and Increment By properties.

If the sequence generator isn’t configuring to Cycle, then the NEXTVAL port makes the sequence numbers up to the set End Value.

We can connect the NEXTVAL port to multiple transformations to get unique values for every row.

The sequence generator transformation creates a block of numbers at an equivalent time. If the block of numbers is employed, then it generates subsequent block of sequence numbers.

For example, we’d connect NEXTVAL to 2 target tables in mapping to make unique primary key values.

The integration service generates a block of numbers 1 to 10 for the primary target. When the primary block of numbers has been loaded, only then another block of numbers 11 to twenty are going to be generated for the second target.

CURRVAL

The CURRVAL port is NEXTVAL plus the Increment By value.

We only connect the CURRVAL port when the NEXTVAL port is already linked to a downstream transformation.

If we combine the CURRVAL port without connecting the NEXTVAL port, the mixing Service passes a continuing value for every row.

When we combine the CURRVAL port during a Sequence Generator Transformation, then the mixing Service processes one row in each block.

We can optimize performance by connecting only the NEXTVAL port during a Mapping.

Example: Suppose STUD are going to be a source table.

Create a target STUD_SEQ_GEN_EXAMPLE within the shared folder. Structure an equivalent as STUD. Add two more ports NEXT_VALUE and CURR_VALUE to the target table.

We can create a Sequence Generator transformation to use during a single mapping, or a reusable Sequence Generator transformation to use in multiple mappings.

A reusable Sequence Generator transformation maintains the integrity of the sequence in each mapping that uses an instance of the Sequence Generator transformation.

Properties of Sequence Generator Transformation

Below are the subsequent properties to configure a sequence data object and a replacement sequence:

Property	Description
Start Value	The start value of the generated sequence is that the Integration Service when using the Cycle option. If we select Cycle, the mixing Service cycles back to the present value when it reaches the top value. The default value is 0. Maximum value is 9,223,372,036,854,775,806.
End Value	The maximum value that the mixing Service generates. If the mixing Service reaches this value during the session, and therefore the sequence isn’t configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807.
Increment Value	Difference between two consecutive values from the NEXTVAL port. The default value is 1. And it must be a positive integer. Maximum value is 2,147,483,647.
Cycle	enabled, the mixing Service cycles through the sequence range and begin over with the beginning value. If disabled, the mixing Service stops the sequence at the configured end value. the mixing Service fails the session with overflow errors if it reaches the top value and still has rows to process.
Reset	If enabled, the mixing service resets the sequence data object to the beginning value when the mapping completely run. If disabled, the mixing Service increments the present value after the mapping run ends, and uses that value within the next mapping run. This property is disabled for reusable Sequence Generator transformations and for non-reusable Sequence Generator transformations that use a reusable sequence data object.
Tracing Level	Level of detail about the transformation that the Integration Service writes into the mapping log. We can choose terse, normal, verbose initialization or verbose data. Normal sets as a default level.
Maintain Row Order	Maintain Row Order Maintain the row order of the input file to the transformation. Select this feature if the mixing Service shouldn’t perform any optimization which will change the row order

Example

In the below example, we’ll generate sequence numbers and store within the target within the following steps, such as:

Step 1: Create a target table.

Step 2: Import that created table in Informatica because the target table.

Step 3: Create a replacement mapping and import STUD source and STUD_SEQUENCE target table.

Step 4: Create a replacement transformation within the mapping,

Select sequence transformation because the type.
Enter transformation name like seq_stud.
Click on the Create

Step 5: Sequence generator transformation are going to be created, then click on the Done button.

Step 6: Link the NEXTVAL column of sequence generator to the SNO column within the target table.

Step 7: Link the opposite columns from source qualifier transformation to the target table.

Step 8: Double click on the sequence generator to open the property window, and then

Select the properties tab.
Enter the properties with Start value =1 and leave the opposite properties as default.
Click on the OK

Now save the mapping and execute it after creating the session and workflow.

The SNO column within the target would contain the sequence numbers generated by the sequence generator transformation.

So, this brings us to the end of blog. This Tecklearn ‘Sequence Generator Transformation in Informatica’ blog helps you with commonly asked questions if you are looking out for a job in Informatica. If you wish to learn Informatica and build a career in Datawarehouse and ETL domain, then check out our interactive, Informatica Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/informatica-training-and-certification/

Informatica Training

About the Course

Tecklearn’s Informatica Training will help you master Data Integration concepts such as ETL and Data Mining using Informatica PowerCenter. It will also make you proficient in Advanced Transformations, Informatica Architecture, Data Migration, Performance Tuning, Installation & Configuration of Informatica PowerCenter. You will get trained in Workflow Informatica, data warehousing, Repository Management and other processes.

Why Should you take Informatica Training?

Informatica professionals earn up to $130,000 per year – Indeed.com
GE, eBay, PayPal, FedEx, EMC, Siemens, BNY Mellon & other top Fortune 500 companies use Informatica.
Key advantages of Informatica PowerCenter: Excellent GUI interfaces for Administration, ETL Design, Job Scheduling, Session monitoring, Debugging, etc.

What you will Learn in this Course?

Informatica PowerCenter 10 – An Overview

Informatica & Informatica Product Suite
Informatica PowerCenter as ETL Tool
Informatica PowerCenter Architecture
Component-based development techniques

Data Integration and Data Warehousing Fundamentals

Data Integration Concepts
Data Profile and Data Quality Management
ETL and ETL architecture
Brief on Data Warehousing

Informatica Installation and Configuration

Configuring the Informatica tool
How to install the Informatica operational administration activities and integration services

Informatica PowerCenter Transformations

Visualize PowerCenter Client Tools
Data Flow
Create and Execute Mapping
Transformations and their usage
Hands On

Informatica PowerCenter Tasks & Workflows

Informatica PowerCenter Workflow Manager
Reusability and Scheduling in Workflow Manager
Workflow Task and job handling
Flow within a Workflow
Components of Workflow Monitor

Advanced Transformations

Look Up Transformation
Active and Passive Transformation
Joiner Transformation
Types of Caches
Hands On

More Advanced Transformations – SQL (Pre-SQL and Post-SQL)

Load Types – Bulk, Normal
Reusable and Non-Reusable Sessions
Categories for Transformation
Various Types of Transformation – Filter, Expression, Update Strategy, Sorter, Router, XML, HTTP, Transaction Control

Various Types of Transformation – Rank, Union, Stored Procedure

Error Handling and Recovery in Informatica
High Availability and Failover in Informatica
Best Practices in Informatica
Debugger
Performance Tuning

Performance Tuning, Design Principles & Caches

Performance Tuning Methodology
Mapping design tips & tricks
Caching & Memory Optimization
Partition & Pushdown Optimization
Design Principles & Best Practices

Informatica PowerCenter Repository Management

Repository Manager tool (functionalities, create and delete, migrate components)
PowerCenter Repository Maintenance

Informatica Administration & Security

Features of PowerCenter 10
Overview of the PowerCenter Administration Console
Integration and repository service properties
Services in the Administration Console (services, handle locks)
Users and groups

Command Line Utilities

Infacmd, infasetup, pmcmd, pmrep
Automate tasks via command-line programs

More Advanced Transformations – XML

Java Transformation
HTTP Transformation

Got a question for us? Please mention it in the comments section and we will get back to you.

615