DATA Step, Macro, Functions and more

Moving mainframe data to a PC

Reply
Occasional Contributor
Posts: 12

Moving mainframe data to a PC

Hello,

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:

 

  • open IBM WebSphere Host On-Demand  through internet explorer
  • start a 3270 session
  • type in ‘srtso myuserid’ hit enter
  • type in mypassword hit enter
  • at log on, it states  ‘xxxxx.ispf.profile’ used as profile data set (does this mean there is a spawner – what is a spawner?) – hit enter
  • ‘xxxxx.tsoutil.clist(startup)’ will be executed as your start up clist – hit enter
  • This brings me to the ISPF/PDF primary option menu

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.

    

Thanks

Drew

Super User
Posts: 5,254

Re: Moving mainframe data to a PC

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.

Data never sleeps
Occasional Contributor
Posts: 12

Re: Moving mainframe data to a PC

Thanks for the replies.  I understand this is not a SAS issue, but a user issue Smiley Wink.  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.

Thanks

Drew     

Occasional Contributor
Posts: 6

Re: Moving mainframe data to a PC

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.

Contributor
Posts: 63

Re: Moving mainframe data to a PC

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

     https://support.sas.com/techsup/technote/ts642.html

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.

Good luck!

John

Super Contributor
Posts: 358

Re: Moving mainframe data to a PC

Hi:

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.

Valued Guide
Posts: 3,208

Re: Moving mainframe data to a PC

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.       

---->-- ja karman --<-----
Super Contributor
Posts: 358

Re: Moving mainframe data to a PC

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.

Valued Guide
Posts: 3,208

Re: Moving mainframe data to a PC

Would not expect old telnet or tn3270 would still be availlable as all things are setting to be encrypted.

http://support.sas.com/documentation/cdl/en/camref/61896/HTML/default/viewer.htm#a000216242.htm

---->-- ja karman --<-----
Occasional Contributor
Posts: 12

Re: Moving mainframe data to a PC

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.

Thanks

Drew

Super User
Posts: 6,928

Re: Moving mainframe data to a PC

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;

data yyyy;

infile xxx truncover;

...

run;

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?

HTH

Message changed by: Kurt Bremser

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,497

Re: Moving mainframe data to a PC

A simple trick to see what you are getting is to use the LIST statement.

data _null_;

   infile xxx lrecl=10000 ;

   input;

   list;

  if _n_ > 5 then stop;

run;

This will show all of the non printable ascii codes as hex so you see what you are retrieving from the mainframe.

Occasional Contributor
Posts: 12

Re: Moving mainframe data to a PC

Thanks again!

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.

Thanks

Drew

Code

filename in2 ftp "'ABCDE.WCA1I010.Z.WCTBLB.WORK1'"
RCMD='SITE RDW'  lrecl=5004
host="kjbnd.gee.ht.hrgw.com" user="xxxx" pass="xxxx";

data US_BOX_RAW;
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.;


input
@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 @;

run;

Valued Guide
Posts: 3,208

Re: Moving mainframe data to a PC

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

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: Moving mainframe data to a PC

You did not follow the recommendations, ts-642  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)

http://publib.boulder.ibm.com/infocenter/zvm/v6r1/index.jsp?topic=/com.ibm.zvm.v610.kijl0/dattrn.htm

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: 

https://publib.boulder.ibm.com/infocenter/comphelp/v8v101/index.jsp?topic=%2Fcom.ibm.xlf101a.doc%2Fx...

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 14 replies
  • 2822 views
  • 0 likes
  • 8 in conversation