IBM Support

DB2 Finding the LSN or LRI value for replication start

Technical Blog Post


Abstract

DB2 Finding the LSN or LRI value for replication start

Body

Occasionally, there are times when you need to define or change the starting point of replication for tools like IBM  Infosphere Data Replication or other 3rd party tools that use the Asynchronous Log Reader (ALR) code to parse the DB2 transaction logs.  That usually requires have the starting LSN (Log Sequence Number) or LRI (Log Record Identifier) value.  The LRI value is composed of the LFS (Log File Sequence) and the LSN values. There is a tool (db2logsForRfwd) in DB2 that uses the tablespace change history file (DB2TSCHG.HIS) in the database directory to determine what logs affect a particular tablespace.  That history file allows db2 to skip log files during tablespace rollforward operations and only rerieve the ones that affect the tablespace(s) that were restored.  One of the features of the records that db2logsForRfwd parses and outputs is the Starting and Ending LFS/LSN values for each transaction log.


By running the db2logsForRfwd command with the -all option against the DB2TSCHG.HIS file for a given database, you can see the starting and ending LRI for each db2 transaction log that has been completed:
 
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0009502.html
 
It will generate an output like this:
 
Log file number         : S0000000.LOG
Log Stream Id           : 0
First Lso In Extent     : 57455297
Last Lso in Extent      : 61629120
Lso of First Log Record : 57455297
First Lfs/Lsn in Extent : 7827/000000000004BB6F  <==== starting value for the first log record in this log
Last Lfs/Lsn in Extent  : 7854/000000000004BC88  
Archive log chain       : 0
Log Chain Info[0]       : 0/0000000000000000/0/0
Log Chain Info[1]       : 0/0000000000000000/0/0
Log Chain Info[2]       : 0/0000000000000000/1412634536/4289
Log Chain Info[3]       :
18446744073709551615/FFFFFFFFFFFFFFFF/4294967295/0
VTS When Extent Closed  : 1412697131 2014-10-07-15.52.11.000000 GMT
Backup End Time Stamp   : 0
Flags                   : 3
pID                     : 4294967295 SQLPG_IGNORE_PID
cID                     : 1412696873 2014-10-07-15.47.53.000000 GMT
Tablespace ID           : 0 2
----------------------------------------------------------
Log file number         : S0000001.LOG
Log Stream Id           : 0
First Lso In Extent     : 61629121
Last Lso in Extent      : 65802944
Lso of First Log Record : 61629121
First Lfs/Lsn in Extent : 7855/000000000004BC89  <==== starting value for the first log record in this log
Last Lfs/Lsn in Extent  : 7855/000000000004BC89
Archive log chain       : 0
Log Chain Info[0]       : 0/0000000000000000/1412634536/4289
Log Chain Info[1]       : 0/0000000000000000/0/0
Log Chain Info[2]       : 0/0000000000000000/1412634536/4289
Log Chain Info[3]       :
18446744073709551615/FFFFFFFFFFFFFFFF/4294967295/0
VTS When Extent Closed  : 1412697131 2014-10-07-15.52.11.000000 GMT
Backup End Time Stamp   : 0
Flags                   : 3
pID                     : 1412696873 2014-10-07-15.47.53.000000 GMT
cID                     : 1412696874 2014-10-07-15.47.54.000000 GMT
 
 
(...)
 
and you can use that information to formulate a starting point for your replication tool.

 

As always, let me know if you have any questions or comments.

 

Thank you,

Andy

 

Update (01/23/2018)  This will not work for pureScale and HADR databases as the DB2_COLLECT_TS_REC_INFO is set to OFF by default for those environments.

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140268