It's all about the answers!

Ask a question

Rational Insight 1.0.1 Why is RIODS.REQUEST_CHANGE_HISTORY.CHANGE_DATE truncating time?


Dave Decker (331516) | asked Mar 03 '14, 12:50 p.m.
Rational Insight 1.0.1 Why is RIODS.REQUEST_CHANGE_HISTORY.CHANGE_DATE truncating time?

2 answers



permanent link
Marc Nehme (276157) | answered Mar 05 '14, 4:14 p.m.
JAZZ DEVELOPER
Hi Dave, I see the character length is set to 10 by default for the CHANGE_DATE column. What does your timestamp look like (truncated)?Are you seeing this truncation in the column itself (viewing your acutal database tables) or on the reporting side through the framework? Depending on what product you are using, open that respective fact build, go to the second icon (data source) and test the SQL to see if it is also truncated when viewing from the source query.

permanent link
Dave Decker (331516) | answered Mar 06 '14, 2:37 p.m.
Thanks Marc;
RIODS.REQUEST_CHANGE_HISTORY.CHANGE_DATE is
Data Type : datetime
Length : 8
Numeric Precision : 23
Numeric Scale : 3

In looking at the fact build in cognos data manager, the TIMESTAMP_COL is going through a calculation to derive CHANGE_DATE:
if (DBMS( $Connection )='MICROSOFT SQL SERVER')
then return PosixTime_To_Timestamp( ToInteger( TIMESTAMP_COL ) );
else return ToDate( TIMESTAMP_COL, 'yyyy-mm-dd' );

When i try to test this query in data manager:
SELECT    W."FACT_ID",
    TI."{$TIMESTAMP_COL}",
    W."WI_ID",
    W."PREV_TEAM_AREA_ID",
    W."PREV_CATEGORY_ID",
    W."PREV_ITERATION_ID",
    TP."WI_TYPE",
    W."PREV_OWNER_ID",
    ST."STATE_NAME",
    P."WI_PRIORITY",
    SV."WI_SEVERITY",
    "PREV_COMMENT_COUNT",
    "PREV_SUBSCRIBER_COUNT",
    "PREV_TAGS",
    "PREV_DURATION",
    "PREV_TIME_SPENT",
    "PREV_SUMMARY"
FROM   {$WORKITEM_CHNGS} W
LEFT JOIN {$TIME_TBL} TI ON W."TIME_ID"=TI."TIME_ID"
LEFT JOIN {$TYPE_TBL} TP ON W."PREV_WI_TYPE_ID"=TP."TYPE_ID"
LEFT JOIN {$STATE_TBL} ST ON W."PREV_WI_STATE_ID"=ST."{$STATE_COL}"
LEFT JOIN {$PRIORITY} P ON W."PREV_WI_PRIORITY_ID"=P."PRIORITY_ID"
LEFT JOIN {$SEVERITY} SV ON W."PREV_WI_SEVERITY_ID"=SV."SEVERITY_ID"
 i get prompted for the following variables and i'm not sure of the values to provide:
$TIMESTAMP_COL
$WORKITEM_CHNGS
$TIME_TBL
$TYPE_TBL
$STATE_TBL
$STATE_COL
$PRIORITY
$SEVERITY
and i'm challenged in getting the correct set of values for all...

Comments
Marc Nehme commented Mar 11 '14, 4:16 p.m. | edited Mar 11 '14, 4:18 p.m.
JAZZ DEVELOPER

Dave, as far as the variables, check a previous successful fact build log (for this specific fact build) and you may find some of those values specified, which will help your test scenario above.

Based on the query you provided above, I can see you are running on an older version of RTC (pre-3.0.1.1). Your column (CHANGE_DATE) you said is 8 in length and of type 'datetime'. If its 8 characters, then your date would have no day value and show something like '2014-03-' ? If so, you can increase the size of that column to 10, the date would then be '2014-03-11'. I am not sure what yours looks like. The ETL normally complains if it tries to insert a 10 character value into an 8 character column, so I am not positive this is the fix without looking at the environment.

I do not know how long this has been going on in your environment, or if you just happened to notice, but it may be best if you open a PMR with Support so they can look at the logs and your environment.

Your answer


Register or to post your answer.