SYMPTOM:
Greenplum 3.0.x is supported as a warehouse with Strategy Intelligence Server 8.1.1 running on Windows and Linux operating systems. Refer to the following Strategy Knowledge Base technical note for details on configuring Strategy 8.1.1 to work with Greenplum:
KB16772 (KB5200-811-2367): How to configure Strategy products to work with Greenplum platform
In spite of using the recommended drivers (on Windows - 32-bit PostgreSQL ODBC Driver 8.01.02.00 or 32-bit PostgreSQL ODBC Driver 8.02.04.00 and on Linux - 32-bit PostgreSQL ODBC Driver 7.01.0003.), it is observed that the SQL queries get executed on the database as cursor queries. Greenplum database logs show the following for a simple report execution:
.......
LOG: execute fetch from <unnamed>/SQL_CUR0xf5c009f8: BEGIN;declare
"SQL_CUR0xf5c009f8" cursor with hold for select distinct a11.ip_country
AS ip_country
from user.ip a11;fetch 100 in "SQL_CUR0xf5c009f8"
2008-02-04 16:11:42
........
As seen in the above logs (bolded part), SQL is submitted as cursor queries instead of normal SQL queries.
CAUSE:
GreenPlum uses cursors by default due to some memory/performance related issues.
ACTION:
Set a parameter called UserDeclareFetch to 0 in the ODBC DSN. On Linux, this setting should be directly added to the DSN in the odbc.ini file (the odbc.ini file is in the HOME folder of Strategy installation.)
Follow the steps below to resolve the issue:
