Deprecated feature Solaris

Creating a Product Advisor search space manually for the separate search space

The process of manually creating a Product Advisor search space requires a sound understanding of the catalog subsystem and the associated database schema.

About this task

The search space consists of additional database tables that contain information extracted from the following standard WebSphere Commerce tables:

The following steps are required to create the database tables necessary to support Product Advisor searches. In the following steps, you will be provided with sample SQL statements to create a simple search space consisting of tables, views, and the appropriate indexes. These statements include data, such as product and category ID's, that you will have to replace with your own data. They are displayed here for demonstration purposes. In cases where an SQL statement returns data, especially when this data is used as input in a later step, the returned data will be displayed in bold, enclosed in square brackets following the SQL statement.

Note: The examples in this file are specific to DB2. Oracle users need to create the Oracle specific SQL statements.

Procedure

Decide on a category for which you want to enable a Product Advisor search. This example uses category "1".

List all product attributes and their types for the category. Issue the following SQL statements to get the unique ID number for the categories:


db2 "select distinct(attribute_id),language_id,attrtype_id,name    
      
   from attribute 
   where
     catentry_id in(select catentry_id from catgpenrel where
     catgroup_id=1)
     order by attribute_id"

Choose one or more attributes that are common to all the products in the category. For example, 'Color' of type String and 'Size' of type Integer.

Create a search space with standard product attributes, such as Product id and product description, and the user defined product attributes specified in step 3 (Color and Size). [Category 1 's search space consists of ICT1, ICTDESC1, ICV1_NULL] Issue the following SQL statements:


db2 create table ICT1
   (PRRFNBR bigint not null,
    PRNBR varchar(64),
    constraint ict1_pk primary key (prrfnbr));
db2 create table ICTDESC1 
(PRRFNBR bigint not null,
PRSDESC varchar(254),
AVAILABLE int not null, 
PRTHMB varchar(254), 
XMLDETAIL varchar(254),  
LANGUAGE_ID int not null, 
F_COLOR char(254), 
F_SIZE int, 
Constraint ictd1_pk 
primary key (prrfnbr,language_id) );
db2 create view  ICV1_NULL as select ic.prrfnbr,
ic.prnbr,
icd.PRSDESC,
icd.AVAILABLE, 
icd.PRTHMB, 
icd.XMLDETAIL,
icd.LANGUAGE_ID, 
icd.F_COLOR, 
icd.F_SIZE, 
p.ipsgnbr, 
p.storeid, 
p.ppprc, 
p.setccurr
from ICTPRICES p, ICT1 ic, ICTDESC1 icd 
where ic.PRRFNBR=p.CATENTRY_ID and
ic.PRRFNBR=icd.PRRFNBR and
p.IPSGNBR is NULL;

A search space exists for a category when there is a row in the ICROOTCAT table for that category. Insert meta data for each search space created. Issue the following SQL statements:
```
db2 "insert into icrootcat 
   (rootcategoryid,catgroup_id,pfpasync,pfreq,tablename,
   lastmodified)
   values
   (1,1,0,1,'ICV1_','2000-06-15-14.48.25.686000')"
```

There is a row in the ICEXPLFEAT table for every searchable attribute. The FEATUREID value can be obtained from the KEYS table. Issue the following SQL statements to insert meta data for each user defined product attribute:


db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) values (1,
1,'F_COLOR',254,'com.ibm.commerce.datatype.DsString', 0,1,1,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(2, 1,'F_SIZE',4,'com.ibm.commerce.datatype.DsInteger', 0,1,1,1,0);

Insert meta data for each standard product attribute. Issue the following SQL statements, which should not require changes:


db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(3, 1,'PRRFNBR',4,'com.ibm.commerce.datatype.DsInteger',
1,0,0,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(4,1,'PRNBR',64,'com.ibm.commerce.datatype.DsString', 0,0,0,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(5, 1,'PRSDESC',254,'com.ibm.commerce.datatype.DsString',
0,1,3,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(6, 1,'PRTHMB',254,'com.ibm.commerce.datatype.DsImage', 0,1,3,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(7, 1,'XMLDETAIL',32700,'com.ibm.commerce.datatype.DsURLLink',
0,1,3,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(8, 1,'PPPRC',16,'com.ibm.commerce.datatype.DsCurrency',
0,0,2,1,0);
db2 "insert into icexplfeat
(featureid,catgroup_id,columnname,length,datatype,keysequence,
nullable,location,included,relevance) 
values
(9, 1,'AVAILABLE',4,'com.ibm.commerce.datatype.DsInteger',
0,0,3,1,0);

The ICEXPLDESC table has one row for every searchable attribute in each supported language. Issue the following SQL statements to insert meta data for each language for each user defined product attribute. The value for the NAME column should be same as the value from the NAME column of the ATTRIBUTE table.


db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values
(1, -1,'Color','','Color',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values 
(2, -1,'Size','','Size',NULL,0,0);

Insert meta data for each language for each standard product attribute. The DESCRIPTION column should be translated for each language.


db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision)
values
(3, -1,'CATENTRY_ID','','Product Reference Number',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values
(4,-1,'PARTNUMBER','','ProductNumber/SKU',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values 
(5, -1,'SHORTDESCRIPTION','','Short Description',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values 
(6, -1,'THUMBNAIL','','Thumbnail Image File',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values 
(7, -1,'XMLDETAIL','','XML Detail',NULL,0,0);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values 
(8, -1,'STANDARDPRICE','','MSRP',NULL,2,15);
db2 "insert into icexpldesc (featureid,language_id,name,
unitofmeasure,description,elaboration,scale,precision) 
values 
(9, -1,'AVAILABLE','','Availability',NULL,0,0);

Extract data from CATENTRY to ICT1. Issue the following SQL commands:


db2 "insert into ict1 (select catentry_id,
partnumber from catentry where MARKFORDELETE=0
AND (catentry_id in (select catentry_id 
from catgpenrel where catgroup_id=1)
or catentry_id in (select catentry_id_child 
from catentrel, catgpenrel 
where catentry_id_parent=catentry_id and catgroup_id=1) ))"

Extract data from CATENTDESC to ICTDESC1. The following should be done for each language. The following example is for English. Issue the following SQL commands:


db2 "insert into ictdesc1 (PRRFNBR,
PRSDESC, AVAILABLE, PRTHMB, XMLDETAIL, LANGUAGE_ID)
(select c2.catentry_id, c2.shortdescription, c2.available,
c2.thumbnail, c2.xmldetail, c2.language_id 
from catentry c1, catentdesc c2 
where c1.MARKFORDELETE=0 and 
(c1.catentry_id = c2.catentry_id and c2.language_id=-1 and
c2.published=1 and (c2.catentry_id in 
(select catentry_id from catgpenrel 
where catgroup_id=1) or 
c2.catentry_id in (select catentry_id_child 
from catentrel, catgpenrel where 
catentry_id_parent=catentry_id 
and catgroup_id=1) )))"

Extract data from ATTRVALUE for each language, for each user defined product attribute to ICTDESC1. The following should be done for each language. The following example is for English. Issue the following SQL commands:


db2 "update ictdesc1 set F_Color = 
(select stringvalue from attrvalue 
where ictdesc1.prrfnbr=attrvalue.catentry_id 
and ictdesc1.language_id=attrvalue.language_id 
and attrvalue.attribute_id in 
(select attribute_id from attribute 
where name = 'Color') 
and attrvalue.language_id=-1 
and (attrvalue.catentry_id in 
(select catentry_id from catgpenrel 
where catgroup_id=1) or attrvalue.catentry_id 
in (select catentry_id_child 
from catentrel, catgpenrel 
where catentry_id_parent=catentry_id 
and catgroup_id=1) )) 
where language_id=-1"
db2 "update ictdesc1 set F_Size = 
(select integervalue from attrvalue 
where ictdesc1.prrfnbr=attrvalue.catentry_id 
and ictdesc1.language_id=attrvalue.language_id 
and attrvalue.attribute_id in 
(select attribute_id from attribute
where name = 'Size') 
and attrvalue.language_id=-1 
and attrvalue.catentry_id in 
(select catentry_id from catgpenrel 
where catgroup_id=1) or attrvalue.catentry_id 
in (select catentry_id_child 
from catentrel, catgpenrel 
where catentry_id_parent=catentry_id 
and catgroup_id=1) ))  
where language_id=-1"

Extract product prices from the OFFERPRICE table to the ICTPRICES table. There should be one price for every product in each currency.


db2 "insert into ictprices 
(storeid, catentry_id,ppprc,setccurr)
select storeent_id, storecent.
catentry_id, price, currency 
from storecent, offerprice,offer,catentry 
where storeent_id=0 and catentry.markfordelete=0 
and (storecent.catentry_id 
in (select catentry_id from catgpenrel 
where catgroup_id=1) or storecent.catentry_id 
in (select catentry_id_child 
from catentrel, catgpenrel where 
catentry_id_parent=catentry_id 
and catgroup_id=1)) 
and storecent.catentry_id = offer.catentry_id 
and offerprice.offer_id in 
(SELECT offer.OFFER_ID FROM TRADEPOSCN T1
WHERE T1.TRADEPOSCN_ID=offer.TRADEPOSCN_ID 
AND T1.TYPE='S' 
AND offer.MINIMUMQUANTITY IS NULL) 
and catentry.catentry_id=storecent.catentry_id"