dropping a database with 392 errors
On IDS 7.31, the poster couldn't drop a database (or several of its tables): DROP DATABASE failed with error 215 "Cannot open file for table" plus ISAM error 102, and SELECTs returned 392 errors. Art Kagel suspected chunk/dbspace corruption, supplied dbschema/systables scripts to generate DROP TABLE statements, and asked how the data was migrated (onunload/onload page copies can carry corruption, text UNLOAD/LOAD normally wouldn't). oncheck -cc showed index/systables inconsistencies; advice turned to checking disk/LVM/SAN storage, oncheck -ce/-pe, and possibly paying IBM for support. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am currently running a system with Informix version 7.31.UD6W3. I
want to drop the database but each time I try to I get the following
error messages:
215: Cannot open file for table (accadm.table_name).
102: ISAM error: illegal argument to ISAM function.
I can manually drop the table, but after I do that a new table shows up
in the error message. Right now I just want to blow this database away
since select statements are coming back with a 392 errors and try to
reload the data. Is there any good way to drop this database? I can
drop the dbspaces if I need to since this database is on its own dbspace
apart from any other databases.
Any ideas would be helpful. Thanks.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
This sounds like one or more of your chunks in this dbspace are going
south. Could it be that one is marked down?
You will not be able to drop a dbspace that has data in it. Anyway, here's
a quick script to generate an SQL script to drop all of the remaining
tables. Perhaps then you will be able to drop the database.
dbschema -d mydatabase | awk '/create table/{ printf "drop table %s;\\
",
$3;}' >drop_tables.sql
Or in dbaccess you can:
unload to 'drop_tables.sql' delimiter ';'select 'drop table ' || tabname
from systables
where tabid > 99;
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 5:41 AM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I am currently running a system with Informix version 7.31.UD6W3. I
> want to drop the database but each time I try to I get the following
> error messages:
>
> 215: Cannot open file for table (accadm.table_name).>
> 102: ISAM error: illegal argument to ISAM function.>
> I can manually drop the table, but after I do that a new table shows up
> in the error message. Right now I just want to blow this database away
> since select statements are coming back with a 392 errors and try to
> reload the data. Is there any good way to drop this database? I can
> drop the dbspaces if I need to since this database is on its own dbspace
> apart from any other databases.
>
> Any ideas would be helpful. Thanks.
>
> Keith Schleicher
>
> IT Database Administrator
>
> B2-253B-A
>
> Office: 847-286-4027
>
> Cell: 224-210-8358
>
> Blackberry: 2242108358@messaging.sprintpcs.com
>
> Page: 2242108358@sprint.skytel.com
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747bfe205544b0477786ba6
When trying to drop some of the tables, I'm getting the same 392 error.
The database I'm trying to drop doesn't have any chunks that are marked
down. However, the data that is in there is from a database that has
some bad chunks. I unloaded the data to a file from the old server,
ftp'd the files over to the new server, and then loaded the data in the
files to the new server. Even though I unloaded the data to files,
could the data in the files be corrupt and cause this? If so, we'd need
to get our data from a different source.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Tuesday, November 03, 2009 8:36 AM
To: ids@iiug.org
Subject: Re: dropping a database with 392 errors [17882]
This sounds like one or more of your chunks in this dbspace are going
south. Could it be that one is marked down?
You will not be able to drop a dbspace that has data in it. Anyway,
here's
a quick script to generate an SQL script to drop all of the remaining
tables. Perhaps then you will be able to drop the database.
dbschema -d mydatabase | awk '/create table/{ printf "drop table %s;\\
",
$3;}' >drop_tables.sql
Or in dbaccess you can:
unload to 'drop_tables.sql' delimiter ';'select 'drop table ' || tabname
from systables
where tabid > 99;
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 5:41 AM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I am currently running a system with Informix version 7.31.UD6W3. I
> want to drop the database but each time I try to I get the following
> error messages:
>
> 215: Cannot open file for table (accadm.table_name).>
> 102: ISAM error: illegal argument to ISAM function.>
> I can manually drop the table, but after I do that a new table shows
up
> in the error message. Right now I just want to blow this database away
> since select statements are coming back with a 392 errors and try to
> reload the data. Is there any good way to drop this database? I can
> drop the dbspaces if I need to since this database is on its own
dbspace
> apart from any other databases.
>
> Any ideas would be helpful. Thanks.
>
> Keith Schleicher
>
> IT Database Administrator
>
> B2-253B-A
>
> Office: 847-286-4027
>
> Cell: 224-210-8358
>
> Blackberry: 2242108358@messaging.sprintpcs.com
>
> Page: 2242108358@sprint.skytel.com
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151747bfe205544b0477786ba6
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
How was the data extracted and loaded? Did you use onunload and onload or
dbexport and dbimport (or UNLOAD/LOAD)?
If you use onunload/onload, then yes, it is likely that the pages extracted
from the original server were corrupted and the contents are confusing the
new server causing the internal errors.
If you used some kind of text unload and reload, then it is unlikely that
the data migration caused the problem.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:00 AM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> When trying to drop some of the tables, I'm getting the same 392 error.
>
> The database I'm trying to drop doesn't have any chunks that are marked
> down. However, the data that is in there is from a database that has
> some bad chunks. I unloaded the data to a file from the old server,
> ftp'd the files over to the new server, and then loaded the data in the
> files to the new server. Even though I unloaded the data to files,
> could the data in the files be corrupt and cause this? If so, we'd need
> to get our data from a different source.
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, November 03, 2009 8:36 AM
> To: ids@iiug.org
> Subject: Re: dropping a database with 392 errors [17882]
>
> This sounds like one or more of your chunks in this dbspace are going
> south. Could it be that one is marked down?
>
> You will not be able to drop a dbspace that has data in it. Anyway,
> here's
> a quick script to generate an SQL script to drop all of the remaining
> tables. Perhaps then you will be able to drop the database.
>
> dbschema -d mydatabase | awk '/create table/{ printf "drop table %s;\\
",>
> $3;}' >drop_tables.sql
>
> Or in dbaccess you can:
>
> unload to 'drop_tables.sql' delimiter ';'> select 'drop table ' || tabname
> from systables
> where tabid > 99;
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 5:41 AM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > I am currently running a system with Informix version 7.31.UD6W3. I
> > want to drop the database but each time I try to I get the following
> > error messages:
> >
> > 215: Cannot open file for table (accadm.table_name).> >
> > 102: ISAM error: illegal argument to ISAM function.> >
> > I can manually drop the table, but after I do that a new table shows
> up
> > in the error message. Right now I just want to blow this database away
>
> > since select statements are coming back with a 392 errors and try to
> > reload the data. Is there any good way to drop this database? I can
> > drop the dbspaces if I need to since this database is on its own
> dbspace
> > apart from any other databases.
> >
> > Any ideas would be helpful. Thanks.
> >
> > Keith Schleicher
> >
> > IT Database Administrator
> >
> > B2-253B-A
> >
> > Office: 847-286-4027
> >
> > Cell: 224-210-8358
> >
> > Blackberry: 2242108358@messaging.sprintpcs.com
> >
> > Page: 2242108358@sprint.skytel.com
> >
> >
> >
> >
> ************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --00151747bfe205544b0477786ba6
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00032555a4e649e1ed047779cb90
I ran a sql script that had a row for each table that looked like this:
unload to <tablename>.unl select * from <tablename>;
I thought if I did that, then it wouldn't cause any database issues with
the data.
I'm guessing that the next step is to have the system administrator look
at the disk, even though there aren't any indicators that the dbspaces
are corrupt.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Tuesday, November 03, 2009 10:15 AM
To: ids@iiug.org
Subject: Re: dropping a database with 392 errors [17884]
How was the data extracted and loaded? Did you use onunload and onload
or
dbexport and dbimport (or UNLOAD/LOAD)?
If you use onunload/onload, then yes, it is likely that the pages
extracted
from the original server were corrupted and the contents are confusing
the
new server causing the internal errors.
If you used some kind of text unload and reload, then it is unlikely
that
the data migration caused the problem.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:00 AM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> When trying to drop some of the tables, I'm getting the same 392
error.
>
> The database I'm trying to drop doesn't have any chunks that are
marked
> down. However, the data that is in there is from a database that has
> some bad chunks. I unloaded the data to a file from the old server,
> ftp'd the files over to the new server, and then loaded the data in
the
> files to the new server. Even though I unloaded the data to files,
> could the data in the files be corrupt and cause this? If so, we'd
need
> to get our data from a different source.
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, November 03, 2009 8:36 AM
> To: ids@iiug.org
> Subject: Re: dropping a database with 392 errors [17882]
>
> This sounds like one or more of your chunks in this dbspace are going
> south. Could it be that one is marked down?
>
> You will not be able to drop a dbspace that has data in it. Anyway,
> here's
> a quick script to generate an SQL script to drop all of the remaining
> tables. Perhaps then you will be able to drop the database.
>
> dbschema -d mydatabase | awk '/create table/{ printf "drop table
%s;\\
",>
> $3;}' >drop_tables.sql
>
> Or in dbaccess you can:
>
> unload to 'drop_tables.sql' delimiter ';'> select 'drop table ' || tabname
> from systables
> where tabid > 99;
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own
opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 5:41 AM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > I am currently running a system with Informix version 7.31.UD6W3. I
> > want to drop the database but each time I try to I get the following
> > error messages:
> >
> > 215: Cannot open file for table (accadm.table_name).> >
> > 102: ISAM error: illegal argument to ISAM function.> >
> > I can manually drop the table, but after I do that a new table shows
> up
> > in the error message. Right now I just want to blow this database
away
>
> > since select statements are coming back with a 392 errors and try to
> > reload the data. Is there any good way to drop this database? I can
> > drop the dbspaces if I need to since this database is on its own
> dbspace
> > apart from any other databases.
> >
> > Any ideas would be helpful. Thanks.
> >
> > Keith Schleicher
> >
> > IT Database Administrator
> >
> > B2-253B-A
> >
> > Office: 847-286-4027
> >
> > Cell: 224-210-8358
> >
> > Blackberry: 2242108358@messaging.sprintpcs.com
> >
> > Page: 2242108358@sprint.skytel.com
> >
> >
> >
> >
>
************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --00151747bfe205544b0477786ba6
>
>
************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00032555a4e649e1ed047779cb90
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Yes, I'd look at the disk. Also, if you still have the original unload
files around, you can look at the files for one or two of the troublesome
tables and see if the data looks weird, just for peace of mind.
Ultimately, if your SA doesn't find anything wrong at the hardware level,
you may have to call IBM and pay for an out-of-support support call for them
to look at this.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:34 AM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I ran a sql script that had a row for each table that looked like this:
>
> unload to <tablename>.unl select * from <tablename>;>
> I thought if I did that, then it wouldn't cause any database issues with
> the data.
>
> I'm guessing that the next step is to have the system administrator look
> at the disk, even though there aren't any indicators that the dbspaces
> are corrupt.
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, November 03, 2009 10:15 AM
> To: ids@iiug.org
> Subject: Re: dropping a database with 392 errors [17884]
>
> How was the data extracted and loaded? Did you use onunload and onload
> or
> dbexport and dbimport (or UNLOAD/LOAD)?
>
> If you use onunload/onload, then yes, it is likely that the pages
> extracted
> from the original server were corrupted and the contents are confusing
> the
> new server causing the internal errors.
>
> If you used some kind of text unload and reload, then it is unlikely
> that
> the data migration caused the problem.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:00 AM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > When trying to drop some of the tables, I'm getting the same 392
> error.
> >
> > The database I'm trying to drop doesn't have any chunks that are
> marked
> > down. However, the data that is in there is from a database that has
> > some bad chunks. I unloaded the data to a file from the old server,
> > ftp'd the files over to the new server, and then loaded the data in
> the
> > files to the new server. Even though I unloaded the data to files,
> > could the data in the files be corrupt and cause this? If so, we'd
> need
> > to get our data from a different source.
> >
> > Keith Schleicher
> > IT Database Administrator
> > B2-253B-A
> > Office: 847-286-4027
> > Cell: 224-210-8358
> > Blackberry: 2242108358@messaging.sprintpcs.com
> > Page: 2242108358@sprint.skytel.com
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Tuesday, November 03, 2009 8:36 AM
> > To: ids@iiug.org
> > Subject: Re: dropping a database with 392 errors [17882]
> >
> > This sounds like one or more of your chunks in this dbspace are going
> > south. Could it be that one is marked down?
> >
> > You will not be able to drop a dbspace that has data in it. Anyway,
> > here's
> > a quick script to generate an SQL script to drop all of the remaining
> > tables. Perhaps then you will be able to drop the database.
> >
> > dbschema -d mydatabase | awk '/create table/{ printf "drop table
> %s;\\
",> >
> > $3;}' >drop_tables.sql
> >
> > Or in dbaccess you can:
> >
> > unload to 'drop_tables.sql' delimiter ';'> > select 'drop table ' || tabname
> > from systables
> > where tabid > 99;
> >
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 5:41 AM, Schleicher, Keith <
> > Keith.Schleicher@searshc.com> wrote:
> >
> > > I am currently running a system with Informix version 7.31.UD6W3. I
> > > want to drop the database but each time I try to I get the following
>
> > > error messages:
> > >
> > > 215: Cannot open file for table (accadm.table_name).> > >
> > > 102: ISAM error: illegal argument to ISAM function.> > >
> > > I can manually drop the table, but after I do that a new table shows
>
> > up
> > > in the error message. Right now I just want to blow this database
> away
> >
> > > since select statements are coming back with a 392 errors and try to
>
> > > reload the data. Is there any good way to drop this database? I can
> > > drop the dbspaces if I need to since this database is on its own
> > dbspace
> > > apart from any other databases.
> > >
> > > Any ideas would be helpful. Thanks.
> > >
> > > Keith Schleicher
> > >
> > > IT Database Administrator
> > >
> > > B2-253B-A
> > >
> > > Office: 847-286-4027
> > >
> > > Cell: 224-210-8358
> > >
> > > Blackberry: 2242108358@messaging.sprintpcs.com
> > >
> > > Page: 2242108358@sprint.skytel.com
> > >
> > >
> > >
> > >
> >
> ************************************************************************
>
> > *******
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --00151747bfe205544b0477786ba6
> >
> >
> ************************************************************************
>
> > *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
> ************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --00032555a4e649e1ed047779cb90
>
> *******************************************************
I ran an "oncheck -cc" on the database and most of the tables have
errors similar to this:
ERROR: accadm.<tablename> nindexes 1 != tblspace.nkeys 0 + detached
keys
3
ERROR: di_nkeys 0 != systables nindexes 1 - detached keys 3
for table accadm.<tablename>.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Tuesday, November 03, 2009 10:39 AM
To: ids@iiug.org
Subject: Re: dropping a database with 392 errors [17886]
Yes, I'd look at the disk. Also, if you still have the original unload
files around, you can look at the files for one or two of the
troublesome
tables and see if the data looks weird, just for peace of mind.
Ultimately, if your SA doesn't find anything wrong at the hardware
level,
you may have to call IBM and pay for an out-of-support support call for
them
to look at this.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:34 AM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I ran a sql script that had a row for each table that looked like
this:
>
> unload to <tablename>.unl select * from <tablename>;>
> I thought if I did that, then it wouldn't cause any database issues
with
> the data.
>
> I'm guessing that the next step is to have the system administrator
look
> at the disk, even though there aren't any indicators that the dbspaces
> are corrupt.
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, November 03, 2009 10:15 AM
> To: ids@iiug.org
> Subject: Re: dropping a database with 392 errors [17884]
>
> How was the data extracted and loaded? Did you use onunload and onload
> or
> dbexport and dbimport (or UNLOAD/LOAD)?
>
> If you use onunload/onload, then yes, it is likely that the pages
> extracted
> from the original server were corrupted and the contents are confusing
> the
> new server causing the internal errors.
>
> If you used some kind of text unload and reload, then it is unlikely
> that
> the data migration caused the problem.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own
opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:00 AM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > When trying to drop some of the tables, I'm getting the same 392
> error.
> >
> > The database I'm trying to drop doesn't have any chunks that are
> marked
> > down. However, the data that is in there is from a database that has
> > some bad chunks. I unloaded the data to a file from the old server,
> > ftp'd the files over to the new server, and then loaded the data in
> the
> > files to the new server. Even though I unloaded the data to files,
> > could the data in the files be corrupt and cause this? If so, we'd
> need
> > to get our data from a different source.
> >
> > Keith Schleicher
> > IT Database Administrator
> > B2-253B-A
> > Office: 847-286-4027
> > Cell: 224-210-8358
> > Blackberry: 2242108358@messaging.sprintpcs.com
> > Page: 2242108358@sprint.skytel.com
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
Of
> > Art Kagel
> > Sent: Tuesday, November 03, 2009 8:36 AM
> > To: ids@iiug.org
> > Subject: Re: dropping a database with 392 errors [17882]
> >
> > This sounds like one or more of your chunks in this dbspace are
going
> > south. Could it be that one is marked down?
> >
> > You will not be able to drop a dbspace that has data in it. Anyway,
> > here's
> > a quick script to generate an SQL script to drop all of the
remaining
> > tables. Perhaps then you will be able to drop the database.
> >
> > dbschema -d mydatabase | awk '/create table/{ printf "drop table
> %s;\\
",> >
> > $3;}' >drop_tables.sql
> >
> > Or in dbaccess you can:
> >
> > unload to 'drop_tables.sql' delimiter ';'> > select 'drop table ' || tabname
> > from systables
> > where tabid > 99;
> >
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 5:41 AM, Schleicher, Keith <
> > Keith.Schleicher@searshc.com> wrote:
> >
> > > I am currently running a system with Informix version 7.31.UD6W3.
I
> > > want to drop the database but each time I try to I get the
following
>
> > > error messages:
> > >
> > > 215: Cannot open file for table (accadm.table_name).> > >
> > > 102: ISAM error: illegal argument to ISAM function.> > >
> > > I can manually drop the table, but after I do that a new table
shows
>
> > up
> > > in the error message. Right now I just want to blow this database
> away
> >
> > > since select statements are coming back with a 392 errors and try
to
>
> > > reload the data. Is there any good way to drop this database? I
can
> > > drop the dbspaces if I need to since this database is on its own
> > dbspace
> > > apart from any other databases.
> > >
> > > Any ideas would be helpful. Thanks.
> > >
> > > Keith Schleicher
> > >
> > > IT Database Administrator
> > >
> > > B2-253B-A
> > >
> > > Office: 847-286-4027
> > >
> > > Cell: 224-210-8358
> > >
> > > Blackberry: 2242108358@messaging.sprintpcs.com
> >
Keith,
Is(Are) your dbspace(s) running on a SAN, NAS, or some kind of remote storage
that is not local to the db server? Also, if you're on a Unix platform, are
use using LVM? If you are, you can to a vgdisplay -v (volume group path) to
find the status of your LVM volumes.
You can also run an oncheck -ce on your dbspaces to see if they are really
there.
HTH
Jonathan B. Smaby
Pomona College
phone: (909) 621-8506
email: jonathan.smaby@pomona.edu
web: http://www.facebook.com/jonathan.smaby
Semper Paratus! "Always Ready!"
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Schleicher, Keith
Sent: Tuesday, November 03, 2009 8:59 AM
To: ids@iiug.org
Subject: RE: dropping a database with 392 errors [17887]
I ran an "oncheck -cc" on the database and most of the tables have errors
similar to this:
ERROR: accadm.<tablename> nindexes 1 != tblspace.nkeys 0 + detached keys
3
ERROR: di_nkeys 0 != systables nindexes 1 - detached keys 3
for table accadm.<tablename>.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
Oops. Sorry, it might be better to run:
oncheck -pe to check physical extents, in preview mode.
Thanks.
Jonathan B. Smaby
Pomona College
phone: (909) 621-8506
email: jonathan.smaby@pomona.edu
web: http://www.facebook.com/jonathan.smaby
Semper Paratus! "Always Ready!"
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jonathan
Smaby
Sent: Tuesday, November 03, 2009 9:07 AM
To: ids@iiug.org
Subject: RE: dropping a database with 392 errors [17888]
Keith,
Is(Are) your dbspace(s) running on a SAN, NAS, or some kind of remote storage
that is not local to the db server? Also, if you're on a Unix platform, are
use using LVM? If you are, you can to a vgdisplay -v (volume group path) to
find the status of your LVM volumes.
You can also run an oncheck -ce on your dbspaces to see if they are really
there.
HTH
Jonathan B. Smaby
Pomona College
phone: (909) 621-8506
email: jonathan.smaby@pomona.edu
web: http://www.facebook.com/jonathan.smaby
Semper Paratus! "Always Ready!"
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Schleicher, Keith
Sent: Tuesday, November 03, 2009 8:59 AM
To: ids@iiug.org
Subject: RE: dropping a database with 392 errors [17887]
I ran an "oncheck -cc" on the database and most of the tables have errors
similar to this:
ERROR: accadm.<tablename> nindexes 1 != tblspace.nkeys 0 + detached keys
3
ERROR: di_nkeys 0 != systables nindexes 1 - detached keys 3
for table accadm.<tablename>.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
It looks like for some reason either the system catalog or the table's
partition page has been corrupted. Did you possible unload and restore some
or all of the system catalog tables like systables, sysindexes, etc.?
Looks like it's time to call IBM and see if they will fix this for you, even
if it costs a few $$.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:58 AM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I ran an "oncheck -cc" on the database and most of the tables have
> errors similar to this:
>
> ERROR: accadm.<tablename> nindexes 1 != tblspace.nkeys 0 + detached
> keys
> 3
> ERROR: di_nkeys 0 != systables nindexes 1 - detached keys 3
>
> for table accadm.<tablename>.
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Tuesday, November 03, 2009 10:39 AM
> To: ids@iiug.org
> Subject: Re: dropping a database with 392 errors [17886]
>
> Yes, I'd look at the disk. Also, if you still have the original unload
> files around, you can look at the files for one or two of the
> troublesome
> tables and see if the data looks weird, just for peace of mind.
>
> Ultimately, if your SA doesn't find anything wrong at the hardware
> level,
> you may have to call IBM and pay for an out-of-support support call for
> them
> to look at this.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:34 AM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > I ran a sql script that had a row for each table that looked like
> this:
> >
> > unload to <tablename>.unl select * from <tablename>;> >
> > I thought if I did that, then it wouldn't cause any database issues
> with
> > the data.
> >
> > I'm guessing that the next step is to have the system administrator
> look
> > at the disk, even though there aren't any indicators that the dbspaces
>
> > are corrupt.
> >
> > Keith Schleicher
> > IT Database Administrator
> > B2-253B-A
> > Office: 847-286-4027
> > Cell: 224-210-8358
> > Blackberry: 2242108358@messaging.sprintpcs.com
> > Page: 2242108358@sprint.skytel.com
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Tuesday, November 03, 2009 10:15 AM
> > To: ids@iiug.org
> > Subject: Re: dropping a database with 392 errors [17884]
> >
> > How was the data extracted and loaded? Did you use onunload and onload
>
> > or
> > dbexport and dbimport (or UNLOAD/LOAD)?
> >
> > If you use onunload/onload, then yes, it is likely that the pages
> > extracted
> > from the original server were corrupted and the contents are confusing
>
> > the
> > new server causing the internal errors.
> >
> > If you used some kind of text unload and reload, then it is unlikely
> > that
> > the data migration caused the problem.
> >
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > with which I am associated either explicitly or implicitly. 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 Tue, Nov 3, 2009 at 11:00 AM, Schleicher, Keith <
> > Keith.Schleicher@searshc.com> wrote:
> >
> > > When trying to drop some of the tables, I'm getting the same 392
> > error.
> > >
> > > The database I'm trying to drop doesn't have any chunks that are
> > marked
> > > down. However, the data that is in there is from a database that has
>
> > > some bad chunks. I unloaded the data to a file from the old server,
> > > ftp'd the files over to the new server, and then loaded the data in
> > the
> > > files to the new server. Even though I unloaded the data to files,
> > > could the data in the files be corrupt and cause this? If so, we'd
> > need
> > > to get our data from a different source.
> > >
> > > Keith Schleicher
> > > IT Database Administrator
> > > B2-253B-A
> > > Office: 847-286-4027
> > > Cell: 224-210-8358
> > > Blackberry: 2242108358@messaging.sprintpcs.com
> > > Page: 2242108358@sprint.skytel.com
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > Art Kagel
> > > Sent: Tuesday, November 03, 2009 8:36 AM
> > > To: ids@iiug.org
> > > Subject: Re: dropping a database with 392 errors [17882]
> > >
> > > This sounds like one or more of your chunks in this dbspace are
> going
> > > south. Could it be that one is marked down?
> > >
> > > You will not be able to drop a dbspace that has data in it. Anyway,
> > > here's
> > > a quick script to generate an SQL script to drop all of the
> remaining
> > > tables. Perhaps then you will be able to drop the database.
> > >
> > > dbschema -d mydatabase | awk '/create table/{ printf "drop table
> > %s;\\
",> > >
> > > $3;}' >drop_tables.sql
> > >
> > > Or in dbaccess you can:
> > >
> > > unload to 'drop_tables.sql' delimiter ';'> > > select 'drop table ' || tabname
> > > from systables
> > > where tabid > 99;
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Oninit (www.oninit.com)
> > > IIUG Board of Directors (art@iiug.org)
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions
> > > and
> > > do not reflect on my employer, Oninit, the IIUG, nor any other
> > > organization
> > > with which I am associated either explicitly or implicitly. Neither
> do
> >
> > > those opinions reflect those of other individuals affiliated with
> any
> > > entity
> > > with which I am affiliated nor those of the entities themselves.
>
> Yes, I'd look at the disk. Also, if you still have the original unload > files around, you can look at the files for one or two of the troublesome > tables and see if the data looks weird, just for peace of mind. > but if there's wierd data that can't be loaded, the LOAD would complain. If it can get through LOAD, it's allowed to live in the database.