CLM 4.0.4 - Tables missing in Data Warehouse after using manual DDL scripts to setup

Sean, please try repotools -addTables on both the JTs and CCM. Also please check that all the server services are activated and no errors are listed in the server diagnostics page. To check that all services are activated for e.g., on the JTS, try:

https://server:port/jts/admin?internal=true#action=com.ibm.team.repository.admin.componentStatus

for CCM:

https://server:port/ccm/admin?internal=true#action=com.ibm.team.repository.admin.componentStatus

Hi Wilbur,

I follow below procedure... hope it helps you. (I have multiple DWs on same DB so, I use schema prefix.)

1. Create user by name RPTUSER_PRD10 on the DB. - With default privileges.
CREATE USER RPTUSER_PRD10 PROFILE DEFAULT
IDENTIFIED BY manager10 DEFAULT TABLESPACE VNF_32K   - We changed default table space (USERS) to VNF_32K
ACCOUNT UNLOCK;
ALTER user RPTUSER_PRD10 profile APP_PROFILE_NO_EXP ;

2. Create the user DWETLUSER_DEV with less permission's as below for running data collection (ETL) jobs.
CREATE USER DWETLUSER_PRD10 PROFILE DEFAULT
IDENTIFIED BY manager10 DEFAULT TABLESPACE VNF_32K   - We changed default table space (USERS) to VNF_32K
ACCOUNT UNLOCK;
ALTER user DWETLUSER_PRD10 profile APP_PROFILE_NO_EXP ;

GRANT DBA TO DWETLUSER_PRD10;

GRANT CREATE SESSION TO DWETLUSER_PRD10;
GRANT CONNECT TO DWETLUSER_PRD10;
GRANT EXECUTE ANY PROCEDURE TO DWETLUSER_PRD10;
GRANT ANALYZE ANY TO DWETLUSER_PRD10;
GRANT DROP ANY TABLE TO DWETLUSER_PRD10;   - THIS PRIVILEGE IS NOT REQUIRED.

NOTE: DBA PRIVILEGE WILL BE GRANTED UNTIL CONFIGURATION IS COMPLETED. SINCE WE HAVE RESTRICTIONS ON SCHEMA USER HAVING DBA PREVILEGE.

3. Start jts/setup, configure the DataWarehouse with above details. (Select the Checkbox the "Do not configure DW at this time" option)
4. Create contentstore tablespace & user

CREATE 
    TABLESPACE CTBS_4K
    LOGGING
    DATAFILE
    '/u04/oradata/RTC01/datafile/CTBS_4K_1.ora'
    SIZE 200M AUTOEXTEND
    ON NEXT  50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT  AUTO;
COMMIT;

CREATE USER RICMUSER_PRD10  PROFILE DEFAULT
    IDENTIFIED BY manager10 DEFAULT TABLESPACE CTBS_4K
    ACCOUNT UNLOCK;
GRANT DBA TO RICMUSER_PRD10;
COMMIT;
 ALTER user RICMUSER_PRD10 profile APP_PROFILE_NO_EXP ;
ALTER user  RICMUSER_PRD10 grant quota unlimited on CTBS_4K ;

GRANT CREATE SESSION TO RICMUSER_PRD10;
GRANT CONNECT TO RICMUSER_PRD10;
GRANT EXECUTE ANY PROCEDURE TO RICMUSER_PRD10;
GRANT ANALYZE ANY TO RICMUSER_PRD10;
GRANT DROP ANY TABLE TO RICMUSER_PRD10;   - THIS PRIVILEGE IS NOT REQUIRED AS IT VIOLATES SECURITY.

NOTE: PROVIDE DBA PRIVILEGE TO DWETLUSER_PRD10 & RICMUSER_PRD10 UNTIL RRDI CONFIGURATION IS COMPLETED.

Ensure that the user RICMUSER_PRD10 account that accesses the database has permission to do the following:
connect to the database
create, alter, and drop tables, triggers, views, procedures, and sequences owned by the user.
insert, update, and delete data in the database tables owned by the user.
Share the Username / Password with us.

5. Stop RTC instance & generate DDLs [ ./repotools-jts.sh -generateWarehouseDDLScripts ]
1-setupCoreSpace.sql
2-createCoreSchema.sql
3-grantCoreSchemaReadAccess.sql
4-populateDateDimension.sql
5-setupCalmSpace.sql
6-createCalmSchema.sql
7-grantCalmSchemaReadAccess.sql

6. Once above SQLs are executed, execute below statements.

ALTER USER DWETLUSER_PRD10 TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER DWETLUSER_PRD10 QUOTA UNLIMITED ON RIS_TEMP;
ALTER USER DWETLUSER_PRD10 QUOTA UNLIMITED ON VNF_32K;
ALTER USER DWETLUSER_PRD10 QUOTA UNLIMITED ON VNF_IDX;
ALTER USER DWETLUSER_PRD10 QUOTA UNLIMITED ON VSTR_32K;
ALTER USER DWETLUSER_PRD10 QUOTA UNLIMITED ON VSTR_IDX;
ALTER USER DWETLUSER_PRD10 QUOTA UNLIMITED ON USERS;   - THIS IS NOT REQUIRED FOR US.

BEGIN
FOR TABNAME IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER IN ('PRD10_ODS','PRD10_DW','PRD10_CFG','PRD10_ASSET','PRD10_SCHK','PRD10_CALM'))
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,UPDATE,DELETE,INSERT ON ' || TABNAME.OWNER || '."' || TABNAME.TABLE_NAME || '" TO DWETLUSER_PRD10';
END LOOP;
END;
/

BEGIN
FOR VNAME IN (SELECT OWNER,VIEW_NAME FROM ALL_VIEWS WHERE OWNER IN ('PRD10_DW','PRD10_CALM'))
LOOP   
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || VNAME.OWNER || '."' || VNAME.VIEW_NAME || '" TO DWETLUSER_PRD10';
END LOOP;
END;
/

7. Go through jts setup & configure above DW details. (uncheck the the "do not configure DW at this time" option)
8. Launch RRDI setup & configure
9. Run the ETL jobs & verify the reports.
10. Be happy.!!

Regards,

Nagesh S

This is really strange. The tables it is complaining about are from the legacy datamart which should have been created part of the repository database. They are not data warehouse tables. Is it too late to try to re-create the repo databases.