IBM?
 
? 我的 IBM
?
 
登录
IBM Support
  Search
LSN limit in DB2 V9.1 and V9.5, and ADM1849C or ADM1850C messages
Want a glimpse into the future? Check out the new support experience beta.
Chat with our new IBM Support Agent Tool (Beta) 
Technote (troubleshooting)




Problem(Abstract)
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: DB2 UDB, 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: DB2 UDB, 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 DB2 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 DB2 Version 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 DB2 Version 9.5 fix pack 5 and later, the value of the current LSN is displayed in the header information, here0x000000000452CABB. 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
Solaris C routine compile and link options
Windows C and C++ routine compile and link options
 
对该页面打分
Click to rate document as Poor. The average rating is 1 star(s) based on 1 user(s). The document reputation is Poor.Click to rate document as Fair. The average rating is 1 star(s) based on 1 user(s). The document reputation is Poor.Click to rate document as Average. The average rating is 1 star(s) based on 1 user(s). The document reputation is Poor.Click to rate document as Good. The average rating is 1 star(s) based on 1 user(s). The document reputation is Poor.Click to rate document as Very good. The average rating is 1 star(s) based on 1 user(s). The document reputation is Poor.
平均评分 (1 用户)
Others also viewed...
? Known issues for DB2 for Linux, UNIX and Windows on AIX 5.3, 6.1, and 7.1
? DB2 transaction log full errors occur if small values for the DB2 parameters LOGFILSIZ, LOGPRIMARY, LOGSECOND, LOGBUFSZ are used.
? Download DB2 Fix Packs by version for DB2 for Linux, UNIX and Windows
文档信息
More support for:DB2 for Linux, UNIX and Windows
软件版本: 9.1, 9.5
操作系统: AIX, HP-UX, Linux, Solaris, Windows
参考号: 1420326
修改日期: 2011-01-05
IBM Technical Support mobile app is now available!
 
 
Chat, contact and feedback
回页首
WeiboGoogle+用电子邮件发送本页面
关闭
Need support?
? Chat with our new IBM Support Agent Tool (Beta)
? E-mail feedback to IBM support
? 1-800-IBM-7378 (USA)
? Directory of worldwide contacts
发现
? 产品
? 服务
? 行业
? 软件产品成功案例
提供信息给
? 开发人员
? 业务合作伙伴（英文)
? 业务及IT决策者
联系我们
? 在线分公司
? 技术支持
? 联系业务代表
? 联系合作伙伴（英文)
关于 IBM
? 求才启事
? 热点新闻
? 企业责任
? IBM 在中国
Select a country/region
 China - Chinese (Simplified)
? 联系 IBM
? 隐私条约
? 使用条款
? 信息无障碍
? Feedback