Checkpoint triggered from LongTx

Hi to ALL In our company we are using "IBM Informix Dynamic Server Version 11.50.FC3" on AIX platform "6.1.0.0" In the last few day I have notice that LongTx are causing a checkpoint despite the fact that I have configure a checkpoint interval every 5 minutes. 342091 15:53:12 CKPTINTVL 168399:0x54f1018 0.5 0.2 0.0 1 0.0 0.2 0.2 5429 5429 3217 10 14876 49 342092 15:58:13 CKPTINTVL 168399:0x7d3d018 0.9 0.6 0.0 0 0.0 0.0 0.0 9776 9776 2406 7 10320 34 342093 16:00:06 *User 168400:0x105018 0.3 0.0 0.0 2 0.00 0.3 0.3 305 305 1910 16 12322 109 342094 16:05:13 CKPTINTVL 168401:0x778c14c 1.2 0.9 0.0 5 0.0 0.2 0.2 17616 17616 9347 30 33377 109 342095 16:10:24 CKPTINTVL 168404:0x2f1018 11.8 11.6 0.0 23 0.0 0.2 0.2 110777 9583 48557 161 125637 417 342096 16:12:53 LongTX 168406:0x276906c 5.2 4.9 0.0 32 0.0 0.3 0.3 139425 28235 67956 438 111911 722 342097 16:14:40 Plog 168407:0xa610018 7.0 6.7 0.0 29 0.00 0.2 0.2 138518 20529 62746 597 85157 811 Is this NORMAL ?????????????????? I thought that a checkpoint is triggered when: 1. Physical Log 75% full 342097 16:14:40 Plog 168407:0xa610018 7.0 6.7 0.0 29 0.00 0.2 0.2 138518 20529 62746 597 85157 811 2. From CKPTINTVL 3. At server startup 4. When "onmode -c" is requested 5. When a dbspace backup is performed 6. When adding a dbspace It's the first time I have notice a checkpoint triggered by LongTx. Can you explain this to me ???? **************************************************************************** *************** Another issue that we facing the last few days I get a WARNING in the message log to perform a Level 0 backup. 12:20:32 WARNING: Next backup of DBspace aabbdbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace bbccdbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace ccdddbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace ddeedbs must be level-0 backup Why is this happening ????? Regards Description: Description: CoopLogo1Description: Description: CoopLogo1 Cooperative Computer Society (S.E.M) Ltd 1306 Nicosia P.O.B. 25037 CY Tel: +357 22 673 901 Fax: +357 22 672 774 Achilleas Achilleos Official A OS and Databases Management <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy --Boundary_(ID_pMq8WaXxvanjxgHA+ZCbFw)

Sure. Because a long transaction can fill the logical logs, performing a checkpoint when a long transaction is detected might free up some logical log space for reuse reducing the possibility that other transactions will be affected. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 6, 2012 at 3:11 AM, Achilleas Achilleos < AchilleasAchilleos@semltd.com.cy> wrote: > Hi to ALL > > In our company we are using "IBM Informix Dynamic Server Version 11.50.FC3" > on AIX platform "6.1.0.0" > > In the last few day I have notice that LongTx are causing a checkpoint > despite the fact that I have configure a checkpoint interval every 5 > minutes. > > 342091 15:53:12 CKPTINTVL 168399:0x54f1018 0.5 0.2 0.0 1 > 0.0 0.2 0.2 5429 5429 3217 10 14876 49 > > 342092 15:58:13 CKPTINTVL 168399:0x7d3d018 0.9 0.6 0.0 0 > 0.0 0.0 0.0 9776 9776 2406 7 10320 34 > > 342093 16:00:06 *User 168400:0x105018 0.3 0.0 0.0 2 > 0.00 0.3 0.3 305 305 1910 16 12322 109 > > 342094 16:05:13 CKPTINTVL 168401:0x778c14c 1.2 0.9 0.0 5 > 0.0 0.2 0.2 17616 17616 9347 30 33377 109 > > 342095 16:10:24 CKPTINTVL 168404:0x2f1018 11.8 11.6 0.0 23 > 0.0 0.2 0.2 110777 9583 48557 161 125637 417 > > 342096 16:12:53 LongTX 168406:0x276906c 5.2 4.9 0.0 32 > 0.0 0.3 0.3 139425 28235 67956 438 111911 722 > > 342097 16:14:40 Plog 168407:0xa610018 7.0 6.7 0.0 29 > 0.00 0.2 0.2 138518 20529 62746 597 85157 811 > > Is this NORMAL ?????????????????? > > I thought that a checkpoint is triggered when: > > 1. Physical Log 75% full > > 342097 16:14:40 Plog 168407:0xa610018 7.0 6.7 0.0 29 > 0.00 0.2 0.2 138518 20529 62746 597 85157 811 > > 2. From CKPTINTVL > > 3. At server startup > > 4. When "onmode -c" is requested > > 5. When a dbspace backup is performed > > 6. When adding a dbspace > > It's the first time I have notice a checkpoint triggered by LongTx. > > Can you explain this to me ???? > > > **************************************************************************** > *************** > > Another issue that we facing the last few days I get a WARNING in the > message log to perform a Level 0 backup. > > 12:20:32 WARNING: Next backup of DBspace aabbdbs must be level-0 backup. > > 12:20:32 WARNING: Next backup of DBspace bbccdbs must be level-0 backup. > > 12:20:32 WARNING: Next backup of DBspace ccdddbs must be level-0 backup. > > 12:20:32 WARNING: Next backup of DBspace ddeedbs must be level-0 backup > > Why is this happening ????? > > Regards > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > Cooperative Computer Society (S.E.M) Ltd > > 1306 Nicosia > > P.O.B. 25037 CY > > Tel: +357 22 673 901 > > Fax: +357 22 672 774 > > Achilleas Achilleos > > Official A > > OS and Databases Management > > <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy > > --Boundary_(ID_pMq8WaXxvanjxgHA+ZCbFw) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340903866ece04c905c719

There are many triggers to checkpoints other than just the checkpoint interval. IE... physical log 75% full, next logical log contains last checkpoint, archive start, manual, just to name a few. I would worry less about the checkpoints until I resolved the long transaction issue. Dan -------------------------------------------------- From: "Achilleas Achilleos" <AchilleasAchilleos@semltd.com.cy> Sent: Thursday, September 06, 2012 3:11 AM To: <ids@iiug.org> Subject: Checkpoint triggered from LongTx [28231] > Hi to ALL > > In our company we are using "IBM Informix Dynamic Server Version > 11.50.FC3" > on AIX platform "6.1.0.0" > > In the last few day I have notice that LongTx are causing a checkpoint > despite the fact that I have configure a checkpoint interval every 5 > minutes. > > 342091 15:53:12 CKPTINTVL 168399:0x54f1018 0.5 0.2 0.0 1 > 0.0 0.2 0.2 5429 5429 3217 10 14876 49 > > 342092 15:58:13 CKPTINTVL 168399:0x7d3d018 0.9 0.6 0.0 0 > 0.0 0.0 0.0 9776 9776 2406 7 10320 34 > > 342093 16:00:06 *User 168400:0x105018 0.3 0.0 0.0 2 > 0.00 0.3 0.3 305 305 1910 16 12322 109 > > 342094 16:05:13 CKPTINTVL 168401:0x778c14c 1.2 0.9 0.0 5 > 0.0 0.2 0.2 17616 17616 9347 30 33377 109 > > 342095 16:10:24 CKPTINTVL 168404:0x2f1018 11.8 11.6 0.0 23 > 0.0 0.2 0.2 110777 9583 48557 161 125637 417 > > 342096 16:12:53 LongTX 168406:0x276906c 5.2 4.9 0.0 32 > 0.0 0.3 0.3 139425 28235 67956 438 111911 722 > > 342097 16:14:40 Plog 168407:0xa610018 7.0 6.7 0.0 29 > 0.00 0.2 0.2 138518 20529 62746 597 85157 811 > > Is this NORMAL ?????????????????? > > I thought that a checkpoint is triggered when: > > 1. Physical Log 75% full > > 342097 16:14:40 Plog 168407:0xa610018 7.0 6.7 0.0 29 > 0.00 0.2 0.2 138518 20529 62746 597 85157 811 > > 2. From CKPTINTVL > > 3. At server startup > > 4. When "onmode -c" is requested > > 5. When a dbspace backup is performed > > 6. When adding a dbspace > > It's the first time I have notice a checkpoint triggered by LongTx. > > Can you explain this to me ???? > > **************************************************************************** > *************** > > Another issue that we facing the last few days I get a WARNING in the > message log to perform a Level 0 backup. > > 12:20:32 WARNING: Next backup of DBspace aabbdbs must be level-0 backup. > > 12:20:32 WARNING: Next backup of DBspace bbccdbs must be level-0 backup. > > 12:20:32 WARNING: Next backup of DBspace ccdddbs must be level-0 backup. > > 12:20:32 WARNING: Next backup of DBspace ddeedbs must be level-0 backup > > Why is this happening ????? > > Regards > > Description: Description: CoopLogo1Description: Description: CoopLogo1 > > Cooperative Computer Society (S.E.M) Ltd > > 1306 Nicosia > > P.O.B. 25037 CY > > Tel: +357 22 673 901 > > Fax: +357 22 672 774 > > Achilleas Achilleos > > Official A > > OS and Databases Management > > <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy > > --Boundary_(ID_pMq8WaXxvanjxgHA+ZCbFw) > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Original post: <stuff removed> Another issue that we facing the last few days I get a WARNING in the message log to perform a Level 0 backup. 12:20:32 WARNING: Next backup of DBspace aabbdbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace bbccdbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace ccdddbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace ddeedbs must be level-0 backup Why is this happening ????? Response: I believe you get that warning when you take level 1 or level 2 backups and in the process of doing that, the server checks the archive time stamp of all dbspaces and if any of those time stamps are out of the interval range (half available timestamps) compared to the current server time stamp, if prints that warning and marks those dbspaces as unavailable for incremental backups. Since incremental backups are based on the archive time stamp of the level 0, after using that many time stamps, the incremental backups are no longer able to correctly identify which pages would need to be backed up. As for why are you using up time stamps so quickly, I believe there were some defects in this area where temp table activity could cause excessive time stamp usage. So it's possible in a newer release you might not see these warnings. Or your system is that busy that you do use time stamps quickly and in that case even a newer release wouldn't get rid of the warnings. Jacques Renaut IBM Informix Advanced Support APD Team

Original post: Original post: <stuff removed> Another issue that we facing the last few days I get a WARNING in the message log to perform a Level 0 backup. 12:20:32 WARNING: Next backup of DBspace aabbdbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace bbccdbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace ccdddbs must be level-0 backup. 12:20:32 WARNING: Next backup of DBspace ddeedbs must be level-0 backup Why is this happening ????? Response: I believe you get that warning when you take level 1 or level 2 backups and in the process of doing that, the server checks the archive time stamp of all dbspaces and if any of those time stamps are out of the interval range (half available timestamps) compared to the current server time stamp, if prints that warning and marks those dbspaces as unavailable for incremental backups. Since incremental backups are based on the archive time stamp of the level 0, after using that many time stamps, the incremental backups are no longer able to correctly identify which pages would need to be backed up. As for why are you using up time stamps so quickly, I believe there were some defects in this area where temp table activity could cause excessive time stamp usage. So it's possible in a newer release you might not see these warnings. Or your system is that busy that you do use time stamps quickly and in that case even a newer release wouldn't get rid of the warnings. Jacques Renaut IBM Informix Advanced Support APD Team Response: Opps, quick clarification. The warning message is NOT dependent on you taking a level 1 or 2 backup. The server checks for dbspaces to go out of interval at every checkpoint. So as soon as your server's time stamp advances far enough to cause problems for incremental backups on dbspaces that have had level 0 archives taken of them, it prints the warning and marks those dbspaces as unavailable for level 1 or 2 backups. Jacques Renaut IBM Informix Advanced Support APD Team