I'm running SAS Access/ODBC on Windows 2012 access Redshift.
Using the Postgres Redshift ODBC Driver.
I can access fine via SAS but when I try to use the SAS 'BULKLOAD=YES' option, I get this error:
ERROR: Unable to initialize bulk loader.
There is not option in the Progress ODBC configuration to turn on Bulkload even though I've seen documentation on the web discussing this option in the Progress ODBC Configuration window.
http://support.sas.com/resources/papers/proceedings15/SAS1789-2015.pdf
Does that Windows ODBC driver support bulk load?
The DataDirect Redshift ODBC driver will transparently leverage the fastest load API available without having to set 'BULKLOAD=YES' in SAS. Let us know how the testing goes.
Actually no it doesn't. With or without BULKLOAD set the upload in to Redshift results in 178 singleton INSERT statements. On an MPP database this is extremely slow. I'm seeing the same behavior on SAS/Windows and SAS/AIX.
The Progress bulk loader isn’t getting evoked. If I’m INSERT’ing 100 values in to a table via the Progress ODBC driver, then on the database side I see 100 INSERT statements logged. That means there are 100 singleton INSERTS, and no optimization is getting done. If in fact Progress was INSERT’ing via an implicit INSERT then I would expect to see a few INSERTS. I tried to turn on the ODBC tracing but nothing is showing up in the log.
I've tested this on WIndows as well and I see the same issue.
178 rows need to be pushed from SAS to Redshift, and in the Redshift SQL logs I see 178 singleton INSERTS.
If this driver is supposed to do it implicitly it's not working. If this feature is supposed to be turned on, there is no ODBC manager option to turn it on.
Thank you for the update. We'd like to get an ODBC trace log and we will reach out via e-mail to collect it. Here are steps for reference: msdn.microsoft.com/.../ms711020