By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

DB2 & System Dates

P: n/a
People,

I have an ESE 8.2.2 database running on win2k server [sp4]. I need to
do some system testing that requires I reset the dates back a couple of
years then progressivily move it forward to current date.

The last time I tried this the database got very upset and the date
functions such as DAYOFWEEK_ISO, YEAR, WEEK, QUARTER etc got all
screwed up and failed. I subsequently had to completely drop the
database, recreate from scratch and reload all of the data. Not a
pleasant task.

Can anyone please suggest a way that I can reset the system date either
forwards or backwards without corrupting or upsetting the database.

Thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Moving dates forward isn't a problem. That's what happens when db2 is
shut down for a while then restarted.

Move date forward: Stop the instance, change the date, restart the
instance. (Stop the instance as insurance that the database is stopped.)

Moving backwards is a different issue. Recovery information is kept by
db2 that gets very unhappy when time regresses. Many, many years ago,
this was a problem in the Fall when the clocks were moved backwards at
the end of the Summer's Daylight Savings Time. I even remember
instrucions to shut down for an hour to prevent timestamp overlaps in
the logs.

The safest approach may be to export your data, change the clock (with
the instance down), define a new database, then import your data. All
clock movement from this point should be forward which should not be a
problem.

Do any of your tables have dates or timestamps in them? if so, what are
you going to do when you initially move the clock back a couple of years?

Phil Sherman
p175 wrote:
People,

I have an ESE 8.2.2 database running on win2k server [sp4]. I need to
do some system testing that requires I reset the dates back a couple of
years then progressivily move it forward to current date.

The last time I tried this the database got very upset and the date
functions such as DAYOFWEEK_ISO, YEAR, WEEK, QUARTER etc got all
screwed up and failed. I subsequently had to completely drop the
database, recreate from scratch and reload all of the data. Not a
pleasant task.

Can anyone please suggest a way that I can reset the system date either
forwards or backwards without corrupting or upsetting the database.

Thanks.

Nov 12 '05 #2

P: n/a
Phil Sherman wrote:
Moving dates forward isn't a problem. That's what happens when db2 is
shut down for a while then restarted.

Move date forward: Stop the instance, change the date, restart the
instance. (Stop the instance as insurance that the database is stopped.)

Moving backwards is a different issue. Recovery information is kept by
db2 that gets very unhappy when time regresses. Many, many years ago,
this was a problem in the Fall when the clocks were moved backwards at
the end of the Summer's Daylight Savings Time. I even remember
instrucions to shut down for an hour to prevent timestamp overlaps in
the logs.

The safest approach may be to export your data, change the clock (with
the instance down), define a new database, then import your data. All
clock movement from this point should be forward which should not be a
problem.

Do any of your tables have dates or timestamps in them? if so, what are
you going to do when you initially move the clock back a couple of years?

Phil Sherman
p175 wrote:
People,

I have an ESE 8.2.2 database running on win2k server [sp4]. I need to
do some system testing that requires I reset the dates back a couple of
years then progressivily move it forward to current date.

The last time I tried this the database got very upset and the date
functions such as DAYOFWEEK_ISO, YEAR, WEEK, QUARTER etc got all
screwed up and failed. I subsequently had to completely drop the
database, recreate from scratch and reload all of the data. Not a
pleasant task.

Can anyone please suggest a way that I can reset the system date either
forwards or backwards without corrupting or upsetting the database.

Thanks.

Can't comment on the DB2 internals, but routine resolution goes by
timestamp. If you create any procedure or function and then roll back
time you will not see the routine until "it's time" :-).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.