Interview Questionary Set 2

26. What is aggregate transformation and what is use of sorted input?
The Aggregator transformation is used to perform aggregate calculations, such as averages, sums, mean, median etc. This is used for rows level calculations based on group by.

By selecting sorted input we are informing Integration Service that the input data is already in sorted. The sorted input option is improve session performance.

27. Why we use joiner transformation, what are different types of joins?

Joiner transformation is used to join source data from two related heterogeneous source residing in different locations or file systems. It can also used to join the source from same database. To join the source it should have at least one matching column.

Types of joins:

Normal Join: It discards all rows of data from the both master and detail source that do not match, based on the condition.
Master Join: A master outer join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
Detail Join: A detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
Full Outer Join: A full outer join keeps all rows of data from both the master and detail sources.

28. What is RANK transformation and usage of group by port in it?

The Rank transformation is used to rank top or bottom for source data.

The use of group by port in it is to get the top or bottom rank from each group instead of complete data.

29. What is performance tuning?

The performance tuning is a process to optimize session performance by eliminating performance bottlenecks. To tune session performance, first identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you are satisfied with the session performance. You can use the test load option to run sessions when you tune session performance.

30. Difference between stop and abort in Informatica?

Stop: It will stop the process by loading the loading the records in buffer which are fetch after the last commit point.
Abort: It will stop the process immediately.

31. What is Persistent Lookup cache? What is its significance?

Persistent Lookup caches is which we can save the lookup cache files once and reuse them the next time the Integration Service processes a Lookup transformation configured to use the cache.

Significance:

• If you want to save the cache files and reuse, you can configure the transformation to use a persistent cache. Use a persistent cache when you know the lookup table does not change between session runs.
• The first time the Integration Service runs a session using a persistent lookup cache, it saves the cache files to disk instead of deleting them. The next time the Integration Service runs the session, it builds the memory cache from the cache files. If the lookup table changes occasionally, you can override session properties to re-cache the lookup from the database.
• When you use a persistent lookup cache, you can specify a name for the cache files. When you specify a named cache, you can share the lookup cache across sessions.

32. How the Informatica server sorts the string values in Rank transformation?
If the Integration Service runs in the ASCII data movement mode, it sorts session data using a binary sort order.

If the Integration Service runs in Unicode data movement mode, the Integration Service uses the sort order configured for the session. You select the session sort order in the session properties. The session properties lists all available sort orders based on the code page used by the Integration Service.

33. What is Normal and Bulk load?

In normal loading, it loads record by record and writes log for that. It takes comparatively a longer time to load data to the target in normal loading.
In bulk loading, it loads number of records at a time to target database and do not writes log for that. It takes less time to load data to target.

34. What is Constraint Based load and Target Load Plan?

Constraint Based Load: In Constraint based load the data was loaded based on constraints. First the data was loaded into Parent table then the Child table based constraints defined.
Target Load Plan: In Target load plan we specify the order in which the Integration Service load the data to the Target instance.

Note: We will define Target Load Plan in case we have more than one flow in the mapping.

35. Is sorter an active or passive transformation? When do we consider it to be active and passive?

The sorter transformation is an active transformation by default. But you can configure it as a passive transformation.

• In properties tab, if you select distinct option then it will acts like active.
• In port tab, if you select one or more port for only to ascending or descending order then it will like passive.

36. Is SQL an active or passive transformation? When do we consider it to be active and passive?
The SQL transformation is an active transformation by default. But you can configure it as a passive transformation when you create the transformation.

Passive: When you configure the transformation to run in script mode, you create a passive transformation. The transformation returns one row for each input row. The output row contains results of the query and any database error.

Active: When you configure the SQL transformation to run in query mode, you create an active transformation. The transformation can return multiple rows for each input row

37. Using Informatica, how to update target table which don’t have any primary key in database?

We can do this in 2 ways:
1. By considering any port of the target instance as Primary Key. And at session level select Treat Source Rows as “update”

2. By Target override with syntax: TU

Example: UPDATE T_SALES SET EMP_NAME = :TU.EMP_NAME, DATE_SHIPPED = :TU.DATE_SHIPPED, TOTAL_SALES = :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID 

38. I have used one param file at workflow level and another at session. Where the param file at workflow having global values and param file at session level having mapping variable. Is it possible to use values from both param file, If  yes how?
A: By adding $PMMergeSessParamFile=TRUE in the param file defined at workflow.

39. What are the output files that the Informatica server creates during running a workflow/session?

The PowerCenter Integration Service creates the following output files:

• Workflow log
• Session log
• Session details file
• Performance details file
• Reject files
• Row error logs
• Recovery tables and files
• Control file
• Post-session email
• Output file
• Cache files

40. What is an Active and Passive transformation?
Active: An active transformation can change the number of rows that passes through the transformation. It changes the transaction boundary and change the row type.
Passive: A passive transformation does not change the number of rows that passes through the transformation. It maintains the transaction boundary and maintains the rows type.

41. What are error tables in Informatica are and how we do error handling in Informatica?

Error handling in Informatica:
Error Handling settings allow you to determine if the session fails or continues when it encounters pre-session command errors, stored procedure errors, or a specified number of session errors.

42. What is difference between IIF and DECODE function?

You can use nested IIF statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is zero or negative:

IIF( SALES > 0, IIF( SALES < 50, SALARY1, IIF( SALES < 100, SALARY2, IIF( SALES < 200, SALARY3, BONUS))), 0 )

You can use DECODE instead of IIF in many cases. DECODE may improve readability. The following shows how you can use DECODE:

DECODE(

SALES > 0 and SALES < 50, SALARY1,

SALES > 49 AND SALES < 100, SALARY2,

SALES > 99 AND SALES < 200, SALARY3,

SALES > 199, BONUS)

43. How to import oracle sequence into Informatica?

The same way we import relation source. In the Source Analyzer, click Sources à Import from Database.

44. How many way you can create source or target instance in designer?

The source definition can be created in 2 ways:
1. In the Source Analyzer, click Sources à Import from Database/File/Cobol File/XML Definition.
2. In the Source Analyzer, click Sources à Create

The target definition can be creating in 4 ways:

1. In the Target Designer, click Target à Import from Database/File/ XML Definition. 
2. In the Target Designer, click Target à Create
3. Creating a Target Definition from a Source Definition
4. Creating a Target Definition from a Transformation

45. What is parameter file?

A parameter file contains a list of parameters and variables with assigned values. Group parameters and variables in different sections of the parameter file.

Each section is preceded by a heading that identifies the Integration Service, Integration Service process, workflow, worklet, or session to which you want to define parameters or variables.

We define parameters and variables directly below the heading, entering each parameter or variable on a new line. You can list parameters and variables in any order within a section.

46. How will you create header/trailer record in target file using Informatica?

Header: Header can be created in 3 ways:
1. In Session Mapping tab: Header Options à Output File Names
2. In Session Mapping tab: Header Options à Use header command output (The shell command whose output will be used as the header.)
3. Manually created in Mapping.

Trailer: Trailer can be created in 2 ways:

1. In Session Mapping tab: Footer Command (The shell command whose output will be used as the footer, which will be appended to the output data. No footer if the command is empty.)
2. Manually created in Mapping.

47. What is mapping parameter and mapping variable?

The mapping parameters and variables to make mappings more flexible. Mapping parameters and variables represent values in mappings and mapplets. If you declare mapping parameters and variables in a mapping, you can reuse a mapping by altering the parameter and variable values of the mapping in the session. This can reduce the overhead of creating multiple mappings when only certain attributes of a mapping need to be changed.

48. What are the session parameters?

Session Parameters are 2 types, user-defined and built-in session parameters.

User-defined:

$PMSessionLogFile: Defines the name of the session log between session runs.
$DynamicPartitionCount: Defines the number of partitions for a session.
$InputFileName: Defines a source file name.
$LookupFileName: Defines a lookup file name.
$OutputFileNames: Defines a target file name.
$BadFileName: Defines a reject file name.
$DBConnectionName: Defines a relational database connection for a source, target, lookup, or stored procedure.
$LoaderConnectionName: Defines external loader connections.
$FTPConnectionName: Defines FTP connections.
$QueueConnectionName: Defines database connections for message queues.
$AppConnectionName: Defines connections to source and target applications.
$ParamName: Defines any other session property. For example, you can use this parameter to define a table owner name, table name prefix, FTP file or directory name, lookup cache file name prefix, or email address. You can use this parameter to define source, lookup, target, and reject file names, but not the session log file name or database connections.

Built-in:

$PMFolderName: Returns the folder name.
$PMIntegrationServiceName: Returns the Integration Service name.
$PMMappingName: Returns the mapping name.
$PMRepositoryServiceName: Returns the Repository Service name.
$PMRepositoryUserName: Returns the repository user name.
$PMSessionName: Returns the session name.
$PMSessionRunMode: Returns the session run mode (normal or recovery).
$PMSourceQualifierName@numAffectedRows: Returns the number of rows the Integration Service successfully read from the named Source Qualifier.
$PMSourceQualifierName@numAppliedRows: Returns the number of rows the Integration Service successfully read from the named Source Qualifier.
$PMSourceQualifierName@numRejectedRows: Returns the number of rows the Integration Service dropped when reading from the named Source Qualifier.
$PMSourceName@TableName: Returns the table name for the named source instance.
$PMTargetName@numAffectedRows: Returns the number of rows affected by the specified operation for the named target instance.
$PMTargetName@numAppliedRows: Returns the number of rows the Integration Service successfully applied to the named target instance.
$PMTargetName@numRejectedRows: Returns the number of rows the Integration Service rejected when writing to the named target instance.
$PMTargetName@TableName: Returns the table name for the named target instance.
$PMWorkflowName: Returns the workflow name.
$PMWorkflowRunId: Returns the workflow run ID.
$PMWorkflowRunInstanceName: Returns the

49. Where does Informatica store rejected data? How do we view them?

By default, the Integration Service names store the rejected data to the reject file after the target instance name: target_name.bad. Optionally, use the $BadFileName session parameter for the file name.

50. What is difference between partitioning of relational target and file targets?

Relational Target Partitioning: The Integration Service creates a separate connection to the target database for each partition at the target instance. It concurrently loads data for each partition into the target database.

File Target Partitioning: You can write the target output to a separate file for each partition or to a merge file that contains the target output for all partitions. When you run the session, the Integration Service writes to the individual output files or to the merge file concurrently. You can also send the data for a single partition or for all target partitions to an operating system command.