Interview Question&ANSWERS-2

1.What is the advantages of converting stored procedures into Informatica mappings?
Ans:-
Informatica is intermediate tool which talks to database. If you use the stored proc , the informatica needs to talks to Database for each record since Informatica process record by record. Making a call to database every time is costly.It is good to avoid stored proc as much as possible unless it is very necessary.If you write the same code in informatica, the informatica server will execute the code.


2.How to list Top 10 salary, without using Rank Transmission?
Ans:-
BY USING SORTER TRANSFORMATION USING SORTED PORT AS SAL AND FILTER TRANSFORMATION TO GET FIRST 10 RECORDS

3.I want load the data into target with out 1st and last record.

BY USING FIRST FUNCTION, TO GET THE 1ST RECORD,TO GET THE LAST RECORD , 

SIMPLY USEAGGREGATE TRANSFORAMATION IN THIS DO NOT APPLY GROUP BY FUNCTION 

BY DEFALUT IT WILL GENERATE LAST RECORD

OR

USE RANK TRANS

IN =>PROPERTIES  NO OF TOP RANKS:1

NO OF BOTTOM  RANKS:LAST RECORD

4. can u apply SCD2 on flat file tgt? if yes wat is the procedure?

 No, you can't apply SCD2 on flat files.

5. we have 6 records in source , i need 2nd record in one target and 5th record in one target or 2nd & 5th record in same target.

Another way is to use rank t/r and load data to target by 

filtering 2 and 5 rows.

or

We can use Router t/r also.

create as many conditions u want.

say, if rownum=2 then flow the data to target 1

if rownum=5 then flow to another target

6.  Re: If u r giving different parameter file names in workflow and session for a single mapping parameter, then which one will be executed? The one u given in workflow or the one u given in the session?

workflow parameter file

7. How to update records in Target, without using Update Strategy?

YES WITHOUT A pk WE CANT UPDATE THE REC u can write sql query at targetlevel. 

in target table properties there isa option updateoverride.

IN SESSION PROPERTIES WE HAVE ONE OPTION TREAT ALL ROWS AS... THERE U SELECT 

UPDATE OPTION AND IN THE TARGET PROPERTIES SELECT UPDTAE OPTION.

8. A TABLE CONTAINS SOME NULL VALUES . HOW TO GET (NOT APPLICABLE(NA))

IN PLACE OF THAT NULL VALUE IN TARGET .?

in a ports tab select particular column write N/A 

in the Default value text box for the particular column

9. How to load time dimension?

Run the procedure to load the ttime dimension.Its not loaded frequently,but 

once or twice a year.

10. What is the main data object present inbetween sourceand target.

The main data object present inbetween source and target is staging layer only, 

Staging layer will do eliminate the inconsistency data and gives the result data

object

11.Can we use unconnected lookup as dynamic lookup?

NO

Unconnected lookup will return one port only. But dynamiclookup will return 

more than one port and it update andinsert the targt while session runs.

12. LOOKUP Condition is nothing but a Join condition? What type of join 

condition it,by default ? Using the LookUP Condition How many types of 

relational conditionswe can make ?

lkp is always behave like left outer join..it ll give you all matched 

records as well as ummatched records which is not present in base table...and 

those unmatched records is ll be null in case of unconnected lkp trn.....

13.  have two flat files.. containing same type of data i want to load it 

to dwh..how many source qualifires i need

If the 2 flat files have the same structure,then we can go for filelist 

concept ininformatica.

only one source qualifier is needed and the source should be either of the 

flat files.

14.when will we use unconnected & connected lookup? Howit will effect on the 

performance of mapping?

Connected Lookup                      

Receives input values directly from the pipeline.    

We can use a dynamic or static cache

Supports user-defined default values

Unconnected Lookup 

Receives input values from the result of a :LKP expression 

in another transformation. 

We can use a static cache 

Does not support user-defined default values 

When you compared both basically connected lookup will return more values 

and unconnected returns one value. conn lookup is in the same pipeline of 

source and it 

will accept dynamic caching. Unconn lookup don't have that faclity but 

in some special cases we can use Unconnected. if output of one lookup is going 

as input of another lookup this unconnected lookups are favourableMoreover if the 

mapping requires multiple lookup using samelookup conditions then its better

to use unconnected lookup& call the lookup wherever required.This also 

prevents the mapping from being complex.

15. how do u use sequence created in oracle in informatica?

 Explain with an simple example

Using Stored Porcedure Transformation we can call the Sequence Generator

16. in which situations do u go for sequence generator ?

In the Fallowing Situations we use Sequence Generator 

Transformation

1)Creating Primary Key Values

2)Replacing The Missing Key Values

3)When You Apply the Cycle Through a Sequential Range Of 

Numbers

17. write a query for how to eliminate the duplicate rows 

without using distinct?

DELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID)FROM

EMP)GROUP BY EMPNO;

18. source is a flat file empname, empno, sal ram, 101, 1,000 sam, 102, 2,000 ques: my target needs the data to be loaded as sal -1000 and 2000 excluding commas target empname, empno, sal ram, 101, 1000 sam, 102, 2000 how to implement this?

OPEN THE SOURCE FILE-->EDIT-->REPLACE ALL , to NULL.

USE FIXED WITH LENGTH WHILE IMPORTING TO SOURCE AND .....

HOPE IT WILL HELP

19. wht is cdc?how to use it in creation of mappings?

CDC stands for change data capture

CDC defines Whenever data is changed in OLTP Systems Only 

that data Will be captured and loaded into our Target

SCD's works internally based on cdc logic

basically cdc's implented by using effective date

20. how can we store previous session logs

 

Go to Session-->right click -->Select Edit Task then Goto -->Config Object
then set the property

Save Session Log By --Runs

Save Session Log for These Runs --->To Number of Historical Session logs you want

 

21.How to read rejected data or bad data from bad file and reload it to target?

Correction the rejected data and send to target relational tables using loadorder utility. Find out the rejected data by using column indicatior and row indicator.

21. In my source table 1000 rec's are there. I want to load 501 rec to 1000 rec into my Target table ?

select * from tab_name where rownum<=1000 minus select * from tab_name where rownum<=500;

or

hi connect a sequence generator next to source qualifier into the expression.Use the filter trans to filter rows like where nextval>500.

22. I have a cobol program with a sub program. How ca i find that it is a dynamic call? or static call..?

1st u check the stmt call 'sub-name' or call 'ws-name'...

if it is call 'sub-name' then it is STATIC CALL 

                    OR

If it is call 'ws-name' then it is DYNAMIC CALL.