Errors similar to the following can occur in SAS Viya when PROC SQL queries are run against large amounts of data:
ERROR: Out of memory while converting blob to table.
ERROR: PROC SQL runtime error for operation=sqxsrc.
ERROR: Insufficient memory
ERROR: Error parsing member tkctb_blob of cas.Table.
ERROR: Error parsing member of type cas.Value.
ERROR: Error parsing member results of cas.Response.
ERROR: An error has occurred.
These errors indicate that an out of memory condition is occurring in the SPRE process when the engine is reading data from the CAS server. Data is transferred in blocks between the client and server and is then converted from a transfer format to a format the engine can consume. Out of memory conditions can occur during this process.
There are three workarounds.
- The CAS engine contains a Data Set and a LIBNAME option, ReadTransferSize=, that can change the size of the buffer used to transfer the data. The default size for the buffer is 500MB. If you reduce the ReadTransferSize= option setting, less memory is used because the data transfer buffer size is smaller.
- You can increase the MEMSIZE setting, which increases the amount of memory that can be used in SPRE.
- You can use the FedSQL procedure instead of the SQL procedure. PROC FedSQL code can be executed in CAS, whereas PROC SQL cannot. If the PROC SQL code can be converted to PROC FedSQL, then the transfer of data between the client and server is not necessary.