db2flsn - Find log sequence number command

The db2flsn command returns the name of the file in a log stream that contains the log record identified by a specified log sequence number (LSN) or log record identifier (LRI).

Authorization

Instance owner

Command syntax

Read syntax diagramSkip visual syntax diagramdb2flsn-q -dbdbname-pathdbpath Log paths input_LSNinput_LRIRange of log files-lsnrangelog_numberRange of log files-lrirangelog_numberRange of log files -kspasswordpassword-online-offline-kspassargfd: file_descriptorfilename: file_name-ksprompt
Log paths
Read syntax diagramSkip visual syntax diagram-logpathactive_logpath-logpath2secondary_logpath
Range of log files
Read syntax diagramSkip visual syntax diagram-startloglog_number-endloglog_number

Command parameters

-q
Specifies that only the log file name be printed. No error or warning messages will be printed, and status can be determined only through the return code. Valid error codes are:
  • -100 Invalid input
  • -101 Cannot open LFH file
  • -102 Failed to read LFH file
  • -103 Invalid LFH
  • -106 Invalid database
  • -108 The LSN or LRI is before the earliest log file db2flsn could open.
  • -109 The LSN or LRI is before the log specified by -startlog parameter.
  • -110 The LSN or LRI could not be found as no log files could be opened.
  • -120 The LSN or LRI could not be found due to an error. Try using -lsnrange option.
Other valid return codes are:
  • 0 Successful execution
  • 100 Warning, the LSN or LRI could be in a later log file, returning the last file that db2flsn could open.
  • 101 Warning, LSN or LRI could be in a later log file, returning the file specified by -endlog parameter. To find the exact file, use a larger value for the -endlog parameter.
-db dbname
Specifies the database name which you want to investigate.
-path dbpath
Specifies the full path to the directory where the LFH files, SQLOGCTL.LFH.1 and its mirror copy SQLOGCTL.LFH.2, reside.
input_LSN
Specifies a 16 character string that represents an 8 byte hexadecimal value with leading zeros.
input_LRI
Specifies a 33 character string where the first character is either "1" or "2" for the lriType value for the LRI, followed 16 character string for the 8 byte hexadecimal value for the part1 field for the LRI, followed by 16 character strings for the 8 byte hexadecimal value for the part2 field for the LRI.
-lsnrange
Specifies a log number or a range of log file numbers. The LSN value range is returned for each of the log numbers provided. For this option, log files must be present and readable.
-lrirange
Specifies a log number or a range of log file numbers. The LRI value range is returned for each of the log numbers provided. For this option, log files must be present and readable.
log_number
Specifies a number that represents a log file number, for example, 52 is the log_number for log file S0000052.LOG.
-logpath active_logpath
Specifies the active log path. If not provided, the database active log path is used.
-logpath2 secondary_logpath2
Specifies the secondary or temporary log path. To use log files from archive, use a temporary directory for retrieval. That temporary path can then be as -logpath2 parameter, so that db2flsn can read the specified logs. If logpath2 is not provided, the database mirror log path is used when available.
-startlog log_number
If log_number is provided, db2flsn does not search for log files before log_number.
-endlog log_number
If log_number is provided, db2flsn does not search for log files after log_number.
-kspassword password
Specifies the password to use when opening the keystore.
-kspassword password
Specifies the password to use when opening the keystore.
-online
Specifies the database is online so the tool will open log files in a way that will optimize access for better write performance to the log file. This may negatively impact read performance for this tool. This option should be used when the database is online. This is the default option.
-offline
Specifies the database is offline so the tool will open log files in a way that will optimize access for better read performance which may negatively affect the performance of the database when writing to the log files. This option should only be used when the database is offline.
-kspassarg fd:file_descriptor | filename:file_name
Specifies the keystore password arguments. The file_descriptor parameter specifies a file descriptor that identifies an open and readable file or pipe that contains the password to use. The file_name parameter specifies the name of the file that contains the password to use.
-ksprompt
Specifies that the user is to be prompted for a password.

Usage notes

  • To know more about log sequence number (LSN) or log record identifier (LRI) concepts, see Log record header.
  • If -db and -path are not specified, db2flsn takes the current directory as database path where the LFH files (SQLOGCTL.LFH.1 and SQLOGCTL.LFH.2) are located. db2flsn also requires access to the GLFH files (SQLOGCTL.GLFH.1 and SQLOGCTL.GLFH.2), which are opened either from the path specified by the -path parameter, or from their default location for the given database.
  • The db2flsn tool tries to open the history file from either the directory provided by -path parameter or the default path where a history file is located for the given database. The history file contains the starting LSN for log files for databases using log archiving. In such cases, db2flsn looks in the history file when it cannot open an already archived log file. A database is using log archiving if it is configured with the logarchmeth1 or logarchmeth2 configuration parameters set to a value other than OFF or LOGRETAIN.
  • This tool works with both recoverable and non-recoverable (circular logging) databases. A database is recoverable if it is configured with the logarchmeth1 or logarchmeth2 configuration parameters set to a value other than OFF.

Examples

Finding an LSN:

db2flsn 000000BF0030
Given LSN is contained in log file S0000002.LOG

db2flsn -q 000000BF0030
S0000002.LOG

db2flsn -db flsntest 0000000000FA0000
Given LSN is contained in log file S0000002.LOG

db2flsn -q -db flsntest 0000000000FA0000
S0000002.LOG

db2flsn -path /db2/NODE0000/SQL00001 0000000000FA4368
Given LSN is contained in log file S0000002.LOG


Using -lsnrange option:

db2flsn -lsnrange -startlog 20 -endlog 27
S0000020.LOG: has LSN range 0000000000023B3D to 0000000000023E0A
S0000021.LOG: has LSN range 0000000000023E0B to 00000000000240D8
S0000022.LOG: is not empty, but has no log records starting within its boundaries.
S0000023.LOG: has LSN range 00000000000240D9 to 00000000000243B9
S0000024.LOG: is unused/blank.
S0000025.LOG: is unused/blank.
S0000026.LOG: could not be opened or was invalid. Check file permissions.
S0000027.LOG: could not be opened or was invalid. Check file permissions.

Warning messages when an LSN could not be found, but it cannot be in 
an earlier log file:

db2flsn 00000000000243D7
Input LSN 00000000000243D7 is within or after log file S0000023.LOG:

S0000023.LOG: Log file starts with LSN 00000000000243BA.
S0000024.LOG: Log file could not be opened or was invalid/unused.

Log paths used:
(./LOGSTREAM0000) and
().

If this is not the end of the log stream, retrieve more log files into a 
temporary directory (to avoid overwriting active log files) and re-run 
db2flsn tool with -logpath2 option to use the log files from that temporary 
directory as well. Check access permissions on log files to make sure db2flsn 
can open necessary log files.

To see the first LSN of a log file, use the -lsnrange option:
db2flsn -lsnrange <log file number> -logpath2 <temp log dir> 

To see the first LSN of a set of log files, use: 
db2flsn -lsnrange -logpath2 <temp log dir> \
-startlog <first log> -endlog <end log> 

db2flsn -q 00000000000243D7 
S0000023.LOG 
=> Return code is: 100   (Warning, the LSN could be in a later log file, 
returning the last file that db2flsn could open). 

db2flsn  00000000000243D7 -endlog 23 
Input LSN 00000000000243D7 is after log file S0000023.LOG. 
This log file has LSN range 00000000000243BA - 00000000000243D6. 
To find the exact log file, try a larger value for the -endlog parameter. 

Log paths used: 
(./LOGSTREAM0000)  and 
(). 

To see the first LSN of a log file, use the -lsnrange option: 
db2flsn -lsnrange <log file number> -logpath2 <temp log dir>  

To see the first LSN of a set of log files, use: 
db2flsn -lsnrange -logpath2 <temp log dir> \     
-startlog <first log> -endlog <end log>

db2flsn -q 00000000000243D7 -endlog 23 
S0000023.LOG 
=> Return code is: 101 (Warning, LSN could be in a later log file, returning 
the file specified by -endlog parameter. To find the exact file, use a larger 
value for the -endlog parameter.)   

db2flsn -lsnrange 23 
S0000023.LOG: has LSN range 00000000000243BA to 00000000000243D6  
=> This shows that LSN 00000000000243D7 has not been produced yet on this log 
stream. However, when it is generated, this LSN is guaranteed that it cannot be 
in a log file before S0000023.LOG.   

Error messages, when an LSN could not be found. In this example, S0000000.LOG 
has already been archived and not found in history file either:

db2flsn  000000000000001 
Input LSN 0000000000000001 could not be found. Input LSN is before the earliest 
LSN found 0000000000021E6B:

S0000001.LOG: Log file starts with LSN 0000000000021E6B. 
S0000000.LOG: Log file could not be opened or was invalid/unused.  

Log paths used: 
(./LOGSTREAM0000) and 
(). 

Retrieve log files before S0000001.LOG into a temporary directory (to avoid 
overwriting active log files) and re-run db2flsn tool with -logpath2 option to 
include the log files from that temporary directory:  

db2flsn <LSN> -db <db> -logpath2 <temp log dir> -startlog <first log> 
   -endlog <last log>

To see the first LSN of a log file, use the -lsnrange option: 
db2flsn -lsnrange <log file number> -logpath2 <temp log dir>  

To see the first LSN of a set of log files, use: 
db2flsn -lsnrange -logpath2 <temp log dir> \         
-startlog <first log> -endlog <eng log>

db2flsn -q 000000000000001  

=> with "-q" option, no output is produced as the log file could not be found. 
Error code returned is -108 (The LSN is before the earliest log file db2flsn 
could open).

db2flsn 000000000000001 -startlog 1 
Input LSN(0000000000000001) is before log file S0000001.LOG. This log file starts 
with LSN 0000000000021E6B.  

Log paths used: 
(./LOGSTREAM0000) and 
().  

To see the first LSN of a log file, use the -lsnrange option:
db2flsn -lsnrange <log file number> -logpath2 <temp log dir>

To see the first LSN of a set of log files, use: 
db2flsn -lsnrange -logpath2 <temp log dir> \
-startlog <first log> -endlog <end log> 

db2flsn -q 000000000000001 -startlog 1

=> with "-q" option, no output is produced as the log file could not be found. 
Error code returned is -109 (The LSN is before the log specified by -startlog 
parameter.)

Using -lrirange option:

db2flsn -lrirange 0
S0000000.LOG: has LRI range 00000000000000010000000000002D65000000000003F1D0 to
00000000000000010000000000002D74000000000003F26A

db2flsn -lrirange -startlog 0 -endlog 3
S0000000.LOG: has LRI range 00000000000000010000000000002D65000000000003F1D0 to
00000000000000010000000000002D74000000000003F26A
S0000001.LOG: has LRI range 00000000000000010000000000002D75000000000003F26B to
00000000000000010000000000002D83000000000003F304
S0000002.LOG: has LRI range 00000000000000010000000000002D84000000000003F305 to
00000000000000010000000000002D97000000000003F39E
S0000003.LOG: is unused/blank.

db2flsn 010000000000002D75000000000003F1C0
Input LSN(000000000003F1C0) is before log file S0000000.LOG. This log file
starts with LSN 000000000003F1D0.

db2flsn 010000000000002D75000000000003F26F
Given LSN is in log file S0000001.LOG