Configuring the Target Update Override

By default, the Integration Service updates target tables based on key values. However, you can override
the default UPDATE statement for each target in a mapping. You might want to update the target based on non-key columns.

For a mapping without an Update Strategy transformation or a Custom transformation with the update strategy property enabled, configure the session to mark source rows as update. The Target Update option only affects source rows marked as update. The Integration Service processes all rows marked as insert, delete, or reject normally. When you configure the session, mark source rows as data-driven. The Target Update Override only affects source rows marked as update by the Update Strategy or Custom transformation.

For example, a mapping passes the total sales for each salesperson to the T_SALES table.
The Designer generates the following default UPDATE statement for the target T_SALES:

UPDATE T_SALES SET EMP_NAME = :TU.EMP_NAME, DATE_SHIPPED = :TU.DATE_SHIPPED, TOTAL_SALES= :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID

Because the target ports must match the target column names, the update statement includes the keyword:TU to specify the ports in the target transformation. If you modify the UPDATE portion of the statement, be sure to use :TU to specify ports.

Overriding the WHERE Clause

You can override the WHERE clause to include non-key columns. For example, you might want to update records for employees named Mike Smith only. To do this, you edit the WHERE clause as follows:
UPDATE T_SALES SET DATE_SHIPPED = :TU.DATE_SHIPPED,
TOTAL_SALES = :TU.TOTAL_SALES WHERE :TU.EMP_NAME = EMP_NAME and
EMP_NAME = 'MIKE SMITH'.

Steps to Enter a Target Update Statement

Use the following procedure to create an update statement.
To enter a target update statement:
1. Double-click the title bar of a target instance.
2. Click Properties.
3. Click the Open button in the Update Override field.
The SQL Editor displays.
4. Select Generate SQL.
The default UPDATE statement appears.
5. Modify the update statement.
You can override the WHERE clause to include non-key columns.
Enclose all reserved words in quotes.
6. Click OK.
The Designer validates the SQL when you save the mapping.

Note:

1. If you use target update override, you must manually put all database reserved words in quotes.
2. You cannot override the default UPDATE statement if the target column name contains any of the following characters:
' , ( ) < > = + - * / \ t \ n \ 0 <space>.

Hope you enjoyed it. Please leave your comments.

Truncate Target Table

It is very interesting to know what happens when the integration service issues a Truncate command on the target. We assume that it truncates the target table. Well but that's not the actual case.  There are some scenarios where truncate option is not considered by the IS and issues a Delete instead of Truncate command.

Read below article to know more .

The Integration Service can truncate target tables before running a session. You can choose to truncate tables on a target-by-target basis. If you have more than one target instance, select the truncate target table option for one target instance.

The Integration Service issues a delete or truncate command based on the target database and primary key foreign key relationships in the session target. To optimize performance, use the truncate table command. The delete from command may impact performance.

The following table describes the commands that the Integration Service issues for each database:

If the database user is not specified as the target owner name or does not have the database privilege to truncate the target table, the Integration Service issues a delete command instead.

If you do not want to save deleted records for rollback, you can disable logging to improve the speed of the delete.

For all databases, if the Integration Service fails to truncate or delete any selected table because the user lacks

the necessary privileges, the session fails.

If you enable truncate target tables with the following sessions, the Integration Service does not truncate target

tables:

• Incremental aggregation. When you enable both truncate target tables and incremental aggregation in thesession properties, the Work flow Manager issues a warning that you cannot enable truncate target tables and incremental aggregation in the same session.

•  Test load. When you enable both truncate target tables and test load, the Integration Service disables the truncate table function, runs a test load session, and writes a message to the session log indicating that the truncate target tables option is turned off for the test load session.

• Real-time. The Integration Service does not truncate target tables when you restart a JMS or WebSphere MQ real-time session that has recovery data.

Hope you enjoyed it.

Please share your valuable comments.

Mapping Parameters and variables:

In this post I would like to share some useful information regarding mapping parameters and variables.

Mapping Parameters and variables enables us to make our code reusable.

first declare and assign value before starting the session

Use mapping parameters and variables in a mapping to incrementally extract data.

Before going into details let us recall what are Parameters and Variables.

Parameters:

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

2.When you use a mapping parameter, you declare and use the parameter in a mapping or mapplet. Then define the value of the parameter in a parameter file. The Integration Service evaluates all references to the parameter to that value.

Variables:

1.Unlike a mapping parameter, 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.

2.To override the saved value, define the start value of the variable in a parameter file or assign a value in the pre-session variable assignment in the session properties.

Using Mapping Parameters and Variables:

1.You can create mapping parameters in Mapping or Mapplet Designer. Once created appears in the variables part of the expression editor.

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

3.When you create a reusable transformation in the Transformation Developer, use any mapping parameter or variable.

4.If the parameter or variable is not defined in the mapplet or mapping, or if it is used incorrectly in the reusable transformation, the Designer logs an error when you validate the mapplet or mapping.

5.When the Designer validates a mapping variable in a reusable transformation, it treats the variable as an Integer datatype.

6.You cannot use mapping parameters and variables interchangeably between a mapplet and a mapping.

7.When you enter mapping parameters and variables of a string data type in a Source Qualifier transformation, use a string identifier appropriate for the source database

8.When you enter string parameters or variables using the PowerCenter transformation language, do not use additional quotes.

Source Qualifier transformation to extract rows for a particular state

STATE = ‘$$State’

Filter transformation to filter rows for a particular state:

STATE = $$State

Using Date-time Parameters and Variables

1.When you use a date-time parameter or variable in the Source Qualifier transformation, you might need to change the date format to the format used in the source.

2.You can also use mapping parameters in transformation overrides in the session properties in the Workflow Manager. You can override properties such as a filter or user-defined join in a Source Qualifier transformation.

Mapping Variables Values:

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

To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:

SETVARIABLE($$MAPVAR,NULL)

Start Value:

The Integration Service looks for the start 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.Data type default value

Current Value:

1.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

2.Unlike the start value of a mapping variable,the current value can change as the Integration Service evaluates the current value of a variable as each row passes through the mapping.

Variable Functions:

SetMaxVariable. Sets the variable to the maximum value . It ignores rows marked for

update, delete, or reject.

SetMinVariable. Sets the variable to the minimum value . It ignores rows marked for

update, delete, or reject.

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.

Note:

1.Use variable functions only once for each mapping variable in a pipeline.

2.When you declare a mapping variable for a mapplet and use the mapplet multiple times within the same mapping, the same mapping variable value is shared across all mapplet instances.

Tips:

1.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

2.You cannot use variable functions in the Rank or Aggregator transformation. Use a different transformation for variable functions.

3.To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:

SETVARIABLE($$MAPVAR,NULL).

Hope you enjoyed it.

Please leave your comments.

 

Using Database Sequence Instead of Sequence Generator in Informatica

We generate Surrogate Key in DW environment to maintain history and we need a key other than Primary Key to uniquely identify the record. Example: If EMPNO is the key, we can keep only one record in target for the same Employee number and can’t maintain history, so we use Surrogate key as Primary key and not EMPNO.
Informatica Sequence Generator:

• Passive and Connected Transformation
• The Sequence Generator transformation generates numeric values
• Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers

If a situation comes to use the DB Level Sequence  instead of the Sequence Generator Transformation in Informatica Here are the possible ways.

1.Create a Sequence in Oracle.(considering the underlying DB as Oracle)
2.Access the Nxtval of that sequence in  nInformatica by using below methods.

Using SQL Transformation:

Below is the mapping flow 

1.Connect all input ports  from source to SQL  Transformation.

 2.Add one extra output port which is attached with sequence generator query
3. Connect all ports from SQL tranformation to Relational Target. Once we do that we are done with the mapping.

 Use Stored Procedure Transformation

 Below is the mapping flow:

 

 1.Create a procedure or function in the DB .Here i'm using function.

CREATE OR REPLACE function SCOTT.getseqval return integer
is
next_val integer;
begin
select supplier_seq.nextval into next_val from dual;
return next_val;
end getseqval;
/

2.Import the function into informatica using Stored procedure transformation.

a.Select Stored Procedure transformation, import stored procedure dialog box opens.connect to the db and select the function and click ok.

3.In Expression transformation take an extra output port and write the expression  :SP.GETSEQVAL()

 

 4.Connect the ports from Exp to Target.

Hope this helps.

Please leave your comments , suggestions or Queries.