Troubleshooting
Problem
The database is close to reaching or has reached the maximum log sequence number (LSN). Once a database reaches the maximum LSN, it will no longer accept transactions that require log records to be written.
Symptom
As of Db2 V9.1 FP6 and Db2 V9.5 FP3, the ADM1849C message is logged to alert you that the current LSN has reached 0xF000 0000 0000. For each additional 0x0080 0000 0000 LSNs reached, the ADM1849C message is logged again. If you allow the database to reach an LSN of 0xFFFF 0000 0000, the ADM1850 message is logged and the database as marked read-only.
Earlier Db2 versions and fix packs do not log the ADM1849C or ADM1850C messages.
Cause
Individual log records in Db2 are identified by their log sequence number (LSN). An LSN represents the byte offset of the log record from the beginning of the database log. In Db2 Versions 9.1 and 9.5, an LSN is represented as a six byte number, with a maximum value of 0xFFFF FFFF FFFF. If this maximum LSN is reached, the database is marked read-only to protect your data.
Diagnosing The Problem
Before the maximum LSN is reached:
Once the LSN reaches an initial threshold of 0xF000 0000 0000, you can find the ADM1849C message in the logs:
In the db2diag log:
2010-04-07-10.02.41.189027-240 E139861A577 LEVEL: Error
PID : 9224292 TID : 3567 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000
EDUID : 3567 EDUNAME: db2loggw (SAMPLE) 0
FUNCTION: Db2UDB, data protection services, sqlpgWriteToDisk, probe:1033
MESSAGE : ADM1849C The current log sequence number (LSN) is "F0800067B000",
which is approaching the maximum value of 0xFFFF FFFF FFFF. If the
database reaches the maximum LSN value, you will not be able to
continue to use the database.
In the administration notification log (e.g. db2inst1.nfy):
2010-04-07-10.02.41.189322 Instance:db2inst1 Node:000
PID:9224292(db2loggw (SAMPLE) 0) TID:3567 Appid:none
data protection services sqlpgWriteToDisk Probe:1033
ADM1849C The current log sequence number (LSN) is "F0800067B000", which is approaching the maximum value of 0xFFFF FFFF FFFF. If the database reaches the maximum LSN value, you will not be able to continue to use the database.
When the LSN hits a second threshold of 0XFFFF 0000 0000 the database is in read-only mode, therefore applications which attempt to write log records will fail with SQL0946C.
After reaching the maximum LSN:
Once the database is marked read-only, you can find the ADM1850C message in the logs:
In db2diag.log:
2010-04-07-14.47.58.507137-240 E16916A663 LEVEL: Error
PID : 8384514 TID : 2829 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-7 APPID: *LOCAL.db2inst1.100407184716
AUTHID : DB2INST1
EDUID : 2829 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: Db2UDB, data protection services, sqlpWriteLR, probe:1715
MESSAGE : ADM1850C The database has reached the Log Sequence Number (LSN)
limit of "FFFF00000000". No more log records can be written, so the
database cannot process any transactions that require log records to
be written.
In the administration notification log (e.g. db2inst1.nfy):
2010-04-07-14.47.58.516208 Instance:db2inst1 Node:000
PID:8384514(db2agent (SAMPLE) 0) TID:2829 Appid:*LOCAL.db2inst1.100407184716
data protection services sqlpWriteLR Probe:1715 Database:SAMPLE
ADM1850C The database has reached the Log Sequence Number (LSN) limit of
"FFFF00000000". No more log records can be written, so the database cannot
process any transactions that require log records to be written.
Resolving The Problem
You must actively monitor the current LSN value on Db2 Version 9.1 and Version 9.5 to ensure there is enough time to react before the maximum LSN is reached. To monitor the current LSN value, use either the db2pd command or the stored procedure in this technote.
To resolve the problem, you should upgrade to Db2 Version 9.7 before the LSN limit is reached, which will effectively prevent the problem from recurring. Db2 Version 9.7 has an expanded LSN limit of 16 exabytes (1024^6) compared to Version 9.5 and earlier, which has a limit of 256 terabytes (1024^4).
You can reset the LSN without upgrading by unloading all data, dropping and recreating the database, and then reloading the data, but you must continue to monitor the current LSN value afterwards and react as necessary. For production systems, the recommended resolution is to upgrade.
Measuring the rate of LSN usage:
It is important to understand not only the proximity of the current LSN to the maximum LSN, but also how fast LSNs are being used up. To measure the rate of LSN usage, collect at least two sets of data spread out over time. The longer the duration between data collections, the greater the accuracy will be in calculating the rate of LSN usage. You should also collect each data set over a full production cycle, so that both periodic spikes and drops in LSN usage are included in the sample data.
To calculate the rate of LSN usage, use the following formula:
(LSN_value_2 - LSN_value_1) / (time_2 - time_1) = LSN_rate
To calculate the time before reaching the maximum LSN:
- (0xFFFF 0000 0000 - current_LSN) / LSN_rate
In partitioned database environments, you need to monitor the current LSN value on each partition.
Examples
To determine the current LSN value using the db2pd command, use the following syntax:
- db2pd -logs -db database_name
The output will differ depending on which version and fix pack you are using.
On Db2Version 9.1 or on Version 9.5 prior to fix pack 5, only the range into which the current LSN value falls is shown. In the example below, the Current Log Number is 0. The current LSN is between the starting LSN (StartLSN) value of Log 0 at 0x0000 01F8 8000 and the starting LSN value of Log 1 at 0x0000 0238 8000. For simplicity, you can use the StartLSN value of the next log file as the current LSN, in this case the value for Log 1, or 0x0000 0238 8000.
Database Partition 0 -- Database TEST -- Active -- Up 0 days 00:00:02
Logs:
Current Log Number 0
Pages Written 0
Method 1 Archive Status n/a
Method 1 Next Log to Archive n/a
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Address StartLSN State Size Pages Filename
0x07000000402EFF38 0x000001F88000 0x00000000 1024 1024 S0000000.LOG
0x070000004005EF78 0x000002388000 0x00000000 1024 1024 S0000001.LOG
0x070000004001FEB8 0x000002788000 0x00000000 1024 1024 S0000002.LOG
0x070000004001FF78 0x000002B88000 0x00000000 1024 1024 S0000003.LOG
0x070000004005FE78 0x000002F88000 0x00000000 1024 1024 S0000004.LOG
0x070000004005FF38 0x000003388000 0x00000000 1024 1024 S0000005.LOG
0x07000000402F7AD8 0x000003788000 0x00000000 1024 1024 S0000006.LOG
0x07000000402F7B98 0x000003B88000 0x00000000 1024 1024 S0000007.LOG
0x07000000402F7C58 0x000003F88000 0x00000000 1024 1024 S0000008.LOG
0x07000000402F7D18 0x000004388000 0x00000000 1024 1024 S0000009.LOG
0x07000000402F7DD8 0x000004788000 0x00000000 1024 1024 S0000010.LOG
0x07000000402F7E98 0x000004B88000 0x00000000 1024 1024 S0000011.LOG
0x07000000402F7F58 0x000004F88000 0x00000000 1024 1024 S0000012.LOG
On Db2Version 9.5 fix pack 5 and later, the value of the current LSN is displayed in the header information, here 0x000000000452CABB. Note that, while the display is eight bytes in length, the internal limit is six bytes:
Database Partition 0 -- Database TEST -- Active -- Up 3 days 00:00:30
Logs:
Current Log Number 0
Pages Written 612
Cur Commit Disk Log Reads 0
Cur Commit Total Log Reads 0
Method 1 Archive Status n/a
Method 1 Next Log to Archive n/a
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Log Chain ID 0
Current LSN 0x000000000452CABB
To determine the current LSN value using the stored procedure:
A stored procedure that returns the current LSN value is attached to this technote in source code format (getcurrlsn.C). Before you can use this stored procedure, you need to compile the code and create the stored procedure in the database. The source code includes instructions that you can follow.
To call the stored procedure, use the following syntax:
- Db2"call getcurrentlsn(?)"
In the example output below, the current LSN is 000026805AB1:
Value of output parameters
--------------------------
Parameter Name : CURRENT_LSN
Parameter Value : 000026805AB1
Return Status = 0
Note that this stored procedure will not work against a database using circular logging. If it is run against a database with circular logging, the SQL2651N (The log records associated with the database can not be asynchronously read) error will be returned.
Related Information
ADM1849C error message (v9.5 information center)
ADM1850C error message (v9.5 information center)
SQL and XML limits (v9.5 information center)
AIX C routine compile and link options
HP-UX C routine compile and link options
Linux C routine compile and link options
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21420326