Date: Tue, 27 Jul 2004 16:38:36 -0400
Reply-To: sashole@bellsouth.net
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Paul M. Dorfman" <sashole@BELLSOUTH.NET>
Organization: Sashole of Florida
Subject: Re: Get data from DB2 OS/390 to PC-SAS
In-Reply-To: <C3E16A30C33FD511B14700508BCF87C413DDE1AF@xexdal06.mbnainternational.com>
Content-Type: text/plain; charset="us-ascii"
Sal,
Very interesting. I did not know MVS FTP server supports this [very useful]
functionality. I wonder in which form the query is returned (a flat file
laid out as the DCLGEN would prescribe, or a formatted report).
Kind regards,
----------------
Paul M. Dorfman
Jacksonville, FL
----------------
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of Fazzino, Sal
> Sent: Tuesday, July 27, 2004 3:52 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Get data from DB2 OS/390 to PC-SAS
>
> SAS-L
>
> It been awhile since I supported FTP on the mainframe, but I
> believe the IBM mainframe FTP server will submit SQL to DB2
> and return the query results to the caller.
>
> A google search on "FTP MVS DB2" returned this (and other links)
>
> SQL Query to DB2
> MVS FTP also supports the DATABASE 2 (DB2) SQL query
> function. Only the SELECT DML operation is supported. That
> is, other DML (INSERT, UPDATE or
> DELETE) and DDL operations are not supported. The SQL query
> may be submitted to a DB2 subsystem at either the local or
> the remote site, as illustrated
> below:
>
> put (submit) a local file containing an SQL query to any DB2
> subsystem at the remote MVS FTP server and get (retrieve) the
> SQL query results in a file from the remote MVS FTP server
> put a local file containing an SQL query to any DB2 subsystem
> at the local MVS FTP client; upon completion of the query
> operation, the local MVS FTP client will transfer the SQL
> query output file to the remote FTP server The site and
> locsite FTP subcommands are used to switch the filetype to
> sql and to identify the name of the DB2 subsystem. Please
> refer to IBM TCP/IP Version 3 Release 1 for MVS: User's Guide
> for the syntax of these FTP subcommands.
>
> Might be possible to do what needs to be done using just FTP and SAS.
>
>
>
> -----Original Message-----
> From: Paul M. Dorfman [mailto:sashole@BELLSOUTH.NET]
> Sent: Tuesday, July 27, 2004 2:09 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Get data from DB2 OS/390 to PC-SAS
>
>
> Walter,
>
> I am not sure I fully understand what you are saying. It
> appears that you have
>
> 1) Base SAS but no SAS/ACCESS to DB2 licensed on S/390 which
> you view as a remote host
> 2) DB2 Connect licensed on the Windows server
>
> From what I understand about DB2 Connect, it is an IBM
> contraption making it possible to access DB2 from any host on
> any other host, which means that snatching a query from DB2
> installed on S/390 and dumping the result to the PC is not a
> problem and can be accomplished without SAS. If so, at which
> point does SAS come in play at all? If your SAS users need
> the DB2 query results in the proprietory SAS form, the
> question is in which form DB2 Connect dumps data to the
> client. That I do not know. If it is a flat file, SAS can of
> course read it. If it is a UDB DB2 table, then it can be
> dumped into a flat PC file using a DB2 utility or, if
> SAS/ACCESS to DB2 is licensed on the Windows server, read by
> SAS via the Access engine.
>
> It would be much less convenient without DB2 Connect,
> SAS/Connect and/or SAS/ACCESS to DB2 anywere, but still possible:
>
> 1) Use a DB2 utility (batch SPUFI or DSNTIAUL via the TSO
> monitor IKJEFT01) to execute a query producing a flat file.
> (Of course, for this, the users in question would need a
> logon access to the real computer, but even if this is
> difficult, it is not as costly as licensing SAS/ACCESS.)
> 2) Make mainframe SAS read the file and create a SAS data
> file in XPORT format.
> 3) Binary dump the export file to the client, which is now is
> directly SAS-readable.
>
> If you had SAS/Connect licensed (but not SAS/ACCESS), a
> remote SAS session could be used to write necessary JCL
> statements (if needed, together with a SAS program reading
> the query result into a XPORT SAS file) to the internal
> reader, which would trigger a batch job. The internal reader
> would receive something no unlike
>
> //UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
> //SYSTSPRT DD SYSOUT=*
> //SYSTSIN DD *
> DSN SYSTEM(V51A)
> RUN PROGRAM(DSNTIAUL) PLAN(DSNTIBNT) --
> LIB('USER.RUNLIB.LOAD')
> //SYSPRINT DD SYSOUT=*
> //SYSUDUMP DD SYSOUT=*
> //SYSREC00 DD DSN=USRT003.F.DSN8UNLD.SYSREC00, //SYSREC..
> //SYSIN DD *
> MYDB2NT.SAMPLE.DEPARTMENT WHERE DEPTNO = 'ABC'
>
> However, one would have to consult the local folks in the
> know. For what my
> $.02 is worth.
>
> Kind regards,
> ----------------
> Paul M. Dorfman
> Jacksonville, FL
> ----------------
>
> > -----Original Message-----
> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of
> > Walter Scott
> > Sent: Tuesday, July 27, 2004 2:05 PM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Re: Get data from DB2 OS/390 to PC-SAS
> >
> > Toby,
> > Thanks for the reply! I have no doubt that SAS/Access
> > DB2 would work great. Our mainframe people, though, feel
> that adding
> > SAS/Access
> > DB2 (and SAS/Connect so the PC or server could talk to the mainframe
> > SAS) is too much cost for too little benefit
> ($A$/users(SAS)) and they
> > feel they already have sufficient data communication tools.
> And the
> > practice here is to standardize on 1
> > (ideally) tool; reduces software and support costs.
> > Based on what I think I understand from reading the
> DB2 Connect
> > manuals today, that's probably the direction my l 'recommendation'
> > will lean; since DB2 Connect is our standard software for accessing
> > DB2 data from the PC.
> >
> > Walter
> >
> > >>> "Dunn, Toby" <Toby.Dunn@tea.state.tx.us> 08:53:04 27-Jul-04 >>>
> > Walter,
> >
> > I have been using PC SAS and SAS/ACCESS for four years now with the
> > DB2
> > here. It works great haven't had any problems yet.
> >
> > HTH
> > Toby Dunn
> >
> > -----Original Message-----
> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of
> > Walter Scott
> > Sent: Monday, July 26, 2004 5:00 PM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Get data from DB2 OS/390 to PC-SAS
> >
> > All,
> > I have a situation where several people using PC-SAS
> and Windows
> > Server SAS need to access DB2 data on an OS/390 mainframe. Getting
> > any SAS/Access products on the mainframe is, at best, an
> absolute last
> > resort. Can anyone offer me any info/advice on the possibility of
> > using DB2-Connect as the go-between between mainframe DB2
> and PC-SAS?
> > (Or can SAS/Access to DB2 talk to mainframe DB2 directly?).
> > SAS Enterprise Miner has also been suggested as a possible
> > solution but, based on what I read on SAS' web-site, this product
> > would be overkill.
> > So far, I've come up with two possible configurations
> for this as
> > follows:
> >
> > OS/390 DB2 to OS/390 SAS via SAS/Access to DB2
> > file transfer from OS/390 to PC or Server (Win32),
> > to PC-SAS
> > -Would require SAS/Access to DB2 (on OS/390)?
> >
> > OS/390 DB2 to DB2-Connect Server (whatever platform),
> > to Win32 DB2-Connect Client,
> > to PC-SAS
> > -Could this use the SAS ODBC-driver?
> > -Require SAS/Access ??? (ODBC?) license on the PC or server?
> >
> > Any advice/comment/comparison is welcome!
> > Thanks
> In Advance,
> > Walter
> >
> > OS/390 DB2 to OS/390 SAS via SAS/Access to DB2
> > file transfer from OS/390 to PC or Server (Win32),
> > to PC-SAS
> > -Would require SAS/Access to DB2 (on OS/390)?
> >
> > OS/390 DB2 to DB2-Connect Server (whatever platform),
> > to Win32 DB2-Connect Client,
> > to PC-SAS
> > -Could this use the SAS ODBC-driver?
> > -Require SAS/Access ??? (ODBC?) license on the PC or server?
> >
> >
> >
> >
> >
> > >>> Richard Graham <richard@GLENCAIRN-CONSULTING.COM> 13:13:55
> > 26-Jul-04 >>>
> > Hi Patrick,
> >
> > I had this problem a long time ago when we switched over to Lotus
> > Notes from ccMail. It took nearly a year to get the
> problem resolved.
> > With the help from Nexgen and SAS Tech Support, we were able to
> > resolve the problem, finally.
> >
> > Two things...
> >
> > 1). In Lotus Notes under the File menu,
> > select the Tools then the USER_ID...
> >
> > make sure the option is checked....
> >
> > Don't prompt for a password from other Notes-based programs.
> >
> > 2). I got the below from IBM NexGen
> > Problem:
> > Third-party applications that use Notes' VIM32.DLL do not work
> > correctly with a clean Notes R5 installation. For example, VIM
> > functionality is no longer a choice when creating Notes mail from
> > Crystal Reports.
> > Solution:
> > This is occurring because the following lines are not added to the
> > WIN.INI file when R5 is installed:
> >
> >
> > [Mail]
> > SMI=1
> > [LotusMail]
> > Application=Notes
> > Program=c:\notes\Notes.exe NoDialogs
> >
> >
> >
> > Adding these lines manually resolves the problem. The
> problem does not
> > occur on upgraded Notes clients because the lines are
> already present
> > in the WIN.INI.
> > This issue has been reported to Lotus Quality Engineering.
> > Supporting Information:
> > This same problem has also been reported when using a
> product called
> > Mail Room, made by Simplify. When running the program, the
> following
> > error occurred:
> > "SMI not properly installed"
> > This was resolved by adding the above entries to the WIN.INI.
> >
> >
> > Try the following to send an attachment. Out Notes client limits
> > files to about 4.7MB. I don't know if SAS in and of itself
> limits the
> > size of a file...probably not since all it does is call the
> particular
> > email program (i.e., lotus notes, ccmail, outlook, etc.).
> >
> > filename testmail email 'Patrick.F.O'Neill@KP.ORG';
> >
> > data _null_;
> > file testmail;
> > /* Assign the SUBJECT attribute */
> > put '!EM_SUBJECT! Test of automatic email notification through
> > Lotus Notes ';
> > put 'This is a test of the Lotus Notes email from SAS ';
> >
> > put '!EM_ATTACH!filename.ext';
> >
> > /* Send the message */
> > put '!EM_SEND!';
> >
> > run;
> >
> >
> >
> > HTH
> >
> > Richard W. Graham
> > Vice President
> > Glencairn Consulting Group, Inc.
> >
>
| 
