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
------------------------------
Original Message:
Sent: Wed January 13, 2021 04:11 PM
From: Andreas Legner
Subject: Is there a way to check the sbspace usage by table?
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
Original Message:
Sent: Wed January 13, 2021 02:19 AM
From: Øyvind Gjerstad
Subject: Is there a way to check the sbspace usage by table?
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
Original Message:
Sent: Tue January 12, 2021 08:02 AM
From: Andreas Legner
Subject: Is there a way to check the sbspace usage by table?
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
Original Message:
Sent: Wed January 06, 2021 01:40 AM
From: SangGyu Jeong
Subject: 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 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