The Reserve Words Support in Informatica

Talking a lot about Informatica keeping Database at bay, doesn’t really make mush sense. At least we can’t avoid the database issues for long. This post deals the support for reserve words as column names of database tables in Informatica.
It’s quite common to use columns names like “Comment” in the tables (At least that was how I learnt about Reserve words). But once you try to populate such table, you may get a database driver error.
For example: Suppose you are using Oracle as your database, and the source or target table has a reserve word say “Year” as a column name then you may get following error:

“WRT_8229 Database errors occurred
SQL0199N The use of the reserved word "YEAR" following "" is not valid”

Solution:

Informatica Version 8.1.1 comes with support for Reserve word issues. All you need here is a file named reswords.txt that is created in the Integration Service installation directory or the installation directory of Informatica. In this file you may have entries in the following format:
[ORACLE]
Comment
Year
[DB2]
Year
Select

This file is then located by the Integration Service which places quotes around reserved words every time an SQL statement is used any database.

My first brushes with Informatica!!!

If you are a newbie to any tool, the first question that ever occurs in your mind (usually, not always) is “Why is it required???” The lessons learnt during college days seem to be more than sufficient for any problem on earth, at least that’s what we all did for our intern project. But what we don’t realize is not only we are new to the tool, we are also unaware of the real world issues and scenarios. The first question that came to my mind was why is Informatica required when we have all the robust database products in place. This sounds quite stupid, but what’s worth mentioning here is by that time I was not fully aware of what does ETL exactly mean (I did know its full form then).
For a newbie, I suppose what’s most important is asking the right question and then following it up till you get a definitive solution. Having said that, I’ll shoot following questions to the fresher:
1. What will you do if you are asked to load data from a text file to any database, say Oracle (I agree there are work around solutions, but I am not getting into them)
2. A table in a database from one vendor (say, DB2) needs to be loaded to the one from another vendor (say, Oracle) along with its data and without distorting the schema of course (Remember different databases support different data types with varying precisions)
3. Now let us take a scenario where you have to load data from a file into a table. The file is replenished with new data daily and you need to load this new data into the same table. Assuming you find a workaround solution for Question 1 above. Now will you carry out this “smart” procedure of yours for the scenario above daily?

Welcome to the technology of Informatica where problems like above and much more complicated than these can be tackled with utmost ease. Once you solve any of the issues mentioned above, you’ll be said to have done ETL i.e. Extract, Transform and Load. There is much more to be talked about the capabilities of the tool and that is what this blog is dedicated to.

Hey Informatikers

We were as nascent to this technology called Informatica, as you are now (may be you aren't)... But now we stand erect on a stage from where we can claim ourselves to be evolved with Informatica. Taught by experience and tested by conditions, we are now out to help others out of tough time that we have been through while playing with this tool. As our further posts will bear testimony to this fact, we also welcome your queries, suggestions and issues on the ETL technology.