12-05-2012 06:28 AM
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;
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.
12-05-2012 08:41 AM
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.