IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Installation Guides > Installation Guide > Tivoli Data Warehouse solution using DB2 for Linux, UNIX, and Windows > Step 1: Create the Tivoli Data Warehouse database

IBM Tivoli Monitoring, Version 6.3 Fix Pack 2

Limit the authority of the warehouse user

If you do not want the warehouse user to have broad administrative authority, you can limit the authority of the warehouse user to just those privileges required for accessing and using the data warehouse. These more limited privileges include the authority to create and update tables, to insert or delete information from the tables, to create indexes for the tables, and to grant public authority to the tables.


The Tivoli Data Warehouse requires one bufferpool and three tablespaces to begin its operation. The bufferpool and tablespaces are created by the warehouse user before the Warehouse Proxy Agent starts, provided the warehouse user has administrative authority to the database. A warehouse user with limited authority cannot create the required bufferpool and tablespaces. Therefore, this procedure to limit the authority of the warehouse user includes steps to create the bufferpool and tablespaces in advance. Be sure to perform this procedure before the Warehouse Proxy Agent starts.

Use the script shown in the next table to create the required bufferpool and tablespaces. Create the script on a computer from which you can connect to the Tivoli Data Warehouse database server. 

    -- CREATE a Bufferpool of page size 8K
CREATE BUFFERPOOL ITMBUF8K IMMEDIATE  SIZE 2501 PAGESIZE 8K;

-- CREATE a Regular Tablespace using the 8K Bufferpool
CREATE REGULAR TABLESPACE ITMREG8K PAGESIZE 8K 
    MANAGED BY SYSTEM 
    USING ('itmreg8k')2 BUFFERPOOL ITMBUF8k;

-- CREATE a System tablespace using the 8K Bufferpool
CREATE SYSTEM TEMPORARY TABLESPACE ITMSYS8K PAGESIZE 8K
    MANAGED BY SYSTEM 
    USING ('itmsys8k')2 BUFFERPOOL ITMBUF8k;

-- CREATE a User tablespace using the 8K Bufferpool
   
CREATE USER TEMPORARY  TABLESPACE ITMUSER8K PAGESIZE 8K
    MANAGED BY SYSTEM 
    USING ('itmuser8k')2 BUFFERPOOL ITMBUF8k;

  1. SIZE is the number of 8K pages to allocate for the bufferpool. If there is sufficient memory, performance can be improved by increasing this number.

  2. A fully qualified path can be specified here. As shown this will be created in a default directory.

  3. IBM Tivoli Monitoring creates SMS tablespaces, which cannot be extended across multiple drives. This can be customized by the database administrator.

To limit the authority of the warehouse user, complete the following steps:

  1. Connect to the data warehouse with db2admin privileges:

      db2 connect to warehouse user db2admin using password

    ...where warehouse is the name of the Warehouse database, db2admin is the DB2 for Linux, UNIX and Windows administrator ID, and password is the password of the db2admin user ID. The user ID must be a DB2 user with SYSADM authority

  2. Change to the directory where the KHD_DB2_crt_BP_TBSP.sql script is located.

  3. Run the script to create the required bufferpool and tablespaces:

      db2 -stvf KHD_DB2_crt_BP_TBSP.sql

  4. Remove administrative privileges from the warehouse user (OS user) that you created when you created the warehouse database:

    • Remove the warehouse user from the Administrator group.

    • Remove the warehouse user from the SYSADM group to which it was assigned (for example, DB2GRP1).

  5. Grant these database authorities to the warehouse user:

    • CONNECT

    • CREATETAB

      The user might already implicitly have CONNECT and CREATEAB authority through the PUBLIC role.

    • USE OF TABLESPACE

    CONNECT authority grants the user access to the database. CREATETAB authority allows the user to create tables. The authority to drop tables, alter tables, create and drop indexes for the tables, insert, delete, or update data in the tables, are all implicitly granted. USE OF TABLESPACE grants the user authority to use particular tablespaces, in this case ITMREG8K.

    To grant these authorities, you can use either the DB2 Control Center (Database Authorities window) or the command-line interface. If you use the command-line interface, run commands similar to the following. In this example, the name of the warehouse user is itmuser.

      db2 "GRANT CONNECT ON DATABASE TO USER itmuser"
      db2 "GRANT CREATETAB ON DATABASE TO USER itmuser"
      db2 "GRANT USE OF TABLESPACE ITMREG8K TO USER itmuser"


What to do next

Perform the additional steps if your security policy prohibits use of the CREATTAB authority:

  1. Use the schema tool to generate the DDL that create the database objects.

    You should create the historical collections that you want first and then configure the Summarization and Pruning Agent so that you can use the schema tool's configured mode.

  2. Execute the generated scripts as described in Schema Publication Tool. Run the scripts as the Tivoli Data Warehouse user so that the Tivoli Data Warehouse user has sufficient privileges on the tables.

  3. Revoke the CREATETAB privilege from the Tivoli Data Warehouse user and from the PUBLIC role.


Parent topic:

Step 1: Create the Tivoli Data Warehouse database

+

Search Tips   |   Advanced Search