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 Tue April 20, 2021 11:46 AM

    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 Tue April 20, 2021 12:10 PM

    The is_extendable flag on syschunks ?

     






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

    Posted Tue April 20, 2021 12:30 PM
    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 Tue April 20, 2021 12:34 PM
    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 Tue April 20, 2021 02:28 PM
    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 Tue April 20, 2021 02:51 PM
    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 Tue April 20, 2021 03:12 PM
    Why oh why didn't occur to me to check in the sysmaster? ^_^
    Thanks Art!

    R

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