sysindexes table missing
A DBA on AIX 5.3 / IDS 11.50.FC7 rebuilt a PeopleSoft HR database (create database, create tables, load, create indexes) and afterwards the sysindexes catalog view was missing, with dbschema failing with error -206; IBM support couldn't help, and rebuilding the SMI tables didn't fix it. Replies clarified that sysindexes is a view over sysindices in the user database (not sysmaster), noted that incomplete sysmaster builds after upgrades can cause oddities, and suggested checking systables directly. Art Kagel's advice was simply to drop and recreate the database; the poster accepted that as the likely course but no confirmed cause or fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Aix 5.3
IDS 11.50fc7wr3
I copied a PeopleSoft HR database from our production to our UAT
environment. After I was done, the sysindexes table was missing from the
database. My steps were as follows:
create database ...
create tables ...
load data ...
create indexes ..
All processes ran successfully. If I look in sysmaster there is no
sysindexes table. If I run a dbschema, I get a -206 error on the
sysindexes table.
Any clues as to where it might have gone, or how to recreate it without
redoing the entire thing. I have been working with Tech Support and they
haven't been able to solve it. Next step is to go to the Down Servers
group.
Thanks in advance ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
Aren't you looking in the wrong place? sysindexes is a view to sysindicies which is a system catalog table. Not a sysmaster table. dan
I realize that .. but dbschema uses the view in the database ... Also,
PS has tools that check to make sure all the indexes are there, and they
also use sysindexes .. TS had me rebuild the SMI tables .. but that
didn't recreate the entry in sysmaster ....
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "DAN MUELLER" <dan.mueller@trnswrks.com>
To: ids@iiug.org
Date: 08/08/2011 02:15 PM
Subject: Re: sysindexes table missing [24554]
Sent by: ids-bounces@iiug.org
Aren't you looking in the wrong place? sysindexes is a view to sysindicies
which is a system catalog table. Not a sysmaster table.
dan
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Drop the database and recreate it from scratch.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Mon, Aug 8, 2011 at 2:20 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> I realize that .. but dbschema uses the view in the database ... Also,
> PS has tools that check to make sure all the indexes are there, and they
> also use sysindexes .. TS had me rebuild the SMI tables .. but that
> didn't recreate the entry in sysmaster ....
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "DAN MUELLER" <dan.mueller@trnswrks.com>
> To: ids@iiug.org
> Date: 08/08/2011 02:15 PM
> Subject: Re: sysindexes table missing [24554]
> Sent by: ids-bounces@iiug.org
>
> Aren't you looking in the wrong place? sysindexes is a view to sysindicies
>
> which is a system catalog table. Not a sysmaster table.
>
> dan
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec547c8716782bd04aa032033
was trying toavoid that .. but figured I was going to have to .. any clue
as to what might cause this?
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Art Kagel" <art.kagel@gmail.com>
To: ids@iiug.org
Date: 08/08/2011 03:05 PM
Subject: Re: sysindexes table missing [24556]
Sent by: ids-bounces@iiug.org
Drop the database and recreate it from scratch.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Mon, Aug 8, 2011 at 2:20 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> I realize that .. but dbschema uses the view in the database ... Also,
> PS has tools that check to make sure all the indexes are there, and they
> also use sysindexes .. TS had me rebuild the SMI tables .. but that
> didn't recreate the entry in sysmaster ....
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "DAN MUELLER" <dan.mueller@trnswrks.com>
> To: ids@iiug.org
> Date: 08/08/2011 02:15 PM
> Subject: Re: sysindexes table missing [24554]
> Sent by: ids-bounces@iiug.org
>
> Aren't you looking in the wrong place? sysindexes is a view to
sysindicies
>
> which is a system catalog table. Not a sysmaster table.
>
> dan
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec547c8716782bd04aa032033
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Did you upgrade the server version at some point? Through 11.50.xC8 the
upgrades sometimes did not completely build sysmaster.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Mon, Aug 8, 2011 at 3:17 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> was trying toavoid that .. but figured I was going to have to .. any clue
> as to what might cause this?
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 08/08/2011 03:05 PM
> Subject: Re: sysindexes table missing [24556]
> Sent by: ids-bounces@iiug.org
>
> Drop the database and recreate it from scratch.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Mon, Aug 8, 2011 at 2:20 PM, Peter_Logan@spartanstores.com <
> Peter_Logan@spartanstores.com> wrote:
>
> > I realize that .. but dbschema uses the view in the database ... Also,
> > PS has tools that check to make sure all the indexes are there, and they
>
> > also use sysindexes .. TS had me rebuild the SMI tables .. but that
> > didn't recreate the entry in sysmaster ....
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From: "DAN MUELLER" <dan.mueller@trnswrks.com>
> > To: ids@iiug.org
> > Date: 08/08/2011 02:15 PM
> > Subject: Re: sysindexes table missing [24554]
> > Sent by: ids-bounces@iiug.org
> >
> > Aren't you looking in the wrong place? sysindexes is a view to
> sysindicies
> >
> > which is a system catalog table. Not a sysmaster table.
> >
> > dan
> >
> >
> >
> >
>
>
>
*******************************************************************************
>
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec547c8716782bd04aa032033
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5016435cc8d6d04aa035430
not recently, and I have done this process a zillion times since the last
upgrade ... just chalk it up to gremlins ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Art Kagel" <art.kagel@gmail.com>
To: ids@iiug.org
Date: 08/08/2011 03:20 PM
Subject: Re: sysindexes table missing [24558]
Sent by: ids-bounces@iiug.org
Did you upgrade the server version at some point? Through 11.50.xC8 the
upgrades sometimes did not completely build sysmaster.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Mon, Aug 8, 2011 at 3:17 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> was trying toavoid that .. but figured I was going to have to .. any
clue
> as to what might cause this?
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 08/08/2011 03:05 PM
> Subject: Re: sysindexes table missing [24556]
> Sent by: ids-bounces@iiug.org
>
> Drop the database and recreate it from scratch.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Mon, Aug 8, 2011 at 2:20 PM, Peter_Logan@spartanstores.com <
> Peter_Logan@spartanstores.com> wrote:
>
> > I realize that .. but dbschema uses the view in the database ... Also,
> > PS has tools that check to make sure all the indexes are there, and
they
>
> > also use sysindexes .. TS had me rebuild the SMI tables .. but that
> > didn't recreate the entry in sysmaster ....
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From: "DAN MUELLER" <dan.mueller@trnswrks.com>
> > To: ids@iiug.org
> > Date: 08/08/2011 02:15 PM
> > Subject: Re: sysindexes table missing [24554]
> > Sent by: ids-bounces@iiug.org
> >
> > Aren't you looking in the wrong place? sysindexes is a view to
> sysindicies
> >
> > which is a system catalog table. Not a sysmaster table.
> >
> > dan
> >
> >
> >
> >
>
>
>
*******************************************************************************
>
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec547c8716782bd04aa032033
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5016435cc8d6d04aa035430
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Peter Logan Wrote:
================================================================================
================================================================================
Aix 5.3
IDS 11.50fc7wr3
I copied a PeopleSoft HR database from our production to our UAT
environment. After I was done, the sysindexes table was missing from the
database. My steps were as follows:
create database ...
create tables ...
load data ...
create indexes ..
All processes ran successfully. If I look in sysmaster there is no
sysindexes table. If I run a dbschema, I get a -206 error on the
sysindexes table.
Any clues as to where it might have gone, or how to recreate it without
redoing the entire thing. I have been working with Tech Support and they
haven't been able to solve it. Next step is to go to the Down Servers
group.
Thanks in advance ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
================================================================================
================================================================================
Response:
I've never seen dbschema provide definitions for sysmaster or system tables in
other databases. Dbschema on sysmaster always responds with "Database has
pseudo tables - can't build schema." On normal databases it always responds
with a "No table or view sysindexes", even when it does exist. Also,
sysindexes, systables, etc. are excluded from dbaccess table listings.
How else did you validate that sysindexes does not exist?
Have you tried direct selects against sysindexes or against systables where
tabname = 'sysindexes'?
Dave Griffen
Yes .. what I originally meant to say was that the sysindexes table/view
is missing from the PS database ... not sysmaster...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "DAVE GRIFFEN" <dgriffen@finishline.com>
To: ids@iiug.org
Date: 08/08/2011 05:52 PM
Subject: Re: sysindexes table missing [24561]
Sent by: ids-bounces@iiug.org
Peter Logan Wrote:
================================================================================
================================================================================
Aix 5.3
IDS 11.50fc7wr3
I copied a PeopleSoft HR database from our production to our UAT
environment. After I was done, the sysindexes table was missing from the
database. My steps were as follows:
create database ...
create tables ...
load data ...
create indexes ..
All processes ran successfully. If I look in sysmaster there is no
sysindexes table. If I run a dbschema, I get a -206 error on the
sysindexes table.
Any clues as to where it might have gone, or how to recreate it without
redoing the entire thing. I have been working with Tech Support and they
haven't been able to solve it. Next step is to go to the Down Servers
group.
Thanks in advance ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
================================================================================
================================================================================
Response:
I've never seen dbschema provide definitions for sysmaster or system
tables in
other databases. Dbschema on sysmaster always responds with "Database has
pseudo tables - can't build schema." On normal databases it always
responds
with a "No table or view sysindexes", even when it does exist. Also,
sysindexes, systables, etc. are excluded from dbaccess table listings.
How else did you validate that sysindexes does not exist?
Have you tried direct selects against sysindexes or against systables
where
tabname = 'sysindexes'?
Dave Griffen
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.