Data Movement Options for Commerce Databases – Creation of staging database during build
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.
I hope to present a series of postings to address a number of scenarios and approaches for this kind of data movement. I’m not talking in this series about data movement as in loading catalog or user data into the database for the first time, or keeping things in sync with a separate system of record.
So the most common set of goals are:
- Keep private data in production only
- Synchronize catalog and other data across environments
- Staging is the master for catalog and data that is needed in all environments
Data movement Scenarios:
Create a staging environment to match production
This is most commonly encountered during build, but can be needed after build as well to refresh the data in a staging environment to match production. Commerce intends that we create our production environment, develop there, and then move the database data back to staging using the stagincopy utility. Essentially the stagingcopy utility does these tasks:
- Exports data from the production database
- Deletes any data from the staging database
- Imports data into the staging database
- Creates the stagingprop triggers (and sequences) in the staging database
When running stagingcopy you can either have stagingcopy do all the work, or you can have it write the script and run the script yourself.
One of the biggest problems with stagingcopy is that it does all of the work in the staging database in ONE UNIT OF WORK! This means that you must have huge transaction logs to support it. We usually run with a LOGFILSIZ of 10000 and 12 primary and 50 secondary logs. For a newer database with a small or medium size, you can get by with bumping LOGSECOND up to 240 in this scenario (an online change). If you’ve got the default log file size of 1000, there is not an online change you can make to make that work. There is a limit of 255 for the combination of LOGPRIMARY and LOGSECOND. What you can do is change LOGFILSIZ and then recycle the database for the change to take effect, making sure that your filesystem has space for the log files. If you don’t have log files on a separate filesystem, you’ll also have to make sure that your filesystem can hold the files data is exported to, the data being loaded into, and the log files. Depending on the size of your catalog, you may not be able to have enough log space to support stagingcopy.
Stagingcopy also tends to end up with cryptic error messages, and I usually expect at least two iterations to get it to work – with an experienced commerce DB2 DBA sitting there waiting for it to succeed or fail. My knee-jerk estimate (when I don’t know database/catalog size) is that a stagingcopy will take 8 hours to complete, during which the target (staging) environment will be unavailable. In most situations, that allows for it to start over at least once. I’ll talk in a future post about some non-supported ways that stagingcopy can be useful.
Stagingcopy must be run from an application server (if you have application servers that are separate from your database servers). You also must use the specify the same id to stagingcopy that you use for the application to connect to your database – stagingcopy cannot handle a different schema name than the user you specify on execution. I’m not going to go through the syntax in detail – it is in the info center:
The one thing I will say is that in my experience the batchsize parameter does not work.
Stagingcopy is the supported way of doing this. But it’s not the only way we do it. IF the Commerce versions including FixPacks, Feature Packs, and Feature enablement are exactly identical(query the site table), and the Merchant Keys are also identical, then you can do a straight restore at the DB2 level from the production database to the staging database. I only recommend doing this pre-go-live or for environments where the database you’re backing up does not have any real customer or order data. If everything falls into line for this, it is immensely easier, taking usually less than 30 minutes, depending on your hardware and your catalog size. With Staging and Production on different servers, we keep the databases such that we can do a straight restore without a redirect (storage architecture is a separate topic), and do this kind of restore quickly.
Using this restore method, you also have to ensure that your staging triggers are in place after you have completed the restore. Conversely, you’ll need to make sure that the staging triggers DO NOT exist on your production database. You can query syscat.triggers like this to see if the staging triggers are there:
$db2 "select count(*) from syscat.triggers where trigname like 'STAG%' with ur" 1 ----------- 951 1 record(s) selected.
The number of triggers will vary by version and with enablement of features like CMC, but you should certainly have over 500. If you don’t have them, you can find the syntax for them on the app servers, here(on Linux, anyway):
and for Commerce 7 you’ll also need to have the sequences in place:
Note that if you have different Feature Packs, FixPacks, or features enabled, using the restore method will succeed, and the app will come up, BUT you will have inconsistencies that will likely require you to rebuild your whole commerce instance from scratch at a later time.
Next post I’ll discuss keeping a dev or qa database in sync with your stage/prod pair.