Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Empty dbspace won't drop

    Posted 04/25/23 05:09 PM

    I cannot drop the following space.   It gives me an ISAM 148.   14.10.FC7

    I am certain the issue is the errors it is reporting.   I don't have any idea how to clear these errors.   This space had a table that spanned all these spaces that has been unloaded into a flat file and the table dropped.  

    I can re-create/restore this at will (test system)


    DBspace Usage Report: rmhsdbs1            Owner: informix  Created: 09/25/2014


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
         5 /opt/informix/disk_links/s_l5_rmhsdbs1           12  1536000       53  1535947

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     rmhsdbs1:'informix'.TBLSpace                                         3       50 0x00500001        1
     FREE                                                                53  1535947

     Total Used:       53
     Total Free:  1535947


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        37 /opt/informix/disk_links/s_l38_rmhsdbs2           12    87381        3    87378

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     FREE                                                                 3    87378

     Total Used:        3
     Total Free:    87378
    ERROR:Chunk used pages: 3, Extent used pages: 3.
    ERROR:Chunk total pages: 87381, Extent total pages: 87381.


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        39 /opt/informix/disk_links/s_l40_rmhsdbs3           12   174762        3   174759

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     FREE                                                                 3   174759

     Total Used:        3
     Total Free:   174759
    ERROR:Chunk used pages: 3, Extent used pages: 3.
    ERROR:Chunk total pages: 174762, Extent total pages: 174762.


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        40 /opt/informix/disk_links/s_l41_rmhsdbs4           12   349525        3   349522

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     FREE                                                                 3   349522

     Total Used:        3
     Total Free:   349522
    ERROR:Chunk used pages: 3, Extent used pages: 3.
    ERROR:Chunk total pages: 349525, Extent total pages: 349525.


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        44 /opt/informix/disk_links/s_l45_rmhsdbs5           12    85333        3    85330

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     FREE                                                                 3    85330

     Total Used:        3
     Total Free:    85330
    ERROR:Chunk free pages: 85330, Extent free pages: 85330.
    ERROR:Chunk total pages: 85333, Extent total pages: 85333.



    ------------------------------
    Jared Heath
    ------------------------------


  • 2.  RE: Empty dbspace won't drop

    Posted 04/26/23 05:08 AM

    Hi Jared,

    Possibly what you are seeing is described by https://www.ibm.com/support/pages/apar/IT42405 - table is using the dbspace but has no extent.

    If it is this, you'd need to identify the table (or partition) causing the issue and drop it. Possibly 'dbschema -ss' might give clues. Or you could 'dd' the extent
    rmhsdbs1:'informix'.TBLSpace                                         3       50 0x00500001        1
    to a file and inspect the strings.
    Another way is to use the hex(partnum) of an object to see in which dbspace it resides.

    Ben.



    ------------------------------
    Benjamin Thompson
    ------------------------------



  • 3.  RE: Empty dbspace won't drop

    Posted 04/26/23 02:55 PM

    There has ever only been a single table in this dbspace.   I dropped it right before trying to drop the space.

    We don't have partitions in this database.



    ------------------------------
    Jared Heath
    ------------------------------

    Original Message


  • 4.  RE: Empty dbspace won't drop

    Posted 04/26/23 03:02 PM
    Edited by Jared Heath 04/26/23 03:02 PM

    I've restored this database.   This is the space befor touching it:


    DBspace Usage Report: rmhsdbs1            Owner: informix  Created: 09/25/2014


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
         5 /opt/informix/disk_links/s_l5_rmhsdbs1           12  1536000  1536000        0

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     rmhsdbs1:'informix'.TBLSpace                                         3       50 0x00500001        1
     star:'informix'.rmhs                                                53  1535947 0x00500002        1

     Total Used:  1536000
     Total Free:        0


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        37 /opt/informix/disk_links/s_l38_rmhsdbs2           12    87381    87381        0

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     star:'informix'.rmhs                                                 3    87378 0x00500002        3

     Total Used:    87381
     Total Free:        0
    ERROR:Chunk used pages: 87381, Extent used pages: 87381.
    ERROR:Chunk total pages: 87381, Extent total pages: 87381.


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        39 /opt/informix/disk_links/s_l40_rmhsdbs3           12   174762   174762        0

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     star:'informix'.rmhs                                                 3   174759 0x00500002        2

     Total Used:   174762
     Total Free:        0
    ERROR:Chunk used pages: 174762, Extent used pages: 174762.
    ERROR:Chunk total pages: 174762, Extent total pages: 174762.


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        40 /opt/informix/disk_links/s_l41_rmhsdbs4           12   349525   349525        0

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     star:'informix'.rmhs                                                 3   349522 0x00500002        4

     Total Used:   349525
     Total Free:        0
    ERROR:Chunk used pages: 349525, Extent used pages: 349525.
    ERROR:Chunk total pages: 349525, Extent total pages: 349525.


     Chunk Pathname                                      Pagesize(k)  Size(p)  Used(p)  Free(p)
        44 /opt/informix/disk_links/s_l45_rmhsdbs5           12    85333    85333        0

     Description                                                   Offset(p)  Size(p)  Partnum  Ext Num
     ------------------------------------------------------------- -------- -------- ---------- --------
     RESERVED PAGES                                                       0        2
     CHUNK FREELIST PAGE                                                  2        1
     star:'informix'.rmhs                                                 3    85330 0x00500002        5
     FREE                                                             85333        0

     Total Used:    85333
     Total Free:        0

    dropping the table makes it the same as the original post

    Running the suggested sysmaster query provides only one result:

    partnum  5242881
    dbsname  rmhsdbs1
    owner    informix
    tabname  TBLSpace
    collate  
    dbsnum   5

    1 row(s) retrieved.

    Not sure what this indicates.


    Here is the dump

    onstat -g dmp 0x54cd0028 ifx_chunk_t

    IBM Informix Dynamic Server Version 14.10.FC7 -- On-Line -- Up 12:04:50 -- 3650896 Kbytes
    2023-04-26 14:01:21

    *(struct ifx_chunk_t *)0x54cd0028 = {  /* sizeof = 304 = 0x130 */
        ch_version   = -26
        ch_number    = 37
        ch_nxchunk   = 39
        ch_size      = 524288
        ch_fpage     = 0
        ch_nfree     = 524268
        ch_overhead  = 0
        ch_offset    = 0
        ch_flags     = 65600
        ch_pagesize  = 12288
        ch_dbsnum    = 5
        ch_namlen    = 39
        ch_fname[0..255] = "/opt/informix/disk_links/s_l38_rmhsdbs2"
    }






    ------------------------------
    Jared Heath
    ------------------------------

    Original Message


  • 5.  RE: Empty dbspace won't drop

    Posted 04/26/23 04:10 PM

    I think I'm seeing the problem, in that last dump: both ch_size (chunk size) and ch_nfree (#free pages) there are in "base pages"  (assuming 2kB page size, Linux, correct?) which is different from onstat -d or oncheck -pe who all display these sizes in dbspace pages (in this case 12kB page size which would be 6 base pages per dbspace page).  So both ch_size and ch_nfree had to be multiples of dbspace page size - which is NOT the case with this ch_size: 524288 is not dividable by 6.
    Because of the way these numbers are used internally for determining whether such chunk is empty ... it will never appear empty.  Don't quite see how this could have happened.

    You'll probably have to have tech support correcting this or patching this dbspace out right away.



    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 6.  RE: Empty dbspace won't drop

    Posted 04/26/23 04:26 PM
    Edited by Jared Heath 04/26/23 05:32 PM

    I found the phone number.

    Its hidden pretty well....



    ------------------------------
    Jared Heath
    ------------------------------

    Original Message


  • 7.  RE: Empty dbspace won't drop

    Posted 04/27/23 02:01 AM

    Jared:

    Yea, I have the same problem with the online support site. Here is the US support phone number, it still works:

    +1-800-426-7378

    Art



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

    Original Message


  • 8.  RE: Empty dbspace won't drop

    Posted 05/08/23 02:18 PM

    For future reference....this ended up being a defect.   The API could allow the addition of a chunk not evenly divisible by the page size (in this case 12k).

    11.70 didn't have a problem with it.   14.10 does.   IBM has logged the defect.

    It can be dropped by the support team.



    ------------------------------
    Jared Heath
    ------------------------------

    Original Message


  • 9.  RE: Empty dbspace won't drop

    Posted 04/26/23 10:23 AM
    Jared,

    It's been so long since I had a reason to look at the Informix Software Repository since it got moved to GitHub that I've forgotten how to access it.  However, somewhere in there is a Perl package named Fragments.shar. This is a Perl package with utilities.  Similarly, there should be an older version of that named Fragments.sh and Fragment-list.sh, in case you don't use Perl.  Fragment-list.sh alone should help you find what you need.

    What's the point of this?

    There are options in both versions (as I recall it) that specify to list *all* table[space]s and to filter by DBspace.  (Or you can filter via grep for the DBspace.)  If there is a stray table in there, these utilities will find it.  How you go about dropping that may be a separate question.  But at least you'll know your target.

    The worst that can happen is that you would have to copy the SQL out of the script and run it with dbaccess.  Edit the %...% out this SQL.  (I hope the blanks don't get compressed; I worked so hard on the formatting to keep it readable!)


    X  = "select trim(tnt.dbsname) dbsname,"
    X  .       " trim(tnt.owner)   owner,"
    X  .       " trim(tnt.tabname) tabname,"
    X  .       " trim(tnt.dbsname) || ':' || trim(tnt.owner) || '.'"
    X  .                         " || trim(tnt.tabname) full_name,"
    X  .       " 'T'               partn_type,"
    X  .       " tnt.partnum       partition," # partnum of table or fragment"
    X  .       " ph.lockid,"                 # partn of 1st table fragment
    X  .       " trim(ds.name)     dbspace," # Name of dbspace hosting the partition
    X  .       " ph.nrows,"                  # Row count
    X  .       " ph.nextns,"                 # Number of extents
    X  .       " (ph.pagesize/1024) pagesize," # Size of a page, in KB
    X  .       " ph.nptotal,"                # How many pages allocated to partn?
    X  .       " ph.npused"                  # How many pages are really occupied?
    X  . "  from systabnames tnt,"
    X  .       " sysptnhdr ph,"
    X  .       " sysdbspaces ds"
    X  . " where (   ph.partnum = ph.lockid" # First table partition"
    X  . "        or ph.nkeys  != 1)"        # OR another table partition"
    X  .   " and tnt.partnum = ph.partnum"
    X  .   " and ph.dbsnum = ds.dbsnum"
    X  .   " and ds.is_sbspace = 0"          # Smart-dbspace have no place here
    X  . " %DBSNAME%"                        # In case caller specified only for
    X  . " %TABLE%"                          # a database, table, or partition
    X  . " %PARTNUM%"
    X  . " %NOSYSTEM%"       # Likely will sub with clause to skip system databases
    X  . " %NOTBLSPACE%"                     # In case caller specified only for
    X  . " %NOCATALOGS%"                     # a database, table, or partition
    X  . " %NOTEMPS%"
    X  . " union "                           # Now for the index partitions
    X  . "select trim(tni.dbsname)  dbsname,"
    X  .       " trim(tni.owner)    owner,"
    X  .       " trim(tnt.tabname) || '(' || trim(tni.tabname) || ')' tabname,"
    X  .       " trim(tnt.dbsname) || ':' || trim(tnt.owner) || '.'"
    X  .        " || trim(tnt.tabname) || '(' || trim(tni.tabname) || ')'"
    X  .                          " full_name,"
    X  .        " 'I'               partn_type,"
    X  .        " tni.partnum       partition,"  # partnum of index
    X  .        " ph.lockid,"
    X  .        " trim(ds.name) dbspace,"    # Name of dbspace hosting the partition
    X  .        " ph.nrows,"                 # Row count
    X  .        " ph.nextns,"                # Number of extents
    X  .        " (ph.pagesize/1024) pagesize," # Size of a page, in KB
    X  .        " ph.nptotal,"               # How many pages allocated to partn?
    X  .        " ph.npused"                 # How many pages are really occupied?
    X  . "  from systabnames tni,"
    X  .       " systabnames tnt, "
    X  .       " sysptnhdr   ph,"
    X  .       " sysdbspaces ds"
    X  . " where (    ph.nkeys  = 1"             # This negates above OR condition
    X  .        " and ph.partnum != ph.lockid)"  # and identifies an index partition
    X  .   " and tni.partnum = ph.partnum"       # Partnum of index partition
    X  .   " and tnt.partnum = ph.lockid"        # Partnum of primary table 
    X  .   " and ph.dbsnum = ds.dbsnum"
    X  .   " and ds.is_sbspace = 0"      # Smart-dbspace have no place here either
    X  . " %DBSNAME%"
    X  . " %TABLE%"
    X  . " %PARTNUM%"
    X  . " %NOSYSTEM%"
    X  . " %INTOTEMP%"
    X  ;

    Let us know if it works.

    +----- Jacob Salomon -- jakesalomon@yahoo.com -------------------------+
    | The first lesson of economics is scarcity: there is never enough of  |
    | anything to fully satisfy all those who want it. The first lesson of |
    | politics is to disregard the first lesson of economics.              |
    +--------------- Thomas Sowell (Is Reality Optional?::Student Loans) --+






  • 10.  RE: Empty dbspace won't drop

    Posted 04/26/23 11:55 AM

    For verifying Ben's suspicion, you could run this sysmaster query:

    select * from sysmaster:systabnames where partnum >= N  * 1048576 and partnum < (N+1) * 1048576;
    with the two Ns being the number of that dbspace.

    If it returns more than the one TBLSpace partition, Ben's point would apply and you'd have to deal with that extra table being shown.

    What I'm finding concerning, though, are those seemingly nonsensical ERROR messages in oncheck -pe output, regarding chunks' total numbers - the numbers they're stating actually do look ok.

    Could you locate such chunk, e.g. #37, in onstat -d, pick its memory address (1st column) and produce the following output and post it here:

    onstat -g dmp 0x<that_address> ifx_chunk_t

    Andreas



    ------------------------------
    Andreas Legner
    ------------------------------



Related Content