11 G New Features

In this article we will cover new features of 11 G1) Case senstive Passwords : In 11G oracle password are case senstive ,but this feature can be changed by altering the parameter    ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;   2) Invisible indexes :In 11G we can make index invisible .Once we make index invisible it will be ignored by Optimiser CREATE INDEX index_name ON table_name(column_name) INVISIBLE;ALTER INDEX index_name INVISIBLE;Invisible index is useful in cases where you want to see the impact...

Read More

Informatica Metadata Queries (Part 3)

We will continue to publish new metadata queriesPurpose :  To Find Truncate Table OptionQuery : select task_name,'Truncate Target Table' ATTR,decode(attr_value,1,'Yes','No') Value  from OPB_EXTN_ATTR OEA,REP_ALL_TASKS RAT  where OEA.SESSION_ID=rat.TASK_ID and attr_id=9 Description : This query will give all sessions where Truncate Table Option is onPurpose : This query will give count of Mapping,Instance where Sqloverride has been doneQuery : WITH detail AS(SELECT c.subject_area, c.mapping_name,d.instance_name...

Read More

Informatica Strange Problems

Some time we encounter strange problem while working with informatica.I would like to highlight some problem  which no way seems to be logicalError : Informatica Debugger: Invalid session can not be debugged .Select a valid session instance to use  Problem : You are trying to run your mapping in debug mode by selecting valid session but you are getting below error even though your session is valid.Solution : 1)     ...

Read More

How to validate all mappings in a particular folder

In this post we will discuss how to validate all mapping in a particular folder Step1 : Go to the Repository manager client Step2 : Go to option Tools->Queries Create a new query with name  ALL_MAP_QUERY Create a query with parameter name and condition to retrieve the mappings in a folder as the following: folder = folder-name object type = mappingStep 3: a)      Connect to power center repository using...

Read More

Informatica Useful Tips (Part1)

Tip 1 : Ignore the SQ SQL Override conditionallyIt is possible by defining a mapping parameter for the WHERE clause of the SQL Override. When you need all records from the source, define this parameter as 1=1 in theparameter file and in case you need only selected data, set the parameter accordingly.Tip 2 : Overcome size limit for a SQL Override in a PowerCenter mappingThe SQL editor for SQL query overrides has a limit of maximum of 32,767 characters.To overcome this we can do followingTo source a SQL of more than 32,767 characters do the following:1....

Read More

Informatica Metadata Queries (Part 2)

In this post we will continue with informatica metadata queriesPurpose : To Find Tracing Level for SessionQuery :select task_name,decode (attr_value,0,'None',1,'Terse',2,'Normal',3,'Verbose Initialisation',4,'Verbose Data','') Tracing_Levelfrom REP_SESS_CONFIG_PARM CFG,opb_task TSKWHERE CFG.SESSION_ID=TSK.TASK_IDand tsk.TASK_TYPE=68and attr_id=204 and attr_type=6Description : This query will give tracing information along with session names.This query is helpful in identifying the session which are having particular type of Tracing level like Verbose.Purpose...

Read More

Unix Interview Questions (Part1)

Following are some unix commonly asked interview questions  Q 1  What is command to check space in Unix Ans : df -k Q 2 If a file has permission 000 then who can access the File Ans : System Administrator .Q 3 What is command to kill last background Job Ans : kill $!Q 4 How you will list all Hidden files Ans : ls -la|grep ^[.] Q 5 What is command to create Zero Byte File Ans : touch filename Q 6  What is difference between diff and cmp command Ans : cmp -It compares...

Read More

Informatica Metadata Queries (Part1)

Informatica Metadata QueriesIn Informatica normally we come across situation like we need to find all mapping which use a particular table as source or something similar .It is very difficult to find it through Informatica tools.In such a situation Repository tables are very useful.Repository tables name start with OPB and are continuously updated when we make changes.Along with OPB tables REP views are also present.It is very dangerous to modify these tables so due care should be taken while dealing with OPB Tables.In this article we will...

Read More

Oracle Deferred Constraints

Oracle Deferred Constraints :Recently we came across a unique situation of updating a primary key of master table which being referenced by child table.Problem with updating primary key of master table was that it would have made child table orphan (ora-02292 child found)and we could not even update child table as it would have violated referential integrity.To overcome this we used the deferrable constraint introduced by oracle.A deferrable constraint is only checked at transaction commit time.We altered the foreign key on child table to make...

Read More

Informatica interview questions (Part 3)

We will continue with third set of questionsQ 1 What is the use of Shared Folder?Ans : Shared folders allow users to create shortcuts to objects in the folder.If you have an object that you want to use in several mappings or across multiple folders, we can place the object in a shared folder.we can then access the object from other folders by creating a shortcut to the object. Shortcuts inherit changes to their shared object. Shared folders in global repositories can be used by any folder in the domainOnce you make a folder shared, you cannot...

Read More

Kimball Vs inmon

There are two major design methodologies followed in data warehousing Ralph Kimball and Bill Inmon.We will discuss about both of these in detail.Bill Inmon Approach : According to Bill Inmon Data warehouse need to fulfill need of all category of users .In an organization there are different type of user like· Marketing· Supply Change Management· OperationsEach department has its different way of interpreting data so Data warehouse should be able...

Read More

Best Practices in Informatica (Part1)

We have tried to come up with some of best practices in informatica1) Always try to add expression transformation after source qualifier and before Target. If source or target definition changes it is easier to reconnect the ports2) Always use Cobol File for normaliser in binary format otherwise there are lot of issues specially with comp-3 fields3) Remove unused ports, though unused ports do not have any effect on performance bit it is always better to remove them for more visibility4) If possible try to do calculation in Output Ports instead...

Read More

Unix Script to Automate FTP Process

Unix Script to Automate FTP ProcessNormally we come across the situation when we have to FTP file daily.Doing this process daily for large no of files can be cumbersome.We can create Unix script to automate FTP ProcessCurrently we need to last day files from production to dev box for testing,so we created below mentioned script to FTP files dailyb=`TZ=CST+24 date +%y%m%d` ###To get last datecd /export/home/mydirftp -v -n FTP_HOST_NAME EOF ##Please put two less than sign before EOF user Userid Pwdbincd /export/home/source_dirget FILE_NAME.`echo...

Read More

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