1

My SAP back end db is db2. Need to understand the impact on this running database (db2 under HPUX) if I change the OS date and set it to a past date please.

Thanks.

  • Why not try it in a development or test environment? – Colin 't Hart Mar 24 '15 at 9:04
2

That's an extremeley broad question. If your DB's tables use date in the primary keys you could run into duplicate problems. If you query by date range you may have duplicates or holes in your data. You may even have difficulty saying which row was written before another, depending on your design. Reproducing any previous state may become impossible e.g. "What was the customer's balance on the 1st January?" and so forth.

Really, you need to understand your application and DB design deeply, and test, test, test.

2

The DB2 for LUW reference does have a section under high availability entitled "Scenario: Changing the system clock" which goes through the implication of moving the system clock forwards or backwards. It is specifically to do with HADR (high availability), but it's worth a look anyway (it's also worth noting that it's talking about moving the clock back minutes, not days).

Moving the clock forwards is never a problem. Moving the clock backward can cause several issues:

  • As mentioned in Michael Green's answer, you'll encounter issues with keys containing dates and queries using date ranges
  • Anything using CURRENT DATE or CURRENT TIMESTAMP in a query or manipulation statement (or column default) will potentially break
  • Function resolution can break if the system date is prior to the function's creation (this is listed in the chapter linked above)
  • Mostly important, if you are using point-in-time recovery (archived logging in DB2 parlance) you will be unable to restore transactions within the overlapping time period; the transaction log obviously relies upon the system clock to associate transactions with a timestamp. In the scenario of moving the clock back there will be transactions in the log that have a timestamp earlier than transactions that occurred before them. This will likely break any recovery of such transactions (and is why the chapter linked above suggests to set the clock back during a period when "no update transactions occur in X minutes").

I would be quite surprised if a SAP installation wasn't using archived logging (even though it's not the default in DB2), so I would expect that last point would apply. You can check by looking at the database configuration (GET DB CFG FOR <dbname>) to see whether LOGARCHMETH1 is something other than OFF.

To summarize: the fact it breaks point in time recovery is particularly scary and should serve as a giant warning sign that this shouldn't be done unless absolutely required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.