Informatica Interview question with answer for lookup Transformation

In below article , we will go through the some the Informatica Interview question based on the lookup transformation in Informatica. In last tutorial , we have gone through the detailed explanation of lookup Transformation.
You can find a good details of Lookup transformation here.

We also checked some of the example of connected and unconnected lookup transformation also.

Lets go through some of the Informatica Interview question on Lookup transformation.

Check here : Difference connected and unconnected Lookup Transformation here .

Que: What is a pipeline lookup transformation??

Ans :A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookups source.

Que: What are the different type of Sources for Lookup ?

Ans :

• Relational source or target definition in the repository
• Flat file source or target definition in the repository
• Table or file that the Integration Service and PowerCenter Client machine can connect to
• Source qualifier definition in a mapping

Ques. What is a lookup transformation?
Ans : A lookup transformation is used to look up data in a flat file, relational table, view, and synonym.

Ques: What are the tasks of a lookup transformation?

Ans : The lookup transformation is used to perform the following tasks?

• Get a related value: Retrieve a value from the lookup table based on a value in the source.
• Perform a calculation: Retrieve a value from a lookup table and use it in a calculation.
• Update slowly changing dimension tables: Determine whether rows exist in a target.

Ques: . What is "Output Old Value on Update"?
Ans : This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

Ques: . How do you configure a lookup transformation?

Ans : Configure the lookup transformation to perform the following types of lookups:

• Relational or flat file lookup
• Pipeline lookup
• Connected or unconnected lookup
• Cached or uncached lookup

Ques: What are the options available to configure a lookup cache?
Ans : The following options can be used to configure a lookup cache:

• Persistent cache
• Recache from lookup source
• Static cache
• Dynamic cache
• Shared Cache
• Pre-build lookup cache

Check here : Difference Static Cache and dynamic Cache  in Informatica here .

I also found a good article on Informatica Interview question on Lookup Transformation  here and here

Slowly Changing Dimension Type 2 Examples (SCD 2)

  SCD Type-2 In Informatica[/caption] Slowly Changing  Dimension Type-2 ,(also known as SCD -2)  tracks historical changes  by  keeping multiple records for a given natural key in the dimensional tables .For example , we may need to track the current location of a supplier along with its previous location just to track his sales in different region .
 Example of SCD Type -2

For Example : If we want to keep track of DEPT of an employee , we can add two extra fields , Say Start-date , End_date to keep track of those records: EMP_SCD2

SRG_KEY	EMPNO	NAME	DEPT	START_DATE	END_DATE
101	1	JOHNSON	OPERATIONS	12/17/1990	4/13/1992
102	2	HARDING	ACCOUNTING	2/2/1998	9/17/1998
103	3	TAFT	RESEARCH	1/2/1996
104	4	HOOVER	SALES	4/2/1990	7/11/1993
105	1	JOHNSON	ACCOUNTING	4/13/1992
106	2	HARDING	SALES	9/17/1998
107	4	HOOVER	OPERATIONS	7/11/1993
108	5	LINCOLN	OPERATIONS	6/23/1994

As you see in above example , we can keep track of records by adding effective _date fields . Other way to accomplish this process is to

• Effective Date
• Flagging
• Versioning

I found a good article on "Slowly Changing Dimension Type 2 Examples (SCD 2)"  here.  and full implementation of SCD Type -2 in Informatica can be found here Other good article on SCD are as below :-

• Slowly Changing Dimension Type 2 Examples (SCD 2) 
• SCD Type 2 implementation in Informatica with example
• Slowly Changing Dimension Type 3 Examples (SCD 3)
• SCD Type 3 implementation in Informatica with example
• Slowly Changing Dimension Type 6 Examples (SCD6)
• SCD Type 6 implementation in Informatica with example

  Hope you enjoyed this small and useful article on SCD Type -2 (Slowly changing dimension type -2 ) and example of SCD Type-2 In Informatica  :)  

Type of SQL join in Oracle

SQL Joins are to combine data from different tables to show relation between them . Example you can join  EMP table with DEPT table to get department along with Employee records.

See Also : Learn about Oracle Architecture here 

There are basically below type of Joins :

1. INNER Join :

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
INNER JOIN dept
ON emp.deptno= dept.deptno;

2. Left Outer Join :

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
LEFT OUTER JOIN dept
ON emp.deptno= dept.deptno;

3. Right Outer Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
RIGHT OUTER JOIN dept
ON emp.deptno= dept.deptno;

4. Full Outer Join

SELECT emp.empno, emp.ename ,emp.job, dept.deptno,dept.dname
FROM emp
FULL OUTER JOIN dept
ON emp.deptno= dept.deptno;

I Found a good article on "Type of Oracle join with Example"  here. 

See Also : Learn about Oracle Architecture here 

Hope you enjoyed this small and useful article on SQL joins in Oracle :)

XML Source in Informatica

XML is gaining its popularity as Data sharing and Data storage medium . With Newer version , Informatica started providing support to XML files as well,

We basically have 3 XML functionality in  Informatica :

1. XML Source Definition

2. XML Target Definition

3. XML transformation.

Here we will discuss about XML File source definition . First of all , e will need to import XML file source definition in Informatica . by

1. Clicking on Source -> Import XML Definition

2. Selecting the  sample XML File and then getting all node information .

3.Checking the format of various nodes

4.Closing the Dialog box

A more detailed example of XML File Source definition  in Informatica  can be found  here.

Another example to xreate XML Target file in Informatica can be found here.

XML Target in Informatica

XML is gaining its popularity as Data sharing and Data storage medium . With Newer version , Informatica started providing support to XML files as well,

We basically have 3 XML functionality in  Informatica :

1. XML Source Definition

2. XML Target Definition

3. XML transformation.

Here we will discuss about XML File Target  definition . First of all , e will need to import XML file source definition in Informatica . by

1. Clicking on Source -> Import XML Definition

2. Selecting the  sample XML File and then getting all node information .

3.Checking the format of various nodes

4.Closing the Dialog box

A more detailed example of XML File Source definition  in Informatica  can be found  here.

Another example to xreate XML Target file in Informatica can be found here.

Oracle database architecture tutorial

Oracle Instance

An oracle instance is a set of different memory structures and the processes which manages all database activities, such as transaction processing, database recovery, form generation, and so on. There is different type of memory block which are accessed by different type of background processes

SGA :

It is also commonly known as  System Global area. It solve many purpose like storing parsed SQL qyeries/ Parsed Pl-sqL code and redo buffer cache.

It has below memory pools

• shared pool : 
• Data Dictionary Cache:
• Buffer Cache:
• Redo Log Buffer:
• Large Pool:
• Java Pool :

PGA :

Although the result of SQL statement parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

Oracle Background Process:

There some back ground process which keep on running to fulfill DB requests.

• DBWR
• LGWR
• CKPT
• SMON
• PMON
• ARCH
• DISPATCHER PROCESSES
• RECO
• SNPn
• LCKn
• Pnnn

Thanks for reading article !! I found a full explanation of these component here

unix interview question with answer

 Below are some list of some unix  interview question , which are commonly asked in unix interview.

1) How to print/display the last line of a file?
A) $> tail -1 file.txt

2) How to display n-th line of a file?
A) $> sed –n ' p' file.txt

3) How to remove the first line / header from a file?
A) $> sed '1 d' file.txt

4) How to remove the last line/ trailer from a file in Unix script?
A) $> sed –i '$ d' file.txt

5) How to remove certain lines from a file in Unix?
A) $> sed –i '5,7 d' file.txt

6) How to remove the last n-th line from a file?
A) $> sed –i '96,100 d' file.txt # alternative to command [head -95 file.txt]

7) How to check the length of any line in a file?
A) $> sed –n ' p' file.txt

8) How to get the nth word of a line in Unix?
A) cut –f -d' '

9) How to reverse a string in unix?
A) $> echo "unix" | rev
xinu

10) How to get the last word from a line in Unix file?
A) $>echo "C for Cat" | rev | cut -f1 -d' ' | rev
Cat

11)What are some common shells and what are their indicators?

sh – Bourne shell
csh – C SHell
bash – Bourne Again Shell
tcsh – enhanced C Shell
zsh – Z SHell
ksh – Korn SHell

12) What is a directory?

Every file is assigned to a directory. A directory is a specialized form of file that maintains a list of all files in it.

13) What is inode?

An inode is an entry created on a section of the disk set aside for a file system. The inode contains nearly all there is to know about a file, which includes the location on the disk where the file starts, the size of the file, when the file was last used, when the file was last changed, what the various read, write and execute permissions are, who owns the file, and other information.

14) You have a file called tonky in the directory honky. Later you add new material to tonky. What changes take place in the directory, inode, and file?

The directory entry is unchanged, since the name and inode number remain unchanged. In the inode file, the file size, time of last access, and time of last modification are updated. In the file itself, the new material is added.

For more good article on unix interview question with answer , pls check here.

Top unix interview question with answer :Part -1

Top unix interview question with answer :Part -2

Top unix interview question with answer :Part -3