Topic
  • 4 replies
  • Latest Post - ‏2012-11-27T07:50:28Z by mwandishi
bluey01
bluey01
50 Posts

Pinned topic Restore database to different server with different date

‏2012-10-25T23:27:49Z |
DB2 9.7 on Redhat 5 linux

We have hit a strange issue related to date/time. We have created a test database and loaded some historical data into it. Lets say TESTDB1 on Server1 done today.

Now we have Server2 that has a system date/time of last month. We restore the TESTDB1 onto Server2. We then get errors on some functions (eg Select upper(name) from customer).

If we roll the date/time on Server2 to the current date, then it works ok. The issue seems to be the Create Time in syscat.functions.

So how do we restore a recently created database onto Server2 (with an old date) and to do some historical testing? I have never experienced this on other databases.
Updated on 2012-11-27T07:50:28Z at 2012-11-27T07:50:28Z by mwandishi
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Restore database to different server with different date

    ‏2012-11-06T17:23:28Z  
    It would probably help if you'd post more details about both the error you're seeing, and what it is that you're trying to accomplish. Why are you setting the clock back just to access data from an older backup image?

    Thanks.
  • bluey01
    bluey01
    50 Posts

    Re: Restore database to different server with different date

    ‏2012-11-11T23:13:42Z  
    It would probably help if you'd post more details about both the error you're seeing, and what it is that you're trying to accomplish. Why are you setting the clock back just to access data from an older backup image?

    Thanks.
    We are doing parallel testing so we are trying to emulate a Production run from a previous month and the system uses current date to process information - so we need to set the test server to a set date & time.

    Since the original post, we have discovered that we have this issue with views and UDFs and have slowly been tring to resolve them. The error was simple - - when you run a select from a view, it says the underlying table does not exist.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Restore database to different server with different date

    ‏2012-11-22T13:11:28Z  
    • bluey01
    • ‏2012-11-11T23:13:42Z
    We are doing parallel testing so we are trying to emulate a Production run from a previous month and the system uses current date to process information - so we need to set the test server to a set date & time.

    Since the original post, we have discovered that we have this issue with views and UDFs and have slowly been tring to resolve them. The error was simple - - when you run a select from a view, it says the underlying table does not exist.
    Hello,

    You might want to consider the use of temporal tables (DB2 10.1) :

    http://www.ibm.com/developerworks/data/library/techarticle/dm-1210temporaltablesdb2/index.html

    Regards

    Yves-Antoine Emmanuelli
  • mwandishi
    mwandishi
    47 Posts

    Re: Restore database to different server with different date

    ‏2012-11-27T07:50:28Z  
    • bluey01
    • ‏2012-11-11T23:13:42Z
    We are doing parallel testing so we are trying to emulate a Production run from a previous month and the system uses current date to process information - so we need to set the test server to a set date & time.

    Since the original post, we have discovered that we have this issue with views and UDFs and have slowly been tring to resolve them. The error was simple - - when you run a select from a view, it says the underlying table does not exist.
    Hi,

    Rolling the clock back is a bad idea. UDFs etc are partially resolved using a timestamp so if the clock goes back it would be as if it hasn't yet been created. The clock going forward is normal, it is what happens every time we do db2stop....wait a while...db2start or a reboot. DB2 transaction logs will be unaffected but UDFs will have resolution issues.

    Stephen