Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

IBM ZOS FTP Best Practices

Posted on 2009-07-07
4
Medium Priority
?
3,316 Views
Last Modified: 2012-05-07
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!
0
Comment
Question by:ttist25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 57

Accepted Solution

by:
giltjr earned 2000 total points
ID: 24802609
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
 
LVL 57

Expert Comment

by:giltjr
ID: 24802644
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
 
LVL 1

Author Comment

by:ttist25
ID: 24812491
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
 
LVL 57

Expert Comment

by:giltjr
ID: 24813870
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
On September 18, Experts Exchange launched the first installment of the Help Bell, a new feature for Premium Members, Team Accounts, and Qualified Experts. The Help Bell will serve as an additional tool to help teams increase question visibility.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question