How to fetch data faster from Mainframe and load data faster into SQL

[Sharath_naik]

Currently I'm using below code

To fetch data from Mainframe 

FILENAME dset1 FTP "'&fac'" RECFM=VB LRECL=266 USER=&USERID PASS=&password HOST="&host" DEBUG PROMPT;

To load into SQL 

libname sql_test odbc dsn=XXXX_test user="XXXXX" pass="XXXXXX" schema=XXXXX;

proc sql;
connect to odbc (dsn=XXXXX_test user=XXXX pwd=XXXX);
execute(truncate table Schema.ABC) by odbc;
insert into sql_test.ABC(Name, height)
select * from _all;
disconnect from odbc;
quit ;

...............................................................................................................................................................

Above code is working perfectly fine, but I'm looking for faster way to load and fetch data... please help me.

[SASKiwi]

There are no options for speeding up single FTP file transfers - it will use whatever network bandwidth it is allowed. You could run two or more FTP file transfers in parallel by selecting different sets of records from the originating file then append them on the target computer. You could try parallel SQL loads as well instead of appending.

How long do the FTP step and SQL load take now?

[Tom]

Those are two different types of operations.  In the first one you are just pointing a fileref at a file that you will access with with the FTP protocol.  To turn that file into data you will need to read it with a data step.  No data will transfer until you try to read from that fileref.

In the second you appear to be appending data to an existing table in your remote database via the ODBC engine.  To improve transfer you might need to get the SAS/Access product for your specific to your database instead of using generic ODBC engine. Look for bulk load options on those drivers.