1. What is an update strategy transformation? Update strategy transformation is used to flag source rows for insert, update, delete or reject within a mapping. Based on this flagging each row will be either inserted or updated or deleted from the target. Alternatively the row can be rejected. 2. Why update strategy is an active transformation? As update strategy transformation can reject rows, it is called as an active transformation. 3. What are the constants used in update strategy transformation for flagging the rows?
- DD_INSERT is used for inserting the rows. The numeric value is 0.
- DD_UPDATE is used for updating the rows. The numeric value is 1.
- DD_DELETE is used for deleting the rows. The numeric value is 2.
- DD_REJECT is used for rejecting the rows. The numeric value is 3.
INFORMATICA PROBLEMS WITH SOLUTIONS - PART 1
1. In this problem we will see how to implement the not equal operator, greater than, greater than or equal to, less than and less than or equal to operators when joining two tables in informatica. Consider the below sales table as an example? Table name: Sales
product, prod_quantity, price , Year A , 10 , 100 , 2010 B , 15 , 150 , 2010 A , 8 , 80 , 2011 B , 26 , 260 , 2011Now the problem is to identify the products whose sales is less than in the current year (In this example: 2011) when compared to the last year. Here in this example, Product A sold less in 2011 when compared with the sales in 2010. This problem can be easily implemented with the help of SQL query as shown below
SELECT cy.* FROM SALES cy, SALES py WHERE cy.product = py.product AND cy.year=2011 AND py.year=2010 AND cy.prod_quantity < py.prod_quantity;In informatica, you can specify only equal to condition in joiner. Now we will see how to implement this problem using informatica. Solution: STEP1: Connect two source qualifier transformations to the source definition. Call the first source qualifier transformation as sq_cy (cy means current year) and the other as sq_py (py means previous year). STEP2: In the sq_cy source qualifier transformation, specify the source filter as price=2011. In the sq_py, specify the source filter as price=2010 STEP3: Now connect these two source qualifier transformations to joiner transformation and make sq_cy as master, sq_py as detail. In the join condition, select the product port from master and detail. STEP4: Now connect all the master ports and only the prod_quantity port from detail to the filter transformation. In the filter transformation specify the filter condition as prod_quantity < prod_quantity1. Here pord_quantity port is from master port and prod_quantity1 is from detail port. STEP4: Connect all the ports except the prod_quantity1 of filter transformation to the target definition. 2. How to implement the not exists operator in informatica which is available in database? Solution: Implementing the Not Exists operator is very easy in informatica. For example, we want to get only the records which are available in table A and not in table B. For this use a joiner transformation with A as master and B as detail. Specify the join condition and in the join type, select detail outer join. This will get all the records from A table and only the matching records from B table. Connect the joiner to a filter transformation and specify the filter condition as B_port is NULL. This will give the records which are in A and not in B. Then connect the filter to the target definition.
REVERSE THE CONTENTS OF FLAT FILE – INFORMATICA
Q1) I have a flat file, want to reverse the contents of the flat file which means the first record should come as last record and last record should come as first record and load into the target file.As an example consider the source flat file data as
Informatica Enterprise SolutionInformatica Power centerInformatica Power exchangeInformatica Data quality
The target flat file data should look as
Informatica Data qualityInformatica Power exchangeInformatica Power centerInformatica Enterprise Solution
Solution :
Follow the below steps for creating the mapping logic
- Create a new mapping.
- Drag the flat file source into the mapping.
- Create an expression transformation and drag the ports of source qualifier transformation into the expression transformation.
- Create the below additional ports in the expression transformation and assign the corresponding expressions
Variable port: v_count = v_count+1Output port o_count = v_count
- Now create a sorter transformation and drag the ports of expression transformation into it.
- In the sorter transformation specify the sort key as o_count and sort order as DESCENDING.
- Drag the target definition into the mapping and connect the ports of sorter transformation to the target.
Q2 ) Load the header record of the flat file into first target, footer record into second target and the remaining records into the third target.
The solution to this problem I have already posted by using aggregator and joiner. Now we will see how to implement this by reversing the contents of the file.
Solution :
- Connect the source qualifier transformation to the expression transformation. In the expression transformation create the additional ports as mentioned above.
- Connect the expression transformation to a router. In the router transformation create an output group and specify the group condition as o_count=1. Connect this output group to a target and the default group to sorter transformation.
- Sort the data in descending order on o_count port.
- Connect the output of sorter transformation to expression transformation (don’t connect o_count port).
- Again in the expression transformation create the same additional ports mentioned above.
- Connect this expression transformation to router and create an output group. In the output group specify the condition as o_count=1 and connect this group to second target. Connect the default group to the third group.
DIFFERENCE BETWEEN STOP AND ABORT IN INFORMATICA
You can stop or abort running workflow by one of the following ways:
When you stop, the integration service first tries to stop processing the task. The integration service does not process other tasks that are in sequence. However it process the tasks that are in parallel to the task on which the stop or abort command is issued. If the Integration Service cannot stop the task, you can try to abort the task. When you abort a task, the Integration Service kills the process on the task. Stopping or Aborting a Session Task: When you issue a stop command on a session, the integration service first stops reading the data from the sources. It continues processing and writing data to the targets and then commits the data. Abort command is handled the same way as the stop command, except that the abort command has timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session. Difference Between Stop and Abort: When you run a session, it holds memory blocks in the OS. When issue a abort on the session, it kills the threads and leaves the memory blocks. This causes memory issues in the server and leads to poor performance. Some operating systems clean the lost memory blocks automatically. However most of the operating systems do not clean up these memory blocks. Stop is clean way of killing the sessions and cleans up the memory blocks. - Issuing stop of abort in the informatica workflow monitor
- Issuing stop of abort command in pmcmd.
- specifying in the control task.
PMCMD COMMAND USAGE IN INFORMATICA
Informatica provides four built-in command line programs or utilities to interact with the informatica features. They are:
- infacmd
- infasetup
- pmcmd
- pmrep
- Start workflows.
- Start workflow from a specific task.
- Stop, Abort workflows and Sessions.
- Schedule the workflows.
pmcmd scheduleworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-nameYou cannot specify the scheduling options here. This command just schedules the workflow for the next run. 2. Start workflow The following pmcmd command starts the specified workflow:
pmcmd startworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name3. Stop workflow Pmcmd command to stop the infromatica workflow is shown below:
pmcmd stopworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name4. Start workflow from a task You can start the workflow from a specified task. This is shown below:
pmcmd startask -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name -startfrom task-name5. Stopping a task. The following pmcmd command stops the specified task instance:
pmcmd stoptask -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name task-name6. Aborting workflow and task. The following pmcmd commands are used to abort workflow and task in a workflow:
pmcmd abortworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-namepmcmd aborttask -service informatica-integration-Service -d domain-name -u user-name -