Informatica Reference

Archive for January 2012

Relational source: To access relational source which is situated in a remote place, you need to configure database connection to the data source.

FileSource : To access the remote source file you must configure the FTP connection to the host machine before you create the session.

Heterogeneous : When your  mapping contains more than one source type, the server manager creates a heterogeneous session that displays source options for all types.

When we use Flat file as a source we give the path of directory where we store the file in Workflow.

But if we want data from multiple flat files then we put the names of all flat files in a single file called as file list.

In workflow mapping we give the path of list file and mark the source filetype as Indirect as we are indirectly accessing all the flat files.

1. When we want to get single return value also we can call this anywhere in the mapping.

2. In addition to this it gives you the flexibility to run lookup for selected source rows which u don’t find in connected lookup.

3. When number of records are less go for unconnected lookup, which improve the performance.

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:

  • Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
  • Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
  • Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
  • Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff.

It is very similar to sub-queries where the parent query is executed based on the values returned by sub-quries. but when come’s to co-related subqueries for every instance of parent query subquery is executed and based on the result od sub-query the parent query will display the record as we will have refernce of parent quries in su-queries we call these as corelated subquries.

so, we can define co-related sub query as for every record retrival from the sub query is processed and based on result of process the parent record is displayed.

Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table.