Re: Flaw in dbschema

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