topic Re: SAS with DB2 update statement taking longer time in SAS Programming

SAS with DB2 update statement taking longer time

dsadsad — Mon, 14 Feb 2022 07:37:09 GMT

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

Re: SAS with DB2 update statement taking longer time

PeterClemmensen — Mon, 14 Feb 2022 07:38:35 GMT

Where is the updrecords table located?

Re: SAS with DB2 update statement taking longer time

PeterClemmensen — Mon, 14 Feb 2022 07:54:56 GMT

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:

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

Feel free to ask 🙂

Re: SAS with DB2 update statement taking longer time

PeterClemmensen — Mon, 14 Feb 2022 08:06:42 GMT

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

Re: SAS with DB2 update statement taking longer time

Patrick — Mon, 14 Feb 2022 11:28:49 GMT

@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.

Re: SAS with DB2 update statement taking longer time

dsadsad — Mon, 14 Feb 2022 22:11:55 GMT

@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.