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

Extracting to DB2

Before extracting usage tracking log file data, a table has to be created to store the data. The following is a sample command script to create the table S_NQ_ACCT for DB2 UDB v6 (SAACCT.DB2.sql).

NOTE:  StartTime and EndTime are defined as CHAR(19), because the DB2 load utility cannot read ODBC format (yyyy-mm-dd hh:mi:ss) for timestamps represented as character strings.

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

From the DB2 Command Line Processor, enter the following commands to load the table:

load from D:\TEMP\NQAcct.20010214.071500.log of DEL
   modified by coldel,
   Method P (1,2,3,4,5,6,7,8,9,10,11,12,13,14)
insert into S_NQ_ACCT(UserName, RepositoryName,
             SubjectAreaName, NodeId, StartTimestamp,
             StartDate, StartHourMin, EndTimestamp,
             EndDate, EndHourMin, QueryText, SuccessFlag,
             RowCnt, TotalTimeInSecs, CompileTimeInSecs,
             NumDBQueries, CumDBTimeInSecs, CumDBRows)


 Siebel Analytics Server Administration Guide 
 Published: 23 June 2003