in which dbspace a table reside?
The poster wanted to find which dbspace a given table lives in, since dbschema -ss didn't show an IN clause and oncheck -pT didn't help. Art Kagel suggested querying sysmaster:systabnames with info('dbspace',partnum); on the poster's older IDS 7.31 (no info function) he noted the dbspace number is the first three hex digits of partnum, and Neville Monteiro's suggestion of oncheck -pe gave the poster what he needed. Follow-up: a table without an IN clause is created in the database's dbspace (where the catalogs are), not necessarily rootdbs, which is why dbschema omits the clause.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
I would like to know the information in which dbspace a certain table resides
in the database. I have done "dhschema -d dbname -t tablename -ss". Is there
any way to do it via sql statement from catalog tables?
i've tried also oncheck -pT dbname:tablename but can't find the location where
a table resides.
Thank you.
Select tabname, info('dbspace',partnum)
From sysmaster:systabnames;On May 16, 2011 5:54 AM, "JACK PAPA" <informix2009@gmail.com> wrote:
> Hi All,
>
> I would like to know the information in which dbspace a certain table
resides
> in the database. I have done "dhschema -d dbname -t tablename -ss". Is
there
> any way to do it via sql statement from catalog tables?
>
> i've tried also oncheck -pT dbname:tablename but can't find the location
where
> a table resides.
>
> Thank you.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--bcaec50166b7d8538604a362be60
Hi Art, My bad, I forgot to mention, i'm still using IDS 7.31 which has no "info" stored proc. Regards,
Will the "oncheck -pe" help ?
On Mon, May 16, 2011 at 4:43 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Select tabname, info('dbspace',partnum)
> >From sysmaster:systabnames;> On May 16, 2011 5:54 AM, "JACK PAPA" <informix2009@gmail.com> wrote:
> > Hi All,
> >
> > I would like to know the information in which dbspace a certain table
> resides
> > in the database. I have done "dhschema -d dbname -t tablename -ss". Is
> there
> > any way to do it via sql statement from catalog tables?
> >
> > i've tried also oncheck -pT dbname:tablename but can't find the location
> where
> > a table resides.
> >
> > Thank you.
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> --bcaec50166b7d8538604a362be60
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747347ce2f7fe04a362e2ce
Thank you. I can see now what i want to know.
Hi,
This is just a follow up question. still with regards to the question posted
here.
when i did "dbschema -d dbname -t tabname -ss", it did not say anything about
the location or dbspace in which the table resides. As far as i know, if a
table is created without the "in dbspacename", it will automatically created
in rootdbs.
Am i correct? how come it is created on another dbspace, not in rootdbs. how
would i know it is the default location?
thanks once again.
A table is created in its own dbspace if you specify it in the CREATE Table
SQL statement.
If you do not specify a dbspace, it goes into the dbspace where the database
to whom it belongs was created.
The same thing goes for the indexes; except that for version 7, the indexes
were attaches ( part of the tablespace) if you did not specify an in dbspace
clause.
Khaled Bentebal de mon portable
Le 16 mai 2011 à 06:38, "JACK PAPA" <informix2009@gmail.com> a écrit :
> Hi,
>
> This is just a follow up question. still with regards to the question posted
> here.
>
> when i did "dbschema -d dbname -t tabname -ss", it did not say anything about
> the location or dbspace in which the table resides. As far as i know, if a
> table is created without the "in dbspacename", it will automatically created
> in rootdbs.
>
> Am i correct? how come it is created on another dbspace, not in rootdbs. how
> would i know it is the default location?
>
> thanks once again.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The dbspace number is the first three hex digits of the partnum. Art On May 16, 2011 6:19 AM, "JACK PAPA" <informix2009@gmail.com> wrote: > Hi Art, > > My bad, I forgot to mention, i'm still using IDS 7.31 which has no "info" > stored proc. > > Regards, > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --bcaec50166b7caa86a04a363e979
No. If there is no IN clause the table resides in the dbspace where the
system catalog tables do, ie where the database was created. Dbschema will
not display the IN clauses for tables in the databases default dbspace.
Myschema does, though.
Art
On May 16, 2011 6:39 AM, "JACK PAPA" <informix2009@gmail.com> wrote:
> Hi,
>
> This is just a follow up question. still with regards to the question
posted
> here.
>
> when i did "dbschema -d dbname -t tabname -ss", it did not say anything
about
> the location or dbspace in which the table resides. As far as i know, if a
> table is created without the "in dbspacename", it will automatically
created
> in rootdbs.
>
> Am i correct? how come it is created on another dbspace, not in rootdbs.
how
> would i know it is the default location?
>
> thanks once again.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--20cf3071cd14aef7ea04a364eb9f
Hi Art,
that's what i'm thinking also. when the database was created, it was already
specified which dbspaces are to be used. That's why, i can't see the "in
dbspace" from the dbschema output.
Thank you for the support.