DSXchange

Hi,

I have a job that reads from a table(partitioned),goes through a transformer and finally writes to the same table (update existing or insert new).My problem is it runs very slowly,about 92 rows/sec. This is a very large table and I need to updata atleast 5 million rows. Can anyone suggest a way to improve this, so the job will run faster?

Thanks!

most common suggestion on this forum: split the inserts and updates in your job and do them seperatly.

Offcource always important: check your indexes(mostly for the updates)

try splitting your data for updates and inserts. that will work much faster. Also, whats your target DB

Thanks for your replies. I use Oracle 8i. I will try to split the inserts and updates.

Thanks!

We have been able to increase performance by changing our initial select to have a where clause to loop through partition keys (PARTITION_KEY = #Counter#) and we disable indexes.

I tried to seperate the inserts and updates. Now I get the error:
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression.

Can anyone tell me to get rid of this error?

Thanks!

Inspect the SQL then supply the missing expression. It might be the WHERE clause in an UPDATE.

Hi,

I'm not sure what to supply in the where clause because I'm updating the table on a unique_id column but that column doesnot get populated in the end table. So I am not sure what to put in the where clause. Any suggestions, please?

So pass the key to your target stage where your updating the table. Write a user defined sql updating all the columns you want to be updated.
in that sql apply the where clause as I am not familiar with the Oracle sytax. Maybe its something else in place of ?.
But thats basically the dirction.

Hi,

Now I am getting the error : Invalid Argument value
Can anyone tell me what is wrong with the below query.

UPDATE table SET a = ?, b = ?, c = ?, d = ?,
e= ?, ALL_AMT = ?, f = ?, g = ?, h = ?, i = ?, j = ?,
k = ? WHERE ((SUBSTR(a),1,11)||b||(TO_CHAR(c,'YYYYMMDD'))||0||d||(TO_CHAR(e,'YYYYMMDD'))=?);

Does your job design have 13 columns, one of which is Key ?

For the original question on making it faster. I suppose you're using the ODBC stage (seeing the error messages)... switch to the oracle stage or the DRS one to see a significant speed increase.

For DB2 the speed difference between ODBC and the DB2 stage is usually not that big... Between ODBC and Oracle I've gotten huge performance increases in the past.

Ogmios

Hi Ray,

The combination of all the 5 columns in the where clause is what makes it unique.
Any help is appreciated.

Thanks!

Spool to a file in one job, transform in another splitting inserts from updates into separate files, then a job to apply inserts and another for updates.

Your fundamental problem is that you're querying the same table you are loading. You should NOT do this. You're using rollback to hold the entire query set, and then more rollback to insert/update rows.

Thanks to all of you.

I was able to resolve the issue.