Informatica Reference

Archive for December 2011

Audit Table is nothing but the table which contains about your workflow names and session names. It contains information about workflow and session status and their details.

  1. WKFL_RUN_ID
  2.  WKFL_NME
  3.  START_TMST
  4.  END_TMST
  5.  ROW_INSERT_CNT
  6.  ROW_UPDATE_CNT
  7.  ROW_DELETE_CNT
  8.  ROW_REJECT_CNT
Advertisements

One can push transformation logic to the source or target database using pushdown optimization. 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.

Just consider the example:

Suppose a mapping contains a Filter transformation that filters out all employees except those with a DEPTNO greater than 40. The Integration Service can push the

Transformation logic to the database. It generates the following SQL statement to process the transformation logic:

INSERT INTO EMP_TGT (EMPNO, ENAME, SAL, COMM, DEPTNO)

SELECT

EMP_SRC.EMPNO,

EMP_SRC.ENAME,

EMP_SRC.SAL,

EMP_SRC.COMM,

EMP_SRC.DEPTNO

FROM EMP_SRC

WHERE (EMP_SRC.DEPTNO >40)

The Integration Service generates an INSERT SELECT statement and it filters the data using a WHERE clause. The Integration Service does not extract data from the database at this time

Surrogate key:

  1. Query processing is fast.
  2. It is only numeric
  3. Developer develop the surrogate key using sequence generator transformation.
  4. Eg: 12453

Primary key:

  1. Query processing is slow
  2. Can be alpha numeric
  3. Source system give the primary key.
  4. Eg: C10999

DML Statement are one category of SQL statement such as Select, Insert, Delete and Update query and update the actual data. Data manipulation means

  • Retrieval of information stored in  the database
  • Insertion of new information into the database
  • Deletion of information from the database
  • Modification of information from the database

A DML is a language that enables users to access or manipulate data as organised by the appropriate data model.

Part or all of a transaction can be undone with the ROLLBACK statement. An automatic rollback occurs whenever a transaction is interrupted such as by an execution error, a power failure, update or delete statement but any that have occurred since the last commit.

When a ROLLBACK statement is issued to the database the transaction is ended and

  • All work done by the transaction is undone as if it had not been issued.
  • Any lock, acquired by the transaction are released.

In a transaction a set of changes are made permanent with the commit statement. When a COMMIT statement is issued to the database the transaction is ended and

  • All work done by the transaction is made permanent
  • Other sessions can see the changes made by this transaction.
  • Any locks by the transaction are released.

Syntax: COMMIT[WORK]

There exists no product/tool without strange exceptions/errors, we will see some from Informatica.

1. You get the below error when you do “Generate SQL” in Source Qualifier and try to validate it.

“Query should return exactly n field(s) to match field(s) projected from the Source Qualifier” Where n is the number of fields projected from the Source Qualifier.

Possible reasons for this to occur are:

  1. The order of ports may be wrong
  2. The number of ports in the transformation may be more/less.
  3. Sometimes you will have the correct number of ports and in correct order too but even then you might face this error in that case make sure that Owner name and Schema name are specified correctly for the tables used in the Source Qualifier Query.
  4. E.g., TC_0002.EXP_AUTH@TIMEP

2. The following error occurs at times when an Oracle table is used

“[/export/home/build80/zeusbuild/vobs/powrmart/common/odl/oracle8/oradriver.cpp] line [xxx]”
Where xxx is some line number mostly 241, 291 or 416.

Possible reasons are
Use DataDirect Oracle ODBC driver instead of the driver “Oracle in
If the table has been imported using the Oracle drivers which are not supported, then columns with Varchar2 data type are replaced by String data type and Number columns are imported with precision Zero(0).

3. Recently I encountered the below error while trying to save a Mapping.

Unexpected Condition Detected Warning: Unexpected condition at: statbar.cpp: 268 Contact Informatica Technical Support for assistance

When there is no enough memory in System this happens. To resolve this we can either

  1. Increase the Virtual Memory in the system
  2. If continue to receive the same error even after increasing the Virtual Memory, in Designer, go to Tools->Options, go to General tab and clear the “Save MX Data” option.