Hello, fellow communities members!
Wanted to share with you an issue we have hit with Data Warehouse, turned out to be a bug.
Affected versions: CA PPM 15.1.0
Fix for 220.127.116.11
Not reproducible on other versions. Oracle and MSSQL STR: 1. Connect to CA PPM 15.1.0 2. Go to Administration - System Options - add Spanish language to Data Warehouse 3. Save 4. Run DWH job - Full, note it runs well 5. Now upgrade the system to 18.104.22.168 6. Run Load Data Warehouse - Full Expected Results: The job to run correctly Actual Results: Load Data Warehouse fails with error message: 2018/01/19 15:00:39 - User Defined Java Class.0 - 2018/01/19 15:00:39 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12899: value too large for column "PPM1510_DWH"."DWH_LKP_TRANSLATION"."TRANSLATION" (actual: 1428, maximum: 255) Alternate MSSQL message: Error message: ERROR 2017-12-29 10:20:06,317 [Thread-53050] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry : Couldn't execute SQL: INSERT INTO DWH_LKP_TRANSLATION(CLARITY_UPDATED_DATE, IS_ACTIVE, LANGUAGE_CODE, LANGUAGE_CODE_KEY, SORT_ORDER, TRANSLATION, TRANSLATION_KEY, dw_updated_date) select LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, IS_ACTIVE as IS_ACTIVE, LANGUAGE_CODE as LANGUAGE_CODE, LANGUAGE_ID as LANGUAGE_CODE_KEY, SORT_ORDER as SORT_ORDER, NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, CONVERT(DATETIME ,'2017-12-29 10:20:05') as dw_updated_date from [PPMDBLINK].niku.niku.DWH_LOOKUPS_V where 1=1 AND LAST_UPDATED_DATE >= CONVERT(DATETIME,'1910/01/01 00:00:00') [CA Clarity][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated. When checking we can see that when running this query on PPM: select NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, length(name) from DWH_LOOKUPS_V order by length(name) desc There is the following value on an OOTB attribute that gets corrupted: Lookup Type PAC_GL_RPTS Lookup value GL_RPT_POST_MATRIX_UNDEFINED Lookup Type: GL_RPT_POST_MATRIX_UNDEFINED Length 1411 (max DWH allowed is 255) Workaround: 1. Go to Administration - Lookups 2. Open the lookup PAC_GL_RPTS 3. Go Values 4. Find in the list the Value GL_RPT_POST_MATRIX_UNDEFINED 5. Click on the little Globe icon (Translate) 6. Reset the Translation (NAME field) in Spanish (and any other included in DWH language) to be under 255 characters, i.e. " No se ha definido la matriz del libro mayor" 7. Save 8. Run Load Data Warehouse - Full If after the above there is still an issue query: select NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, length(name) from DWH_LOOKUPS_V order by length(name) desc Any lookup translations over 255 will have to be manually modified as per above.
Hope this helps! -Nika