topic Re: Unable to Transfer Large SAS Dataset to Teradata Using ODBC in SAS Programming

Unable to Transfer Large SAS Dataset to Teradata Using ODBC

sasmaverick — Wed, 15 Jun 2022 09:28:12 GMT

I have an extremely large SAS dataset (15mn rows) that I want to create as a table in Teradata. I do not have SAS/ACCESS Interface to Teradata, hence trying to use the SAS/ACCESS Interface to ODBC. The problem is it takes an extremely long time to create table in Teradata using the ODBC approach.

What is the workaround?

Re: Unable to Transfer Large SAS Dataset to Teradata Using ODBC

Ksharp — Wed, 15 Jun 2022 12:02:51 GMT

You could check option of libname like :
readbuff=10000
insertbuff=10000
connection=unique
dbcommit=yes
...........

Also calling expert about this topic : @LinusH @SASKiwi

Re: Unable to Transfer Large SAS Dataset to Teradata Using ODBC

LinusH — Wed, 15 Jun 2022 13:00:10 GMT

Unfortenately, bulk load (or the different Teradata counterparts) is only available when you have the native SAS/ACCESS to Teradata product.

As @Ksharp already pointed out, INSERTBUFF= is the option you can tweak.

Re: Unable to Transfer Large SAS Dataset to Teradata Using ODBC

Tom — Wed, 15 Jun 2022 15:24:31 GMT

@sasmaverick wrote:

I have an extremely large SAS dataset (15mn rows) that I want to create as a table in Teradata. I do not have SAS/ACCESS Interface to Teradata, hence trying to use the SAS/ACCESS Interface to ODBC. The problem is it takes an extremely long time to create table in Teradata using the ODBC approach.

What is the workaround?

If you don't have SAS/Access to Teradata then work with your Teradata database team to show you how to use the Teradata tools for bulk loading data. Dump the SAS dataset to a text file and load that into Teradata using the tools from Teradata.

Re: Unable to Transfer Large SAS Dataset to Teradata Using ODBC

SASKiwi — Wed, 15 Jun 2022 20:39:53 GMT

How long is extremely long? In my experience SAS's default database table loading settings such as DBCOMMIT and INSERTBUFF are not optimal. You should experiment with load times using a much smaller sample of rows, say 100,000. Set DBCOMMIT / INSERTBUFF to say 5,000 (default is 1,000) to start with then try increasing it. What SAS code are you using to load the data?