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

Roy Boxwell

Antwort: Re: [DB2-L] Command to check tablespaces
also do not forget that DSNUM is only updated by RUNSTATS/REORG ...when
the next dataset is created (Eg by doing an ALTER PIECESIZE then the
catalog is not updated even though the
TS now exists on many datasets) a RUNSTATS is still needed here...

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-35
E-mail [login to unmask email]
Homepage www.seg.de





Robert Lawrence <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
17.01.2006 16:23
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: Re: [DB2-L] Command to check tablespaces


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


---------------------------------------------------------------------------------
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 Roy Boxwell)
Unfortunately the column is populated by RUNSTATS only, not in real time,
so it cannot be considered reliable (it can be manually updated as well).
Also the count includes empty datasets with the proper name (e.g.
pre-allocated datasets), not just the ones actually being in use for
holding data.

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








Robert Lawrence <[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]>
17-01-06 16:23

To
[login to unmask email]
cc



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






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

---------------------------------------------------------------------------------
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)
Thanks for reminding me of these. In our case we do not update stats except
with RUNSTATS which for production is run on at least a weekly basis on most
tablespaces and indexes

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 10:45 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Command to check tablespaces



Unfortunately the column is populated by RUNSTATS only, not in real time,
so it cannot be considered reliable (it can be manually updated as well).
Also the count includes empty datasets with the proper name (e.g.
pre-allocated datasets), not just the ones actually being in use for holding
data.

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







Robert Lawrence <[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]>
17-01-06 16:23
To [login to unmask email]
cc

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







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
--------------------------------------------------------------------------
------- 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