topic DB2 CLI ERROR WHEN JOINING SAS DATA SET AND DB2 TABLE.Suggest Efficient way in SAS Programming

DB2 CLI ERROR WHEN JOINING SAS DATA SET AND DB2 TABLE.Suggest Efficient way

JasonNC — Wed, 05 Dec 2012 11:28:15 GMT

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.

Re: DB2 CLI ERROR WHEN JOINING SAS DATA SET AND DB2 TABLE.Suggest Efficient way

LinusH — Wed, 05 Dec 2012 13:41:12 GMT

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

Re: DB2 CLI ERROR WHEN JOINING SAS DATA SET AND DB2 TABLE.Suggest Efficient way

Reeza — Wed, 05 Dec 2012 15:40:01 GMT

reformat your where clause so all the work happens on the server:

where id in (&my_ids);