Smartblob spaces

Hey all, We have a smart blob space that has reached 100% for a vendor package TWS. We don't know what is in this space. If this was a data space, we would repack and shrink. If it was an index space we would drop and rebuild some of the larger indexes. Is there anything like this you can do to a smartblob? -- Thanks, Kate Tomchik She/Her/Hers Principal Systems Engineer, Database Solutions INTERNAL USE #Informix

cat /dev/null > chunkpath will release all the space

Is it the default sbspace or a separate one? You can always check dbschema to see if any tables have clob/blob columns. You can also query syscolumns and look for coltype 41 Normally if you need that data, just adding an additional chunk is the next step. How full is the metadata space by the way? ------------------------------ Thomas Beebe Vice President xDB Systems, Inc Woodbridge VA 5713399029 ------------------------------

I suggest the following: 1. dbschema -d dbname -ss > dbname.sql 2. cat dbname.sql|grep -iE 'clob|blob' #you can get tabname which table have a smart object 3. unload to tabname.unl select * from tabname #You can find out which table's intelligent large object takes up a lot of space You need to pay attention to the space usage of user data and metadata； and you delete or truncate a table contain smart object,You may encounter longTX good lucky ------------------------------ ZhiWei Cui GBASE ------------------------------

You working for Russia now Paul? ------------------------------ Kate Tomchik ------------------------------

Hi ZhiWeiCui, yes, checking the DB schema is the fastest way to get clarity about the usage. After that you can use the "oncheck -pt dbname:tabname" and the "onstat -d update" to clarify the storage space used. Regards ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 ------------------------------

I am far from an expert, but, have also been working on some BLOB/CLOB issues, recently. I offer this, for what it may be worth (and with the caveat that my understanding is evolving): If you query syscolumns for coltype=41, you can get more rows then you might want, and you can fail to get some rows you might want. For the first case ("extra" rows)... coltype=41 will also return datatype BOOLEAN. For the second case ("missed" rows)... coltype-41 will fail to return BLOB/CLOB columns that have NOT NULL constraints. Smart Large Objects with NOT NULL constraints have coltype=297 (41 + 256, which is a bit-mapped flag for "not null"). To query syscolumns for all BLOB/CLOB types (including NOT NULL) and only BLOB/CLOB (excluding coltype=41 which are not BLOB/CLOB), I am also filtering for "syscolumns.extended_id IN (10, 11)". Just for convenience, I like to have the type stated clearly, so I use this: SELECT UPPER(x.name), t.tabname, c.colname FROM systables t, syscolumns c, sysxtdtypes x WHERE t.tabid=c.tabid AND x.extended_id=c.extended_id AND c.extended_id IN (10, 11) --See Ifmx docs: syscolumns.coltype not unique for Smart LOBs. Use extended_id. AND t.tabid>99; DG Edit: P.S. Removed "extended_id" from SELECT. (I had it in while I was writing it.) ------------------------------ David Grove ------------------------------

Yes I know which table is causing the problem (see details below). We can purge this table of old records, but it still shows the s001 dbspace as 100% full, so the clobs are either 1) not being removed or 2) not being reorged/repacked/shrinked (whatever it is for blobs) to show the space is now free. create table "LOG".lrpr_record_properties ( lrpr_object_id char(32) not null , lrpr_property_name varchar(50) not null , llrc_id decimal(20,0) not null , llrc_category char(1) not null , lrpr_property_value "informix".clob, primary key (llrc_id,lrpr_object_id,lrpr_property_name) constraint "informix".pk_lrpr ) PUT lrpr_property_value in ( s001 ) extent size 32 next size 32 lock mode row; revoke all on "LOG".lrpr_record_properties from "public" as "LOG";create index "LOG".i_lrpr_01 on "LOG".lrpr_record_properties (llrc_id) using btree in d001; alter table "LOG".lrpr_record_properties add constraint (foreign key (llrc_id) references "LOG".llrc_log_records on delete cascade constraint "informix".fk_lrpr_llrc_01); Unload of tables using s001 showed the problem table to be the properties one: set isolation to dirty read;-- sql to find tables that are filling the smart blob spaces -- lkt01 Mar 4, 2022 --unload to /opt/hd/db/tmp/ s001 /log.lprp_record_properties select * from "LOG".lprp_record_properties; I got these files in the filesystem created. It is clearly the LOG.lprp_record_properties table that is taking all the space. stuxsh02.st7001:/opt/hd/db/tmp/ s001 > ls -ltr total 5857 -rw-rw-r-- 1 informix informix 2217537 Mar 4 17:14 LOG.lrpr_record_properties – table that is growing -rw-rw-r-- 1 informix informix 2728616 Mar 4 17:14 clob1698.9a4 – blobs going to s001 -- Thanks, Kate Tomchik She/Her/Hers Principal Systems Engineer, Database Solutions The Home Depot Cell: 678-427-4914 (Text preferred) "The success of every woman should be the inspiration to another. We should raise each other up. Make sure you're very courageous: be strong, be extremely kind, and above all be humble." – Serena Williams , professional tennis player who has won more singles titles than any man or woman For non-urgent requests create a TICKET: https://servicecatalog.apps.homedepot.com/home/catalog/Databases/databasesupport Choose experience: IT-08034-Database Solutions Operations

Kate: What do the sysmaster:syschunks rows for the SBSpace chunks show? Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hello Kate, You can use oncheck -pe s001and oncheck -cS s001 # column Ref Cnt shows if blob is referenced more than once, ie insert into t2 select blobcol from t1 do not copy blob but increase ref countto get info on LO of all blob objects in s001 also check in output of oncheck -cS if sblobsbs:'informix'.LO_hdr_partn is not full (metadata table) and than script below gives you list of all LOs in that table. If can help to locate the problem. # p1 dbname # p2 tabname # p3 colname dbaccess $1 <<EOF select "$2" tabname,"$3" colname, ("0x"||substr($3::lvarchar,17,8))::integer sbs, ("0x"||substr($3::lvarchar,25,8))::int sbc, ("0x"||substr($3::lvarchar,33,8))::int seq, rowid from $2 where $3 is not null EOF Milan Rafaj www.kyndryl.com Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)

Hi, in case you want to make sure the table data is not filling up your (possible too small) sblob space s001, I would recommend recreation of the table by using a new defined sblob space and unload/load the data. In case you already deleted the data and the sblob is still filled, check for other references. The table can be created with a different name and same structure initially (maybe adjust first/next extent sizes if needed), let the clob point to a new dimensioned sblob space which is created with onspaces -c -S before. Then unload/load the data from original table into the new one (unload to file select * from oldtable; load from file insert into newtable). After completion, drop old table (or rename it temporarily in order to have a backup, drop the foreign key constraint in this case), rename the new table to the original name and recreate constraint to the main table. That way, the old sblob should be cleared (when the old table is dropped). When load is not possible because of long transaction, you should make sure enough log space is present. Or you can load the table in multiple steps. Just make sure using dbschema -ss that no other table has a reference to the sblob space s001, which can be dropped after this copying. I am not sure if it would be possible to modfiy the column storage using an alter table statement instead. It might be only resulting in new records to be stored at the new sblob location and not the old records copied to the new location. Check https://www.ibm.com/docs/en/informix-servers/14.10?topic=statement-put-clause for details. Good luck, ------------------------------ Marcus Haarmann ------------------------------

Hi, as to sblob movement to another subspace, if LLD datablade is registered, procedure could be: # p1 dbname # p2 tablename # p3 colname # p4 new sblobspacename dbaccess $1 <<EOF alter table $2 put $3 in ($4); -- new data in new sblobspace update $2 set $3 = locopy($3,"$2","$3") where $3 is not null; -- move old data to new sblobspace -- or divide update command to more transactions EOF locopy function copies sblob not just referencing ------------------------------ Milan Rafaj Infrastructure Architects and Specialists Team Leader Kyndryl +420737264248 ------------------------------