how to determine which chunks a table is in?
Poster wanted to find which chunks (not just dbspaces) a table occupies using the sysmaster SMI tables rather than oncheck. One reply pointed to the sysextents view, but on the poster's version it only exposes dbsname, tabname, start (pe_phys) and size, with no chunk number (XPS versions have pe_phys_chunk). The working answer: extract the chunk number from the high-order part of the hex of 'start' and join to syschunks on chknum to get the chunk/filename per table. Poster confirmed this gave the needed data; Informix Server Administrator was also suggested as a GUI alternative.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I'd like to know how to determine which chunks (not only dbspaces) a
table is in by using SMI tables (not oncheck)?
Thanks in advance.
Sent via Deja.com
http://www.deja.com/
In article <93igpk$eli$1@nnrp1.deja.com>,
hanna_shaw@my-deja.com wrote:
> Hi,
>
> I'd like to know how to determine which chunks (not only dbspaces) a
> table is in by using SMI tables (not oncheck)?
>
> Thanks in advance.
>
> Sent via Deja.com
> http://www.deja.com/
>
The sysmaster:sysextents view had table name and chunk number, is that
good enough?
Will
Sent via Deja.com
http://www.deja.com/
Thanks, William.
According to sysmaster.sql, the the definition of the view
sysmaster:sysextents is:
create view sysextents ( dbsname, tabname, start, size)
as
select dbsname, tabname, pe_phys, pe_size
from systabnames a, sysptnext b
where a.partnum = b.pe_partnum;
Do you mean the column 'start' is related to the chunk number? I know
trunc(sysmaster:sysptnext.pe_partnum/1048576) is dbspace number. But I
don't know how to get chunk number. I need to know the information of
all tables in a chunk.
In article <93imn3$kct$1@nnrp1.deja.com>,
William Rice <ricew@operamail.com> wrote:
> In article <93igpk$eli$1@nnrp1.deja.com>,
> hanna_shaw@my-deja.com wrote:
> > Hi,
> >
> > I'd like to know how to determine which chunks (not only dbspaces) a
> > table is in by using SMI tables (not oncheck)?
> >
> > Thanks in advance.
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> The sysmaster:sysextents view had table name and chunk number, is that
> good enough?
>
> Will
>
> Sent via Deja.com
> http://www.deja.com/
>
Sent via Deja.com
http://www.deja.com/
Hello,
Try this;
select hex(start) hexnum,dbsname,tabname
from sysextents
into temp tempext
;
select unique fname, dbsname,tabname
from tempext a, syschunks b
where hex(a.hexnum[1,5]) = hex(b.chknum) and
dbsname = "yourdatabase" and
tabname = "yourtable";
Erickson
In article <93igpk$eli$1@nnrp1.deja.com>,
hanna_shaw@my-deja.com wrote:
> Hi,
>
> I'd like to know how to determine which chunks (not only dbspaces) a
> table is in by using SMI tables (not oncheck)?
>
> Thanks in advance.
>
> Sent via Deja.com
> http://www.deja.com/
>
Sent via Deja.com
http://www.deja.com/
Ah, yeah, it's the information I want. Thanks a lot.
In article <93klo3$8g6$1@nnrp1.deja.com>,
Norman Erickson Lugtu <elugtu@my-deja.com> wrote:
> Hello,
>
> Try this;
>
> select hex(start) hexnum,dbsname,tabname
> from sysextents
> into temp tempext
> ;
> select unique fname, dbsname,tabname
> from tempext a, syschunks b
> where hex(a.hexnum[1,5]) = hex(b.chknum) and
> dbsname = "yourdatabase" and
> tabname = "yourtable"> ;
>
> Erickson
>
> In article <93igpk$eli$1@nnrp1.deja.com>,
> hanna_shaw@my-deja.com wrote:
> > Hi,
> >
> > I'd like to know how to determine which chunks (not only dbspaces) a
> > table is in by using SMI tables (not oncheck)?
> >
> > Thanks in advance.
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>
Sent via Deja.com
http://www.deja.com/
My apologies, below is what is in the view on my system. I assumed it
was the same on yours, bad assumption on my part. I am pretty sure the
sysptnext table in 7.x/9.x has start_chunk as well, so you could mimick
the join on the view. But I am much less sure now, I do not administer
any boxes with 7.x or 9.x :( ... so sometimes make mistakes of this
sort.
Hope this helps,
Will
--view on my system
create view sysextents ( coserver_id, dbsname, tabname,
start_chunk,
start_offset, size)
as
select a.coserver_id, dbsname, tabname, pe_phys_chunk,
pe_phys_offset,
pe_size
from systabnames a, sysptnext b
where a.partnum = b.pe_partnum
and a.coserver_id = b.coserver_id;
In article <93kl3o$7sn$1@nnrp1.deja.com>,
hanna_shaw@my-deja.com wrote:
> Thanks, William.
>
> According to sysmaster.sql, the the definition of the view
> sysmaster:sysextents is:
> create view sysextents ( dbsname, tabname, start, size)
> as
> select dbsname, tabname, pe_phys, pe_size
> from systabnames a, sysptnext b
> where a.partnum = b.pe_partnum;>
> Do you mean the column 'start' is related to the chunk number? I know
> trunc(sysmaster:sysptnext.pe_partnum/1048576) is dbspace number. But
I
> don't know how to get chunk number. I need to know the information of
> all tables in a chunk.
>
> In article <93imn3$kct$1@nnrp1.deja.com>,
> William Rice <ricew@operamail.com> wrote:
> > In article <93igpk$eli$1@nnrp1.deja.com>,
> > hanna_shaw@my-deja.com wrote:
> > > Hi,
> > >
> > > I'd like to know how to determine which chunks (not only
dbspaces) a
> > > table is in by using SMI tables (not oncheck)?
> > >
> > > Thanks in advance.
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> > >
> >
> > The sysmaster:sysextents view had table name and chunk number, is
that
> > good enough?
> >
> > Will
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>
Sent via Deja.com
http://www.deja.com/
Or use Informix Server Administrator which allows you to brwose through
chunks and see the tables and vice versa.
Yours
Earle A Long (Senior Informix DBA)
SINGLEPOINT LIMITED
<hanna_shaw@my-deja.com> wrote in message
news:93kmds$947$1@nnrp1.deja.com...
> Ah, yeah, it's the information I want. Thanks a lot.
>
> In article <93klo3$8g6$1@nnrp1.deja.com>,
> Norman Erickson Lugtu <elugtu@my-deja.com> wrote:
> > Hello,
> >
> > Try this;
> >
> > select hex(start) hexnum,dbsname,tabname
> > from sysextents
> > into temp tempext
> > ;
> > select unique fname, dbsname,tabname
> > from tempext a, syschunks b
> > where hex(a.hexnum[1,5]) = hex(b.chknum) and
> > dbsname = "yourdatabase" and
> > tabname = "yourtable"> > ;
> >
> > Erickson
> >
> > In article <93igpk$eli$1@nnrp1.deja.com>,
> > hanna_shaw@my-deja.com wrote:
> > > Hi,
> > >
> > > I'd like to know how to determine which chunks (not only dbspaces) a
> > > table is in by using SMI tables (not oncheck)?
> > >
> > > Thanks in advance.
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> > >
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
> Sent via Deja.com
> http://www.deja.com/