Date: Thu, 29 Sep 2005 22:20:49 -0400
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Paul M. Dorfman" <sashole@BELLSOUTH.NET>
Organization: Sashole of Florida
Subject: Re: Reading a DB2-created file in SAS
Content-Type: text/plain; charset="us-ascii"
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of Robert Saunders
> Sent: Wednesday, September 28, 2005 7:20 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Reading a DB2-created file in SAS
> One of our data suppliers switched to using a RDMS that
> stores the data as DB2 files.
A BIG mistake on their part. I have just implemented (say, just for the heck of
comparison because I could) two data-identical parallel DWs in SAS and UDB (on
AIX), where the fact table contains upwards of 1 billion rows, the dimensions
being sizeable (up to 20M rows), too. With indexing being identical on both
systems, an average query runs about 10 times faster on the SAS-based system
than on the DB2-based system. 'Nuff said. And this is not SPDS yet! I can only
imagine what the ratio would be for Oracle - the most maladroit and overhyped
RDBMS I have ever encountered.
> I see in the manuals that SAS/ACCESS lets you
> access such files, but it looks from reading a little bit
> that this is designed for accessing the data directly from
> the RDMS. Our suppliers simply made an extract of the data
> and sent us the dbload instructions (which effectively look
> like an input statement--or could be readily converted to
> one--indicating variable names and column positions).
You owe them a lot of your run time, because they have the hardest job -
extracting data from RDBMS - done for you. Rest assured your SAS will read from
the flat file times faster than it would "directly" from DB2 via SAS/Access.
> What I'm wondering is:
> 1. Would you still use SAS/ACCESS to read in the actual tables/datasets?
No. You already have the flat file. Just read it.
> I'm anticipating that because the data are not physically on
> the IBM system (I'm reading the data files stored on my SAS
> server) that SAS will want to access the data more directly
> (e.g., a regular INFILE with a bunch $EBCDIC and S370FPD informats).
I am not sure what you mean by "more directly". You have a flat file unloaded
from DB2. All you need it to read it. It is as direct as it gets, especially as
you seem to have no access to DB2, anyway.
> 2. Would I not also need all the usual RECFM and other
> options to read in these raw data files?
Oh yeah. You are reading a flat file, remember?
> 3. Do these answers vary depending upon whether you use
> SAS8.2 PC or SAS9.1 server (some stuff may be run on
> workstations that only have 8.2 rather than off the server)?
No, they do not. As far as reading a flat file is concerned, even V5 would do
just fine, as long as you use SAS names castrated to 8 bytes. V8 does not
present even that challenge.
Paul M. Dorfman