Temporary table schema definition

Temporary table schema definition

The WebSphere Commerce Search index is built from temporary tables. The temporary tables are structured to efficiently push data into the search index.

Table summary
The following list summarizes the table categories listed in this topic:

Naming conventions
General change history tables
Index scope tables
Attribute Dictionary data preprocessing
Unstructured content attachment information
Workspace synchronization
Prices

Naming conventions

Use a prefix of XI_ when naming custom temporary tables. This naming convention prevents naming conflicts between customization tables and default WebSphere Commerce tables.

General change history tables

The following tables track change history for catalog entries or catalog groups:

TI_DELTA_CATENTRY
Delta changes for catalog entries.
TI_DELTA_CATENTRY
Column Name Column Type Column Description
MASTERCATALOG_ID BIGINT NOT NULL The master catalog ID of the catalog entry.
CATENTRY_ID BIGINT NOT NULL The ID of the changed catalog entry.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is in progress).

A

Used when a product is previewed.

The product is updated with a U action; then, the U action changes to an A action.

B

The build index REST call is completed

X

Marked for cleanup.

LASTUPDATE TIMESTAMP NOT NULL The last modification time of the changed catalog entry.
Unique index:

Name Column Name Type
idce111 MASTERCATALOG_ID + CATENTRY_ID + ACTION Unique Index

TI_CATENTRY_WS
This table is used to track all search index-related change history for catalog entries in a workspace.
TI_CATENTRY_WS
Column Name Column Type Column Description
MASTERCATALOG_ID BIGINT NOT NULL The master catalog ID of the catalog entry.
CATENTRY_ID BIGINT NOT NULL The ID of the changed catalog entry.
LASTUPDATE TIMESTAMP NOT NULL The last modification time of the changed catalog entry.
TASKGROUP VARCHAR The task group name of the changed catalog entry.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is in progress).

X

Marked for cleanup.

CONTENT_BASE INTEGER IBM internal use only.
Unique index:

Name Column Name Type
idwscat111 MASTERCATALOG_ID + CATENTRY_ID + TASKGROUP Unique Index

TI_CATGROUP_WS
This table is used to track all search index-related change history for catalog groups in a workspace.
TI_CATGROUP_WS
Column Name Column Type Column Description
MASTERCATALOG_ID BIGINT NOT NULL The master catalog ID of the catalog group.
CATGROUP_ID BIGINT NOT NULL The ID of the changed catalog group.
LASTUPDATE TIMESTAMP NOT NULL The last modification time of the changed catalog group.
TASKGROUP VARCHAR The task group name of the changed catalog group.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is in progress).

X

Marked for cleanup.

CONTENT_BASE INTEGER IBM internal use only.
Unique index:

Name Column Name Type
idwscat222 MASTERCATALOG_ID + CATGROUP_ID + TASKGROUP Unique Index

TI_DELTA_CATGROUP
This table is used to track all search index-related delta change history for catalog groups in a workspace.
TI_DELTA_CATGROUP
Column Name Column Type Column Description
MASTERCATALOG_ID BIGINT NOT NULL The master catalog ID of the catalog group.
CATGROUP_ID BIGINT NOT NULL The ID of the changed catalog group.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is in progress).

X

Marked for cleanup.

B

The build index REST call has successfully run.

LASTUPDATE TIMESTAMP NOT NULL The last modification time of the changed catalog group.
CONTENT_BASE INTEGER IBM internal use only.
Unique index:

Name Column Name Type
idce222 MASTERCATALOG_ID + CATGROUP_ID + ACTION Unique Index

TI_DELTA_INVENTORY
This table is used to track all search index-related delta change history for inventory in a workspace.
Note: This table is not populated by default.
TI_DELTA_INVENTORY
Column Name Column Type Column Description
MASTERCATALOG_ID BIGINT NOT NULL The master catalog ID of the catalog entry.
CATENTRY_ID BIGINT NOT NULL The ID of the changed catalog entry.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is in progress).

X

Marked for cleanup.

A

Used when a product is previewed.

The product is updated with a U action; then, the U action changes to an A action.

B

The build index REST call has successfully run.

LASTUPDATE TIMESTAMP NOT NULL The last modification time of the changed inventory.
Unique index:

Name Column Name Type
idce333 MASTERCATALOG_ID + CATENTRY_ID + ACTION Unique Index

Index scope tables
For the following tables, #INDEX_SCOPE_TAG# is replaced by search index core creation procedure. The value is the property value of IndexScopeTag under the CONFIG column of the
SRCHCONF table for a master catalog ID. For example:
select config from srchconf where indextype='CatalogEntry' and indexscope='10101'
Where 10101 is the master catalog ID.

TI_CATENTRY_#INDEX_SCOPE_TAG#
This table stores catalog entry type properties.
TI_CATENTRY_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY table.
CATENTTYPE_ID CHAR(16) The catalog entry type. This column has one of the following values:

ProductBean
ItemBean
PackageBean
BundleBean
DynamicKitBean
The value is from the CATENTRY table.

TI_CATALOG_#INDEX_SCOPE_TAG#
This table stores catalog properties.
TI_CATALOG_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
CATALOG VARCHAR(256) The catalog IDs that the catalog entry belongs to. The catalog IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATGPENREL table.

TI_CEDSOVR_
This table contains preprocess catalog entry description override information to be used to generate search hits.
TI_CEDSOVR_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the CATGPENREL table.
NAME_OVR CHAR (128) The language-dependent override name of the corresponding catalog entry in the
CATENTDESCOVR table.
SHORTDESC_OVR VARCHAR (254) The short description override of the corresponding catalog entry in the CATENTDESCOVR table.
KEYWORD_OVR VARCHAR (254) The override keyword for the corresponding catalog entry in the CATENTDESCOVR table.
PUBLISHED_OVR INTEGER The PUBLISHED flag override used for the corresponding catalog entry in the
CATENTDESCOVR table. Indicates whether this catalog entry should be displayed for the language indicated by LANGUAGE_ID: 0= catalog entry should not be displayed, 1= catalog entry should be displayed.

TI_DPCATENTRY_#INDEX_SCOPE_TAG#
This table stores catalog entry parent properties.
TI_DPCATENTRY_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID_CHILD column of the
CATENTREL table.
CATENTRY_PARENT VARCHAR(512) The parent catalog entries this catalog entry belongs to. The parent catalog entry IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATENTRY_ID_PARENT column of the CATENTREL table.
ITEM_SEQUENCE DECIMAL The sequence of the item. The value is from the SEQUENCE column of the CATENTREL table.

TI_DPGROUP_#INDEX_SCOPE_TAG#
This table stores catalog group properties.
TI_DPGROUP_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the CATGPENREL table.
CATGROUP VARCHAR(256) The direct categories that this catalog entry belongs to. The category IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATALOG_ID column of the CATGPENREL table.

TI_DPVARIANT_#INDEX_SCOPE_TAG#
This table stores catalog entry parent variant properties.
TI_DPVARIANT_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT The catalog entry ID. The value is from the CATENTRY_ID_CHILD column of the
CATENTREL table.
PARENT_VARIANT VARCHAR(1024) The parent variants to which this catalog entry belongs. The parent catalog entry IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATENTRY_ID_PARENT column of the CATENTREL table.

TI_PRODUCTSET_#INDEX_SCOPE_TAG#
This table stores product set properties.
TI_PRODUCTSET_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the PRSETCEREL table.
PRODUCTSET VARCHAR(256) The product sets the catalog entry belongs to. The product set IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the PRODUCTSET_ID column of the PRODUCTSET table.

TI_OFFERPRICE_#INDEX_SCOPE_TAG#
The offer price for the catalog entry. This table does not include catalog entries of type BundleBean.
TI_OFFERPRICE_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the OFFER table.
PRICE_USD DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the USD currency.
PRICE_EUR DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the EUR currency.
PRICE_CAD DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the CAD currency.
PRICE_CNY DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the CNY currency.
PRICE_JPY DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the JPY currency.
PRICE_KRW DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the KRW currency.
PRICE_BRL DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the BRL currency.
PRICE_TWD DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the TWD currency.
PRICE_PLN DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the PLN currency.
PRICE_RON DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the RON currency.
PRICE_RUB DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the RUB currency.
PRICE_EGP DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the EGP currency.
PRICE_GBP DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the GBP currency.
PRICE_TRY DECIMAL(20,5) The value from the PRICE column of the OFFERPRICE table, for the TRY currency.
PRICE_ILS DECIMAL(20,5) The value from the PRICE column of the OFFERPRICE table, for the ILS currency.

TI_BUNDLEPRICE_#INDEX_SCOPE_TAG#
This table stores the offer price for catalog entries of type BundleBean.
TI_BUNDLEPRICE_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the OFFER table.
PRICE_USD DECIMAL(20,5) The value is the SUM of the prices in the PRICE_USD column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_EUR DECIMAL(20,5) The value is the SUM of the prices in the PRICE_EUR column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_CAD DECIMAL(20,5) The value is the SUM of the prices in the PRICE_CAD column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_CNY DECIMAL(20,5) The value is the SUM of the prices in the PRICE_CNY column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_JPY DECIMAL(20,5) The value is the SUM of the prices in the PRICE_JPY column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_KRW DECIMAL(20,5) The value is the SUM of the prices in the PRICE_KRW column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_BRL DECIMAL(20,5) The value is the SUM of the prices in the PRICE_BRL column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_TWD DECIMAL(20,5) The value is the SUM of the prices in the PRICE_TWD column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_PLN DECIMAL(20,5) The value is the SUM of the prices in the PRICE_PLN column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_RON DECIMAL(20,5) The value is the SUM of the prices in the PRICE_RON column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_RUB DECIMAL(20,5) The value is the SUM of the prices in the PRICE_RUB column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_EGP DECIMAL(20,5) The value is the SUM of the prices in the PRICE_EGP column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.
PRICE_GBP DECIMAL(20,5) The value is the SUM of the prices in the PRICE_GBP column of the TI_OFFERPRICE_#INDEX_SCOPE_TAG# table for the CATENTRY_IDs included in the bundle.

TI_APGROUP_#INDEX_SCOPE_TAG#
This table stores catalog group relationship properties.
TI_APGROUP_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
CATGROUPS VARCHAR(4000) The value in the column has the following format: 10101_10132. The first number is the catalog_id and the second number is the catalog group ID. If the catalog entry belongs to multiple groups, the multiple values are separated by a semicolon by default.

Attribute Dictionary data preprocessing

The #lang_tag# tag is replaced by the data import preprocess. The role is using the value from the language_id column of the language table without the minus sign. For example, TI_ADSTB1_#INDEX_SCOPE_TAG#_1 stores the information for Attribute Dictionary attributes for English. The mapping between the attribute and columns in the following Attribute Dictionary related tables are store in the ATTRDICTSRCHCONF table.

TI_ATTR_TI_#INDEX_SCOPE_TAG#_#lang_tag#

This table stores the attributes for Attribute Dictionary data preprocessing.
TI_ADFTB1_#INDEX_SCOPE_TAG#_#lang_tag#

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
ATTRS CLOB The values for a string type of attribute that is configured in the ATTRDICTSRCHCONF table.
ATTRI CLOB The values for an integer type of attribute that is configured in the ATTRDICTSRCHCONF table.
ATTRF CLOB The values for a float type of attribute that is configured in the ATTRDICTSRCHCONF table.
SRCHS CLOB The values for a string type of attribute that is configured in the ATTRDICTSRCHCONF table.
SRCHI CLOB The values for an integer type of attribute that is configured in the ATTRDICTSRCHCONF table.
SRCHF CLOB The values for a float type of attribute that is configured in the ATTRDICTSRCHCONF table.

TI_ADATTR_#INDEX_SCOPE_TAG#_#lang_tag#
This table stores catalog entry descriptive and defining attribute dictionary attributes properties.

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL (Primary Key) The catalog entry ID. The value is from the CATENTRYATTR table.
ATTRIBUTES VARCHAR A tokenized string of attributes' properties. See Attribute Dictionary attributes.

TI_GROUPING_#INDEX_SCOPE_TAG#
This table is used to populate the grouping_parentCatentry_id index field. The field is used to create a relation between productBeans anditemBeans.
TI_GROUPING_#INDEX_SCOPE_TAG#
Column name Column type Column description
CATENTRY_ID BIGINT NOT NULL (primary key) The catalog entry ID.
GROUPING BIGINT IBM internal use only.

TI_GROUPING_VARIANT_#INDEX_SCOPE_TAG#
This table is used to populate the grouping_parentVariant_id index field. The field is used to create a relation between variantBeans and itemBeans.
TI_GROUPING_VARIANT_#INDEX_SCOPE_TAG#
Column name Column type Column description
COMBINEKEY VARCHAR(50) The primary key.
CATENTRY_ID BIGINT The catalog entry ID.
GROUPING_VARIANT BIGINT IBM internal use only.

TI_CGPUB_#INDEX_SCOPE_TAG#
This table is used to populate the deepPublished index field, which identifies what category group is published or not. This is a deep published feature.
TI_CGPUB_#INDEX_SCOPE_TAG#
Column name Column type Column description
CATGROUP_ID BIGINT NOT NULL (primary key) The catalog entry ID.
PUBLISHED INTEGER IBM internal use only.

TI_ENTGRPPATH_#INDEX_SCOPE_TAG#
This table is used to populate the globalSequence index field. It is supports the global sequence feature.
TI_ENTGRPPATH_#INDEX_SCOPE_TAG#
Column name Column type Column description
CATENTRY_ID BIGINT NOT NULL (primary key) The catalog entry ID.
ENTGRPPATH CLOB IBM internal use only.

Unstructured content attachment information

The following tables store the attachment information for unstructured content.

TI_CEATCHT_#INDEX_SCOPE_TAG#_#lang_tag# and TI_CEATCHTU_#INDEX_SCOPE_TAG#_#lang_tag#

TI_CEATCHT_#INDEX_SCOPE_TAG#_#lang_tag#

Column Name Column Type Column Description
ATCHREL_ID BIGINT NOT NULL The attachment relationship ID.
ATCHAST_ID BIGINT NOT NULL The primary key value of the attachment asset metadata table, ATCHAST.
CATENTRY_ID BIGINT NOT NULL The catalog entry ID the attachment is associated with.
IDENTIFIER VARCHAR(128) NOT NULL The identifier of the attachment in the ATCHTGT table
NAME VARCHAR(128) The language-dependent name of the attachment target in the ATCHTGTDSC table.
SHORTDESCRIPTION VARCHAR(254) The language-dependent short description of the attachment target in ATCHTGTDSC table.
LONGDESCRIPTION VARCHAR(4000) The language-dependent long description of the attachment target in ATCHTGTDSC table.
ATCHASTPATH VARCHAR(128) The attachment asset path is the relative path of the attachment asset, which includes the directory path and the file name of the attachment asset. For example, /image/a.gif. The value is from the ATCHAST table.
DIRECTORY VARCHAR(254) The base name of the directory in which store-specific Web assets, such as JSP files and property files, are found. The value is from the STORE table.
DIRECTORYPATH VARCHAR(128) The directory path of the attachment asset. This is a substring of the attachment asset path when the attachment asset is a file. For example, if the attachment asset path is /image/a.gif, then its directory path is /image. The value is from the ATCHAST table.
MIMETYPE VARCHAR(254) The MIME type of the attachment asset. The value is from the ATCHAST table.
LANGUAGE_ID INTEGER The value is from the ATCHASTLG table.
IMAGE VARCHAR(254) The image path that represents the attachment relation usage. The value is from the ATCHRLUS table.
RULENAME VARCHAR(254) The identifier for the attachment relation usage. For example, Image. The value is from the IDENTIFIER column of the ATCHRLUS table.
SEQUENCE BIGINT NOT NULL The sequence column value from ATCHREL table.

TI_CEATCHTU_#INDEX_SCOPE_TAG#_#lang_tag#

TI_CEATCHTU_#INDEX_SCOPE_TAG#_#lang_tag#

Column Name Column Type Column Description
ATCHREL_ID BIGINT NOT NULL The attachment relationship ID.
ATCHAST_ID BIGINT NOT NULL The primary key value of the attachment asset metadata table, ATCHAST.
CATENTRY_ID BIGINT NOT NULL The catalog entry ID the attachment is associated with.
IDENTIFIER VARCHAR(128) NOT NULL The identifier of the attachment in the ATCHTGT table
NAME VARCHAR(128) The language-dependent name of the attachment target in the ATCHTGTDSC table.
SHORTDESCRIPTION VARCHAR(254) The language-dependent short description of the attachment target in ATCHTGTDSC table.
LONGDESCRIPTION VARCHAR(4000) The language-dependent long description of the attachment target in ATCHTGTDSC table.
ATCHASTPATH VARCHAR(520) The attachment asset path is the relative path of the attachment asset, which includes the directory path and the file name of the attachment asset. For example, /image/a.gif. The value is from the ATCHAST table.
DIRECTORY VARCHAR(254) The base name of the directory in which store-specific Web assets, such as JSP files and property files, are found. The value is from the STORE table.
DIRECTORYPATH VARCHAR(128) The directory path of the attachment asset. This is a substring of the attachment asset path when the attachment asset is a file. For example, if the attachment asset path is /image/a.gif, then its directory path is /image. The value is from the ATCHAST table.
MIMETYPE VARCHAR(254) The MIME type of the attachment asset. The value is from the ATCHAST table.
LANGUAGE_ID INTEGER The value is from the ATCHASTLG table.
IMAGE VARCHAR(254) The image path that represents the attachment relation usage. The value is from the ATCHRLUS table.
RULENAME VARCHAR(254) The identifier for the attachment relation usage. For example, Image. The value is from the IDENTIFIER column of the ATCHRLUS table.
SEQUENCE BIGINT NOT NULL The sequence column value from ATCHREL table.

Workspace synchronization

The following tables are used for workspace synchronization between workspaces and base content.

TI_CGCATALOG_#INDEX_SCOPE_TAG#
This table stores catalog group workspace properties.
TI_CGCATALOG_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATGROUP_ID BIGINT NOT NULL The catalog group ID.
CATALOG VARCHAR(256) The catalog IDs that the catalog entry belongs to. The catalog IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATGPENREL table.

TI_DKITPRICE_#INDEX_SCOPE_TAG#
This table stores dynamic kit price properties.
TI_DKITPRICE_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the OFFER table.
PRICE_USD DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the USD currency.
PRICE_EUR DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the EUR currency.
PRICE_CAD DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the CAD currency.
PRICE_CNY DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the CNY currency.
PRICE_JPY DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the JPY currency.
PRICE_KRW DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the KRW currency.
PRICE_BRL DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the BRL currency.
PRICE_TWD DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the TWD currency.
PRICE_PLN DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the PLN currency.
PRICE_RON DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the RON currency.
PRICE_RUB DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the RUB currency.
PRICE_EGP DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the EGP currency.
PRICE_GBP DECIMAL(20,5) The value is from the PRICE column of the OFFERPRICE table, for the GBP currency.

TI_ATTR_#INDEX_SCOPE_TAG#
This table stores catalog entry attribute properties.
TI_ATTR_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
ATTRS CLOB The values for a string type of attribute that is configured in the ATTRDICTSRCHCONF table.
ATTRI CLOB The values for a string type of attribute that is configured in the ATTRDICTSRCHCONF table.
ATTRF CLOB The values for a string type of attribute that is configured in the ATTRDICTSRCHCONF table.

TI_CATENTREL_#INDEX_SCOPE_TAG#
This table stores catalog entry relationship properties.
TI_CATENTREL_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID_PARENT CLOB NOT NULL The parent catalog entries this catalog entry belongs to. The parent catalog entry IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATENTRY_ID_PARENT column of the CATENTREL table.
CATENTRY__ID_CHILD CLOB NOT NULL The child catalog entries this catalog entry belongs to. The child catalog entry IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATENTRY_ID_CHILD column of the CATENTREL table.

TI_CATGPENREL_#INDEX_SCOPE_TAG#
This table stores catalog group sequence properties.
TI_CATGPENREL_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATGROUP_ID BIGINT NOT NULL The catalog group ID.
SEQUENCE VARCHAR(10240) The sequence of the catalog group.

TI_CATGRPREL_#INDEX_SCOPE_TAG#
This table stores catalog group sequence properties.
TI_CATGRPREL_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATGROUP_ID BIGINT NOT NULL The catalog group ID.
SEQUENCE VARCHAR(10240) The sequence of the catalog group.

TI_CGSEOURL_#INDEX_SCOPE_TAG#
This table stores catalog group SEO properties.
TI_CGSEOURL_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATGROUP_ID BIGINT NOT NULL The catalog group ID.
SEO_TOKEN VARCHAR(256) The SEO token.

TI_SEOURL_#INDEX_SCOPE_TAG#
This table stores catalog entry SEO properties.
TI_SEOURL_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
SEO_TOKEN VARCHAR(256) The SEO token.

TI_D_CATENTRY_#INDEX_SCOPE_TAG#
This table stores the catalog entry action properties.
TI_D_CATENTRY_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is currently in progress).

X

Marked for cleanup.

TI_D_CGCATLG_#INDEX_SCOPE_TAG#
This table stores the catalog group action properties.
TI_D_CGCATLG_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATGROUP_ID BIGINT NOT NULL The catalog group ID.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is currently in progress).

X

Marked for cleanup.

TI_OFFER_
This table is used to preprocess the offer price.
TI_OFFER_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the CATGPENREL table.
OFFER_ID BIGINT NOT NULL The generated unique key OFFER_ID from the OFFER table.

TI_STORECENT_
This table is used to preprocess the store path info to be indexed.
TI_STORECENT_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID. The value is from the CATENTRY_ID column of the CATGPENREL table.
STOREENT_ID INTEGER NOT NULL The generated unique key STOREENT_ID from the STOREENT table.

TI_X_CATENTRY_#INDEX_SCOPE_TAG#
This table stores the catalog entry action properties.
TI_X_CATENTRY_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is currently in progress).

X

Marked for cleanup.

TI_X_CGCATLG_#INDEX_SCOPE_TAG#
This table stores the catalog group action properties.
TI_X_CGCATLG_#INDEX_SCOPE_TAG#
Column Name Column Type Column Description
CATGROUP_ID BIGINT NOT NULL The catalog group ID.
ACTION CHAR The action type:
U

Create/update.

D

Delete.

F

Full update is required.

P

Pending (indexing is currently in progress).

X

Marked for cleanup.

TI_WEBCONTENT_#INDEX_SCOPE_TAG#_#lang_tag#
This table stores the web content properties.
TI_WEBCONTENT_#INDEX_SCOPE_TAG#_#lang_tag#

Column Name Column Type Column Description
CONTENT_ID BIGINT NOT NULL The web content ID.
URL VARCHAR(512) The URL of the web content.
LOCALPATH VARCHAR(1024) The local path of the web content.

TI_DPGRPNAME_#INDEX_SCOPE_TAG#_#lang_tag#
This table stores the catalog group properties.
TI_DPGRPNAME_#INDEX_SCOPE_TAG#_#lang_tag#

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL The catalog entry ID.
CATGRPNAME CLOB The parent directory category name.

TI_COMPONENTS_#INDEX_SCOPE_TAG#
This table stores the bundle. packages, and dynamic kits components properties.

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL (Primary Key) The catalog entry ID. This the CATENTRY_ID_PARENT value from the CATENTREL table.
COMPONENTS VARCHAR A tokenized string of component's properties. See Bundles and Packages components.

Prices

The following tables contain information for list prices for catalog entries:

TI_LISTPRICE_#INDEX_SCOPE_TAG#
The list price for the catalog entry. This table does not include catalog entries of type BundleBean, and DynamicKitBean.

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL (Primary Key) The catalog entry ID. This the CATENTRY_ID_PARENT value from the CATENTREL table.
LISTPRICE_USD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the USD currency.
LISTPRICE_EUR DECIMAL The value is from the PRICE column of the LISTPRICE table, for the EUR currency.
LISTPRICE_CAD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the CAD currency.
LISTPRICE_CNY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the CNY currency.
LISTPRICE_JPY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the JPY currency.
LISTPRICE_KRW DECIMAL The value is from the PRICE column of the LISTPRICE table, for the KRY currency.
LISTPRICE_BRL DECIMAL The value is from the PRICE column of the LISTPRICE table, for the BRL currency.
LISTPRICE_TWD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the TWD currency.
LISTPRICE_PLN DECIMAL The value is from the PRICE column of the LISTPRICE table, for the PLN currency.
LISTPRICE_RON DECIMAL The value is from the PRICE column of the LISTPRICE table, for the RON currency.
LISTPRICE_RUB DECIMAL The value is from the PRICE column of the LISTPRICE table, for the RUB currency.
LISTPRICE_EGP DECIMAL The value is from the PRICE column of the LISTPRICE table, for the EGP currency.
LISTPRICE_GBP DECIMAL The value is from the PRICE column of the LISTPRICE table, for the GBP currency.
LISTPRICE_TRY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the TRY currency.
LISTPRICE_ILS DECIMAL The value is from the PRICE column of the LISTPRICE table, for the ILS currency.

TI_LBUNDLEPRICE_#INDEX_SCOPE_TAG#
This table stores the list price for catalog entries of type BundleBean.

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL (Primary Key) The catalog entry ID. This the CATENTRY_ID_PARENT value from the CATENTREL table.
LISTPRICE_USD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the USD currency.
LISTPRICE_EUR DECIMAL The value is from the PRICE column of the LISTPRICE table, for the EUR currency.
LISTPRICE_CAD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the CAD currency.
LISTPRICE_CNY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the CNY currency.
LISTPRICE_JPY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the JPY currency.
LISTPRICE_KRW DECIMAL The value is from the PRICE column of the LISTPRICE table, for the KRY currency.
LISTPRICE_BRL DECIMAL The value is from the PRICE column of the LISTPRICE table, for the BRL currency.
LISTPRICE_TWD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the TWD currency.
LISTPRICE_PLN DECIMAL The value is from the PRICE column of the LISTPRICE table, for the PLN currency.
LISTPRICE_RON DECIMAL The value is from the PRICE column of the LISTPRICE table, for the RON currency.
LISTPRICE_RUB DECIMAL The value is from the PRICE column of the LISTPRICE table, for the RUB currency.
LISTPRICE_EGP DECIMAL The value is from the PRICE column of the LISTPRICE table, for the EGP currency.
LISTPRICE_GBP DECIMAL The value is from the PRICE column of the LISTPRICE table, for the GBP currency.
LISTPRICE_TRY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the TRY currency.
LISTPRICE_ILS DECIMAL The value is from the PRICE column of the LISTPRICE table, for the ILS currency.

TI_LDKITPRICE_#INDEX_SCOPE_TAG#
This table stores the offer price for catalog entries of type DynamicKitBean.

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL (Primary Key) The catalog entry ID. This the CATENTRY_ID_PARENT value from the CATENTREL table.
LISTPRICE_USD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the USD currency.
LISTPRICE_EUR DECIMAL The value is from the PRICE column of the LISTPRICE table, for the EUR currency.
LISTPRICE_CAD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the CAD currency.
LISTPRICE_CNY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the CNY currency.
LISTPRICE_JPY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the JPY currency.
LISTPRICE_KRW DECIMAL The value is from the PRICE column of the LISTPRICE table, for the KRY currency.
LISTPRICE_BRL DECIMAL The value is from the PRICE column of the LISTPRICE table, for the BRL currency.
LISTPRICE_TWD DECIMAL The value is from the PRICE column of the LISTPRICE table, for the TWD currency.
LISTPRICE_PLN DECIMAL The value is from the PRICE column of the LISTPRICE table, for the PLN currency.
LISTPRICE_RON DECIMAL The value is from the PRICE column of the LISTPRICE table, for the RON currency.
LISTPRICE_RUB DECIMAL The value is from the PRICE column of the LISTPRICE table, for the RUB currency.
LISTPRICE_EGP DECIMAL The value is from the PRICE column of the LISTPRICE table, for the EGP currency.
LISTPRICE_GBP DECIMAL The value is from the PRICE column of the LISTPRICE table, for the GBP currency.
LISTPRICE_TRY DECIMAL The value is from the PRICE column of the LISTPRICE table, for the TRY currency.
LISTPRICE_ILS DECIMAL The value is from the PRICE column of the LISTPRICE table, for the ILS currency.

TI_CNTRPRICE_#INDEX_SCOPE_TAG#
This table stores the offer price for catalog entries of type DynamicKitBean.

Column Name Column Type Column Description
CATENTRY_ID BIGINT NOT NULL (Primary Key) The ID of the catalog entry. This column contains all catalog entry for the specified master catalog.
TRADING_ID BIGINT The ID of the applied contract.
CURRENCY CHAR(3) The currency for the specified price amount.
PRICE DECIMAL(20,5) Contains the final, unique, and the best price for this catalog entry under this contract.

Related concepts
Workspaces in WebSphere Commerce Search

Column Name	Column Type	Column Description
MASTERCATALOG_ID	BIGINT NOT NULL	The master catalog ID of the catalog entry.
CATENTRY_ID	BIGINT NOT NULL	The ID of the changed catalog entry.
ACTION	CHAR	The action type: U Create/update. D Delete. F Full update is required. P Pending (indexing is in progress). A Used when a product is previewed. The product is updated with a U action; then, the U action changes to an A action. B The build index REST call is completed X Marked for cleanup.
LASTUPDATE	TIMESTAMP NOT NULL	The last modification time of the changed catalog entry.

Column Name	Column Type	Column Description
MASTERCATALOG_ID	BIGINT NOT NULL	The master catalog ID of the catalog group.
CATGROUP_ID	BIGINT NOT NULL	The ID of the changed catalog group.
LASTUPDATE	TIMESTAMP NOT NULL	The last modification time of the changed catalog group.
TASKGROUP	VARCHAR	The task group name of the changed catalog group.
ACTION	CHAR	The action type: U Create/update. D Delete. F Full update is required. P Pending (indexing is in progress). X Marked for cleanup.
CONTENT_BASE	INTEGER	IBM internal use only.

Column Name	Column Type	Column Description
CATENTRY_ID	BIGINT NOT NULL	The catalog entry ID.
CATALOG	VARCHAR(256)	The catalog IDs that the catalog entry belongs to. The catalog IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATGPENREL table.

Column Name	Column Type	Column Description
CATENTRY_ID	BIGINT NOT NULL	The catalog entry ID. The value is from the CATENTRY_ID column of the CATGPENREL table.
NAME_OVR	CHAR (128)	The language-dependent override name of the corresponding catalog entry in the CATENTDESCOVR table.
SHORTDESC_OVR	VARCHAR (254)	The short description override of the corresponding catalog entry in the CATENTDESCOVR table.
KEYWORD_OVR	VARCHAR (254)	The override keyword for the corresponding catalog entry in the CATENTDESCOVR table.
PUBLISHED_OVR	INTEGER	The PUBLISHED flag override used for the corresponding catalog entry in the CATENTDESCOVR table. Indicates whether this catalog entry should be displayed for the language indicated by LANGUAGE_ID: 0= catalog entry should not be displayed, 1= catalog entry should be displayed.

Column Name	Column Type	Column Description
CATENTRY_ID	BIGINT	The catalog entry ID. The value is from the CATENTRY_ID_CHILD column of the CATENTREL table.
PARENT_VARIANT	VARCHAR(1024)	The parent variants to which this catalog entry belongs. The parent catalog entry IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATENTRY_ID_PARENT column of the CATENTREL table.

Column Name	Column Type	Column Description
CATENTRY_ID	BIGINT NOT NULL (Primary Key)	The catalog entry ID. The value is from the CATENTRYATTR table.
ATTRIBUTES	VARCHAR	A tokenized string of attributes' properties. See Attribute Dictionary attributes.

Column name	Column type	Column description
COMBINEKEY	VARCHAR(50)	The primary key.
CATENTRY_ID	BIGINT	The catalog entry ID.
GROUPING_VARIANT	BIGINT	IBM internal use only.

Column Name	Column Type	Column Description
ATCHREL_ID	BIGINT NOT NULL	The attachment relationship ID.
ATCHAST_ID	BIGINT NOT NULL	The primary key value of the attachment asset metadata table, ATCHAST.
CATENTRY_ID	BIGINT NOT NULL	The catalog entry ID the attachment is associated with.
IDENTIFIER	VARCHAR(128) NOT NULL	The identifier of the attachment in the ATCHTGT table
NAME	VARCHAR(128)	The language-dependent name of the attachment target in the ATCHTGTDSC table.
SHORTDESCRIPTION	VARCHAR(254)	The language-dependent short description of the attachment target in ATCHTGTDSC table.
LONGDESCRIPTION	VARCHAR(4000)	The language-dependent long description of the attachment target in ATCHTGTDSC table.
ATCHASTPATH	VARCHAR(128)	The attachment asset path is the relative path of the attachment asset, which includes the directory path and the file name of the attachment asset. For example, /image/a.gif. The value is from the ATCHAST table.
DIRECTORY	VARCHAR(254)	The base name of the directory in which store-specific Web assets, such as JSP files and property files, are found. The value is from the STORE table.
DIRECTORYPATH	VARCHAR(128)	The directory path of the attachment asset. This is a substring of the attachment asset path when the attachment asset is a file. For example, if the attachment asset path is /image/a.gif, then its directory path is /image. The value is from the ATCHAST table.
MIMETYPE	VARCHAR(254)	The MIME type of the attachment asset. The value is from the ATCHAST table.
LANGUAGE_ID	INTEGER	The value is from the ATCHASTLG table.
IMAGE	VARCHAR(254)	The image path that represents the attachment relation usage. The value is from the ATCHRLUS table.
RULENAME	VARCHAR(254)	The identifier for the attachment relation usage. For example, Image. The value is from the IDENTIFIER column of the ATCHRLUS table.
SEQUENCE	BIGINT NOT NULL	The sequence column value from ATCHREL table.

Column Name	Column Type	Column Description
CATGROUP_ID	BIGINT NOT NULL	The catalog group ID.
CATALOG	VARCHAR(256)	The catalog IDs that the catalog entry belongs to. The catalog IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATGPENREL table.

Column Name	Column Type	Column Description
CATENTRY_ID_PARENT	CLOB NOT NULL	The parent catalog entries this catalog entry belongs to. The parent catalog entry IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATENTRY_ID_PARENT column of the CATENTREL table.
CATENTRY__ID_CHILD	CLOB NOT NULL	The child catalog entries this catalog entry belongs to. The child catalog entry IDs are separated by a semicolon by default, if no separator is specified in the wc-dataimport-preprocess-common.xml file. The values are from the CATENTRY_ID_CHILD column of the CATENTREL table.

Column Name	Column Type	Column Description
CONTENT_ID	BIGINT NOT NULL	The web content ID.
URL	VARCHAR(512)	The URL of the web content.
LOCALPATH	VARCHAR(1024)	The local path of the web content.

Column Name	Column Type	Column Description
CATENTRY_ID	BIGINT NOT NULL (Primary Key)	The catalog entry ID. This the CATENTRY_ID_PARENT value from the CATENTREL table.
COMPONENTS	VARCHAR	A tokenized string of component's properties. See Bundles and Packages components.

Column Name	Column Type	Column Description
CATENTRY_ID	BIGINT NOT NULL (Primary Key)	The ID of the catalog entry. This column contains all catalog entry for the specified master catalog.
TRADING_ID	BIGINT	The ID of the applied contract.
CURRENCY	CHAR(3)	The currency for the specified price amount.
PRICE	DECIMAL(20,5)	Contains the final, unique, and the best price for this catalog entry under this contract.