There are three .ddl files to create the tables in the bttsmartchannel.jar for three different databases: DB2: dataCollector_DB2.ddl Oracle: dataCollector_ORACLE.ddl SQLServer: dataCollector_SQLSERVER.ddl.
Use the following commands to create databases that used in smart channel in DB2.
-- ============================================================ -- ============================================================ -- Table: ITEM_LIST -- ========================= =================================== create table ITEM_LIST ( ITEM_ID VARCHAR(20) not null , NAME VARCHAR(200) , DESCRIPTION VARCHAR(200) , constraint PK_4 primary key (ITEM_ID) ); -- ============================================================ -- Table: CHANNEL_LIST -- ============================================================ create table CHANNEL_LIST ( CHANNEL_ID VARCHAR(50) not null , NAME VARCHAR(50) , DESCRIPTION VARCHAR(200) , constraint PK_5 primary key (CHANNEL_ID) ); -- ============================================================ -- Table: VISIT_LOG -- ============================================================ create table VISIT_LOG ( VISIT_ID VARCHAR(200) not null , CHANNEL_ID VARCHAR(50) not null , USER_ID VARCHAR(50) not null , LOGON_TIME TIMESTAMP not null , IP_ADDRESS VARCHAR(20) , BROWSER VARCHAR(50) , LOGOUT_TIME TIMESTAMP , constraint PK_3 primary key (VISIT_ID) ); -- ============================================================ -- Index: VISIT_INDEX -- ============================================================ create unique index VISIT_INDEX on VISIT_LOG (USER_ID asc, LOGON_TIME asc, LOGOUT_TIME asc); -- ============================================================ -- Table: BEHAVIOR_LOG -- ============================================================ create table BEHAVIOR_LOG ( BEHAVIOR_ID VARCHAR(50) not null , USER_ID VARCHAR(50) not null , ITEM_ID VARCHAR(20) not null , BEHAVIOR_TYPE VARCHAR(2) not null , ACCESS_TIME TIMESTAMP not null , VISIT_ID VARCHAR(200) not null , constraint PK_1 primary key (BEHAVIOR_ID) ); -- ============================================================ -- Index: BEHAVIOR_INDEX -- ============================================================ create unique index BEHAVIOR_INDEX on BEHAVIOR_LOG (ACCESS_TIME asc, USER_ID asc, BEHAVIOR_TYPE asc, ITEM_ID asc); -- ============================================================ -- Table: PREFERENCE_LOG -- ============================================================ create table PREFERENCE_LOG ( PREFERENCE_ID VARCHAR(50) not null , USER_ID VARCHAR(50) not null , ITEM_ID VARCHAR(20) not null , ACCESS_TIME TIMESTAMP not null , VISIT_ID VARCHAR(200) not null , constraint PK_8 primary key (PREFERENCE_ID) ); -- ============================================================ -- Index: PREFERENCE_INDEX -- ============================================================ create unique index PREFERENCE_INDEX on PREFERENCE_LOG (USER_ID asc, ACCESS_TIME asc, ITEM_ID asc); alter table VISIT_LOG foreign key FK_41 (CHANNEL_ID) references CHANNEL_LIST (CHANNEL_ID) on delete cascade; alter table BEHAVIOR_LOG foreign key FK_51 (ITEM_ID) references ITEM_LIST (ITEM_ID) on delete cascade; alter table BEHAVIOR_LOG foreign key FK_54 (VISIT_ID) references VISIT_LOG (VISIT_ID) on delete cascade; alter table PREFERENCE_LOG foreign key FK_45 (VISIT_ID) references VISIT_LOG (VISIT_ID) on delete cascade; alter table PREFERENCE_LOG foreign key FK_48 (ITEM_ID) references ITEM_LIST (ITEM_ID) on delete cascade;