Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Multipage file allocation

    Hi,

    On page 243 in DB2 UDB Exploitation of the Windows Environment redbook
    it states:


    Code:
    Multipage file allocation
    
    When using SMS table spaces, DB2 uses the operating system file system to
    allocate space. By default this space allocation is acquired in small increments
    that can introduce a great deal of overhead when insert activity for a table 
    space is high. Multipage file allocation is used to improve insert performance 
    for SMS table spaces. If enabled, all SMS table spaces are affected: there is 
    no selection possible for individual SMS table spaces.
    
    The default for the parameter is No  multipage file allocation is not enabled.
    Following database creation, the parameter may be set to Yes, which indicates
    that multipage file allocation is enabled. This is done using the db2empfa tool.
    
    The db2empfa tool can be executed from a command prompt, with the name of
    the database to enable multipage file allocation as a parameter as follows:
    DB2EMPFA SAMPLE
    Important: Once set to Yes, the parameter cannot be changed back to No.
    How to check out if database uses multipage file allocation or single page file allocation (the default)?

    My system:
    DB2 v7.2 FP5 on Windows XP SP1

    Thanks,
    Grofaty
    Last edited by grofaty; 08-30-04 at 05:47.

  2. #2
    Join Date
    Aug 2004
    Posts
    15
    you can use

    get db cfg for db-alias


    and observe the value for parameter " Multi-page file allocation enabled "

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks a lot. It works fine for me.
    On Windows: get db cfg for db-alias | find "Multi"

    Grofaty

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    1. I checked the multi-page parameter: get db cfg for <database> | find "Multi". It was set to NO.
    2. I made a backup of database.
    3. I enabled multi-page allocation with command: db2empfa <database>
    4. I checked the multi-page parameter: get db cfg for <database> | find "Multi". It was set to YES.
    5. I tested multi-page allocation, it works fine
    6. I restored the database from backup image.
    7. I checked the multi-page parameter: get db cfg for <database> | find "Multi". It was set to YES???

    Why is Multi-page paramters set to YES after restore of backup image? I have expected that this parameter after step 7 would be set to NO. Why isn't it?

    Thanks,
    Grofaty
    Last edited by grofaty; 11-10-04 at 09:10.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    when you restore over an existing database, the database config parameters of the exisiting parameters are retained ... If you want the original config settings of the database being restored, then restore as a new db

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I believe that starting with version 8.1 or 8.2 (I don't remember which), multipage file allocation is now the default. But it may not get changed if you upgrade from a previous release.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    sathyaram_s thanks a lot. I have tested and it works fine with "create database using restore".

    Marcus_A, thanks a lot. As I have tested databases and the multipage allocation is the default setting in db2 v8.2 and "one page allocation" is default setting in v7.2 and v8.1. I still use v7.2 so multipage allocation in default is set to multipage=NO.

    Thanks to you both, no more help is needed.

    Thanks,
    Grofaty
    Last edited by grofaty; 11-11-04 at 03:58.

Posting Permissions

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