02-11-2014 08:44 AM
At a high level, I am trying to move a file from a mainframe environment to a PC. I tried to FTP the file to a .txt file, but when I import the data to SAS it is not producing the
desired results. From what I have read it is likely due to non-standard numeric data.
I currently run PC SAS 9.3 on Win 7 64 bit and connect to the mainframe through a TSO session. I believe SAS connect would be the best solution, but I am struggling to configure the scripts (likely due to my lack of mainframe knowledge). I have tried all the scripts in the saslink folder with no luck. They usually end up with the error ‘ERROR: A communication subsystem partner link setup request failure has occurred.’ At this point, I am looking for direction as to which script I should utilize for this process and any other helpful hints.
Below is my current tso logon process:
I am hoping someone could point me in the right direction based on my login process. Let me know if there are any other pieces to the puzzle that I did not include.
02-11-2014 09:16 AM
To make the SAS/CONNECT to work, your best path forward is to contact the SAS responsible at your site. If ou have a SAS/CONNECT license on both PC and MF, there must likely be other who is using this.
Is it a text file you are moving? Not really a SAS issue. But if you have troubles with numerical data, be sure to move it using binary mode. Again, there must be lots of other MF user ay your site who is moving files up and down that should be able to help you out.
02-11-2014 10:14 AM
Thanks for the replies. I understand this is not a SAS issue, but a user issue . When I browse the dataset on the mainframe, I see a } several times. When I imported the data into SAS, I have gotten a variety of differt outputs depending on how it was imported. Sometimes I would see new special characters added, sometime the columns would not line up as expected, and other times it would just fail. I tried most of the import processes with no luck. I was under the impression that the data was not being converted to the PC format appropriately during the FTP process. Let me know what you think.
02-11-2014 09:57 AM
Which undesired results are you getting?
My first guess:
The characterset might be an issue?
MF uses the EBCDIC character set the PC does not use this character set.
02-11-2014 11:28 AM
You say you want to move 'a file'. What kind of file is it? If it's a SAS data set you will naturally see lots of special characters if you use any viewer/editor except SAS to open and browse the data.
Your connect error sounds to me like a mainframe-side error thrown by SAS/Connect. You need SAS/Connect on both sides, client and server (PC and mainframe). Are you sure you have SAS/Connect installed on the mainframe? In light of security concerns it would not surprise me if it's not installed or if you just don't have access to it. Mainframe IT security folks can be even more paranoid than the server-side people, if that's possible. The default connect script from SAS is very robust and should work as-is.
Regarding the FTP error, FTPing doesn't convert anything. I suggest explicity converting the file on the mainframe to either a column- or comma-delimited text file. That way you'll know exactly what you have. But even then you will have to use SAS's EBCDIC formats when you read the data into a PC SAS dataset. Hey, maybe that's the problem... you're trying to import a mainframe EBCDIC file into an ASCII system on the PC. Read SAS's Tech Note TS-642, "Reading EBCDIC Files on ASCII Systems".
When you FTP the file, move it binary format so it lands as an exact image of the original. If it is "large" then first subset the data on the mainframe to get a few hundred/couple thousand records for testing. You might look at my SGF 2011 paper titled "Using SAS to Move Data between Servers". There is a section towards the end that talks about using SAS's FTP/SFTP access method and a code example is provided. The SAS documentation on FTP/SFTP is also very good.
02-11-2014 01:04 PM
You need to modify the logon script for the RLINK to emulate the steps and data that you are entering to sign on to TSO manually. First, take a copy of the TSOCMSIP.scr and give it a new name. Using a different screen, you should follow the steps that you use to sign on to TSO. You need to edit the script to match the responses from the screen exactly and add what you type into the script as responses. For example, you use 'srtso userid' while we use 'tso userid' - which is part of our script.
Also note that once you have ISPF running in the script, you need to exit to 'ready' mode for the connection to be established.
You can also turn on the trace and debug in the scrip to help diagnose your problems.
02-11-2014 01:59 PM
These days a mainframe running SAS would run partially as in the Open-part. When SAS/connect is installed and available the SAS spawner would fork BPXAS adress spaces. Check with SDSF wether you can find something of that. BPXAS is the Unix part of z/OS.
The best entry would be a less paranoid system programmer guy supporting SAS. He should tell you the port-number and more for using that.
Runnig this you have the best interface to mainframe data.
On TSO you can find WSA (workstation Agent) that is a dekstop cleint tool to be downloaded. It will communicate by TCP/IP and switch the TSO screens to captured Windows versions. It offers a download/upload for text files. In the 1990's the TCP/IP stack of IBM was actually using the SAS institute delivered one. WSA and SAS V6-V8 sharing the same stack on mainframes.
Some terminal emulators also offer some up-download but these are harder to configure (3270 datastreams).
FTP/SFTP are commonly closed as often a part connecting to JES2.
If you can start a SAS-spawner on you pc and you have a 3270 sas session it could be possible to connect you pc form you 3270 sas session.
02-11-2014 02:48 PM
Usually the SAS Spawner on a z/OS mainframe requires installation and setup by mainframe specialists. Since they already have a TSO id, it would be easier to connect that way. If the Spawner has not been setup, it would be less trouble to go the TSO route.
02-12-2014 09:04 AM
Would not expect old telnet or tn3270 would still be availlable as all things are setting to be encrypted.
02-14-2014 12:38 AM
Thanks for all the replies! At this point, I have gotten the connection strings all set up. I was over complicating things. Now I am stuck on the file formats. I read up on the differences between EBCDIC and ASCII and all the different methods to make it work - but I have not yer been successfull. The mainframe file has the following attributes - Org = PS, Format = VB, Rec Leng = 5004, block size 27998. From what I read the variable length records could be tough when transfered to different machine types. I have tried reading the file in changing virtually all the options (connection string below).
filename in1 ftp "'WCUSP.WCUSI010.Z.WCTBLB.WORK1'"
s370v RCMD='site rdw' RECFM=vb lrecl=5004 blocksize=27998 debug
host="ABC.DEF.COM" user="abcde" pass="1234";
Followed by a simple infile statement applying all the formats I thought might work. Any direction would be helpful.
02-14-2014 02:50 AM
recfm=vb is not needed when you already have the s370v option.
You need to use the $ebcdicXX. format for strings.
Quite likely you may have packed decimal data, which you have to read with the s370fpdXX. format.
Caution is needed if the file contains PL/1 or DB/2 VARCHAR data.
And don't forget the truncover option in the infile statement.
Our (working) method looks like that:
filename xxx "'filename'" host="hostname" user="user" pass="password" s370v rcmd="site rdw" lrecl=XXXX;
infile xxx truncover;
Note the single quotes inside the double quotes when supplying the filename, without single quotes the MF system will insert your TSO username before the filename!
(Cancel that, just saw you do this already)
Could you please give examples of the problems you still have?
Message changed by: Kurt Bremser
02-14-2014 10:22 PM
A simple trick to see what you are getting is to use the LIST statement.
infile xxx lrecl=10000 ;
if _n_ > 5 then stop;
This will show all of the non printable ascii codes as hex so you see what you are retrieving from the mainframe.
02-17-2014 03:25 PM
The list option helped me out quite a bit to visualize the data! I thought I had what I needed, but when I got into the data valadition piece, it looks like the at some point the alignment within the file changes and characters are being dropped or moved (likley part of the variable length). When I read the data in, it always says the min/max record length is 500....shouldn't it vary or am I missing something? I know very little about mainframe files/data and this is my first time using the file type.
I have a feeling the way I am 'viewing' the data set might be the issue. I have a SQL background and am trying to capture specific positions in the file with the needed data (like a substring) viewing it as one big 'row' of text. How would I complete something like this with a variable length record?
Any direction would be helpful.
filename in2 ftp "'ABCDE.WCA1I010.Z.WCTBLB.WORK1'"
RCMD='SITE RDW' lrecl=5004
host="kjbnd.gee.ht.hrgw.com" user="xxxx" pass="xxxx";
infile in2 TRUNCOVER;
format bl $3.;
format center $1.;
format name $21.;
format id $7.;
format level $2.;
format sub_level $1.;
format dept $3.;
format lang $2.;
format reporting_id $7.;
format id_type $1.;
format ca_type $1.;
format id_ty $1.;
@1 BL $23-25 @
@1 center $26 @
@1 name $30-70 @
@1 id $11-17 @
@1 level $104-105 @
@1 sub_level $106 @
@1 dept $26-28 @
@1 lang $414-415 @
@1 reporting_id $96-102 @
@1 id_type $462 @
@1 ca_type $107 @
@1 id_ty $2 @;
02-14-2014 01:27 AM
It is not only Ebcdic ASCII but also record-types and data-types.
You found a connection by using FTP, the naming (8.8.-.8) and dataset-types (VB) is indicating you are using classic MVS datasets.
SAS was orignally build on mainframes and it still knows all mainframe record-types and data-types even when you are running on Windows or Unix.
Start to understand what was you record and data structure on the mainframe and what tool does what conversion.
Normally you do not need to specify dataset attributes on a mainframe. The tool reading the dataset can also read all that information.
I would expect you FTP program is:
- reading all records and remove the record information (VB will specify 2 binary bytes indicating the record length)
- reading byte for byte the records and transform ebcdic to ascii according to his internal transformations. But remember encoding is far more challenging with code-pages.
This should work with character-only datasets. But many mainframe datasets build with Cobol are having zoned, packed and possible other binary types. The Ebcdic/Ascii transformations will effectively corrupt these kind of data making it impossible to read.
In this situatioon it will be better to download the file binary and doing no CR LF record transformation (two other options with FTP)
You will end up with a binary datasets unreadable by most tools but readabe with SAS when using the Mainframe recordtypes input and mainframe datatypes. TS-DOC: TS-642 - Reading EBCDIC Files on ASCII Systems
It is the same kind of advice found wiht mxg (www.mxg.com) part of SAS IT services
02-18-2014 02:34 PM
You did not follow the recommendations, ts-642 bentleyj1 has mentioned as first one.
z/OS V1R13 information center ( FTP subcommands, z/OS Communications Server: IP User's Guide and Commands SC31-8780-11)
Start the FTP BINARY..... Leave lrecl out of the filename as in the VB-sample of that note. It really describes very well the process.
When you get the listing as above you will notice the chars are Ebcdic not ASCII.
For the numeric fields used the according SAS formats SAS(R) 9.3 Formats and Informats: Reference (s370fpd...)
for the char fields use that Ebcdic format. SAS(R) 9.3 Formats and Informats: Reference.
(I got the list in the mail not seen here)
By that you can see the hex-representatation of all data. Seeing a lot of 20-ascii spaces where that is 40's with Ebcdic
Also series of 00's and some 00 1D's looking to be Packed-data. This is indicating an ASCII-Ebcdic transformation was done wihtoud being in control (columns) whether it shoudl be done that way.
For ebcic/ascii difference see IBM: