Hi Folks,

I created a Data Module using DB2 BLU (columnar mode) tables.

When activating the Relative Time into my Time Dimension I got this error:

Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: No authorized routine named "TIMESTAMP" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.71.22

This is the query using only the Time Dimension filtering Current Month:
SELECT DISTINCT
"TIME_1"."PERIOD_NAME" AS "Period_Name",
"TIME_1"."BUSINESS_DATE" AS "Business_Date"
FROM
"D_W"."TIME" "TIME_1"
WHERE
"TIME_1"."BUSINESS_DATE" >= timestamp(DATE('2019-09-01'),'00:00:00') AND
"TIME_1"."BUSINESS_DATE" < timestamp(DATE('2019-10-01'),'00:00:00') FOR FETCH ONLY

Even running the query directly into DB2 console it fails, but if I remove the ,'00:00:00' from the query it runs perfectly.

Did anyone have this same error before? Do you think I should change the syntax of the filter into my Data Module or is it something wrong in my DB2 environment?

Thank you!

------------------------------
Vitor Flisch Cavalanti
------------------------------

#CognosAnalyticswithWatson

The expression should be resolved by Db2

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000859.html?pos=2

BTW, you should update your IBM JCC version. Old versions such as shown in your message were not certified for IBM JRE 8 which Cognos Analytics uses.

------------------------------
NIGEL CAMPBELL
------------------------------

is BUSINESS_DATE a date datatype or is it datetime?

------------------------------
NORBERT Bracke
------------------------------

it would be a timestamp as the right hand side used a date literal which had to be type cast to a timestamp type.

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Mon September 30, 2019 06:56 AM
From: NORBERT Bracke
Subject: Relative time functions using DB2 BLU

is BUSINESS_DATE a date datatype or is it datetime?

------------------------------
NORBERT Bracke
------------------------------

@NIGEL CAMPBELL

The column is TIMESTAMP both in the Database:



------------------------------
Vitor Flisch Cavalanti
------------------------------

Original Message:
Sent: Mon September 30, 2019 07:43 AM
From: NIGEL CAMPBELL
Subject: Relative time functions using DB2 BLU

it would be a timestamp as the right hand side used a date literal which had to be type cast to a timestamp type.

------------------------------
NIGEL CAMPBELL
------------------------------


Original Message:
Sent: Mon September 30, 2019 06:56 AM
From: NORBERT Bracke
Subject: Relative time functions using DB2 BLU

is BUSINESS_DATE a date datatype or is it datetime?

------------------------------
NORBERT Bracke