Informix

Expand all | Collapse all

How to find which dbspaces are set to auto extend?

  • 1.  How to find which dbspaces are set to auto extend?

    Posted 21 days ago

    Howdy y'all!
    I understand what needs to be done to do make a dbspace auto extendable:

    1. Mark the chunk(s) extendable:
      DATABASE sysadmin;
      EXECUTE FUNCTION task("modify chunk extendable", "12");​​
    2. Set the SP_THRESHOLD onconfig setting.
    3. Change how often the mon_low_storage task runs:
      DATABASE sysadmin;
      UPDATE ph_task set tk_frequency = INTERVAL (10) MINUTE TO MINUTE
       WHERE tk_name = "mon_low_storage";​​
    4. Setup the size or percentage of the extend:
      DATABASE sysadmin;
      EXECUTE FUNCTION task("modify dbspace sp_sizes", "3", "60000", "10000");​​

    Being the last step key to set the auto extend (at least how I understand it): how else would the mon_low_storage task know how much to add to a dbspace?

    Ultimately, I need to know how to check if the last step has been done. Hopefully there is a table in the sysadmin (?) database that I can query to find if the last step has been done and the "dbspace sp_sizes" size or percentage for the auto extend.

    Many thanks in advance!


    Ramón

    ------------------------------
    Ramon Rey
    ------------------------------


  • 2.  RE: How to find which dbspaces are set to auto extend?

    Posted 21 days ago

    The is_extendable flag on syschunks ?

     






  • 3.  RE: How to find which dbspaces are set to auto extend?

    Posted 21 days ago
    Ramon:

    There is a column in sysmaster:syschunks.is_extendable that is set to '1' if the chunk is correctly marked to be extended and the sysmaster:sysdbspaces.extend_size contains the amount of space to add with each extend.

    Manually you can see the "E" in the chunk list in onstat -d. Note, for example, that in my server the root chunk and temp dbspace and sbspace chunks as well as the last chunk added, are not extendable (no E):


    Chunks
    address          chunk/dbs     offset     size       free       bpages     flags pathname
    46ad9268         1      1      0          150000     132013                PO-B-D /opt/informix/chunks/rootdbs.chk.001
    47e2c028         2      2      0          247220     2167                  PO-BED /opt/informix/chunks/llogspace.chk.001
    47e2d028         3      3      0          111611     11611                 PO-BED /opt/informix/chunks/plogspace.chk.001
    47e2e028         4      4      0          739698     161213                PO-BED /opt/informix/chunks/datadbs_1.chk.001
    47e2f028         5      5      0          37500      16311                 PO-BED /opt/informix/chunks/indexdbs_1.chk.001
    47e30028         6      6      0          102400     102347                PO-B-D /opt/informix/chunks/tempdbs.chk.001
    47e31028         7      7      0          50000      46438      46558      POSB-D /opt/informix/chunks/sbspace.chk.001
                                    Metadata 3389       541        3389     
    47e32028         8      8      0          50000      16965                 PO-BED /opt/informix/chunks/cdrspace.chk.001
    47e33028         9      9      0          35000      892                   PO-BED /opt/informix/chunks/indexdbs.chk.001
    47e34028         10     7      0          500000     450327     466319     POSB-D /opt/informix/chunks/elendil_sbspace_p_1
                                    Metadata 33678      26364      33678    
    47e35028         11     10     0          5000       4939                  PO-B-D /opt/informix/chunks/datadbs_2.chk.001



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 4.  RE: How to find which dbspaces are set to auto extend?

    Posted 21 days ago
    FWIW, here's my script to mark all of the last chunks in each dbspace extendable (except those that are not eligible to be extended):

    #
    # mark_all_extendable.ksh
    #
    dbaccess sysmaster - <<EOF
    unload to /tmp/extend.api delimiter ";"
    select 'execute function task( "modify chunk extendable", '||chknum||' )'  
    from syschunks sc, sysdbspaces sd
    where sc.dbsnum = sd.dbsnum
     and sd.is_temp = 0
     and sd.is_blobspace = 0
     and sd.is_sbspace = 0
     and sd.dbsnum != 1
     and sc.nxchknum = 0
     and sc.is_extendable = 0
    ;
    EOF
    dbaccess -e sysadmin - </tmp/extend.api
    rm /tmp/extend.api


    ​​​

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: How to find which dbspaces are set to auto extend?

    Posted 20 days ago
    Thanks Art and Paul,

    What I actually need to find is:
    • Has a dbspace had its "sp_sizes modified", as per "EXECUTE FUNCTION task('modify dbspace sp_sizes', '3', '60000', '10000');"?
    • If so, what were the create, extend and max extend sizes and/or percentages set to?
    I figure since the "EXECUTE FUNCTION task('modify dbspace sp_sizes', '3', '60000', '10000');" call is performed in the sysadmin database, there must be a table storing those sizes/percentages, or at least some offset of some field.

    Thanks again! ^_^

    R

    ------------------------------
    Ramon Rey
    ------------------------------



  • 6.  RE: How to find which dbspaces are set to auto extend?

    Posted 20 days ago
    Ramon:

    No, there is nothing in sysadmin about extending chunks of expanding dbspaces using the storage pool either. It is all stored in sysdbspaces and syschunks. Witness:

    dbaccess sysmaster -

    > select * from sysdbspaces where dbsnum = 4;

    dbsnum        4
    name          datadbs_1
    owner         informix
    pagesize      2048
    fchunk        4
    nchunks       1
    create_size   10.00000000000
    extend_size   10000.00000000
    max_size      0
    is_mirrored   0
    is_blobspace  0
    is_sbspace    0
    is_temp       0
    is_encrypted  0
    flags         1

    1 row(s) retrieved.

    > database sysadmin;
    > execute function task( 'modify space sp_sizes', 'datadbs_1', 1000, 50000, 1000000000 );

    (expression)  Succeeded: Create size changed to 1000, Extend size
                 changed to 50000, Max size changed to 1000000000, for DBspace dat
                 adbs_1.  
    1 row(s) retrieved.

    > select * from sysmaster:sysdbspaces where dbsnum = 4;

    dbsnum        4
    name          datadbs_1
    owner         informix
    pagesize      2048
    fchunk        4
    nchunks       1
    create_size   1000.000000000
    extend_size   50000.00000000
    max_size      976562
    is_mirrored   0
    is_blobspace  0
    is_sbspace    0
    is_temp       0
    is_encrypted  0
    flags         1

    1 row(s) retrieved.




    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 7.  RE: How to find which dbspaces are set to auto extend?

    Posted 20 days ago
    Why oh why didn't occur to me to check in the sysmaster? ^_^
    Thanks Art!

    R

    ------------------------------
    Ramon Rey
    ------------------------------