I don't know why but when I count number of indexes generated by
dbschema for specific "database and owner" is different than the
number I get when I look at sysindexes table for specific "database
and owner".

All I can tell is they are 3 idexes off and all 3 indexes name that
wasn't generated by dbschema starts with number.

Could you please tell me why or how can I get these indexes.

I think I didn't explain properly,

when I do "select count(*) from sysindexes where owner = "abcd" gives
higher count than
when I do dbschema -d DB and take count for indexes of owner abcd.
Those index names that don't show up are not part of dbschema starts
with numeric. Is there a flaw or is there a way to get it using
dbschema ?

On Jun 18, 5:55 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> Generated indices for constraints are numbered and will not show up indbschemaoutput as 'create index'. Instead they will show up as
> constraints. Genrally their names start with space, followed by tabid,
> underscore, and I forget the rest.
>
> j.
>
>
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
> [mailto:informix-list-boun...@iiug.org]On Behalf Of
> mohitanch...@gmail.com
> Sent: Monday, June 18, 2007 8:26 PM
> To: informix-l...@iiug.org
> Subject:Flawindbschema
>
> I don't know why but when I count number of indexes generated bydbschemafor specific "database and owner" is different than the
> number I get when I look at sysindexes table for specific "database
> and owner".
>
> All I can tell is they are 3 idexes off and all 3 indexes name that
> wasn't generated bydbschemastarts with number.
>
> Could you please tell me why or how can I get these indexes.
>
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text -
>
> - Show quoted text -

On Jun 19, 10:46 am, mohitanch...@gmail.com wrote:
> I think I didn't explain properly,
>
> when I do "select count(*) from sysindexes where owner = "abcd" gives
> higher count than
> when I do dbschema -d DB and take count for indexes of owner abcd.
> Those index names that don't show up are not part of dbschema starts
> with numeric. Is there a flaw or is there a way to get it using
> dbschema ?
>
> On Jun 18, 5:55 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
>
> > Generated indices for constraints are numbered and will not show up indbschemaoutput as 'create index'. Instead they will show up as
> > constraints. Genrally their names start with space, followed by tabid,
> > underscore, and I forget the rest.
>
> > j.
>
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org
> > [mailto:informix-list-boun...@iiug.org]On Behalf Of
> > mohitanch...@gmail.com
> > Sent: Monday, June 18, 2007 8:26 PM
> > To: informix-l...@iiug.org
> > Subject:Flawindbschema
>
> > I don't know why but when I count number of indexes generated bydbschemafor specific "database and owner" is different than the
> > number I get when I look at sysindexes table for specific "database
> > and owner".
>
> > All I can tell is they are 3 idexes off and all 3 indexes name that
> > wasn't generated bydbschemastarts with number.
>
> > Could you please tell me why or how can I get these indexes.

Did you READ Jack's answer? Dbschema will NOT report on implicitly
created indexes that were created as the result of creating
a primary key, foreign key, or unique constraint. If you just want to
have the counts match add "AND idxname NOT MATCHES ' *'"
to your catalog query. If you want to know what those indexes are you
have two choices:

1) Look at the constraints on the table in dbschema ... -ss and any
constraints that do not have a matching index explicitly created
DO have an implicit index on the same columns as the constraint,
UNIQUE for primary key and unique constraints, non-unique for
foreign key constraints.

2) Get my dbschema replacement utility, myschema, which DOES print out
the definitions of implied indexes, giving them names
that are readable (ie do not start with an embedded space). Myschema
is included in the package utils2_ak available for download
from the IIUG Software Repository.

Art S. Kagel

Thanks guys. I understand now. I didn't realise that those were
internally created by informix.
On Jun 19, 9:04 am, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Jun 19, 10:46 am, mohitanch...@gmail.com wrote:
>
>
>
>
>
> > I think I didn't explain properly,
>
> > when I do "select count(*) from sysindexes where owner = "abcd" gives
> > higher count than
> > when I dodbschema-d DB and take count for indexes of owner abcd.
> > Those index names that don't show up are not part ofdbschemastarts
> > with numeric. Is there a flaw or is there a way to get it using
> >dbschema?
>
> > On Jun 18, 5:55 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
>
> > > Generated indices for constraints are numbered and will not show up indbschemaoutput as 'create index'. Instead they will show up as
> > > constraints. Genrally their names start with space, followed by tabid,
> > > underscore, and I forget the rest.
>
> > > j.
>
> > > -----Original Message-----
> > > From: informix-list-boun...@iiug.org
> > > [mailto:informix-list-boun...@iiug.org]On Behalf Of
> > > mohitanch...@gmail.com
> > > Sent: Monday, June 18, 2007 8:26 PM
> > > To: informix-l...@iiug.org
> > > Subject:Flawindbschema
>
> > > I don't know why but when I count number of indexes generated bydbschemafor specific "database and owner" is different than the
> > > number I get when I look at sysindexes table for specific "database
> > > and owner".
>
> > > All I can tell is they are 3 idexes off and all 3 indexes name that
> > > wasn't generated bydbschemastarts with number.
>
> > > Could you please tell me why or how can I get these indexes.
>
> Did you READ Jack's answer? Dbschemawill NOT report on implicitly
> created indexes that were created as the result of creating
> a primary key, foreign key, or unique constraint. If you just want to
> have the counts match add "AND idxname NOT MATCHES ' *'"
> to your catalog query. If you want to know what those indexes are you
> have two choices:
>
> 1) Look at the constraints on the table indbschema... -ss and any
> constraints that do not have a matching index explicitly created
> DO have an implicit index on the same columns as the constraint,
> UNIQUE for primary key and unique constraints, non-unique for
> foreign key constraints.
>
> 2) Get mydbschemareplacement utility, myschema, which DOES print out
> the definitions of implied indexes, giving them names
> that are readable (ie do not start with an embedded space). Myschema
> is included in the package utils2_ak available for download
> from the IIUG Software Repository.
>
> Art S. Kagel- Hide quoted text -
>
> - Show quoted text -