We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

IBM ZOS FTP Best Practices

ttist25
ttist25 asked
on
Medium Priority
3,438 Views
Last Modified: 2020-04-13
Hey there,

I'm about to start work on a project that will export data from an IBM DB2 dataset and use that data to populate a MSSQL DB.

We have created a small  COBOL program to extract this data to delimited format in the past and will probably use something similar for this task.

I'm thinking about using FTP to transfer the data to our MSSQL backend but I'm a little sketchy on the details of how that is normally accomplished due to my infamiliarity of ZOS.

Is there a utility in zos that allows automated connection and transfer to an FTP server?  What would be the normal way to accomplish the transfer from zos to the SQL server?

Just a high level explanation should suffice.  We will get into details later on.

TIA!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2014

Commented:
Also, not sure if you want to look into alternatives, but:

The ftp server on z/OS has a "connection" to DB2.  I have not used it, but based on the doc, from the SQL server you could login to the ftp server on z/OS and then issue a "get mainframe.file.name pc.filename" where the data set "mainframe.file.name" contains SQL select statments and the ftp server will pass the select to DB2, which will extract the data, pass it back to the ftp server, which when then pass it back to the client and put the results in "pc.filename".

Also, you can get DB2 client and setup DB2 as a ODBC data source and from the SQL server write a program that extracts from DB2 and inserts into MQSQL server.  I'm not 100% sure but at one time I thought that MS was going to make MSSQL support DRDA, which is the standard for DB2 data bases to talk to each other over a network.  

Author

Commented:
Thanks a bunch.  That's exactly what I was looking for.  

I have considered a connector from MSSQL to DB2 but I haven't read much about success with that.

I think automating our COBOL program and then running FTP in a batch as you suggest will probably be the best way.

Thanks again.
CERTIFIED EXPERT
Top Expert 2014

Commented:
If you want more error checking or more functionality you can also call ftp from a REXX EXEC.  But for basic ftp you can just use what I have above.  That is basically what we use for about 2,000 jobs.

Not sure how you are checking for the presence of the file on the Window side, but since file locking in the distributed world is generally non-exsistant you may want to do:

put 'MAINFRAME.FILE.NAME' +                
     pcfile.name.tmp
rename pcfile.name.tmp pcfile.name                          
quit                                      

We had some problems where the traget system was starting  a process based on the fact that 'pcfile.name' existed and when the file was big the process would get started before the transfer was finished.  Caused all types of problems.  So we started sending to ".tm" names and then renamed to the real name.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.