IDS Forum

sysblobs.spacename misleading

Posted By: JACOB SALOMON
Date: Sunday, 10 January 2016, at 6:43 p.m.

Hi family.

I trying to improve on an already pretty good script (IMHO, of course :-) I am trying to get information about any blobspace blobs (test and byte; laeving smart stuff out of this) and I ran the following query:
select t.tabname[1,20], c.colname[1,20], b.spacename[1,15]
from systables t, syscolumns c, sysblobs b
where t.tabid = c.tabid

and c.tabid = b.tabid

and c.colno = b.colno
order by tabname, colname ;

This returned exactly 11 rows, exactly the number of rows in sysblobs. Among those rows:
tabname colname spacename
form_letters body1 rptdbs
form_letters body2 rptdbs
sysfragments exprarr
sysfragments exprbin
sysfragments exprtext

(Unfortunately, the formatting is messed up when this message is posted.)

Note that for sysfragments, no spacename is mentioned. This makes sense, since the text columns are in the table. (I can't run dbschema against the system catalogs to confirm this but that's another story.) On the other hand, for table form_letters, those two columns are listed as being in a DBspace named rptdbs. And that's wrong on a couple of levels:
1. DBspace rptdbs is, in fact, the DBspace wherein the database was created.
2. My server has no blob spaces at all.
3. The dbschema of table form_letters merely lists the TEXT columns as "text"; it does not specify an "in <dbspace>" clause for the text column. (Interestingly enough, it also does not show "in table", which would be the default anyway for a text/byte column.)
4. The table actually resides in another DBspace entirely, with its indexes in yet another, neither where the database was created.

Now, I *could* refine my sysblobs query to list only TRUE blobspace blobs by adding this clause:
and c.spacename in (select name from sysmaster:sysdbspaces where is_blobspace = 1)

But I'm trying to get a handle on the correct meaning the spacename column in sysfragments. Why do I get the name of the database's home DBspace?

Seeking the guidance of the true gurus,
-- Jacob S.

IDS Forum is maintained by Administrator with WebBBS 5.12.