ETI-NET

BCOM, among other features, permits the automatic, unattended transfer of files between various platforms. This technical note shows how to use BCOM to transfer data from a mainframe platform (MVS) to a SQL Server. (Note: The same general approach works for data residing on Tandem, OS/2, Windows NT, etc.)

The following steps must be performed to load a SQL Server table under Windows NT:

Data is extracted by the user and put into a flat file on the originating platform.
A Send File (SF) request is queue to BCOM (through the BINT interface or the batch interface, which will allow the SF request to be triggered as part of the regular schedule) to transfer the flat file to the BCOM workstation. For the rest of the discussion, let's assume that the name of the file is c:\temp\user.dat.
A Remote Job (RJ) request is queue to BCOM (we suggest that you chain the SF request through BCOM's Follow-On Scheduling). This remote job, which is a file, calls the bcp utility program to load the data to the SQL table. Sample (1) shows the content of this remote job. The file USER.DAT has been transferred from the MVS platform whereas the FORMAT.FIL describes the flat file format (see the documentation for program bcp).

With the general approach, the file to be loaded to the SQL table is transferred to the BCOM workstation and the bcp program accesses the SQL Server remotely. Other approaches are possible:

BCOM resides on the same workstation as SQL Server.
BCOM and SQL Server reside on different workstations. The Send File (SF) request specifies a mapped network drive in the Remote File field. The flat file is thus written directly to the SQL Server hard disk. The Remote Job (Sample 2) calls the rcmd utility program (from Microsoft Resource Kit) which is used to trigger the execution of a .bat file on the remote SQL Server.



Source LOAD.BAT
ECHO OFF
ISQL . . .

BCP DB.USER.TABLE IN C:\TEMP\USER.DAT /F FORMAT.FIL /S SQLSRVR

Source RLOAD.BAT