SYMPTOMS
When doing a select from a large table in PADB you either see the following error:
Sample Code/Error
2014-05-16 06:17:13.210-05:00 Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. out of memory for query result
Connection String: DSN=TEST;DB=TEST;UID=TEST;HOST=x.x.x.x.x;PORT=5439;. SQL Statement: select *
from public.test_table
OR the application where you run the select statement will hang.
If you limit the number of rows or select particular columns, you can avoid this error
CAUSE:
The driver streams all the data to the client first instead of fetching increments of data.
ACTION:
The solution is to use the client side cursor.
If the ODBC data source is on Windows, you configure it using the DSN setup "Cursor" tab.
For a 32 bit windows driver ,typically enable it wth the threshold at 1024 MB and leave the default path and default max file size.
Option Description:
Enable - If checked, enables the client side cursor.
Threshold (in MB) Threshold memory cursor size (in MB). If the client side cursor is enabled and the result set for a query exceeds the memory amount in threshold, the excess data threshold gets saved in a file in the directory specified in Path. A value of 0 means no threshold. Negative values are treated as 0.
Path - The directory in which to store the client side cursor files. If the provided path does not exist, the value is changed to the default path: TMP/TEMP.
Max File Size (in MB) - The maximum disk cursor size (in MB) per statement for the client side cursor. An error occurs if the result set for a single query exceeds this limit. A value of 0 means no limit. Negative values are treated as 0.
If you are using a Linux ODBC you have to update the INI file. If you are using a DSN'less connection, contact Actian Matrix support as this is done differently.
For any other questions regarding this, contact Actian Matrix support.
WARNING:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.
