Stagingprop – the basics

You may also like...

7 Responses

  1. Gerry says:

    Hi there,

    I’m a db2 DBA working on a site with WC and StagingProp for the first time. We’re having a problem where the db2 (9.7 FP 0) transaction logs become saturated and it looks like Stagingprop is the culpret. When the WC guys look at the logs there is an error with a row (much like you have decribed in this article) but it seems that perhaps Stagingprop is not rolling back the UOW when it hits this error instead leaving the UOW open until the logs get saturated :/

    I could obviously increase the number of secondary logs (or indeed make them infdinite) IF it’s just the case that SP needs a lot of logs, but it already has 76 logs available (1.5GB) and looking at the archive logs it seems the prop only usually uses 3-4 at most so I’m thinking it actually an SP crash which isn’t rolling back for some reason.

    Gosh that was longwinded, apologies… but have you come across this behavior before?

    Cheers,

    Gerry

    • Ember Crooks says:

      Is this a particularly large stagingprop? You can always take a look at the number of rows in the staglog table on the staging database with a STGPROCESSED of 0 to get an idea of how many rows it is trying to move. This would give you an idea if 1.5 GB is reasonable on log files, or if you need to increase it. I’ve seen exceedingly large (millions of rows) stagingprops cause problems before. When invoking stagingprop, are you using a value for -trasaction? My preference is to specify that to increase the frequency of commits, but I have clients who don’t like to do it because it means that only part of the stagingprop succeeds, leaving the production-role database in an “inconsistent” state.

      My favorite starting points for log files are a LOGFILSIZ of 10,000 with about 12 primary and 50 secondary log files. So that’s about 2.8 GB. I fairly frequently find myself bumping logsecond up to the max, though, and occasionally sometimes even end up increasing LOGFILSIZ, so it is certainly possible that more space is needed in the log files – very dependent on your database size and the amount of data you’re trying to stageprop.

      Personally, assuming I had disk space, I’d bump up the size of the log files before going down the road of digging into other issues at the Stagingprop level.

  2. rohit says:

    Dam good Crooks i am searching about stageprop bit dint found any thing like this…
    Read your other article…
    Dam good..
    Any article on order flow as well??

  3. Brian says:

    Hi Ember,

    I know this is an old post but it’s still useful where I am working. Have you ever seen an issue where you have several pending changes for a store (merchant level publishing) but need to mark a few of them as published because they aren’t wanted in Prod, then later you need to publish the records prior (date wise) to the marked published ones? I’m finding that they remain unprocessed and aren’t picked up by the staging prop job.

    Any shared experience would be appreciated.

    • Ember Crooks says:

      Yes. I generally pick a number I won’t use for anything else, and update stgprocessed to that number (101 or something). Then when I want them to go, I switch stgprocessed for those rows back to 0. This works if there are no dependencies that are missed.

  1. May 1, 2014

    […] and not in a production-role database. You’ll find many more details on it in my entries on stagingprop and on dbclean for […]

Leave a Reply

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