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
------------------------------
Original Message:
Sent: Thu March 31, 2022 11:56 AM
From: Kate Tomchik
Subject: Smartblob spaces
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
Original Message:
Sent: 3/2/2022 12:10:00 PM
From: Kate Tomchik
Subject: 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