It's all about the answers!

Ask a question

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


Sean G Wilbur (87212421) | asked Nov 18 '13, 11:37 p.m.
JAZZ DEVELOPER
edited Nov 18 '13, 11:38 p.m.
 
Setting up a new 4.0.4 instance on SQL Server 2008 and followed the guide at
to generate the DDLs, setup was happy and I was able to proceed without errors. (Only setting up JTS and CCM here)

1-setupCoreSpace.sql
2-createCoreSchema.sql ( too big for one pastebin: part1 , part2 )
3-grantCoreSchemaReadAccess.sql (not used only one user here)
4-populateDateDimension.sql (7 MB of dimensional data)
5-setupCalmSpace.sql (empty file)
6-createCalmSchema.sql
7-grantCalmSchemaReadAccess.sql (not used only one user here)
	
	


Now when I trigger the ETLs, I get errors in both JTS and CCM.
JTS
 Repository -  invalid object REPOSITORY_SNAPSHOT.INTEGRAL_RANGE

CCM
 * Build -invalid object BUILD_SNAPSHOT.BUILD_RESULTS
 * SCM - HTTP 500
 * APT - invalid object APT_SNAPSHOT.TEAM_CAPACITY
 * Repository - invalid object REPOSITORY_SNAPSHOT.INTEGRAL_RANGE

 Reviewing the DDLs generated, I don't see any mention of these tables or views so not sure where they were supposed to get created ? Let me know if this is expected and I missed a step somewhere or if this is a defect ?

   -Sean

Accepted answer


permanent link
Rafik Jaouani (5.0k16) | answered Nov 20 '13, 8:30 a.m.
JAZZ DEVELOPER

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

Sean G Wilbur selected this answer as the correct answer

Comments
Sean G Wilbur commented Nov 20 '13, 7:44 p.m.
JAZZ DEVELOPER

  Both componentStatus pages were showing all green before, stopped the server and run the addTables and restarted, Success! Now the ETLs work!


  I have not tried to reproduce this but shouldn't all the tables have been created by the original call to createTables as this is a vew instance not an upgrade ? Either way this may be something to follow up on in the future.

 Thank you for your help!


Rafik Jaouani commented Nov 21 '13, 10:27 a.m.
JAZZ DEVELOPER

Sean, internally the server is composed of many plugins. The schema is  contributed by multiple plugins. Each plugin contributes its own tables. My guess during the initial addTables run, the plugins contributing the datamart schema did not activate correctly.

2 other answers



permanent link
Rafik Jaouani (5.0k16) | answered Nov 19 '13, 8:40 a.m.
JAZZ DEVELOPER

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.


Comments
Sean G Wilbur commented Nov 19 '13, 6:09 p.m.
JAZZ DEVELOPER

 This is in production and I used the repotools commands to create the dbs, trying to get the createTables logs now, would you expect there are errors in there ?


Sean G Wilbur commented Nov 19 '13, 8:45 p.m.
JAZZ DEVELOPER

 Error in the logs, I corrected this before running through JTS setup but the property was not set during the JTS/CCM repotools createTables commands?

2013-11-19 14:04:03,248 CRJAZ1093E The "com.ibm.team.datawarehouse.service.internal.RemoteDatabaseService" feature could not start.
com.ibm.team.repository.common.transport.TeamServiceRegistryException: CRJAZ1126I The service "com.ibm.team.datawarehouse.service.internal.RemoteDatabaseService" has some invalid configuration properties.
    CRJAZ1125I The configuration property value "null" is not valid for the property "db.base.folder". The DB base folder must be set.

Could this cause the DataMart tables not get created ? And will running the DataMart collection or a repotool addTables pick up the missing tables and create them?

-Sean


permanent link
Nagesh Srinivas (10828) | answered Nov 19 '13, 4:13 a.m.

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


Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.