Is there a way to check the sbspace usage by table?

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 blobdbs1BLOBspace 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

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------