Updating the LQE rs Data Warehouse for Engineering Lifecycle Management 7.1 interim fix 009 or later

Prerequisites

Before you run the DDL scripts, ensure that the following conditions are met:

• All ELM applications are shut down and there are no active database connections.

• The data warehouse is backed up.

• You have DBA privileges to run DDL statements.

• No Data Warehouse jobs or report queries are running.

• LQE rs and related services are stopped.

When this update applies

This DDL update is required only if:

• You are upgrading to ELM 7.1 interim fix 009 or later

• Your current version is earlier than interim fix 009

You do not need to run the DDL scripts if your environment is already on interim fix 009 or later.

Steps

1. Connect to the Data warehouse database.

2. Run the appropriate DDL script for your vendor.

   Db2 Database : 

   -- =========================================
   -- UPGRADE SCRIPT
   -- =========================================
   
   SET CURRENT SCHEMA = RITRS;
   
   COMMIT;
   
   ALTER TABLE CACHED_CONFIG ADD COLUMN LAST_USED TIMESTAMP;
   
   COMMIT;
   
   CREATE INDEX CACHED_CONFIG_IDX3 ON CACHED_CONFIG (LAST_USED) ALLOW REVERSE SCANS;
   
   COMMIT;
   
   ALTER TABLE CACHED_CONFIG ADD COLUMN EXPIRED SMALLINT;
   
   COMMIT;
   
   CREATE TABLE CACHED_CONFIG_ALL_SELECTS (ITEM_ID BIGINT NOT NULL, PROPERTY_ID BIGINT NOT NULL, VAL_ID BIGINT NOT NULL) IN VNF_32K INDEX IN VNF_IDX;
   
   ALTER TABLE CACHED_CONFIG_ALL_SELECTS ADD CONSTRAINT CACHED_CONFIG_ALL_SELECTS_FK1 FOREIGN KEY (ITEM_ID) REFERENCES CACHED_CONFIG (ID) ON DELETE CASCADE;
   
   CREATE INDEX CACHED_CONFIG_ALL_SELECTS_IDX1 ON CACHED_CONFIG_ALL_SELECTS (ITEM_ID ASC) ALLOW REVERSE SCANS;
   
   CREATE INDEX CACHED_CONFIG_ALL_SELECTS_IDX2 ON CACHED_CONFIG_ALL_SELECTS (PROPERTY_ID ASC) ALLOW REVERSE SCANS;
   
   CREATE INDEX CACHED_CONFIG_ALL_SELECTS_IDX3 ON CACHED_CONFIG_ALL_SELECTS (VAL_ID ASC, PROPERTY_ID ASC, ITEM_ID ASC) ALLOW REVERSE SCANS;
   
   COMMIT;
   
   ALTER TABLE F_WORK_ITEM_TOTALS ADD COLUMN TOTAL_STORY_POINTS BIGINT;
   
   COMMIT;
   
   ALTER TABLE F_WORK_ITEM_CREATION ADD COLUMN TOTAL_STORY_POINTS BIGINT;
   
   COMMIT;
   
   ALTER TABLE F_WORK_ITEM_CLOSURE ADD COLUMN TOTAL_STORY_POINTS BIGINT;
   
   COMMIT;
   
   ALTER TABLE F_TEST_RESULT_TOTALS ADD COLUMN TEST_CASE_STATE_ID BIGINT;
   
   COMMIT;
   
   CREATE INDEX F_TEST_RESULT_TOTALS_IDX6 ON F_TEST_RESULT_TOTALS (TEST_CASE_STATE_ID) ALLOW REVERSE SCANS;
   
   COMMIT;
   
   CREATE TABLE F_ARCHITECTURE_ELEMENT_TOTALS (REC_DATETIME TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL, DATE_ID BIGINT NOT NULL, CONFIGURATION_ID BIGINT, PROJECT_AREA_ID BIGINT, ARCHITECTURE_TYPE VARCHAR(400), COMPONENT_ID BIGINT, TOTAL BIGINT) IN VSTR_32K INDEX IN VSTR_IDX;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX1 ON F_ARCHITECTURE_ELEMENT_TOTALS (CONFIGURATION_ID ASC) ALLOW REVERSE SCANS;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX2 ON F_ARCHITECTURE_ELEMENT_TOTALS (PROJECT_AREA_ID ASC) ALLOW REVERSE SCANS;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX3 ON F_ARCHITECTURE_ELEMENT_TOTALS (ARCHITECTURE_TYPE ASC) ALLOW REVERSE SCANS;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX4 ON F_ARCHITECTURE_ELEMENT_TOTALS (COMPONENT_ID ASC) ALLOW REVERSE SCANS;
   
   COMMIT;
   
   SET CURRENT SCHEMA CONFIG;
   
   UPDATE SYSCONFIG_INFO SET INT_VALUE=75, CHAR_VALUE='75.0' WHERE INFO_ID=17;
   
   COMMIT;
   
   SET CURRENT SCHEMA CONFIG;
   
   UPDATE SYSCONFIG_INFO SET INT_VALUE=76, CHAR_VALUE='76.0' WHERE INFO_ID=17;
   
   CREATE USER TEMPORARY TABLESPACE USERTEMPSPACE32K
       IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
       PAGESIZE 32768
       MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BPTEMP32K;
   
   COMMIT;
   

   
   Oracle Database  :

   -- =========================================
   -- UPGRADE SCRIPT
   -- =========================================
   
   
   
   ALTER SESSION SET CURRENT_SCHEMA = RITRS;
   
   COMMIT;
   
   ALTER TABLE CACHED_CONFIG ADD LAST_USED TIMESTAMP;
   
   COMMIT;
   
   CREATE INDEX CACHED_CONFIG_IDX3 ON CACHED_CONFIG (LAST_USED) REVERSE TABLESPACE VNF_IDX;
   
   COMMIT;
   
   ALTER TABLE CACHED_CONFIG ADD EXPIRED SMALLINT;
   
   COMMIT;
   
   CREATE TABLE CACHED_CONFIG_ALL_SELECTS (ITEM_ID NUMBER(20) NOT NULL, PROPERTY_ID NUMBER(20) NOT NULL, VAL_ID NUMBER(20) NOT NULL) TABLESPACE VNF_32K;
   
   ALTER TABLE CACHED_CONFIG_ALL_SELECTS ADD CONSTRAINT CACHED_CONFIG_ALL_SELECTS_FK1 FOREIGN KEY (ITEM_ID) REFERENCES CACHED_CONFIG (ID) ON DELETE CASCADE;
   
   CREATE INDEX CACHED_CONFIG_ALL_SELECTS_IDX1 ON CACHED_CONFIG_ALL_SELECTS (ITEM_ID ASC) REVERSE TABLESPACE VNF_IDX;
   
   CREATE INDEX CACHED_CONFIG_ALL_SELECTS_IDX2 ON CACHED_CONFIG_ALL_SELECTS (PROPERTY_ID ASC) REVERSE TABLESPACE VNF_IDX;
   
   CREATE INDEX CACHED_CONFIG_ALL_SELECTS_IDX3 ON CACHED_CONFIG_ALL_SELECTS (VAL_ID ASC, PROPERTY_ID ASC, ITEM_ID ASC) REVERSE TABLESPACE VNF_IDX;
   
   COMMIT;
   
   ALTER TABLE F_WORK_ITEM_TOTALS ADD TOTAL_STORY_POINTS NUMBER(20);
   
   COMMIT;
   
   ALTER TABLE F_WORK_ITEM_CREATION ADD TOTAL_STORY_POINTS NUMBER(20);
   
   COMMIT;
   
   ALTER TABLE F_WORK_ITEM_CLOSURE ADD TOTAL_STORY_POINTS NUMBER(20);
   
   COMMIT;
   
   ALTER TABLE F_TEST_RESULT_TOTALS ADD TEST_CASE_STATE_ID NUMBER(20);
   
   COMMIT;
   
   CREATE INDEX F_TEST_RESULT_TOTALS_IDX6 ON F_TEST_RESULT_TOTALS (TEST_CASE_STATE_ID) REVERSE TABLESPACE VSTR_IDX;
   
   COMMIT;
   
   CREATE TABLE F_ARCHITECTURE_ELEMENT_TOTALS (REC_DATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, DATE_ID NUMBER(20) NOT NULL, CONFIGURATION_ID NUMBER(20), PROJECT_AREA_ID NUMBER(20), ARCHITECTURE_TYPE VARCHAR(400), COMPONENT_ID NUMBER(20), TOTAL NUMBER(20)) TABLESPACE VSTR_32K;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX1 ON F_ARCHITECTURE_ELEMENT_TOTALS (CONFIGURATION_ID ASC) REVERSE TABLESPACE VSTR_IDX;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX2 ON F_ARCHITECTURE_ELEMENT_TOTALS (PROJECT_AREA_ID ASC) REVERSE TABLESPACE VSTR_IDX;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX3 ON F_ARCHITECTURE_ELEMENT_TOTALS (ARCHITECTURE_TYPE ASC) REVERSE TABLESPACE VSTR_IDX;
   
   CREATE INDEX F_ARCHITECTURE_ELEMENT_TOTALS_IDX4 ON F_ARCHITECTURE_ELEMENT_TOTALS (COMPONENT_ID ASC) REVERSE TABLESPACE VSTR_IDX;
   
   COMMIT;
   
   ALTER SESSION SET CURRENT_SCHEMA = CONFIG;
   
   UPDATE SYSCONFIG_INFO SET INT_VALUE=75, CHAR_VALUE='75.0' WHERE INFO_ID=17;
   
   COMMIT;
   
   ALTER SESSION SET CURRENT_SCHEMA = CONFIG;
   
   UPDATE SYSCONFIG_INFO SET INT_VALUE=76, CHAR_VALUE='76.0' WHERE INFO_ID=17;
   
   COMMIT;
   
   -- Upgrade complete
   SELECT 'Upgrade completed successfully' AS STATUS FROM DUAL;  
   

    

Important: If you do not run the DDL scripts during the upgrade:

• A warning message appears on the LQE Data Provider page.

  Image displaying the LQE Data Provider page:
  
  

• LQE indexing stops and does not progress.
• This warning message appears in lqe.log: [ lqe.PgsIndexer-thread-0] WARN ion.lqe.lib.dataset.pgs.sql5.SqlGraphStoreProvider [TID: 3ED56D8D] - The data warehouse version 74 is not compatible with LQE which requires version 76. The SQL graph store provider cannot initialize but will keep retrying.