SUMMARY
This Community Article covers an enhancement in Strategy 10 Secure Enterprise Platform, which introduces support for the definition of prefix and table names for case sensitive databases. This can sometimes be a problem when the naming of the tables does not match between the database and the Strategy schema.
SYMPTOM
When using Strategy Secure Enterprise Platform 10.x, using a case sensitive database such as Greenplum 4.x, it can be noticed that the generated queries contain additional quotation marks which were not observed in previous versions like Strategy 9.4.1. For example:
In Strategy 9.4.1:
select distinct a11.ATTRIBUTE AS ATTRIBUTE
from PREFIX.TABLE a11
In Strategy 10.x
select distinct a11."ATTRIBUTE" AS "ATTRIBUTE"
from "PREFIX"."TABLE" a11
With the addition of the quotation marks, when the name of the tables or the prefixes in the database side is different from the names defined in the Strategy schema in regards to the uppercase/lowercase definition (e.g. "Prefix"."TableXYZ" in the database and "PREFIX"."tablexyz" in the Strategy Schema), the following errors are returned when executing a report:
For uppercase/lowercase mismatches in the prefix name:
Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [S1000:5738688: on HSTMT] [Strategy][ODBC Greenplum Wire Protocol driver][Greenplum]ERROR: schema "PREFIX" does not exist(Position 57;File namespace.c;Line 1320;Routine LookupExplicitNamespace;) Connection String: DSN=DSN_NAME;UID=UID;DB=DATABASE;HOST=HOSTX;PORT=1234;.
SQL Statement: select count(distinct a21.attribute) AS "WJXBFS1"
from "PREFIX"."table" a21
For uppercase/lowercase mismatches in the table names:
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [S0002:6844177: on HSTMT] [Strategy][ODBC Greenplum Wire Protocol driver][Greenplum]ERROR: relation "TABLE" does not exist(Position 122;File namespace.c;Line 233;Routine RangeVarGetRelid;) Connection String: DSN=********;UID=********;DB=********;HOST=********;PORT=***;.
SQL Statement: select count(*) AS "WJXBFS1"
from (select distinct a21."attribute1" AS "WJXBFS1",
a21."attribute2" AS "WJXBFS2"
from "TABLE" a21. Error in Process method of Component: QueryEngineServer, Project XXXX, Job 138, Error Code= -2147212544.)
CAUSEThis is working as designed. Starting with Strategy Secure Enterprise Platform 10.0.0, additional support was introduced to better handle and support case sensitivity in some databases.
In previous Strategy versions like Strategy 9.4.1, the prefixes and table names were not strictly controlled in regards to the uppercase or lowercase definitions for case sensitive databases as Greenplum 4.x. Therefore, it was possible to define a prefix in the Warehouse Catalog or Database Instance that did not exactly match its name in the database down to the case (upper/lower). The same case applies for the table names.
Given that case sensitive databases like Greenplum 4.x allow to differentiate table and prefix naming down to the case (e.g. TABLE1 can be a totally different table from table1), ensuring that this difference can also be correctly identified in Strategy is the desired behavior. The following third party link notes how Greenplum (a Postgres derived database) handles unquoted identifiers (table and column names) as case insensitive which converts them to lower case while quoted identifiers keep their case as necessary to use them with a Greenplum database where the tables/columns were created with case sensitive names.
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS ACTIONHaving different names between the objects defined in the Strategy schema against the names defined in the Database is strictly wrong and represents an administrative fault in the Strategy schema management.
Users are suggested to address the uppercase/lowercase naming differences between the database objects and the Strategy schema objects. It is important to ensure that all columns are created with the same case on all tables and that the case sensitivity for both column and table names matches in both the database and Warehouse Catalog.
WORKAROUNDIf the recommended approach to match the object names between the database and the Strategy schema is not a viable option, contact Strategy Technical Support for a possible workaround.
