Siebel Analytics Server Administration Guide > Sample Scripts and Load Procedures for Usage Tracking Data >

Extracting to Oracle

Before extracting usage tracking log file data, a table has to be created to store the data. The following is a sample script to create the table S_NQ_ACCT for Oracle 7 or 8 (SAACCT.Oracle.sql):

drop table S_NQ_ACCT;
create table S_NQ_ACCT (
UserName           varchar(128),
RepositoryName     varchar(128),
SubjectAreaName    varchar(128),
NodeId             varchar(15),
StartTimestamp     date,
StartDate          date,
StartHourMin       char(5),
EndTimestamp       date,
EndDate            date,
EndHourMin         char(5),
QueryText          varchar(1024),
SuccessFlag        integer,
RowCnt             integer default 0,
TotalTimeInSecs    integer default 0,
CompileTimeInSecs  integer default 0,
NumDBQueries       integer default 0,
CumDBTimeInSecs    integer default 0,
CumDBRows          integer default 0
);

The following is a sample SQL*Loader script for Oracle 7 or 8.

LOAD DATA
INFILE `D:\TEMP\NQAcct.20010214.071500.log' BADFILE NQAcctng.bad
APPEND
INTO TABLE S_NQ_ACCT
fields terminated by `;' optionally enclosed by '"'
(
   UserName,
   RepositoryName,
   SubjectAreaName,
   NodeId,
   StartTimestamp date (19) "YYYY-MM-DD HH24:MI:SS",
   StartDate date (10) "YYYY-MM-DD",
   StartHourMin,
   EndTimestamp date (19) "YYYY-MM-DD HH24:MI:SS",
   EndDate date (10) "YYYY-MM-DD",
   EndHourMin,
   QueryText char(1024),
   SuccessFlag integer external,
   RowCnt integer external,
   TotalTimeInSecs integer external,
   CompileTimeInSecs integer external,
   NumDBQueries integer external,
   CumDBTimeInSecs integer External,
   CumDBRows integer external
)

The next example shows a command to invoke the Oracle loader (for Oracle 7.3). Northwind/n is the user ID and password for logging on to Oracle, and ex1.ctl is the name of the SQL*Loader script. The user ID needs to have permission to update the S_NQ_ACCT table.

Sqlldr73 userid=northwind/n control=ex1.ctl


 Siebel Analytics Server Administration Guide 
 Published: 23 June 2003