DB2 10.5 for Linux, UNIX, and Windows

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


>>-db2flsn--+-----+--+- -db--dbname---+--+---------------+------>
            '- -q-'  '- -path--dbpath-'  '-| Log paths |-'   

>--+-+-input_LSN-+--+------------------------+-+---------------->
   | '-input_LRI-'  '-| Range of log files |-' |   
   +-input_LSO---------------------------------+   
   '- -lsnrange--+-log_number-------------+----'   
                 '-| Range of log files |-'        

>--+-------------------------------------+---------------------><
   +- -kspassword--password--------------+   
   +- -kspassarg--+-fd:file_descriptor-+-+   
   |              '-filename:file_name-' |   
   '- -ksprompt--------------------------'   

Log paths

|--+----------------------------+------------------------------->
   '- -logpath--primary_logpath-'   

>--+-------------------------------+----------------------------|
   '- -logpath2--secondary_logpath-'   

Range of log files

|--+------------------------+--+----------------------+---------|
   '- -startlog--log_number-'  '- -endlog--log_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 34 character string that represents a 17 byte hexadecimal value with leading zeros.

input_LSO: Specifies a character string that represents the decimal value of a log sequence offset.

-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.

log_number: Specifies a number that represents a log file number, for example, 52 is the log_number for log file S0000052.LOG.

-logpath primary_logpath: Specifies the primary log path. If not provided, the database primary 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.
-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.

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.)

Usage notes

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.