Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: SQL1218N There are no pages currently available in bufferpool "". SQLSTATE=57011

    Hello folks,

    Has anyone have experienced a problem like this.

    Code:
    DB2 ROLLFORWARD DATABASE ECX TO end of logs and stop OVERFLOW LOG PATH ('G:\ECX_Daily_backup\Logs')
    
    SQL1218N  There are no pages currently available in bufferpool "". SQLSTATE=57011
    ErroLevel 4


    We restore on Mirror server every early morning.

    Restore and Roillforward were OK till we used 8.2.5

    Now on v 9.5.4 and before V 9.5.0 we are hacving above issue.

    Retrying again some times works.

    The Mirror Win 2k3 (8G) has half the memory of Production Win 2k3 (16G)

    Any info, how to resolve.

    DBFinder

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    There is not enough memory to allocate the bufferpools. This error occurs when db2 tries to connect to the db to apply the logs. The bufferpool size it's trying to allocate is what's stored in the backup image (bp allocations you have in prod). You can use DB2_OVERRIDE_BPF registry variable to override the size of you bufferpools if rollforward continues to fail. You should also adjust the size of your bufferpools after rollforward completes and unset the variable.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Bella,

    Yes you are right,

    But I am still wondering to find what value should I set it to.

    Database is in RollForward Pending state. How to figure out a tentative number for this variable.

    DBFinder

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    DBFinder, as a guess, since you have half the memory, I would set the value to half the production size.

    If you make it to big, it will abend again. If you make it 'too small' it should still work but not as efficiently.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2girl
    You can use DB2_OVERRIDE_BPF registry variable to override the size of you bufferpools if rollforward continues to fail.
    No, won't work (been there). We've had the same problem lately. Can only be solved to ensure that the hardware configuration is exactly the same as the source computer. In our case we had to change the "boot.ini" with the /3G switch. The DB2_OVERRIDE_BPF does not kick-in during rollforeward.

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Bella,

    This WORKAROUND did not work, I tried 7 different settings.

    DBFinder

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Hi all,

    This WORKAROUND has finally worked.

    DB2SET DB2_OVERRIDE_BPF=25000

    I had tried 64 128 256 50000 250000 2500000 25000000 25000

    So my number is 25000.

    Thanks everybody
    DBFinder

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Sorry, could not reply earlier... What I believe is happening during rollforward is that db2 tries to connect to the db and finds that there is not enough memory to allocate prod-size bufferpools (this is what's in the image) so it tries to connect using hidden bufferpools. Hidden bufferpools are small (I think 16 pages), 16 pages is not enough for rollforward to complete and so it fails with sql1218n. What DB2_OVERRIDE_BPF will do is override the bufferpool sizes stored in the backup image and also the hidden ones so rollforward will use the size specified with DB2_OVERRIDE_BPF. Usually, setting DB _OVERRIDE_BPF to somewhere between 1000-2000 should be enough (instance needs to be restarted).

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Correction: DB2_OVERRIDE_BPF will not override hidden bufferpools

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Hi,

    Today it worked with value 25000 for DB2_OVERRIDE_BPS.

    This will be confirmed over time because occasionally it used to work with even hidden BPs. Today the logs were more, and most probablly were from table with bigger pagesize.

    Your help has really worked for me and I hope it will be ok till we upgrade the server (Memory). We restore this DB every morning.

    thanks
    DBFinder

    PS: Recently IBM support has confirmed that this is the proper way to do it.
    Last edited by DBFinder; 06-10-09 at 16:34.

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by dr_te_z
    The DB2_OVERRIDE_BPF does not kick-in during rollforeward.
    We had to restore an old (V8.2) database so we re-built a V8.2 machine and tried to redirect-restore.
    Can someone confirm that the DB2_OVERRIDE_BPF behaviour changed in V9 (I hope so)?

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    No,

    I had a discussion with IBM support rep yesteday around 3:00pm. The result of discussion was that I started to believe that our machine previously used this variable and I never had this rollforward problem since I was hired.
    Recently when I attempted UPGRADE, I had a problem with upgrade, I could not rollforward, I dropped the instance and reinstalled 8.2 afresh. This variable might have been unset ( no way to confirm this.). Yesterday I started PMR with IBM and I got answer after I had already resolved this via current post; IBM called me around 3:00pm. I let IBM rep say whole thing to ensure that what I was doing was right.

    They told us to start with a value of 1000 and try going up in steps till it succeeds. He also said that this variable is for this particular scenario.

    Please compare this

    Ver 8

    http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0005665.htm?resultof=%22%44%42%32%5f%4f%56%45%52% 52%49%44%45%5f%42%50%46%22%20

    and

    Ver 9

    http://publib.boulder.ibm.com/infoce...2_OVERRIDE_BPF


    also

    Management of multiple database buffer pools

    I hope I can ask IBM rep if you have anything else to know other than this regarding this subject.

    Thanks and good luck

    DBFinder

    PS: Today's restore is going to finish around 6:00 am. I will confirm that it I working after that.
    Last edited by DBFinder; 06-11-09 at 04:22.

  13. #13
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Restore and Rollforward were OK today.

    So the problem has been resolved

    DBFinder
    Last edited by DBFinder; 06-11-09 at 17:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •