*BARC*
1. what sort of strategies used whil loading the data
2. types of source/target used
3. toughest mapping wrt data size
4. active/passive
Soln:
Active Transformation:- An active transformation can perform any of the following actions:
(a) Change the number of rows that pass through the transformation:- for eg, the Filter transformation is
active because it removes rows that do not meet the filter condition.
(b) Change the transaction boundary:- for eg, , the Transaction Control transformation is active because it defines a commit or roll back transaction based on an expression evaluated for each row.
(c) Change the row type:- for eg, the Update Strategy transformation is active because it flags rows for insert, delete, update, or reject.
Passive Transformation:- An Passive transformation which will satisfy all below conditions:
(a) Do not Change the number of rows that pass through the transformation
(b) Maintains the transaction boundary
(c) Maintains the row type.
5. Push Down Optimization
Soln:
Pushdown optimization is a way of load-balancing among servers in order to achieve optimal performance.In this technique we push the transformation logic into source or target database or both partially depending upon the situation.The Integration Service translates the transformation logic into SQL queries and sends the SQL queries to the source or the target database which executes the SQL queries to process the transformations. The amount of transformation logic one can push to the database depends on the database, transformation logic, and mapping and session configuration. The Integration Service analyzes the transformation logic it can push to the database and executes the SQL statement generated against the source or target tables, and it processes any transformation logic that it cannot push to the database.This option is available in the session.It has 3 types:
source-side,target-side & full.
Using source-side pushdown optimization:
The Integration Service pushes as much transformation logic as possible to the source database. The Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the source database and executes the corresponding SELECT statement.
Using target-side pushdown optimization:
The Integration Service pushes as much transformation logic as possible to the target database. The Integration Service analyzes the mapping from the target to the source or until it reaches an upstream transformation it cannot push to the target database. It generates an INSERT, DELETE, or UPDATE statement based on the transformation logic for each transformation it can push to the database and executes the DML
Using full pushdown optimisation:
The Integration Service pushes as much transformation logic as possible to both source and target databases. If you configure a session for full pushdown optimization, and the Integration Service cannot push all the transformation logic to the database, it performs source-side or target-side pushdown optimization instead. Also the source and target must be on the same database. The Integration Service analyzes the mapping starting with the source and analyzes each transformation in the pipeline until it analyzes the target.
When it can push all transformation logic to the database, it generates an INSERT SELECT statement to run on the database. The statement incorporates transformation logic from all the transformations in the mapping. If the Integration Service can push only part of the transformation logic to the database, it does not fail the session, it pushes as much transformation logic to the source and target database as possible and then processes the remaining transformation logic.
6. advantage of lookup over joiner
Soln : A joiner supports outer & equijoins(=).A lookup supports both equi & non-equijoins.(<=,>=,=).
In lookup we can use lookup override to fetch only required columns needed for join whereas there is no such provision in joiner transformation.
7. If the job failed and the reson in log is FATAL ERROR What will be the expected reason
8. in SQ if the mapping posts are wrongly mapped what will happen ? .. if it fails what will be the error shwon ?
9. Exception Handling
*HUDSON*
Below is the source file:
ID:
1
1
2
3
3
4
10. Need the output such that duplicates get eliminated
11. Need the output in below format such that unique records and duplicate records in separate targets but the count of record is same as the source:
Unique Duplicate
2 1
4 1
3
3
12. Below is the source file:
ID Tel num1 Tel num2 Tel num3
1 T1 T2 T3
Output should be in below format:
ID Tel num
1 T1
1 T2
1 T3
*RANDOM*
Simple questions
13. What are the different types of scd2 dimensions?
14. What is the difference between a mapping & mapplet?
15. What is a reusable transformation?
16. What are the different types of Commit intervals?
Soln : Source Commit Interval & Target Commit Interval.
17. What is sql override where do we use and which transformations?
Soln : If there is no sql override,Informatica by default selects all the columns from the table.If that is not required,we can write our own query to pull only required columns needed for our purpose.This also helps in improving performance.This functionality is called sql override.Its present in the source qualifier for relational tables.This feature is not available for flat files.We can do similar thing in lookup transformation as well.There it is called lookup override.
18. How many worklets can be defined within a workflow?
19. What are the settings that you use to configure the joiner transformation?
20. At the max how many tranformations can be used in a mapping?
21. Differences between connected & unconnected lookup.
22. What is meant by active & passive transformation?
23. What is the difference between router & filter?
Soln : Diff 1:
In Router ,we can specify more than one filter condition.In filter we can specify only one filter transformation.
Diff 2:
Router does not result in loss of data.The data that has not passed the filter conditions are passed through the default group.Filter transformation does not provide this option.
Diff 3:
Router transformation works like CASE statement in db.Filter acts like WHERE clause in db.
Diff 4:
Router is a single input & multi output group transformation.Filter is a single input & single output group transformation.
24. What are the different lookup cache(s)?
Soln : Static cache,Dynamic Cache & Persistent Cache.
Intermediate questions
25. What is DTM & Load Manager?
26. What are the different types of threads involved in DTM process?
27. What are the scheduling options to run a session?
28. What are the different components of informatica architecture?
29. What is the need of Informatica Cache?
30. What are the different types of Informatica variables?
31. What is the Difference between static cache and dynamic cache?
32. What are mapping parameters and varibles in which situation we can use it?
33. How can you complete unrcoverable sessions?
34. In which circumstances that Informatica server creates Reject files?
35. What is Code Page used for?
36. What is CDC?
37. Which one is better performance wise joiner or lookup?
38. How do you handle decimal places while importing a flatfile into Informatica?
39. How to retrieve the records from a rejected file? Explain with syntax or example.
40. What is the difference between PowerCenter and PowerMart?
41. When do you use a unconnected lookup and connected lookup?
42. How can we store previous session logs?
43. How to read rejected data or bad data from bad file and reload it to target?
44. In my source table 1000 rec's are there.I want to load 501 rec to 1000 rec into my Target table.How can you do this ?
45. What are the session parameters?
46. When do you use Normal Loading and the Bulk Loading, Tell the difference?
47. How can we use pmcmd command in a workflow or to run a session?
48. In case of use of dynamic look up cache in a lookup transformation, the lookup cache gets
updated first and then the target table. What happens if the target able rejects a row after it is updated in the Cache?
49. How to perform a "Loop Scope / Loop condition" in an Informatica program ? Give me few examples ?
50.What are the different performance tuning mechanisms in Informatica?
51.What can we do to improve the performance of Informatica Aggregator Transformation?
52. How can we update a record in target table without using Update strategy?
53. Under what condition selecting Sorted Input in aggregator may fail the session?
54 how to update data without using update strategy
55 how to implement scd-1 using only sql query
56 I have created a mapping and respective workflow/session. Again I created a Copy of original map, now I want to point existing session/workflow to newly created mapping. How shall I do it?
57 How do I restore the previous version of a particular mapping/session/workflow? What are the limitations of it?
58 How will you transfer CLOB Data to BLOB Data using informatica?
59 Explain any scenario of XML as a Source
60 Explain any scenario of XML as a Target
61 Explain any scenario of XML Parser
62 Explain any scenario of XML Generator
63 How will you use Oracle Sequence Generator in Informatica
64 What is a need of Dynamic Lookup
65 We need to find workflow/session execution parameters after successful or failed load/ [Sudip we did this one in one way and Fracture Bandhya told us the better way]
66 How will you create target files dynamically?
67 How will you re-create Persistent Lookup Files on a particular date?
68 How will you delete Duplicate records in informatica?
69 Explain the Load Ordering in Informmatica i.e. SQ, SQ Pre SQL, SQ Post SQL, Target Pre SQL, Target Post SQL
70 How will you migrate worflows from Prod to Dev at the time of development?
71 How will you view the dependancies of an object?
72 How and why you will perform labeling at Informatica?
73 some joiner related question which Mishra Ji has asked [like if sources are 2 flat files, how will u perform full outer join or something]
74 You have 2 session serially connected to each other. How will you pass Mapping Variable Value of first session to Mapping Variable Value of second session?
-- Infra Admin
75 How to delete the objects from Stg and Prod environment by migrating from Dev to Stg --> Prod
76 What is a difference between Perge and Delete
77 How to migrate from Stg to Pro using deployment group
78 How to find out latest checked in objects in particular folder using Unix Script
79 How will you compare Mappings and Folders
80 What are different queries to find out the Infra Objects [checked out/ version wise/Deleted/ all workflows-session/ to perform sanity check etc]
-- Scenario
81 We need to transfer latest checked in objects [most likely workflow] from Dev environment to Stg environment and have to run those respective workflows at Stg environment. We can use corn job to schedule the workflow execution.
82 We are having 100 source files, which we are ftping from different servers to Infra server. Now we have to merge those files into 10 different files and load into 1 target. How will u do that?
83 Partitioning when source is Flat File
84 Partitioning when Source is Oracle Target
85 You have Unicode character set[eg. Chinese] in source table and you need to bring that in at Teradata. Catch here is you are first pulling data into Flat File and after that you are inserting that to TD Table using Fast Load. What are Infra / TD level changes you need to do?
86 You have Resource Dim [whcich is SCD-2] and TimeCard Fact tables which are joined by Surrogate Key. Particular resource A got changed to Aa and new record got created at Dim table. Now how the Old and New record relationship be maintained between them?
*BARC*
87. What are the different loaders used in teradata.
Soln : TD fast load,multiload,Tpump.
88. How is fastload & multiload loader different from tpump.
Soln : fastload & multiload are faster than tpump.However they lock tables while
Executing.Tpump does not lock tables & can work in real-time environment.
89. Scenario : How can you generate dynamic files using informatica?ex : I need employee information
about different depts. in a separate file,which is all emp under dept 10 should come in one file,under dept 20 in a different file & so on.
What is a transaction control transformation & its properties.
Soln : tc_before_commit,tc_after_commit,tc_before_rollback,tc_after_rollback,tc_continue_transaction.
(explain all the above properties in detail)
90.What is target load plan?
Soln : We can set it at mapping level to determine the order targets will be loaded using informatica.
91.What is pushdown optimisation?
Soln : Pushes the transformation logic into source or target db or both & execute them in the form of sql query.has to be set at session property level.Can be source_side,tgt_side or full.
(Explain the above in detail)
92. What are the files created when you extract or load data into teradata from informatica.
Soln : Staging file or named pipe,ctrl file & log file.
93. What scheduling tool is used in your project?
Soln : I used $U as a scheduling tool. Its a Cisco tool.
94. What is pmcmd start workflow & how and where can you execute it with proper syntax?
Pmcmd startworkflow –sv integration servicename –d domain name –f folder name –wf workflowname(pls chk the syntax once)
We have to execute it in the bin folder of informatica.
95. Difference between connected & unconnected lookup.
Soln : Most imp diff : Unconnected lkp can return only one o/p port at a time whereas connected can return multiple.Other differences are also there.
96. Difference between lookup & joiner.
Soln : Joiner supports only equijoin but lkp supports both equijoin & non-equijoin.
97. How is aggregator an active transformation?
Soln : It changes the rowcount .hence active.(need to explain in detail)
98. Different join types in joiner transformation.Explain in detail.
Soln :Normal join,master outer join,detail outer join,full outer join.(Detail explanation reqd)
99. What will happen if I do not select “sorted input” in an aggregator transformation
& there is no sorter added before it?is it necessary for the data to be sorted in an
Aggregator transformation?if not,why?if yes,why?
Soln : Agg will not fail if sorted input is not used & no sorter is used before it.but agg itself internally will sort the data because index cache has to be sorted always.So if we do not sort explicitly,it might sometimes impact performance.
100. Scenario : Suppose a source contains 3,4 fields namely id,name,ph_no,salary.
I want to concatenate all these fields in a single field & place it in a flat file.
But there is a catch : If the field length exceeds 2000,the part after 2000,should
Move into a different field & if the length is less than 2000,it should be in the same field.
Soln : need to used expr transformation for concatenation.In the file file while creating choose fixed width option & give the length as 2000.(need to practically chk it once)
101. Informatica Architecture
102. Threads in Informatica(Busy Percentage 95 > meaning)
103. How to implement Cross Join in Informatica on Flat files.
104. What will happen if we select distinct option in Sorter(port level changes)
105. How to get session statistics in a flat file?
106. How to do re-cache from source in persistent lookup only once in a month (Workflow runs daily although)
107. Why do we need flat-file in td process(why not directly src > stg)
108. How to use a command(say echo 1) as source in informatica?
109. How to do recovery strategy without using recovery strategy option?
110. How to override Informatica variables(like Informatica root directory)
111.
Source
Id contact_type contact_number
1 Mobile 111
1 Home 222
1 Other 333
2 Home 555
2 Other 777
3 Other 888
Tgt
Load the tgt table based on the contact type only
Contact type preference : Mobile(if not found) > Home(If not found) > other
output
Tgt
Id contact_type contact_number
1 Mobile 111
2 Home 555
3 Other 888
112.One Directory has 3 flat files, named as below :
Abv_<today’s date>.dat
Abv_<yesterday’s date>.dat
Abv_<daybeforeyesterday_date>.dat
How will u pick latest file based on the date mentioned in the file name itself ?
113.Update without using update strategy.
114.Source :
A b c d e f g h(Single row having 8 cols as mentioned)
Tgt : (single row split into 2 rows having 4 cols each)
A b c d
E f g h
115. Suppose you have a source that contains data from 4 regions namely east,west,north & south.
Now the mapping is one,but it has 4 sessions.One session only gets activated at one time. For ex :
If east data is coming from source then one session will get activated.For west data another session & likewise.
How will u achive this using informatica.(Hint : need to swap mapping variable value with workflow variable)
==================================================================================
116.
Draw in details..with example scd1…
Change it to SCD2…
117.
Source tables:
T1, Cust name, cust address, cust id, city
T2, Zone, city
T3, Cust Id, Order DT, Order Amt
117.1
Develop Bi Model…(done as start schema)
117.2
Convert it to snowflake…
117.3
Now load source data using infa to above model
117.4
Now, load data to show what amount of order is placed by a cust in last one year…only at some specific zones(no query, src is flat file, so use informatica)
118.
UT/Integration testing…how to be done ?
119. TD loader, which loader to use when ? advantage/disad.
120. What is Click in TD?
121. Performance tuning..
122. we have 3 facts and 4 dimensions..tell the load strategy .. how to use persistent cache for the dimensional tables ?
123. Diff. between snowflake and star .. explain snowflake?
124. 2 tables having miilion rows joining thru joiner .. how to improve the performance ?
125. ways to improve the mapping performance ?
126. pmcmd cmd to run a session from a wf having 10 sessions ?
127. a info. job fialed , we dont have session log generated , what could be the issue for failur of job ?
128. 1000 rows read by source, no data laoded in target , what could be reason ? .. no fltr, router, aggregator Tr. used in mapping
129. Dynamic lookup example, where no other way..
130. Mapping variable can be used at session level, why those are called mapping var/param then..
131. What is New lookup rows in dynamic lookup..
132. What is associate port in lookup..
133. Structure of parameter file..
134. Can we call only session without a workflow from pmcmd command
***************************************
New Set by Avishek on email
Informatica Questions:
How many types of lookup cache are there?
How does persistent cache works?
How does dynamic cache works?
What’s the difference between connected and unconnected lookup?
If we disable the lookup cache, how does it work?
Can a same record be in multiple groups of a router?
How do Pre/Post Stored Procedures work?
How do we do Error logging and handle exceptions in Informatica?
How to prevent Informatica job from failing till a certain number of Errors encountered.
How to set Target Table load ordering if they are connected to the same pipeline?
How to update a target table if it does not have a primary key?
How does a Sequence generator connected to multiple targets behave?
How to get a same key from a Sequence generator to multiple targets?
What’s the difference between Informatica 8x and 9x?
If parameter file is defined at workflow level and session level, which would take precedence?
What is constraint based loading?
What are the direct and indirect methods for reading a flat file?
What is the difference between a router and a filter?
For a dynamic lookup cache what are the specific things to be care of
What is the syntax of PMCMD command?
Can we run a particular session in a workflow using the PMCMD command?
Oracle Questions:
Can we commit data inside a Trigger code?
What are conformed dimensions?
Difference between a view and a materialized view.
UNIX Questions:
What does ‘$?’ means?
What does ‘$#’ means?
What does ‘’$$’ mean?
What are the awk and sed commands?
Today's Sudip's questions
+ Scenario of Assignment task and creating session logs by workflow parameters
+ Batch file as a source instead of flat and relational source
+What is the format of workflow & session logs that Informatica creates?
Soln : The logs are created in .bin format.
+What is the function of "write backward compatible log file" option at worflow & session level in Informatica?
Soln : if we check this option logs are created in both .bin & .log format.The log format is a readable text format.
1. what sort of strategies used whil loading the data
2. types of source/target used
3. toughest mapping wrt data size
4. active/passive
Soln:
Active Transformation:- An active transformation can perform any of the following actions:
(a) Change the number of rows that pass through the transformation:- for eg, the Filter transformation is
active because it removes rows that do not meet the filter condition.
(b) Change the transaction boundary:- for eg, , the Transaction Control transformation is active because it defines a commit or roll back transaction based on an expression evaluated for each row.
(c) Change the row type:- for eg, the Update Strategy transformation is active because it flags rows for insert, delete, update, or reject.
Passive Transformation:- An Passive transformation which will satisfy all below conditions:
(a) Do not Change the number of rows that pass through the transformation
(b) Maintains the transaction boundary
(c) Maintains the row type.
5. Push Down Optimization
Soln:
Pushdown optimization is a way of load-balancing among servers in order to achieve optimal performance.In this technique we push the transformation logic into source or target database or both partially depending upon the situation.The Integration Service translates the transformation logic into SQL queries and sends the SQL queries to the source or the target database which executes the SQL queries to process the transformations. The amount of transformation logic one can push to the database depends on the database, transformation logic, and mapping and session configuration. The Integration Service analyzes the transformation logic it can push to the database and executes the SQL statement generated against the source or target tables, and it processes any transformation logic that it cannot push to the database.This option is available in the session.It has 3 types:
source-side,target-side & full.
Using source-side pushdown optimization:
The Integration Service pushes as much transformation logic as possible to the source database. The Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the source database and executes the corresponding SELECT statement.
Using target-side pushdown optimization:
The Integration Service pushes as much transformation logic as possible to the target database. The Integration Service analyzes the mapping from the target to the source or until it reaches an upstream transformation it cannot push to the target database. It generates an INSERT, DELETE, or UPDATE statement based on the transformation logic for each transformation it can push to the database and executes the DML
Using full pushdown optimisation:
The Integration Service pushes as much transformation logic as possible to both source and target databases. If you configure a session for full pushdown optimization, and the Integration Service cannot push all the transformation logic to the database, it performs source-side or target-side pushdown optimization instead. Also the source and target must be on the same database. The Integration Service analyzes the mapping starting with the source and analyzes each transformation in the pipeline until it analyzes the target.
When it can push all transformation logic to the database, it generates an INSERT SELECT statement to run on the database. The statement incorporates transformation logic from all the transformations in the mapping. If the Integration Service can push only part of the transformation logic to the database, it does not fail the session, it pushes as much transformation logic to the source and target database as possible and then processes the remaining transformation logic.
6. advantage of lookup over joiner
Soln : A joiner supports outer & equijoins(=).A lookup supports both equi & non-equijoins.(<=,>=,=).
In lookup we can use lookup override to fetch only required columns needed for join whereas there is no such provision in joiner transformation.
7. If the job failed and the reson in log is FATAL ERROR What will be the expected reason
8. in SQ if the mapping posts are wrongly mapped what will happen ? .. if it fails what will be the error shwon ?
9. Exception Handling
*HUDSON*
Below is the source file:
ID:
1
1
2
3
3
4
10. Need the output such that duplicates get eliminated
11. Need the output in below format such that unique records and duplicate records in separate targets but the count of record is same as the source:
Unique Duplicate
2 1
4 1
3
3
12. Below is the source file:
ID Tel num1 Tel num2 Tel num3
1 T1 T2 T3
Output should be in below format:
ID Tel num
1 T1
1 T2
1 T3
*RANDOM*
Simple questions
13. What are the different types of scd2 dimensions?
14. What is the difference between a mapping & mapplet?
15. What is a reusable transformation?
16. What are the different types of Commit intervals?
Soln : Source Commit Interval & Target Commit Interval.
17. What is sql override where do we use and which transformations?
Soln : If there is no sql override,Informatica by default selects all the columns from the table.If that is not required,we can write our own query to pull only required columns needed for our purpose.This also helps in improving performance.This functionality is called sql override.Its present in the source qualifier for relational tables.This feature is not available for flat files.We can do similar thing in lookup transformation as well.There it is called lookup override.
18. How many worklets can be defined within a workflow?
19. What are the settings that you use to configure the joiner transformation?
20. At the max how many tranformations can be used in a mapping?
21. Differences between connected & unconnected lookup.
22. What is meant by active & passive transformation?
23. What is the difference between router & filter?
Soln : Diff 1:
In Router ,we can specify more than one filter condition.In filter we can specify only one filter transformation.
Diff 2:
Router does not result in loss of data.The data that has not passed the filter conditions are passed through the default group.Filter transformation does not provide this option.
Diff 3:
Router transformation works like CASE statement in db.Filter acts like WHERE clause in db.
Diff 4:
Router is a single input & multi output group transformation.Filter is a single input & single output group transformation.
24. What are the different lookup cache(s)?
Soln : Static cache,Dynamic Cache & Persistent Cache.
Intermediate questions
25. What is DTM & Load Manager?
26. What are the different types of threads involved in DTM process?
27. What are the scheduling options to run a session?
28. What are the different components of informatica architecture?
29. What is the need of Informatica Cache?
30. What are the different types of Informatica variables?
31. What is the Difference between static cache and dynamic cache?
32. What are mapping parameters and varibles in which situation we can use it?
33. How can you complete unrcoverable sessions?
34. In which circumstances that Informatica server creates Reject files?
35. What is Code Page used for?
36. What is CDC?
37. Which one is better performance wise joiner or lookup?
38. How do you handle decimal places while importing a flatfile into Informatica?
39. How to retrieve the records from a rejected file? Explain with syntax or example.
40. What is the difference between PowerCenter and PowerMart?
41. When do you use a unconnected lookup and connected lookup?
42. How can we store previous session logs?
43. How to read rejected data or bad data from bad file and reload it to target?
44. In my source table 1000 rec's are there.I want to load 501 rec to 1000 rec into my Target table.How can you do this ?
45. What are the session parameters?
46. When do you use Normal Loading and the Bulk Loading, Tell the difference?
47. How can we use pmcmd command in a workflow or to run a session?
48. In case of use of dynamic look up cache in a lookup transformation, the lookup cache gets
updated first and then the target table. What happens if the target able rejects a row after it is updated in the Cache?
49. How to perform a "Loop Scope / Loop condition" in an Informatica program ? Give me few examples ?
50.What are the different performance tuning mechanisms in Informatica?
51.What can we do to improve the performance of Informatica Aggregator Transformation?
52. How can we update a record in target table without using Update strategy?
53. Under what condition selecting Sorted Input in aggregator may fail the session?
-- Infra
54 how to update data without using update strategy
55 how to implement scd-1 using only sql query
56 I have created a mapping and respective workflow/session. Again I created a Copy of original map, now I want to point existing session/workflow to newly created mapping. How shall I do it?
57 How do I restore the previous version of a particular mapping/session/workflow? What are the limitations of it?
58 How will you transfer CLOB Data to BLOB Data using informatica?
59 Explain any scenario of XML as a Source
60 Explain any scenario of XML as a Target
61 Explain any scenario of XML Parser
62 Explain any scenario of XML Generator
63 How will you use Oracle Sequence Generator in Informatica
64 What is a need of Dynamic Lookup
65 We need to find workflow/session execution parameters after successful or failed load/ [Sudip we did this one in one way and Fracture Bandhya told us the better way]
66 How will you create target files dynamically?
67 How will you re-create Persistent Lookup Files on a particular date?
68 How will you delete Duplicate records in informatica?
69 Explain the Load Ordering in Informmatica i.e. SQ, SQ Pre SQL, SQ Post SQL, Target Pre SQL, Target Post SQL
70 How will you migrate worflows from Prod to Dev at the time of development?
71 How will you view the dependancies of an object?
72 How and why you will perform labeling at Informatica?
73 some joiner related question which Mishra Ji has asked [like if sources are 2 flat files, how will u perform full outer join or something]
74 You have 2 session serially connected to each other. How will you pass Mapping Variable Value of first session to Mapping Variable Value of second session?
-- Infra Admin
75 How to delete the objects from Stg and Prod environment by migrating from Dev to Stg --> Prod
76 What is a difference between Perge and Delete
77 How to migrate from Stg to Pro using deployment group
78 How to find out latest checked in objects in particular folder using Unix Script
79 How will you compare Mappings and Folders
80 What are different queries to find out the Infra Objects [checked out/ version wise/Deleted/ all workflows-session/ to perform sanity check etc]
-- Scenario
81 We need to transfer latest checked in objects [most likely workflow] from Dev environment to Stg environment and have to run those respective workflows at Stg environment. We can use corn job to schedule the workflow execution.
82 We are having 100 source files, which we are ftping from different servers to Infra server. Now we have to merge those files into 10 different files and load into 1 target. How will u do that?
83 Partitioning when source is Flat File
84 Partitioning when Source is Oracle Target
85 You have Unicode character set[eg. Chinese] in source table and you need to bring that in at Teradata. Catch here is you are first pulling data into Flat File and after that you are inserting that to TD Table using Fast Load. What are Infra / TD level changes you need to do?
86 You have Resource Dim [whcich is SCD-2] and TimeCard Fact tables which are joined by Surrogate Key. Particular resource A got changed to Aa and new record got created at Dim table. Now how the Old and New record relationship be maintained between them?
*BARC*
87. What are the different loaders used in teradata.
Soln : TD fast load,multiload,Tpump.
88. How is fastload & multiload loader different from tpump.
Soln : fastload & multiload are faster than tpump.However they lock tables while
Executing.Tpump does not lock tables & can work in real-time environment.
89. Scenario : How can you generate dynamic files using informatica?ex : I need employee information
about different depts. in a separate file,which is all emp under dept 10 should come in one file,under dept 20 in a different file & so on.
What is a transaction control transformation & its properties.
Soln : tc_before_commit,tc_after_commit,tc_before_rollback,tc_after_rollback,tc_continue_transaction.
(explain all the above properties in detail)
90.What is target load plan?
Soln : We can set it at mapping level to determine the order targets will be loaded using informatica.
91.What is pushdown optimisation?
Soln : Pushes the transformation logic into source or target db or both & execute them in the form of sql query.has to be set at session property level.Can be source_side,tgt_side or full.
(Explain the above in detail)
92. What are the files created when you extract or load data into teradata from informatica.
Soln : Staging file or named pipe,ctrl file & log file.
93. What scheduling tool is used in your project?
Soln : I used $U as a scheduling tool. Its a Cisco tool.
94. What is pmcmd start workflow & how and where can you execute it with proper syntax?
Pmcmd startworkflow –sv integration servicename –d domain name –f folder name –wf workflowname(pls chk the syntax once)
We have to execute it in the bin folder of informatica.
95. Difference between connected & unconnected lookup.
Soln : Most imp diff : Unconnected lkp can return only one o/p port at a time whereas connected can return multiple.Other differences are also there.
96. Difference between lookup & joiner.
Soln : Joiner supports only equijoin but lkp supports both equijoin & non-equijoin.
97. How is aggregator an active transformation?
Soln : It changes the rowcount .hence active.(need to explain in detail)
98. Different join types in joiner transformation.Explain in detail.
Soln :Normal join,master outer join,detail outer join,full outer join.(Detail explanation reqd)
99. What will happen if I do not select “sorted input” in an aggregator transformation
& there is no sorter added before it?is it necessary for the data to be sorted in an
Aggregator transformation?if not,why?if yes,why?
Soln : Agg will not fail if sorted input is not used & no sorter is used before it.but agg itself internally will sort the data because index cache has to be sorted always.So if we do not sort explicitly,it might sometimes impact performance.
100. Scenario : Suppose a source contains 3,4 fields namely id,name,ph_no,salary.
I want to concatenate all these fields in a single field & place it in a flat file.
But there is a catch : If the field length exceeds 2000,the part after 2000,should
Move into a different field & if the length is less than 2000,it should be in the same field.
Soln : need to used expr transformation for concatenation.In the file file while creating choose fixed width option & give the length as 2000.(need to practically chk it once)
101. Informatica Architecture
102. Threads in Informatica(Busy Percentage 95 > meaning)
103. How to implement Cross Join in Informatica on Flat files.
104. What will happen if we select distinct option in Sorter(port level changes)
105. How to get session statistics in a flat file?
106. How to do re-cache from source in persistent lookup only once in a month (Workflow runs daily although)
107. Why do we need flat-file in td process(why not directly src > stg)
108. How to use a command(say echo 1) as source in informatica?
109. How to do recovery strategy without using recovery strategy option?
110. How to override Informatica variables(like Informatica root directory)
111.
Source
Id contact_type contact_number
1 Mobile 111
1 Home 222
1 Other 333
2 Home 555
2 Other 777
3 Other 888
Tgt
Load the tgt table based on the contact type only
Contact type preference : Mobile(if not found) > Home(If not found) > other
output
Tgt
Id contact_type contact_number
1 Mobile 111
2 Home 555
3 Other 888
112.One Directory has 3 flat files, named as below :
Abv_<today’s date>.dat
Abv_<yesterday’s date>.dat
Abv_<daybeforeyesterday_date>.dat
How will u pick latest file based on the date mentioned in the file name itself ?
113.Update without using update strategy.
114.Source :
A b c d e f g h(Single row having 8 cols as mentioned)
Tgt : (single row split into 2 rows having 4 cols each)
A b c d
E f g h
115. Suppose you have a source that contains data from 4 regions namely east,west,north & south.
Now the mapping is one,but it has 4 sessions.One session only gets activated at one time. For ex :
If east data is coming from source then one session will get activated.For west data another session & likewise.
How will u achive this using informatica.(Hint : need to swap mapping variable value with workflow variable)
==================================================================================
116.
Draw in details..with example scd1…
Change it to SCD2…
117.
Source tables:
T1, Cust name, cust address, cust id, city
T2, Zone, city
T3, Cust Id, Order DT, Order Amt
117.1
Develop Bi Model…(done as start schema)
117.2
Convert it to snowflake…
117.3
Now load source data using infa to above model
117.4
Now, load data to show what amount of order is placed by a cust in last one year…only at some specific zones(no query, src is flat file, so use informatica)
118.
UT/Integration testing…how to be done ?
119. TD loader, which loader to use when ? advantage/disad.
120. What is Click in TD?
121. Performance tuning..
122. we have 3 facts and 4 dimensions..tell the load strategy .. how to use persistent cache for the dimensional tables ?
123. Diff. between snowflake and star .. explain snowflake?
124. 2 tables having miilion rows joining thru joiner .. how to improve the performance ?
125. ways to improve the mapping performance ?
126. pmcmd cmd to run a session from a wf having 10 sessions ?
127. a info. job fialed , we dont have session log generated , what could be the issue for failur of job ?
128. 1000 rows read by source, no data laoded in target , what could be reason ? .. no fltr, router, aggregator Tr. used in mapping
129. Dynamic lookup example, where no other way..
130. Mapping variable can be used at session level, why those are called mapping var/param then..
131. What is New lookup rows in dynamic lookup..
132. What is associate port in lookup..
133. Structure of parameter file..
134. Can we call only session without a workflow from pmcmd command
***************************************
New Set by Avishek on email
Informatica Questions:
How many types of lookup cache are there?
How does persistent cache works?
How does dynamic cache works?
What’s the difference between connected and unconnected lookup?
If we disable the lookup cache, how does it work?
Can a same record be in multiple groups of a router?
How do Pre/Post Stored Procedures work?
How do we do Error logging and handle exceptions in Informatica?
How to prevent Informatica job from failing till a certain number of Errors encountered.
How to set Target Table load ordering if they are connected to the same pipeline?
How to update a target table if it does not have a primary key?
How does a Sequence generator connected to multiple targets behave?
How to get a same key from a Sequence generator to multiple targets?
What’s the difference between Informatica 8x and 9x?
If parameter file is defined at workflow level and session level, which would take precedence?
What is constraint based loading?
What are the direct and indirect methods for reading a flat file?
What is the difference between a router and a filter?
For a dynamic lookup cache what are the specific things to be care of
What is the syntax of PMCMD command?
Can we run a particular session in a workflow using the PMCMD command?
Oracle Questions:
Can we commit data inside a Trigger code?
What are conformed dimensions?
Difference between a view and a materialized view.
UNIX Questions:
What does ‘$?’ means?
What does ‘$#’ means?
What does ‘’$$’ mean?
What are the awk and sed commands?
Today's Sudip's questions
+ Scenario of Assignment task and creating session logs by workflow parameters
+ Batch file as a source instead of flat and relational source
+What is the format of workflow & session logs that Informatica creates?
Soln : The logs are created in .bin format.
+What is the function of "write backward compatible log file" option at worflow & session level in Informatica?
Soln : if we check this option logs are created in both .bin & .log format.The log format is a readable text format.
