RTC CRJAZ1745E 데이터웨어 하우스 테이블을 만드는 중 오류가 발생했습니다. 다시 시도하십시오 .ID

r18 - 2017-10-25 - 19:52:19 - RafikJaouaniYou are here: TWiki >  Main Web > ReportsMain > MoreControlOverTheOracleDataWarehouseSetup

NOTE: The development wiki is a work area used by the Jazz development teams to plan and discuss technical designs and operational procedures related to the development projects at Jazz.net. Often you will find work items linking to documents in the wiki. Everyone is welcome to browse the wiki, follow along, and participate in the development process. Participation is what Jazz.net is all about! But please keep in mind that information on the wiki is "as is", unsupported, and may be outdated or inaccurate. For information on released products, consult the product documentation, support tech notes, and the Jazz.net library. See also the Jazz.net Terms of Use.

More Control over the Oracle Data Warehouse Setup

Introduction

The steps described below are for informational purposes, for those users curious about the commands executed when the JTS setup wizard (or repotools) creates the data warehouse. We do not recommend that you manually execute the steps below. Scripts that perform these operations are also available in the server installation in the datawarehouse/ directory - we do not recommend that you manually modify or execute these scripts unless advised to do so by IBM support.

Note: The Scripts mentioned above are only available on 3.0.1.x, as of 4.0.x the datawarehouse directory is no longer available.

The Oracle Data Warehouse setup happens in multiple steps. All these steps are run part of the setup process happening during the JTS setup wizard or the manual invocation of:

repotools-jts -createWarehouse

What are you trying to do?

Please read this section before proceeding with the rest of the document. How you use the information below depends on what you are trying to achieve. Here is a rough flowchart:

1. If you simply want to create the data warehouse using repotools-jts -createWarehouse, or the server setup wizard, using all of the default settings, read no further.
2. If you want to create the Oracle Data Warehouse schemas using a Schema Prefix. Note: Available with 4.0.1 and above
3. If you want to create the warehouse using repotools-jts -createWarehouse, or the server setup wizard, but want to know more about what commands are executed as a result, and what happens to the database as a result, read the rest of this document for information, but do not execute any of the commands here manually.
4. If you need to have the tablespaces created without AUTOEXTEND, then read Step 1, and execute the five statements there manually (after modifying them to remove AUTOEXTEND and supplying appropriate paths for the tablespace files). Then, change "com.ibm.team.datawarehouse.db.automatic.setup" to "false" in your teamserver.properties, and use repotools-jts -createWarehouse or the server setup wizard. This is described in more detail below in the "Modifying the Tablespace creation step" section.
5. If you need the tablespaces to be created on ASM, instead of a local filesystem, then read Step 1, and execute the five statements there manually (after modifying them to appropriately specify the tablespace location for your environment). Then, change "com.ibm.team.datawarehouse.db.automatic.setup" to "false" in your teamserver.properties, and use repotools-jts -createWarehouse or the server setup wizard. This is described in more detail below in the "Modifying the Tablespace creation step" section.
6. If you simply want to create the data warehouse but are concerned about the elevated (DBA) privileges required to do so, skip to the "DBA role required for creating the Data Warehouse" section, which describes the steps to take after using repotools-jts -createWarehouse, or the server setup wizard.

Creating the Schemas with a Prefix (4.0.1+)

This part explains how to create the Data Warehouse Scemas with a Prefix for ease of management. These steps need to be done before the Data Warehouse creation, if you have already created the Data Warehouse you will need to drop it.

1. Stop the Server
2. Open the jts teamserver.properties and add the following line, where JT1 is the prefix of your choice. Save the file.
   • com.ibm.team.datawarehouse.db.schemaPrefix=JT1   
3. Now open the teamserver.properties for all your application CCM and QM and add this line. Save.
4. Start the server and run through jts setup to create the Data Warehouse.

In the Oracle Database you will see the following Schema's created for the Data Warehouse:

JT1_ASSET
JT1_CALM
JT1_CFG
JT1_DW
JT1_ODS
JT1_SCHK

The effect of schema prefix on the schema names

Note: The scripts used in this wiki assume no schema prefix is being used. If the datawarehouse was setup with a schema prefix then the schema names must be replaced with the proper names. If, .e.g., a schema prefix of JT1 is being used then the deafult schema names must be substituted as follows:

RIODS -> JT1_ODS
RIDW -> JT1_DW
CONFIG ->JT1_CFG
RIASSET -> JT1_ASSET
RISCHK -> JT1_SCHK
RICALM -> JT1_CALM

Step 1: Tablespace creation

This step will create the various tablespaces that will be used by the data warehouse. The location of the tablespace files is up to the user but the names cannot be changed.

CREATE TABLESPACE VNF_IDX LOGGING DATAFILE 'c:\jazzdb/VNF_IDX_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE VNF_32K LOGGING DATAFILE 'c:\jazzdb/VNF_32K_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE VSTR_IDX LOGGING DATAFILE 'c:\jazzdb/VSTR_IDX_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE VSTR_32K LOGGING DATAFILE 'c:\jazzdb/VSTR_32K_1.ora' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE RIS_TEMP TEMPFILE 'c:\jazzdb/RIS_TEMP01.DBF' SIZE 250M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

This step is implemented by the ddl/tbspace.sql scripts.

If a customer has certain restrictions on tablespaces, such as (1) not being able to use AUTOEXTEND, or (2) having to use a SAN instead of a local filesystem for tablespace storage, then please refer to the section "Modifying the Tablespace creation step" for details on how to manually perform this step and then proceed with the normal warehouse setup.

Step 2: User (schema) creation

This step is informational only. Do not manually execute any of the statements below unless instructed by IBM support.

This step will create the various locked users that will be used as owners of the various data warehouse tables and views. It will also grant SELECT privileges to the RIDW and RICALM users which own all the views that expose the data warehouse. This step will also grant connect privileges to the RPTUSER user. The RPTUSER user will be used by the Cognos report server to query data from the data warehouse.

Notice how the users are created using the USERS tablespace. For the setup to work on Oracle, the USERS tablespace must exist.

CREATE USER CONFIG  PROFILE DEFAULT IDENTIFIED BY nopassword PASSWORD EXPIRE DEFAULT TABLESPACE USERS ACCOUNT LOCK;
CREATE USER RIODS  PROFILE DEFAULT IDENTIFIED BY nopassword PASSWORD EXPIRE DEFAULT TABLESPACE USERS ACCOUNT LOCK;
CREATE USER RIDW  PROFILE DEFAULT IDENTIFIED BY nopassword PASSWORD EXPIRE DEFAULT TABLESPACE USERS ACCOUNT LOCK;

GRANT SELECT ANY TABLE TO RIDW WITH ADMIN OPTION;

CREATE USER RIASSET  PROFILE DEFAULT IDENTIFIED BY nopassword PASSWORD EXPIRE DEFAULT TABLESPACE USERS ACCOUNT LOCK;
CREATE USER RISCHK  PROFILE DEFAULT IDENTIFIED BY nopassword PASSWORD EXPIRE DEFAULT TABLESPACE USERS ACCOUNT LOCK;

ALTER USER CONFIG QUOTA UNLIMITED ON VNF_32K;
ALTER USER CONFIG QUOTA UNLIMITED ON VNF_IDX;
ALTER USER CONFIG QUOTA UNLIMITED ON USERS;
ALTER USER RIODS QUOTA UNLIMITED ON VNF_32K;
ALTER USER RIODS QUOTA UNLIMITED ON VNF_IDX;
ALTER USER RIODS QUOTA UNLIMITED ON USERS;
ALTER USER RIDW QUOTA UNLIMITED ON VSTR_32K;
ALTER USER RIDW QUOTA UNLIMITED ON VSTR_IDX;
ALTER USER RIDW QUOTA UNLIMITED ON USERS;
ALTER USER CONFIG TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER RIODS TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER RIDW TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER RPTUSER TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER RIASSET QUOTA UNLIMITED ON VNF_32K;
ALTER USER RIASSET QUOTA UNLIMITED ON VNF_IDX;
ALTER USER RIASSET QUOTA UNLIMITED ON USERS;
ALTER USER RIASSET TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER RISCHK QUOTA UNLIMITED ON VNF_32K;
ALTER USER RISCHK QUOTA UNLIMITED ON VNF_IDX;
ALTER USER RISCHK QUOTA UNLIMITED ON USERS;
ALTER USER RISCHK TEMPORARY TABLESPACE RIS_TEMP;

CREATE USER RICALM  PROFILE DEFAULT IDENTIFIED BY nopassword PASSWORD EXPIRE DEFAULT TABLESPACE USERS ACCOUNT LOCK;

GRANT SELECT ANY TABLE TO RICALM WITH ADMIN OPTION;

ALTER USER RICALM QUOTA UNLIMITED ON VNF_32K;
ALTER USER RICALM QUOTA UNLIMITED ON VNF_IDX;
ALTER USER RICALM QUOTA UNLIMITED ON USERS;
ALTER USER RICALM TEMPORARY TABLESPACE RIS_TEMP;

GRANT CONNECT TO RPTUSER;

This step is implemented by the following scripts:

• ddl/createUsers.sql
• ddl/createCALMUsers.sql

Step 3: Table and View creation

This step is informational only. Do not manually execute any of the statements below unless instructed by IBM support.

This step will create all the tables, views, indices, triggers and constraints needed by the data warehouse.

Here is sample SQL from this step:

CREATE TABLE ACTIVITY_CLASSIFICATION (
   ACTIVITY_CLASS_ID INTEGER NOT NULL,
   NAME VARCHAR(50) DEFAULT 'Activity Class Name Missing' NOT NULL,
   REC_DATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
) TABLESPACE VNF_32K;

CREATE INDEX UNT_TSTRES_IDX7 ON UNIT_TEST_RESULT (ISSOFTDELETED) REVERSE TABLESPACE VNF_IDX;

ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID) USING INDEX TABLESPACE VNF_IDX;

INSERT INTO TESTCASE_CLASSIFICATION (TESTCASE_CLASS_ID,NAME) VALUES (-1,'Info Not Available');

This step is implemented by the following scripts:

• ddl/createSTGCFG.ddl
• ddl/createNF.ddl
• ddl/createRIASSET.ddl
• ddl/createRISCHK.ddl
• ddl/createSTAR.ddl
• ddl/createSTARViews.ddl
• scripts/initCONFIG.sql
• scripts/initNF.sql
• scripts/initRIASSET.sql
• scripts/initRISCHK.sql
• scripts/initSTAR.sql
• ddl/createCALM.ddl
• ddl/createCALMViews.ddl
• scripts/initCALM.sql

Step 4: Grants

This step is informational only. Do not manually execute any of the statements below unless instructed by IBM support.

The Custom Report Database User (RPTUSER) is the database user used by RRDI and Rational Insight to read data from the Data Warehouse.

This user must already exist in the database. None of the he Data Warehouse setup scripts will create the user.

In this step, the RPTUSER user will be granted select privileges to the data warehouse views.

Here is sample SQL from this step:

GRANT SELECT ON RIDW..D_ASSET TO RPTUSER;                        

This step is implemented by the following scripts:

• scripts/star_grants.sql
• scripts/calm_grants.sql

Dropping the Data Warehouse

If any of the above steps fail for a reason, the following script can be used to drop the partially created artifacts.

DROP USER CONFIG CASCADE;
DROP USER RIODS CASCADE;
DROP USER RIDW CASCADE;
DROP USER RIASSET CASCADE;
DROP USER RISCHK CASCADE;
DROP USER RICALM CASCADE;

DROP TABLESPACE VNF_IDX INCLUDING CONTENTS;
DROP TABLESPACE VNF_32K INCLUDING CONTENTS;
DROP TABLESPACE VSTR_IDX INCLUDING CONTENTS;
DROP TABLESPACE VSTR_32K INCLUDING CONTENTS;
DROP TABLESPACE RIS_TEMP INCLUDING CONTENTS;

The tablespace files will also need to be deleted in the database server:

cd <tablespace folder>
del VNF_32K*.ORA
del VNF_IDX*.ORA
del VSTR_32K*.ORA
del VSTR_IDX*.ORA

Modifying the Tablespace creation step

You can customize the tablespace creation, as described in step 1, and still use repotools -createWarehouse (or the server setup wizard) to create the rest of the warehouse. To customize the tablespace creation:

1. Manually execute the tablespace creation steps from step 1, with any modifications that you need to make, such as removing the AUTOEXTEND flag, or modifying the location parameters to use a SAN instead of the local filesystem.
2. In 4.0 and beyond, you will also need to manually run step 2.
3. Set the following property to "false" in the teamserver.properties file of the JTS server prior to running the data warehouse setup:

com.ibm.team.datawarehouse.db.automatic.setup = false

Then, run repotools -createWarehouse or the server setup wizard to create the warehouse.

The "automatic.setup" flag controls whether or not the tablespaces are created by the setup process ("true") or are assumed to be already created ("false"). Please note that using this flag also renders this property, com.ibm.team.datawarehouse.db.base.folder, unnecessary. When using the server setup wizard, this property translates to the field Database Table Space Folder. Thus, when using the server setup wizard and setting the flag above, you do not have to enter a value for this field.

DBA role required for creating the Data Warehouse

The creation of the data warehouse on Oracle requires elevated permissions as compared to other databases. When you specify the database user in the connection spec for data warehouse, ensure that the database user has DBA permissions. If the elevated permission is a concern, after the data warehouse is created, you may change the user in the connection spec, using the Reports Admin Web UI. You need to change this value in the JTS, CCM, and QM applications.

To create the new user with diminished permissions for running the ETL jobs, open a SQL*Plus window and run the following commands:

CREATE USER <etl db user> PROFILE DEFAULT 
IDENTIFIED BY <password> DEFAULT TABLESPACE USERS
ACCOUNT UNLOCK;

ALTER USER <etl db user> TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER <etl db user> QUOTA UNLIMITED ON VNF_32K;
ALTER USER <etl db user> QUOTA UNLIMITED ON VNF_IDX;
ALTER USER <etl db user> QUOTA UNLIMITED ON USERS;
ALTER USER <etl db user> QUOTA UNLIMITED ON VSTR_32K;
ALTER USER <etl db user> QUOTA UNLIMITED ON VSTR_IDX;

GRANT CREATE SESSION TO <etl db user>;
GRANT CONNECT TO <etl db user>;
GRANT ANALYZE ANY TO <etl db user>;
GRANT DROP ANY TABLE TO <etl db user>;

BEGIN
FOR TABNAME IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER IN ('RIODS','RIDW','CONFIG','RIASSET','RISCHK','RICALM'))
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,UPDATE,DELETE,INSERT ON ' || TABNAME.OWNER || '."' || TABNAME.TABLE_NAME || '" TO <etl db user>';
END LOOP;
END;
/

BEGIN
FOR VNAME IN (SELECT OWNER,VIEW_NAME FROM ALL_VIEWS WHERE OWNER IN ('RIDW','RICALM')) 
LOOP 
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || VNAME.OWNER || '."' || VNAME.VIEW_NAME || '" TO <etl db user>';
END LOOP;
END;
/

Scripts in the Server Data Warehouse folder

Note: The Scripts mentioned above are only available on 3.0.1.x, as of 4.0.x the datawarehouse directory is no longer available. 

Scripts in the server data warehouse folder can be used to create the data warehouse but these will do an A to Z creation of the database, including changing the database configuration parameters. So the usage of these scripts is not recommended and is there just for historical/documentation reasons to give the users an idea about what the automated data warehouse creation process does.

-- RafikJaouani - 12 Jul 2011