Command to check tablespaces

Do Park

Command to check tablespaces
Hello all,

We're on DB2 v7 and z/SO v1.4.

Accidentally a tabeslpace dataset was deleted during DB2 down. After
that DB2 sub-system started. The status of the tablespace was fine when
I displayed the status of tabelspace.
For example, /=DB2P DIS DB2(dbname) SPACENAM(*) RES.

I thought that DB2 display command checks whether there is a DB2
tablespaces physically and logically as well, but it did not. It just
checked DB2 catalog, not MVS catalog(ICF).

Is there a DB2 command to check physically and logically tablespace and
table are healthy?
Is there a way to check physically and logically tablespace and table
are healthy?


Best Regards,
Do.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: Command to check tablespaces
(in response to Do Park)
DB2 won't check on the physical dataset until one of
1. -START command
2. SQL references the table
3. utility references the table.

The only good thing about losing the actual dataset is the recovery
information is still available so a normal recover to current will get
everything back. (If you DROP'ed the tablespace, then you have to manually
find image copies and use a tool to extract the update information from the
logs).

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Do Park
Sent: Monday, January 16, 2006 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] Command to check tablespaces

Hello all,

We're on DB2 v7 and z/SO v1.4.

Accidentally a tabeslpace dataset was deleted during DB2 down. After
that DB2 sub-system started. The status of the tablespace was fine when
I displayed the status of tabelspace.
For example, /=DB2P DIS DB2(dbname) SPACENAM(*) RES.

I thought that DB2 display command checks whether there is a DB2
tablespaces physically and logically as well, but it did not. It just
checked DB2 catalog, not MVS catalog(ICF).

Is there a DB2 command to check physically and logically tablespace and
table are healthy?
Is there a way to check physically and logically tablespace and table
are healthy?


Best Regards,
Do.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Do Park

Re: Command to check tablespaces
(in response to Mike Bell)
Hello Mike,

Thank you for your update.

After I stoped and started the tablespace, the status of tablespace was
fine (no error). I think that DB2 checked the status of the tablespace in
DB2 Catlog and Directory.

When I ran SQL Query against a table on the tablespace, I got an error(-
904).

when I ran DB2 utillities against the table, Some utilites ran
successfully. For example, QUIESCE utility and Check data utility ran
successfully. This doesn't make sense to me.

I'd like to make automation to check physical tablespaces.
Is there anyone who has this kind of automation at your shop?

Best Regards,
Do.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

BALACHANDRAN CHANDRASEKARAN

Re: Command to check tablespaces
(in response to Do Park)
Hello,

I have done a rexx recently somewhat close to this. The REXX would query
all the tablespaces and indexspaces and form the LDS name (partly
qualified). Then, the LISTCAT or REXX SYSDSN functions could be used to
see if the tablespace is healthy physically as well. In fact, with the
LISTCAT, I managed to get the EXTENTS and see the list of tablespaces
that might need "increase in space" or "reorg" most likely. I think
with these commands, to write a rexx is quite simple. If you want, let
me know.. I have no copyright issues to share with the group ;-)

Regards,
Bala.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Do Park
Sent: Tuesday, January 17, 2006 4:17 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Command to check tablespaces


Hello Mike,

Thank you for your update.

After I stoped and started the tablespace, the status of tablespace was
fine (no error). I think that DB2 checked the status of the tablespace
in
DB2 Catlog and Directory.

When I ran SQL Query against a table on the tablespace, I got an error(-
904).

when I ran DB2 utillities against the table, Some utilites ran
successfully. For example, QUIESCE utility and Check data utility ran
successfully. This doesn't make sense to me.

I'd like to make automation to check physical tablespaces.
Is there anyone who has this kind of automation at your shop?

Best Regards,
Do.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Command to check tablespaces
(in response to BALACHANDRAN CHANDRASEKARAN)
Hi Do,

I think the information about restricted conditions is kept in the DBET
(DataBase Exception Table), which is an in-memory structure. If I remember
correctly, it is stored with the checkpoint information (in the log
stream). If DB2 needed to access the actual VSAM file to get this info,
then a -DIS DB(*) SP(*) RESTRICT would take ages on all but the smallest
systems.

I'm pretty sure DB2 doesn't check on VSAM files when doing a -START DB()
TS(). It doesn't open the file then, so why should it?

When DB2 needs to access table data, it obviously has to read from the
file, so it would discover if it is gone. With utilities, it also depends
on whether the file has to be read. This would be the case for a REORG but
not for a LOAD REPLACE. A QUIESCE just drains writers from the TS and
records the RBA where this was achieved. If the file is closed (which DB2
can tell from SYSLGRNX; also if the file is deallocated then it is not
being accessed), there is no need to access it. I don't remember whether
QUIESCE updates any header info if the file was active, I suspect not.
CHECK DATA would only read the TS if there was something to check - i.e.
the table in there must be a child in some FK relationship. (Just looking
at a CHECKP restricted state would not be enough, the file might have been
changed by DSN1COPY).

You want to know whether all files are still there. Then you need a
program to list all datasets and then compare them with the info in the
Catalog/Directory - you can get a list of expected datasets from the info
in SYSTABLEPART and SYSINDEXPART (plus hardcoded values for the DSNDBD01
datasets). You need VCAT, DBNAME, TSNAME, PARTITION, IPREFIX.
There are two restrictions: indexes on Declared Global Temporary Tables
(DGTTs) are not recorded in the Catalog (they have index space names
tempdbname.TIXnnnnn); and if you've got multi-dataset segmented or simple
TSs, there is no way to get the number of datasets from the Cat/Dir. You
can check for an unbroken sequence Annn, but if the last file is missing,
you can't detect it.

There are two ways to list datasets: one is LISTCAT (relatively easy,
slow, very limited selection patterns) or the Catalog Search interface
IGGCSI00. Much faster but more difficult to set up. I've got a combined
REXX/SAS program to build the files list. I'll put it on the IDUG Insider
Code Place in a moment. In SYS1.SAMPLIB(IGGCSIRX) you can find the
IBM-supplied sample REXX. Note that the last time I looked at it (which is
5 years ago), it was buggy and didn't actually work, so test carefully...

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany








Do Park <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
16-01-06 23:47

To
[login to unmask email]
cc



Subject
Re: [DB2-L] Command to check tablespaces






Hello Mike,

Thank you for your update.

After I stoped and started the tablespace, the status of tablespace was
fine (no error). I think that DB2 checked the status of the tablespace in
DB2 Catlog and Directory.

When I ran SQL Query against a table on the tablespace, I got an error(-
904).

when I ran DB2 utillities against the table, Some utilites ran
successfully. For example, QUIESCE utility and Check data utility ran
successfully. This doesn't make sense to me.

I'd like to make automation to check physical tablespaces.
Is there anyone who has this kind of automation at your shop?

Best Regards,
Do.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Robert Lawrence

Re: Command to check tablespaces
(in response to Michael Ebert)
SYSTABLEPART and SYSINDEXPART have a column DSNUM which records the number
of datasets that a non-partitioned tablespace and non partitioned index has.
Also remember that if the tablespace/index have been defined with the DEFINE
NO attribute the underlying VSAM dataset is not created until the first
record is inserted into a table defined in the tablespace(very helpful for
ERP system with many unused objects). I would check the SPACE(F) column in
these tables for a value of -1

Bob Lawrence
DBA
Boscov's Dept Stores LLc


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of Michael Ebert
Sent: Tuesday, January 17, 2006 3:19 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Command to check tablespaces



Hi Do,

I think the information about restricted conditions is kept in the DBET
(DataBase Exception Table), which is an in-memory structure. If I remember
correctly, it is stored with the checkpoint information (in the log stream).
If DB2 needed to access the actual VSAM file to get this info, then a -DIS
DB(*) SP(*) RESTRICT would take ages on all but the smallest systems.

I'm pretty sure DB2 doesn't check on VSAM files when doing a -START DB()
TS(). It doesn't open the file then, so why should it?

When DB2 needs to access table data, it obviously has to read from the
file, so it would discover if it is gone. With utilities, it also depends on
whether the file has to be read. This would be the case for a REORG but not
for a LOAD REPLACE. A QUIESCE just drains writers from the TS and records
the RBA where this was achieved. If the file is closed (which DB2 can tell
from SYSLGRNX; also if the file is deallocated then it is not being
accessed), there is no need to access it. I don't remember whether QUIESCE
updates any header info if the file was active, I suspect not. CHECK DATA
would only read the TS if there was something to check - i.e. the table in
there must be a child in some FK relationship. (Just looking at a CHECKP
restricted state would not be enough, the file might have been changed by
DSN1COPY).

You want to know whether all files are still there. Then you need a
program to list all datasets and then compare them with the info in the
Catalog/Directory - you can get a list of expected datasets from the info in
SYSTABLEPART and SYSINDEXPART (plus hardcoded values for the DSNDBD01
datasets). You need VCAT, DBNAME, TSNAME, PARTITION, IPREFIX.
There are two restrictions: indexes on Declared Global Temporary Tables
(DGTTs) are not recorded in the Catalog (they have index space names
tempdbname.TIXnnnnn); and if you've got multi-dataset segmented or simple
TSs, there is no way to get the number of datasets from the Cat/Dir. You can
check for an unbroken sequence Annn, but if the last file is missing, you
can't detect it.

There are two ways to list datasets: one is LISTCAT (relatively easy,
slow, very limited selection patterns) or the Catalog Search interface
IGGCSI00. Much faster but more difficult to set up. I've got a combined
REXX/SAS program to build the files list. I'll put it on the IDUG Insider
Code Place in a moment. In SYS1.SAMPLIB(IGGCSIRX) you can find the
IBM-supplied sample REXX. Note that the last time I looked at it (which is 5
years ago), it was buggy and didn't actually work, so test carefully...

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm