Date: Mon, 10 Jan 2000 19:29:39 GMT
Reply-To: charles_s_patridge@MY-DEJA.COM
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: charles_s_patridge@MY-DEJA.COM
Organization: Deja.com - Before you buy.
Subject: Re: SAS Access to DB2
Dear Keith,
I use SAS Access for Oracle and have used SAS Access for DB2 in the past
as well as Pass-Through for DB2.
A couple of notes:
1. For speed and efficiency, Pass-Through is usually better than Access
provided the SQL query is properly coded. That is, SQL Pass-Through
will direct DB2 to perform the query and the results will be handed back
to your SAS program.
However, the downside to using Pass-Through is you have to code the
query using the Table Names and Columns as defined in the DB2 table(s)
for any and all programs accessing DB2 this way. You will probably want
to develop SAS Macros to handle this for you, especially for DB2 tables
that may change
structurally on a frequent basis (that is, if column names are changed,
deleted or added to). Because of
this, you would need to also create a RENAME Process to handle the LONG
TABLE COLUMN NAMES
so SAS can handle 8 character limit (version 6 and prior). Not sure, if
you still need to do the same in Version 7 or 8 as I am not positive to
the length DB2/ORACLE can assign and how its relates to the longer names
version 7 or 8 is limited to.
2. For ease of maintenance, I prefer the SAS Access method.
Since I update my SAS files once a month from our production system
(Oracle) speed is not a major issue.
In addition, we have numerous programs that need access to these Oracle
tables, and instead of coding in
Pass-Through, I just need to code my programs to read the SAS VIEWS I
have created using the SAS ACCESS module. I only need to define my
VIEWS once until something has changed and usually do not have to
maintain any of my programs that use these Oracles tables as they are
accessed by the VIEW.
All this naming of Columns and Renaming is done once while creating the
VIEWS and no longer has to be considered until a Table has been modified
(column added, deleted or changed). But if needed to be done, I only
need to do it once in the Access Module, and all my programs should
remain in tact. If a column is
deleted then programming changes may be necessary. If the data type of
a column is changed, then programming changes may be needed. ETC ETC.
M sas programs read the VIEWS as if they were SAS datasets when in
reality they are Oracle Tables. Much easier to write and maintain SAS
programs doing it this way instead of coding in Pass-Through.
If you are going to use SAS Access to create VIEWS, I know at least in
an Alpha Environment, I STRONGLY SUGGEST you keep your VIEWS in a
Separate SAS LIBRARY from all other SAS Libraries
so as not incurred slow access time when trying to open a SAS Dataset
that is NOT an ORACLE (probably the same for DB2) VIEW.
Hope this helps you.
Regards,
Charles Patridge
The Hartford
Email: Charles.Patridge@thehartford.com
In article <001501bf5bab$c9e7a540$e4999cd1@oemcomputer>,
Keith Vossberg <whorf@PRODIGY.NET> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0012_01BF5B68.BAB7D740
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> I would be interested in hearing experiences, good or otherwise, from
=
> anyone who has used SAS Access to DB2.
>
> With regards to the processing of data, am I correct in assuming that
=
> there are basically two ways to process these data;
> 1) Pass-through SQL where the processing takes place in DB2; and
> 2) DB2 libname statement (V7) where DB2 acts like a file server and
SAS =
> processes the information locally.
>
> Any information is appreciated!
>
> Keith Vossberg
> Research Manager
>
> ------=_NextPart_000_0012_01BF5B68.BAB7D740
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content=3D"text/html; charset=3Diso-8859-1" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT size=3D2>I would be interested in hearing experiences, good
=
> or=20
> otherwise, from anyone who has used SAS Access to DB2.</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>With regards to the processing of data, am I
correct =
> in=20
> assuming that there are basically two ways to process these =
> data;</FONT></DIV>
> <DIV><FONT size=3D2>1) Pass-through SQL where the processing takes
place =
> in DB2;=20
> and</FONT></DIV>
> <DIV><FONT size=3D2>2) DB2 libname statement (V7) where DB2 acts like
a =
> file=20
> server and SAS processes the information locally.</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>Any information is appreciated!</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT size=3D2>Keith Vossberg</FONT></DIV>
> <DIV><FONT size=3D2>Research Manager</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_0012_01BF5B68.BAB7D740--
>
--
Charles Patridge - PDPC, Ltd.
172 Monce Road - Burlington, CT 06013
860-673-9278 or 860-675-9026
Charles_S_Patridge@prodigy.net www.sasconsig.com
Sent via Deja.com http://www.deja.com/
Before you buy.
