Who is using a smartblobspace
Ulf wanted to find which tables use a particular smart blobspace (11.70.FC7) without hand-checking every schema. Suggestions: oncheck -pe (didn't identify tables) and a join of systables/syscolattribs on sbspace — which only works when the column was created with PUT IN; columns defaulting to SBSPACENAME have no syscolattribs row. Art then supplied a UNION query listing BLOB/CLOB columns (extended_id 10,11) with their explicit sbspace, plus columns with none matched against SBSPACENAME from sysmaster:sysconfig. Ulf got it working after dropping the coltype=40 test and adding tabtype='T'.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi! Version 11.70.FC7 Does anybody know how I can find out which tables are using a specific smartblobspace? I can tell that the space is being used but the only way I have found so far to find out who is using it is to browse all schemas and look for blob/clob declarations and then if there is a PUT IN declaration. Is there an easier way? TIA Ulf
Hello.
Have you tried a oncheck -pe [sbspace_name] command?
It should show you all objects inside the referred space.
Hope it helps.
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10
IBM Information Management Informix Technical Professional
IBM Infosphere DataStage Technical Professional
Informix Senior DBA - Orizon Brasil
BRIUG website administrator
Informix independent consultant
> To: ids@iiug.org
> From: ulf.akerberg@migrationsverket.se
> Subject: Who is using a smartblobspace [31731]
> Date: Wed, 16 Oct 2013 07:29:21 -0400
>
> Hi!
>
> Version 11.70.FC7
>
> Does anybody know how I can find out which tables are using a specific
> smartblobspace?
>
> I can tell that the space is being used but the only way I have found so far
> to find out who is using it is to browse all schemas and look for blob/clob
> declarations and then if there is a PUT IN declaration.
>
> Is there an easier way?
>
> TIA
>
> Ulf
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
select tabname
from systables st, syscolattribs sa
where st.tabid = sa.tabid
and sa.sbspace = "mysbspace";
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Oct 16, 2013 at 7:29 AM, ULF ÅKERBERG <
ulf.akerberg@migrationsverket.se> wrote:
> Hi!
>
> Version 11.70.FC7
>
> Does anybody know how I can find out which tables are using a specific
> smartblobspace?
>
> I can tell that the space is being used but the only way I have found so
> far
> to find out who is using it is to browse all schemas and look for blob/clob
> declarations and then if there is a PUT IN declaration.
>
> Is there an easier way?
>
> TIA
>
> Ulf
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d042ac0e830ec0e04e8da5977
Thanks Art and Alexandre.
Tried both suggestions with the following results:
- the select does show a correct result if you have used a PUT IN, if not the
data ends up in the default SBSPACE (which is the problem in my case) and
there is nothing in syscolattribs.
- the oncheck -pe does not give any hint as to which table/database that uses
the sbspace as far as I can tell.
Any other ideas?
Ulf
You can track the default sbspace from sysmaster:sysconfig if you need it
in a query:
select cf_effective
from sysmaster:sysconfig
where cf_name = 'SBSPACENAME';
But if you need to identify tables with BLOB and CLOB columns you can:
select tabname, colname
from systables st, syscolumns sc
where st.tabid = sc.tabid
and sc.coltype = 40
and sc.extended_id in (10,11);
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Oct 16, 2013 at 8:10 AM, ULF ÅKERBERG <
ulf.akerberg@migrationsverket.se> wrote:
> Thanks Art and Alexandre.
>
> Tried both suggestions with the following results:
>
> - the select does show a correct result if you have used a PUT IN, if not
> the
> data ends up in the default SBSPACE (which is the problem in my case) and
> there is nothing in syscolattribs.
>
> - the oncheck -pe does not give any hint as to which table/database that
> uses
> the sbspace as far as I can tell.
>
> Any other ideas?
>
> Ulf
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11335e5c86e75304e8dcbfaa
Oops that got away from me. I wanted to add: you could combine this with my
original query to get all of the info:
select tabname, colname, (
select cf_effective::varchar(128) from sysmaster:sysconfig where
cf_name = 'SBSPACENAME'
) as sbspace -- SLOB columns with no explicit sbspace
from systables st
join syscolumns sc
on st.tabid = sc.tabid
and sc.coltype = 40
and sc.extended_id in (10,11)
left outer join syscolattribs sa
on sa.tabid = sc.tabid
and sa.colno = sc.colno
where sa.tabid IS NULL
UNION ALL
select tabname, colname, sbspace::varchar(128) as sbspace
from systables st
join syscolumns sc
on st.tabid = sc.tabid
and sc.coltype = 40
and sc.extended_id in (10,11)
JOIN syscolattribs sa
on sa.tabid = sc.tabid
and sa.colno = sc.colno
;
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Oct 16, 2013 at 10:45 AM, Art Kagel <art.kagel@gmail.com> wrote:
> You can track the default sbspace from sysmaster:sysconfig if you need it
> in a query:
>
> select cf_effective
> from sysmaster:sysconfig
> where cf_name = 'SBSPACENAME';>
> But if you need to identify tables with BLOB and CLOB columns you can:
>
> select tabname, colname
> from systables st, syscolumns sc
> where st.tabid = sc.tabid>
> and sc.coltype = 40
>
> and sc.extended_id in (10,11);
>
> Art
>
> Art S. Kagel, Principal Consultant
>
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Wed, Oct 16, 2013 at 8:10 AM, ULF ÅKERBERG <
> ulf.akerberg@migrationsverket.se> wrote:
>
> > Thanks Art and Alexandre.
> >
> > Tried both suggestions with the following results:
> >
> > - the select does show a correct result if you have used a PUT IN, if not
> > the
> > data ends up in the default SBSPACE (which is the problem in my case) and
> > there is nothing in syscolattribs.
> >
> > - the oncheck -pe does not give any hint as to which table/database that
> > uses
> > the sbspace as far as I can tell.
> >
> > Any other ideas?
> >
> > Ulf
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11335e5c86e75304e8dcbfaa
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7beb99f2d40a7304e8dd07b8
Thanks Art!
I did two small adjustments:
--and sc.coltype = 40, removed this, did not get any results when it was used,
version 11.70.FC7
and st.tabtype = "T", added to get only tables
Now it works fine for me
Ulf
select cf_effective::varchar(128) from sysmaster:sysconfig where
cf_name = 'SBSPACENAME'
) as sbspace -- SLOB columns with no explicit sbspace
from systables st
join syscolumns sc
on st.tabid = sc.tabid
--and sc.coltype = 40
and st.tabtype = "T"
and sc.extended_id in (10,11)
left outer join syscolattribs sa
on sa.tabid = sc.tabid
and sa.colno = sc.colno
where sa.tabid IS NULL
UNION ALL
select tabname, colname, sbspace::varchar(128) as sbspace
from systables st
join syscolumns sc
on st.tabid = sc.tabid
and sc.coltype = 40
and sc.extended_id in (10,11)
JOIN syscolattribs sa
on sa.tabid = sc.tabid
and sa.colno = sc.colno
Great!
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Oct 17, 2013 at 3:14 AM, ULF ÅKERBERG <
ulf.akerberg@migrationsverket.se> wrote:
> Thanks Art!
>
> I did two small adjustments:
>
> --and sc.coltype = 40, removed this, did not get any results when it was
> used,
> version 11.70.FC7
> and st.tabtype = "T", added to get only tables
>
> Now it works fine for me
>
> Ulf
>
> select cf_effective::varchar(128) from sysmaster:sysconfig where
> cf_name = 'SBSPACENAME'
> ) as sbspace -- SLOB columns with no explicit sbspace
> from systables st
> join syscolumns sc
> on st.tabid = sc.tabid
>
> --and sc.coltype = 40
> and st.tabtype = "T"
> and sc.extended_id in (10,11)
> left outer join syscolattribs sa
> on sa.tabid = sc.tabid
>
> and sa.colno = sc.colno
> where sa.tabid IS NULL
> UNION ALL
> select tabname, colname, sbspace::varchar(128) as sbspace
> from systables st
> join syscolumns sc
> on st.tabid = sc.tabid>
> and sc.coltype = 40
>
> and sc.extended_id in (10,11)
> JOIN syscolattribs sa
> on sa.tabid = sc.tabid
>
> and sa.colno = sc.colno
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e014942eecf075104e8ed8ca2