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
Comments feed
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?
Hi,
I am running a workflow, in this workgflow there are 2 session. one session is for loading data from file into staging table and 2nd session for loading data from staging into fact table.
first session is running fine , but when i run the 2nd session which has 65k records to load, it keep running for 1-1.5 hrs withour any error message, only message i found in logs is “CONNECTING to DATABASE” i checked every level in db i.e. checked the temp tablespace it has enough space.. and there’s no lock on the target table.
Need your support on this issue.
thanks in advance,
Nitin
Hi kirtan this what they are expecting;–
One point to be noted that we are not looking for “runtime” manipulation of metadata for target schema or the process flow.
Based on customer specific requirements for target schema variation (elimination of columns or entities), we should be able to generate from scratch or tweak master metadata for target schema map or process flow to generate desired output. This is still considered as “design” time, just that on-site person is not using GUI tools to design the process flow rather our infrastructure will generate it.
Based on your evaluation, does Talend and Pentaho support this?
I will need to answer this from Imformatica perspective.
_______________________________________________________________________________________________________________________________________________________
Our Analysis:——————
steps:—
1. Create table control_load
2. Add 3 columns like tab_name,column_name,flag
3.Develop map to generate the parameter file where flag = ‘Y’ with parameter
as INSERT statement( for those columns or table you dont want make the flag as ‘N’)
4. Develop the actual map, after source add SQL transformation and call the parameter
for Inserting to target tableflow make dymamic for SQL transformation
5.In work
two mappings one workflow, call sessions in sequential mode
it covers both scenarios
based on flag you can do that
it all depends on how you’ll set the flag
Kirtan, make changes to the steps if i m wrong
Shivam
@Nitin
I would need more details about your mapping. but a few things you can check for are: table indexes, table locks, connections etc.
you can search for “performance tuning” on this site and see if it gives you any ideas.
let me know if you have more questions.
@Shivam
How often are you expecting these changes? And why do you have to make this dynamic? What’s going to happen to all the stored procs, reports, feeds that are written based on the structure? are you going to make all of that dynamic too?
Hi
I may be wrong on dynamic part ,just elimating columns or entities
May i know the steps to map or proceess for the desired output
seems fine. try it out. it should work. I personally don’t like the idea of such automation. I am all for automation but change control processes are also important as a product/project matures.
Hi Thanks,
I agree your words but what the are the other possibilities ,so that change control process
is also a factor keeping in mind
Thanks for your paitence in guoding me ,I appreciate it
Shivam
Hi all,
I am facing a problem in Informatica. I have to execute SELECT statement stored in a flat file in informatica. I tried SQL transformation, but not sure how well it fits into the solution, i am looking for. The problem is:
I have a flat file in which i have 20 select statements for twenty different tables. Now i want to execute one select statement from the .txt file to load the output of that select stmt in to a column in target table using informatica and subsequently for remaining 19 Select stmts. So is there any way i can execute SELECT stmt from flat file.
Need your suggestion.
Thanks in advance,
Nitin