Sequence Generator Transformation
Sequence Generator Transformation in Infotmatica
Sequence generator transformation is a passive and connected transformation. The sequence generator transformation is used for
Creating Sequence Generator Transformation:
Follow the below steps to create a sequence generator transformation:
Configuring Sequence Generator Transformation:
Configure the following properties of sequence generator transformation:
Sequence Generator Transformation Ports:
The sequence generator transformation contains only two output ports. They are CURRVAL and NEXTVAL output ports.
NEXTVAL Port:
You can connect the NEXTVAL port to multiple transformations to generate the unique values for each row in the transformation. The NEXTVAL port generates the sequence numbers base on the Current Value and Increment By properties. If the sequence generator is not configure to Cycle, then the NEXTVAL port generates the sequence numbers up to the configured End Value.
The sequence generator transformation generates a block of numbers at a time. Once the block of numbers is used then it generates the next block of sequence numbers. As an example, let say you connected the nextval port to two targets in a mapping, the integration service generates a block of numbers (eg:1 to 10) for the first target and then another block of numbers (eg:11 to 20) for the second target.
If you want the same sequence values to be generated for more than one target, then connect the sequence generator to an expression transformation and connect the expression transformation port to the targets. Another option is create sequence generator transformation for each target.
CURRVAL Port:
The CURRVAL is the NEXTVAL plus the Increment By value. You rarely connect the CURRVAL port to other transformations. When a row enters a transformation connected to the CURRVAL port, the integration service passes the NEXTVAL value plus the Increment By value. For example, when you configure the Current Value=1 and Increment By=1, then the integration service generates the following values for NEXTVAL and CURRVAL ports.
If you connect only the CURRVAL port without connecting the NEXTVAL port, then the integration service passes a constant value for each row.
- Generating unique primary key values.
- Replace missing primary keys
- Generate surrogate keys for dimension tables in SCDs.
- Cycle through a sequential range of numbers.
We use it to generate Surrogate Key in DWH environment mostly. When we want to Maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key. Example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.
Sequence Generator Ports :
The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.
- We cannot edit or delete these ports.
- Likewise, we cannot add ports to the transformation.
NEXTVAL:
Use the NEXTVAL port to generate sequence numbers by connecting it to a Transformation or target.
For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.
Sequence in Table 1 will be generated first. When table 1 has been loaded, only then Sequence for table 2 will be generated.
CURRVAL:
CURRVAL is NEXTVAL plus the Increment By value.
- We typically only connect the CURRVAL port when the NEXTVAL port is Already connected to a downstream transformation.
- If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
- when we connect the CURRVAL port in a Sequence Generator Transformation, the Integration Service processes one row in each block.
- We can optimize performance by connecting only the NEXTVAL port in a Mapping.
Example: To use Sequence Generator transformation
- EMP will be source.
- Create a target EMP_SEQ_GEN_EXAMPLE in shared folder. Structure same as EMP. Add two more ports NEXT_VALUE and CURR_VALUE to the target table.
- Create shortcuts as needed.
Creating Mapping:
1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping-> Create-> Give name. Ex: m_seq_gen_example
4. Drag EMP and Target table.
5. Connect all ports from SQ_EMP to target table.
6. Transformation -> Create -> Select Sequence Generator for list -> Create -> Done
7. Connect NEXT_VAL and CURR_VAL from Sequence Generator to target.
8. Validate Mapping
9. Repository -> Save
- Create Session and then workflow.
- Give connection information for all tables.
- Run workflow and see the result in table.
Sequence Generator Properties:
| Setting | Required/Optional |
Description
|
| Start Value |
Required
|
Start value of the generated sequence that we want IS to use if we use Cycle option. Default is 0.
|
| Increment By |
Required
|
Difference between two consecutive values from the NEXTVAL port.
|
End Value
| Optional |
Maximum value the Integration Service generates.
|
| Current Value | Optional |
First value in the sequence.If cycle option used, the value must be greater than or equal to the start value and less the end value.
|
| Cycle | Optional |
If selected, the Integration Service cycles through the sequence range. Ex: Start Value:1 End Value 10 Sequence will be from 1-10 and again start from 1.
|
| Reset | Optional |
By default, last value of sequence during session is saved to repository. Next time the sequence is started from the valued saved.
If selected, the Integration Service generates values based on the original current value for each session.
|
Points to Ponder:
- If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
- If we connect just CURR_VAL only, the value will be same for all records.
- If Current value is 1 and end value 10, cycle option there. Start value is 0.
- There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
- To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
- If Current value is 1 and end value 10, cycle option there. Start value is 1.
- There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
- Use reset option if you want to start sequence from CURR_VAL every time
Creating Sequence Generator Transformation:
Follow the below steps to create a sequence generator transformation:
- Go to the mapping designer tab in power center designer.
- Click on the transformation in the toolbar and then on create.
- Select the sequence generator transformation. Enter the name and then click on Create. Click Done.
- Edit the sequence generator transformation, go to the properties tab and configure the options.
- To generate sequence numbers, connect the NEXTVAL port to the transformations or target in the mapping.
Configuring Sequence Generator Transformation:
Configure the following properties of sequence generator transformation:
- Start Value: Specify the start value of the generated sequence that you want the integration service to use the cycle option. If you select cycle, the integration service cycles back to this value when it reaches the end value.
- Increment By: Difference between two consecutive values from the NEXTVAL port. Default value is 1. Maximum value you can specify is 2,147,483,647.
- End Value: Maximum sequence value the integration service generates. If the integration service reaches this value during the session and the sequence is not configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807.
- Current Value: Current Value of the sequence. This value is used as the first value in the sequence. If cycle option is configured, then this value must be greater than or equal to start value and less than end value.
- Cycle: The integration service cycles through the sequence range.
- Number of Cached Values: Number of sequential values the integration service caches at a time. Use this option when multiple sessions use the same reusable generator. Default value for non-reusable sequence generator is 0 and reusable sequence generator is 1000. Maximum value is ,223,372,036,854,775,807.
- Reset: The integration service generate values based on the original current value for each session. Otherwise, the integration service updates the current value to reflect the last-generated value for the session plus one.
- Tracing level: The level of detail to be logged in the session log file.
Sequence Generator Transformation Ports:
The sequence generator transformation contains only two output ports. They are CURRVAL and NEXTVAL output ports.
NEXTVAL Port:
You can connect the NEXTVAL port to multiple transformations to generate the unique values for each row in the transformation. The NEXTVAL port generates the sequence numbers base on the Current Value and Increment By properties. If the sequence generator is not configure to Cycle, then the NEXTVAL port generates the sequence numbers up to the configured End Value.
The sequence generator transformation generates a block of numbers at a time. Once the block of numbers is used then it generates the next block of sequence numbers. As an example, let say you connected the nextval port to two targets in a mapping, the integration service generates a block of numbers (eg:1 to 10) for the first target and then another block of numbers (eg:11 to 20) for the second target.
If you want the same sequence values to be generated for more than one target, then connect the sequence generator to an expression transformation and connect the expression transformation port to the targets. Another option is create sequence generator transformation for each target.
CURRVAL Port:
The CURRVAL is the NEXTVAL plus the Increment By value. You rarely connect the CURRVAL port to other transformations. When a row enters a transformation connected to the CURRVAL port, the integration service passes the NEXTVAL value plus the Increment By value. For example, when you configure the Current Value=1 and Increment By=1, then the integration service generates the following values for NEXTVAL and CURRVAL ports.
NEXTVAL CURRVAL --------------- 1 2 2 3 3 4 4 5 5 6
If you connect only the CURRVAL port without connecting the NEXTVAL port, then the integration service passes a constant value for each row.
Share This:
Tharun Katanguru
I'm Tharun Katanguru. A full time web designer and Data Engineer. I enjoy to make modern template. I love create blogger template and write about Web Design, Data Warehousing and Data Management. Now I'm working as a Jr Etl Developer.
No Comment to " Sequence Generator Transformation "