Informix

 View Only
  • 1.  Is there a way to check the sbspace usage by table?

    Posted Wed January 06, 2021 01:40 AM
    Edited by System Fri January 20, 2023 04:13 PM
    Hello All,
    I want to know the usage of sbspace by table.
    In the case of BLOBspace, it is possible to check the usage for each table with the oncheck -pe command.


    ---- checking utilization for table using BLOBspace

    $ dbschema -d testdb -t mycatalog
    ...
    create table "informix".mycatalog
    (
    catalog_num serial not null ,
    stock_num smallint,
    manu_code char(3),
    cat_descr text in blobdbs1,
    cat_picture byte in blobdbs1,
    cat_advert varchar(255,65)
    );

    $ oncheck -pe blobdbs1

    BLOBspace Usage Report: blobdbs1 Owner: informix Created: 02/29/2020


    Chunk Pathname Size Used Free
    14 /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_blobdbs1_p_1 32 25000 9480 15520

    Disk usage for Chunk 14 Total Pages
    -------------------------------------------------------------------------------
    OVERHEAD 8
    testdb:'informix'.mycatalog 9472
    FREE 15520


    Is there a way to calculate or view usage for each table with BLOB or CLOB data entered in sbspace?
    For sbspace, the usage per row(column?) is displayed with the command onstat -g smb e, but I want to know if there is a way to check for each table.


    Thanks,


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------
    #Informix


  • 2.  RE: Is there a way to check the sbspace usage by table?

    IBM Champion
    Posted Wed January 06, 2021 06:03 AM
    The best you can do, I think, is to query the lengths of every BLOB or CLOB in each table and add them up. You can do that if you import the excompat datablade which contains the dbms_lob_getlength() function which returns the length of the BLOB or CLOB passed to it. I have overloaded the length() function in my databases with the underlying binary function so I can just use length(BLOBcol):

    CREATE FUNCTION  "art".length ( blob ) returns integer
                    external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)" language c;           

    CREATE FUNCTION  "art".length ( clob ) returns integer
                    external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)" language c;           
    ​_


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Is there a way to check the sbspace usage by table?

    Posted Wed January 06, 2021 11:10 AM
    Hello, there is also article on how to get sbspace number from a slob column
    How to determine which sbspace a table's sblobs reside in
    therefore you can combine size and sbspace number/sbspace name as well.
    I did in the past some sql to get more info on sblobs from tables as customer needed to move sblobs to another sbspace
    or to find out to which table some sblob identified by LO (sbspace, chunk, rid) is belong to.
    Currently I am quite busy but I will try to find and send as an example when spare time.

    ------------------------------
    Milan Rafaj
    ------------------------------



  • 4.  RE: Is there a way to check the sbspace usage by table?

    Posted Thu January 14, 2021 09:36 AM
    Another fun option if you don't mind having the Java Virtual machine spin up we have these functions built in there as well (latest large object functions shown below are in 14.10.FC4 or higher). You just have to register them in your database.

    > create table lob(a clob);

    Table created.

    > CREATE FUNCTION lobSize(CLOB) RETURNS BIGINT EXTERNAL NAME 'com.informix.judrs.LargeObjects.lobSize(java.sql.Clob)' LANGUAGE JAVA;

    Routine created.

    > create function toclob(varchar(255)) returns clob external name 'com.informix.judrs.LargeObjects.toClob(java.lang.String)' LANGUAGE JAVA;

    Routine created.

    > insert into lob values(toclob('Hello there'));

    1 row(s) inserted.

    > insert into lob values(toclob('lets add some rows'));

    1 row(s) inserted.

    > insert into lob values(toclob('into our clob'));

    1 row(s) inserted.

    > select lobsize(a) from lob;


    (expression)

    11
    18
    13

    3 row(s) retrieved.

    > select sum(lobsize(a)) from lob;


    (sum)

    42

    1 row(s) retrieved.

    ------------------------------
    Brian Hughes
    ------------------------------



  • 5.  RE: Is there a way to check the sbspace usage by table?

    IBM Champion
    Posted Tue January 12, 2021 08:02 AM
    Technically, an sblob doesn't belong to a table, but rather is referenced by an entry in a table, or by multiple such entries, from same or from different tables. Hence the concept of "sbspace usage by table" isn't really supported, even though the desire for viewing it this way is understandable.

    E.g. if you copy a table comprising an sblob column to a new table within same instance, using simple "insert into ... select * ...", you'd not actually copy the sblob data, but rather the references to those sblobs - so how much sblob space would be used by either of these two tables now?

    A good practice probably would be having separate sbspaces for different tables, maybe even their various sblob columns, in which case you'd have a better handle on tables' sbspace consumption.  Of course, as soon as you start copying like mentioned above, you'd also start blurring the lines.

    ------------------------------
    Andreas Legner
    ------------------------------



  • 6.  RE: Is there a way to check the sbspace usage by table?

    Posted Wed January 13, 2021 02:19 AM

    Interesting. I didn't know this. Have not used sblobs much in the past, but we have just created one now recently.

    In your scenario with copying an sblob column, does the engine keep track of how many references there are to each particular sblob, and only deleting the sblob when there are no more references to it?
    Or will it be deleted when the original row pointing to it is deleted, thus rendering the copied row useless?



    ------------------------------
    Øyvind Gjerstad
    Developer/Architect
    PostNord AS
    ------------------------------



  • 7.  RE: Is there a way to check the sbspace usage by table?

    IBM Champion
    Posted Wed January 13, 2021 04:11 PM
    Yep, but only by means of a reference counter on each sblob, so no (bullet proof) way for finding the table(s) for a given sblob.

    ------------------------------
    Andreas Legner
    ------------------------------



  • 8.  RE: Is there a way to check the sbspace usage by table?

    Posted Thu January 14, 2021 07:01 PM
    Hello,

    I think there is a way how to find tables/columns with specific reference of given slob (if this sblob is really db table sblob and no the other one (like ER spooled txn object for example).
    The following script generates sql script containing list of sql select commands which can return specific table/columns/rowid for given db and sblob id (sbspacenum, chunknum, lonum). Not elegant, can be improved, but working.
    #1. parameter - dbname
    #2. parameter - sbspace num
    #3. parameter - chunk num chunku
    #4. parameter - LO num
    echo "set isolation to dirty read;" >find_table_of_sblob_generated_${1}_${2}_${3}_${4}.sql
    dbaccess $1 <<EOF
    --select tabname, colname, rowid
    unload to find_table_of_sblob_generated_${1}_${2}_${3}_${4}.xxx delimiter ";"
    select 'select "'||trim(tabname)||'", "'||trim(colname)||'", "$2", "$3", "$4", rowid FROM '||trim(tabname)||' WHERE ("0x" || substr('||trim(colname)||'::lvarchar,17,8))::INT = $2 AND ("0x" || substr('||trim(colname)||'::lvarchar,25,8))::INT = $3 AND ("0x" || substr('||trim(colname)||'::lvarchar,33,8))::INT = $4'
    from systables t,syscolumns c, sysxtdtypes e
    where
    t.tabid = c.tabid
    and c.extended_id = e.extended_id
    and e.name in ('clob','blob')
    --order by tabname,colname
    EOF
    cat find_table_of_sblob_generated_${1}_${2}_${3}_${4}.xxx >>find_table_of_sblob_generated_${1}_${2}_${3}_${4}.sql
    rm find_table_of_sblob_generated_${1}_${2}_${3}_${4}.xxx
    # end of script

    Script generates​ sql file which gives you table, columns and rowid of table which references this sblob, if exist in this db.
    If you run script and generated sql for all databases you will find all tables in all databases which reference this specific sblob.

    Info on sblobs and reference count you can get from
    onspaces -cS <sbspacename> or from
    onstat -g smb lod

    ------------------------------
    Milan Rafaj
    ------------------------------