?
Solved

IBM ZOS FTP Best Practices

Posted on 2009-07-07
4
Medium Priority
?
3,341 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 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

In my humble opinion (IMHO), TouchDown from Symantec is the best in class for this type of application, but Symantec has end-of-lifed it and although one can keep using it, it will no longer be supported or upgraded.  Time to look for alternatives t…
The article explains the process to deploy a Self-Service password reset portal I developed a few years ago. Hopefully, it will prove useful to someone.  Any comments, bug reports etc. are welcome...
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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