To know DB start up time in DB2 LUW after crash recovery

Hari Korrapati

To know DB start up time in DB2 LUW after crash recovery

Hi,
I have been searching in google on how to identify DB start time after crash recovery / restore from full back up in DB2 LUW. I knew how to get this info from Mainframe DB2 ( as soon as sub system get started , we can find DB2 time stamp from SSIDMSTR STC  ) and in the same way i want to know  exactly from what time DB have data after crash recovery or restore from full backup . Could any one please help me this ..

Thanks in advance... 

 

 

hAri

Greg Palgrave

RE: To know DB start up time in DB2 LUW after crash recovery
(in response to Hari Korrapati)

Hi Hari,

A couple of ways you can get the instance (equivalent to MF subsystem) startup times:

 

db2pd -d mydatabase -bufferpools | head -3

Database Partition 0 -- Database mydatabase -- Active -- Up 116 days 14:48:52 -- Date 2017-12-14-08.31.28.974523


db2 "SELECT DB2_STATUS, DB2START_TIME, LAST_RESET, LOCAL_CONS, REM_CONS_IN,
    (AGENTS_CREATED_EMPTY_POOL/AGENTS_FROM_POOL) AS AGENT_USAGE,
    DBPARTITIONNUM FROM SYSIBMADM.SNAPDBM ORDER BY DBPARTITIONNUM"

DB2_STATUS   DB2START_TIME              LAST_RESET                 LOCAL_CONS           REM_CONS_IN          AGENT_USAGE          DBPARTITIONNUM
------------ -------------------------- -------------------------- -------------------- -------------------- -------------------- --------------
ACTIVE       2017-08-19-17.41.04.398211 -                                             6                   59                    0              0

  1 record(s) selected.

 

The DB after a crash recovery will have data to the current time unless something has gone seriously wrong, in which case there will be error messages in the db2diag.log file.

 

If you do a restore, then the data will either be to the end of backup time for an offline backup,or to the last committed transaction applied in the ROLLFORWARD for an online backup.

e.g.

db2 rollforward database mydatabase to end of logs and complete

                                 Rollforward Status

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

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0003557.LOG - S0003560.LOG
 Last committed transaction             = 2015-01-21-11.31.28.000000 UTC

 

There will be messages in the db2diag.log during a restart/crash recovery - always worth checking after a restart.

e.g.

Crash Recovery is needed.
Crash recovery started. LowtranLSN 0000007C53792152 MinbuffLSN
ADM1530E Crash recovery has been initiated.
DIA2051W Forward phase of crash recovery has completed. Next LSN is
ADM1531E Crash recovery has completed successfully.
Crash recovery completed. Next LSN is 0000007C54755DC3

 

I hope that is some help.

 

Cheers

Greg

 

In Reply to Hari Korrapati:

Hi,
I have been searching in google on how to identify DB start time after crash recovery / restore from full back up in DB2 LUW. I knew how to get this info from Mainframe DB2 ( as soon as sub system get started , we can find DB2 time stamp from SSIDMSTR STC  ) and in the same way i want to know  exactly from what time DB have data after crash recovery or restore from full backup . Could any one please help me this ..

Hari Korrapati

To know DB start up time in DB2 LUW after crash recovery
(in response to Greg Palgrave)
HI Greg,

Thanks your replay with explanation.

As per command suggested by you we are knowing that when DB is started and
since how long this is up and running. May be i have to give more
clarification on my question. Exactly what i m looking is if DB got crashed
and during its restart process it do crash recovery and keeps DB in proper
state. Suppose if this DB restarted after 4 hours and as a DBA if i want to
check up to what time DB was SYNC before crash recovery , how do i come
to know ? In diag.log we can find when it crashed and restarted time frames
and not able to get upto what time it was SYNC .In mainframes in same
scenario or DR testing process we can easily find upto what time its in
SYNC by looking at below message in MSTR STC
DSNJ127I !SSID SYSTEM TIMESTAMP FOR BSDS= 17.322 21:49:06.44
If we see above message , we can easily tel that system was sync upto
21.49 at 17.322( 11/18) and this message will be generated during system
starts . I m also looking for same kind message from DB2 LUW.

Thanks in advance ..

Hari

On Thu, Dec 14, 2017 at 6:35 AM, Greg Palgrave <[login to unmask email]> wrote:

> Hi Hari,
>
> A couple of ways you can get the *instance* (equivalent to MF subsystem)
> startup times:
>
>
>
> *db2pd -d mydatabase -bufferpools | head -3*
>
> Database Partition 0 -- Database mydatabase -- Active -- Up 116 days
> 14:48:52 -- Date 2017-12-14-08.31.28.974523
>
>
> *db2 "SELECT DB2_STATUS, DB2START_TIME, LAST_RESET, LOCAL_CONS,
> REM_CONS_IN,*
> * (AGENTS_CREATED_EMPTY_POOL/AGENTS_FROM_POOL) AS AGENT_USAGE,*
> * DBPARTITIONNUM FROM SYSIBMADM.SNAPDBM ORDER BY DBPARTITIONNUM"*
>
> DB2_STATUS DB2START_TIME LAST_RESET
> LOCAL_CONS REM_CONS_IN AGENT_USAGE
> DBPARTITIONNUM
> ------------ -------------------------- --------------------------
> -------------------- -------------------- --------------------
> --------------
> ACTIVE 2017-08-19-17.41.04.398211 -
> 6 59 0 0
>
> 1 record(s) selected.
>
>
>
> The DB after a crash recovery will have data to the current time unless
> something has gone seriously wrong, in which case there will be error
> messages in the db2diag.log file.
>
>
>
> If you do a restore, then the data will either be to the end of backup
> time for an offline backup,or to the last committed transaction applied in
> the ROLLFORWARD for an online backup.
>
> e.g.
>
> *db2 rollforward database mydatabase to end of logs and complete*
>
> Rollforward Status
>
> Input database alias = mydatabase
> Number of nodes have returned status = 1
>
> Node number = 0
> Rollforward status = not pending
> Next log file to be read =
> Log files processed = S0003557.LOG - S0003560.LOG
> * Last committed transaction = 2015-01-21-11.31.28.000000 UTC*
>
>
>
> There will be messages in the db2diag.log during a restart/crash recovery
> - always worth checking after a restart.
>
> e.g.
>
> Crash Recovery is needed.
> Crash recovery started. LowtranLSN 0000007C53792152 MinbuffLSN
> ADM1530E Crash recovery has been initiated.
> DIA2051W Forward phase of crash recovery has completed. Next LSN is
> ADM1531E Crash recovery has completed successfully.
> Crash recovery completed. Next LSN is 0000007C54755DC3
>
>
>
> I hope that is some help.
>
>
>
> Cheers
>
> Greg
>
>
>
> In Reply to Hari Korrapati:
>
> Hi,
> I have been searching in google on how to identify DB start time after
> crash recovery / restore from full back up in DB2 LUW. I knew how to get
> this info from Mainframe DB2 ( as soon as sub system get started , we can
> find DB2 time stamp from SSIDMSTR STC ) and in the same way i want to know
> exactly from what time DB have data after crash recovery or restore from
> full backup . Could any one please help me this ..
>
>
> -----End Original Message-----
>

Greg Palgrave

RE: To know DB start up time in DB2 LUW after crash recovery
(in response to Hari Korrapati)

Hi Hari,

The Crash recovery will only tell you what Log Sequence Number (LSN) it is up to, but not the timestamp as far as I recall.

You can approximate it by running db2flsn to see which log contains the LSN, which might give you a time range.

e.g.

Crash recovery completed. Next LSN is 0000007DB37C8E87

db2flsn -db mydatabase  0000007DB37C8E87
Given LSN is contained in log page 137 in log file S0055080.LOG.

 

I think you could probably use the message  timestamps from the db2diag.log to better estimate the actual crash time. There may also be other error files in the dump directory that you can use to get a timestamp.

You could also look into the db2ReadLog API if you must have the timestamp info - I'm not sure exactly what detail is available though.

DB2 mainframe and DB2 LUW share many similarities, but unfortunately, also many differences.

Cheers

Greg

 

In Reply to Hari Korrapati:

In mainframes in same
scenario or DR testing process we can easily find upto what time its in
SYNC by looking at below message in MSTR STC
DSNJ127I !SSID SYSTEM TIMESTAMP FOR BSDS= 17.322 21:49:06.44
If we see above message , we can easily tel that system was sync upto
21.49 at 17.322( 11/18) and this message will be generated during system
starts . I m also looking for same kind message from DB2 LUW.

Hari Korrapati

RE: To know DB start up time in DB2 LUW after crash recovery
(in response to Greg Palgrave)

HI Greg,

Thanks for your replay ...

I too have same doubt that DB2 LUW will not give time stamp as like as mainframe DB2..Based on explanation i got convenienced and tried db2flsn command to get LRSN value  LOG number and getting below error message

Failed to open SQLOGCTL.LFH.1 and SQLOGCTL.LFH.2 in /db2/SE1/db2se1/NODE0000/SQL00001/, rc = -2045837302.

I could see that both files have required permissions.Do you have any idea about this error?  

hAri

Greg Palgrave

RE: To know DB start up time in DB2 LUW after crash recovery
(in response to Hari Korrapati)

Hi Hari,

Checking our installation, those files are rw only to the instance owner, yours may be different.

Were you using the instance owner, or another id?

Cheers

Greg

In Reply to Hari Korrapati:

Failed to open SQLOGCTL.LFH.1 and SQLOGCTL.LFH.2 in /db2/SE1/db2se1/NODE0000/SQL00001/, rc = -2045837302.

I could see that both files have required permissions.Do you have any idea about this error?  

hAri

Hari Korrapati

To know DB start up time in DB2 LUW after crash recovery
(in response to Greg Palgrave)
Hi Greg,

Im using instance owner and still getting same message.

Thanks,
Hari

On Tue, Dec 19, 2017 at 6:34 AM, Greg Palgrave <[login to unmask email]> wrote:

> Hi Hari,
>
> Checking our installation, those files are rw only to the instance owner,
> yours may be different.
>
> Were you using the instance owner, or another id?
>
> Cheers
>
> Greg
>
> In Reply to Hari Korrapati:
>
> Failed to open SQLOGCTL.LFH.1 and SQLOGCTL.LFH.2 in
> /db2/SE1/db2se1/NODE0000/SQL00001/, rc = -2045837302.
>
> I could see that both files have required permissions.Do you have any idea
> about this error?
>
> hAri
>
>
> -----End Original Message-----
>

Mark Barinstein

RE: To know DB start up time in DB2 LUW after crash recovery
(in response to Hari Korrapati)

Hi,

If you know the log file needed for recovery, you can get various timestamps from this file with the db2logscan utility.
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/DB2HADR/page/db2logscan