Informatica Issue
I got this question as a comment.
Hi,
In informatica one of our mappings are using oracle target table. Daily it runs in production and complete in 10 min. but suddenly its just running and running. we stopped it forcely. what could be the potential cause for its continuous run?
Thanks.
There could be many reasons for this. One that comes to my mind is database statistics. When was the last time you gathered stats? The other reasons could be duplicate keys on the target table or if you have setup your session to collect all kinds of stats and data in the logs (it would just take longer but not a whole lot) or huge lookup source with no calculated cache settings….I could go on with this but it would be useless. I would like you to provide some information. Tell me about the version, the OS, the DB version, and all other architectural details. And tell me a bit about your mapping as well. If you want, go ahead and paste the session log here. I will take a look at it, see if I can find the issue and then remove it from the site.
Kirtan
Hi ,
I have one requirement.how to implement in informatica.
Source flat file having 100 records.
In the first run it should load only 10 records.
Second run it should load next 10 records.
Untill all the 100 records are loaded it
In each run it should load only 10 records.
Regards
Satya
i am assuming that you have an id on each row that uniquely identifies each row. [call it ID ]
sort the rows by ID.
in your mapping create a lookup to the table that you are going to load with the contents of this file.
in a filter transformation, discard the rows that are already in the DB/table.
AFTER that rank rest of the rows. let all the rows with rank < =10 pass through. and discard rest. OR discard where rank > 10.
————-
one of the biggest drawback of informatica in my opinion is that it does not and can not allow looping construct…
now the solution above would work fine if you are dealing with 100 rows (as you mentioned). however , if you are dealing with 200 million rows it can be a nightmare.
i would rather do something like the following if my source file was 200m and if i had a choice.
say your source file looks like:
ID NAME
1 A
2 B
3 C
4 D
5 E
…
…
26 Z
I would load it into a database table [ in staging area/schema/space sort of] called Table T.
So table T will look just like the file.
now, from table T to your main table where you really want to load this data, you can either loop through the data or run any type of SQL on it by providing range etc… your options are limitless from this point onwards…and the time/money/resource saved by not doing it using pure informatica code can be used for something else.
in ETL, remember this…
if you can do something using SQL and nothing else, use only SQL and nothing else
if you cannot do it in SQL , use PL/SQL or T-SQL or similar.
If you cannot do it in PL/SQL or T-SQL or similar, use Informatica
If you cannot do it in Informatica, use .NET/Java/Perl etc
If you cannot do it in .NET/Java/Perl etc, stop coding . go back to your analysis and find the biggest mistake/misunderstanding of your life.
Hello,
There are couple of steps to load the data.
First, create a variable name “rowval” and assign 0 to that initially.
Step 1: configure the source qualifier(default)
step 2: sort the the record by some unique identifier if it have.
step 3: create sequence generator transformation take the output to expression and similarly output of sorting transformation to the expression transformation.
step 4: configure filter transformation with condition sequence_col > rowval and sequence_col <= (rowval+10)
step 5: connect the output from the filter to the target
Hi Mahendar, Can you please let me know how would the ‘rowval’ variable get updated?