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.
Please let me know if any further details required
Where is the updrecords table located?
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:
Feel free to ask 🙂
This is equivalent to the Pushing Updates part of Temporary Table Support for SAS/ACCESS.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.