Empty dbspace won't drop

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 ------------------------------

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 ------------------------------

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) --+

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_tAndreas ------------------------------ Andreas Legner ------------------------------

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 ------------------------------

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 = 39ch_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 ------------------------------

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 ------------------------------

I found the phone number. Its hidden pretty well.... ------------------------------ Jared Heath ------------------------------

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 ------------------------------

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 ------------------------------