Exporting historical data using SFTP

You can export your extracted data or CSV file out of the mainframe for analysis or another purpose after performing the following steps:
1. Choosing elements from the data map
2. Extracting the history data set
3. Gathering the relevant elements
4. Converting the elements to displayable format
5. Creating the CSV file

See Extracting historical data (PTFs BQY1950 and BQY2072 applied) and Transforming historical data (PTF BQY2138 applied) for more details.

This topic presents an example of one method of exporting a CSV file using secure FTP (SFTP). 

This method involves:

• Preparing the server side
• Running the SFTP client

SFTP is a secure method for transferring files via the SSH protocol. It supports the full security and authentication functionality of SSH.

For more information about SFTP, see the IBM documentation.

Before you begin

• An SFTP client is required to connect to the server and download the CSV file.
• For the client to work, you must already have z/OS OpenSSH installed on the system. For more information about OpenSSH, see the IBM documentation.

Preparing the server side

The authentication type is based on the server configuration. In this example the authentication type is a pair of public/private keys:

• Id_rsa is the private key.
• Id_rsa.pub is the public key that is stored on the server side to be used for authentication.

The keys are copied to the .ssh folder in your home library.

To prepare the server side

1. In a UNIX environment, run the following command to generate public/private keys:

   SSH-KEYGEN -T RSA

2. On the server side, add the contents of your id_rsa.pub file to the end the authorized_keys and authorized_keys2 files. If these files do not yet exist, create them.
3. Connect to the server manually the first time to verify that the connection works, and to add a fingerprint of the server to the known_hosts file in the .ssh folder.

Running the SFTP client

Use the following JCL as an SFTP client to enable connection to the  server:

//MVSSFTP JOB  ...
//SFTP EXEC PGM=BPXBATCH,REGION=0M 
//STDPARM DD * 
SH cat "//'<userName>.CSV.MQSE1'"|iconv -f IBM-1047 -t ISO8859-1>Local_CSV_fileName; 
echo 'put Local_CSV_filename Remote_CSV_fileName' > uplcmd; 
sftp -B UPLCMD <userName>@<IP Address>; 
/* 
//SYSPRINT DD SYSOUT=* 
//STDOUT DD SYSOUT=* 
//STDENV DD * 
/* 
//STDERR DD SYSOUT=* 
//

The UPLCMD member includes the following command for the SFTP console to upload the CSV file to the remote server:

put ‘<path in localHost>’ ‘<path in remoteHost>’