RSS
 

Archive for the ‘Informatica’ Category

Disable / Enable Index

24 Jul

Indexes are useful when reading data. But when inserting data into a table, it may be useful to disable the indexes and rebuild them again after insert operation completed.

Especially when loading huge data manually or with ETL, disable index on pre-sql and rebuild on post-sql:

----Diable Index
ALTER INDEX [IX_Index_Name] ON dbo.TABLE_NAME DISABLE
----Enable Index
ALTER INDEX [IX_Index_Name] ON dbo.TABLE_NAME REBUILD
Social Share Toolbar
 

Oracle/PLSQL & Informatica To_Char Function

13 Mar

The to_char function converts a number or date to a string.

The syntax for to_char function is: to_char( value, [ format_mask ], [ nls_language ] )

Date Examples

Parameter Explanation
YEAR Year, spelled out
YYYY 4-digit year
YYY
YY
Y
Last 3, 2, or 1 digit(s) of year.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7; 1 = Sunday).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.
Social Share Toolbar
 

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.

Social Share Toolbar
 

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

Social Share Toolbar
 

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.

Social Share Toolbar
 

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
Social Share Toolbar
 

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
Social Share Toolbar
 

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;

Social Share Toolbar
 

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;

Social Share Toolbar
 

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.

Social Share Toolbar