Q: How to Avoid Duplicates Records in target Table by Using Lookup?

Informatica Interview Questions and Answers, Informatica Real time Issues and Scenarios.

Q: How to Avoid Duplicates Records in target Table by Using Lookup?

Answer:  Below are the ways to find that:

1. Connected lookup: Use a Connected lookup with Enabled Dynamic Cache. and Please check the Output Port of the NewlookupRow(Which WIll be Inserted into the target Table). lookup will Decide whether the incoming record is existed or not in the table cache?

2.UnConneted Lookup: Create An UnConnected Lookup. and Call the lookup in Expression Transformation and Please Check the Lookup Condition Port Value(NULL/NOT NULL) to decide whether the incoming record already existed in the target table or not?

Loading Techniques in ETL at Database end

Informatica Interview Questions and Answers, Informatica Real time Issues and Scenarios.

Loadinig Techniques in ETL Loading at Database end.

Answer: There are two Type of Loading Techniuqs while Loading data into Database tables.

1. Direct Load

2.Conventional Load

Direct Load:

Data will be Loaded first into table and then it will check the Constarints defined on the table.

it will be faster loading.

Conventional Loading:

Data will loaded into table after Checking the Constraints defined on the table. if the data saticifies the Constarint Conditions then only data will be Loaded into table. otherwise it will not load and job will fail.

compared with Direct loading Conventional Loading is little slow but Safer in Loading.

Direct Loading, if we have any data issues then we need to Clean the table to correct the Bad data. becuase the data will be loaded before constraint checking.

in Conventional loading,if we have any data issues then data will not be loaded into table. before loading data it will fail.

Thanks,

Anilkumar.

Scenario 11: ORA-01502: index 'XYZ.UNIQUE_UK1' or partition of such index is in unusable state

Answer: This is error which is Related to Database table XYZ having Unique index UNIQUE_UK1. the Index was Dropped by somehow, so due to this we are getting this error.

Resolution: we have to Ask the DBA Guys to Recreate the indexes or we need to build the index manually. once indexes are built, we are good to restart the job.

***Note***

Generally indexes will be dropped if there are any Duplicate Records in the table.

Scenario10: Incomplete Record at the end of file(Abinitio error)

This is an Abinitio Error. we usually get this kind of error when Delimiters were Present in between the field Values. Due to this Records will not be Ended with Proper Delimiters.

Example:

we have a flatfile which is having below delimiters:

Field Delimiter: "&&"
Record Delimiter: "\n"

We are Creating the Flatfile one of the file field value Contained "\n" in its Value. So Record will be Spillted into Another Record from that point(Where it contains "\n" new line Character). it will Treat "\n" as a Record Delimitter. Due to this One Complete record will be Splitted into Two Incomplete Records.

Example records::

100&&Rama&&156&&Good Boy
101&&Laxmi&&1560&&Bad Boy
102&&Suryanarayana
&&1760&&Good Boy
103&&JrNtr&&9999&&Perfect Actor

from the Above example Records we can see that record 102 was Splitted at Suryanarayana Baceuse of Newline Character("\n") at the end of Field. and again the next two fields are Treated as Another new record. both the Records are incomplete.

Resolution:

So we have to Remove the new line Character("\n") from the Value(Suryanarayana) and Re-create the Flatfile again. then data will be Loaded without Corrupted data. 

ORA-01422: exact fetch returns more than requested number of rows

Scenario:

ORA-01422: exact fetch returns more than requested number of rows

Solution:  This is an Error related to Cursor Variables. when we are Declaring Cursor variables and then we are trying to Insert Values INTO these variables from PL/SQL Select Statements. while Retrieving the Value from Select Statement at a time is more than one value then it will Throw the below error.

Example:

BEGIN
SELECT A.XYZ
INTO l_XYZ
FROM XYZ_TABLE_LOG A
WHERE
A.XYZ IN ((SELECT MAX(A.XYZ) FROM XYZ_TABLE_LOG A) where TEXT='SAMPLE')

Error:

ORA-01422: exact fetch returns more than requested number of rows

So here The Values Retrieving from the Select Statement Returning More than one value. so The Cursor Variable is Throwing an Error.

Select MAX(A.XYZ) from XYZ_TABLE_LOG A where TEXT='SAMPLE'
342
342

so here the max value is 342. but it returns Two Values, becuase it is selecting based on TEXT='SAMPLE' in where Clause. so Remove one Value from Table XYZ_TABLE_LOG.

Please investigate why we are getting 2 values As a MAX value. then Issue will be resolved.

Thanks,
Anilkumar.

Why is Persistant Cache in Lookup Transformation in Informatica?

Why is persistant Cache in informatica lookup?

Ans: Persistant cache is one of the lookup cache in informatica. persistant cache plays an important role in Lookup caches.

if your lookup file/table is Static and will not change regularly, then we can check the option persistant Cache in lookup Transformation. 

Note: make sure the Data in Lookup table/file is always Constant if we check this option.

thsi will help to Increase the Lookup Performance. it plays important role in Performance of llokup tranformation.

Q: How to Print Data in Multifile Prtition?

Q: How to Print Data in Multifile Prtition?

Ans: To Print Data in a Multifile Partition:

Ex:  m_dump Loader.dml mfile:mfs8/tempfile.dat -partition 6

the above xample is to print the data of a multifile tempfile.dat from the partition 6.