Topic
5 replies Latest Post - ‏2013-06-07T06:18:31Z by Ulli_Zu
Ulli_Zu
Ulli_Zu
46 Posts
ACCEPTED ANSWER

Pinned topic Check HADR standby database

‏2013-05-31T07:42:40Z |

Hi there,

How can I check the status of the tables of a HADR standby-database?

I'm not looking for the "peer"-status in a snapshot, because that only says something about the log-transferral, not if the transaction logging is applied correct in the standby-database. Furthermore - I can't set the readable standby feature, as I don't have TSA implemented.

Best regards.

  • AngocA
    AngocA
    20 Posts
    ACCEPTED ANSWER

    Re: Check HADR standby database

    ‏2013-06-01T01:39:00Z  in response to Ulli_Zu

    Hi,

    With HADR, you do not check the tables in the standby database. You just check if the logs are being applied. You can know that via the command db2pd -d dbName - hadr.

    You get the log sequence number, and you can execute this in both machines. With this information you will know how different are the databases. If they are in the same LSN, it means both databases are equal*. Also you can check the current transaction log in both database.

    Reads in the standby database is activated by modifying the instance. However, checking the table contents is not a valid way to be sure the databases are equal. Tables are not the only object in the database, routines, sequences, are also objects that could be modified.

    * If you perform loads in the primary, make sure they are replied in the standby via a shared disk. Load operations are not replied in the standby via logs.

  • LukeNumrych
    LukeNumrych
    55 Posts
    ACCEPTED ANSWER

    Re: Check HADR standby database

    ‏2013-06-01T08:25:22Z  in response to Ulli_Zu

    Here is an example of db2pd -hadr output:

    db2pd -db xxx -hadr
     
    Database Partition 0 -- Database xxx -- Standby -- Up 193 days 16:56:03 -- Date 2013-06-01-03.09.54.530000
     
    HADR Information:
    Role       State                SyncMode   HeartBeatsMissed   LogGapRunAvg (bytes)
    Standby    Peer                 Nearsync   0                  0
     
    ConnectStatus ConnectTime                           Timeout
    Connected     Fri May 24 15:34:33 2013 (1369427673) 120
     
    LocalHost                                LocalService
    10.x.x.1                                 51001
     
    RemoteHost                               RemoteService      RemoteInstance
    10.x.x.2                                 51001              DB2
     
    PrimaryFile  PrimaryPg  PrimaryLSN
    S0000271.LOG 0          0x0000000088800010
     
    StandByFile  StandByPg  StandByLSN         StandByRcvBufUsed
    S0000271.LOG 0          0x0000000088800010 0%

     

    As you can see, PrimaryFile, PrimaryPg, and PrimaryLSN match StandByFile, StandByPg, and StandByLSN, which means that every transaction logged on the primary server has also been logged on the standby server.

    A quick way to verify the status of HADR is, well, the "State" section above... Peer means standby takes log pages as soon as primary flushes them to disk, and this is the desired state for a HADR pair.  Here is a description of the other states, as well as the transitions between the states.

     

  • Ulli_Zu
    Ulli_Zu
    46 Posts
    ACCEPTED ANSWER

    Re: Check HADR standby database

    ‏2013-06-03T08:00:52Z  in response to Ulli_Zu

    Hi,

    Thanks for replying, but I can't reproduce the LSN as the informational parameter on the issue.

    See the following shell-log, in which I create a non-logged table - which seems to replicate without a problem - and where the LNS seems to remain equal:

    $ db2 get snapshot for database on hadrtest | grep -i -p "hadr status" | grep -v -e Remote -e Local
    HADR Status
      Role                   = Primary
      State                  = Peer
      Synchronization mode   = Sync
      Connection status      = Connected, 04/24/2013 09:25:15.394298
      Heartbeats missed      = 0
      timeout(seconds)       = 120
      Primary log position(file, page, LSN) = S0000090.LOG, 199, 00000000173F7A6F
      Standby log position(file, page, LSN) = S0000090.LOG, 199, 00000000173F7A6F
      Log gap running average(bytes) = 473

    $ db2 "create table testt (sometext char(30)) not logged initially"
    DB20000I  The SQL command completed successfully.
    $ db2 "insert into testt values ('sometext')"
    DB20000I  The SQL command completed successfully.
    $ db2 "insert into testt values ('sometext')"
    DB20000I  The SQL command completed successfully.
    $ db2 get snapshot for database on hadrtest | grep -i -p "hadr status" | grep -v -e Remote -e Local
    HADR Status
      Role                   = Primary
      State                  = Peer
      Synchronization mode   = Sync
      Connection status      = Connected, 04/24/2013 09:25:15.394298
      Heartbeats missed      = 0
      timeout(seconds)       = 120
      Primary log position(file, page, LSN) = S0000090.LOG, 200, 00000000173F8DD4
      Standby log position(file, page, LSN) = S0000090.LOG, 200, 00000000173F8DD4
      Log gap running average(bytes) = 470

  • nivanov1
    nivanov1
    231 Posts
    ACCEPTED ANSWER

    Re: Check HADR standby database

    ‏2013-06-06T15:53:12Z  in response to Ulli_Zu

    NOT LOGGED INITIALLY remains in effect within a single unit of work. Since the DB2 CLP has autocommit turned on by default, and you didn't seem to turn it off, the CREATE TABLE statement had been immediately committed and the NOT LOGGED INITIALLY state had been turned off before you issued your INSERTs. 

  • Ulli_Zu
    Ulli_Zu
    46 Posts
    ACCEPTED ANSWER

    Re: Check HADR standby database

    ‏2013-06-07T06:18:31Z  in response to Ulli_Zu

    But it is still a non-logged transaction, for which IBM explicitly mentiones it won't be replicated. A select on the table after a takeover illustrates that:

     

    db2 "select * from testt"

    SOMETEXT
    ------------------------------
    SQL1477N  For table "DB2INST1.TESTT" an object "9" in table space "5" cannot be
    accessed.  SQLSTATE=55019

     

    So my question remains - how can I check the table-states of a standby-database?