Re: Reading a DB2-created file in SAS: OT

Paul,
Just out of curiosity, exactly what kind of table could have a _billion_ rows? I'm definitely interested! And how many columns? Just how big are these tables, in GB, I mean?


best,
-Tir


> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On
> Behalf Of Paul
> M. Dorfman
> Sent: Friday, September 30, 2005 7:51 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Reading a DB2-created file in SAS
>
>
> > -----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
> >
> > Hi,
> >
> > One of our data suppliers switched to using a RDMS that
> > stores the data as DB2 files.
>
> Robert,
>
> 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.
>
> Kind regards
> ----------------
> Paul M. Dorfman
> Jacksonville, FL
> ----------------
>