Database schema changes in this release

This topic describes the database schema changes between WebSphere Commerce 5.4 and WebSphere Commerce 5.5. The changes are grouped into the following sections:

New tables

ACPLGPDESC
This table stores locale specific information for the ACPOLGRP table.
ACPLGPSUBS
This table stores the association between access control policy groups and organizational entities. An organizational entity that is associated with a policy group is said to subscribe to that policy group. The access control policies belonging to that policy group will apply to that organization. An organization can subscribe to multiple policy groups and the same policy group can be subscribed to by multiple organizations.
ACPOLGPPOL
This table stores the association between access control policy groups and access control policies. An access control policy that belongs to a policy group will be applied to all the organizations that subscribe to that policy group.
ACPOLGRP
This table stores all the access control policy groups in the system. Each a row in this table represents a policy group.
ACPOLSUBGP
Reserved for IBM internal use.
APPLY_INIT
This table holds the mining model apply results. The mining model is applied to information pertaining to initiatives.
APPLY_MEMBER
The table contains the output from WebSphere Commerce Analyzer after applying the mining model to members and running the WebSphere Commerce Analyzer sample closed-loop script.
APPLY_RESULT
This table contains the result of a mining apply for contracts or accounts.
CACHEIVL
This table records changes to product or category information in the database. It can be populated by user-defined database triggers.
COLLABWS
This table stores collaborative workspace information.
CPEMAILMAP
This table stores the mapping of the coupon ID to the e-mail IDs of guest customers to which specific coupon IDs persisted by them were sent.
CPPMNACC
This table stores the number of times coupons have been accepted from the coupon promotion by the guest customer identified by the e-mail ID. It also stores the number of times the customer has redeemed coupons from this promotion.
CPPMNCAT
This table contains the purchase conditions for a category-level promotion. If the purchase condition type in CPPMN table was C, then this table is used to find out all the items that are required for this coupon to be redeemed. There can be more than one category condition for a given promotion. All the purchase conditions for a coupon are combined using the AND operator.
CWMEMBER
This table stores collaborative workspace member information.
EMLCFG
This table stores the configuration of e-mail activity accounts. Each store can have one inbound (POP3) and one outbound (SMTP) server configured.
EMLGRPREL
This table stores the relationship between an e-mail activity and its assigned member group (customer profile).
EMLMSG
This table stores e-mail message templates. Each row corresponds to an e-mail message template which can be used within an e-mail activity.
EMLPROMO
This table stores e-mail activities. Each row corresponds to an e-mail activity which is sent to a given customer profile.
EMLRCPTS
This table records the recipients of an e-mail activity.
EMLRPTEVTS
This table records e-mail events for each recipient of a particular e-mail activity. If a row does not exist for a recipient of an e-mail activity, the user has not yet caused an event.
EMLUSRRECV
This table stores user preference of whether to receive e-mail or not.
FILEUPLOAD
This table contains the information of uploaded files.
MBRGRPDESC
This table contains locale specific information for the entries in the MBRGRP table.
MSALESVW
Reserved for IBM internal use.
MSGARCHIVE
This table stores sent messages (if ARCHIVEMSG = "1" is set for the message profile). The stored messages can be resent using the integrated GUI in the Administration Console.
MUSRTRVW
Reserved for IBM internal use.
NOTIFY
Reserved for IBM internal use.
ORDQUOTREL
Each row of this table represents a relationship between a parent Order and a child Order.
PSETADJMNT
This table stores information on product set adjustments and shows which product set is referenced by a term and condition for a business policy.
QUEUECSR
This table contains customer care queue assignment records for Customer Service Representatives.
QUEUEDESC
This table contains Customer Care Queue display information according to languages.
REFKEYS
The table represents a foreign relationship between tables that does not already exist in the database.
REPDLVRY
This table represents how each store wants the automated commerce reports to be delivered.
RFQCATEGRY
This table holds categories created in each RFQ.
RFQRSPEVAL
This table holds all the response product evaluations for the RFQ.
RFQRSPPARL
This table holds the relationship of the personalized attributes in the RFQ and the response.
RFQTARGET
This table holds the target stores for RFQ.
ROLEDESC
This table stores locale specific information for the ROLE table.
SEGMENTATION
This table holds each segmentation run's output. A segmentation is made up of several segments (clusters) defined by attributes (data columns) which are then grouped into several value bins.
SEGMENTATION_REF
Reference table for language-specific translation of each model (SEGMENTATION row) used in the datamining segmentation run.
SHPMODCLCD
Reserved for IBM internal use.
SMTPHOST
This table maintains a list of SMTP servers where WebSphere Commerce has successfully connected to in the past. Any entries in this table should represent a valid SMTP server name and port pair.
STCGRYDESC
This table contains language-dependent information about a StoreCategory.
STOREREL
This table contains the relationships between stores.
STRELTYP
This table contains the store relationship types.
STRELTYPDS
This table contains the store relationship type descriptions.
TCATTR
This table stores information about the terms and conditions attributes specified within a contract. This table stores the XML data required by a term and condition.
WSALESVW
Reserved for IBM internal use.
WUSRTRVW
Reserved for IBM internal use.

Tables containing new columns

ACPLGPDESC
  • ACPOLGRP_ID
  • DESCRIPTION
  • DISPLAYNAME
  • LANGUAGE_ID
ACPLGPSUBS
  • ACPOLGRP_ID
  • ORGENTITY_ID
ACPOLGPPOL
  • ACPOLGRP_ID
  • ACPOLICY_ID
ACPOLGRP
  • ACPOLGRP_ID
  • MEMBER_ID
  • NAME
ACPOLSUBGP
  • CHILD_ID
  • PARENT_ID
APPLY_INIT
  • CONFIDENCE
  • INITIATIVE_ID
  • LAST_UPDATED
  • LAST_UPDATED_ID
  • SCORE
  • SCORE2
  • SEGMENTATION_ID
  • SEGMENT_INDEX
  • SEGMENT_INDEX2
APPLY_MEMBER
  • CONFIDENCE
  • LAST_UPDATED
  • LAST_UPDATED_ID
  • MEMBER_ID
  • SCORE
  • SCORE2
  • SEGMENTATION_ID
  • SEGMENT_INDEX
  • SEGMENT_INDEX2
  • SUB_ID
  • TYPE_ID
APPLY_RESULT
  • CONFIDENCE
  • LAST_UPDATED
  • LAST_UPDATED_ID
  • RESULT_ID
  • SCORE
  • SCORE2
  • SEGMENTATION_ID
  • SEGMENT_INDEX
  • SEGMENT_INDEX2
  • TYPE_ID
ATTACHMENT
  • CONTENT
  • CREATEMETHOD
  • DESCRIPTION
  • FILENAME
  • FILESIZE
  • IMAGE1
  • IMAGE2
  • MARKFORDELETE
  • RESERVED
ATTRIBUTE
  • GROUPNAME
  • MULTITYPE
  • NOTEINFO
  • QTYUNIT_ID
ATTRVALUE
  • ATTACHMENT_ID
  • QTYUNIT_ID
CACHEIVL
  • DATAID
  • INSERTTIME
  • TEMPLATE
CATENTRY
  • ENDDATE
  • STARTDATE
CATGRPDESC
  • NOTE
CATGRPPS
  • USAGE
CATGRPREL
  • CATALOG_ID_LINK
CATGRPTPC
  • STORE_ID
CLEANCONF
  • STATEMENTTYPE
CNTRSTORE
  • CONTRACT_ID
  • STOREXML
COLLABWS
  • ACCOUNT_ID
  • COLLABWS_ID
  • CREATORLOGONID
  • DESCRIPTION
  • NAME
  • PROCID
  • PROCTYPE
  • TEMPLATE
  • TIMECREATED
CPEMAILMAP
  • CPWALLET_ID
  • SHOPPEREMAILID
CPPMN
  • NUMACPALLOWED
CPPMNACC
  • CPPMN_ID
  • NUMACCEPTED
  • NUMREDEEMED
  • SHOPPEREMAILID
CPPMNCAT
  • CATALOG_ID
  • CATGROUP_ID
  • CPPMN_ID
  • MAXQTY
  • MAXVALUE
  • MINQTY
  • MINVALUE
  • QTYMEASURE
CWMEMBER
  • COLLABWS_ID
  • LOGONID
  • ROLE
EMLCFG
  • ACCOUNT
  • ADDRESS
  • DESCRIPTION
  • EMLCFG_ID
  • EMLSERVER
  • HOST
  • NAME
  • PASSWORD
  • PORT
  • STOREENT_ID
  • TIME
  • TYPE
EMLGRPREL
  • EMLPROMO_ID
  • MBRGRP_ID
EMLMSG
  • DESCRIPTION
  • EMLMSG_ID
  • JSPPATH
  • NAME
  • PROPERTYFILE
  • STOREENT_ID
EMLPROMO
  • DESCRIPTION
  • EMLMSG_ID
  • EMLPROMO_ID
  • MODDATE
  • MODIFIEDBY
  • NAME
  • SCCJOBREFNUM
  • STATUS
  • STOREENT_ID
EMLRCPTS
  • COUNT
  • EMLPROMO_ID
  • RECIPIENTS
EMLRPTEVTS
  • BOUNCED
  • CLICKED
  • EMLPROMO_ID
  • OPENED
  • USERS_ID
EMLUSRRECV
  • RECEIVEEML
  • STOREENT_ID
  • USERS_ID
FFMCENTER
  • EXTFFMSTORENUM
  • INVENTORYOPFLAGS
FILEUPLOAD
  • FILEENCODING
  • FILENAME
  • FILEPATH
  • FILESIZE
  • FILETYPE
  • FILEUPLOAD_ID
  • HOSTNAME
  • MEMBER_ID
  • SCCJOBREFNUM
  • STATUS
  • STORE_ID
  • UPLOADTIME
  • VERSION
INTVSCHED
  • STOREENT_ID
ITEMFFMCTR
  • RESTOCKTIME
MASSOCCECE
  • DATE1
  • STORE_ID
MASSOCGPGP
  • DATE1
  • STORE_ID
MBRGRPDESC
  • DESCRIPTION
  • DISPLAYNAME
  • LANGUAGE_ID
  • MBRGRP_ID
MSALESVW
  • GROSSSALES
  • STOREENT_ID
  • TOTALORDERS
MSGARCHIVE
  • MESSAGE
  • MESSAGEINDEX
  • MSGARCHIVE_ID
  • STOREENT_ID
  • TRANSPORT_ID
MSGSTORE
  • EXPIRY
  • STOREENT_ID
  • TRANSPORT_ID
MUSRTRVW
  • DISTINCTUSERS
  • STOREENT_ID
  • TOTALSESSIONS
  • TOTALVISITS
NOTIFY
  • MSGTYPE_ID
  • NOTIFICATIONID
  • NOTIFY_ID
  • TRANSPORTATTNAME
  • TRANSPORTATTVALUE
  • TRANSPORT_ID
OICOMPLIST
  • SUPPLIERDATA
  • SUPPLIERPARTNUMBER
ORDERITEMS
  • AVAILQUANTITY
  • SUPPLIERDATA
  • SUPPLIERPARTNUMBER
ORDERS
  • COMMENTS
  • NOTIFICATIONID
ORDISTAT
  • ORDSTAT_ID
  • SUPPLIERDATA
ORDQUOTREL
  • CHILDSTORE_ID
  • CHILD_ID
  • DISPLAYSEQUENCE
  • FLAGS
  • ORDQUOTREL_ID
  • PARENT_ID
  • RELTYPE
  • TIMEOUTTIME
  • TRADING_ID
ORDRELEASE
  • EXTORDNUM
  • EXTREF
ORDSTAT
  • OSCODE
ORGENTITY
  • STATUS
PATTRVALUE
  • ATTACHMENT_ID
  • CHANGEABLE
  • CORRELATIONGROUP
  • MANDATORY
  • ORDERITEMS_ID
  • RFQPROD_ID
  • RFQRSPPROD_ID
PROFILE
  • ARCHIVEMSG
PSETADJMNT
  • ADJUSTMENT
  • PRECEDENCE
  • PRODUCTSET_ID
  • TERMCOND_ID
  • TYPE
QUEUE
  • ALLCSR
  • NAME
  • QUEUE_ID
  • STORE_ID
QUEUECSR
  • CSR_ID
  • QUEUE_ID
QUEUEDESC
  • DESCRIPTION
  • DISPLAYNAME
  • LANGUAGE_ID
  • QUEUE_ID
REFKEYS
  • FKCOLUMN_NAME
  • FKTABLE_NAME
  • TABLENAME
REPDLVRY
  • FISCALYEAR
  • FORMAT
  • FREQUENCY
  • STOREENT_ID
RFQ
  • RULETYPE
  • TRACKNUMBER
  • VERSIONFLAGS
RFQCATEGRY
  • NAME
  • RFQCATEGRY_ID
  • RFQ_ID
RFQPROD
  • CHANGEABLE
  • RFQCATEGRY_ID
  • RFQPRODNAME
  • RFQPROD_ID
RFQRSP
  • ACCEPTACTION
  • PREVRSP
  • STORE_ID
  • VERSIONFLAGS
RFQRSPEVAL
  • EVALRESULT
  • REMARKS
  • RFQRSPEVAL_ID
  • RFQRSPPROD_ID
  • RFQRSP_ID
  • RFQ_ID
  • UPDATETIME
RFQRSPPARL
  • RFQPATTRVALUE_ID
  • RFQRSPPARL_ID
  • RFQRSPPATTRVAL_ID
  • RFQRSP_ID
  • RFQ_ID
RFQRSPPROD
  • RFQPROD_ID
  • RFQRSPPROD_ID
  • SKUGENFLAG
RFQTARGET
  • RFQ_ID
  • STORE_ID
ROLEDESC
  • DESCRIPTION
  • DISPLAYNAME
  • LANGUAGE_ID
  • ROLE_ID
SCHACTIVE
  • SCSPRIORITY
SEGMENTATION
  • LAST_UPDATED
  • LAST_UPDATED_ID
  • NUM_ATTRIBUTES
  • NUM_ITEMS
  • NUM_SEGMENTS
  • PER_ID
  • SAMPLE_SIZE_PCT
  • SEGMENTATION_ALG
  • SEGMENTATION_DESC
  • SEGMENTATION_ID
  • SEGMENTATION_NAME
  • SEGMENTATION_TYPE
SEGMENTATION_REF
  • LANGUAGE_ID
  • LAST_UPDATED
  • LAST_UPDATED_ID
  • SEGMENTATION_DESC
  • SEGMENTATION_ID
SHPMODCLCD
  • CALCODE_ID
  • SHIPMODE_ID
  • SHPMODCLCD_ID
  • STORE_ID
  • TRADING_ID
SMTPHOST
  • HOST
  • PORT
STADDRESS
  • URL
STCGRYDESC
  • DESCRIPTION
  • DISPLAYNAME
  • LANGUAGE_ID
  • STORECGRY_ID
STORE
  • CRTDBYCNTR_ID
  • LASTUPDATESTATUS
STOREENT
  • MARKFORDELETE
STOREREL
  • RELATEDSTORE_ID
  • SEQUENCE
  • STATE
  • STORE_ID
  • STRELTYP_ID
STRELTYP
  • NAME
  • STRELTYP_ID
STRELTYPDS
  • DESCRIPTION
  • LANGUAGE_ID
  • STRELTYP_ID
TCATTR
  • SEQUENCE
  • TERMCOND_ID
  • TRADING_ID
  • TYPE
  • XMLDEFINITION
TMPBOLIST
  • BASEITEM_ID
  • FORCEBACKORDER
  • MARKFORDELETE
TMPCMPLIST
  • BASEITEM_ID
  • MARKFORDELETE
TRADING
  • REFTRADING_ID
USRTRAFFIC
  • CATENTRY_ID
  • CATGROUP_ID
  • STORE_ID
WSALESVW
  • GROSSSALES
  • STOREENT_ID
  • TOTALORDERS
WUSRTRVW
  • DISTINCTUSERS
  • STOREENT_ID
  • TOTALSESSIONS
  • TOTALVISITS

Tables removed since version 5.4

CACHLOG
This table records changes to product or category information in the database. CACHLOG is populated by the cachlog triggers. Refer to Cache invalidation triggers from the online help.
CATALGDESC
Reserved for IBM internal use.
CATEGRYREL
Reserved for IBM internal use.
ENUMDESC
This table contains language specific descriptions of enumerations used in the Catalog Manager Web Editor.
PRICEDESC
Reserved for IBM internal use.
PRODDSTATR
Reserved for IBM internal use.
WCCATDESC
Reserved for IBM internal use.
WCCATEGORY
Reserved for IBM internal use.
WCCATRTCAT
Reserved for IBM internal use.
WCCATSTORS
Reserved for IBM internal use.
WCMLANG
This table contains the current installed Language for the Catalog Manager Web Editor.
WCPRDCTPID
Reserved for IBM internal use.
WCPRDPRICE
Reserved for IBM internal use.
WCPRODCATP
Reserved for IBM internal use.
WCPRODDESC
Reserved for IBM internal use.
WCPRODSHIP
Reserved for IBM internal use.
WCPRODUCT
Reserved for IBM internal use.

Deprecated tables

ACCCMDGRP
This table defines all commands and views that require access control and categorize them into different groups. This table is deprecated and is provided for backward compatibility only.
ACCCMDTYPE
This table defines all the valid ACCCMDTYPE_ID that can be used in the ACCCMDGRP table. This table is deprecated and is provided for backward compatibility only.
ACCCUSTEXC
This table excludes a command or view from a customer group for a specific owner. This table is deprecated and is provided for backward compatibility only.
ACCMBRGRP
This table is used to assign an administrative user to various access groups for a particular store owner. This table is deprecated and is provided for backward compatibility only.
ACORGPOL
This table enables an organization to be exempted from one or more of the template policies. This table is deprecated and is provided for backward compatibility only.
CATENTATTR
This table holds descriptive Attributes for Catalog Entries. These Attributes are only used for descriptive purposes, and not SKU resolution. This table is deprecated and is provided for backward compatibility only.
MGPTRDPSCN
A row in this table associates a TradingPositionContainer with a MemberGroup. Task commands can use this relationship to restrict use of the TradingPositionContainer to members of certain MemberGroups. (See also the STOREMBRGP table.) This table is deprecated and is provided for backward compatibility only. Use TermAndCondition objects and Participant objects to limit the use of TradingPositionContainers to certain customers. See the TRADING, TERMCOND, and PARTICIPNT tables for current information.
PAYMTHD
This table is a site-wide table that lists all the Cashier profiles used in the store group. Each profile has a unique integer ID and a name. This table is deprecated and is provided for backward compatibility only.
PAYMTHDDSC
This table is a site-wide table that contains a short description of the Cashier profiles in each of the supported language. This table is deprecated and is provided for backward compatibility only.
PAYMTHDSUP
This table lists all the profiles supported by the Store or StoreGroup. This table is deprecated and is provided for backward compatibility only.
PKGITEMREL
This table holds the relationship between Packages, and the Items they contain. This table is deprecated and is provided for backward compatibility only.

Tables containing deprecated columns

Table name Column name Column description
ACPOLICY POLICYTYPE This field is used to distinguish between the different policy types. 2 is a groupable standard policy. 3 is a groupable template policy. Policies of type 2 and 3 must belong to a policy group in order to be applied to an organization. Policy types 0, 1 and null, are deprecated. Policy type 1 indicates that it is a template policy. If it is 0 or null, it is a standard policy. Policies of type 0, 1 and null are applied based on their ownership: they apply to resources owned by the policy owner.
ORGENTITY MEMBER_ID This column contains the member ID of the parent OrgEntity for this OrgEntity. This column is deprecated and is provided for backward compatibility only.
PVCBUFFER PARAMETERS Buffered name-value pairs from URL. This column is deprecated and is provided for backward compatibility only.
PVCDEVMDL MODELNAME The model name. An empty string means default for devices which have the same SESSION_TYPE. This column is deprecated and is provided for backward compatibility only.
PVCDEVSPEC SESSIONTYPE Type of carrier. For example, iMode. This column is deprecated and is provided for backward compatibility only.
STORECGRY REMARK This column is deprecated and is provided for backward compatibility only.
TAXTYPE SEQUENCE This column is deprecated and is provided for backward compatibility only. See the SEQUENCE column of the STENCALUSG table for current information.
TRADEPOSCN FLAGS This column contains the following bit flag indicating special processing to be performed by task commands that find prices for customers: 1 = restricted. To use TradingPositions in this TradingPositionContainer, the customer must be in one of the associated MemberGroups recognized by the Store as customer groups (see MGPTRDPSCN and STOREMBRGP). Use the TermAndCondition and Participant objects to limit the use of TradingPositionContainers to certain customers. See the TRADING, TERMCOND, and PARTICIPNT tables for current information. No other bit flags can be stored in this column. This column is deprecated and is provided for backward compatibility only.
USERPVCDEV DEVICEIDENTIFIER The terminal identifier of the pervasive computing device. This column is deprecated and is provided for backward compatibility only.

Tables containing changed columns

ACACGPDESC
DISPLAYNAME
ACACTDESC
DISPLAYNAME
ACATTRDESC
DISPLAYNAME
ACCOUNT
COMMENTS
ACPOLDESC
DISPLAYNAME
ACRELDESC
DISPLAYNAME
ACRELGRP
CONDITIONS
ACRESGPDES
DISPLAYNAME
ACRESGRP
CONDITIONS
ACRSCGDES
DISPLAYNAME
BASEITMDSC
LONGDESCRIPTION
BZRPENTSTG
VALUE
CALCODEDSC
LONGDESCRIPTION
CALMETHOD
DESCRIPTION
CATALOGDSC
LONGDESCRIPTION
CATCONFINF
REFERENCE
CATCONFINF
URL
CATENTDESC
AUXDESCRIPTION1
CATENTDESC
AUXDESCRIPTION2
CATENTDESC
LONGDESCRIPTION
CATENTDESC
NAME
CATGRPDESC
LONGDESCRIPTION
CLEANCONF
STATEMENT
COLLDESC
MARKETINGTEXT
CONTRACT
COMMENTS
CONTRLRULE
RULETEXT
EMSPOT
DEFAULTRULE
FFMCENTDS
DESCRIPTION
FLDOMNDESC
DESCRIPTION
FLOWDESC
DESCRIPTION
FLTRANSDSC
EVENTDESC
FLTYPEDESC
DESCRIPTION
FORUM
DESCRIPTION
FORUMMSG
ACTREMARK
FORUMMSG
MSGBODY
MBRATTRVAL
STRINGVALUE
MBRGRPCOND
CONDITIONS
PARTICIPNT
INFORMATION
PARTROLEDS
DESCRIPTION
PATTRVALUE
TERMCOND_ID
PAYSTATUS
SETSWAKEUPMSG
POLICYDESC
DESCRIPTION
POLICYDESC
LONGDESCRIPTION
REFUNDMTHD
RETURNXML
RFQPROD
CATENTRY_ID
RFQRSPPROD
CATENTRY_ID
ROLE
DESCRIPTION
RULECLSREG
NAME
SHIPMODE
TRACKINGURL
STAGLOG
STGLDCOLS
STORE
STATUS
STORECGRY
NAME
STOREENTDS
DESCRIPTION
TCDESC
LONGDESCRIPTION
TCSUBTYPDS
DESCRIPTION
TFTRENGDSC
LONGDESCRIPTION
TRDDESC
LONGDESCRIPTION
TRDPSCNXML
XMLDEFINITION
TRDTYPEDSC
DESCRIPTION
USERPROF
DESCRIPTION
USRTRAFFIC
PREVURL
USRTRAFFIC
QUERYSTRING
USRTRAFFIC
REFURL