Advanced Transformations
Advanced transformations appear in the harder scenario questions: Rank, Normalizer, Transaction Control, Stored Procedure, SQL and Java.
Rank
Rank returns the top or bottom N rows per group. Set the Top/Bottom property, the rank port (a numeric port to rank on), Number of Ranks, and Group By ports. Sort the input on the group-by ports first, or groups are ranked incorrectly. The output rank index starts at 1.
Normalizer
Normalizer turns one input row into many output rows by expanding repeating groups - typically COBOL or VSAM records. You define Normalize ports and the number of occurrences, and choose the normalizer type (VSAM or GK). It can also split delimited values in relational data.
Transaction Control
This transformation emits per-row codes that decide commit and rollback boundaries: TC_CONTINUE_TRANSACTION, TC_COMMIT_BEFORE, TC_COMMIT_AFTER, TC_ROLLBACK_BEFORE and TC_ROLLBACK_AFTER. It works together with the session commit interval.
Stored Procedure and SQL
A Stored Procedure transformation runs a database procedure; it can be connected (values flow through ports) or unconnected (called from an expression such as :SP.proc_name(...)), and runs once per session or per row depending on its execution setting. The SQL transformation has Static SQL mode (no input ports, SQL runs as written) and Dynamic SQL mode (SQL built per row), with ANSI or passthrough dialects. A Java transformation can be active or passive and emits rows with generateRow().
Worked example
Top two salaries per department: sort by department, add Rank with Group By = department, rank port = salary, Top, Number of Ranks = 2, then keep only rows whose rank index is 1 or 2.
Traps
- Forgetting the sort before Rank, or omitting Group By and ranking globally.
- Mismatched occurrence counts in a Normalizer.
- A Transaction Control condition that never returns a valid TC_ code.