INFORMATICA SCENARIO BASED INTERVIEW QUESTIONS WITH ANSWERS - PART 1

INFORMATICA SCENARIOS

I have listed the following informatica scenarios which are frequently asked in the informatica interviews. These informatica scenario interview questions helps you a lot in gaining confidence in interviews.

1. How to generate sequence numbers using expression transformation?

Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign the variable port to an output port. In the expression transformation, the ports are:
V_count=V_count+1
O_count=V_count

2. Design a mapping to load the first 3 rows from a flat file into a target?

Solution:
You have to assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3

3. Design a mapping to load the last 3 rows from a flat file into a target?

Solution:
Consider the source has the following data.
col
a
b
c
d
e

Step1: You have to assign row numbers to each record. Generate the row numbers using the expression transformation as mentioned above and call the row number generated port as O_count. Create a DUMMY output port in the same expression transformation and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.

In the expression transformation, the ports are
V_count=V_count+1
O_count=V_count
O_dummy=1

The output of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b, 2, 1
c, 3, 1
d, 4, 1
e, 5, 1

Step2: Pass the output of expression transformation to aggregator and do not specify any group by condition. Create an output port O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row by default. The output of aggregator contains the DUMMY port which has value 1 and O_total_records port which has the value of total number of records in the source.

In the aggregator transformation, the ports are
O_dummy
O_count
O_total_records=O_count

The output of aggregator transformation will be
O_total_records, O_dummy
5, 1

Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.

In the joiner transformation, the join condition will be
O_dummy (port from aggregator transformation) = O_dummy (port from expression transformation)

The output of joiner transformation will be
col, o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5, 5

Step4: Now pass the ouput of joiner transformation to filter transformation and specify the filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2

In the filter transformation, the filter condition will be
O_total_records - O_count <=2

The output of filter transformation will be
col o_count, o_total_records
c, 3, 5
d, 4, 5
e, 5, 5

4. Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C? 

Solution:
This is similar to the above problem; the first 3 steps are same. In the last step instead of using the filter transformation, you have to use router transformation. In the router transformation create two output groups.

In the first group, the condition should be O_count=1 and connect the corresponding output group to table A. In the second group, the condition should be O_count=O_total_records and connect the corresponding output group to table B. The output of default group should be connected to table C.

5. Consider the following products data which contain duplicate rows.
A
B
C
C
B
D
B

Q1. Design a mapping to load all unique products in one table and the duplicate rows in another table.
The first table should contain the following output
A
D

The second target should contain the following output
B
B
B
C
C

Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a dummy port O_dummy and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.

The output of expression transformation will be
Product, O_dummy
A, 1
B, 1
B, 1
B, 1
C, 1
C, 1
D, 1

Pass the output of expression transformation to an aggregator transformation. Check the group by on product port. In the aggreagtor, create an output port O_count_of_each_product and write an expression count(product).

The output of aggregator will be
Product, O_count_of_each_product
A, 1
B, 3
C, 2
D, 1

Now pass the output of expression transformation, aggregator transformation to joiner transformation and join on the products port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.

The output of joiner will be
product, O_dummy, O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1, 2
C, 1, 2
D, 1, 1

Now pass the output of joiner to a router transformation, create one group and specify the group condition as O_dummy=O_count_of_each_product. Then connect this group to one table. Connect the output of default group to another table.

Q2. Design a mapping to load each product once into one table and the remaining products which are duplicated into another table.
The first table should contain the following output
A
B
C
D

The second table should contain the following output
B
B
C

Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a variable port,V_curr_product, and assign product port to it. Then create a V_count port and in the expression editor write IIF(V_curr_product=V_prev_product, V_count+1,1). Create one more variable port V_prev_port and assign product port to it. Now create an output port O_count port and assign V_count port to it.

In the expression transformation, the ports are
Product
V_curr_product=product
V_count=IIF(V_curr_product=V_prev_product,V_count+1,1)
V_prev_product=product
O_count=V_count

The output of expression transformation will be
Product, O_count
A, 1
B, 1
B, 2
B, 3
C, 1
C, 2
D, 1

Now Pass the output of expression transformation to a router transformation, create one group and specify the condition as O_count=1. Then connect this group to one table. Connect the output of default group to another table.

Informatica mapping to load first half records to 1 target while other half records to a separate target.

Scenario :

Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.

Solution:

You will have to assign a row number with each record. To achieve this, either use Oracle's psudo column rownum in Source Qualifier query or use NEXTVAL port of a Sequence generator. Lets name this column as rownumber.

From Source Qualifier, create 2 pipelines:

First Pipeline:
Carry first port Col1 from SQ transformation into an aggregator transformation. Create a new output port "tot_rec" and give the expression as COUNT(Col1). Do not group by any port. This will give us the total number of records in Source Table. Carry this port tot_rec to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Second Pipeline:
from SQ transformation, carry all the ports(including an additional port rownumber generated by rownum or sequence generator) to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Join these 2 pipelines with a Joiner Transformation on common port DUMMY. carry all the source table ports and 2 additional ports tot_rec and rownumber to a router transformation. Add 2 groups in Router : FIRST_HALF and SECOND_HALF. Give condition rownumber<=tot_rec/2 in FIRST_HALF. Give condition rownumber>tot_rec/2 in SECOND_HALF. Connect the 2 groups to 2 different targets.

Sending alternate record to target

Scenario: 

How to send alternate record to target?

Or

Sending Odd numbered records to one target and even numbered records to another target.

Solution:
Step 1: Drag the source and connect to an expression transformation.

Step 2: Add the next value of a sequence generator to expression transformation.

Step 3: In expression transformation make two port, one is "odd" and another "even".

And Write the expression like below

Step 4: Connect a router transformation to expression.

Make two group in router.
And give condition  Like below

Step 5: Then send the two group to different targets.

The entire mapping is as below

MONITORING
OF
LINUX SYSTEM
[1] top : top is a command that neatly list all processes with valuable
info sort by CPU and memory usage.
'top' provides lots of information on the processes running, including the
percentage of the cpu and the memory being consumed by that process, who
'owns' the process and it's pid (Process ID), it also shows the output of
'uptime' and a summary of memory usage, similar to 'free'. The output of
'top' is full screen, and refreshes itself frequently (or at user definable
intervals).
#top
[2] To see last shut down time Of RHEL System:

#last -x|grep shutdown | head -1

[3] To see last reboot history of RHEL System.
#last reboot
[4] W command is use to see the Login Linux user time.
It shows the uptime of the linux system
#w
#w root
[5] netstat -tulpn command to display listening sockets/ports
and associated program.
#netstat -tulpn
[6] control the service :
The ntsysv command is a simple interface for configuring
runlevel services which are also configurable through chkconfig
command. It allows to control which services are
started when the system boots up:
#ntsysv
The rcconf command under Debian / Ubuntu Linux allows you
to control which services are started when the system boots
up or reboots.
#rcconf
[7] uname command :
uname -r: Print the kernel release
#uname -r
uname -v: Showing kernel version:
#uname -v
uname -o : print the operating system
#uname -o
uname -a : print all information
#uname -a
[8] To see list of enable port:
#nmap ‘IP address’
[9] who Command to see who is on the system.
#who
[10] df command to view disk usage and we'll be shown disk usage for
all mounted filesystems in 1K blocks.
#df
Check available space on the hard drive in human readable format.
#df –h
[11] du command:
Now that To know how much space has been used on a filesystem
how can you find out where that data is?
To view usage by a directory or file we can use du. Unless we specify
a filename du will act recursively
Check disk usage for a specific folder:
#du -sh
#du file.txt
1300 file.txt
In human readable format.
#du −h file.txt
1.3M file.txt
[12] free command shows information about the machine's memory.
This includes physical memory (RAM), swap as well as the shared memory
and buffers used by the kernal. All measurements are in Kb.
#free
To see Total RAM with Free avliable.
#free –k
Display shows how much memory is being used in kilobytes,
because we specified the -k option.
#free –m
[13] ps command to see current working process.
#ps
[14] The 'kill' command is complementary to the 'ps' command as it
will allow you to terminate a process revealed with the previous command.
In cases where a process is not responding, you would use the following
syntax to effectively kill it:
‘Kill-9 pid’
where 'pid' is the Process ID (PID) that 'ps' displays for each task.
[15] ls commands:
To see list of all files with hidden files.
#ls -al
To see the largest files on filsystem.
#ls -lS
[16] Finds all files over 20,000KB (roughly 20MB) in size and presents
their names and size in a human readable format:
#find . -size +20000k -exec du -h {} \;
[17] To search the file.
#locate ‘file name ‘
[18] Shutdown the system.
# shutdown −r now.
[19] Reboot the system.
#reboot

Linux Basic Commands

Linux Basic Commands

1. ls -l for listing the files as well as directories those are kept in
the particular working directory
Syntax
[root@sss root]#ls -l
2. ls -la same as 'ls -l'but by this command we can also see the hiden
files.
Syntax
[root@sss root]#ls -la
3. ls -li same as 'ls -la' but it will also shows us the inode number of
each and every file
Syntax
[root@sss root]#ls -li
4. ls by this command we can see only file name nothing else
Syntax
[root@sss root]#ls
5. clear it will clear the screen(short cut ctl+l)
Syntax
[root@sss root]#clear
6. exit to end a current session as well current terminal logging
Syntax
[root@sss root]exit
7. touch to create a new empty file
syntax
[root@sss root]#touch
8. cd to change the working/present directory
Syntax
[root@sss root]#cd /home/mango
where '/home/mango' is the desired directory to be change from
'/root'
9. cat to view the contents of a file and it is also used for creating a
new file with some contents
Syntax
[root@sss root]#cat <file name> to view file contents
[root@sss root]#cat > newfilename enter,then you can write something in
the file and then to save the file contents press clt+d then enter
10. mkdir to make a new directory
Syntax
[root@sss root]#mkdir newdirname
you can also create a directory at your desired path without
changing your present working directory
Syntax
[root@sss root]#mkdir /home/mango/newdirname
11. rm to remove a empty file
Syntax
[root@sss root]#rm filename
Page 1
Basic Commands
12. rmdir to remove a empty directory
Syntax
[root@sss root]#rmdir directoryname
13. rm [-i/-r/-f] to remove a directory with its subdirectories as well as its
files that is to remove a directory which already contains some files in it
Syntax
[root@sss root]#rm -i directory/filename
-i stands for interactively
-r stands for recursively
-f stands for forcefully
14. cp to copy something in a destination file or directory
Syntax
[root@sss root]#cp sourcepath destinationpath
example: [root@sss root]#cp /home/mango/webmin.rpm /root/abcd
in this example the webmin.rpm file will be copied in
/root/abcd directory
15. mv to move one file or directory from one place to another place, it
is also used for renaming a directory or file
Syntax
[root@sss root]#mv source destination
[root@sss root]#mv oldfilename newfilename [to change the file name]
16. man to view the mannual page of commands for syntax
Syntax
[root@sss root]#man commandname
17. info to view the information about any command
Syntax
[root@sss root]#mkdir info
18. --help to view the help doccuments of a command
Syntax
[root@sss root]#commandname --help
19 .dir to view the subdirectories and filesn under the directory
Syntax
[root@sss root]#dir
20. su - to become a super user
Syntax
[mango@sss mango]$su -
output wil be
[root@sss root#]
21. who by this command you can see the user name and their ip addresses
who have loged in on your server
Syntax
[root@sss root]#who
22. whoami this command shows your current logged in terminal user name
Syntax
[root@sss root]#whoami
23. who am i this command shows you the logged in terminal number and user
name and more detailed information
Page 2
Basic Commands
Syntax
[root@sss root]#who am i
24. pwd to view the present working directory
Syntax
[root@sss root]#pwd
25. rpm -ivh to intall a rpm package
Syntax
[root@sss root]#rpm -ivh packagename.rpm
rpm stands for 'redhat package manager'
-i stands for install
-v stands for verbose mode
-h stands for with hash sign(#)
26. rpm -q to querry about any rpm package
Syntax
[root@sss root]#rpm -q packagename
27. rpm -e to uninstall a rpm package
Syntax
[root@sss root]#rpm -e package
28. find / -name to find any file or directory in linux file system
Syntax
[root@sss root]#find / -name filename
29. su username to switch from one user to another users home directory
Syntax
[root@sss root]#su mango
output will be
[mango@sss root]#cd
[mango@sss mango]#
30. su - username to switch from one user to another user users home
directory directly
Syntax
[root@sss root]#su - mango
31. useradd to create a new user
Syntax
[root@sss root]#useradd username
32. passwd to give a password of a user
Syntax
[root@sss root]#passwd tarun
output will be
give a password for user tarun:(here you have to type a password for tarun user)
confirm password:(again type the same password)
33. userdel to remove a user from linux
Syntax
[root@sss root]#userdel tarun
Page 3
Basic Commands
34. groupadd to add a new group
Syntax
[root@sss root]#groupadd groupname
35. gruopdel to delete a group
Syntax
[root@sss root]#groupdel groupname
36. chown to change the ownership of a file or directory
Syntax
[root@sss root]#chown ownername filename
example:
[root@sss /]#ls -l
output
drwxrw-rw- 2 root root 4096 Mar 11 12:03 abcd
(permission) (own) (group own)(size) (name)
[root@sss root]#chown tarun /abcd
in this example /abcd directory owner will be change to tarun user
effect
[root@sss /]#ls -l
drwxrw-rw- 2 tarun root 4096 Mar 11 12:03 abcd
37. chgrp to change the group ownership of a file or directory
Syntax
[root@nettec root]#chgrp newgroupownername filename
example
[root@sss /]#ls -l
drwxrw-rw- 2 tarun root 4096 Mar 11 12:03 abcd
[root@sss root]#chgrp tarun /abcd
effect
[root@sss /]#ls -l
drwxrw-rw- 2 tarun tarun 4096 Mar 11 12:03 abcd
38. chmod to change the permission of a file or directory
drwxrw-rw- 2 root root 4096 Mar 11 12:03 abcd
(permission) (ownr) (grpownr) (size) (name) IN
OCTAL VALUE
d stands for directiry READ=4
r stands for read only permission WRITE=2
w stands for write only permission EXECUTE=1
x stands for execute only permission
drwxrw-rw- FIRST OCTET FOR DENOTING THE DIRECTORY OR FILE OR LINK FILE ETC.
SECOND THREE OCTET FOR USER OR OWNER PERMISSION (rwx OR 7 IN
OCTAL VALUE)
THIRD THREE OCTET FOR GROUP PERMISSION (rw- OR 6 IN OCTAL VALUE)
FORTH THREE OCTET FOR OTHERS PERMISSION (rw- OR 6 IN OCTAL VALUE)
SYNTAX
[root@sss root]#chmod value fileordirectoryname
example
[root@sss /]#ls -l
drwxrw-rw- 2 nidhi root 4096 Mar 11 12:03 abcd
[root@sss /]#chmod 402 /abcd
[root@sss /]#ls -l
drw-----w- 2 nidhi nidhi 4096 Mar 11 12:03 abcd

NOTE :- 
               1.Execute                2. Write                 3. Write & Execute 
   4. Read                  5. Read & Execute    6. Read & Write
   7. Read & Write & Execute

 DIFFERENCE BETWEEN PRIMARY KEY AND  SURROGATE KEY

A Surrogate key is something which is having the sequence generated numbers with no meaning,
and just to identify the row uniquely.
A Primary key would be anything that has some meaning and will be used to identified the row uniquely.

What are 2 modes of data movement in Informatica Server?
The data movement mode depends on whether Informatica Server should process single byte or multi-byte character data.
This mode selection can affect the enforcement of code page relationships and code page validation
in the Informatica Client and Server.

a) Unicode - IS allows 2 bytes for each character and uses additional byte for each non-ascii
character (such as Japanese characters)

b) ASCII - IS holds all data in a single byte

The IS data movement mode can be changed in the Informatica Server configuration parameters.
This comes into effect once you restart the Informatica Server.


1) Normal Mode in which for every record a separate DML stmt will be prepared and executed

Bulk Mode in which for multiple records DML stmt will be preapred and executed thus improves performance.


Data Movement Modes

1. ASCII mode :- Single byte of data is processed.
2. UNICODE mode :- Two bytes of data is processed.

Data Loading Modes

1. Normal Mode :- Commit is executed after 10000 records (default).
2. Bulk Mode :- Commit is executed after all records have been loaded.