dbschema output don't show table's dbspace
A user running dbschema -ss on IDS 9.40 saw the dbspace listed for an index but not for the table, and asked how to find where the table lives. Replies explained this is expected: dbschema only prints 'in <dbspace>' for objects NOT in the database's default dbspace (where the catalogs reside, often rootdbs). Suggested ways to confirm: oncheck -pe, "select dbinfo('dbspace', partnum) from systables where tabid=1", a sysmaster query joining systabnames/systabinfo, or Art Kagel's utils2_ak tools (listdb7/myschema). The poster thanked them.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I run dbschema on an 9.40 informix version instance. Unfortunately I have no
indication of the dbspace where a table has been created.
How can this be possible and how can I find this table location. Below is the
dbschema command and it result :
DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9
Copyright IBM Corporation 1996, 2004 All rights reserved
Software Serial Number AAA#B000000
{ TABLE "bank".bkevec row size = 93 number of columns = 14 index size = 30 }
create table "bank".bkevec
(
age char(5),
ope char(3),
eve char(6),
nat char(6),
iden char(5),
typc char(1),
devr char(3),
mcomr decimal(19,4),
txref decimal(18,10),
mcomc decimal(19,4),
mcomn decimal(19,4),
mcomt decimal(19,4),
tax char(1),
tcom decimal(15,7)
) extent size 44055 next size 198 lock mode row;
revoke all on "bank".bkevec from "public";
create unique index "bank".i0_bkevec on "bank".bkevec (age,ope,
eve,nat,iden) using btree in hisidx1dbs1 ;
Can someone help, please ?
Did you use the "-ss" option when executing dbschema ?
If "in <dbspace>" is not specified in output it could mean that the table is
in the "default" dbspace (probably rootdbs)
Best regards,
Jacques Lapeire
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of GILLES
TCHAPPI
Sent: Thursday, November 25, 2010 1:00 PM
To: ids@iiug.org
Subject: dbschema output don't show table's dbspace [22046]
Hi,
I run dbschema on an 9.40 informix version instance. Unfortunately I have no
indication of the dbspace where a table has been created.
How can this be possible and how can I find this table location. Below is the
dbschema command and it result :
DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9
Copyright IBM Corporation 1996, 2004 All rights reserved
Software Serial Number AAA#B000000
{ TABLE "bank".bkevec row size = 93 number of columns = 14 index size = 30 }
create table "bank".bkevec
(
age char(5),
ope char(3),
eve char(6),
nat char(6),
iden char(5),
typc char(1),
devr char(3),
mcomr decimal(19,4),
txref decimal(18,10),
mcomc decimal(19,4),
mcomn decimal(19,4),
mcomt decimal(19,4),
tax char(1),
tcom decimal(15,7)
) extent size 44055 next size 198 lock mode row;
revoke all on "bank".bkevec from "public";
create unique index "bank".i0_bkevec on "bank".bkevec (age,ope,
eve,nat,iden) using btree in hisidx1dbs1 ;
Can someone help, please ?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Dbschema -ss
Khaled Bentebal de mon portable
Le 25 nov. 2010 à 13:00, "GILLES TCHAPPI" <ntgilfr@voila.fr> a écrit :
> Hi,
>
> I run dbschema on an 9.40 informix version instance. Unfortunately I
> have no
> indication of the dbspace where a table has been created.
>
> How can this be possible and how can I find this table location.
> Below is the
> dbschema command and it result :
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9
> Copyright IBM Corporation 1996, 2004 All rights reserved
> Software Serial Number AAA#B000000
> { TABLE "bank".bkevec row size = 93 number of columns = 14 index
> size = 30 }
> create table "bank".bkevec
> (
>
> age char(5),
>
> ope char(3),
>
> eve char(6),
>
> nat char(6),
>
> iden char(5),
>
> typc char(1),
>
> devr char(3),
>
> mcomr decimal(19,4),
>
> txref decimal(18,10),
>
> mcomc decimal(19,4),
>
> mcomn decimal(19,4),
>
> mcomt decimal(19,4),
>
> tax char(1),
>
> tcom decimal(15,7)
> ) extent size 44055 next size 198 lock mode row;
> revoke all on "bank".bkevec from "public";>
> create unique index "bank".i0_bkevec on "bank".bkevec (age,ope,
>
> eve,nat,iden) using btree in hisidx1dbs1 ;
>
> Can someone help, please ?
>
>
> ***
> ***
> ***
> **********************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Gilles
You obviously used the -ss option as you have extent/next sizes and a
location for
the index. Therefore the table must be in the default dbspace (the one
where the
database was created, or ROOTDBS if noen was specified on database creation).
You can check this by running oncheck -pe > database.pe and then viewing this
file to find the table and seeing which dbspace it is in.
Keith
On 25 November 2010 12:00, GILLES TCHAPPI <ntgilfr@voila.fr> wrote:
> Hi,
>
> I run dbschema on an 9.40 informix version instance. Unfortunately I have no
> indication of the dbspace where a table has been created.
>
> How can this be possible and how can I find this table location. Below is the
> dbschema command and it result :
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9
> Copyright IBM Corporation 1996, 2004 All rights reserved
> Software Serial Number AAA#B000000
> { TABLE "bank".bkevec row size = 93 number of columns = 14 index size = 30 }
> create table "bank".bkevec
> (
>
> age char(5),
>
> ope char(3),
>
> eve char(6),
>
> nat char(6),
>
> iden char(5),
>
> typc char(1),
>
> devr char(3),
>
> mcomr decimal(19,4),
>
> txref decimal(18,10),
>
> mcomc decimal(19,4),
>
> mcomn decimal(19,4),
>
> mcomt decimal(19,4),
>
> tax char(1),
>
> tcom decimal(15,7)
> ) extent size 44055 next size 198 lock mode row;
> revoke all on "bank".bkevec from "public";>
> create unique index "bank".i0_bkevec on "bank".bkevec (age,ope,
>
> eve,nat,iden) using btree in hisidx1dbs1 ;
>
> Can someone help, please ?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
OK, if you pass the '-ss' option to dbschema it will print out the dbspace
of any object that is NOT resident in the dbspace in which the database's
catalog tables are resident. To find out what dbspace the database itself
(and so its catalog) resides in you can run the following:
select dbinfo('dbspace', partnum) from systables where tabid = 1;
Another option is to get my package utils2_ak. In there are the
listdb7.ecutility which will list out every database in your system
and optionally the
tables in the databases. With the -D option it will include details on the
objects such as the dbspace, the logging mode of the database, etc. Another
utility in the package is myschema which is a replacement for dbschema with
lots of nice features that dbschema does not support including printing out
ALL of the dbspace residencies for all tables regardless of where they
reside. Myschema supports all dbschema features except -hd and the new -n
option (to print out commands to recreate the server's storage - but stay
tuned). Also, if you are already using Informix 11.70 you should know that
dbschema does not yet properly support a few of the new 11.70 features, but
the latest myschema (dated November 17) supports everything that dbschema
missed.
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
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, Nov 25, 2010 at 7:00 AM, GILLES TCHAPPI <ntgilfr@voila.fr> wrote:
> Hi,
>
> I run dbschema on an 9.40 informix version instance. Unfortunately I have
> no
> indication of the dbspace where a table has been created.
>
> How can this be possible and how can I find this table location. Below is
> the
> dbschema command and it result :
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9
> Copyright IBM Corporation 1996, 2004 All rights reserved
> Software Serial Number AAA#B000000
> { TABLE "bank".bkevec row size = 93 number of columns = 14 index size = 30
> }
> create table "bank".bkevec
> (
>
> age char(5),
>
> ope char(3),
>
> eve char(6),
>
> nat char(6),
>
> iden char(5),
>
> typc char(1),
>
> devr char(3),
>
> mcomr decimal(19,4),
>
> txref decimal(18,10),
>
> mcomc decimal(19,4),
>
> mcomn decimal(19,4),
>
> mcomt decimal(19,4),
>
> tax char(1),
>
> tcom decimal(15,7)
> ) extent size 44055 next size 198 lock mode row;
> revoke all on "bank".bkevec from "public";>
> create unique index "bank".i0_bkevec on "bank".bkevec (age,ope,
>
> eve,nat,iden) using btree in hisidx1dbs1 ;
>
> Can someone help, please ?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf3054a56b9912300495e1dee0
Hi Gilles,
Isn't necesary run dbschema for locate dbspaces from the table that you
created, only run the next SQL statement to SYSMASTER database
SELECT tabname, dbinfo('dbspace', tn.partnum) Dbspace
FROM systabnames tn, systabinfo ti
WHERE ti.ti_partnum = tn.partnum
AND tn.dbsname = "*your_database_name*"
AND tn.tabname = "*your_table_name*"
Saludos,
Javier Gray Benítez
jgray@graytechnology.net
GRAY Technology & Systems
Tel.: +595 981 467505
Asunción, Paraguay
blog: http://informixpy.blogspot.com
Twitter: http://www.twitter.com/xavigray
2010/11/25 GILLES TCHAPPI <ntgilfr@voila.fr>
> Hi,
>
> I run dbschema on an 9.40 informix version instance. Unfortunately I have
> no
> indication of the dbspace where a table has been created.
>
> How can this be possible and how can I find this table location. Below is
> the
> dbschema command and it result :
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9
> Copyright IBM Corporation 1996, 2004 All rights reserved
> Software Serial Number AAA#B000000
> { TABLE "bank".bkevec row size = 93 number of columns = 14 index size = 30
> }
> create table "bank".bkevec
> (
>
> age char(5),
>
> ope char(3),
>
> eve char(6),
>
> nat char(6),
>
> iden char(5),
>
> typc char(1),
>
> devr char(3),
>
> mcomr decimal(19,4),
>
> txref decimal(18,10),
>
> mcomc decimal(19,4),
>
> mcomn decimal(19,4),
>
> mcomt decimal(19,4),
>
> tax char(1),
>
> tcom decimal(15,7)
> ) extent size 44055 next size 198 lock mode row;
> revoke all on "bank".bkevec from "public";>
> create unique index "bank".i0_bkevec on "bank".bkevec (age,ope,
>
> eve,nat,iden) using btree in hisidx1dbs1 ;
>
> Can someone help, please ?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
--0016364d27a5f6fb7f0495e406ca
Hi, Thanks a lot Regards