Sequence Generator

1. Sequence Generator
   

 

The SG transformation generates numeric values. We can use the SG to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.

 

NEXTVAL:

 

    We can use the NEXTVAL port to generate sequence numbers by connecting it to downstream transformation or target.

 

CURRVAL:

 

    CURRVAL is the NEXTVAL plus Increment By value. We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation. When a row enters a transformation connected to the CURRVAL port, the IS passes the last created NEXTVAL value plus one.

Lookup Transformation

1. Lookup Transformation
   

 

We can use lookup transformation to lookup data in a flat file, relational table , view or synonym.

 

Tasks:

 

à
Get a related value: We can retrieve a value from lookup table based on value in the source.

 

à
Perform a calculation: We can retrieve a value from lookup table and use it in calculation.

 

à
Update slowly changing dimension table: Using lookup we can check whether rows exist in a target or not.

 

 

 

Connected lookup

 

    à Receives input values directly from the pipeline.

 

    à Use a dynamic or static cache.

 

    à Can return multiple columns from the same row or insert into dynamic cache.

 

    à If there is no match, the IS returns default value for all output ports, for dynamic cache IS inserts row into cache.

 

    à If there is a match, the IS returns a result from the lookup condition for all the lookup/output ports.

 

    à Pass multiple output values to another transformation.

 

    à Supports user-defined default values.

 

Un-connected lookup

 

    à Receives input value from the result of :LKP expression in another transformation.

 

    à Use a static cache.

 

    à Returns one column from each row.

 

    à If there is no match, the IS returns NULL.

 

    à If there is match, the IS returns the result of lookup condition.

 

    à Pass one output value to another transformation.

 

    à Does not support user-defined default values.

 

We can perform the following tasks with un-connected lookup:

 

    à Test the result of a lookup in an expression.

 

    à Filter rows based on the lookup results.

 

    à Mark rows for update based on the result of a lookup and update SCD's.

 

    à Call the same lookup multiple times in a mapping.

 

 

 

 

 

Cached lookup:

 

    When we enable lookup caching, the IS queries the lookup source once, caches the values , and lookup values in the cache during the session. Caching lookup values can improve session performance.

 

 

Un-cached lookup:

 

    When we disable caching, each time a row passes into the transformation , the IS issues a select statement to the lookup source for lookup values.

 

 

Lookup policy on multiple match:

 

    Determines which rows the lookup transformation returns when it finds multiple rows that match the lookup condition. We can select the first row or last row returned from the cache or lookup source, or report an error. Or, we can allow the lookup to return any matching value the transformation returns the first value that matches the lookup condition.

 

If we do not enable the output old value on update option, the lookup policy on multiple match option is set to Report an error for dynamic lookups.

 

 

Lookup query:

 

    The IS queries the lookup based on the ports and properties we configure in the lookup transformation. The IS runs a default SQL statement when the first row enters the lookup transformation.

 

Default lookup query contains:

 

à
SELECT statement includes all the lookup ports in the mapping. Do not add or delete any ports columns from the default SELECT statement.

 

à ORDER BY clause orders the columns in the same order they appear in the lookup transformation. We cannot view this when we generate the default SQL using the lookup SQL override.

 

Overriding the LOOKUP Query:

 

    We can override the lookup query for a relational lookup.

 

à Override the ORDER BY clause: Create order by clause with fewer columns to increase performance. When we override the ORDER BY clause we must suppress the generated ORDER BY clause with a comment notation (--).

 

à If the table name or column name in the query contains any reserved words we must enclose them in the quotes.

 

à Add a WHERE clause: Use a lookup SQL override to add a WHERE clause to the default SQL statement. We can use the WHERE clause to reduce the number of rows included in the cache.

 

à Use a lookup SQL override to query the lookup data from multiple tables.

 

 

Notes:

 

è Lookup table can be a single table or we can join multiple tables in the same database using a lookup SQL override.

 

è The Designer designates each column in the lookup source as a lookup(L) and output(O) port.

 

è If we delete ports from flat file lookup, the session fails.

 

è We can delete ports from a relational lookup if the mapping does not use the lookup port. This reduces the amount of memory the IS needs to run the session.

 

è The IS always caches flat files and pipeline lookups.

 

è If we use pushdown optimization, we cannot override the ORDER BY clause or suppress the order by clause with the comment notation.

 

è The IS matches null values for lookup transformation. For example, if an input lookup condition column is NULL, the IS evaluates the NULL equal to NULL in the lookup.

 

è If we configure flat file lookup for sorted input, the IS fails the session if the condition columns are not grouped. If the condition columns are grouped, but not sorted, the IS processes the lookup as if we did not configure sorted input.

 

è Lookup condition contains the following operators: =,>,<,>=,<=,!=

 

è We can use a dynamic cache for relational or flat file lookups.

 

è The IS builds caches for un-connected lookup sequentially regardless of how we configure cache building.

 

 

 

 

 

 

 

 

 

Lookup caches:

 

    The IS builds a cache in memory when it processes the first row of data in a cached lookup transformation. The IS stores condition values in the index cache and output values in the data cache. The IS queries the cache for each row that enters the transformation.

 

Building caches:

 

    We can configure the session to build caches sequentially or concurrently. When we build sequential caches, the IS creates cache as the source rows enter the lookup.

When we configure the session to build concurrent caches, the IS does not wait for the first row to enter the lookup before it creates cache. Instead, it builds multiple caches concurrently.

 

Persistent cache:

 

    We can save the lookup cache files and reuse them the next time the IS processes a lookup. If the lookup table does not change between sessions we can the lookup to use persistent cache. The first time the IS runs a session using a persistent lookup cache, it saves the cache files to disk instead of deleting them. Then next time we run the session it builds the cache memory from cache files. If the lookup table changes occasionally, we can override the lookup property to Recache the lookup from the database.

 

 

Recache from source:    

 

    If the persistent cache is not synchronized with the lookup table, we can configure the lookup transformation to rebuild the lookup cache.

We can instruct the IS to rebuild the cache if we think that the lookup source changed since the last time the IS built the persistent cache.

 

 

Static cache:

 

    The IS does not update the cache while it processes the lookup transformation.

 

Dynamic cache:

 

    The IS dynamically inserts or updates data in the lookup cache and passes data to the target.

 

 

 

 

 

 

 

 

 

Properties:

 

NewLookupRow:

 

    The designer adds this port to a lookup configured to use a dynamic cache. Indicates with a numeric value whether the IS inserts or updates the row in the cache, or makes no changes to the cache.

 

0 – IS does not update or insert the row in the cache.

 

1 – IS inserts the row into the cache.

 

2 – IS updates the row in the cache.

 

Associated port:

 

    The IS uses the data in the associated port to insert or update rows in the cache. If we associate a sequence ID, the IS generates a primary key for inserted rows in the lookup cache.

 

Ignore Null inputs for updates:

 

    We can enable this property when we don't want the IS to update the column in the cache when the data in this column contains a null value.

 

Ignore in Comparison:

 

    The IS compares the values in all lookup ports with the values in their associated input ports by default. We can use this property when we want the IS to ignore the port when it compares values before updating a row.

 

    When we add a WHERE clause in a lookup SQL override, the IS uses the WHERE clause to build the cache from the database and to perform a lookup on the database table for an un-cached lookup. It does not use the WHERE clause to insert rows into a dynamic cache when it runs a session.

 

 

Shared cache:

 

    We can share the cache between multiple transformations. We can share the unnamed cache between transformations in the same mapping. We can share the named cache between transformations in same or different mappings.

Router Transformation

1. Router Transformation
   

 

Router transformation tests same input data based on multiple conditions and gives the option to route rows of data that do not meet any of the conditions to a default output group.

Working with Groups

 

Router has the following types of groups.

 

• Input Group : The designer copies property information from the input ports of the input group to create a set of output ports for each output group.
  

 

• 
  Output Group : There are two types of output groups.
  

    è User-defined Groups

    è Default Group

 

We cannot delete or modify output ports or properties.

 

User defined Group

 

The Designer creates the default group after we create one new user-defined group. The designer does not allow to edit or delete the default group. This group does not have a group filter condition associated with it. If all the conditions evaluate to FALSE, the IS passes the row to the default group.

 

• If you want to drop all rows in the default group, do not connect it to transformation or target.
  
• The Designer deletes the default group when we delete the last user-defined group from the list.
  

 

• We can enter default values for input ports in Router to replace NULL input values.
  
• If a row meets more than one group filter condition, the IS passes this row multiple times.
  

 

• We can connect one group to one transformation or one target.
  
• We can connect one output port in a group to multiple transformations or targets.
  
• We can connect multiple output ports in one group to multiple transformations or targets.
  
• We cannot connect more than one group to one transformation or target.
  

1. Router Transformation
   

 

Router transformation tests same input data based on multiple conditions and gives the option to route rows of data that do not meet any of the conditions to a default output group.

Working with Groups

 

Router has the following types of groups.

 

• Input Group : The designer copies property information from the input ports of the input group to create a set of output ports for each output group.
  

 

• 
  Output Group : There are two types of output groups.
  

    è User-defined Groups

    è Default Group

 

We cannot delete or modify output ports or properties.

 

User defined Group

 

The Designer creates the default group after we create one new user-defined group. The designer does not allow to edit or delete the default group. This group does not have a group filter condition associated with it. If all the conditions evaluate to FALSE, the IS passes the row to the default group.

 

• If you want to drop all rows in the default group, do not connect it to transformation or target.
  
• The Designer deletes the default group when we delete the last user-defined group from the list.
  

 

• We can enter default values for input ports in Router to replace NULL input values.
  
• If a row meets more than one group filter condition, the IS passes this row multiple times.
  

 

• We can connect one group to one transformation or one target.
  
• We can connect one output port in a group to multiple transformations or targets.
  
• We can connect multiple output ports in one group to multiple transformations or targets.
  
• We cannot connect more than one group to one transformation or target.
  

Rank Transformation

1. Rank Transformation
   

 

The Rank transformation allows us to select the top or bottom rank of the data.

 

• When the IS runs in the ASCII data movement mode, it sorts session data using a binary sort order.
  
• For Unicode data movement mode, it uses the sort order configured for the session.
  

 

Rank Caches

 

The IS stores group information in an index cache and row data in a data cache.

During a workflow, the IS compares an input row with rows in the data cache. If the input row out-ranks a cached row, the IS replaces a cached row with input row.

For multiple partitions, the IS creates separate caches for each partition.

 

Rank Port

 

Use to designate the column for which we want to rank values. We can designate only one Rank port in Rank transformation. The Rank port is an input/output port.

 

Rank Index Port

 

The designer automatically creates a RANKINDEX port for each rank transformation. The IS uses the Rankindex port to store the ranking position for each row in a group. It is an output port only.

 

Defining Groups

 

Like the Aggregator, the Rank transformation allows to group information.

 

à If two rank values match, they receive the same value in the rank index and the transformation skip the next value.

Joiner Transformation

1. Joiner Transformation
   

 

Joiner transformation joins two related heterogeneous sources residing in different locations or file systems.

 

• We use the joiner to join two sources with at least one matching port.
  

 

• Joiner typically combine information from two different sources that do not have matching keys such as flat file sources.
  

 

• Joiner allows to use join sources that contain binary data.
  

 

There are some limitations on the pipelines we connect to the joiner. We cannot use a joiner in the following situations.

 

• Both input pipelines originate from the same Source Qualifier transformation.
  
• Both input pipelines originate from the same Normalizer or Joiner.
  
• Either input pipeline contains an Update Strategy transformation.
  
• We connect a sequence generator transformation directly before joiner.
  

 

Master and Detail Source

 

When we add the ports of a transformation to a Joiner, the ports from the first source are automatically set as detail sources. Adding the ports from the second transformation automatically sets them as master sources.

 

Join Types

 

• Normal (Default)
  
• Master Outer
  
• Detail Outer
  
• Full Outer
  

 

Master – Detail Join Rules

 

If a session contains a mapping with multiple joiner transformations, the IS reads rows in the following order.

 

• For each joiner, the IS reads all the master rows before it reads the first detail row.
  

 

• For each joiner, the IS produces output rows as soon as it reads the first detail row.
  

 

If you create a mapping with two joiners in the same target load order group, make sure each joiner receives detail rows from a different source pipeline, so that IS reads the rows according to the master-detail join rules.

 

 

 

 

 

 

 

Joiner Caches

 

When we run a session with joiner, the IS reads all the rows from the master source and builds index and data caches based on the master source. Since the cache read only the master source rows, we should specify the source with fewer rows as the master source.

 

During a workflow, the joiner compares each row of the master source against the detail source. The fewer unique rows in the master, the fewer iterations of the join comparison occur, which speeds the join process.

 

 

Join Condition

 

Both ports in a condition must have the same data type. We need to convert the data types for non-matching data types.

If the data types do not match, the mapping will be invalid.

Join condition only supports equality between fields.

The joiner does not match null values.

To join rows with null values, we can replace null input with default values, and then join the default values.

 

Join Types

 

Normal Join

 

The IS discards all rows of data from the master detail source that do not match based on the condition.

 

Master Outer join

 

It keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.

 

 

Detail Outer join

 

It keeps all rows of data from the master source and the matching rows from the detail source. It discards unmatched rows from the detail source.

 

Full Outer Join

 

It keeps all rows of data from both the master and detail sources.

 

 

• A normal or master outer join performs faster than a full outer or detail outer join.
  
• If a result set includes fields that do not contain data in either of the sources, the joiner populates the empty fields with null values.
  

 

 

 

 

Performing a join in the database

 

Performing a join in the database is faster than performing a join in the session. In some cases, its not possible, such as joining tables from two different databases or flat file systems.

 

To perform a join in the database

 

• Create a pre-session stored procedure.
  
• Use the Source Qualifier to perform the join.
  

 

Mapping Parameters and Mapping Variables

1. Mapping Parameters and Mapping Variables
   

 

Mapping parameters and variables represent values in mappings and mapplets.

 

Mapping Parameter

 

A mapping parameter represents a value that we can define before running a session. A mapping parameter retains the same value throughout the entire session.

 

 

For example, you want to use the same session to extract transaction records for each of the customers individually. Instead of creating a separate mapping for each customer account, you can create a mapping parameter to represent a single customer account. Then use the parameter in a source filter to extract only data for that customer account. Before running the session, you enter the value of the parameter in the parameter file.

 

 

If the parameter is not defined in the parameter file, the Integration Service uses the user-defined initial value for the parameter. If the initial value is not defined, the Integration Service uses a default value based on the data type of the mapping parameter.

 

IsExprVar: TRUE or FALSE

 

 

Determines how the Integration Service expands the parameter in an expression string. If true, the Integration Service expands the parameter before parsing the expression. If false, the Integration Service expands the parameter after parsing the expression.

 

Note: If you set this field to true, you must set the parameter data type to String, or the Integration Service fails the session.

 

 

 

The Integration Service looks for the value in the following order:

 

1. Value in parameter file

2. Value in pre-session variable assignment

3. Value saved in the repository

4. Initial value

5. Datatype default value

 

 

 

Mapping Variables

 

A mapping variable represents a value that can change through the session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

 

 

Use mapping variables to perform incremental reads of a source. For example, the customer accounts in the mapping parameter example above are numbered from 001 to 065, incremented by one. Instead of creating a mapping parameter, you can create a mapping variable with an initial value of 001. In the mapping, use a variable function to increase the variable value by one. The first time the Integration Service runs the session, it extracts the records for customer account 001. At the end of the session, it increments the variable by one and saves that value to the repository. The next time the Integration Service runs the session, it extracts the data for the next customer account, 002. It also increments the variable value so the next session extracts and looks up data for customer account 003.

 

 

The Integration Service holds two different values for a mapping variable during a session run:

 

 

♦Start value of a mapping variable

♦Current value of a mapping variable

 

Start value

 

The start value is the value of the variable at the start of the session. The start value could be a value defined in the parameter file for the variable, a value assigned in the pre-session variable assignment, a value saved in the repository from the previous run of the session, a user defined initial value for the variable, or the default value based on the variable data type.

 

Current value

 

The current value is the value of the variable as the session progresses. When a session starts, the current value of a variable is the same as the start value. As the session progresses, the Integration Service calculates the current value using a variable function that you set for the variable. The final current value for a variable is saved to the repository at the end of a successful session. When a session fails to complete, the Integration Service does not update the value of the variable in the repository. The Integration Service states the value saved to the repository for each mapping variable in the session log.

 

If a variable function is not used to calculate the current value of a mapping variable, the start value of the variable is saved to the repository.

 

 

Aggregation type

 

The Integration Service uses the aggregate type of a mapping variable to determine the final current value of the mapping variable.

 

Types

 

--> Count

--> Max

--> Min

 

You can configure a mapping variable for a Count aggregation type when it is an Integer or Small Integer. You can configure mapping variables of any data type for Max or Min aggregation types.

 

Variable functions

 

Use variable functions in an expression to set the value of a mapping variable for the next session run.

 

 

SetMaxVariable. Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMaxVariable with a mapping variable, the aggregation type of the mapping variable must be set to Max.

 

 

SetMinVariable. Sets the variable to the minimum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMinVariable with a mapping variable, the aggregation type of the mapping variable must be set to Min.

 

 

SetCountVariable. Increments the variable value by one. In other words, it adds one to the variable value when a row is marked for insertion, and subtracts one when the row is marked for deletion. It ignores rows marked for update or reject.

 

 

SetVariable. Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository. To use the SetVariable function with a mapping variable, the aggregation type of the mapping variable must be set to Max or Min. The SetVariable function ignores rows marked for delete or reject.

 

The Integration Service does not save the final current value of a mapping variable to the repository when any of the following conditions are true:

 

 

♦The session fails to complete.

♦The session is configured for a test load.

♦The session is a debug session.

♦The session runs in debug mode and is configured to discard session output.

 

 

Use a variable function in any of the following transformations:

♦Expression

♦Filter

♦Router

♦Update Strategy

 

 

à These will appear in the variables tab of the Expression editor.

 

à When you use mapping parameters and variables in a Source Qualifier transformation, the Designer expands them before passing the query to the source database

 

à When you create a reusable transformation in the Transformation Developer, use any mapping parameter or variable. Since a reusable transformation is not contained within any mapplet or mapping, the Designer validates the usage of any mapping parameter or variable in the expressions of reusable transformation for validation.

 

à Enclose string and date time parameters and variables in quotes in the SQL Editor.

 

à Mapping parameter and variable values in mapplets must be preceded by the mapplet name in the parameter file, as follows:

mappletname.parameter=value

mappletname.variable=value

 

à You cannot use variable functions in the Rank or Aggregator transformation.

 

Default Values for Mapping Parameters and Variables Based on Data type

 

String – Empty String; Numeric – 0;

Date/time – 1/1/1753 A.D

 

à Source qualifier filter condition: state = '$$State'

Filter transformation filter condition: state = $$State

Normalizer Transformation

1. Normalizer Transformation
   

 

The Normalizer transformation receives a row that contains multiple-occurring columns and returns a row for each instance of the multiple-occurring data. The transformation processes multiple-occurring columns or multiple-occurring groups of columns in each source row.

 

The Normalizer transformation parses multiple-occurring columns from COBOL sources, relational tables, or other sources.

 

For example, you might have a relational table that stores four quarters of sales by store. You need to create a row for each sales occurrence. You can configure a Normalizer transformation to return a separate row for each quarter.

 

 

The following source rows contain four quarters of sales by store:

Store1 100 300 500 700

Store2 250 450 650 850

 

Output

 

Store1 100 1

Store1 300 2

Store1 500 3

Store1 700 4

Store2 250 1

Store2 450 2

Store2 650 3

Store2 850 4

 

 

The Normalizer transformation generates a key for each source row.

 

 

 

Types

 

VSAM Normalizer transformation

 

    A non-reusable transformation that is a Source Qualifier transformation for a COBOL source. The Mapping Designer creates VSAM Normalizer columns from a COBOL source in a mapping. The column attributes are read-only. The VSAM Normalizer receives a multiple-occurring source column through one input port.

 

Pipeline Normalizer transformation

 

    A transformation that processes multiple-occurring data from relational tables or flat files.

You might choose this option when you want to process multiple-occurring data from another transformation in the mapping.

 

 

A VSAM Normalizer transformation has one input port for a multiple-occurring column. A pipeline Normalizer transformation has multiple input ports for a multiple-occurring column.

When you create a Normalizer transformation in the Transformation Developer, you create a pipeline Normalizer transformation by default. When you create a pipeline Normalizer transformation, you define the columns based on the data the transformation receives from another type of transformation such as a Source Qualifier transformation.

 

The Normalizer transformation has one output port for each single-occurring input port.

 

Generated Column ID (GCID)

 

    The Normalizer transformation has a generated column ID (GCID) port for each multiple-occurring column. The generated column ID is an index for the instance of the multiple-occurring data. For example, if a column occurs four times in a source record, the Normalizer returns a value of 1, 2, 3, or 4 in the generated column ID based on which instance of the multiple-occurring data occurs in the row.

 

The Normalizer transformation has at least one generated key column in the output row.

 

At the end of each session, the Integration Service updates the generated key value in the Normalizer transformation to the last value generated for the session plus one.

 

-->

You can change the ports on a pipeline Normalizer transformation by editing the columns on the Normalizer tab for relational. To change a VSAM Normalizer transformation, you need to change the COBOL source and recreate the transformation.

 

 

Properties

 

Reset

 

    At the end of a session, resets the value sequence for each generated key value to the value it was before the session.

 

Restart

 

    Starts the generated key sequence at 1. Each time you run a session, the key sequence value starts at 1 and overrides the sequence value on the Ports tab.

 

 

Normalizer tab

 

    The Normalizer tab defines the structure of the source data.

    

    Level: Group columns. Columns in the same group occur beneath a column with a lower level number. When each column is the same level, the transformation contains no column groups.

 

    Occurs: The number of instances of a column or group of columns in the source row.

When you create a pipeline Normalizer transformation, you can edit the columns. When you create a VSAM Normalizer transformation, the Normalizer tab is read-only.

 

 

 

VSAM Normalizer Transformation

 

 

    The VSAM Normalizer transformation is the source qualifier for a COBOL source definition. A COBOL source is a flat file that can contain multiple-occurring data and multiple types of records in the same file.

 

    VSAM (Virtual Storage Access Method) is a file access method for an IBM mainframe operating system. VSAM files organize records in indexed or sequential flat files.

 

    A COBOL source definition can have an OCCURS statement that defines a multiple-occurring column. The COBOL source definition can also contain a REDEFINES statement to define more than one type of record in the file.

 

 

Normalizer Tab for a VSAM Normalizer Transformation

 

    POffs: Physical offset. Location of the field in the file. The first byte in the file is zero.

    Plen: Physical length. Number of bytes in the field.

    

    Level: Provides column group hierarchy. The higher the level number, the lower the data is in the hierarchy. Columns in the same group occur beneath a column with a lower level number.

 

    Picture: How the data is stored or displayed in the source. Picture 99V99 defines a numeric field with two implied decimals. Picture X(10) indicates ten characters.

 

    Key Type: Type of key constraint to apply to this field. When you configure a field as a primary key, the Integration Service generates unique numeric IDs for this field when running a session with a COBOL source.

 

    Redefines: Indicates that the column REDEFINES another column.

Transaction Control Transformation

1. Transaction Control Transformation
   

 

We can control commit and roll back transactions based on a set of rows that pass through using Transaction Control transformation.

A transaction is the set of rows bound by commit or roll back rows.

 

Use the Transaction Control transformation to define conditions to commit and roll back transactions from transactional targets.

 

In PowerCenter, you define transaction control at two levels:

 

Within a mapping: within a mapping, you use the Transaction Control transformation to define a transaction. You define transactions using an expression in a Transaction Control transformation. Based on the return value of the expression, you can choose to commit, roll back, or continue without any transaction changes.

 

Within a session: When you configure a session, you configure it for user-defined commit. You can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

 

When you run the session, the Integration Service evaluates the expression for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the target or targets. When the Integration Service evaluates a roll back row, it rolls back all rows in the transaction from the target or targets.

 

If the mapping has a flat file target you can generate an output file each time the Integration Service starts a new transaction. You can dynamically name each target flat file.

 

Properties

 

Transaction control condition

 

Enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression:

 

IIF (condition, value1, value2)

 

IIF (NEW_DATE = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

 

The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row. When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction.

 

 

Use the following built-in variables in the Expression Editor when you create a transaction control expression:

 

TC_CONTINUE_TRANSACTION: The Integration Service does not perform any transaction change for this row. This is the default value of the expression.

 

TC_COMMIT_BEFORE: The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.

 

TC_COMMIT_AFTER: The Integration Service writes the current row to the target, commits the transaction, and begins a new transaction. The current row is in the committed transaction.

 

TC_ROLLBACK_BEFORE: The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.

 

TC_ROLLBACK_AFTER: The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.

 

 

If the transaction control expression evaluates to a value other than commit, roll back, or continue, the Integration Service fails the session.

 

Example

You want to use transaction control to write order information based on the order entry date. You want to ensure that all orders entered on any given date are committed to the target in the same transaction.

 

You can also use Custom transformations configured to generate transactions to define transaction boundaries.

 

Transaction Control transformations can be either effective or ineffective for the downstream transformations and targets in the mapping. The Transaction Control transformation becomes ineffective for downstream transformations or targets if you put a transformation that drops incoming transaction boundaries after it. This includes any of the following active sources or transformations:

 

Aggregator transformation with the All Input level transformation scope

Joiner transformation with the All Input level transformation scope

Rank transformation with the All Input level transformation scope

Sorter transformation with the All Input level transformation scope

Custom transformation with the All Input level transformation scope

Custom transformation configured to generate transactions

Transaction Control transformation

 

 

 

 

Notes

 

--> Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.

 

--> You must connect each target instance to a Transaction Control transformation.

 

-->You can connect multiple targets to a single Transaction Control transformation.

 

--> You can connect only one effective Transaction Control transformation to a target.

 

--> You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.

TRANSFORMATION 3

Target Load Order

 

We specify a target load order based on the Source Qualifier transformations in a mapping. If you have multiple Source Qualifier transformations connected to multiple targets, you can designate the order in which the Integration Service loads data into the targets.

 

constraint-based loading

 

If one Source Qualifier transformation provides data for multiple targets, you can enable constraint-based loading in a session to have the Integration Service load data based on target table primary and foreign key relationships.

 

 

 

1. Stored Procedure Transformation
   

 

A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script. Stored procedures are stored and run within the database.

 

You might use stored procedures to complete the following tasks:

 

--> Check the status of a target database before loading data into it.

--> Determine if enough space exists in a database.

--> Perform a specialized calculation.

--> Drop and recreate indexes.

 

Stored procedures also provide error handling and logging necessary for critical tasks.

 

You might use a stored procedure to perform a query or calculation that you would otherwise make part of a mapping.

 

There are three types of data that pass between the Integration Service and the stored procedure:

 

Input/Output Parameters

        

For many stored procedures, you provide a value and receive a value in return. These values are known as input and output parameters.

 

Return Values

 

The Stored Procedure transformation captures return values in a similar manner as input/output parameters, depending on the method that the input/output parameters are captured.

If a stored procedure returns a result set rather than a single return value, the Stored Procedure transformation takes only the first value returned from the procedure.

 

Status Codes

        

Status codes provide error handling for the Integration Service during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. The Integration Service uses it to determine whether to continue running the session or stop.

 

Connected Stored Procedure

 

The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.

 

We can go for Connected when we want to

 

--> Run a stored procedure every time a row passes through the Stored Procedure transformation.

--> Pass parameters to the stored procedure and receive a single output parameter.

--> Pass parameters to the stored procedure and receive multiple output parameters.

    

Un Connected Stored Procedure

 

The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.

 

 

 

 

 

 

We can go for Connected when we want to

 

--> Run a stored procedure before or after a session.

--> Run a stored procedure once during a mapping, such as pre- or post-session.

--> Run nested stored procedures.

--> Call multiple times within a mapping.

 

 

Stored Procedure Types

 

Normal. The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. This is useful for calling the stored procedure for each row of data that passes through the mapping, such as running a calculation against an input port. Connected stored procedures run only in normal mode.

 

Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.

 

Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.

 

Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for verifying target tables or disk space on the target system.

 

Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

 

You can run more than one Stored Procedure transformation in different modes in the same mapping. For example, a pre-load source stored procedure can check table integrity, a normal stored procedure can populate the table, and a post-load stored procedure can rebuild indexes in the database.

 

--> Stored Procedure transformations are created as Normal type by default, which means that they run during the mapping, not before or after the session.

 

-->If stored procedures exist in the database that do not contain parameters or return values, they appear in a folder called PROCEDURES otherwise in the FUNCTIONS folder.

 

 

Configuring an Unconnected Transformation

 

When using an unconnected Stored Procedure transformation in an expression, you need a method of returning the value of output parameters to a port.

    

--> Assign the output value to a local variable.

--> Assign the output value to the system variable PROC_RESULT.

 

By using PROC_RESULT, you assign the value of the return parameter directly to an output port, which can apply directly to a target. You can also combine the two options by assigning one output parameter as PROC_RESULT, and the other parameter as a variable.

 

Use PROC_RESULT only within an expression. If you do not use PROC_RESULT or a variable, the port containing the expression captures a NULL. You cannot use PROC_RESULT in a connected Lookup transformation or call text.

 

If the stored procedure returns a single output parameter or a return value (but not both), you should use the reserved variable PROC_RESULT as the output variable. In the previous example, the expression would appear as:

:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)

 

inID can be either an input port for the transformation or a variable in the transformation. The value of PROC_RESULT is applied to the output port for the expression.

 

If the stored procedure returns multiple output parameters, you must create variables for each output parameter. For example, if you create a port called varOUTPUT2 for the stored procedure expression, and a variable called varOUTPUT1, the expression appears as:

 

:SP.GET_NAME_FROM_ID(inID, varOUTPUT1, PROC_RESULT)

 

Calling a Pre- or Post-Session Stored Procedure

 

You may want to run a stored procedure once per session. For example, if you need to verify that tables exist in a target database before running a mapping, a pre-load target stored procedure can check the tables, and then either continue running the workflow or stop it. You can run a stored procedure on the source, target, or any other connected database.

 

Stored Procedure à Properties --> Call Text

 

This is the name of the stored procedure, followed by all applicable input parameters in parentheses. If there are no input parameters, you must include an empty pair of parentheses, or the call to the stored procedure fails.

For example, to call a stored procedure called check_disk_space, enter the following text:

check_disk_space()

 

if the stored procedure check_disk_space required a machine name as an input parameter, enter the following text:

check_disk_space(oracle_db)

 

When passing a datetime value through a pre- or post-session stored procedure, the value must be in the Informatica default date format and enclosed in double quotes as follows:

SP("12/31/2000 11:45:59")

 

 

Error Handling

 

Sometimes a stored procedure returns a database error, such as "divide by zero" or "no more rows." The final result of a database error during a stored procedure depends on when the stored procedure takes place and how the session is configured.

You can configure the session to either stop or continue running the session upon encountering a pre- or post-session stored procedure error. By default, the Integration Service stops a session when a pre- or post-session stored procedure database error occurs.

    

Session --> Config Object --> On Stored Procedure error --> Stop, Continue

 

 

If the database returns an error for a particular row, the Integration Service skips the row and continues to the next row. As with other row transformation errors, the skipped row appears in the session log.

 

Notes

 

à A single output parameter is returned using the variable PROC_RESULT.

 

à When you call a stored procedure in an expression, use the :SP reference qualifier.

 

à Use PROC_RESULT to apply the output parameter of a stored procedure expression directly to a target. You cannot use a variable for the output parameter to pass the results directly to a target. Use a local variable to pass the results to an output port within the same transformation.

 

à Nested stored procedures allow passing the return value of one stored procedure as the input parameter of another stored procedure. For example, if you have the following two stored procedures:

 

 

−get_employee_id (employee_name)

−get_employee_salary (employee_id)

And the return value for get_employee_id is an employee ID number, the syntax for a nested stored procedure is:

:sp.get_employee_salary (:sp.get_employee_id (employee_name))

 

à Do not use single quotes around string parameters. If the input parameter does not contain spaces, do not use any quotes. If the input parameter contains spaces, use double quotes.