DSXchange

Hi,

I have a problem when reading from a flat file, it has around 40,000 rows that update a table in oracle. when I try to run the job it only reads 300 rows and takes ages to do anything. I've put it into another flat file to test it and reads all 40,000 rows in one go and blindingly fast.

Has anyone else come across this problem with flat files??

I'd venture a guess that this is an Oracle issue and not a Sequential file one.

What happens after it processes 300 rows? Where is your database located? Off somewhere in the darkest reaches of your network or co-located with the DataStage server? What action are you using in the OCI stage? Do you have indexes on the key fields and are they properly defined? Questions like this will help narrow down where the bottle-neck is in your process.

I left the job running over the weeknend and came in this morning, it was still running!! The server is in France but this shouldn't be a problem as I've been upadating tables with no trouble at all.

In the OCI stage, its update existing rows only. I have a hash file which is my look up for client_ids. Also the sequential file has no unique fields. In the transform it matches on keywords and client_id.

Thanks for looking at this.

If there aren't billions of lines in your target table (probably the case when talking about clients), what about a deadlock ? Something like a manual update/insert into your table, with no following commit ?

I'd get your DBA involved at this point. What is going on in your Oracle database at this moment?

If the sequential file has "no unique fields" does this mean that your updates are updating multiple records? Or perhaps doing full table scans to find what it needs to update? Still curious about the indexes available on the target table...

Yes, the file is updating columns in a table based on client_id, for example a client can buy many products hence no unqiue field. each column represents a product which why I'm using a matches so its marked with '1' id that client has made a purchase, there are 17 columns in total.

Nothing is happening on the Oracle database, oh BTW the index is on the client_id.