RSS
 

Archive for the ‘Informatica’ Category

Informatica Executive Vice President Girish Pancha and Me

14 Oct

Yesterday I met Mr. Pancha at Corporate Data Management Event in Istanbul which is sponsored by Informatica, Komtaş and Ereteam.

 

Informatica: Target table does not allow INSERT/UPDATE/DELETE

12 Oct

Rows are rejected and in the log detail, error message is;

WRT_8119 Target table [TARGET_TABLE_NAME] does not allow INSERT Row # in bad file
or
WRT_8119 Target table [TARGET_TABLE_NAME] does not allow UPDATE Row # in bad file
or
WRT_8119 Target table [TARGET_TABLE_NAME] does not allow DELETE Row # in bad file

I recommend to change “Treat source rows as” attribute to “Data driven

This error occurs because of the incoordinate between “Treat source rows as” and target table’s relational writer properties

When Insert is selected, but Update is selected at “Treat source rows as” property, it will get the error;

WRT_8119 Target table [TARGET_TABLE_NAME] does not allow UPDATE Row # in bad file

 

Useful Informatica blogs

06 Oct

http://community.informatica.com/
http://informaticatutorials-naveen.blogspot.com/
http://etl-tools.info/informatica/tutorial.html

To be added… Please send any useful URLs about Informatica.

 

How to List Informatica Workflow’s Completion Time

28 Jul

Below script lists the start and finish times of the workflows, also the process length in minutes.
Very useful to watch the performance of Informatica.
Please run it on Informatica Repository Database.

SELECT SUBJECT_AREA, WORKFLOW_NAME, START_TIME, FINISH_TIME,
DATEDIFF(mi, START_TIME, FINISH_TIME) AS DURATION_MINUTE
FROM
(
SELECT SUBJECT_AREA, WORKFLOW_NAME, WORKFLOW_RUN_ID,
MIN(ACTUAL_START) AS START_TIME , MAX(SESSION_TIMESTAMP) AS FINISH_TIME
FROM dbo.REP_SESS_LOG (NOLOCK)
WHERE ACTUAL_START BETWEEN '20110701' AND '20110702'
/*FILTER BY SUBJECT_AREA*/ --AND SUBJECT_AREA = 'INTERFACE'
/*FILTER BY WORKFLOW_NAME*/ --AND WORKFLOW_NAME = 'wf_CORE_BASI_EOD'
GROUP BY SUBJECT_AREA, WORKFLOW_NAME, WORKFLOW_RUN_ID
) SUB_TABLE
WHERE DATEDIFF(mi, START_TIME, FINISH_TIME) > 0
ORDER BY 1, 2, 3
 

Search all Informatica mapping source filters

07 Apr

Search all mapping source filters with a given text in Informatica repository;

SELECT INSTANCE_NAME, ATTR_VALUE FROM OPB_SWIDGET_ATTR, OPB_TASK_INST WHERE
ATTR_VALUE LIKE '%TEXT_2_SEARCH%' AND SESSION_ID=TASK_ID
 

Task is Succeeded but no Applied Rows

30 Jun

I faced an interesting issue in Informatica yesterday.

There is no error on the Monitor screen, but when I list the details of the “Succeeded” task, I saw:

Applied Rows = 0

Rejected Rows = 261733 (required to be applied rows number)

Last Error Code = 8425

Last Error Message = ERROR: Writer execution failed. Database error: [0]… bula bula bula

Actually, I do not interest with the error. I am confused because however there is an error, on the monitor screen task’s status is Succeeded!

What about the solution?

We want task to stop when any errors occured. So we have to follow and change:

Edit Tasks -> Config Object -> Error handling / Stop on errors = 1 (default value is 0)

As you can see below;

Or alternatively, we can add a Control with Expression “TgtFailedRows > 0″ as you can see in the figures below;

 

Informatica error CMN_1653 solution

30 Jun

Error Code: 1653

Error Details: CMN_1653 Cache file was created with logical database connection [Relational:DB1] while cache expects [Relational:DB2]

As you can see easily, error is because of usually a transformation (lookup, etc) in the task tries to read from cache persistently!

We have to follow and edit;

Edit Tasks -> Mapping -> Select the related Transformation -> Properties / Lookup cache persistent -> Unselect the checkbox

As you can see also in the figure below;

 

Could not acquire the execute lock for Worklet

10 Jun

If a worklet fails with the status message below;

ERROR: Worklet task instance [wklet_name]: Could not acquire the execute lock for Worklet [wklet_name]

This means the worklet is already working or suspended in some other workflows. You have to check other workflows and abort the worklet.