Updates

[cognos11]

Hi All

I have a table wherein two new columns have added.i want to update those columns using informatica process( Historicle Load)

The table has a PK defined.

I am using the target table as source and in the

properties i am using TREAT ROWS AS UPDATE and chose UPDATE AS UPDATE option.

Total number of rows to be updated are around 1000.

When i run the mapping, its taking for ever to update the columns.
Infact i aborted it when the time was still nearly 20 mts past the start time.

However, when i used a source filter in the source qualifier to limit the numbers of source rows to 1, surprsisingly , it load the row in a jiffy.

I am just wondering if there are any issue updating the SAME TABLE THAT I AM USING AS THE SOURCE.

There arent any complex expressions in the mappiing,just,one susbstring expression.

 

[ArtieChoke]

If this is a one-shot deal, I suggest using SQL directly. I don't know if it's an issue or not to use the target as a source also. Usually with an update map, you do a lookup in the target table, sourcing from some other table.

One other option is to create a copy of your target table, then use that as the source.



"I think we're all Bozos on this bus!" - Firesign Theatre

 

[blom0344]

Updating just 1000 records should take seconds on a normal machine, but are there any other issues involved:

1. Locking (are there locks active on the target)
2. Is the target log file opened?
3. Is there any write activity to the table according to the server session details?
4. What is the performance when you write output to a flatfile?

I suspect read and write actions to conflict. Write first to flatfile or intermediate table and then perform the updates using this as source


T. Blom
Information analyst
tbl@shimano-eu.com