SYMPTOM
Few reports in the Strategy Enterprise Manager 9.0.0 - 9.0.1 project fail with the following error:
Sample Code/Error
QueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. CURSOR IDENTIFIED IN FETCH OR CLOSE STATEMENT IS NOT OPEN (DIAG INFO: ). For more information, please consult DB2 document for error code SQL0501. STRING REPRESENTATION OF DATETIME VALUE HAS INVALID SYNTAX. Connection String: xxxxxx;. SQL Statement: with gopa1 as
(select a11.EM_USER_ID EM_USER_ID
from IS_SESSION a11
group by a11.EM_USER_ID
having (max(a11.IS_CONNECT_TS) < '03/17/2010'
and max(a11.IS_DISCONNECT_TS) < '03/17/2010')
The following reports display this problem:
CAUSE:
Observing the SQL seen in the above error message, the having clause is trying to filter data through the connect and disconnect timestamps. The having clause is as shown below:
Sample Code/Error
having (max(a11.IS_CONNECT_TS) < '03/17/2010'
and max(a11.IS_DISCONNECT_TS) < '03/17/2010')
The IS_CONNECT_TS and IS_DISCONNECT_TS columns in the IS_SESSION table are of datatype timestamp. In the above SQL clause, a timestamp data is being compared against value which is of the format 'mm/dd/yyyy'. On DB2, when using a timstamp column, the format always has to be of the format 'yyyy-mm-dd hh:nn:ss'. The default date pattern generated by Strategy SQL Engine for date datatype is 'mm/dd/yyyy'. The date datatype is being used here because of how the filter is defined.
ACTION
Modify the default SQL Date Format pattern generated for the Warehouse Database Instance pointing to the DB2 warehouse. Access the VLDB properties for the required database instance and access the SQL Date Format property under SELECT/INSERT. Change the pattern to 'yyyy-mm-dd hh:nn:ss' as shown below.
Note: It is required to restart the Strategy Intelligence Server to apply this change.
