Clarity

After updating ca ppm from 15.1 to 15.2 I do not have the table DWH_CMN ERROR_MESSAGE

Can I to do some step sequence for add this table?
Can anyone help me?

My error when I run the job LOAD DATA WAREHOSE.

ERROR 2017-05-03 15:45:15,367 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2017/05/03 15:45:15 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
2017/05/03 15:45:15 - dwh_db_check_ - Couldn't execute SQL: TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE;
2017/05/03 15:45:15 - dwh_db_check_ - EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'
2017/05/03 15:45:15 - dwh_db_check_ -
2017/05/03 15:45:15 - dwh_db_check_ - [CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot find the object "DWH_CMN_ERROR_MESSAGE" because it does not exist or you do not have permissions.
null

Seems to be permission issue

NJ

Hi Ricardo,

It looks like during upgrade it did not upgrade the DWH schema properly, can you please import the blank DWH schema and run the command 

admin db dwh-upgrade -Dupgrade.phase=maintenance

This command will upgrade DWH schema to PPM version level and then you can run the DWH Job

Let me know how it goes

Regards

Suman Pramanik 
 

Summan when I execute: admin db dwh-upgrade -Dupgrade.phase=maintenance.... this occurrs

D:\CAPPM\CAPPM152\CAPPM152\bin>admin db dwh-upgrade -Dupgrade.phase=maintenance
upgrade.phase = maintenance
install.upgrade.dir = D:\CAPPM\CAPPM152\CAPPM152/maintenance
maintenance.release = false
Installing schema file: D:\CAPPM\CAPPM152\CAPPM152/maintenance/infrastructure/dw
hschema/function/IS_DWH_CUSTOMER_DB.xml...
==========================================
DBTools Log - Fri May 05 17:40:14 GFT 2017
==========================================

Command: install database schema file
To URL: jdbc:clarity:sqlserver://NXPPMLAB;DatabaseName=ppm_dwh;InsensitiveResult
SetBufferSize=0;ProgramName=Clarity
As user: ppm_dwh
For vendor: mssql

Process: D:\CAPPM\CAPPM152\CAPPM152\maintenance\infrastructure\dwhschema\functio
n\IS_DWH_CUSTOMER_DB.xml

Total time: 0H:0M:2S
Error occurred: D:\CAPPM\CAPPM152\CAPPM152\.setup\scripts\db.xml:1893: The follo
wing error occurred while executing this line:
D:\CAPPM\CAPPM152\CAPPM152\.setup\scripts\db.xml:1914: The following error occur
red while executing this line:
D:\CAPPM\CAPPM152\CAPPM152\.setup\scripts\db.xml:2752: The following error occur
red while executing this line:
D:\CAPPM\CAPPM152\CAPPM152\.setup\scripts\db.xml:893: java.io.FileNotFoundExcept
ion: D:\CAPPM\CAPPM152\CAPPM152\maintenance\infrastructure\dwhschema\function\IS
_DWH_CUSTOMER_DB.xml (The system cannot find the path specified)
Check admin.log or use -verbose for more information.

Hi Ricardo,

I guess you have 15.2 base and if its base you don't need to run the maintenance command. It would be better if you can attach the bg-dwh log and we can take a look at the same and let you know. 

Regards

Suman Pramanik 

Hi Suman, this is the file bg-dwh of that date...
Please you see my last response to Shubham about the sentence admin...

thank you in advance

INFO 2017-05-03 15:45:14,464 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event Job Parameters
INFO 2017-05-03 15:45:14,465 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event SRC_DATABASE_URL: jdbc:clarity:sqlserver://NXPPMLAB;DatabaseName=niku;InsensitiveResultSetBufferSize=0;ProgramName=Clarity
INFO 2017-05-03 15:45:14,465 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event SRC_DATABASE_CLASS: com.ca.clarity.jdbc.sqlserver.SQLServerDriver
INFO 2017-05-03 15:45:14,465 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event SRC_DATABASE_USER: niku
INFO 2017-05-03 15:45:14,465 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event TGT_DATABASE_URL: jdbc:clarity:sqlserver://NXPPMLAB;DatabaseName=ppm_dwh;InsensitiveResultSetBufferSize=0;ProgramName=Clarity
INFO 2017-05-03 15:45:14,465 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event TGT_DATABASE_CLASS: com.ca.clarity.jdbc.sqlserver.SQLServerDriver
INFO 2017-05-03 15:45:14,465 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event TGT_DATABASE_USER: ppm_dwh
INFO 2017-05-03 15:45:14,465 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event CLARITY_DB_TYPE: mssql
INFO 2017-05-03 15:45:14,466 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event DWH_DB_TYPE: mssql
INFO 2017-05-03 15:45:14,466 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event SRC_DBNAME: niku
INFO 2017-05-03 15:45:14,466 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event DB_LINK: PPMDBLINK
INFO 2017-05-03 15:45:15,133 [Thread-45] dwh.event dwh_etl_master - Start of job execution
ERROR 2017-05-03 15:45:15,330 [Thread-45] dwh.event dwh_db_check_ - An error occurred executing this job entry :
Couldn't execute SQL: TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE;
EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'

[CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot find the object "DWH_CMN_ERROR_MESSAGE" because it does not exist or you do not have permissions.

INFO 2017-05-03 15:45:15,347 [Thread-45] dwh.event dwh_etl_master - Job execution finished
ERROR 2017-05-03 15:45:15,367 [Dispatch Load Data Warehouse : bg@NXPPMLAB (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2017/05/03 15:45:15 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
2017/05/03 15:45:15 - dwh_db_check_ - Couldn't execute SQL: TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE;
2017/05/03 15:45:15 - dwh_db_check_ - EXEC DWH_CFG_PRE_CONFIG_SP 'PPMDBLINK.niku.niku', 'Y'
2017/05/03 15:45:15 - dwh_db_check_ -
2017/05/03 15:45:15 - dwh_db_check_ - [CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot find the object "DWH_CMN_ERROR_MESSAGE" because it does not exist or you do not have permissions.
null

This should never be done 

I found the IS_DWH_CUSTOMER_DB.xml file but in another folder: "upgrade" instead of "manitenance"

Then I ran the statement admin db dwh-upgrade -Dupgrade.phase=upgrade and it worked

This is not the right way, if the PPM and DWH database is not in sync only maintenance command can make it sync.

Once you run the command from CSA it shows all ok but the underlying objects are not in sync. In these situation its best to import the blank DWH schema and run the maintenance command 

Hi RnFernandez

It seems like during upgrade from 15.1 to 15.2 it did not upgrade the DWH schema.

On execution of this command: admin db dwh-upgrade -Dupgrade.phase=maintenance,   it gets failed. As per the logs it seems that the IS_DWH_CUSTOMER_DB.xml file is unavailable at this path D:\CAPPM\CAPPM152\CAPPM152\maintenance\infrastructure\dwhschema\function\

1. Can you check whether this file exists or not ?
2. Can you provide the install.log (upgrade) file and the screenshot of Installation overview page from NSA ?

Regards
Shubham Bhatia

Thank you Shubham, for your information.

I found the IS_DWH_CUSTOMER_DB.xml file but in another folder: "upgrade" instead of "manitenance"

Then I ran the statement admin db dwh-upgrade -Dupgrade.phase=upgrade and it worked.

I will be reviewing your result and will inform you.

This is my view of nsa