Reading DB2 files in MVS
in [SAS]
|
From: charles.harbour on 23 Aug 2006 17:27 Why not take advantage of the db2 system, instead of attempting to continue to read them as flat files? You <may> read them directly (sas can read vsam), or have db2 dump you a flat file--but why would you? Something tells me that you truly aren't using every last record from that flat file.... My suggestion is to use the db2 engine to connect to db2 via the libname statement, and process using either normal sas data step, or by proc sql. Go get the following white paper (Accessing db2 data with sas9): http://www.sas.com/ctx/whitepapers/whitepapers.jsp?code=187 I believe what you've posted is the dataspace and database name--you'll need more than that to connect to db2 (including being defined as a user with access to appropriate tables, etc in this database)--so get to know your friendly neighborhood dba. After you've reviewed the material, feel free to repost your problems or contact me offline (though the list obviously has a wider audience). My suggestion would be to confirm your connection and access role via something like QMF before you try using it through sas (many a headache is fixed here first). HTH, CH On Wed, 23 Aug 2006 17:04:15 -0400, Anthony Pitruzzello <tonypit45(a)YAHOO.CA> wrote: >Greetings everyone, > > In our organization, we run SAS9 on an IBM mainframe in MVS (z/OS Version 1.4, to be upgraded to z/OS Version 1.7), through WYLBUR or TSO. Our programs have a JCL front end to define system resources, followed by SAS code. Typically, we might reference a sequential data set as $TEST.SREV.AP.DSNAME. We are in the process of modernizing our Student Information System. The OTS people want to replace our current ASCII files with DB2 files. My understanding is that SAS9 can read DB2 files. The problem is that the DB2 files are referenced as, for example, DBPUB.CURRENROLLMENT. No one seems to know how to reference the files in the JCL so they can be read by SAS. The SAS Companion for the MVS Environment doesn?t provide much help. Does anyone know how to do this? Does it require the purchase of a special SAS module? > > >--------------------------------- >The best gets better. See why everyone is raving about the All-new Yahoo! Mail.
From: "Dorfman, Paul" on 23 Aug 2006 18:33 Anthony, A few things. First, Base SAS is not sufficient for the purpose, and you will need to license SAS/Access to DB2 and have the system people install it. Maybe you already have it but do not know. Run proc setinit ; run ; (in either the Display Manager or batch), and look at the log. Second, you CANNOT reference DB2 table in JCL for the purpose to have SAS interact with them (of which reading is one mode), even after SAS/Access has been licensed and installed. You do it in the SAS program itself. How - is amply documented in the DB2 chapter for SAS/Access. There are basically two ways: (1) DB2 libname engine and (2) Pass-Through Facility. In yesteryears, I used to prefer (2) because I could optimize SQL query by writing it in the DB2-specific SQL. Nowadays, though, the libname method has been so well optimized that in a majority of situations it is preferable. If your DB2 subsystem ID is, say, DB2P, you would merely define up front something like libname mylib db2 ssid = db2p schema = dbpub ; and then read the table DBPUB.CURRENROLLMENT simply as if it were a SAS data set named CURRENROLLMENT in a SAS data library with the libref DBPUB (that would be an equivalent of the DD name if the z/OS data set representing the library were defined in JCL). That should be enough for you to start going in the right direction. Now let me ask you if this is the right direction. However vast your student information might be, it is definitely not large enough that SAS as a data base in its own right could not handle it with aplomb. You can easily create a single multi-volume SAS library (as a z/OS file with RECFM=FS, BLKSIZE=27648, LRECL=27648, and you can find more details on how to do it in the sas-l archives). That would be an equivalent of a DB2 data base. Within that "data base", you will be able to store whatever tables/views/indices you want in the form of SAS data files, define referential constraints between tables, set up audit trail, etc., if you even need all that functionality. Pros: no money wasted on DB2 and SAS/Access (neither is a small chunk of money) SAS reads SAS data files about 10 times faster than does equivalent DB2 tables SAS indices over SAS tables are faster that DB2 indices over DB2 tables you need not a swarm of DBAs, $100/hr each to grant a permission a day you have all the metadata information at your SAS fingertips instead of a bunch of DB2 tables with unmanageable names Cons: SAS is not a "real" data base, which matters only if you need commits and rollbacks which functionality, I suspect, you currently handle with GDGs/backups, and which of course can be done the same way with SAS some DBA will be upset that they cannot get $100/hr for his permissions, but I reckon the right not to be upset is not enumerated in the Constitution Kind regards ------------ Paul Dorfman Jax, FL ------------ +-----Original Message----- +From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On +Behalf Of Anthony Pitruzzello +Sent: Wednesday, August 23, 2006 5:04 PM +To: SAS-L(a)LISTSERV.UGA.EDU +Subject: Reading DB2 files in MVS + + +Greetings everyone, + + In our organization, we run SAS9 on an IBM mainframe in MVS +(z/OS Version 1.4, to be upgraded to z/OS Version 1.7), +through WYLBUR or TSO. Our programs have a JCL front end to +define system resources, followed by SAS code. Typically, we +might reference a sequential data set as $TEST.SREV.AP.DSNAME. + We are in the process of modernizing our Student Information +System. The OTS people want to replace our current ASCII +files with DB2 files. My understanding is that SAS9 can read +DB2 files. The problem is that the DB2 files are referenced +as, for example, DBPUB.CURRENROLLMENT. No one seems to know +how to reference the files in the JCL so they can be read by +SAS. The SAS Companion for the MVS Environment doesn't +provide much help. Does anyone know how to do this? Does it +require the purchase of a special SAS module? + + +--------------------------------- +The best gets better. See why everyone is raving about the +All-new Yahoo! Mail. + + ------------------------------------------------------------------------------ Notice: This e-mail message, together with any attachments, contains information of Merck & Co., Inc. (One Merck Drive, Whitehouse Station, New Jersey, USA 08889), and/or its affiliates (which may be known outside the United States as Merck Frosst, Merck Sharp & Dohme or MSD and in Japan, as Banyu - direct contact information for affiliates is available at http://www.merck.com/contact/contacts.html) that may be confidential, proprietary copyrighted and/or legally privileged. It is intended solely for the use of the individual or entity named on this message. If you are not the intended recipient, and have received this message in error, please notify us immediately by reply e-mail and then delete it from your system. ------------------------------------------------------------------------------
|
Pages: 1 Prev: problem connecting to oracle database using SAS Next: Accessing SPDS Server |