Services Behind Scene

INTEGRATION SERVICE PROCESS
The Integration Service starts an Integration Service process to run and monitor workflows. The Integration Service process accepts requests from the Power Center Client and from pmcmd. It performs the following tasks:

• Manages workflow scheduling.
• Locks and reads the workflow.
• Reads the parameter file.
• Creates the workflow log.
• Runs workflow tasks and evaluates the conditional links connecting tasks.
• Starts the DTM process or processes to run the session.
• Writes historical run information to the repository.
• Sends post-session email in the event of a DTM failure.

LOAD BALANCER
The Load Balancer is a component of the Integration Service that dispatches tasks to achieve optimal performance and scalability. When we run a workflow, the Load Balancer dispatches the Session, Command, and predefined Event-Wait tasks within the workflow.
The Load Balancer dispatches tasks in the order it receives them. When the Load Balancer needs to dispatch more Session and Command tasks than the Integration Service can run, it places the tasks it cannot run in a queue. When nodes become available, the Load Balancer dispatches tasks from the queue in the order determined by the workflow service level.
DTM PROCESS
Informatica online training - Informatica Jobs
When the workflow reaches a session, the Integration Service process starts the DTM process. The DTM is the process associated with the session task. The DTM process performs the following tasks:

• Retrieves and validates session information from the repository.
• Performs pushdown optimization when the session is configured for pushdown optimization.
• Adds partitions to the session when the session is configured for dynamic partitioning.
• Expands the service process variables, session parameters, and mapping variables and parameters.
• Creates the session log.
• Validates source and target code pages.
• Verifies connection object permissions.
• Runs pre-session shell commands, stored procedures, and SQL.
• Sends a request to start worker DTM processes on other nodes when the session is configured to run on a grid.
• Creates and run mapping, reader, writer, and transformation threads to extract, transform, and load data.
• Runs post-session stored procedures, SQL, and shell commands.
• Sends post-session email.

PROCESSING THREADS
The DTM allocates process memory for the session and divides it into buffers. This is also known as buffer memory. The default memory allocation is 12,000,000 bytes.
The DTM uses multiple threads to process data in a session. The main DTM thread is called the master thread.
The master thread can create the following types of threads:

•  Mapping Threads: One mapping thread for each session.
•  Pre- and Post-Session Threads: One thread created.
•  Reader Threads: One thread for each partition
•  Transformation Threads: One thread for each partition
•  Writer Threads: One thread for each partition

CODE PAGES and DATA MOVEMENT
A code page contains the encoding to specify characters in a set of one or more languages. An encoding is the assignment of a number to a character in the character set.
The Integration Service can move data in either ASCII or Unicode data movement mode. These modes determine how the Integration Service handles character data.
We choose the data movement mode in the Integration Service configuration settings. If we want to move multi byte data, choose Unicode data movement mode.
ASCII Data Movement Mode: In ASCII mode, the Integration Service recognizes 7-bit ASCII and EBCDIC characters and stores each character in a single byte.
Unicode Data Movement Mode: Use Unicode data movement mode when sources or targets use 8-bit or multi byte character sets and contain character data.

Difference Between 7.1 and 8.6

1. Target from Transformation: In Informatica 8X we can create target from Transformation by dragging transformation in Target designer
2. Pushdown optimization: Uses increased performance by pushing Transformation logic to the database by analyzing the transformations and Issuing SQL statements to sources and targets. Only processes any Transformation logic that it cannot push to the database.
3. New function in expression editor: New function have been introduced in Informatica 8X like reg_extract and reg_match
4. Repository query available in both versioned and non versioned Repositories previously it was available only for versioned repository.
5. UDF (User defined function) similar to macro in excel
6. FTP: We can have partitioned FTP targets and Indirect FTP File source (with file list).
7. Propagating Port Descriptions: In Informatica 8 we can edit a port description and propagate the description to other transformations in the mapping.
8. Environment SQL Enhancements: Environment SQL can still be used to Execute an SQL statement at start of connection to the database. We can Use SQL commands that depend upon a transaction being opened during The entire read or write process. For example, the following SQL command Modifies how the session handles characters: Alter session set
NLS_DATE_FORMAT=’DD/MM/YYYY';”.
9. Concurrently write to multiple files in a session with partitioned targets.
10. Flat File Enhancements:

• Reduced conversion of data types
• Delimited file performance has improved
• Flat file now can have integer and double data types
• Data can be appended to existing flat files

Informatica power center 8 is having the following features which makes it more powerful, easy to use and manage when compared to previous versions.

• Supports Service oriented architecture
• Access to structured, unstructured and semi structured data
• Support for grid computing
• High availability
• Pushdown optimization
• Dynamic partitioning
• Metadata exchange enhancements
• Team based Development
• Global Web-based Admin console
• New transformations
• 23 New functions
• User defined functions
• Custom transformation enhancements
• Flat file enhancements
• New Data Federation option
• Enterprise GRID

Steps to install Informatica Power Center Server 8.6

Steps to install Informatica Power Center Server 8.6

 

Steps to install Informatica Power Center Server 8.6

Pre-requisites:
1)Window xp sp2 ( You can also install the same on any high end OS ).
2)At least one database(here we assume you to have Oracle XE pre installed ).
3)2 GB Ram Recommended

Informatica 8.6 Server Installation:
1) Select the sever folder from the informatica installation CD.
2)There will find set up, click on the set up file.
3)click on Next button.





4)Select the license key by using browse button.It will be available the fallowing location:…\Informatica 8.6\RAWANA
5)Click on Next button




6)Click on Next button.




7)Select complete radio button and click on Next button.
8)Leave the defaults and select next button.

9)Click on Install button

10) Choose create new domain radio button, and click on Next button.

• Select database type
• Database URL: ComputerName:portno

Eg:windows-4dc4631:1521

• To find the computer name.. Click on Start->Right click on My computer->Properties->Computer Name.
• Provide the database credentials along with the Service name.
• Find database service name:
• Control Panel–Administrative tools–Services

• Here XE is the service name.
• For Oracle 10g users you will find the service name like :OracleServiceORCL then service name would be ORCL.

Note:SQL Server users they can give the name of the server name in the above field instead of service name.

• Click on Test connection:If this succeed then it will take you to the further step.

11. Leave the defaults, enter password and confirm password and click on next button.

12.Uncheck the check box and click on next button.

13. Click on Done.
14.After finishing installation need to run the services
Run the Informatica services:
windows+r->services.msc-> Locate the Informatica services –> Start them.

Configure the repository service and Integration service:

• To create these two above, please follow the below steps.

• Give your Credentials

• Select administrative Console. Make sure Domain is running.

• Make sure node is also running.From create drop down select repository service.

1)Enter any service name.
2)Select License.
3)Select database type as oracle (if your sys contains oracle db)
4)Select node for which you want to create repository.
5)Leave the code page as default.
6)Connection string if ORCL for oracle users(Make sure connection string should be same as the service name what is given while installing server)
7)Enter Database Credentials
8)Leave other defaults as well.
9)Click on create button.
10)Enable the repository service.

• Now Create content for the repository by selecting drop down shown below.

Note:Make sure Processes are enabled and repository service is enabled.
Creation of Integration service:


1)Give any name for Integration sevice.
2)Assign node to the IS.
3)Assign Repository to the IS.
4)Enter the rep.username and password.
Note:make this username and password should same as username and password of the Repository service.
5.Select data movement mode as ASCII.

In the process tab of the IS, enable the process.
Before enable, edit this section and select “Code page” as shown above.
Now Install client.
Open Informatica Repository Manager.


Select the RS from the RHS pane and click on OK.


Connect to the repository service.

Enter the repository username and password.

Generate rows based on a column value - Informatica

Q) How to generate or load values in to the target table based on a column value using informatica etl tool.

I have the products table as the source and the data of the products table is shown below.

Table Name: Products
Product  Quantity
-----------------
Samsung  NULL
Iphone   3
LG       0
Nokia    4

Now i want to duplicate or repeat each product in the source table as many times as the value in the quantity column. The output is

product  Quantity
----------------
Iphone   3
Iphone   3
Iphone   3
Nokia    4
Nokia    4
Nokia    4
Nokia    4

The Samsung and LG products should not be loaded as their quantity is NULL, 0 respectively.

Now create informatica workflow to load the data in to the target table?
Solution:
Informatica online training - Informatica Jobs
Follow the below steps

• Create a new mapping in the mapping designer
• Drag the source definition in to the mapping
• Create the java transformation in active mode
• Drag the ports of source qualifier transformation in to the java transformation.
• Now edit the java transformation by double clicking on the title bar of the java transformation and go to the "Java Code" tab.
• Enter the below java code in the "Java Code" tab.

if (!isNull("quantity"))
{
  double cnt = quantity;
  for (int i = 1; i <= quantity; i++)
  {
    product = product;
    quantity = quantity;
    generateRow();
  }
}

• Now compile the java code. The compile button is shown in red circle in the image.
• Connect the ports of the java transformation to the target.
• Save the mapping, create a workflow and run the workflow.

Informatica Scenario Based Interview Questions with Answers

Informatica Scenarios

I have listed the following informatica scenarios which are frequently asked in the informatica interviews. These informatica scenario interview questions helps you a lot in gaining confidence in interviews.
1. How to generate sequence numbers using expression transformation?
Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign the variable port to an output port. In the expression transformation, the ports are:
V_count=V_count+1
O_count=V_count
2. Design a mapping to load the first 3 rows from a flat file into a target?
Solution:
You have to assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3
3. Design a mapping to load the last 3 rows from a flat file into a target?
Solution:
Consider the source has the following data.
col
a
b
c
d
e
Step1: You have to assign row numbers to each record. Generate the row numbers using the expression transformation as mentioned above and call the row number generated port as O_count. Create a DUMMY output port in the same expression transformation and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
In the expression transformation, the ports are
V_count=V_count+1
O_count=V_count
O_dummy=1
The output of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b, 2, 1
c, 3, 1
d, 4, 1
e, 5, 1
Step2: Pass the output of expression transformation to aggregator and do not specify any group by condition. Create an output port O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row by default. The output of aggregator contains the DUMMY port which has value 1 and O_total_records port which has the value of total number of records in the source.
In the aggregator transformation, the ports are
O_dummy
O_count
O_total_records=O_count
The output of aggregator transformation will be
O_total_records, O_dummy
5, 1
Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
In the joiner transformation, the join condition will be
O_dummy (port from aggregator transformation) = O_dummy (port from expression transformation)
The output of joiner transformation will be
col, o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5, 5
Step4: Now pass the ouput of joiner transformation to filter transformation and specify the filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2
In the filter transformation, the filter condition will be
O_total_records – O_count <=2
The output of filter transformation will be
col o_count, o_total_records
c, 3, 5
d, 4, 5
e, 5, 5
4. Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?
Solution:
This is similar to the above problem; the first 3 steps are same. In the last step instead of using the filter transformation, you have to use router transformation. In the router transformation create two output groups.
In the first group, the condition should be O_count=1 and connect the corresponding output group to table A. In the second group, the condition should be O_count=O_total_records and connect the corresponding output group to table B. The output of default group should be connected to table C.
5. Consider the following products data which contain duplicate rows.
A
B
C
C
B
D
B
Q1. Design a mapping to load all unique products in one table and the duplicate rows in another table.
The first table should contain the following output
A
D
The second target should contain the following output
B
B
B
C
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a dummy port O_dummy and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
The output of expression transformation will be
Product, O_dummy
A, 1
B, 1
B, 1
B, 1
C, 1
C, 1
D, 1
Pass the output of expression transformation to an aggregator transformation. Check the group by on product port. In the aggreagtor, create an output port O_count_of_each_product and write an expression count(product).
The output of aggregator will be
Product, O_count_of_each_product
A, 1
B, 3
C, 2
D, 1
Now pass the output of expression transformation, aggregator transformation to joiner transformation and join on the products port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
The output of joiner will be
product, O_dummy, O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1, 2
C, 1, 2
D, 1, 1
Now pass the output of joiner to a router transformation, create one group and specify the group condition as O_dummy=O_count_of_each_product. Then connect this group to one table. Connect the output of default group to another table.
Q2. Design a mapping to load each product once into one table and the remaining products which are duplicated into another table.
The first table should contain the following output
A
B
C
D
The second table should contain the following output
B
B
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a variable port,V_curr_product, and assign product port to it. Then create a V_count port and in the expression editor write IIF(V_curr_product=V_prev_product, V_count+1,1). Create one more variable port V_prev_port and assign product port to it. Now create an output port O_count port and assign V_count port to it.
In the expression transformation, the ports are
Product
V_curr_product=product
V_count=IIF(V_curr_product=V_prev_product,V_count+1,1)
V_prev_product=product
O_count=V_count
The output of expression transformation will be
Product, O_count
A, 1
B, 1
B, 2
B, 3
C, 1
C, 2
D, 1
Now Pass the output of expression transformation to a router transformation, create one group and specify the condition as O_count=1. Then connect this group to one table. Connect the output of default group to another table

What is Data warehousing Architecture..?

Data warehousing includes two Phases 1) ETL 2) Reporting
The following Image shows both Phases….



The following Diagram shows a Detailed ETL PHASE….



We have data sources ODS1, ODS2, ODS3 and so on… These contain Operational Data or Real time data… These systems may locate in various places across geographical regions..
We extract the data from these data sources and we keep the data in Staging area for a while and we apply Data Transformations such as Data formatting, Data Cleansing, and Data aggregations. Then we load the transformed data from Staging area into Data warehouse,

A Data warehouse is just a Database contains Fact tables and Dimension Tables to form Star Schema..

What is Informatica and its Basic Interview Questions

Introduction to Informatica

Informatica is a tool, supporting all the steps of Extraction, Transformation and Load process. Now a days Informatica is also being used as an Integration tool.
Informatica is an easy to use tool. It has got a simple visual interface like forms in visual basic. You just need to drag and drop different objects (known as transformations) and design process flow for Data extraction transformation and load. These process flow diagrams are known as mappings. Once a mapping is made, it can be scheduled to run as and when required. In the background Informatica server takes care of fetching data from source, transforming it, & loading it to the target systems/databases.
Important Components of Informatica are:
Power Exchange
Power Centre
Power Centre Connect
Power Channel
Metadata Exchange
Power Analyser
Super Glue
Components of Informatica Client
Repository Manager : Manage the Informatica repository from the client machine
Designer: The Designer helps you create source definitions, target definitions, and
transformations to build a mapping
Workflow Manager : Allows user to execute a mapping created in the designer
Workflow Monitor : Tool that allows you to monitor workflows and tasks

                                                  Basic Interview Questions On Informatica:

What is Data Integration?
Data Integration is the process of combining data residing at different sources and providing the user with a unified view of these data.
Explain PowerCenter Repository?
Repository consist of database tables that store metadata. Metadata describes different types of objects , such as mappings or transformations , that you can create using PowerCenter Client tools. The interation service uses repository objects to extract , transform and load data. The repository also stores administrative information such as user names, passwords , permissions and previleges. When any task is performed through PowerCenter Client application such as creating users, analyzing sources , developing mapping or mapplets or creating workflows , Metadata is added to repository tables.
What is a Mapping?
A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation. Mappings represent the data flow between sources and targets. When the Integration Service runs a session, it uses the instructions configured in the mapping to read, transform, and write data.
What is a mapplet?
A mapplet is a reusable object that contains a set of transformations and enables to reuse that transformation logic in multiple mappings.
While importing the relational source definition from database, what are the metadata of source you import?
Source name
Database location
Column names
Data types
Key constraints
How many ways you can update a relational source definition and what are they?
Two ways
1. Edit the definition
2. Re import the definition
Where should U place the flat file to import the flat file definition to the designer?
Place it in local folder
To provide support for Mainframes source data, which files r used as a source definitions?
COBOL files
Which transformation should u need while using the COBOL sources as source definitions?
Normalizer transformation is used to normalize the data. Since COBOL sources r often consists of Demoralized data.

Flat file header row, footer row and detail rows to multiple tables

Assume that we have a flat file with header row, footer row and detail rows. Now Lets see how to load header row into one table, footer row into other table and detail rows into another table just by using the transformations only.
First pass the data from source qualifier to an expression transformation. In the expression transformation assign unique number to each row (assume exp_count port). After that pass the data from expression to aggregator. In the aggregator transformation don’t check any group by port. So that the aggregator will provide last row as the default output (assume agg_count port).
Now pass the data from expression and aggregator to joiner transformation. In the joiner select the ports from aggregator as master and the ports from expression as details. Give the join condition on the count ports and select the join type as master outer join. Pass the joiner output to a router transformation and create two groups in the router. For the first group give the condtion as exp_count=1, which gives header row. For the second group give the condition as exp_count=agg_count, which gives the footer row. The default group will give the detail rows.

Normalizer Transformation Error – Informatica

Normalizer transformation is used to convert the data in multiple columns into different rows. Basically the normalizer transformation converts the denormalized data in a table in to a normalized table.
Normalizer Transformation Error
Getting the following Error for the Normalizer transformation in mapping when pivoting the columns in to Rows

TT_11054 Normalizer Transformation: Initialization Error: [Cannot match OFOSid with IFOTid.]

How to fix the Normalizer Transformation Error?
Solution:
Follow the below steps to avoid this error.

1. There should be no unconnected input ports to the Normalizer transformation.
2. If the Normalizer has an OCCURS in it, make sure number of input ports matches the number of OCCURS.

Creating a Non Reusable Object from Reusable Object

Q) How to create a non-reusable transformation or session or task from a reusable transformation or session or task?
I still remember my first project in which i created so many reusable transformations and developed a mapping. My project lead reviewed the code and told me that you created unnecessary reusable transformation change them to non reusable transformations. I created non reusable transformations and re-implemented the entire logic. It took almost one day for me to implement the code. Still so many new informatica developers will do the same mistake and re implement the entire logic.
I found an easy way to create a non-reusable transformation from a reusable transformation. Follow the below steps to create a non-reusable transformation or session or task from a reusable transformation or session or task in informatica is

1. Go to the Navigator which is on the left side.
2. Select the reusable transformation or session or task which you want to convert to non resuable with the mouse.
3. Drag the object (transformation/session/task) to the work-space and just before leaving the object on the work-space hold the ctrl key and then release the object.

Now you are done with creating a non-reusable transformation or session or task.

Informatica Performance Improvement Tips

Following standards/guidelines can improve the overall performance:

• Use Source Qualifier if the Source tables reside in the same schema.
• Make use of Source Qualifer  “Filter” Properties if the Source type is Relational.
• If the subsequent sessions are doing lookup on the same table, use persistent cache in the first session. Data remains in the Cache and available for the subsequent session for usage.
• Use flags as integer, as the integer comparison is faster than the string comparison.
• Use tables with lesser number of records as master table for joins.
• While reading from Flat files, define the appropriate data type instead of reading as String and converting.
• Have all Ports that are required connected to Subsequent Transformations else check whether we can remove these ports.
• Suppress ORDER BY using the ‘–’ at the end of the query in Lookup Transformations.
• Minimize the number of Update strategies.
• Group by simple columns in transformations like Aggregate, Source Qualifier.
• Use Router transformation in place of multiple Filter transformations.
• Turn off the Verbose Logging while moving the workflows to Production environment.
• For large volume of data drop index before loading and recreate indexes after load.
• For large of volume of records Use Bulk load Increase the commit interval to a higher value large volume of data.
• Set ‘Commit on Target’ in the sessions.

Case Converter Transformation in informatica

The Case converter transformation is a passive transformation used to format the data to similar character formats. Case converter transformation is used to maintain data quality.
The predefined case conversion types are uppercase, lowercase, toggle case, title case and sentence case.
Reference tables can also be used to control the case conversion. Use the “Valid” column in the reference table to change the case of input strings. Use reference tables only when the case conversion type is “title case or sentence case”.
Case Strategy Properties:
You can create multiple case conversion strategies. Each strategy uses a single conversion type. Configure the following properties on the strategies view in the case converter transformation:
Reference Tables: used to apply the capitalization format specified by a reference table. Reference tables work only if the case conversion option is title case or sentence case. If a reference table match occurs at the start of a string, the next character in that string changes to uppercase. For example, if the input string is vieditor and the reference table has an entry for Vi, the output string is ViEditor.
Conversion Types: The conversion types are uppercase, lowercase, toggle case, title case and sentence case. The default conversion type is uppercase.
Leave uppercase words unchanged: Overrides the chosen capitalization for uppercase strings.
Delimiters: Specifies how capitalization functions work for title case conversion. For example, choose a colon as a delimiter to transform “james:bond” to “James:Bond”. The default delimiter is the space character.

SQL Transformation in Informatica

SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.
The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor. You can also pass the database connection information to the SQL transformation as an input data at run time.
The following SQL statements can be used in the SQL transformation.

• Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
• DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)
• DATA Retrieval Statement (SELECT)
• DATA Control Language Statements (GRANT, REVOKE)
• Transaction Control Statements (COMMIT, ROLLBACK)

Configuring SQL Transformation

The following options can be used to configure an SQL transformation

• Mode: SQL transformation runs either in script mode or query mode.
• Active/Passive: By default, SQL transformation is an active transformation. You can configure it as passive transformation.
• Database Type: The type of database that the SQL transformation connects to.
• Connection type: You can pass database connection information or you can use a connection object.

We will see how to create an SQL transformation in script mode, query mode and passing the dynamic database connection with examples.

Creating SQL Transformation in Query Mode

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.
You can create the following types of SQL queries
Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows.
Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query.

SQL Transformation Example Using Static SQL query

Q1) Let’s say we have the products and Sales table with the below data.

Table Name: Products
PRODUCT 
-------
SAMSUNG
LG
IPhone

Table Name: Sales
PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
LG      3        80
IPhone  5        200
SAMSUNG 5        500

Create a mapping to join the products and sales table on product column using the SQL Transformation? The output will be

PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
SAMSUNG 5        500
IPhone  5        200
LG      3        80

Solution:
Just follow the below steps for creating the SQL transformation to solve the example

• Create a new mapping, drag the products source definition to the mapping.
• Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a name and then click create.
• Select the execution mode as query mode, DB type as Oracle, connection type as static. This is shown in the below image.Then click OK.

• Edit the sql transformation, go to the “SQL Ports” tab and add the input and output ports as shown in the below image. Here for all the ports, you have to define Data Type (informatica specific data types) and Native Type (Database specific data types).

• In the same “SQL Ports” Tab, go to the SQL query and enter the below sql in the SQL editor.

select product, quantity, price from sales where product = ?product?

• Here ?product? is the parameter binding variable which takes its values from the input port. Now connect the source qualifier transformation ports to the input ports of SQL transformation and target input ports to the SQL transformation output ports. The complete mapping flow is shown below.

• Create the workflow, session and enter the connections for source, target. For SQL transformation also enter the source connection.

After you run the workflow, the integration service generates the following queries for sql transformation

select product, quantity, price from sales where product ='SAMSUNG'
select product, quantity, price from sales where product ='LG'
select product, quantity, price from sales where product ='IPhone'

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.
SQL Transformation Example Using Full Dynamic query
Q2) I have the below source table which contains the below data.

Table Name: Del_Tab
Del_statement
------------------------------------------
Delete FROM Sales WHERE Product = 'LG'
Delete FROM products WHERE Product = 'LG'

Solution:
Just follow the same steps for creating the sql transformation in the example 1.

• Now go to the “SQL Ports” tab of SQL transformation and create the input port as “Query_Port”. Connect this input port to the Source Qualifier Transformation.
• In the “SQL Ports” tab, enter the sql query as ~Query_Port~. The tilt indicates a variable substitution for the queries.
• As we don’t need any output, just connect the SQLError port to the target.
• Now create workflow and run the workflow.

SQL Transformation Example Using Partial Dynamic query
Q3) In the example 2, you can see the delete statements are similar except Athe table name. Now we will pass only the table name to the sql transformation. The source table contains the below data.

Table Name: Del_Tab
Tab_Names
----------
sales
products

Solution:
Create the input port in the sql transformation as Table_Name and enter the below query in the SQL Query window.

Delete FROM ~Table_Name WHERE Product = 'LG'

Informatica Scenario Based Interview Questions with Answers

Informatica Scenarios

I have listed the following informatica scenarios which are frequently asked in the informatica interviews. These informatica scenario interview questions helps you a lot in gaining confidence in interviews.
1. How to generate sequence numbers using expression transformation?
Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign the variable port to an output port. In the expression transformation, the ports are:
V_count=V_count+1
O_count=V_count
2. Design a mapping to load the first 3 rows from a flat file into a target?
Solution:
You have to assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3
3. Design a mapping to load the last 3 rows from a flat file into a target?
Solution:
Consider the source has the following data.
col
a
b
c
d
e
Step1: You have to assign row numbers to each record. Generate the row numbers using the expression transformation as mentioned above and call the row number generated port as O_count. Create a DUMMY output port in the same expression transformation and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
In the expression transformation, the ports are
V_count=V_count+1
O_count=V_count
O_dummy=1
The output of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b, 2, 1
c, 3, 1
d, 4, 1
e, 5, 1
Step2: Pass the output of expression transformation to aggregator and do not specify any group by condition. Create an output port O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row by default. The output of aggregator contains the DUMMY port which has value 1 and O_total_records port which has the value of total number of records in the source.
In the aggregator transformation, the ports are
O_dummy
O_count
O_total_records=O_count
The output of aggregator transformation will be
O_total_records, O_dummy
5, 1
Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
In the joiner transformation, the join condition will be
O_dummy (port from aggregator transformation) = O_dummy (port from expression transformation)
The output of joiner transformation will be
col, o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5, 5
Step4: Now pass the ouput of joiner transformation to filter transformation and specify the filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2
In the filter transformation, the filter condition will be
O_total_records – O_count <=2
The output of filter transformation will be
col o_count, o_total_records
c, 3, 5
d, 4, 5
e, 5, 5
4. Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?
Solution:
This is similar to the above problem; the first 3 steps are same. In the last step instead of using the filter transformation, you have to use router transformation. In the router transformation create two output groups.
In the first group, the condition should be O_count=1 and connect the corresponding output group to table A. In the second group, the condition should be O_count=O_total_records and connect the corresponding output group to table B. The output of default group should be connected to table C.
5. Consider the following products data which contain duplicate rows.
A
B
C
C
B
D
B
Q1. Design a mapping to load all unique products in one table and the duplicate rows in another table.
The first table should contain the following output
A
D
The second target should contain the following output
B
B
B
C
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a dummy port O_dummy and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
The output of expression transformation will be
Product, O_dummy
A, 1
B, 1
B, 1
B, 1
C, 1
C, 1
D, 1
Pass the output of expression transformation to an aggregator transformation. Check the group by on product port. In the aggreagtor, create an output port O_count_of_each_product and write an expression count(product).
The output of aggregator will be
Product, O_count_of_each_product
A, 1
B, 3
C, 2
D, 1
Now pass the output of expression transformation, aggregator transformation to joiner transformation and join on the products port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
The output of joiner will be
product, O_dummy, O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1, 2
C, 1, 2
D, 1, 1
Now pass the output of joiner to a router transformation, create one group and specify the group condition as O_dummy=O_count_of_each_product. Then connect this group to one table. Connect the output of default group to another table.
Q2. Design a mapping to load each product once into one table and the remaining products which are duplicated into another table.
The first table should contain the following output
A
B
C
D
The second table should contain the following output
B
B
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a variable port,V_curr_product, and assign product port to it. Then create a V_count port and in the expression editor write IIF(V_curr_product=V_prev_product, V_count+1,1). Create one more variable port V_prev_port and assign product port to it. Now create an output port O_count port and assign V_count port to it.
In the expression transformation, the ports are
Product
V_curr_product=product
V_count=IIF(V_curr_product=V_prev_product,V_count+1,1)
V_prev_product=product
O_count=V_count
The output of expression transformation will be
Product, O_count
A, 1
B, 1
B, 2
B, 3
C, 1
C, 2
D, 1
Now Pass the output of expression transformation to a router transformation, create one group and specify the condition as O_count=1. Then connect this group to one table. Connect the output of default group to another table

Informatica Upgrade Process:

Informatica Upgrade Process:

Stages across upgrade can be categorized as below:

1. Upgrading the domain and server file: run the Informatica server installer and select the upgrade option. The domain upgrade wizard installs the server files and configures the domain. If the domain has multiple nodes, you must upgrade on all the nodes.

The following table describes the actions that the installer performs when you upgrade Informatica:

Tasks	Description
1. Installs Informatica.	Installs Informatica directories and files into the new Directory.
2. Copies infa_shared directory.	Copies the contents of the infa_shared directory from the existing installation directory into the new installation Directory.
3. Copies mm_files directory.	Copies the contents of the mm_files directory from the default location in the existing installation directory into the New installation directory.
4. Upgrades the domain.	Upgrades the domain to run version 9.0.1 application Services. The upgrade retains the user and administrator accounts in The domain.
5. Starts Informatica Services.	Starts Informatica Services on the node.

2.Upgrading the application services: After you upgrade the domain and server files, log in to the Administrator Tool and upgrade the application services. The service upgrade wizard provides a list of all application that must be upgraded. It upgrades the services based on the order required by the dependent objects.
3.Upgrading the Informatica client: To upgrade the Informatica client, run the Informatica client installer and Select the upgrade option

Pre-Upgrade Tasks
Before you upgrade the domain and server files, complete the following tasks:
1. Review the prerequisites.
2. Verify the file descriptor settings.
3. Verify the configuration of the environment variables used by the installer.
4. Clear the configuration of environment variables that pertain to previous installations of Informatica.
5. Prepare the domain.
6. Prepare the Power Center repository.
7. Prepare the Power Center Profiling warehouse.
8. Prepare for upgrade from Power Center 8.6.1

• Export Reference Table Manager Data.
• Prepare Metadata Manager.
• Prepare the Data Analyzer repository.

9. Shut down the domain.

Upgrading the Domain and Server in Graphical Mode:
You can upgrade the Informatica domain and server files in graphical mode on Windows or UNIX.
1. Verify that your environment meets the minimum system requirements and complete the pre-upgrade tasks.
2. Log in to the machine with the same user account that you used to install the previous version.
3. Close all other applications.
4. To begin the upgrade on Windows, run install.bat from the root directory.
To begin the upgrade on UNIX, use a shell command line to run install.sh from the root directory, and then Select the option for graphical mode installation.
5. In the Installation Type window, select Upgrade to Informatica 9.0.1 and click next.

• The Upgrade Pre-Requisites window displays the upgrade system requirements. Verify that all requirements are met before you continue the upgrade.

6. Click Next.
7. In the Upgrade Directory window, enter the following directories

Directory	Description
1.Directory of the Informatica Product to upgrade.	Directory that contains the previous version of Power Center that you want to upgrade.
2.Directory for Informatica 9.0.1	Directory in which to install Informatica 9.0.1. Enter the absolute path for the installation directory. The directory cannot be the same as the directory that contains the previous version of Power Center. The directory names in the path must not contain spaces or the following special characters: @|* $ # ! % ( ) { } [ ] , ; ' On Windows, the installation directory must be on the current machine.

Click Next.
The upgrade wizard displays a warning to shut down the Informatica domain before you continue the upgrade.
9. Click OK.
10. In the Pre-Installation Summary window, review the upgrade information, and click Install to continue.
The upgrade wizard installs the Informatica server files to the Informatica 9.0.1 installation directory.
11. In the Domain Configuration Upgrade window, the upgrade wizard displays the database and user account information for the domain configuration repository to be upgraded.

Property	Description
Database type	Database for the domain configuration repository.
Database user ID	Database user account for the domain configuration repository.
User password	Password for the database user account.
Tablespace	Displayed for IBM DB2 only. Name of the tablespace for the upgraded domain configuration repository tables. If the database of the domain configuration repository that you are upgrading does not use a 32 K tablespace, this property is blank. Enter the name of a tablespace with a page size of 32 K. In a single-partition database, if you do not specify a tablespace name, the installer writes the upgraded tables in the default tablespace. The default tablespace must be 32 K. In a multi-partition database, you must specify a 32 K tablespace.

The upgrade wizard displays the database connection string for the domain configuration repository based on how the connection string of the previous version was created at installation:

• If the previous version used a JDBC URL at installation, the upgrade wizard displays the JDBC connection properties, including the database address and service name.
• If the previous version used a custom JDBC connection string at installation, the upgrade wizard displays the custom connection string.
• Optionally, you can specify additional JDBC parameters to include in the connection string. To provide Additional JDBC parameters, select JDBC parameters and enter a valid JDBC parameter string.

12. Click Test Connection to verify that you can connect to the database, and then click OK to continue.
13. Click Next.
On the Port Configuration Upgrade window, the upgrade wizard displays the default port numbers assigned to the domain and node components.
14. You can specify new port numbers or use the default port numbers.
The following table describes the ports that you can specify:

Port	Description
Service Manager port	Port number used by the Service Manager in the node. Client applications and the Informatica command line programs use this port to communicate to the services in the domain.
Informatica Administrator port	Port number used by the Administrator tool. Available if you upgrade a gateway node.
Informatica Administrator shutdown port	Port number used by the Administrator tool to listen for shut down commands. Available if you upgrade a gateway node.

15. Click Next.
On Windows, the upgrade wizard creates a service to start Informatica. By default, the service runs under the same user account as the account used for installation. You can run the Windows service under a different User account.
16. Select whether to run the Windows service under a different user account.
The following table describes the properties that you set:

Property	Description
Run Informatica under a different user account	Indicates whether to run the Windows service under a different user account.
User name	User account with which to run the Informatica Windows service. Use the following format: DomainName\UserAccount This user account must have the Act as operating system permission.
Password	Password for the user account with which to run the Informatica Windows service.

17. Click Next.
The Post-Upgrade Summary window indicates whether the upgrade completed successfully.
18. Click Done.

Upgrade the Application Services: 

1. Configure Informatica Environment Variables
You can configure the INFA_JAVA_OPTS, INFA_DOMAINS_FILE, and INFA_HOME environment variables to store memory, domain, and location settings.
(i) INFA_JAVA_OPTS
For example, to configure 1 GB of system memory for the Informatica daemon on UNIX in a C shells.
setenv INFA_JAVA_OPTS “-Xmx1024m”
(ii) INFA_DOMAINS_FILE
Set the value of the INFA_DOMAINS_FILE variable to the path and file name of the domains.infa file. If you configure the INFA_DOMAINS_FILE variable, you can run infacmd and pmcmd from a directory other than /server/bin.
Configure the INFA_DOMAINS_FILE variable on the machine where you install the Informatica services. On Windows, configureINFA_DOMAINS_FILE as a system variable.
(iii) INFA_HOME
Use a softlink in UNIX for any of the Informatica directories. To configure INFA_HOME so that any Informatica application or service can locate the other Informatica components it needs to run, set INFA_HOME to the location of the Informatica installation directory.
2. Configure Locale Environment Variables
Use the following command to verify that the value for the locale environment variable is compatible with the Language settings for the machine and the type of code page you want to use for the repository:
Eg : locale –a
Locale for Oracle Database Clients
if the value is american_america.UTF8, set the variable in a C shell with the following command:
setenv NLS_LANG american_america.UTF8

Service Upgrade:

Use the service upgrade wizard to upgrade services.
1. In the Informatica Administrator header area click Manage > Upgrade.
2. Select the objects to upgrade.
3. Click Next.
4. If dependency errors exist, the Dependency Errors dialog box appears. Review the dependency errors and click OK. Then, resolve dependency errors and click next.
5. Enter the repository login information. Optionally, choose to use the same login information for all Repositories.
6. Click Next.
The service upgrade wizard upgrades each service and displays the status and processing details.
7. When the upgrade completes, the Summary section displays the list of services and their upgrade status.
Click each service to view the upgrade details in the Service Details section.
8. Optionally, click Save Report to save the upgrade details to a file.
If you choose not to save the report, you can click Save Previous Report the next time you launch the Service upgrade wizard.
9. Click Close.
10. Restart upgraded services.
After you upgrade the PowerCenter Repository Service, you must restart the service and its dependent Services.

Informatica Client Upgrade

1. Close all applications.
2. Run install.bat from the root directory.
The Upgrade Pre-Requisites window displays the system requirements. Verify that all installation Requirements are met before you continue the installation.
3. Click Next.
On the Select Component window, select the Informatica client you want to upgrade.
You can upgrade the following Informatica client applications:

• Informatica Developer
• PowerCenter Client

If both Informatica Developer and PowerCenter Client are installed on the machine, you can upgrade the tools in the same process.
4. On the Upgrade Directory window, enter the following directories:

Directory	Description
Directory of the Informatica client to upgrade	Directory that contains the previous version of the Informatica client tool that you want to upgrade
Directory for Informatica 9.0.1 client tools	Directory in which to install the Informatica 9.0.1 client tools Enter the absolute path for the installation directory. The installation directory must be on the current machine. The directory names in the path must not contain spaces or the following special characters: @|* $ # ! % ( ) { } [ ] , ; '

5. Click Next.
6. On the Pre-Installation Summary window, review the installation information, and click Install.
The installer copies the Informatica client files to the installation directory.
The Post-installation Summary window indicates whether the upgrade completed successfully.
7. Click Done.

Post Upgrade Tasks: 

Informatica Domain

• Configure LDAP Connectivity.
• Update the Log Events Directory.
• Update ODBC Data Sources.
• Update Statistics for the Domain Configuration Repository.
• View Log Events from the Previous Informatica Version.

Metadata Manager Service

• Reload Metadata Manager Resources
• Update the Metadata Manager Properties File
• Reference Table Manager

For detailed study on version Upgrade, you may go through Documents from informatica corporation on upgrade.