How to Resume a Failed Workflow in Informatica

I am pleased to write this article, as today I have figured out a very important feature of Informatica. I was in a Informatica user group meeting about a month ago. We discussed there that the other organizations are using a third party scheduling tool to schedule workflows of Informatica. I asked them why they do so and they replied that if something fails in the middle of the workflow, there is no way to resume the workflow from the point they want it. They need to run the whole workflow again. Well, that's not true. There is a way to resume the execution of a workflow. 

Here is what we need to do in order to resume a workflow execution. 

• Open the workflow in workflow manager. 
• Right click on the task you want to resume execution from.
• Click on the 'Start Workflow from Task' option.

That's it. 

This will run all the tasks after it, that means all the way down. Note that if there is anything which do not have dependency on it or not in the downstream, will not be executed.

How to find the max column value from more than one tables

I was needed to find out the maximum value for a column from 16 tables. The column was ROW_LAST_MODIFIED_THIS and I was needed to compare it with a source table field value, which contains the row_last_modified value at source side. The idea behind was to compare both the fields and get only records which are new or updated later than the max modification date. Here is how I figured it out:



SELECT * FROM table WHERE
 table.ROW_LAST_MODIFIED > NVL((select max(a) from(
(SELECT MAX(row_last_modified_this) as a
FROM table1) union all
(SELECT MAX(row_last_modified_this) as a
FROM table2) union all
(SELECT MAX(row_last_modified_this) as a
FROM table3) union all
(SELECT MAX(row_last_modified_this) as a
FROM table4) union all
(SELECT MAX(row_last_modified_this) as a
FROM table5) union all
(SELECT MAX(row_last_modified_this) as a
FROM table6) union all
(SELECT MAX(row_last_modified_this) as a
FROM table7) union all
(SELECT MAX(row_last_modified_this) as a
FROM table7) union all
(SELECT MAX(row_last_modified_this) as a
FROM table8) union all
(SELECT MAX(row_last_modified_this) as a
FROM table9) union all
(SELECT MAX(row_last_modified_this) as a
FROM table10) union all
(SELECT MAX(row_last_modified_this) as a
FROM table11) union all
(SELECT MAX(row_last_modified_this) as a
FROM table12) union all
(SELECT MAX(row_last_modified_this) as a
FROM table13) union all
(SELECT MAX(row_last_modified_this) as a
FROM table14)))
                                ,SYSDATE-36500)

You can use it with any number of tables. The field you are comparing should contain the similar value, like in this example it is date. Hope it will help :)

WorkFlow Manager Problem(Repository Navigator invisible)

Few days back, I was working with Informatica and I faced a problem in the Workflow Manager.The repository navigator window in Workflow manager just went away. I was not able to bring it back anyhow. Probably it was invisible :) . I tried few things listed below.

Clicked on View menu option. Checked the Repository Navigator.
Restarted Whole application.
Rebooted System.

Nothing helped. I was still not able to see then Repository Navigator in Workflow Manager. Finally what worked is as below.

1.Exit from all Informatica applications. 

2. From the Start menu, select Run. 

3. Type REGEDIT in the Open textbox and click OK. 

4. Look for the HKEY_CURRENT_USER folder and expand it. 

5. Look for the Software folder and expand it. 

6. Look for the Informatica folder and expand it. 

7. Look for the PowerMart Client Tools folder and expand it. 

8. Look for the version number(9.x in my case) folder and expand it. 

9. Look for the Workflow folder and select it. 

10. Export the Registry file. (From the Registry menu, select Export 
Registry File…). 

11. Save the Registry file on your Desktop. 

12. Open the Registry file from your Desktop (Double click on the file, when 
prompted click OK, click OK again). 

13. Delete the Workflow folder in REGEDIT. 

14. Close any applications currently running on your computer. 

15. Restart your computer. 

16. Log into the Informatica Client tool that displayed the corrupted output 
window (i.e. Informatica Workflow). 

17. The output window should now be fixed and displayed properly. 

18. Delete the Registry file from your Desktop. 

Hope this will be useful.

ETL

Most of the organizations deal with different types of data sources. They may get data in different formats from different databases. To store data in Data Warehouse it is required that we convert all the data in same format and make it usable. There are several concepts, I am going to talk about ETL process.

What is ETL?

Extract - to extract/read data from different sources

Transform - Perform the required tranformations on the data

Load - Load the data into the Target/Data Warehouse.

There are several tools in the market to perform ETL or Data Integration. Whenever we need to perform an ETL, we should first come up with a strategy. There are following suggestions for a good ETL strategy.

• Keep it simple.
• Try to break the task in pieces in order to accomplish the whole task.
• Use data driven methods.
• Avoid custom SQL codes.
• Use files, variables and parameters to avoid rewriting values and to achieve efficiency.

Here are some qualities of a good ETL architecture:

• The performance should be good.
• The ETL design should be scalable.
• Migratibility is another quality.
• Robustness and ability to recover are very important.
• Operable (completion-codes for phases, re-running from checkpoints, etc.)
• Auditable (in two dimensions: business requirements and technical troubleshooting)

How to Create DataBase Links in ORACLE

Many times we need to interact with different databases at same time. I assume many of you, like me has faced this situation. There is an easy way to do so. DB Links are really useful. I mostly use them while data comparison and validation, but they can be used for much more. Here is a way you can easily create and use a DB Link.

CREATE DATABASE LINK "Link_Name"
CONNECT TO Schema Name
IDENTIFIED BY Password
USING 'DB Alias';

This code will create the DB Link and then you can use it in following manner:

Select * from TableName@Link_Name

You can execute this script in other database connections.