Saturday 10 March 2018

More Informatica Interview questions (26-50)


Please go to our previous set of question by using link <<<Informatica Interview Question 1-25>>>

Question 26

What are  the different dynamic partitioning configurations which can make a session to run with one partition

You set dynamic partitioning to the number of nodes in the grid, and the session does not run on a grid.
You create a user-defined SQL statement or a user-defined source filter.
You use dynamic partitioning with an Application Source Qualifier.

Question 27
Under which situations you  will use the Source Filter, Select Distinct and Number of Sorted Ports properties of Source Qualifier transformation.
  • Source Filter option is used basically to reduce the number of rows the Integration Service queries, so as to improve performance.
  • Select Distinct option is used when we want the Integration Service to select unique values from a source filtering out unnecessary data earlier in the data flow, will improve performance.
  • Number Of Sorted Ports option is used when we want the source data to be in a sorted fashion, so as to use the same in some following transformations like Aggregator or Joiner, those when configured for sorted input will improve the performance.

Question 28
What is Persistent Lookup Cache?
If the cache generated for a Lookup needs to be preserved for subsequent use then persistent cache is used.It will not delete the index and data files. It is useful only if the lookup table remains constant.Lookups are cached by default in Informatica. Lookup cache can be either non-persistent or persistent. The Integration Service saves or deletes lookup cache files after a successful session run based on, whether the Lookup cache is checked as persistent or not.
Question 29
What are the restrictions of Union Transformation?


  1. All input groups and the output group must have matching ports. The precision, data type, and scale must be identical across all groups.
  2. We can create multiple input groups, but only one default output group.
  3. The Union transformation does not remove duplicate rows.
  4. We cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.
  5. The Union transformation does not generate transactions
Question 30 
What is difference between copy and shortcut?

CopyShortcut
Copy an object to another folderDynamic link to an object in the folder
Changes to original object doesn’t reflectDynamically reflects the changes
Duplicate’s the spacePreserves the space
Created from unshared foldersCreated from shared folders


Question 31 
Suppose we do not group by on any ports of the aggregator what will be the output.?
If we do not group values, the Integration Service will return only the last row for the input rows
Question 32 
What is the difference between variable port and  mapping variable?

Variable PortMapping Variable
Local to transformationLocal to mapping
Values are not persistentValues are persistent
It can’t be used with SQL overrideCan be used with SQL override

Question 33
What happens to a mapping if we alter the data types between Source and its corre
sponding Source Qualifier?
The Source Qualifier transformation displays the Informatica data types. The transformation data types determine how the source database binds data when the Integration Service reads it.N
ow if we alter the data types in the Source Qualifier transformation or the data types in the Source definition and Source Qualifier transformation do not match, the Designer marks the mapping as invalid when we save the mapping.

Question 34 
How to make the persistent lookup cache in sync with lookup table?
To make the persistent cache in sync with the lookup table simply enable Re-cache option of the lookup transformation to rebuild the lookup cache from lookup table again.
Question 35
Will session fail if the SELECT list COLUMNS in the Custom override SQL Query and the
Output Ports order in Source Qualifier transformation do not match?
Mismatch or changing the order of the list of selected columns in the SQL Query override of Source Qualifier to that of the connected transformation output ports may result is unexpected value result for ports if data types matches by chance, else will lead to session failure.
Question 36 
Can we use mapping/workflow variables in standalone email task?
No, we can only use pre-defined built in variables
Question 37
What are the characteristics of active transformation?
A transformation is active when it satisfies either of the following 4 conditions:

1. No of rows @Source is not equal to No of rows @Target.
2. Transactions boundaries for the input change.
3. Row type changes.
4. It changes the order of data
Question 38
Can we use $PMTargetName@numAffectedRows/@numAppliedRows/$PMTargetName@numRejectedRows in post session variable assignment?
We cant use these variable in post session variable assignment but we can use these variable in post session command tasks

Question 39 
Can we connect to Microsoft SQL Server database with Kerberos authentication from Informatica?
Yes we can connect to Microsoft SQL Server database with Kerberos authentication from Informatica 10 ,It requires few configuration changes

Question 40 
What is informatica domain ?
When you install and run the Informatica services, the installation is known as a node. The node becomes part of an Informatica domain. A domain is a grouping of one or more nodes. The domain forms the environment upon which the Informatica service processes run. A gateway node can also be a master gateway node.

Question 41 
What is informatica EBF ?
Emergency Bug Fix
Question 42
What is purpose of  option Synchronize Dynamic Cache in lookup?

It can be used when running multiple sessions updating the same target simultaneously to avoid integrity issue.This synchronization behavior is different only in case of insert as compared to Dynamic cache.For update it is same as Dynamic cache that is when rows marked for update are received the dynamic cache will be updated and NewLookupRow= 2.

Question 43 
How can you limit use of parameter files in informatica ?
It can be done by defining parameters in metadata table and then create a mapping to assign variable using setvariable and then these values can be passed to different session using post session assignment .

Question 44
How can you issue Operating system command in middle of mapping?
This can be achieved by using java transformation and using process builder java built in

Question 45
How does Sorted Input improves  Aggregator Transformation Performance?
Integration Service creates the index and data caches files in memory to process the Aggregator transformation. If the Integration Service requires more space as allocated for the index and data cache sizes in the transformation properties, it stores overflow values in cache files i.e. paging to disk.One way to increase session performance is to increase the index and data cache sizes in the transformation properties.But when we check Sorted Input the Integration Service uses memory to process an Aggregator transformation it does not use cache files.

Question 46
Can we call Stored Procedure in Source Qualifier query?
It is possible to use a stored procedure as a source as long as it returns a result set.
The result set returned to the PowerCenter by Microsoft SQL Server stored procedures are the last select statement executed in the procedure source code.Stored procedure of SQL server called directly in SQL Override at SQ transformation.
It is not possible to call a stored procedure of Oracle in the Source Qualifier SQL override. When you call the stored procedure a variable needs to be passed to collect the OUT TYPE of REF CURSOR,which is not possible to do in a Source Qualifier.A workaround is possible for oracle stored procedure using pre session task.

Question 47
What is the difference between $ and $$
$ represents session parameter/variable, you can declare this in the session level

$$ represents mapping parameter/variable, you can declare this under mapping designer tool

Question 48
What is the priority in source qualifier if we give filter condition (EMPNAME =10) and also sql override (EMPNO=20)
If we double click on source qualifier, we can see both the properties filter condition and sql override. The highest priority is sql override, it takes the condition EMPNO=20. If we don't provide sql override then it will take value from the filter condition.

Question 49
Can we connect more than one source to a single source qualifier?
we can connect more than one source to a single source qualifier. When you drag multiple sources,for each source you can see one source qualifier, you need manually delete all source qualifier except one and then all other sources ports you can connect to one source qualifier

Question 50
What is impacted mapping?
If any of the components in the mapping changed, example sources or targets or reusable transformations or mapplets then mapping becomes impacted. It gives in yellow color triangle symbol. We have to validate the mapping to disappear impacted symbol.


30 comments: