> -----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 ----------------