DB2 for Commerce IDs

So the easy thing to do on Commerce build is to use your DB2 instance owner for everything related to the database. But that’s not really the best choice. It’s almost always the choice I see when a DBA was not involved with the architecture or build of a Commerce system. You’ll notice in the instance XML that there is an option to specify a separate DBUser and DBAName. Generally, it is best to have different values for these. The DBAName is the instance owner, but the DBUser name is a dedicated user for Commerce to use to connect to the database.

Continue reading »

Build Tip – DB2 Backups

Schedule DB2 backups starting immediately after instance creation – the most common time that restores are needed is during the build process. Of course the most common cause for needing a restore is human error, but that’s something that is important to protect against during build.

Continue reading »

Build Tip – Database Naming

Just a quick tip. When building Commerce environments, select a different database name for each environment (Stage, Prod, etc), even if they are on different database servers. This will help you ensure that you(or developers or whoever else accesses the databases) never do something in the wrong environment. It adds a bit of complexity when you restore between different environments, but nothing that can’t be cured in the restore command. It will also help you know what database backups come from what environments if you end up with backup images in the same directory.

Continue reading »

DBClean – Junk OrderItems

So I’m starting out my series on DBClean and data pruning items with the one that I have seen cause the most immediate severe performance impact. I’ve seen this one take a site to its knees within two months of go-live.

Continue reading »

The role of the DBA in supporting WebSphere Commerce

I have generally been a bit disappointed on the information coming out of IBM on supporting Commerce databases. I also meet clients who don’t believe they need a dba or that they can hire someone out of college or with minimal database experience to fill such a role. In my opinion, any reasonably sized Commerce implementation needs a DBA, and unless that DBA has years of experience with DB2 (or Oracle as the case may be), and at least a year of experience supporting Commerce databases specifically, then it’s going to need to be a full-time dedicated DBA.

Continue reading »

Data Movement Options for Commerce Databases – Synchronizing Data Between Commerce Environments

So one of the most common questions I get is about moving data between homogenous Commerce databases. Our standard setup includes 4 environments – Dev, QA, Stage, and Prod. Dev/QA are a stagingprop pair, Stage/Prod are a stagingprop pair, and Prod usually has HADR. So with 4 environments, they can get out of sync. For the most part, business users are making changes on staging, and stagingprop is used to move those changes to prod. Therefore the catalog between staging and prod is kept in sync, and we know right away if stagingprop fails and it gets out of sync. Some data we never expect in an environment other than prod – and in fact do not want it to end up in the lower environments where many developers have unrestricted access, and security is not as tight. That data includes orders, users, payment information, etc.

Continue reading »

Never do runstats on volatile tables

Ok, so everyone will tell you that it doesn’t matter if you do runstats on volatile tables. The volatile flag is supposed to tell DB2 that the statistics are absolutely not correct, and heavily bias db2 to use indexes over table scans. Commerce has 8 tables in Commerce 6 (9 in Commerce 7) that are marked as volatile. At least two of them I would argue do not meet the definition for volatile, but that’s another post. We have seen clients with severe performance issues when they did runstats on volatile tables, so our scripts do not do runstats on volatile tables and advise all of our customers not to as well.

Continue reading »

DB2 and Transparent LDAP

Ok, so I know I’m in the middle of a multi-part post on Data Movement between Commerce databases (and I will get back to that), but I found this and had to share it because I’m so excited DB2 has finally added support for it.

Continue reading »