Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Server Administration Guide > Query Caching in Siebel Analytics Server > Cache Event Processing with an Event Polling Table >
Setting Up Event Polling Tables on the Physical Databases
This section describes how to set up the Siebel Analytics Server event polling tables on physical databases.
Polling Table Structure
You can set up a physical event polling table on each physical database to monitor changes in the database. You can also set up the event table in its own database. The event table should be updated every time a table in the database changes. The event table needs to have the structure shown in Table 28; some columns can contain NULL values depending on where the event table resides.
The column names for the event table are suggested; you can use any names you want. However, the order of the columns has to be the same as shown in Table 28. Sample CREATE TABLE statements to create an event polling table are shown in Sample Event Polling Table CREATE TABLE Statements.
The Siebel Analytics Server needs to have read and write permission on the event polling table. The server reads the event table at specified intervals to look for changed data. Applications add rows to the event table when database tables are modified (for example, during a load operation). When there are rows in the event table, there is changed data in the underlying databases. The server then invalidates any cache entries corresponding to the changed physical tables and periodically deletes obsolete rows from the event table. The next time it checks the event table, the process repeats.
To allow Siebel Analytics Server write access to the event polling table but not to any other tables in a database, you can create a separate physical database in the Physical layer of the Administration Tool with a privileged connection pool (privileged to delete from the table). Populate this privileged database with the event table. Setting up this type of polling database makes sure that the Siebel Analytics Server has write access to the event polling table, but not to any tables that are used to answer user queries.
Sample Event Polling Table CREATE TABLE Statements
The following are sample CREATE TABLE statements for SQL Server 7.0 and Oracle 8i. These CREATE TABLE statements create the structure required for a Siebel Analytics Server event polling table. In these statements, the table created is named UET. It resides in the same database as the physical tables that are being updated.
NOTE: The column lengths need to be large enough to represent the object names in your repository.
The following is the CREATE TABLE statement for SQL Server 7.0:
// SQL Server 7.0 Syntax
create table UET (
UpdateType Integer not null,
UpdateTime datetime not null DEFAULT CURRENT_TIMESTAMP,
DBName char(40) null,
CatalogName varchar(40) null,
SchemaName varchar(40) null,
TableName varchar(40) not null,
Other varchar(80) null DEFAULT NULL
)The following is the CREATE TABLE statement for Oracle 8i:
// Oracle 8i syntax
create table UET (
UpdateType Integer not null,
UpdateTime date DEFAULT SYSDATE not null,
DBName char(40) null,
CatalogName varchar(40) null,
SchemaName varchar(40) null,
TableName varchar(40) not null,
Other varchar(80) DEFAULT NULL
);You might need to modify these CREATE TABLE statements slightly for different versions of SQL Server and Oracle, or for other databases. Additionally, if you want to specify any explicit storage clauses, you need to add the appropriate clauses to the statements.
Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Server Administration Guide Published: 11 March 2004 |