Informix

 View Only
Expand all | Collapse all

Smartblob spaces

  • 1.  Smartblob spaces

    InnerCircle
    Posted Wed March 02, 2022 12:10 PM

    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



  • 2.  RE: Smartblob spaces

    Posted Wed March 02, 2022 12:13 PM

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

     






  • 3.  RE: Smartblob spaces

    InnerCircle
    Posted Thu March 03, 2022 11:26 AM
    You working for Russia now Paul?

    ------------------------------
    Kate Tomchik
    ------------------------------



  • 4.  RE: Smartblob spaces

    IBM Champion
    Posted Wed March 02, 2022 12:31 PM
    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
    ------------------------------



  • 5.  RE: Smartblob spaces

    Posted Wed March 30, 2022 06:49 PM
    Edited by David Grove Wed March 30, 2022 07:12 PM
    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
    ------------------------------



  • 6.  RE: Smartblob spaces

    Posted Thu March 03, 2022 08:44 AM
    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
    ------------------------------



  • 7.  RE: Smartblob spaces

    IBM Champion
    Posted Fri March 04, 2022 02:39 AM
    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
    ------------------------------



  • 8.  RE: Smartblob spaces

    InnerCircle
    Posted Thu March 31, 2022 11:56 AM

    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

     

     






  • 9.  RE: Smartblob spaces

    IBM Champion
    Posted Thu March 31, 2022 01:57 PM
    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
    ------------------------------