IBM ZOS FTP Best Practices

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!
LVL 1
ttist25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

giltjrCommented:
z/OS has ftp client that you can run in batch.  The simplest way to use it would be to have JOB setup something like:

//S01OF01 EXEC PGM=FTP,REGION=4096K        
//SYSPRINT DD SYSOUT=*                    
//OUTPUT   DD SYSOUT=*                    
//INPUT    DD *                            
10.1.1.1  (exit                            
userid                                    
password                                  
put 'MAINFRAME.FILE.NAME' +                
     pcfile.name                          
quit                                      
/*                                        



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
giltjrCommented:
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.  
0
ttist25Author 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.
0
giltjrCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Mainframe OS

From novice to tech pro — start learning today.