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 |
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 ] )
| 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. |
Yesterday I met Mr. Pancha at Corporate Data Management Event in Istanbul which is sponsored by Informatica, Komtaş and Ereteam.
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
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.
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 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 |
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;
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;
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.
