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