topic Re: How to fetch data faster from Mainframe and load data faster into SQL in All Things Community

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

Sharath_naik — Fri, 14 Feb 2020 14:55:18 GMT

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.

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

SASKiwi — Sat, 15 Feb 2020 04:04:35 GMT

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?

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

Tom — Sat, 15 Feb 2020 18:12:35 GMT

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.