Informatica interview questions (Part 2)

We will continue with second set of questions Q 1. What is the difference between Connected and Unconnected Lookups?Ans : Connected Lookup 1.Connected Lookup Receives input values directly from the pipeline. 2.Connected Lookup You can use a dynamic or static cache.3.Connected Lookup Cache includes all lookup columns used in the mapping 4.Connected Lookup Can return multiple columns from the same row or insert into the dynamic lookup cache.5.Connected Lookup If there is no match for the lookup condition, the Informatica Server returns the default...

Read More

Informatica interview questions (Part 1)

Following is the list of some of Informatica interview questions.We will try to come up with more questions in later posts.Q.1 Flat file is having 10 records as input and I want to push 5 records to the target?Ans : one variable with the name $$count at mapping variable with a Count aggregation typeand initial value for that $$count=0. Create one expression and use SETCOUNTVARIABLEv_count=SetCountVariable ($$Count) Create one filter after expression and put condition v_count <=5Q2 What is difference between direct and indirect loading options...

Read More

Trigger Firing sequence in D2K (Oracle Forms)

Trigger Firing sequence:This is most important thing to understand in Oracle D2K Forms When you open a form following triggers are executedFirst Logon Triggers are fired1.PRE-LOGON2.ON-LOGON3.POST-LOGONAfter that Pre Triggers4. PRE-FORM5. PRE-BLOCK6. PRE-TEXTAfter that WHEN-NEW Triggers7. WHEN-NEW-FORM-INSTANCE8. WHEN-NEW-BLOCK-INSTANCE9. WHEN-NEW-ITEM-INSTANCEAfter that ITEM Level TriggersAfter this focus is on the first item of the Block. If you type some data and press the tab key following trigger will fire in sequence10.KEY-NEXT-ITEM (This...

Read More

Normalizer transformation (Working with VSAM source)

Normalizer transformation :Normalizer transformation is used with COBOL sources, which are often stored in a denormalized format. The OCCURS statement in a COBOL file nests multiple records of information in a single record. We can use Normalizer transformation, to break out repeated data within a record into separate records. For each new record it creates, the Normalizer transformation generates a unique identifier.Step 1: Create the Copybook for...

Read More

All About Oracle Synonym

Synonym : A synonym is an alias for table, stored procedure and other database objects.Main use of synonym :1. Hide Complexity : If you have to access a object owned by another database user thenYou have to use syntax Owner.Objectname every time to use that object. Instead of using cumbersome syntax every time we can create synonym for thatCreate Synonym Syn_Name for Owner.ObjectnameSuppose you have to access Table Tab_Y owner by user User_Y .Before creating synonym you have to useSelect * from User_Y.Tab_YNow if we create a synonymCreate Synonym...

Read More

New features of Informatica 8

Enhancments in informatica 8.6 Version :Target from Transformation :In Infa 8 we can create target from transformation by dragging transformation in Target designerPushdown optimization : Uses increased performance by pushing transformation logic to the database by analyzing the transformations and issuing SQL statements to sources and targets. Only processes any transformation logic that it cannot push to the database.New function in expression editor :New function have been introduced in informatica 8 like reg_extract and reg_matchRepository...

Read More

Inline view

Inline view :An inline view is term given to sub query in FROM clause of query which can be used as table. Inline view effectively is a named sub queryEx : Select Tab1.col1,Tab1.col.2,Inview.col1,Inview.Col2 From Tab1, (Select statement) InviewWhere Tab1.col1=Inview.col1SELECT DNAME, ENAME, SAL FROM EMP ,(SELECT DNAME, DEPTNO FROM DEPT) DWHERE A.DEPTNO = B.DEPTNOIn the above query (SELECT DNAME, DEPTNO FROM DEPT) D is the inline view. Inline views are determined at runtime, and in contrast to normal view they are not stored in the data...

Read More

Star Vs Snowflake Schema

In continuation to my last post we will continue with dimensional modeling in detailIn dimension modeling there are mainly two types of schemas1.Star Schema2.Snowflake SchemaStar Schema :Star schema is simplest data warehouse schema .It is called star schema because ER diagram of this schema looks like star with points originating from center. Center of star schema consists of large fact table and points of star are dimensional table.Star schema...

Read More

Dimensional Data Modeling

Dimensional Data Modeling :It is a modeling technique used in data warehousing systems. It is different from ER modeling technique used in OLTP systems. In Dimensional modeling a model of tables is combined together with aim of optimized query performance in Decision Support systems in relational databases.Before further going into details of dimensional data modeling we should be aware of key concepts in Dimensional Data ModelingDimension : It can be considered as category of information. For example geographic dimension. It provides a way to...

Read More

High Water Mark (Oracle)

High-water mark High-water mark indicates the point up to which table or index has ever contained data.Let me explain .Suppose we create an empty table,the high-water mark would be at the start of the table segmentNow if we insert some data into the table segment, the HWM moves and it will be at position up to which the data is in the segmentIf insert more data in the table segment, HWM moves further to point the position up to which the data is...

Read More

All About Oracle Sequences

Oracle sequenceOracle sequence is a database object that is used to generate unique numbers,It is mainly used for primary key values.SyntaxSyntax for creating sequence isCREATE SEQUENCE sequenceINCREMENT BY numberSTART WITH numberMAXVALUE numberMINVALUECYCLE/NOCYCLECACHE/NOCACHEORDER/NOORDERImportant option need to be consideredCYCLE/NOCYCLE:If we specify cycle option while creating sequence then it will continue to generate values even after reaching it's max value. It will again start with min value. Nocycle means it will not generate value after...

Read More

How to transpose data in Informatica

Suppose we are having input data coming asFirstname1Ph1Address1Firstname2Ph2Address2Firstname3Ph3Address3You want data in output like i.e. you want to convert 9 rows into 3 rows with similar group togetherFirstname1 Ph1 Address1Firstname2 Ph2 Address2Firstname3 Ph3 Address3You can create a mapping by following these stepsCreate a SQ with one input field Field1Add a expression transformation after SQLink Field1 from SQ to expression TransformationAdd a new op port name grp =substr(FIELD1,length(FIELD1),1)Add a aggregator transformation after Expression...

Read More

Unix Handy Commands

These are the commands which i use on daily basis in Unix.I found these commands very useful In point 2 and 6 it is not displaying pipe symbol due to some problem.Please use pipe between commands Too see a particular line For example if you just want to see 180th line in file sed -n '180p' testfile.txt To find a particular column in file cat testfile.txt awk -F"," '{print $2}' To rename file with current date mv test test_`date +%Y-%m-%d This command will take out all those lines which are having 8 at 17th position grep '^.\{16\}8' testfile.txt...

Read More

Difference between TRUNCATE and DELETE

This is one of most frequently asked question in Oracle interviews.I tried to put all the differences i know,you are welcome to put your thoughts and comment on thatDeleteIt is a DML statementCan RollbackCan delete selective recordsIt fires database triggers.It does not requires disabling of referential constraintsDeletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued...

Read More