Date:         Wed, 23 Jan 2008 09:15:27 -0500
Reply-To:     Ashley Sanders <asanders@UFL.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ashley Sanders <asanders@UFL.EDU>
Subject:      Re: PROC SQL: Using local database when querying DSN
In-Reply-To:  <010001c85d3c$b10f4cd0$c12fa8c0@HP82083701405>
Content-Type: text/plain; charset="iso-8859-1"

I had great go rounds with DB2 administrators over this... Explained in the
context of the alternative suggestions (involving repeated calls to DB2 for
each lookup from your local table --which really will make them grumpy) I
was finally able to convince them that we needed a user with limited write
access so SAS could create temporary tables and then allow DB2 to do its
thing: process the query (this is, after all, what DB2 is for right?)

You are using pass-through, so I'm guessing you do not have SAS/ACCESS for
DB2 available. If you do a lot of this type of querying, you should talk to
whoever is in charge of licensing about getting it. In the grand scheme of
things, you already have a large investment in DB2 and SAS, so paying for
the tools to use them efficiently together seems like a no brainer.

In shameless self promotion, see
http://aipl.arsusda.gov/publish/other/2005/nesug_ahs.pdf for ways to use
ACCESS.

For specifics on your platform connections you can contact me directly and
I'll offer whatever I learned from painful experience...


Ashley Sanders
Department of Animal Sciences
PO Box 110910
Gainesville, FL 32611-0910
asanders@ufl.edu
(352) 328-1559 (cellular)


>>-----Original Message-----
>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>>Mary
>>Sent: Tuesday, January 22, 2008 4:21 PM
>>To: SAS-L@LISTSERV.UGA.EDU
>>Subject: Re: PROC SQL: Using local database when querying DSN
>>
>>Dan,
>>
>>I'm not sure if pass-through DB2 SQL can accept a macro variable; that
>>might be the only way to handle it if you don't have any other access
>>to DB2.
>>
>>You may want to try a small macro variable like this:
>>
>>%let myvar='001' '002';
>>/* where you put in some of the ID's that you have */
>>Then you could use Proc SQL to pull maybe 500 ID's at a time into a
>>macro variable and then put your proc sql here into a macro and call
>>it.
>>
>>Do you have any access to the machine where DB2 is located?  For
>>instance, if it is TSO, a much preferred approach would be to load your
>>temp table into a session table in a PL/1 or COBOL program and then
>>join by the rest of the query, creating a file to output and then
>>download, but you'd have to be on the machine that runs DB2 to run
>>that- maybe speak to your database people about that.
>>
>>Write back if a macro variable will work- then we could see about
>>pulling 500 or so items from your list at a time- but that would still
>>make about 200 calls; pretty difficult still.
>>
>>-Mary
>>  ----- Original Message -----
>>  From: Dan
>>  To: SAS-L@LISTSERV.UGA.EDU
>>  Sent: Tuesday, January 22, 2008 2:27 PM
>>  Subject: PROC SQL: Using local database when querying DSN
>>
>>
>>  I need to use a local file to filter the results I am recieving in a
>>PROC
>>  SQL statement on the fly.
>>
>>  I have a database of ID's:
>>  Local.IDList
>>
>>  My code so far, without joining Local.IDList is here:
>>  PROC SQL;
>>    CONNECT TO DB2(DSN=DSNServ user=&ID password=&PWD);
>>    CREATE TABLE Local.SampleSet AS
>>    SELECT * FROM CONNECTION TO DB2
>>   (
>>    SELECT
>>      S.ID,
>>      S.Date,
>>      S.CDType,
>>      A.Parp,
>>      K.PartNum
>>    FROM DD.FullList S
>>    LEFT OUTER JOIN DD.ParpList A ON
>>         S.CDSysKey = A.CDSysKey
>>    LEFT OUTER JOIN DD.PartList K ON
>>         A.PartIndex = K.PartIndex
>>    WHERE K.PartNum in ('01','07','09')
>>  );
>>  %PUT &SQLXMSG;
>>  DISCONNECT FROM DB2;
>>
>>  My problem is that the above pull is over 700 GB, so I cannot just
>>pull it
>>  and later filter it.  My other problem is that I do not have write
>>access
>>  to the DSN Server, so I cannot upload my file to a table.  Is what I
>>ask
>>  even possible?
>>
>>  IDList is too long to put into the SQL itself, as it is over 100,000
>>rows.
>>  One idea I thought of was a macro that would run the query once for
>>each
>>  item in IDList, but I wouldn't know how to impliment that, and it
>>doesn't
>>  seem very efficient either especially for the size of the data sets I
>>am
>>  working with.
>>
>>  Is what I ask even possible to do without blowing up my computer
>>and/or
>>  temp space with the massive size of the tables I am querying against?
>>
>>  Thanks for your time,
>>  Dan