How to configure the log consolidator database

Using Actuate logging and monitoring APIs : About BIRT iHub usage and error log consolidator : How to configure the log consolidator database

1 Configure a database server machine, Oracle server, and database.

2 Using Oracle SQL*Plus, log in as the system database administrator.

3 Run the CreateActuateLogTables.sql script.

The following command runs the CreateActuateLogTables.sql script from the default directory in the BIRT iHub Integration Technology installation for Windows:

SQL> @"C:\Program Files\Actuate\ServerIntTech\UsageAndErrorConsolidator\DBScripts\CreateActuateLogTables.sql";

CreateActuateLogTables.sql drops the ActuateLog and ActuateLogUser users, performs cascading deletes on all their objects, including sequences, tables, and indexes, and recreates these objects. The script creates the following database objects:

Tables to contain the usage and error log data

INSERT statements add predefined codes and descriptions for the event, file, job, object, operation, output format, service, and status types, after creating the tables.

Sequence generators to provide the values for usage and error event IDs when inserting log data

Each usage and error event ID is a unique value.

Indexes to contain primary and foreign key columns in the tables:

Primary key constraints on columns containing predefined codes ensure that these values are unique and not null.

Foreign key references on columns containing predefined codes ensure that these values are consistent with the values in the primary keys.

The ActuateLog schema contains the following tables and indexes:

AcAdminEvent

Contains the log records for administration operation events, including the following data:

Event ID

Object type code, indicating a User, Role, Channel, Group, File, or Folder object

Object operation code, indicating a Create, Delete, Modify, Login, Logout, or Download operation

Object name, version name, size, and attribute

Old and new values

Table 11‑7 shows the structure of the AcAdminEvent table.

Table 11‑7 Structure of the AcAdminEvent table
Column	Data type	Constraint	References	Key
EventId	INTEGER	AcApEv _AdEvId _Idx	AcEvent .EventId	Primary
ObjectTypeCode	INTEGER		AcObject Type .Object TypeCode
ObjectOperationCode	INTEGER		AcObject Operation .Object Operation Code
ObjectName	VARCHAR2(1000)
ObjectVersionName	VARCHAR2(255)
ObjectSize	INTEGER
ObjectAttribute	VARCHAR2(50)
OldValue	VARCHAR2(2000)
NewValue	VARCHAR2(2000)

AcApplicationEvent

Contains the log records for application events, including the following data:

Event ID

Executable name

Executable version, indicating an RPTDOCUMENT, RPTDESIGN, or other file type

Job type code, indicating an Async, Persistent, and Transient job type

Resource group ID

Dispatch node, indicating the volume, system, and server

Output format code, indicating PDF, XLS, HTML, or other output format

Table 11‑8 shows the structure of the AcApplicationEvent table.

Table 11‑8 Structure of the AcApplicationEvent table
Column	Data type	Constraint	References	Key
EventId	INTEGER	AcApEv _ApEvId _Idx	AcEvent .EventId	Primary
ExecutableName	VARCHAR2(1000)
ExecutableVersion	VARCHAR2(100)		AcFileType .FileTypeCode
FileTypeCode	INTEGER
Parameters	VARCHAR2(2000)
JobName	VARCHAR2(100)
JobTypeCode	INTEGER		AcJobType .JobTypeCode
JobSubmittedTimestamp	DATE
ResourceGroupId	INTEGER		AcResource Group .Resource GroupId
DispatchNode	INTEGER		AcSystem Component .System ComponentId
RequestId	VARCHAR2(100)
RequestWaitTime	INTEGER
RequestRunningTime	INTEGER
OutputName	VARCHAR2(1000)
OutputVersion	VARCHAR2(100)
OutputFormatCode	INTEGER		AcOutput Format .Output Format Code
OutputSize	INTEGER
PageCount	INTEGER
PageNumbersViewed	VARCHAR2(50)

AcErrorEvent

Contains the log records for error events, including the following data:

Error event ID

System component ID, indicating the volume, system, and server

User name

Error code, category, severity, parameters, and message

Table 11‑9 shows the structure of the AcErrorEvent table.

Table 11‑9 Structure of the AcErrorEvent table
Column	Data type	Constraint	References	Key
ErrorEventId	INTEGER	AcErEv_ErEvId _Idx	AcEvent .EventId	Primary
EventTimestamp	DATE
SystemComponentId	INTEGER		AcSystem Component .System ComponentId
UserName	VARCHAR2(255)
ErrorCode	INTEGER
ErrorCategory	VARCHAR2(255)
ErrorSeverity	INTEGER
ErrorParameter1	VARCHAR2(50)
ErrorParameter2	VARCHAR2(50)
ErrorParameter3	VARCHAR2(50)
ErrorMessage	VARCHAR2(255)

AcErrorLogOffset

Contains the following usage log data:

File offsets

Volume names

Last update timestamp

Table 11‑10 shows the structure of the AcErrorLogOffset table.

Table 11‑10 Structure of the AcErrorLogOffset type
Column	Data type/Values	Constraint	References	Key
FileIndex	NUMBER
FileOffset	NUMBER
VolumeName	VARCHAR2(50)	NOT NULL		Primary
LastUpdateTimeStamp	NUMBER

AcEvent

Contains the log records for events, including the following data:

Event ID

Event timestamp

System component ID, indicating the volume, system, and server

Event type code, indicating a Generate, Print, View, Delete, Admin, Query, or Search event type

Start and end timestamps

Status code, indicating Success or Failure

Service type code, indicating a Factory, View, Encyclopedia, Integration, or Cache service type

Table 11‑11 shows the structure of the AcEvent table.

Table 11‑11 Structure of the AcEvent table
Column	Data type	Constraint	References	Key
EventId	INTEGER	AcSt_StCo_Idx		Primary
EventTimestamp	DATE
SystemComponentId	INTEGER		AcSystem Component .SystemComponent Id
UserName	VARCHAR2(50)
EventTypeCode	INTEGER		AcEventType .EventTypeCode
StartTimestamp	DATE
EndTimestamp	DATE
StatusCode	INTEGER		AcStatus .StatusCode
ServiceTypeCode	INTEGER	EventId	AcServiceType .ServiceType Code

AcEventType

Contains the codes and descriptions for event types, including the following data:

Event type code, including the following predefined values:

1 through 7

Event type description, including the following predefined values:

Generate, Print, View, Delete, Admin., Query, Search

Table 11‑12 shows the structure of the AcEventType table.

Table 11‑12 Structure of the AcEventType table
Column	Data type/Values	Constraint	References	Key
EventTypeCode	INTEGER1-7	AcEvTy_EvTyCo_Idx		Primary
EventTypeDescription	VARCHAR2(50)

AcFileType

Contains the codes and descriptions for the following file type data:

File type codes, including the following predefined values:

1 through 39

File types, including the following predefined values:

UNKNOWN, DOX, DCD, HTM, HTML, ICD, IOB, PDF, ROL, ROP, ROS, ROV, ROW, RPTDESIGN, RPTDOCUMENT, RPTLIBRARY, RPTTEMPLATE, RTF, SMA, TXT, XLS

Table 11‑13 shows the structure of the AcFileType table.

Table 11‑13 Structure of the AcFileType table
Column	Data type/Value	Constraint	References	Key
FileTypeCode	INTEGER1-39	AcFiTy_FiTyCo_Idx		Primary
FileType	VARCHAR2(20)

AcJobType

Contains the codes and descriptions for the following job type data:

Job type codes, including the following predefined values:

1 through 3

Job type descriptions, including the following predefined values:

Async, Persistent, Transient

Table 11‑14 shows the structure of the AcJobType table.

Table 11‑14 Structure of the AcJobType table
Column	Data type/Value	Constraint	References	Key
JobTypeCode	INTEGER1-3	AcJoTy_JoTyCo_Idx		Primary
JobTypeDescription	VARCHAR2(20)

AcObjectOperation

Contains the codes and descriptions for the following object operation data:

Object operation codes, including the following predefined values:

1 through 6

Object operation descriptions, including the following predefined values:

Create, Delete, Modify, Login, Logout, Download

Table 11‑15 shows the structure of the AcObjectOperation table.

Table 11‑15 Structure of the AcObjectOperation table
Column	Data type/Value	Constraint	References	Key
ObjectOperationCode	INTEGER	AcObOp_ObOpCo_Idx		Primary
ObjectOperation Description	VARCHAR2(20)

AcObjectType

Contains the codes and descriptions for the following object type data:

Object type codes, including the following predefined values:

1 through 6

Object type descriptions, including the following predefined values:

User, Role, Channel, Group, File, Folder

Table 11‑16 shows the structure of the AcObjectType table.

Table 11‑16 Structure of the AcObjectType table
Column	Data type/Values	Constraint	References	Key
ObjectTypeCode	INTEGER	AcObTy_ObTyCo_Idx		Primary
ObjectTypeDescription	VARCHAR2(20)

AcOutputFormat

Contains the codes and descriptions for the following output format data:

Output format codes, including the following predefined values:

1 through 42

Output format descriptions, including the following predefined values:

UNKNOWN, PDF, XLS, ROW, DHTML, HTML, HTM, RTF, REPORTLET, XMLDISPLAY, XMLCOMPRESSEDDISPLAY, DHTMLRAW, DHTMLLONG, CSS, ANALYSIS, EXCELDISPLAY, EXCELDATA, EXCELDATADUMP, RTFFULLYEDITABLE, UNCSV, TSV, EXCEL, XMLDATADUMP, XMLREPORTLET, XMLCOMPRESSEDREPORTLET, XMLCOMPRESSEDEXCEL, XMLCOMPRESSEDPDF, XMLCOMPRESSEDRTF, XMLSTYLE, XMLDATA, RPTDOCUMENT, RPTLIBRARY, RPTTEMPLATE

Table 11‑17 shows the structure of the AcOutputFormat table.

Table 11‑17 Structure of the AcOutputFormat table
Column	Data type/Values	Constraint	References	Key
OutputFormatCode	INTEGER1-42	AcOuFo_OuFoCo_Idx		Primary
OutputFormat Description	VARCHAR2(30)

AcResourceGroup

Contains the codes and descriptions for the following resource group data:

Resource group ID, including the following predefined value:

Resource group name, including the following predefined value:

NULL

Table 11‑18 shows the structure of the AcResourceGroup table.

Table 11‑18 Structure of the AcResourceGroup table
Column	Data type/Values	Constraint	References	Key
ResourceGroupId	INTEGER	AcReGr_ReGrId_Idx		Primary
ResourceGroupName	VARCHAR2(128)

AcServiceType

Contains the codes and descriptions for the following service type data:

Service type code, including the following predefined values:

1 through 5

Service type description, including the following predefined values:

Factory, View, Encyclopedia, Integration, Cache

Table 11‑19 shows the structure of the AcServiceType table.

Table 11‑19 Structure of the AcServiceType table
Column	Data type/Values	Constraint	References	Key
ServiceTypeCode	INTEGER	AcSeTy_SeTyCo_Idx		Primary
ServiceTypeDescription	VARCHAR2(50)

AcStatus

Contains the codes and descriptions for the following status type data:

Status codes, including the following predefined values:

0 through 1

Status descriptions, including the following predefined values:

Failure, Success

Table 11‑20 shows the structure of the AcStatus table.

Table 11‑20 Structure of the AcStatus type
Column	Data type/Values	Constraint	References	Key
StatusCode	INTEGER	AcSt_StCo_Idx		Primary
StatusDescription	VARCHAR2(20)

AcSystemComponent

Contains the log records for system components, including the following data:

System component ID

Volume, system, and server names

Table 11‑21 shows the structure of the AcSystemComponent table.

Table 11‑21 Structure of the AcSystemComponent table
Column	Data type/Values	Constraint	References	Key
SystemComponentId	INTEGER	AcSt_StCo_Idx		Primary
VolumeName	VARCHAR2(50)
SystemName	VARCHAR2(50)
ServerName	VARCHAR2(50)

AcUsageLogOffset

Contains the following usage log data:

File offsets

Volume names

Last update timestamp

Table 11‑22 shows the structure of the AcUsageLogOffset table.

Table 11‑22 Structure of the AcUsageLogOffset type
Column	Data type/Values	Constraint	References	Key
FileIndex	NUMBER,
FileOffset	NUMBER
VolumeName	VARCHAR2(50)	NOT NULL		Primary
LastUpdateTime Stamp	NUMBER

The ActuateLog schema contains the following indexes listed in Table 11‑23.

Table 11‑23 Indexes in the ActuateLog schema
Index	Table.Column(s)
AcApEv_AdEvId_Idx	AcAdminEvent.EventId
AcApEv_ApEvId_Idx	AcApplicationEvent.EventId
AcApEv_ExNa_Idx	AcApplicationEvent.ExecutableName
AcApEv_FiTyCo_Idx	AcApplicationEvent.FileTypeCode
AcApEv_JoTyCo_Idx	AcApplicationEvent.JobTypeCode
AcAdEv_ObNa_Idx	AcAdminEvent.ObjectName
AcApEv_OuNa_Idx	AcApplicationEvent.OutputName
AcAdEv_ObTyCo_ObOpCo_Idx	AcAdminEvent.ObjectTypeCode, ObjectOperationCode
AcErEv_ErCo_Idx	AcErrorEvent.ErrorCode
AcErEv_ErEvId_Idx	AcErrorEvent.ErrorEventId
AcErEv_ErSe_Idx	AcErrorEvent.ErrorSeverity
AcErEv_EvTi_Id	AcErrorEvent.EventTimestamp
AcErEv_SyCoId_Idx	AcErrorEvent.SystemComponentId
AcErEv_UsNa_Idx	AcErrorEvent.UserName
AcEv_EvId_Idx	AcStatus.EventId
AcEv_SyCoId_Idx	AcEvent.SystemComponentId
AcEv_StCo_Idx	AcEvent.StatusCode
AcEv_SyTyCo_Idx	AcEvent.ServiceTypeCode
AcEv_EvTi_Idx	AcEvent.EventTimestamp
AcEv_EvTyCo_Idx	AcEvent.EventTypeCode
AcEv_UsNa_Idx	AcEvent.UserName
AcEvTy_EvTyCo_Idx	AcEventType.EventTypeCode
AcFiTy_FiTyCo_Idx	AcFileType.FileTypeCode
AcJoTy_JoTyCo_Idx	AcJobType.JobTypeCode
AcObOp_ObOpCo_Idx	AcObjectOperation.ObjectOperationCode
AcObTy_ObTyCo_Idx	AcObjectType.ObjectTypeCode
AcOuFo_OuFoCo_Idx	AcOutputFormat.OutputFormatCode
AcReGr_ReGrId_Idx	AcResourceGroup.ResourceGroupId
AcSeTy_SeTyCo_Idx	AcServiceType.ServiceTypeCode
AcSt_StCo_Idx	AcStatus.StatusCode
AcSyCo_SyCoId_Idx	AcSystemComponent.SystemComponentId