![]() |
![]() |
[ Bottom of Page | Previous Page | Next Page | Contents | Index ]
This section describes maintenance tasks for the warehouse pack.
See Installing and Configuring Tivoli(R) Data Warehouse for information about backing up and restoring your data. This Tivoli Enterprise Console(R) warehouse pack requires no additional procedures.
This section provides information about pruning data from the central data warehouse database and data mart database.
Parameters are provided to control how often the databases are pruned. The parameter values represent a pruning duration whose format is yyyymmdd. Preceding zeros are not included in the pruning duration value. For example, the value 0000300 represents three months. The following are examples of other values that are used:
yyyymmdd | Example value |
---|---|
600 | 6 months |
10000 | 1 year |
0050000 | 5 years |
To manage the high volume of event data, use the Prune_Event_Ctrl table to delete that data. The Prune_Event_Log table keeps a history of data pruning.
By default, the data older than 6 months is pruned when the CDW_c05_Prune_and_Mark_Active process runs. This process is within the CDW_Tivoli_Data_Warehouse_v1.2.0_Subject_Area. By default, this process runs daily at 6:00 a.m.
Tivoli Data Warehouse uses the event age and the event date and time to find aged events. Aged events are ones that are older than the value specified in the Event_Age column. Then, the following data is deleted in this order:
Table 6 shows an example of the values that the Tivoli Enterprise Console specifies in the Prune_Event_Ctrl table.
MSrc_CD CHAR (6) | TmSum_CD CHAR(1) | Event_Age |
---|---|---|
EC2 | P | 600 |
This means that event data is retained by default for 6 months in the central data warehouse. Use the following SQL statement on the control server at the DB2(R) prompt to change the default value:
update twg.prune_event_ctrl set Event_Age=x where MSrc_Cd='EC2'
The value x is the new value that you want to specify. For an explanation of the value format, see Pruning data.
Pruning data from the fact tables is implemented in the EC2_m10_s050_mart_prune step. The prune mart control table EC2.Prune_Mart_Control specifies which data are pruned and contains a pruning duration value for the fact tables. By default, all fact data older than 1 year is pruned when the process step runs.
The record of data pruning is written in the EC2.Prune_Mart_log table.
Specify the data to be pruned by setting the value of the PMARTC_DURATION column of the Prune_MART_Control table. Modify the value using an SQL statement on the control server at the DB2 prompt. For example, modify and then run the following SQL command on the control server at the DB2 prompt on the data mart (EC2_MART) to change the prune values:
UPDATE EC2.PRUNE_MART_Control
SET PMARTC_DURATION =xxx
WHERE TABLE_NAME='EC2.F_EVENT_PIT'
The value xxx is the value you want to change. For an explanation of the value format, see Pruning data.
The extraction control table assists you in incrementally extracting data from a source database. For an example of incremental extraction, see the Enabling an Application for Tivoli Data Warehouse guide. Table 7 and Table 8 provide the extract control information.
ExtCtl_
Source VARCHAR (120) |
ExtCtl_
Target VARCHAR (120) |
ExtCtl_
From_ RawSeq CHAR (10) |
ExtCtl_
To_ RawSeq CHAR (10) |
ExtCtl_
From_ IntSeq BIGINT |
ExtCtl_
To_ IntSeq BIGINT |
ExtCtl_
From_ DtTm TIME STAMP |
ExtCtl_
To_ DtTm TIME STAMP |
MSrc_
Corr_Cd CHAR (6) |
---|---|---|---|---|---|---|---|---|
1x.tec_t_evt_rep | EC2.stg_events | 0 |
99999 99999 99999 999 |
1970-01-01-00.00. 00.000000 |
1970-01-01-00.00. 00.000000 |
EC2 |
1 The central data warehouse ETL can be sourced from multiple databases. Each source has a different value specified for x in ExtCtl_Source.
ExtCtl_
Source VARCHAR (120) |
ExtCtl_
Target VARCHAR (120) |
ExtCtl_
From_ RawSeq CHAR (10) |
ExtCtl_
To_ RawSeq CHAR (10) |
ExtCtl_
From_ IntSeq BIGINT |
ExtCtl_
To_IntSeq BIGINT |
ExtCtl_
From_ DtTm TIME STAMP |
ExtCtl_
To_ DtTm TIME STAMP |
MSrc_
Corr_Cd CHAR (6) |
---|---|---|---|---|---|---|---|---|
TWG.EVENTTYP | EC2.D_EVTTYP_
METRIC |
0 | 0 |
1970-01-01-00.00. 00.000000 |
9999-01-01-00.00. 00.000000 |
EC2 | ||
TWG.EVENT | EC2.F_EVENT_PIT | 0 | 0 |
1970-01-01-00.00. 00.000000 |
9999-01-01-00.00. 00.000000 |
EC2 | ||
TWG.EVENTATTR | EC2.F_EVENT_PIT | 0 | 0 |
1970-01-01-00.00. 00.000000 |
9999-01-01-00.00. 00.000000 |
EC2 |
This section provides supplemental information about the Tivoli Enterprise Console warehouse pack.
The wtdwfilter command is used to create and modify the event filter and the extended attribute filter. These filters are respectively used to determine which classes of events and extended attributes are moved from the Tivoli Enterprise Console event database to the central data warehouse database. Both filters are inclusive filters. Only events that belong to the event classes that are specified in the event filter table are moved. Likewise, only extended attributes that are specified in the extended attribute filter table and that are also related to the specified event classes are moved.
wtdwfilter [option [suboption]]
Use the wtdwfilter command to perform the following tasks:
See Command line syntax for a description of the characters that are used to specify wtdwfilter command syntax.
The user who issues the wtdwfilter command must be authorized to create and delete tables, because the wtdwfilter command issues RDBMS Interface Module (RIM) commands. If an unauthorized user tries to issue the wtdwfilter command, it fails when you try to create or delete the associated event filter and extended attribute filter tables.
TEC_DB TEC_Error TEC_Notice TEC_Start TEC_Stop
TEC_DB TEC_Error TEC_Notice TEC_Start TEC_Stop
sql_code
sql_code
wrb -lsrbclass Default > new.fileDefault is the name of your rule base and new.file can be any name you choose.
Class Name ---------------------------------
wrb -lsrbclass -detailed Default > classAttr.fileDefault is the name of your rule base and classAttr.file can be any name you choose.
Remember that only extended attributes that belong to event classes that have been selected and moved to the event filter table are moved from the Tivoli Enterprise Console source database to the central data warehouse.
For more information on the wrb command, see the Tivoli Enterprise Console Command and Task Reference.
The following example creates an event filter table and extended attribute filter in a table space named yourtablespace:
wtdwfilter -crttb -tblspace yourtablespace
The following example imports into the event filter table the event classes listed in a file named class.list:
wtdwfilter -impclass -file class.list
The following is an example of the class.list file that lists the classes:
TEC_DB TEC_Error TEC_Notice TEC_Start TEC_Stop
The following example imports into the extended attribute filter table the attribute names listed in a file named attribute.list:
wtdwfilter -impattr -file attribute.list
The following is an example of the attribute.list file that lists the classes:
sql_code
The following example deletes the classes that are loaded in the rulebase named Default from the event filter table. Note that the -debug option is also specified to provide informational messages.
wtdwfilter -delclass -rb Default -debug
The following example deletes the extended attributes that are listed in the file named classAttr.file from the extended attribute filter table. Note that the -debug option is also specified to provide informational messages.
wtdwfilter -delattr -file classAttr.file -debug
This warehouse pack ETL can read data from multiple instances of Tivoli Enterprise Console event databases. However, each of the Tivoli Enterprise Console servers must have a different server ID to differentiate them. The server ID is 1 by default. Use the following procedure to change the server ID:
wstopesvr
wtdbclear -efl -t 0
#tec_server_handle=1
tec_server_handle=6
wstartesvr
During installation of this warehouse pack, the user name that is used to install the warehouse pack is the default schema name for each of the tables that contain the source event information in the event database. Although this does not affect the behavior of the warehouse pack, it prevents you from sampling or viewing the table contents using the DB2 Data Warehouse Center. For more information about the source tables, see Overview of the Tivoli Enterprise Console warehouse pack.
To sample or view the table contents for event databases, perform the following procedure for all data sources with a name similar to EC2_xxx_Source. EC2_TEC_Source is the default name and is used in the following procedure.
Database type | Name | More information |
---|---|---|
1 You cannot sample or view the contents of a table if your event database is a Sybase database. | ||
DB2 | Database administrator name | Specify the name of the database administrator that was used to create the event database and the source tables. |
Informix(R) | None | Do not specify a name. |
Sybase | None | Do not specify a name. 1 |
Oracle | Database administrator name | Specify the name of the database administrator that was used to create the event database and the source tables. |
Microsoft(R) SQL | Database administrator name | Specify the name of the database administrator that was used to create the event database and the source tables. |
If your event database is not a DB2 database, the database client for your database must be installed on the control server or the Tivoli Data Warehouse remote agent to enable communication with the events database. To determine which database clients are supported by the Tivoli Data Warehouse and the procedure to install the clients, see the Tivoli Data Warehouse Release Notes. Once the client has been installed, you must configure the control server DB2 drivers as described in this section.
Do one of the following procedures on the control server for your event database type:
If your event database is an Informix database, you must configure the control server DB2 Data Warehouse drivers to communicate with the Informix database.
If your event database is a Sybase database, you must configure the Tivoli Data Warehouse DB2 Data Warehouse drivers to communicate with the Sybase database using the following procedure:
If your event database is a Microsoft SQL database, you must configure the Tivoli Data Warehouse DB2 Data Warehouse drivers to communicate with the Microsoft SQL database using the following procedure:
If your event database is a Oracle database, you must configure the Tivoli Data Warehouse DB2 Data Warehouse drivers to communicate with the Oracle database using the following procedure:
For common problems and solutions, see Installing and Configuring Tivoli Data Warehouse.
If you have a problem with this warehouse pack, review the information in this section.
======================== = Generic file line # : 381 = Script file line # : 102 = Entering xferTable : TEC (IBM DB2) -> TWH_CDW (IBM DB2) = Unaltered SQL Stmt : "insert into EC2.STG_EVT_RELN ( src_corr_val, tgt_corr_val ) SELECT {fn CONCAT({fn RTRIM(CAST (a.server_hndl as char(10)))}, {fn CONCAT('-', {fn CONCAT({fn RTRIM(CAST (a.cause_event_handle as char(10)))}, {fn CONCAT('-',{fn RTRIM(CAST (a.cause_date_receptn as char(10)))})})})})}, {fn CONCAT({fn RTRIM(CAST (a.server_hndl as char(10)))}, {fn CONCAT('-', {fn CONCAT({fn RTRIM(CAST (a.event_hndl as char(10)))}, {fn CONCAT('-', {fn RTRIM(CAST (a.date_reception as char(10)))})})})})} FROM tec_t_evt_rep a, EC2_SRC_EXTCTL b, tec_evt_filter c WHERE a.cause_date_receptn <> 0 AND a.cause_event_handle <> 0 AND NOT EXISTS ( SELECT 1 FROM EC2_SRCCHK_RUN d WHERE d.db_instance_num = '1' AND d.table_name = 'EC2.STG_EVT_RELN' ) AND b.extctl_source = '1.tec_t_evt_rep' AND a.last_modified_time <= b.extctl_to_dttm AND a.last_modified_time >= b.extctl_from_dttm AND a.class = c.class" = Select Statement : " SELECT {fn CONCAT({fn RTRIM(CAST (a.server_hndl as char(10)))}, {fn CONCAT('-', {fn CONCAT({fn RTRIM(CAST (a.cause_event_handle as char(10)))}, {fn CONCAT('-',{fn RTRIM(CAST (a.cause_date_receptn as char(10)))})})})})}, {fn CONCAT({fn RTRIM(CAST (a.server_hndl as char(10)))}, {fn CONCAT('-', {fn CONCAT({fn RTRIM(CAST (a.event_hndl as char(10)))}, {fn CONCAT('-',{fn RTRIM(CAST (a.date_reception as char(10)))})})})})} FROM tec_t_evt_rep a, EC2_SRC_EXTCTL b, tec_evt_filter c WHERE a.cause_date_receptn <> 0 AND a.cause_event_handle <> 0 AND NOT EXISTS ( SELECT 1 FROM EC2_SRCCHK_RUN d WHERE d.db_instance_num = '1' AND d.table_name = 'EC2.STG_EVT_RELN' ) AND b.extctl_source = '1.tec_t_evt_rep' AND a.last_modified_time <= b.extctl_to_dttm AND a.last_modified_time >= b.extctl_from_dttm AND a.class = c.class" CDWEX8087E A general SQL error occurred: SQL_ERROR: 'Execute Src Select' 2004.12.16 10:57:57.937 sqlState = 42S02, nativeErr = -204, errorMsg = [IBM][CLI Driver][DB2/NT] SQL0204N "DB2ADMIN.TEC_EVT_FILTER" is an undefined name. SQLSTATE=42704 . ======== Aborted (16) 2004.12.16 10:57:57.953 ========
======================== = Generic file line # : 1494 = Script file line # : 229 = Entering xferTable : TEC (IBM DB2) -> TWH_CDW (IBM DB2) = Unaltered SQL Stmt : "insert into EC2.STG_EXTS_FILTER ( slot_name ) SELECT a.slot_name FROM tec_t_exts_filter a" = Select Statement : " SELECT a.slot_name FROM tec_t_exts_filter a" CDWEX8087E A general SQL error occurred: SQL_ERROR: 'Execute Src Select' 2004.12.16 11:01:04.062 sqlState = 42S02, nativeErr = -204, errorMsg = [IBM][CLI Driver][DB2/NT] SQL0204N "DB2ADMIN.TEC_T_EXTS_FILTER" is an undefined name. SQLSTATE=42704 . ======== Aborted (16) 2004.12.16 11:01:04.062 ========
The step should be changed as follows:
net stop vwlogger
net start vwkernel
[ Top of Page | Previous Page | Next Page | Contents | Index ]