Hi,
The problem I am having is
In my case i have a data set with some thing about 10 million and i need to do a look up on DB2 table which contains about 600 million records based on iD and pull some information for that ID.
I tried different ways like INNER JOIN ,sub query but i got a DB2 CLI error as the query is getting timed out
When i am doing subquery like this
Proc sql noprint;
select id,info
from db2.table
where id in (select id from sas data set)
The problem here is when this query goes to Db2 the query against d2 table is going the query other than subquery is sent to db2 and pulling the entire 600 million records and then executing the subquery as a result it is showing DB2 CLI cursor error.
Is it fine to use Hashing little skeptical about it.So is it fine if i load DB2 table into DATA STEP and load the 10 million into hashing.Will it give better performance.
Please let me know how to achieve this efficiently.
You can achieve hashing in Proc SQL as well, but you can't force it... 😞
If you have permissions, upload your table to DB2, and do the join there.
I presume that the id column is indexed in the db2 table, so try to use the DBKEY= option.
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371553.htm
reformat your where clause so all the work happens on the server:
where id in (&my_ids);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.