Solved

IBM ZOS FTP Best Practices

Posted on 2009-07-07
4
3,277 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
  • 3
4 Comments
 
LVL 57

Accepted Solution

by:
giltjr earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query regarding cond codes 12 679
Oracle enteprise one on IBM Power 7 4 390
AIX certified? 9 860
Second Window representing another PC 5 325
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now