Altering database objects in Commerce databases

Like many vended applications, Commerce creates the whole schema of it’s database and this is largely something you don’t mess with. There are, however, some changes that developers or administrators may make. My suggestion is that if you don’t know if a particular modification is supported by Commerce, you call IBM support and ask them. Then if you end up having issues you can reference the PMR/conversation to ensure they don’t blame you for the problems.

This part of the info center talks about what changes are allowed and not:

Essentially you can:

  1. Extend the length of a varchar column
  2. Change the data type of a column to a compatible data type (i.e. Varchar to CLOB)
  3. Add custom tables
  4. Add new Foreign keys involving custom tables
  5. Adding new indexes
  6. Add new triggers
  7. Add new Stored Procedures for custom code
In reality, I have seen all of these done. Most common are adding indexes and custom tables (and triggers and FK for those custom tables).
They don’t explicitly state it, but you can also change the tablespace structure behind the Commerce database – Commerce is blind to the tablespace structure.
You can even alter the database schema scripts to make changes before your Commerce database is created. The bulk of these scripts are in

/opt/IBM/WebSphere/CommerceServer70/schema/db2

The most common thing you might use these scripts for is dropping and creating the Staging triggers.

How to track it

If you read the info center closely, it tells you that any changes to the schema are lost on upgrade. I find that many clients will re-do their website at the same time they do a Commerce upgrade, meaning that most modifications are null and void anyway. But if you don’t, you’ll likely find modifications missing.

Naming Standards

Having your own naming standards will help you identify custom objects. Generally, custom tables start with an ‘X’. Since Commerce indexes all have either system default names (for the primary keys) or are of the format INNNNNNN where the N’s are replaced with digits, of course. For indexes I create, I like them to include the table name.

Code Repository

As with any coding, it is good to have a code repository to track your changes. Simply storing the SQL scripts you’ve used to make the changes.

Things you should specifically not do:

Never add a unique index on the Member table – this is specifically stated as unsupported

Never add a trigger without cascading deletes. If you add one with “on delete restrict”, you may not be able to delete objects (such as users or orders).

Avoid dropping base commerce indexes – they’re often needed for things that Commerce is likely to do.

Don’t over-index. Consider the impact of new indexes – in OLTP applications each index slows down an insert, so consider the importance of the positive impact indexes will have.

Don’t forget, when adding a custom table, to also add foreign keys, primary key, and indexes for expected activity. Many performance problems stem from custom tables that were added without these things in place.

You may also like...

1 Response

  1. December 12, 2011

    […] talked about 10 Commerce Tables You Should Be Familiar With, and about making alterations to Commerce objects, and about general observations about Commerce databases. The main purpose of this post is to give […]

Leave a Reply

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