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