Recently, I discovered that somehow the tablespace for the system
catalog tables was dropped (SQL1008N) on list tablespace containers
for 0.
The error message also says that the use of the database be
discontinued (I assume this is because the catalog tables are
corrupt). Why then allow the database to be operational, shouldn't the
database manager mark the database as bad and forcibly prevent
connections?
Otherwise, what is the danger is continuing operations?

"JS" <fyi85@hotmail.com> wrote in message
news:e9c166ca.0504120804.21aaebbe@posting.google.c om...
> Recently, I discovered that somehow the tablespace for the system
> catalog tables was dropped (SQL1008N) on list tablespace containers
> for 0.
> The error message also says that the use of the database be
> discontinued (I assume this is because the catalog tables are
> corrupt). Why then allow the database to be operational, shouldn't the
> database manager mark the database as bad and forcibly prevent
> connections?
> Otherwise, what is the danger is continuing operations?

Although the database was not be marked as 'BAD', and you still can connect
to the database. But once you can't access system tables, that means you
couldn't run any sql stmt, any db2 commands. You can't cause more hurt to
the current database.

"Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message news:<Rj17e.1703$bc2.825@newssvr17.news.prodigy.co m>...
> "JS" <fyi85@hotmail.com> wrote in message
> news:e9c166ca.0504120804.21aaebbe@posting.google.c om...
> > Recently, I discovered that somehow the tablespace for the system
> > catalog tables was dropped (SQL1008N) on list tablespace containers
> > for 0.
> > The error message also says that the use of the database be
> > discontinued (I assume this is because the catalog tables are
> > corrupt). Why then allow the database to be operational, shouldn't the
> > database manager mark the database as bad and forcibly prevent
> > connections?
> > Otherwise, what is the danger is continuing operations?
>
> Although the database was not be marked as 'BAD', and you still can connect
> to the database. But once you can't access system tables, that means you
> couldn't run any sql stmt, any db2 commands. You can't cause more hurt to
> the current database.
No, I'm afarid that is incorrect. The database is entirely useable.
The fact that the tablespace has been dropped does not prevent
querying the syscat or sysibm tables, nor does it prevent application
connects and sql statements. The only difference is that the database
snapshot show the "Catalog database partition number" =1, (this is on
multi node system). So this looks like serious 'bug' in my opinion.

"JS" <fyi85@hotmail.com> wrote in message
news:e9c166ca.0504130518.7f6a4101@posting.google.c om...
> "Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
news:<Rj17e.1703$bc2.825@newssvr17.news.prodigy.co m>...
> > "JS" <fyi85@hotmail.com> wrote in message
> > news:e9c166ca.0504120804.21aaebbe@posting.google.c om...
> > > Recently, I discovered that somehow the tablespace for the system
> > > catalog tables was dropped (SQL1008N) on list tablespace containers
> > > for 0.
> > > The error message also says that the use of the database be
> > > discontinued (I assume this is because the catalog tables are
> > > corrupt). Why then allow the database to be operational, shouldn't the
> > > database manager mark the database as bad and forcibly prevent
> > > connections?
> > > Otherwise, what is the danger is continuing operations?
> >
> > Although the database was not be marked as 'BAD', and you still can
connect
> > to the database. But once you can't access system tables, that means you
> > couldn't run any sql stmt, any db2 commands. You can't cause more hurt
to
> > the current database.
> No, I'm afarid that is incorrect. The database is entirely useable.
> The fact that the tablespace has been dropped does not prevent
> querying the syscat or sysibm tables, nor does it prevent application
> connects and sql statements.
==========
Well, I really got confused. Did I miss something? Are you going to tell me
for whatever reason cause the system catalog
tablespace was dropped. But the system tables are still available. And they
are still allowed to access?
You can create new tables/indexes? You can run query? You can run runstats,
....?


> The only difference is that the database
> snapshot show the "Catalog database partition number" =1, (this is on
> multi node system). So this looks like serious 'bug' in my opinion.
================
The catalog tablespace can be built on the db partition #1. What do you mean
it is the only difference here?
Is that you try to run "list tablespace containers for 0" on a none-catalog
db partition. You can only
find the system catalog tablespace on catalog node.

Fan Ruo Xin wrote:

>
> "JS" <fyi85@hotmail.com> wrote in message
> news:e9c166ca.0504130518.7f6a4101@posting.google.c om...
>> "Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
> news:<Rj17e.1703$bc2.825@newssvr17.news.prodigy.co m>...
>> > "JS" <fyi85@hotmail.com> wrote in message
>> > news:e9c166ca.0504120804.21aaebbe@posting.google.c om...
>> > > Recently, I discovered that somehow the tablespace for the system
>> > > catalog tables was dropped (SQL1008N) on list tablespace containers
>> > > for 0.
>> > > The error message also says that the use of the database be
>> > > discontinued (I assume this is because the catalog tables are
>> > > corrupt). Why then allow the database to be operational, shouldn't
>> > > the database manager mark the database as bad and forcibly prevent
>> > > connections?
>> > > Otherwise, what is the danger is continuing operations?
>> >
>> > Although the database was not be marked as 'BAD', and you still can
> connect
>> > to the database. But once you can't access system tables, that means
>> > you couldn't run any sql stmt, any db2 commands. You can't cause more
>> > hurt
> to
>> > the current database.
>> No, I'm afarid that is incorrect. The database is entirely useable.
>> The fact that the tablespace has been dropped does not prevent
>> querying the syscat or sysibm tables, nor does it prevent application
>> connects and sql statements.
> ==========
> Well, I really got confused. Did I miss something? Are you going to tell
> me for whatever reason cause the system catalog
> tablespace was dropped. But the system tables are still available. And
> they are still allowed to access?
> You can create new tables/indexes? You can run query? You can run
> runstats, ...?

The only thing I can imagine here is that the container was deleted
physically, i.e. not via ALTER TABLESPACE or something and, in addition,
DB2 still has some data in the buffer pool. So the queries against the
catalog can be answered with the buffer pool only. After recycling the
system the error messages should pop up right away.

Summarized, I think it's completely unclear what really happened for the
damage to occur.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

"Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message news:<Puj7e.2205$bc2.404@newssvr17.news.prodigy.co m>...
> "JS" <fyi85@hotmail.com> wrote in message
> news:e9c166ca.0504130518.7f6a4101@posting.google.c om...
> > "Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
> news:<Rj17e.1703$bc2.825@newssvr17.news.prodigy.co m>...
> > > "JS" <fyi85@hotmail.com> wrote in message
> > > news:e9c166ca.0504120804.21aaebbe@posting.google.c om...
> > > > Recently, I discovered that somehow the tablespace for the system
> > > > catalog tables was dropped (SQL1008N) on list tablespace containers
> > > > for 0.
> > > > The error message also says that the use of the database be
> > > > discontinued (I assume this is because the catalog tables are
> > > > corrupt). Why then allow the database to be operational, shouldn't the
> > > > database manager mark the database as bad and forcibly prevent
> > > > connections?
> > > > Otherwise, what is the danger is continuing operations?
> > >
> > > Although the database was not be marked as 'BAD', and you still can
> connect
> > > to the database. But once you can't access system tables, that means you
> > > couldn't run any sql stmt, any db2 commands. You can't cause more hurt
> to
> > > the current database.
> > No, I'm afarid that is incorrect. The database is entirely useable.
> > The fact that the tablespace has been dropped does not prevent
> > querying the syscat or sysibm tables, nor does it prevent application
> > connects and sql statements.
> ==========
> Well, I really got confused. Did I miss something? Are you going to tell me
> for whatever reason cause the system catalog
> tablespace was dropped. But the system tables are still available. And they
> are still allowed to access?
> You can create new tables/indexes? You can run query? You can run runstats,
> ...?
>
>
> > The only difference is that the database
> > snapshot show the "Catalog database partition number" =1, (this is on
> > multi node system). So this looks like serious 'bug' in my opinion.
> ================
> The catalog tablespace can be built on the db partition #1. What do you mean
> it is the only difference here?
> Is that you try to run "list tablespace containers for 0" on a none-catalog
> db partition. You can only
> find the system catalog tablespace on catalog node.
Yup, my mistake. Somehow, the catalog tablespace was created on node
1, the confusion results from the error message which says
'discontinue use of the database'. But you are right, the catalog can
be created on any node, I got further confused because
syscat.tablespaces doesn't say which node the tablespace is on, it
just gives the container id number, which of course doesnt exits on
node 0, but does exist on node 1.
Thanks for your help in clearing this up.

fyi85@hotmail.com (JS) wrote in message news:<e9c166ca.0504140552.2019eada@posting.google. com>...
> "Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message news:<Puj7e.2205$bc2.404@newssvr17.news.prodigy.co m>...
> > "JS" <fyi85@hotmail.com> wrote in message
> > news:e9c166ca.0504130518.7f6a4101@posting.google.c om...
> > > "Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
> > news:<Rj17e.1703$bc2.825@newssvr17.news.prodigy.co m>...
> > > > "JS" <fyi85@hotmail.com> wrote in message
> > > > news:e9c166ca.0504120804.21aaebbe@posting.google.c om...
> > > > > Recently, I discovered that somehow the tablespace for the system
> > > > > catalog tables was dropped (SQL1008N) on list tablespace containers
> > > > > for 0.
> > > > > The error message also says that the use of the database be
> > > > > discontinued (I assume this is because the catalog tables are
> > > > > corrupt). Why then allow the database to be operational, shouldn't the
> > > > > database manager mark the database as bad and forcibly prevent
> > > > > connections?
> > > > > Otherwise, what is the danger is continuing operations?
> > > >
> > > > Although the database was not be marked as 'BAD', and you still can
> > connect
> > > > to the database. But once you can't access system tables, that means you
> > > > couldn't run any sql stmt, any db2 commands. You can't cause more hurt
> > to
> > > > the current database.
> > > No, I'm afarid that is incorrect. The database is entirely useable.
> > > The fact that the tablespace has been dropped does not prevent
> > > querying the syscat or sysibm tables, nor does it prevent application
> > > connects and sql statements.
> > ==========
> > Well, I really got confused. Did I miss something? Are you going to tell me
> > for whatever reason cause the system catalog
> > tablespace was dropped. But the system tables are still available. And they
> > are still allowed to access?
> > You can create new tables/indexes? You can run query? You can run runstats,
> > ...?
> >
> >
> > > The only difference is that the database
> > > snapshot show the "Catalog database partition number" =1, (this is on
> > > multi node system). So this looks like serious 'bug' in my opinion.
> > ================
> > The catalog tablespace can be built on the db partition #1. What do you mean
> > it is the only difference here?
> > Is that you try to run "list tablespace containers for 0" on a none-catalog
> > db partition. You can only
> > find the system catalog tablespace on catalog node.
> Yup, my mistake. Somehow, the catalog tablespace was created on node
> 1, the confusion results from the error message which says
> 'discontinue use of the database'. But you are right, the catalog can
> be created on any node, I got further confused because
> syscat.tablespaces doesn't say which node the tablespace is on, it
> just gives the container id number, which of course doesnt exits on
> node 0, but does exist on node 1.
> Thanks for your help in clearing this up.

Kurt
My only point now is that this error message sql1008 is somewhat
misleading, because there really is no need to discontinue use of the
database, it is just that the catalog tables were not where I was
expecting them to be, so perhaps that error message could be re
written