What are two types of processes that informatica runs the session?
Load manager Process: Starts the session, creates the DTM process, and sends post-session
email when the session completes.
The DTM process: Creates threads to initialize the session, read, write, and transform data,
What are mapping parameters and variables in which situation we can use it ?
If we need to change certain attributes of a mapping after every time the session is run, it will be very difficult to edit the mapping and then change the attribute. So we use mapping parameters and variables and define the values in a parameter file. Then we could edit the parameter file to change the attribute values. This makes the process simple.
Mapping parameter values remain constant. If we need to change the parameter value then we needs to edit the parameter file.
But value of mapping variables can be changed by using variable function. If we need to increment the attribute value by 1 after every session run then we can use mapping variables.
In a mapping parameter we need to manually edit the attribute value in the parameter file after every session run.
What is the method of loading 5 flat files of having same structure to a single target and which transformations I can use?
Two Methods.
1. Write all files in one directory then use file repository concept (don’t forget to type source file
type as indirect in the session).
2. Use union transformation to combine multiple input files into a single target.
In which circumstances that informatica server creates Reject files?
When it encounters the DD_Reject in update strategy transformation. Violates database constraint
Field in the rows was truncated or overflown.
What is the default join that source qualifier provides?
Inner equi join.
What is the difference between Stored Procedure (DB level) and Stored proc trans (INFORMATICA level) ? Why should we use SP trans ?
First of all stored procedures (at DB level) are series of SQL statement. And those are stored and compiled at the server side. In the Informatica it is a transformation that uses same stored procedures which are stored in the database. Stored procedures are used to automate time- consuming tasks that are too complicated for standard SQL statements. if you don't want to use the stored procedure then you have to create expression transformation and do all the coding in it.
0 comments
Email This BlogThis! Share to Twitter Share to Facebook Share to Google Buzz
Informatica Interview Questions - Part 9
What are variable ports and list two situations when they can be used?
We have mainly tree ports Import, Output, Variable port. Import represents data is flowing into transformation. Out port is used when data is mapped to next transformation. Variable port is used when we mathematical calculations are required.
This is a scenario in which the source has 2 columns
10 A 10 A 20 C 30 D 40 E 20 C
and there should be 2 targets one to show the duplicate values and another target for distinct rows.
T1 T2 10 A 10 A 20 C 20 C 30 D
which transformation can be used to load data into target?
40 E
Step1: sort the source data based on the unique key.
Expression:
Flag= iif(col1 =prev_col1,'Y','N') prev_col1 = col1
Router:
1.for duplicate record: condition: falg = 'Y' 2. For distinct Records condition
flag = 'N'
What r the types of lookup caches?
1) Static Cache 2) Dynamic Cache 3) Persistent Cache 4) Reusable Cache
5) Shared Cache
What are the real times problems that generally come up while doing/running mapping/any transformation? Explain with an example?
Here are few real time examples of problems while running informatica mappings:
1) Informatica uses OBDC connections to connect to the databases.The database passwords
(production) is changed in a periodic manner and the same is not updated at the Informatica side. Your mappings will fail in this case and you will get database connectivity error.
2) If you are using Update strategy transformation in the mapping, in the session properties
you have to select Treat Source Rows: Data Driven. If we do not select this Informatica server will ignore updates and it only inserts rows.
3) If we have mappings loading multiple target tables we have to provide the Target Load Plan
in the sequence we want them to get loaded.
4) Error: Snapshot too old is a very common error when using Oracle tables. We get this error
while using too large tables. Ideally we should schedule these loads when server is not very busy (meaning when no other loads are running).
5) We might get some poor performance issues while reading from large tables. All the source
tables should be indexed and updated regularly.
Informatica Interview Questions - Part 8
Is sorter an active or passive transformation? What happens if we uncheck the distinct option in sorter? Will it be under active or passive transformation?
Sorter is an active transformation. if you don't check the distinct option it is considered as a passive transformation. Because this distinct option eliminates the duplicate records from the table.
How can we partition a session in Informatica?
Partitioning option optimizes parallel processing on multiprocessor hardware by providing a thread-based architecture and built-in data partitioning. GUI-based tools reduce the
development effort necessary to create data partitions and streamline ongoing troubleshooting and performance tuning tasks, while ensuring data integrity throughout the execution process. As the amount of data within an organization expands and real-time demand for information grows, the Power Center Partitioning option enables hardware and applications to provide outstanding performance and jointly scale to handle large volumes of data and users.
In update strategy target table or flat file which gives more performance? Why?
will be in ASCII mode.
Cons: There is no concept of updating existing records in flat file. As there is no indexes, while
lookups speed will be lesser.
What is the difference between constraint base load ordering and target load plan ?
Constraint based load ordering
Example:
Table 1---Master Take 2---Detail
If the data in Table-1 is dependent on the data in Table-2 then Table-2 should be loaded first. In such cases to control the load order of the tables we need some conditional loading which is nothing but constraint based load. In Informatica this feature is implemented by just one check box at the session level.
What is parameter file?
When you start a workflow, you can optionally enter the directory and name of a parameter file. The Informatica Server runs the workflow using the parameters in the file you specify.
For UNIX shell users, enclose the parameter file name in single quotes:
-paramfile '$PMRootDir/myfile.txt'
For Windows command prompt users, the parameter file name cannot have beginning or trailing spaces. If the name includes spaces, enclose the file name in double quotes:
-paramfile ?$PMRootDirmy file.txt?
Note: When you write a pmcmd command that includes a parameter file located on another
machine, use the backslash () with the dollar sign ($). This ensures that the machine where the variable is defined expands the server variable.
Pmcmd startworkflow -UV USERNAME -PV PASSWORD -s SALES: 6258 -f east -w wSalesAvg -paramfile '$PMRootDir/myfile.txt'