Procedures

The warehouse can be hosted on any of three databases: DB2®, Oracle, or Microsoft SQL Server. There are different sets of script files for each type of database. These scripts are provided as part of the monitoring agent Tivoli Enterprise Portal Server support file installation. After installing the Tivoli Enterprise Portal Server support files for the monitoring agent, the files are located on the Tivoli Enterprise Portal Server computer in install_dir/CNPS/SQLLIB/WAREHOUSE. There is a subdirectory for each type of database: DB2 for DB2, Oracle for Oracle, and SQLServer for Microsoft SQL Server.

The scripts provide commands for all affected tables and views. If you do not have summarization enabled for some periods, for example, quarterly or yearly, you will not have the corresponding summary tables (_Q, _Y) and summary views (_QV, _YV) in your warehouse database. If you run the scripts that are provided, the database reports errors for these missing objects. The scripts continue to run the remaining commands. Similarly, if you rerun the scripts, all commands are attempted. If the objects do not exist, or the command cannot be run (especially for the ALTER commands), the scripts continue processing the remaining commands.

DB2 warehouse database procedure

  1. Stop all running Warehouse Proxy agent instances and the Summarization and Pruning agent.
  2. Back up your warehouse database.
  3. Copy the scripts from the Tivoli Enterprise Portal Server in one of the following directories to a temporary directory on the system where the warehouse database is located:
    • Windows: 
      install dir\CNPS\SQLLIB\WAREHOUSE\DB2
    • UNIX and Linux: 
      install dir/arch/cq/sqllib/WAREHOUSE/DB2
  4. On the system where the warehouse database is located, change to the directory where you placed the script files in Step 3. Then, connect to the warehouse database through the DB2 command line with a user ID that has the authorization to load and alter tables and drop views. Run commands based on the following example to connect, set the schema, and save the script to an output file: 
    db2 connect to WAREHOUS user ITMUSER using ITMPASS
db2 set current schema="ITMUSER"
db2 -tv -z log/script.sql.log -f script.sql
    These parameters are used in the example:
    • WAREHOUS is the database name.
    • ITMUSER is the user name used by the Warehouse Proxy agent.
    • ITMPASS is the password used by the Warehouse Proxy agent.
    • script.sql is the name of the script file. See Table 20 for the script file names.
    • script.sql.log is the name of the output file.

    Notes:
    You might receive error messages such the following from DB2:
    • SQL0204N "schema name.table name" is an undefined name. SQLSTATE=42704

      This message indicates that the table named table name does not exist and cannot be altered or dropped. This happens if you do not have warehousing or summarization enabled for the given table. For example if you only have hourly and daily summarization enabled, you see this message for the weekly, monthly, quarterly, and yearly summarization tables because these tables do not exist.

    • SQL3304N The table does not exist.

      This message indicates that the table does not exist and cannot be loaded. This happens if you do not have warehousing or summarization enabled for the given table. For example if you only have hourly and daily summarization enabled, you see this message for the weekly, monthly, quarterly, and yearly summarization tables because these tables do not exist.

Oracle warehouse database procedure

  1. Stop all running Warehouse Proxy agent instances and the Summarization and Pruning agent.
  2. Back up your warehouse database.
  3. Copy the scripts from The Tivoli Enterprise Portal Server in one of the following directories to a temporary directory on the system where the warehouse database is located:
    • Windows 
      install dir\CNPS\SQLLIB\WAREHOUSE\Oracle
    • UNIX and Linux 
      install dir/arch/cq/sqllib/WAREHOUSE/Oracle
  4. On the system where the warehouse database is located, change to the directory where you placed the script files in Step 3. Then, connect to the warehouse database through the Oracle command line with the same user that the Warehouse Proxy agent uses to connect to the warehouse, and run the script. To run the script, the user ID must have authorization to alter tables and drop views, or to drop tables when using Case 2 Drop, or truncate tables when using Case 2 Truncate. The output is saved to a file named script name.log. Run the following command: 
    sqlplus ITMUSER/ITMPASS@WAREHOUS @script.sql
    These parameters are used in the example:
    • WAREHOUS is the connect identifier.
    • ITMUSER is the user name used by the Warehouse Proxy agent.
    • ITMPASS is the password used by the Warehouse Proxy agent.
    • script.sql is the name of this script file. See Table 20 for the script file names.

    Note:
    You might receive error messages such as the following from Oracle: ORA-00942: table or view does not exist

    This message indicates that the table does not exist and cannot be altered, dropped, or truncated. This happens if you do not have warehousing or summarization enabled for the given table. For example if you only have hourly and daily summarization enabled, you see this message for the weekly, monthly, quarterly, and yearly summarization tables because these tables do not exist.

MS SQL warehouse database procedure

  1. Stop all running Warehouse Proxy agent instances and the Summarization and Pruning agent.
  2. Back up your warehouse database.
  3. Copy the scripts from the Tivoli Enterprise Portal Server in the one of the following directories to a temporary directory on the system where the warehouse database is located:
    • Windows: 
      install dir\CNPS\SQLLIB\WAREHOUSE\SQLServer
    • UNIX and Linux: 
      install dir/arch/cq/sqllib/WAREHOUSE/SQLServer
  4. On the system where the warehouse database is located, change to the directory where you placed the script files in Step 3. Then, connect to the warehouse database through the SQL Server command line with the same user that the Warehouse Proxy agent uses to connect to the warehouse, and run the script. To run the script, the user ID must have authorization to alter tables and drop views, or to drop tables when using Case 2 Drop, or truncate tables when using Case 2 Truncate. The output is saved to a file named script name.log. Run the following command: 
    osql -I -S SQLHOST[\SQLINST] -U ITMUSER -P ITMPASS -d WAREHOUS
   -m-1 -n -o log/script.sql.log -i script.sql
    These parameters are used in the example:
    • WAREHOUS is the database name.
    • ITMUSER is the user name used by the Warehouse Proxy agent.
    • ITMPASS is the password used by the Warehouse Proxy agent.
    • script.sql is the name of this script file.
    • SQLHOST is the SQL server name.
    • SQLINST is the optional SQL instance name.

    Note:
    You might receive error messages from the SQL Server such as the following: Msg 4902, Level 16, State 1, Server ENTERPRISE, Line 1 Cannot find the object "table name" because it does not exist or you do not have permissions.

    This message indicates that the table named table name does not exist and cannot be dropped or truncated. This happens if you do not have warehousing or summarization enabled for the given table. For example if you only have hourly and daily summarization enabled, you see this message for the weekly, monthly, quarterly, and yearly summarization tables because these tables do not exist.