SQL OVERRIDES

Informatica Power Center Integration Service  allows us to override the default query generated by it while running a session. Power Center Integration Service generates the query when querying the relational Soures and Lookups. In this section I discuss about SQL Override in SQ.

Relational Sources:

Before override the the default query we must first understand the Default Query.
For relational sources, the Integration Service generates a query for each Source Qualifier transformation when it runs a session. The default query is a SELECT statement for each source column used in the mapping. In other words, the Integration Service reads only the columns that are connected to another transformation.

Ex: If the SQ contains 10 columns out of which only 3 are connected to next transformation then the default SQL Query generated contains only those 3 columns in it.

If any table name or column name contains a database reserved word, you can create and maintain a file,
reswords.txt, containing reserved words. When the Integration Service initializes a session, it searches for
reswords.txt in the Integration Service installation directory. If the file exists, the Integration Service places quotes around matching reserved words when it executes SQL against the database. If you override the SQL, you must enclose any reserved word in quotes.

When generating the default query, the Designer delimits table and field names containing the following characters with double quotes:
/ + - = ~ ` ! % ^ & * ( ) [ ] { } ' ; ? , < > \ | <space>

You must connect the columns in the Source Qualifier transformation to another transformation or target before you can generate the default query.

Viewing the Default Query

You can view the default query in the Source Qualifier transformation.
To view the default query:
1. From the Properties tab, select SQL Query.
2. Click Generate SQL.
3. Click Cancel to exit.

Overriding the Default Query :

You can alter or override the default query in the Source Qualifier transformation by changing the default settings of the transformation properties.

Points to be noted while overriding the default query:

1. Do not change the list of selected ports or the order in which they appear in the default query. This list must match the connected transformation output ports.

2. When you edit transformation properties (User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct) the Source Qualifier transformation includes these settings in the default query.

3. However, if you enter an SQL query, the Integration Service uses only the defined SQL statement and ignores the properties set  in the Source Qualifier transformation.

4. When you override the default SQL query, you must enclose all database reserved words in quotes.

Overriding the Default Join: 

You might need to override the default join under the following circumstances:
1.Columns do not have a primary key-foreign key relationship.
2. The datatypes of columns used for the join do not match.

3. You want to specify a different type of join, such as an outer join

When joining tables you can perform in two ways:

1. Import both the tables to be joined and drag into the mapping area:

    If foreign key -primary key relationship exists between them in database itself then a link indicating FK-PK  relation is shown in mapping.When you generate the query a default join condition is added.

2. the mqapping contains only one Source definition and you can override the default query with the join query you want.

Note:

1. Add the necessary columns in the SQ  by clicking on "create new port" icon. and name chose the data types for columns accordingly.

2. connect these new ports with the ports  of same datatype from source definition .

3. Edit the default query to reflect the new column names.(The generated query contains the column names from source to which you connected the new ports in place of new port names.)

You can see the same in below figure .

you can validate the override query by providing the parameters required in the "connect to database" section .

Hope this helps.

Rules and Guidelines for Mapplets

A mapplet is a reusable object that you create in the Mapplet Designer. It contains a set of transformations and lets you reuse the transformation logic in multiple mappings. When you use a mapplet in a mapping, you use an instance of the mapplet. Like a reusable transformation, any change made to the mapplet is inherited by all instances of the mapplet.

A mapplet has the following components:                                   

• Mapplet input   : Can be a input transformation or a source definition
• Mapplet output : Each mapplet must contain one or more Output transformations
• Mapplet ports  : consist of input ports from Input  and output Output transformations.

The following list summarizes the rules and guidelines that should be followed while working with mapplets.

•  You can connect an Input transformation to multiple transformations in a mapplet. However, you cannot connect a single port in the Input transformation to multiple transformations in the mapplet.

• An Input transformation must receive data from a single active source.

• An output group can pass data to multiple pipelines in a mapping.

• A mapplet must contain at least one Input transformation or source definition with at least one port connected to a transformation in the mapplet. 

• A mapplet must contain at least one Output transformation with at least one port connected to another transformation in the mapping. 

• You can open or iconize all the transformations in the mapplet and mapping. You cannot edit any of the properties,
  navigate to other folders, or save the repository while the mapplet is expanded.

• When a mapplet contains a source qualifier that has an override for the default SQL query, you must connect all of the source qualifier output ports to the next transformation within the mapplet.

• If the mapplet contains more than one source qualifier, use a Joiner transformation to join the output into one pipeline.

• If the mapplet contains only one source qualifier, you must connect the mapplet output ports to separate pipelines. You cannot use a Joiner transformation to join the output.

• When you edit a mapplet, you might invalidate mappings if you change the mapplet type from passive to active.

• If you delete ports in the mapplet when the mapplet is used in a mapping, you can invalidate the mapping. 

•  Do not change the datatype, precision, or scale of a mapplet port when the mapplet is used by a mapping.

• If you use a Sequence Generator transformation, you must use a reusable Sequence Generator transformation.

• If you use a Stored Procedure transformation, you must configure the Stored Procedure Type to be Normal.

• You cannot include PowerMart 3.5-style LOOKUP functions in a mapplet.

• You cannot include the following objects in a mapplet:

               - Normalizer transformations
               - Cobol sources
               - XML Source Qualifier transformations
               - XML sources
               - Target definitions
               - Pre- and post- session stored procedures
               - Other mapplets

•  Although reusable transformations and shortcuts in a mapplet can be used, to protect the validity of the mapplet, use a copy of a transformation instead. Reusable transformations and shortcuts inherit changes to their original transformations. This might invalidate the mapplet and the mappings that use the mapplet.

New Informatica 9.5 Features - Support for bigdata / Hadoop

With the increase in the volume of data, most companies are using hadoop and other big data processing software to process and analyze the data. Informatica 9.5 is designed to handle huge volume of data (big data).

Key Features of Informatica 9.5:

• Extracting data from Hadoop to deliver Big data as business reports
• Captures data from all social networking sites irrespective of their volume.
• Access and delivery of critical data using high availability of Informatica Power Exchange
• Enhanced LDAP integration, SSL remote access and support for Internet Protocol version 6 (IPv6)
• Reduces the risk of business errors with Informatica Data validation option
• Expands metadata management connectivity
• Supports push down optimization for Teradata

Data Validation Features:

• A central repository for complete data validation tests and results.
• Incremental data validation for testing the newly loaded data
• Pre-built dashboards for validation
• Increased connectivity with all systems
• Set of Pre-built operators

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.