Updated: The 10 WebSphere Commerce Tables You Should Be Familiar With

One of my most popular posts that’s specific to WebSphere Commerce is The 10 Commerce Tables You Should Be Familiar With. This is an updated version of that post, since the preferred method of dealing with attributes has changed. As a result, this post is only applicable to WebSphere Commerce 7 and above, and some of the content is identical between the two posts.

So there are 800+ tables in the typical WebSphere Commerce database – you’re never going to know them all. And getting a start on understanding the data model – even in the more limited way needed when you’re playing primarily the system dba role can be daunting. But developer and dba alike should at least be familiar with these 10.

Note all table details are copied from the WebSphere Commerce 7 Info Center.

MEMBER

Column Name Column Type Description
MEMBER_ID BIGINT NOT NULL ID for the member, a unique primary key for this table.
TYPE CHAR(3) NOT NULL The type of member as follows:
O = OrgEntity
U = User
G = MemberGroup
STATE INTEGER The registration approval status as follows:
0 = pending approval
1 = approved
2 = rejected
Null = the approval is not applicable
OPTCOUNTER SMALLINT Reserved for IBM internal use.

So maybe I’m just a geek, but I think of this as “one table to rule them all”. The main reason being that almost every table in the database has a column that has a foreign key to either Member or Users. Deleting a row from one of these tables easily cascades to 300 tables. Try explaining a delete from member sometime if you’re bored – the results are amusing.

USERS and MEMBER are closely related. Every USER has a single MEMBER, but not every MEMBER has a USER. USERS.USERS_ID is a foreign key to MEMBER.MEMBER_ID. Most tables reference the USERS_ID.

The Commerce Info Center tells me there are currently 99 Foreign Keys referencing the MEMBER table.

Generally, MEMBERs that do not have USERS associated with them should not be deleted – they are part of the Commerce system and not actual end-users.

I’ve got one of the pruning areas for this table documented – Guest Users – but there are others.

USERS

Column Name Column Type Description
USERS_ID BIGINT NOT NULL ID for the user member. Foreign key to MEMBER_ID in MEMBER table.
DN VARCHAR(1000) The distinguished name of the user, for example, uid=wcsadmin,o=root organization. It must be in lower case, and should not have any spaces immediately before or after the , or = symbols. When using LDAP, this value must correspond with the DN of the user in the LDAP server.
REGISTERTYPE CHAR(4) NOT NULL The user registration type. Valid values are as follows:
R – registered user
G – guest user
A – administrator
S – site administrator
The default member group called Administrators defines the list of administrative roles.
PROFILETYPE CHAR(2) Identifies whether the user has a profile, and if so, the profile type. Valid values are as follows:
Null – no profile data
C – base profile data
B – business profile data
LANGUAGE_ID INTEGER Preferred language. For a list of language components, see the LANGUAGE table. Foreign key relationship to LANGUAGE table.
FIELD1 VARCHAR(254) Customizable.
SETCCURR CHAR(3) Preferred currency in 3-character alphabetic code as per ISO 4217. This is a currency code as per ISO 4217 standards. Compare with the SHPREFERREDCURR column of the SHOPPER table provided with previous versions of WebSphere Commerce or WebSphere Commerce Suite.
FIELD3 VARCHAR(254) Customizable.
FIELD2 VARCHAR(254) Customizable.
LASTORDER TIMESTAMP The date and time that the user last placed an order at this site.
REGISTRATION TIMESTAMP The date or time that the user was registered, directly by way of UserRegistrationAdd, during synchronization from LDAP to the WebSphere Commerce database.
LASTSESSION TIMESTAMP The date and time that the user last visited the WebSphere Commerce site. Last visited means last logon to the WebSphere Commerce site.
REGISTRATIONUPDATE TIMESTAMP The date or time the user last changed registration information. This value is set during UserRegistrationAdd and UserRegistrationUpdate ResetPassword synchronization with LDAP during logon.
REGISTRATIONCANCEL TIMESTAMP Reserved for IBM internal use.
PREVLASTSESSION TIMESTAMP Reserved for IBM internal use.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
PERSONALIZATIONID VARCHAR(30) The Personalization ID associated with the user


Usually the number of USERS is within a hundred of the number of MEMBERs. Some of the system users are in here, and some don’t have entries. All of the site users are represented here (assuming you don’t use AD tie-in, I have no idea what that looks like). The USERS table has 25 Foreign Keys referencing it.

USERREG

Column Name Column Type Description
USERS_ID BIGINT NOT NULL ID for the user member. Foreign key to the USERS table.
STATUS INTEGER Allows the user to have the logon ID disabled without removing the user from the system. Valid values are as follows:

  • 1=enabled
  • 0=disabled
PLCYACCT_ID INTEGER The account policy for this user, foreign key to the PLCYACCT table.
LOGONID VARCHAR(254) NOT NULL The user logon ID.
LOGONPASSWORD BINARY The encrypted user logon password. Compare with the SHLPSWD column of the SHOPPER table provided with previous versions of WebSphere Commerce or WebSphere Commerce Suite.
PASSWORDEXPIRED INTEGER Specifies whether or not the user password has expired. Valid values are 0 (not expired) or 1 (expired).
CHALLENGEQUESTION VARCHAR(254) Challenge question for confirmation of the user identity. Compare with the SCHAQUE column of the SHOPPER table provided with previous versions of WebSphere Commerce or WebSphere Commerce Suite.
CHALLENGEANSWER VARCHAR(254) Answer to challenge question. Compare with the SHCHAANS column of the SHOPPER table provided with previous versions of WebSphere Commerce or WebSphere Commerce Suite.
TIMEOUT BIGINT NOT NULL DEFAULT -1 Time for which the user cannot log in after failed attempts to log in.
PASSWORDRETRIES INTEGER DEFAULT 0 The number of times consecutively the password is entered incorrectly.
SALT VARCHAR(254) The value that is appended to the password before hashing.
PASSWORDCREATION TIMESTAMP The last time the password for the user was created or updated.
PASSWORDINVALID TIMESTAMP Timestamp of the last failed attempt to log in.
OPTCOUNTER SMALLINT Reserved for IBM internal use.

The USERREG table is one of the places you can connect identifying data with the less-informative sequential ids used in the USERS and MEMBER tables. The LOGONID column usually represents the email address of the user, and passwords are stored here in an encrypted format. You can reset the configadmin password here manually (by also using the encryptPW function that Commerce provides). As a side note, for other IDs, you should not have to reset passwords for other users – that should be done through the Commerce tooling.

ORDERS

Column Name Column Type Description
ORDERS_ID BIGINT NOT NULL Generated unique key.
ORMORDER CHAR(30) A merchant-assigned order reference number, if any.
ORGENTITY_ID BIGINT The immediate parent organization ID of the creator.
TOTALPRODUCT DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.TOTALPRODUCT for the OrderItems in the Order.
TOTALTAX DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.TAXAMOUNT for the OrderItems in the Order.
TOTALSHIPPING DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.SHIPCHARGE for the OrderItems in the Order.
TOTALTAXSHIPPING DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.SHIPTAXAMOUNT for the OrderItems in the Order.
DESCRIPTION VARCHAR(254) A mnemonic description of the order, entered by the customer, suitable for display to the customer.
STOREENT_ID INTEGER NOT NULL The store entity the order is part of. This is normally a store unless STATUS is Q, in which case it is normally a store group.
CURRENCY CHAR(10) The currency for monetary amounts associated with this order. This is the currency code according to ISO 4217 standards.
LOCKED CHAR(1) Reserved for IBM internal use.
TIMEPLACED TIMESTAMP The time this order was processed by the OrderProcess command.
LASTUPDATE TIMESTAMP The time this order was most recently updated.
SEQUENCE DOUBLE NOT NULL DEFAULT 0 Can be used by a user interface to control the sequence of orders in a list.
STATUS VARCHAR(3) The status of the order. This is similar to order states. For specific values of status and their descriptions, refer to the order states page, but omit states ‘Y’ and ‘Z’.
MEMBER_ID BIGINT NOT NULL The customer that placed the order.
FIELD1 INTEGER Customizable.
ADDRESS_ID BIGINT This column is deprecated. In versions 5.6.1 and earlier, this column contained the billing address. In version 6 and later, the billing address is part of payment data and is stored in PPCEXTDATA table as encrypted data.
FIELD2 DECIMAL (20,5) Customizable.
PROVIDERORDERNUM INTEGER Reserved for IBM internal use.
SHIPASCOMPLETE CHAR(1) NOT NULL DEFAULT ‘Y’ Reserved for IBM internal use.
FIELD3 VARCHAR(254) Customizable.
TOTALADJUSTMENT DECIMAL (20,5) DEFAULT 0 The sum of ORDERITEMS.TOTALADJUSTMENT for the order items in the order. This column also includes all kinds of shipping charge adjustments like discount, coupon, shipping adjustment and surcharge.
ORDCHNLTYP_ID BIGINT Reserved for IBM internal use.
COMMENTS VARCHAR(254) Comments from the customer.
NOTIFICATIONID BIGINT Notification identifier referring to the rows in the NOTIFY table that store notification attributes. These attributes override the defaults for notifications related to this order.
TYPE CHAR(3) For an order, TYPE indicates whether it is a regular order (ORD), private requisition list order (PRL), shareable requisition list order (SRL), standing order (STD), quotation order (QUT), quote (QOT), profile order (QUK), recurring order (REC), or subscription (SUB).
OPTCOUNTER SMALLINT Reserved for IBM internal use.
EDITOR_ID BIGINT Stores the ID of the person editing the order.
BUSCHN_ID INTEGER The Business Channel ID of the order.
SOURCEID BIGINT This field indicates the sources from which this order came, if any. This field is relevant for orders that were generated from a previously saved quote, for example. In that situation, the QuoteId would be saved here.
EXPIREDATE TIMESTAMP This entry is only relevant when the row refers to a quote, type “QOT”. This field then indicates the expiration date for that quote.
BLOCKED SMALLINT DEFAULT 0 Indicates if this order has a block placed against it or not.
OPSYSTEM_ID INTEGER This field indicates the Order Processing system for the order.
TRANSFERSTATUS SMALLINT Order transferring status: 0, it is in the initial status. 1, it is in the transferring status. 2, it is in the transferred status. null, the same as 0.
BUYERPO_ID BIGINT The Buyer PO Number Id related to an order

As the name implies, this table holds orders placed in the database. It also includes shopping carts (orders that have not been submitted yet). Note especially the STATUS column – this can tell you where in the process the order is – submitted, completed, shipped, etc. There are a limited set of values and you should understand what they mean in your database.

The total $ is stored here, which is nice when you need to look for $ of orders during certain periods or find the largest orders by $.

Note that many of the columns here reference other tables to pull in the details – 9 of the columns are Foreign Keys to other tables.

ORDERITEMS

Column Name Column Type Description
ORDERITEMS_ID BIGINT NOT NULL Generated unique key.
STOREENT_ID INTEGER NOT NULL The store entity the order (this order item is part of) is part of. This is normally a store unless STATUS is Q, in which case it is normally a store group.
ORDERS_ID BIGINT NOT NULL The order of which this order item is part.
TERMCOND_ID BIGINT The TermAndCondition, if known, that determined the price for this order item.
TRADING_ID BIGINT The TradingAgreement, if known, that determines the TermAndCondition objects (including how the price is determined) that apply to this order item.
ITEMSPC_ID BIGINT The specified item to be allocated from available inventory and shipped to the customer.
CATENTRY_ID BIGINT The catalog entry, if any, of the product being purchased.
PARTNUM VARCHAR(64) The part number of the catalog entry(CATENTRY.PARTNUMBER) for the product.
SHIPMODE_ID INTEGER The shipping mode, if still known.
FFMCENTER_ID INTEGER The fulfillment center, if known, from which the product will ship.
MEMBER_ID BIGINT NOT NULL The customer of the order item (which is the same as the customer of the order).
ADDRESS_ID BIGINT The shipping address, if any, for this order item.
ALLOCADDRESS_ID BIGINT The shipping address used when inventory for this order item was allocated or backordered.
PRICE DECIMAL (20,5) The price for the nominal quantity of the product (CATENTSHIP.NOMINALQUANTITY).
LINEITEMTYPE CHAR(4) If specified, indicates the type of the order item.ALT = the order item represents an alternative item (might not be exactly what the customer requested).
STATUS CHAR(1) NOT NULL The status for the order item. It may not be the same as the status in the order.
OUTPUTQ_ID BIGINT Reserved for IBM internal use.
INVENTORYSTATUS CHAR(4) NOT NULL DEFAULT ‘NALC’ The allocation status of inventory for this order item:NALCInventory is not allocated nor on back-order.BOInventory is on back-order.

ALLC

Inventory is allocated.

FUL

Inventory has been released for fulfillment.

AVL

Inventory is available.

LASTCREATE TIMESTAMP The time this order item was created.
LASTUPDATE TIMESTAMP The most recent time this order item was updated. Changing inventory allocation related information does not cause this timestamp to be updated (refer to the LASTALLOCUPDATE column).
FULFILLMENTSTATUS CHAR(4) NOT NULL DEFAULT ‘INT’ The fulfillment status of the order item:
INT = not yet released for fulfillment.
OUT = released for fulfillment.
SHIP = shipment confirmed.
HOLD = a temporary status between INT and OUT status.
LASTALLOCUPDATE TIMESTAMP The most recent time inventory was checked (for unallocated order items), allocated, or backordered, for this order item.
OFFER_ID BIGINT The offer, if any, and if it still exists, from which PRICE was obtained.
TIMERELEASED TIMESTAMP The time this order item was released for fulfillment.
TIMESHIPPED TIMESTAMP The time this order item was manifested for shipment.
CURRENCY CHAR(10) The currency of order item monetary amounts other than BASEPRICE. This is the same as the currency of the order, ORDERS.CURRENCY. This is a currency code according to ISO 4217 standards.
COMMENTS VARCHAR(254) Comments from the customer, such as a greeting for a gift.
TOTALPRODUCT DECIMAL (20,5) DEFAULT 0 PRICE times QUANTITY.
QUANTITY DOUBLE NOT NULL The result of multiplying QUANTITY by CATENTSHIP.NOMINALQUANTITY must be a multiple of CATENTSHIP.QUANTITYMULTIPLE. And it represents the actual quantity being purchased, in the unit of measurement specified by CATENTSHIP.QUANTITYMEASURE.
TAXAMOUNT DECIMAL (20,5) The total sales taxes associated with this order item, in the currency specified by CURRENCY.
TOTALADJUSTMENT DECIMAL (20,5) DEFAULT 0 The total of the monetary amounts of the order item adjustments for this order item, in the currency specified by CURRENCY. This column also includes all kinds of shipping charge adjustments like discount, coupon, shipping adjustment and surcharge.
SHIPTAXAMOUNT DECIMAL (20,5) The total shipping taxes associated with this order item, in the currency specified by CURRENCY.
ESTAVAILTIME TIMESTAMP An estimate of when sufficient inventory will be available to fulfill this order item. This estimate does not include the shipping offset.
FIELD1 INTEGER Customizable.
DESCRIPTION VARCHAR(254) A mnemonic description of the order Item, suitable for display to the customer. This field is usually NULL when CATENTRY_ID is not NULL, since in that case the CatalogEntry description can be displayed.
FIELD2 VARCHAR(254) Customizable.
ALLOCATIONGROUP BIGINT Reserved for IBM internal use.
SHIPCHARGE DECIMAL (20,5) The base shipping charge associated with the order item, in the currency specified by CURRENCY, it is the shipping charge before any adjustments. The shipping charge adjustment will be persisted in the ORDERITEMS.TOTALADJUSTMENT column with other adjustments including discount and surcharge. The total shipping charge is the sum of base shipping charge in ORDERITEMS.SHIPCHARGE column and the shipping charge adjustment in ORDERITEMS.TOTALADJUSTMENT column.
BASEPRICE DECIMAL (20,5) If PRICE was converted from a currency different from the order item currency, BASEPRICE is the price that was converted to determine the PRICE.
BASECURRENCY CHAR(3) The currency of BASEPRICE.
TRACKNUMBER VARCHAR(64) Reserved for IBM internal use.
TRACKDATE TIMESTAMP Reserved for IBM internal use.
PREPAREFLAGS INTEGER NOT NULL DEFAULT 0 Contains bit flags indicating special processing to be performed by the OrderPrepare command.PREPAREFLAGS column
CORRELATIONGROUP BIGINT Normally this is the same as ORDERITEMS_ID, except:1. When an order item is split by the AllocateInventory task command, the newly created OrderItem inherits the CORRELATIONGROUP value from the original order item.2. when the PREPAREFLAGS column indicates “quotation”, the OrderItem inherits the CORRELATIONGROUP value from the corresponding OrderItem in the parent order.
PROMISEDAVAILTIME TIMESTAMP When an order is placed (using the OrderProcess command), this would be set to EstAvailTime. After that it would normally not be updated, although a CSR could manually update this to reflect a verbal commitment made to the customer.
SHIPPINGOFFSET INTEGER NOT NULL DEFAULT 0 An estimate of how many seconds it will take to ship this item once the order is placed and inventory has been allocated.
NEEDEDQUANTITY INTEGER NOT NULL DEFAULT 0 Quantity needed for fulfillment. If CATENTRY is not NULL, this is QUANTITY times CATENTSHIP.NOMINALQUANTITY, converted from CATENTSHIP.QUANTITYMEASURE to BASEITEM.QUANTITYMEASURE, divided by BASEITEM.QUANTITYMULTIPLE and rounded to the nearest integer.
ALLOCQUANTITY INTEGER NOT NULL DEFAULT 0 Quantity allocated or backordered for this order item. The quantity in BASEITEM.QUANTITYMEASURE units can be calculated by multiplying this value by BASEITEM.QUANTITYMULTIPLE, for the base item of the specified item indicated by ITEMSPC_ID.
ALLOCFFMC_ID INTEGER The fulfillment center from which inventory for this order item is allocated or backordered.
ORDRELEASENUM INTEGER The associated order release, if any.
CONFIGURATIONID VARCHAR(128) The identifier that is provided by an external product configurator. This identifier represents a list of order item components that are stored in the OICOMPLIST table.
SUPPLIERDATA VARCHAR(254) Opaque to WebSphere Commerce. This attribute can be returned with a quotation, and sent when an order is placed on an external system. For example, it could contain a supplier distribution center ID.
SUPPLIERPARTNUMBER VARCHAR(254) The supplier part number, if known. Suitable for display to the customer.
AVAILQUANTITY INTEGER If specified, indicates the quantity available for purchase.
ISEXPEDITED CHAR(1) NOT NULL DEFAULT ‘N’ Indicates that the item should receive expedited handling in fulfillment. Y indicates that it should receive expedited handling. N indicates that it should not (this is the default).
OPTCOUNTER SMALLINT Reserved for IBM internal use.
REQUESTEDSHIPDATE TIMESTAMP The ship date that has been requested for the order item. When no date (null) is specified, the ship date will be whenever the item is available to be shipped. When a date is specified, the ship date will be no sooner than the requested date. This is because a backorder could delay the ship date beyond the requested date. See the definition of ALLOCATIONOFFSET and MAXFOOFFSET in the STORE table.
TIECODE SMALLINT TieCode used for items that are shipping at the same time (Ship Together).

For every item on an order, there is a row in the ORDERITEMS table. This is a common structure in OLTP databases, but may not be as familiar to those used to working with DW/DSS databases. Much like orders, there are many Foreign Keys pulling in data from other tables – 14 columns reference other tables. The CATENTRY_ID can help connect the ORDERITEM to an item in the catalog.

Sometimes you’ll need to look for ORDERS with the most items – ORDERS with large numbers of items can cause performance issues. For example, you might have a monitor that tries to add an item to a cart. If you haven’t also implemented proper data pruning, that monitoring process could be adding items to the same ORDER over and over and never checking out – this can cause site-wide performance issues.

Pruning junk ORDERITEMS is also one of the more critical data pruning areas.

STAGLOG

Column Name Column Type Description
STGRFNBR BIGINT NOT NULL The reference number for the staging row.
STGSTMP TIMESTAMP NOT NULL The time stamp.
STGTABLE CHAR(18) NOT NULL The name of the table with staged changes.
STGOP CHAR(1) NOT NULL The change operator:IinsertUupdate

D

delete

STGMENBRNAME CHAR(32) Reserved for IBM internal use.
STGMENBR INTEGER Indicates whether the table is a:
0 = site table or 1 = merchant table.
STGPKEYNAME CHAR(32) The name of the primary key.
STGPKEY BIGINT The value of the primary key.
STGKEY1NAME CHAR(32) First column name for the unique index of the table defined in STGTABLE. For example, if STGTABLE is ‘catentry’, then STGKEY1NAME is ‘partnumber’
STGKEY2NAME CHAR(32) Second column name for the unique index of the table defined in STGTABLE. For example, if STGTABLE is ‘catentry’, then STGKEY1NAME is ‘member_id’.
STGKEY3NAME CHAR(32) Third column name for the unique index of the table defined in STGTABLE.
STGKEY4NAME CHAR(32) Fourth column name for the unique index of the table defined in STGTABLE.
STGKEY5NAME CHAR(32) Fifth column name for the unique index of the table defined in STGTABLE.
STGOKEY1 BIGINT Old value for first column of the unique index.
STGOKEY2 BIGINT Old value for second column of the unique index.
STGOKEY3 VARCHAR(254) Old value for third column of the unique index.
STGOKEY4 BIGINT Old value for fourth column of the unique index.
STGOKEY5 VARCHAR(254) Old value for fifth column of the unique index.
STGNKEY1 BIGINT New value for first column of the unique index.
STGNKEY2 BIGINT New value for second column of the unique index.
STGNKEY3 VARCHAR(254) New value for third column of the unique index.
STGNKEY4 BIGINT New value for fourth column of the unique index.
STGNKEY5 VARCHAR(254) New value for fifth column of the unique index.
STGPROCESSED INTEGER DEFAULT 0 Indicate if the corresponding record has been processed by StagingProp. 0 = not processed, 1 = processed.
STGRESERVED1 INTEGER DEFAULT 0 Reserved for IBM internal use.
STGLDPTH VARCHAR(254) Reserved for IBM internal use.
STGLDCOLS VARCHAR(4000) Reserved for IBM internal use.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
STGFILTER INTEGER This column is used for Staging by filter value. When a filter value is specified in StagingProp parameters, all the corresponding STAGLOG records with this STGFILTER value matching the filter value will be processed.

The STAGLOG table holds all of the changes captured by stagingprop to be copied from the staging database to the production database. It is only populated in a staging-role database and not in a production-role database. You’ll find many more details on it in my entries on stagingprop and on dbclean for staglog.

CATENTRY

Column Name Column Type Description
CATENTRY_ID BIGINT NOT NULL The internal reference number of the catalog entry.
MEMBER_ID BIGINT NOT NULL The reference number that identifies the owner of the catalog entry. Along with the PARTNUMBER, these columns are a unique index.
ITEMSPC_ID BIGINT The specified item that this catalog entry relates to. This column should only be populated for catalog entries that are of type “ItemBean”, “PackageBean”, or “DynamicKitBean”. Specified items are used for fulfillment.
CATENTTYPE_ID CHAR(16) NOT NULL Identifies the type of catalog entry. Foreign key to the CATENTTYPE table. The supported default types are: ProductBean, ItemBean, PackageBean, BundleBean and DynamicKitBean.
PARTNUMBER VARCHAR(64) NOT NULL The reference number that identifies the part number of the catalog entry. Along with the MEMBER_ID, these columns are a unique index.
MFPARTNUMBER VARCHAR(64) The part number used by the manufacturer to identify this catalog entry.
MFNAME VARCHAR(64) The name of the manufacturer of this catalog entry.
MARKFORDELETE INTEGER NOT NULL Indicates if this catalog entry has been marked for deletion:
0 = No.
1 = Yes.
URL VARCHAR(254) The URL to this catalog entry, which can be used as a download URL for soft goods.
FIELD1 INTEGER Customizable.
FIELD2 INTEGER Customizable.
LASTUPDATE TIMESTAMP Indicates the last time the catalog entry was updated.
FIELD3 DECIMAL (20,5) Customizable.
ONSPECIAL INTEGER This flag identifies if this catalog entry is on special.
ONAUCTION INTEGER This flag identifies if this catalog entry is on auction.
FIELD4 VARCHAR(254) Customizable.
FIELD5 VARCHAR(254) Customizable.
BUYABLE INTEGER Indicates whether this catalog entry can be purchased individually: 1=yes and 0=no.
OID VARCHAR(64) Reserved for IBM internal use.
BASEITEM_ID BIGINT The base item to which this catalog entry relates. This column should only be populated for catalog entries that are of type ProductBean, PackageBean or DynamicKitBean. Base items are used for fulfillment.
STATE CHAR(1) DEFAULT ‘1’ Reserved for IBM internal use.
STARTDATE TIMESTAMP The date when this catalog entry is introduced. This column is for your interpretation and information only.
ENDDATE TIMESTAMP The date when this catalog entry is withdrawn. This column is for your interpretation and information only.
RANK DOUBLE Reserved for IBM internal use.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
AVAILABILITYDATE TIMESTAMP The date that the product is available to customers for purchase. This column is for your interpretation and information only.
LASTORDERDATE TIMESTAMP The final date that the product is available for order. This is the latest date a customer can order the product. This column is for your interpretation and information only.
ENDOFSERVICEDATE TIMESTAMP The date when the product is no longer in service. For example, this date can be the date that a software manufacturer stops providing upgrades for a version of their product. This column is for your interpretation and information only.
DISCONTINUEDATE TIMESTAMP The date when the product is discontinued. For example, this date can be the date the manufacturer stops producing the product, or the date your store stops selling the product. It is recommended that this column be used consistently to avoid confusion. This column is for your interpretation and information only.

This is one of the master tables in the catalog. And when I say catalog, I mean the list of items the site/store sells, not the DB2 System Catalog. There are plenty of others in this data model, and you may get familiar with them depending on the work needed.

This is where things start to differ from my previous version of this post

ATTR

Column Name Column Type Description
ATTR_ID BIGINT NOT NULL The unique identifier for this attribute.
IDENTIFIER VARCHAR(254) NOT NULL An external identifier for the attibute
ATTRTYPE_ID CHAR(16) NOT NULL The attribute type. For example, INTEGER, STRING, or FLOAT.
ATTRDICT_ID BIGINT The reference number of the attribute dictionary to which this attribute belongs. Null for a localattribute.
STOREENT_ID INTEGER NOT NULL The reference number of the store entity to which this attribute belongs.
SEQUENCE DOUBLE NOT NULL DEFAULT 0 The display order of attributes in an attribute group or in the root of the attribute dictionary.
DISPLAYABLE INTEGER This flag identifies if this attribute is displayable on the store front. This flag can be used to temporarily hide an attribute on the store front. It can also used for attributes that belong to a web crawler (such as Google’s web crawling robot), that is not for display to customer. A value of 1 or null indicates that the attribute is displayable, while a value of 0 indicates that the attribute is not displayable.
SEARCHABLE INTEGER This flag identifies if this attribute can be searched. Searchable attributes can be indexed to search engine. A value of 1 indicates that theattribute is searchable, while a value of 0 or null indicates that the attribute is not searchable.
COMPARABLE INTEGER This flag identifies if this attribute can be used for comparison. A value of 1 indicates that theattribute can be used for comparison, while a value of 0 or null indicates that the attribute cannot be used for comparison. For example,attribute “Auto On/Off” is marked as comparable for all coffee makers. If a shopper selects thisattribute on the store front page, only coffee makers that have the “Auto On/Off” feature will be displayed.
FIELD1 INTEGER Customizable field.
FIELD2 INTEGER Customizable field.
FIELD3 VARCHAR(254) Customizable field.
OPTCOUNTER SMALLINT NOT NULL DEFAULT 0 Reserved IBM Internal Use.
ATTRUSAGE INTEGER No ATTRUSAGE external description assigned yet

This table holds attribute DICTIONARY attributes. So while the old attribute table had a row for every combination of catentry_id and a particular attribute, now we instead have attr as a list of all possible attributes, but without associating them with a particular catentry. In DBA-speak, it is a fact table. This makes attr much smaller than attribute was in most cases. Possible values are stored in ATTRVAL, and the table CATENTRYATTR is used to relate a catentry to it’s corresponding ATTRs and ATTRVALs. This is a better model than before. I find that the CATENTRYATTR table seems to be the largest of this set of tables now in terms of cardinality, which is great considering the maximum row width is only about 300, as compared to close to 1,300 for the old ATTRIBUTE table.

KEYS

Column Name Column Type Description
KEYS_ID INTEGER NOT NULL The internally generated unique ID.
TABLENAME VARCHAR(18) NOT NULL The table into which the next key value is inserted.
COLUMNNAME VARCHAR(18) NOT NULL The column into which the next key value is inserted.
COUNTER BIGINT NOT NULL The next starting key value to be fetched by the server from the database.
PREFETCHSIZE BIGINT DEFAULT 20 The size of the block of keys prefetched by the server.
LOWERBOUND BIGINT DEFAULT 0 The lower bound of the range of key values available for this table.
UPPERBOUND BIGINT DEFAULT 2147483648 The upper bound of the range of valid key values for this table.
OPTCOUNTER SMALLINT Reserved for IBM internal use.

Nearly every Primary Key in a Commerce database is a generated key. Up through Commerce 6, all of those values came from this table – each table with a generated key had a row in KEYS, and Commerce would query the KEYS table to get the right value on insert. In Commerce 7, they started making more significant use of sequences, but the KEYS table is still used for some things.

Often as data is loaded into a new Commerce database, different ranges are chosen to avoid key conflicts. There are also some interesting articles out there on key-splitting and what scenarios you might use that in. I have a few developers who call me every time they get SQL0803, and tell me to check the keys entries for the tables they’re working with. It’s rarely the problem, but it is possible for the value in the keys table to get behind the values in the actual table and end up with PK conflicts as a result. The solution if that happens is simply to increase the value of the COUNTER for that table.

SITE

Column Name Column Type Description
DATABASEVENDOR VARCHAR(32) Reserved for IBM internal use.
EDITION VARCHAR(16) Reserved for IBM internal use.
VERSION SMALLINT Reserved for IBM internal use.
RELEASE SMALLINT Reserved for IBM internal use.
MOD SMALLINT Reserved for IBM internal use.
FIXPACK SMALLINT Reserved for IBM internal use.
COMPNAME VARCHAR(32) NOT NULL DEFAULT ‘BASE’ Reserved IBM Internal Use
OPTCOUNTER SMALLINT Reserved for IBM internal use.

This table is not one that Commerce itself uses a lot, but it does store the version and FixPack and Feature Pack information, so can be very useful if you somehow have a mismatch between the Commerce application and the Commerce database, or if you need to verify Commerce versions at the database level. It is not one you will generally update.

You may also like...

1 Response

  1. June 5, 2012

    […] UPDATE: An updated version of this post is available – Updated: 10 WebSphere Commerce Tables You Should Be Familiar With […]

Leave a Reply

Your email address will not be published. Required fields are marked *