Unable to drop sbspace
A user on IDS 9.40.FC9 (AIX) could not drop an sbspace: onspaces -d reported the sbspace still contains smart large objects and demanded the -f flag, even though oncheck -pe showed almost nothing in use. Marcus Haarmann suggested checking all databases with dbschema -ss for columns referencing the sbspace, noting he once hit a stuck usage counter in sysmaster that IBM had to patch. dbschema found no references, and Art Kagel confirmed the remaining objects (sbspace_desc, chunk_adjunc, LO_hdr_partn, etc.) are system objects still blocking the drop, advising the user to open a case with IBM support. No fix is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi there,
I've informix dataserver Version 9.40.FC9 on AIX server. When trying to remove
a sbspace I got the following messages :
#onspaces -d siglob1dbs1
WARNING: Dropping a sbspace.
Do you really want to continue? (y/n)y
The sbspace contains smart large objects.
To drop this sbspace, you must force the drop by using the -f flag.
Cannot drop the Space.
The 'oncheck -pe siglob1dbs1' gave the results below :
DBspace Usage Report: siglob1dbs1 Owner: informix Created: 11/13/2010
Chunk Pathname Size Used Free
81 /bases/informix/pcoipcoi/app/deltasigv32/dat/d01/sig1_lo_01.dbf 2621438 see
below see below
Description Offset Size
------------------------------------------------------------- -------- --------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
siglob1dbs1:'informix'.TBLSpace 3 50
SBLOBSpace FREE USER DATA (AREA 1) 53 1224180
siglob1dbs1:'informix'.sbspace_desc 1224233 4
siglob1dbs1:'informix'.chunk_adjunc 1224237 4
siglob1dbs1:'informix'.LO_ud_free 1224241 3374
siglob1dbs1:'informix'.LO_hdr_partn 1227615 57675
siglob1dbs1:'informix'.LO_ud_free 1285290 3374
siglob1dbs1:'informix'.LO_hdr_partn 1288664 57676
SBLOBSpace FREE META DATA 1346340 3374
siglob1dbs1:'informix'.chunk_adjunc 1349714 4
SBLOBSpace FREE META DATA 1349718 47540
SBLOBSpace RESERVED USER DATA (AREA 2) 1397258 524276
SBLOBSpace FREE USER DATA (AREA 2) 1921534 699904
Total Used: 122164
Total SBLOBSpace FREE META DATA: 50914
Total SBLOBSpace FREE USER DATA: 2448360
Chunk Pathname Size Used Free
82 /bases/informix/pcoipcoi/app/deltasigv32/dat/d02/sig1_lo_02.dbf 2621440 see
below see below
Description Offset Size
------------------------------------------------------------- -------- --------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
SBLOBSpace FREE USER DATA (AREA 1) 3 1224204
SBLOBSpace FREE META DATA 1224207 173028
SBLOBSpace RESERVED USER DATA (AREA 2) 1397235 524284
SBLOBSpace FREE USER DATA (AREA 2) 1921519 699921
Total Used: 3
Total SBLOBSpace FREE META DATA: 173028
Total SBLOBSpace FREE USER DATA: 2448409
Can someone explain why I can't remove this sbspace ?
Thanks in advance
Hi,
I had a similar situation a while ago with a blob space (not sblob).
The DBSpace was empty and could not be deleted.
However, we found that we had columns of empty tables referencing the blob
space.
Check this with dbschema of all your databases, which contain blob columns.
dbschema -ss will give you the complete table definition, which contains thesblob
location of the blob columns.
But - we ran into a problem where the blob space was not referenced any more
and still
could not be dropped. The usage counter somewhere in sysmaster for the dbspace
was
still set to 1 and IBM had to patch it to get rid of the unused dbspace.
Maybe this helps,
Marcus Haarmann
----- Ursprüngliche Mail -----
Von: "GILLES TCHAPPI" <giltfr@yahoo.fr>
An: ids@iiug.org
Gesendet: Freitag, 13. Juni 2014 10:52:55
Betreff: Unable to drop sbspace [33190]
Hi there,
I've informix dataserver Version 9.40.FC9 on AIX server. When trying to remove
a sbspace I got the following messages :
#onspaces -d siglob1dbs1
WARNING: Dropping a sbspace.
Do you really want to continue? (y/n)y
The sbspace contains smart large objects.
To drop this sbspace, you must force the drop by using the -f flag.
Cannot drop the Space.
The 'oncheck -pe siglob1dbs1' gave the results below :
DBspace Usage Report: siglob1dbs1 Owner: informix Created: 11/13/2010
Chunk Pathname Size Used Free
81 /bases/informix/pcoipcoi/app/deltasigv32/dat/d01/sig1_lo_01.dbf 2621438 see
below see below
Description Offset Size
------------------------------------------------------------- --------
--------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
siglob1dbs1:'informix'.TBLSpace 3 50
SBLOBSpace FREE USER DATA (AREA 1) 53 1224180
siglob1dbs1:'informix'.sbspace_desc 1224233 4
siglob1dbs1:'informix'.chunk_adjunc 1224237 4
siglob1dbs1:'informix'.LO_ud_free 1224241 3374
siglob1dbs1:'informix'.LO_hdr_partn 1227615 57675
siglob1dbs1:'informix'.LO_ud_free 1285290 3374
siglob1dbs1:'informix'.LO_hdr_partn 1288664 57676
SBLOBSpace FREE META DATA 1346340 3374
siglob1dbs1:'informix'.chunk_adjunc 1349714 4
SBLOBSpace FREE META DATA 1349718 47540
SBLOBSpace RESERVED USER DATA (AREA 2) 1397258 524276
SBLOBSpace FREE USER DATA (AREA 2) 1921534 699904
Total Used: 122164
Total SBLOBSpace FREE META DATA: 50914
Total SBLOBSpace FREE USER DATA: 2448360
Chunk Pathname Size Used Free
82 /bases/informix/pcoipcoi/app/deltasigv32/dat/d02/sig1_lo_02.dbf 2621440 see
below see below
Description Offset Size
------------------------------------------------------------- --------
--------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
SBLOBSpace FREE USER DATA (AREA 1) 3 1224204
SBLOBSpace FREE META DATA 1224207 173028
SBLOBSpace RESERVED USER DATA (AREA 2) 1397235 524284
SBLOBSpace FREE USER DATA (AREA 2) 1921519 699921
Total Used: 3
Total SBLOBSpace FREE META DATA: 173028
Total SBLOBSpace FREE USER DATA: 2448409
Can someone explain why I can't remove this sbspace ?
Thanks in advance
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Because this space is used:
siglob1dbs1:'informix'.
sbspace_desc 1224233 4
siglob1dbs1:'informix'.chunk_adjunc 1224237 4
siglob1dbs1:'informix'.LO_ud_free 1224241 3374
siglob1dbs1:'informix'.LO_hdr_partn 1227615 57675
siglob1dbs1:'informix'.LO_ud_free 1285290 3374
siglob1dbs1:'informix'.LO_hdr_partn 1288664 57676
siglob1dbs1:'informix'.chunk_adjunc 1349714 4
SBLOBSpace RESERVED USER DATA (AREA 2) 1397235 524284
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Jun 13, 2014 at 4:52 AM, GILLES TCHAPPI <giltfr@yahoo.fr> wrote:
> Hi there,
> I've informix dataserver Version 9.40.FC9 on AIX server. When trying to
> remove
> a sbspace I got the following messages :
>
> #onspaces -d siglob1dbs1
> WARNING: Dropping a sbspace.
> Do you really want to continue? (y/n)y
> The sbspace contains smart large objects.
> To drop this sbspace, you must force the drop by using the -f flag.
> Cannot drop the Space.
>
> The 'oncheck -pe siglob1dbs1' gave the results below :
>
> DBspace Usage Report: siglob1dbs1 Owner: informix Created: 11/13/2010
>
> Chunk Pathname Size Used Free
>
> 81 /bases/informix/pcoipcoi/app/deltasigv32/dat/d01/sig1_lo_01.dbf 2621438
> see
> below see below
>
> Description Offset Size
> ------------------------------------------------------------- --------
> --------
> RESERVED PAGES 0 2
> CHUNK FREELIST PAGE 2 1
> siglob1dbs1:'informix'.TBLSpace 3 50
> SBLOBSpace FREE USER DATA (AREA 1) 53 1224180
> siglob1dbs1:'informix'.sbspace_desc 1224233 4
> siglob1dbs1:'informix'.chunk_adjunc 1224237 4
> siglob1dbs1:'informix'.LO_ud_free 1224241 3374
> siglob1dbs1:'informix'.LO_hdr_partn 1227615 57675
> siglob1dbs1:'informix'.LO_ud_free 1285290 3374
> siglob1dbs1:'informix'.LO_hdr_partn 1288664 57676
> SBLOBSpace FREE META DATA 1346340 3374
> siglob1dbs1:'informix'.chunk_adjunc 1349714 4
> SBLOBSpace FREE META DATA 1349718 47540
> SBLOBSpace RESERVED USER DATA (AREA 2) 1397258 524276
> SBLOBSpace FREE USER DATA (AREA 2) 1921534 699904
>
> Total Used: 122164
> Total SBLOBSpace FREE META DATA: 50914
> Total SBLOBSpace FREE USER DATA: 2448360
>
> Chunk Pathname Size Used Free
>
> 82 /bases/informix/pcoipcoi/app/deltasigv32/dat/d02/sig1_lo_02.dbf 2621440
> see
> below see below
>
> Description Offset Size
> ------------------------------------------------------------- --------
> --------
> RESERVED PAGES 0 2
> CHUNK FREELIST PAGE 2 1
> SBLOBSpace FREE USER DATA (AREA 1) 3 1224204
> SBLOBSpace FREE META DATA 1224207 173028
> SBLOBSpace RESERVED USER DATA (AREA 2) 1397235 524284
> SBLOBSpace FREE USER DATA (AREA 2) 1921519 699921
>
> Total Used: 3
> Total SBLOBSpace FREE META DATA: 173028
> Total SBLOBSpace FREE USER DATA: 2448409
>
> Can someone explain why I can't remove this sbspace ?
>
> Thanks in advance
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01227df41de54804fbb50c10
Hi,
Thanks for your answer.
I ran dbschema on all my databases and found no objects created on this
sbspace (as u can see below)
#dbschema -d sysutils -ss > dbschema.sysutils
#dbschema -d sysuser -ss > dbschema.sysuser
#dbschema -d sysmaster -ss > dbschema.sysmaster
#dbschema -d sogebourse -ss > dbschema.sogebourse
#dbschema -d sgconserv -ss > dbschema.sgconserv
#dbschema -d sgbci99000 -ss > dbschema.sgbci99000
#dbschema -d param -ss > dbschema.sgbci99000
#dbschema -d param -ss > dbschema.param
#dbschema -d sgbci99000 -ss > dbschema.sgbci99000
#dbschema -d frombkcoma -ss > dbschema.frombkcoma
#dbschema -d cesssgbci99000 -ss > dbschema.cesssgbci99000
#dbschema -d bank -ss > dbschema.bank
#dbschema -d achats -ss > dbschema.achats
#grep siglob1dbs1 dbschema.*
#
It seems to me that this objects are system objects and not application
objects. Am I wrong ?
Best regards
Hi Art,
Thanks for your answer. It seems to me that the objects in this this
'siglob1dbs1' are system objects and not application objects. Am I wrong ?
siglob1dbs1:'informix'.
sbspace_desc 1224233 4
siglob1dbs1:'informix'.chunk_adjunc 1224237 4
siglob1dbs1:'informix'.LO_ud_free 1224241 3374
siglob1dbs1:'informix'.LO_hdr_partn 1227615 57675
siglob1dbs1:'informix'.LO_ud_free 1285290 3374
siglob1dbs1:'informix'.LO_hdr_partn 1288664 57676
siglob1dbs1:'informix'.chunk_adjunc 1349714 4
Further more, I ran dbschema on all my database and there's no object in the
bases that is realted to this siglob1dbs1 :
#dbschema -d sysutils -ss > dbschema.sysutils
#dbschema -d sysuser -ss > dbschema.sysuser
#dbschema -d sysmaster -ss > dbschema.sysmaster
#dbschema -d sogebourse -ss > dbschema.sogebourse
#dbschema -d sgconserv -ss > dbschema.sgconserv
#dbschema -d sgbci99000 -ss > dbschema.sgbci99000
#dbschema -d param -ss > dbschema.sgbci99000
#dbschema -d param -ss > dbschema.param
#dbschema -d sgbci99000 -ss > dbschema.sgbci99000
#dbschema -d frombkcoma -ss > dbschema.frombkcoma
#dbschema -d cesssgbci99000 -ss > dbschema.cesssgbci99000
#dbschema -d bank -ss > dbschema.bank
#dbschema -d achats -ss > dbschema.achats
#grep siglob1dbs1 dbschema.*
#
Regards
Gilles: I agree, these are likely all system objects. Doesn't change the
fact that they exist and are preventing the sbspace from dropping. I would
go with the advice to open a case with IBM support.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Jun 13, 2014 at 9:04 AM, GILLES TCHAPPI <giltfr@yahoo.fr> wrote:
> Hi Art,
>
> Thanks for your answer. It seems to me that the objects in this this
> 'siglob1dbs1' are system objects and not application objects. Am I wrong ?
>
> siglob1dbs1:'informix'.
>
> sbspace_desc 1224233 4
>
> siglob1dbs1:'informix'.chunk_adjunc 1224237 4
>
> siglob1dbs1:'informix'.LO_ud_free 1224241 3374
>
> siglob1dbs1:'informix'.LO_hdr_partn 1227615 57675
>
> siglob1dbs1:'informix'.LO_ud_free 1285290 3374
>
> siglob1dbs1:'informix'.LO_hdr_partn 1288664 57676
>
> siglob1dbs1:'informix'.chunk_adjunc 1349714 4
>
> Further more, I ran dbschema on all my database and there's no object in
> the
> bases that is realted to this siglob1dbs1 :
>
> #dbschema -d sysutils -ss > dbschema.sysutils
>
> #dbschema -d sysuser -ss > dbschema.sysuser
>
> #dbschema -d sysmaster -ss > dbschema.sysmaster
>
> #dbschema -d sogebourse -ss > dbschema.sogebourse
>
> #dbschema -d sgconserv -ss > dbschema.sgconserv
>
> #dbschema -d sgbci99000 -ss > dbschema.sgbci99000
>
> #dbschema -d param -ss > dbschema.sgbci99000
>
> #dbschema -d param -ss > dbschema.param
>
> #dbschema -d sgbci99000 -ss > dbschema.sgbci99000
>
> #dbschema -d frombkcoma -ss > dbschema.frombkcoma
>
> #dbschema -d cesssgbci99000 -ss > dbschema.cesssgbci99000
>
> #dbschema -d bank -ss > dbschema.bank
>
> #dbschema -d achats -ss > dbschema.achats
>
> #grep siglob1dbs1 dbschema.*
>
> #
>
> Regards
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f235015b9b7fe04fbb76ac7
Art, Thanks a lot Best regards