topic Re: Proc SQL Completes, but Redshift Errors Out on Memory Full Error in SAS Procedures

Proc SQL Completes, but Redshift Errors Out on Memory Full Error

Pretti — Mon, 22 Jul 2019 19:12:06 GMT

We are using explicit SQL pass through statements to query a large table in Redshift. The Proc SQL completes without a SAS error but has only about half the number of records we were expecting. Our DBA reviewed the Redshift database log and found the query had an out of memory error and stopped fetching additional rows from the table.

Since these are production Jobs we need to have the SAS Proc SQL Step fail with an error as we do not want to continue the process with an incomplete data set. Additionally, since this is a "silent" error, our users would never know there was problem.

Does anyone know how to pass this Redshift error down to our SAS job?

Thanks.

Re: Proc SQL Completes, but Redshift Errors Out on Memory Full Error

Tom — Mon, 22 Jul 2019 19:19:11 GMT

Are you using SAS/Access to Redshift? Or SAS/Access to ODBC?

You might want to raise a support ticket with SAS support.

Re: Proc SQL Completes, but Redshift Errors Out on Memory Full Error

Reeza — Mon, 22 Jul 2019 19:54:03 GMT

Agreeing with Tom, this is a question for SAS tech support for sure.

Re: Proc SQL Completes, but Redshift Errors Out on Memory Full Error

Pretti — Tue, 23 Jul 2019 19:13:38 GMT

I first contacted SAS support and the response was to work with our DBA to optimize our queries to get them to run with the size of our memory. This won't work as the database is shared resource and the total amount of available memory at any given time will be unknown. I'll continue to experiment to see if I can find a solution.

Re: Proc SQL Completes, but Redshift Errors Out on Memory Full Error

Reeza — Tue, 23 Jul 2019 19:33:09 GMT

Have you tried implicit SQL queries instead? Do those error out as indicated?