Jacob:
I did a simple test like below. As a result of testing, I could infer some facts.
Please point out what is wrong or what I misunderstand.
1. If a BLOBSpace is specified in the IN clause when defining a BYTE or TEXT column, the corresponding BLOBSpace name is displayed in the spacename column of the sysblobs table.
2. If IN TABLE is used or omitted when defining BYTE or TEXT columns, the name of the DBSpace where the database to which the table belongs is created is displayed in the spacename column of the sysblobs table.
3. If IN TABLE is used or omitted when defining a BYTE or TEXT column, BYTE or TEXT data is not stored in the DBSpace indicated in the spacename column of the sysblobs table, but in the DBSpace defined for the table.
/work2/INFORMIX/1210FC13/skjeong]onstat -d update
IBM Informix Dynamic Server Version 12.10.FC13WE -- On-Line -- Up 11 days 23:35:09 -- 218096 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
700000020478028 1 0x14020001 1 1 4096 N BAE informix rootdbs
7000000205907a8 2 0x15000001 2 1 4096 N PBAE informix plog
7000000205909e8 3 0x14000001 3 1 4096 N BAE informix llog
700000020590c28 4 0x14000001 4 1 4096 N BAE informix datadbs1
7000000226fb028 5 0x14000001 5 1 4096 N BAE informix datadbs2
7000000226fb268 6 0x14000001 6 1 4096 N BAE informix datadbs3
7000000226fb4a8 7 0x14000001 7 1 8192 N BAE informix data8dbs1
7000000226fb6e8 8 0x14000001 8 1 8192 N BAE informix data8dbs2
7000000226fb928 9 0x14000001 9 1 8192 N BAE informix data8dbs3
7000000226fbb68 10 0x14002001 10 1 4096 N TBAE informix tmpdbspace
7000000226fbda8 11 0x14008001 11 1 4096 N SBAE informix sbspace1
7000000226fd028 12 0x1400a001 12 1 4096 N UBAE informix tmpsbspace
7000000226fd268 13 0x14000001 13 1 4096 N BAE informix dbs1
700000025c03d10 14 0x14020011 14 1 32768 N BBAE informix blobdbs1
14 active, 2047 maximum
Waiting for server to update BLOB chunk statistics...
Chunks
address chunk/dbs offset size free bpages flags pathname
700000020478268 1 1 0 38912 18864 PO-B-C /work2/INFORMIX/1210FC13/storage/rootdbs
7000000226fd4a8 2 2 0 18455 2071 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_plog_p_1
7000000226fe028 3 3 0 56569 8900 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_llog_p_1
7000000226ff028 4 4 0 16384 8328 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs1_p_1
700000022700028 5 5 0 16384 8721 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs2_p_1
700000022701028 6 6 0 16384 14112 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs3_p_1
700000022702028 7 7 0 8192 7221 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs1_p_1
700000022706028 8 8 0 8192 7429 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs2_p_1
700000022707028 9 9 0 8192 7437 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs3_p_1
700000022708028 10 10 0 16384 16331 PO-BE- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpdbspace_p_1
700000022709028 11 11 0 16384 11822 11879 POSB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_sbspace1_p_1
Metadata 868 4138 868
70000002270a028 12 12 0 16384 11879 11879 POSB-- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpsbspace_p_1
Metadata 868 4138 868
70000002270b028 13 13 0 25000 9617 PO-B-C /work2/INFORMIX/1210FC13/storage/dbs1
700000023285028 14 14 0 25000 3124 3125 POBB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_blobdbs1_p_1
14 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
/work2/INFORMIX/1210FC13/skjeong]dbaccess - -
> create database testdb in datadbs1 with log;
Database created.
> create table "informix".mycatalog
> (
> catalog_num serial not null ,
> stock_num smallint,
> manu_code char(3),
> cat_descr text in table,
> cat_picture byte in blobdbs1,
> cat_advert varchar(255,65)
> ) in data8dbs1;
Table created.
> select t.tabname[1,18], b.tabid, c.colname[1,18], b.colno,
> b.type, b.spacename[1,10]
> from sysblobs b, systables t, syscolumns c
> where b.tabid = t.tabid
> and b.colno = c.colno
> and t.tabid = c.tabid
> order by tabname, colname;
tabname tabid colname colno type spacename
mycatalog 100 cat_descr 4 M datadbs1
mycatalog 100 cat_picture 5 M blobdbs1
sysdirectives 48 directive 3 M
sysdirectives 48 directivecode 4 M
sysdirectives 48 query 2 M
sysfragments 25 exprarr 12 M
sysfragments 25 exprbin 11 M
sysfragments 25 exprtext 10 M
8 row(s) retrieved.
> ^C/work2/INFORMIX/1210FC13/skjeong]onstat -d update
IBM Informix Dynamic Server Version 12.10.FC13WE -- On-Line -- Up 11 days 23:36:04 -- 218096 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
700000020478028 1 0x14020001 1 1 4096 N BAE informix rootdbs
7000000205907a8 2 0x15000001 2 1 4096 N PBAE informix plog
7000000205909e8 3 0x14000001 3 1 4096 N BAE informix llog
700000020590c28 4 0x14000001 4 1 4096 N BAE informix datadbs1
7000000226fb028 5 0x14000001 5 1 4096 N BAE informix datadbs2
7000000226fb268 6 0x14000001 6 1 4096 N BAE informix datadbs3
7000000226fb4a8 7 0x14000001 7 1 8192 N BAE informix data8dbs1
7000000226fb6e8 8 0x14000001 8 1 8192 N BAE informix data8dbs2
7000000226fb928 9 0x14000001 9 1 8192 N BAE informix data8dbs3
7000000226fbb68 10 0x14002001 10 1 4096 N TBAE informix tmpdbspace
7000000226fbda8 11 0x14008001 11 1 4096 N SBAE informix sbspace1
7000000226fd028 12 0x1400a001 12 1 4096 N UBAE informix tmpsbspace
7000000226fd268 13 0x14000001 13 1 4096 N BAE informix dbs1
700000025c03d10 14 0x14020011 14 1 32768 N BBAE informix blobdbs1
14 active, 2047 maximum
Waiting for server to update BLOB chunk statistics...
Chunks
address chunk/dbs offset size free bpages flags pathname
700000020478268 1 1 0 38912 18864 PO-B-C /work2/INFORMIX/1210FC13/storage/rootdbs
7000000226fd4a8 2 2 0 18455 2071 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_plog_p_1
7000000226fe028 3 3 0 56569 8900 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_llog_p_1
7000000226ff028 4 4 0 16384 7200 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs1_p_1
700000022700028 5 5 0 16384 8721 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs2_p_1
700000022701028 6 6 0 16384 14112 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs3_p_1
700000022702028 7 7 0 8192 7221 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs1_p_1
700000022706028 8 8 0 8192 7429 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs2_p_1
700000022707028 9 9 0 8192 7437 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs3_p_1
700000022708028 10 10 0 16384 16331 PO-BE- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpdbspace_p_1
700000022709028 11 11 0 16384 11822 11879 POSB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_sbspace1_p_1
Metadata 868 4138 868
70000002270a028 12 12 0 16384 11879 11879 POSB-- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpsbspace_p_1
Metadata 868 4138 868
70000002270b028 13 13 0 25000 9617 PO-B-C /work2/INFORMIX/1210FC13/storage/dbs1
700000023285028 14 14 0 25000 3124 3125 POBB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_blobdbs1_p_1
14 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
/work2/INFORMIX/1210FC13/skjeong]dbaccess testdb -
Database selected.
> load from /work2/skjeong/1150scripts/catalog.unl insert into mycatalog;
74 row(s) loaded.
/work2/INFORMIX/1210FC13/skjeong]onmode -l;onmode -l;ontape -s -L 0 -t /dev/null
Archive to tape device '/dev/null' is complete.
Program over.
/work2/INFORMIX/1210FC13/skjeong]onstat -d update
IBM Informix Dynamic Server Version 12.10.FC13WE -- On-Line -- Up 11 days 23:36:45 -- 218096 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
700000020478028 1 0x14020001 1 1 4096 N BAE informix rootdbs
7000000205907a8 2 0x15000001 2 1 4096 N PBAE informix plog
7000000205909e8 3 0x14000001 3 1 4096 N BAE informix llog
700000020590c28 4 0x14000001 4 1 4096 N BAE informix datadbs1
7000000226fb028 5 0x14000001 5 1 4096 N BAE informix datadbs2
7000000226fb268 6 0x14000001 6 1 4096 N BAE informix datadbs3
7000000226fb4a8 7 0x14000001 7 1 8192 N BAE informix data8dbs1
7000000226fb6e8 8 0x14000001 8 1 8192 N BAE informix data8dbs2
7000000226fb928 9 0x14000001 9 1 8192 N BAE informix data8dbs3
7000000226fbb68 10 0x14002001 10 1 4096 N TBAE informix tmpdbspace
7000000226fbda8 11 0x14008001 11 1 4096 N SBAE informix sbspace1
7000000226fd028 12 0x1400a001 12 1 4096 N UBAE informix tmpsbspace
7000000226fd268 13 0x14000001 13 1 4096 N BAE informix dbs1
700000025c03d10 14 0x14020011 14 1 32768 N BBAE informix blobdbs1
14 active, 2047 maximum
Waiting for server to update BLOB chunk statistics...
Chunks
address chunk/dbs offset size free bpages flags pathname
700000020478268 1 1 0 38912 18864 PO-B-C /work2/INFORMIX/1210FC13/storage/rootdbs
7000000226fd4a8 2 2 0 18455 2071 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_plog_p_1
7000000226fe028 3 3 0 56569 8900 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_llog_p_1
7000000226ff028 4 4 0 16384 7200 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs1_p_1
700000022700028 5 5 0 16384 8721 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs2_p_1
700000022701028 6 6 0 16384 14112 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_datadbs3_p_1
700000022702028 7 7 0 8192 7213 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs1_p_1
700000022706028 8 8 0 8192 7429 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs2_p_1
700000022707028 9 9 0 8192 7437 PO-BEC /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_data8dbs3_p_1
700000022708028 10 10 0 16384 16331 PO-BE- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpdbspace_p_1
700000022709028 11 11 0 16384 11822 11879 POSB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_sbspace1_p_1
Metadata 868 4138 868
70000002270a028 12 12 0 16384 11879 11879 POSB-- /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_tmpsbspace_p_1
Metadata 868 4138 868
70000002270b028 13 13 0 25000 9617 PO-B-C /work2/INFORMIX/1210FC13/storage/dbs1
700000023285028 14 14 0 25000 3050 3125 POBB-C /work2/INFORMIX/1210FC13/storage/ol_informix1210_3_blobdbs1_p_1
14 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
/work2/INFORMIX/1210FC13/skjeong]------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Original Message:
Sent: Fri February 28, 2020 02:57 PM
From: Jacob Salomon
Subject: sysblobs.spacename: partition, dbspace, or family
Hi Y'all.
My current client has a fair number of tables with text and binary blob columns. However, no blob spaces; all blob column are in the TBLspace with the row. (Of course on pages separate from the row.) So I got curious about the way they are tracked and I ran this query:
select t.tabname[1,18], b.tabid, c.colname[1,18], b.colno,
b.type, b.spacename[1,10]
from sysblobs b, systables t, syscolumns c
where b.tabid = t.tabid
and b.colno = c.colno
and t.tabid = c.tabid
order by tabname, colname
;
Here is a partial result of the above, with names changed:
Now, the system catalogs sysfragments and sysdirectives have 3 blob columns each and all the values for column spacename are null. This makes sense because, of course, there was no blobspace designation when the catalog was created. But all of the user-level blob columns are the same way - in TBLspace, as I mentioned above - are listed in the same DBspace, named abcxyz7. (Name changed to avoid concerns of confidentiality.) That DBspace exists but none of the tables returned by my query have any partitions in abcxyz7. This value should also be null for these tables!
According to the meager explanation in the knowledge base, the spacename column may be "Name of partition, dbspace, or family". Since none of these blob-hosting tables are partitioned, the partition name would be irrelevant anyway.
But what in blazes the this "family" the knowledge base refers to? And what could it possible have to do with that totally disconnected DBspace? I need a better explanation of how the value gets set in sysblobs.spacename.
Anybody got one? :-)
Thanks for some enlightenment.
------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra
------------------------------
#Informix