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??
Sequential File
Moderators: chulett, rschirm, roy
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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...
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...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Nothing is happening on the Oracle database, oh BTW the index is on the client_id.