SAS with DB2 update statement taking longer time

[dsadsad]

Hi ,

My source table is in SAS and need to update the target table in DB2 . I am using the below code .however it is taking too long to execute if the source table has many rows . any suggestions on how to improve the performance. My target table can not be dropped or rows can not be deleted  due to constraints defined . please suggest for any other options.

proc sql;

update DB2.ACCOUNT

set AREA = (select  AREA from updrecords where Account_key = "&account_key"),  

COY = (select  COY from updrecords where Account_key = "&account_key"),

BRANCH = (select  BRANCH from updrecords

where Account_key = "&account_key"),

Please let me know if any further details required

[PeterClemmensen]

Where is the updrecords table located?

[PeterClemmensen]

You will most likely increase performance if you place the source table in the DB and then make the update from that. 

There are several ways to do this. However, the most elegant provided by SAS/ACCESS is probably the DBMSTEMP= LIBNAME Statement Option. The data you put in the library (that points to the DB) will become #temp tables in the DB.

So:

1. Create a new library that points to the DB and use the option above. 
2. Put the source table in the new library.
3. Run the update entirely in the DB using the new #temp table as the source.

Feel free to ask 🙂

[PeterClemmensen]

This is equivalent to the Pushing Updates part of Temporary Table Support for SAS/ACCESS.

[dsadsad]

@PeterClemmensen 

Thanks for your response and suggestion. i will try this option.

Does it give better performance if we use sql passthrogh for update statement ?

please share if you have any sample code for update statement with in DB2. i can google but just checking if there is any which works well

finally if you already tried this approach with in DB, how much performance improvement we are expecting .

i will be trying this option, however it just takes time to get create table access from DBA with all the process to be followed and hence asking the above questions.

[Patrick]

@dsadsad 

Given the where clause: Account_key = "&account_key" are you executing this code for a single row or do you call this code within a SAS Macro or call execute() statement.

If there are many accounts in your transaction table updrecords for updating rows then as others proposed first upload this table to DB2 and then code an inner join for updating the target table with a transaction table. There are many examples out there if you Google with keywords like "update a table with another table" - or similar.