Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: interesting UTC problem in rollforward process

    Hi Everyone

    I'm trying to familiarize myself with the process of backup, restore and follforward.

    I did an offline backup and online tablespace backup(I set logretain= recovery), my recovery history file looks as following:
    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
    -- --- ------------------ ---- --- ------------ ------------ --------------
    B D 20030121211900001 F D S0000004.LOG S0000004.LOG
    ----------------------------------------------------------------------------
    Contains 2 tablespace(s):

    00001 SYSCATSPACE
    00002 USERSPACE1
    ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE OFFLINE
    Start Time: 20030121211900
    End Time: 20030121211925
    ----------------------------------------------------------------------------
    00009 Location: /home/db2inst1


    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
    -- --- ------------------ ---- --- ------------ ------------ --------------
    B P 20030121212023001 N D S0000004.LOG S0000005.LOG
    ----------------------------------------------------------------------------
    Contains 1 tablespace(s):

    00001 USERSPACE1
    ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE ONLINE
    Start Time: 20030121212023
    End Time: 20030121212045
    ----------------------------------------------------------------------------
    00010 Location: /home/db2inst1

    Then I take the step to restore the database as following:
    1.restore database sample tablespace (userspace1) online taken at 20030121212023
    DB20000I The RESTORE DATABASE command completed successfully.
    2.db2 => rollforward database sample query status

    Rollforward Status

    Input database alias = sample
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = TBS pending
    Next log file to be read = S0000009.LOG
    Log files processed = -
    Last committed transaction = 2003-01-22-02.26.29.000000
    3.rollforward database sample to 2003-01-22-02.26.29.000000 tablespace (userspace1) online

    Rollforward Status

    Input database alias = sample
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = TBS working
    Next log file to be read = S0000008.LOG
    Log files processed = -
    Last committed transaction = 2003-01-22-02.26.29.000000

    DB20000I The ROLLFORWARD command completed successfully.
    4.rollforward database sample stop ( Then I get the error message as following)
    SQL4906N The list of table space names specified is an incomplete set for the
    rollforward operation.

    The thing confused me is if I issue 'rollforward database sample complete', it going smooth. I check the IBM document, it said the command rolls-forward time is UTC, but how can you decide the UTC time of a UOW, Let's say if we have two transaction after we did the backup image, how can we rollforward to each point of time commit the UOW.( I don't think we have that kind of luck always rollforwd to end of logs ;-)

    Cat

  2. #2
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: interesting UTC problem in rollforward process

    Hy Cat,

    If you want to discover the UTC time of a UOW there´s a note on the Data Recovery and High Availability Guide and Reference for V7:

    "This value is specified as a time stamp, a 7-part character
    string that identifies a combined date and time. The format is
    yyyy-mm-dd-hh.mm.ss.nnnnnn (year, month, day, hour, minutes,
    seconds, microseconds), expressed in Coordinated Universal
    Time (UTC). UTC helps to avoid having the same time stamp
    associated with different logs (because of a change in time
    associated with daylight savings time, for example). The time
    stamp in a backup image is based on the local time at which
    the backup operation started. The CURRENT TIMEZONE
    special register specifies the difference between UTC and local
    time at the application server. The difference is represented by
    a time duration (a decimal number in which the first two
    digits represent the number of hours, the next two digits
    represent the number of minutes, and the last two digits
    represent the number of seconds). Subtracting CURRENT
    TIMEZONE from a local time converts that local time to UTC."

    But for this you must know the time of the UOW.

    If you don´t know the time at which the "commit" of the UOW happened, you can use the "Event Monitor" and monitor each transaction to see the time of the "commits" and use it on the rollfowrd. But for this you should have some storage space available on your system.

    If you need more information about backups and recovery, take a look at the Data Recovery and High Availability Guide and Reference for V7.

    It wasn´t quite clear to me if you wanted the Time of the UOW or the conversion between your local time to UTC. So I hope it helps,

    Fernando
    ps: I assumed that you were using DB2 V7

    Originally posted by misscat
    Hi Everyone

    I'm trying to familiarize myself with the process of backup, restore and follforward.

    I did an offline backup and online tablespace backup(I set logretain= recovery), my recovery history file looks as following:
    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
    -- --- ------------------ ---- --- ------------ ------------ --------------
    B D 20030121211900001 F D S0000004.LOG S0000004.LOG
    ----------------------------------------------------------------------------
    Contains 2 tablespace(s):

    00001 SYSCATSPACE
    00002 USERSPACE1
    ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE OFFLINE
    Start Time: 20030121211900
    End Time: 20030121211925
    ----------------------------------------------------------------------------
    00009 Location: /home/db2inst1


    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
    -- --- ------------------ ---- --- ------------ ------------ --------------
    B P 20030121212023001 N D S0000004.LOG S0000005.LOG
    ----------------------------------------------------------------------------
    Contains 1 tablespace(s):

    00001 USERSPACE1
    ----------------------------------------------------------------------------
    Comment: DB2 BACKUP SAMPLE ONLINE
    Start Time: 20030121212023
    End Time: 20030121212045
    ----------------------------------------------------------------------------
    00010 Location: /home/db2inst1

    Then I take the step to restore the database as following:
    1.restore database sample tablespace (userspace1) online taken at 20030121212023
    DB20000I The RESTORE DATABASE command completed successfully.
    2.db2 => rollforward database sample query status

    Rollforward Status

    Input database alias = sample
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = TBS pending
    Next log file to be read = S0000009.LOG
    Log files processed = -
    Last committed transaction = 2003-01-22-02.26.29.000000
    3.rollforward database sample to 2003-01-22-02.26.29.000000 tablespace (userspace1) online

    Rollforward Status

    Input database alias = sample
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = TBS working
    Next log file to be read = S0000008.LOG
    Log files processed = -
    Last committed transaction = 2003-01-22-02.26.29.000000

    DB20000I The ROLLFORWARD command completed successfully.
    4.rollforward database sample stop ( Then I get the error message as following)
    SQL4906N The list of table space names specified is an incomplete set for the
    rollforward operation.

    The thing confused me is if I issue 'rollforward database sample complete', it going smooth. I check the IBM document, it said the command rolls-forward time is UTC, but how can you decide the UTC time of a UOW, Let's say if we have two transaction after we did the backup image, how can we rollforward to each point of time commit the UOW.( I don't think we have that kind of luck always rollforwd to end of logs ;-)

    Cat

  3. #3
    Join Date
    Jan 2003
    Posts
    4

    pls help

    Hi Fernando

    Thanks for your reply. Your assumption is right. I am using DB2 UDB EE on AIX 4.3.3. Actually I have both time of the UOW and the conversion between local time to UTC problem. DB2 rollforward is as following:

    db2 rollforward database sample to 2003-01-22-11.55.31.000000 and
    stop tablespace (userspace1) online

    That time should be UTC, but how can I find out the UTC time of the
    transaction. I tried to create an event monitor of statements and
    output it. it looks like following:

    17) Statement Event ...
    Appl Handle: 78
    Appl Id: *LOCAL.DB2.030122185651
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Execute Immediate
    Section : 203
    Creator : NULLID
    Package : SQLC2D01
    Cursor :
    Cursor was blocking: FALSE
    Text : update staff set name='ingrid' where id=350
    -------------------------------------------
    Start Time: 01-22-2003 14:07:54.558383
    Stop Time: 01-22-2003 14:07:54.560824
    Exec Time: 0.002441 seconds
    Number of Agents created: 1
    User CPU: 0.000000 seconds
    System CPU: 0.000000 seconds
    Fetch Count: 0
    Sorts: 0
    Total sort time: 0
    Sort overflows: 0
    Rows read: 2
    Rows written: 1
    Internal rows deleted: 0
    Internal rows updated: 0
    Internal rows inserted: 0
    SQLCA:
    sqlcode: 0
    sqlstate: 00000
    24) Statement Event ...
    Appl Handle: 78
    Appl Id: *LOCAL.DB2.030122185651
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Execute Immediate
    Section : 203
    Creator : NULLID
    Package : SQLC2D01
    Cursor :
    Cursor was blocking: FALSE
    Text : update staff set name='lily' where id=350
    -------------------------------------------
    Start Time: 01-22-2003 14:08:15.034283
    Stop Time: 01-22-2003 14:08:15.036549
    Exec Time: 0.002266 seconds
    Number of Agents created: 1
    User CPU: 0.000000 seconds
    System CPU: 0.000000 seconds
    Fetch Count: 0
    Sorts: 0
    Total sort time: 0
    Sort overflows: 0
    Rows read: 2
    Rows written: 1
    Internal rows deleted: 0
    Internal rows updated: 0
    Internal rows inserted: 0
    SQLCA:
    sqlcode: 0
    sqlstate: 00000

    Let's say I try to rollforward to the stats just after the first
    update transaction, but the start time in the output file looks like local time not UTC

    db2 rollforward database sample to (transaction) UTC time and stop
    tablespace (userspace1) online

    How to define the UTC time of that transaction? Thanks a lot

    Cat

  4. #4
    Join Date
    Jan 2003
    Posts
    4

    pls help

    Hi Fernando

    Thanks for your reply. Your assumption is right. I am using DB2 UDB EE on AIX 4.3.3. Actually I have both time of the UOW and the conversion between local time to UTC problem. DB2 rollforward is as following:

    db2 rollforward database sample to 2003-01-22-11.55.31.000000 and
    stop tablespace (userspace1) online

    That time should be UTC, but how can I find out the UTC time of the
    transaction. I tried to create an event monitor of statements and
    output it. it looks like following:

    17) Statement Event ...
    Appl Handle: 78
    Appl Id: *LOCAL.DB2.030122185651
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Execute Immediate
    Section : 203
    Creator : NULLID
    Package : SQLC2D01
    Cursor :
    Cursor was blocking: FALSE
    Text : update staff set name='ingrid' where id=350
    -------------------------------------------
    Start Time: 01-22-2003 14:07:54.558383
    Stop Time: 01-22-2003 14:07:54.560824
    Exec Time: 0.002441 seconds
    Number of Agents created: 1
    User CPU: 0.000000 seconds
    System CPU: 0.000000 seconds
    Fetch Count: 0
    Sorts: 0
    Total sort time: 0
    Sort overflows: 0
    Rows read: 2
    Rows written: 1
    Internal rows deleted: 0
    Internal rows updated: 0
    Internal rows inserted: 0
    SQLCA:
    sqlcode: 0
    sqlstate: 00000
    24) Statement Event ...
    Appl Handle: 78
    Appl Id: *LOCAL.DB2.030122185651
    Appl Seq number: 0001

    Record is the result of a flush: FALSE
    -------------------------------------------
    Type : Dynamic
    Operation: Execute Immediate
    Section : 203
    Creator : NULLID
    Package : SQLC2D01
    Cursor :
    Cursor was blocking: FALSE
    Text : update staff set name='lily' where id=350
    -------------------------------------------
    Start Time: 01-22-2003 14:08:15.034283
    Stop Time: 01-22-2003 14:08:15.036549
    Exec Time: 0.002266 seconds
    Number of Agents created: 1
    User CPU: 0.000000 seconds
    System CPU: 0.000000 seconds
    Fetch Count: 0
    Sorts: 0
    Total sort time: 0
    Sort overflows: 0
    Rows read: 2
    Rows written: 1
    Internal rows deleted: 0
    Internal rows updated: 0
    Internal rows inserted: 0
    SQLCA:
    sqlcode: 0
    sqlstate: 00000

    Let's say I try to rollforward to the stats just after the first
    update transaction, but the start time in the output file looks like local time not UTC

    db2 rollforward database sample to (transaction) UTC time and stop
    tablespace (userspace1) online

    How to define the UTC time of that transaction? Thanks a lot

    Cat

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: pls help

    I thought Fernando meant a UOW event monitor ... Though I have never used, I believe this will show the start and end time of a UOW ...

    Using the time displayed and your Time Zone, UTC can be calculated ..

    In layman's terms, UTC is GMT ..]


    HTH

    Sathyaram

  6. #6
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: pls help

    That was what I meant,
    Althought with the information gathered from the monitor you used I believe you can calculate the UTC time from the transaction.

    just use the command "db2 values current timezone" and it will give you your timezone with a plus(+) or a minus(-) sign. Don´t discard the signs and use the UOW time, either stop or start, minus the timezone.

    For instance, if your timezone is -20000, (this is the return of the current timezone statement), being the first 2 digits the diference in hours, the next 2 digits the minutes and the last two digits the seconds. So it means I´m two hours behind UTC, or as Sathyaram posted GMT. And using the UOW stop time:
    01-22-2003 14:07:54.560824. Just do the math

    01-22-2003 14:07:54.560824 - (-20000) = 01-22-2003 16:07:54.560824

    so the UTC Transaction time is 01-22-2003 16:07:54.560824.

    HTH,
    Fernando

    Originally posted by sathyaram_s
    I thought Fernando meant a UOW event monitor ... Though I have never used, I believe this will show the start and end time of a UOW ...

    Using the time displayed and your Time Zone, UTC can be calculated ..

    In layman's terms, UTC is GMT ..]


    HTH

    Sathyaram

  7. #7
    Join Date
    Jan 2003
    Posts
    4

    GOt it

    Hi

    I got it. Actually, I user the following command to caculate the UTC

    select current timezone from sysibm.sysdummy1

    and get the result as -50000.

    Now I know the UTC time of the transaction and can rollforward every point of time. WOW!!!! Thanks eveyone for helping.

    catcatcat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •