Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS

Bharath Nunepalli

Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS

Hi all,


I ran this query and noticed that there is a segmented TS that has 2 entries in SYSTABLESPACESTATS.
SELECT
DBNAME
,NAME
FROM SYSIBM.SYSTABLESPACESTATS WHERE PARTITION=0
GROUP BY DBNAME, NAME
HAVING COUNT(NAME)>1;

This is the timeline of this particular Tablespace.
First created on -- 2009-08-11
First dropped on -- 2015-06-16
Again created on -- 2017-06-13

First entry in SYSTABLESPACESTATS has the UPDATESTATSTIME as 2015-06-16 16.30.27.641640, and the 2nd entry has UPDATESTATSTIME as today’s date (RTS externalized every 30 minutes).
I thought entries pertaining to a Tablespace will be removed from all Db2 catalog tables when a Tablespace is dropped.

Can you please help me in understanding why there are 2 entries for this particular Tablespace?
Also, what can be the reasons for a Tablespace to have UPDATESTATSTIME < CURRENT DATE when RTS are externalized every 30 mins?

 

Thanks.

Walter Jani&#223;en

AW: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Bharath Nunepalli)
Hi

Is it a PBG? Then this entry might belong to the second dataset.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Bharath Nunepalli <[login to unmask email]>
Gesendet: Donnerstag, 28. Juni 2018 23:55
An: [login to unmask email]
Betreff: [DB2-L] - Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS


Hi all,

I ran this query and noticed that there is a segmented TS that has 2 entries in SYSTABLESPACESTATS.
SELECT
DBNAME
,NAME
FROM SYSIBM.SYSTABLESPACESTATS WHERE PARTITION=0
GROUP BY DBNAME, NAME
HAVING COUNT(NAME)>1;

This is the timeline of this particular Tablespace.
First created on -- 2009-08-11
First dropped on -- 2015-06-16
Again created on -- 2017-06-13

First entry in SYSTABLESPACESTATS has the UPDATESTATSTIME as 2015-06-16 16.30.27.641640, and the 2nd entry has UPDATESTATSTIME as today’s date (RTS externalized every 30 minutes).
I thought entries pertaining to a Tablespace will be removed from all Db2 catalog tables when a Tablespace is dropped.

Can you please help me in understanding why there are 2 entries for this particular Tablespace?
Also, what can be the reasons for a Tablespace to have UPDATESTATSTIME < CURRENT DATE when RTS are externalized every 30 mins?



Thanks.

-----End Original Message-----
Attachments

  • image001.png (2.6k)

alain pary

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Bharath Nunepalli)

Hi, 

In the past the RTS are not really catalog table. So you can have orphan rows in it . If the drop was in concurrence with a refresh RTS . DB2  skip the delete to free the other catalog tables. In your case as you recreate the TS with the same name , you have two rows. 

regards

Alain

Bharath Nunepalli

RE: AW: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Walter Janißen)

Walter,

No, this is regular Seg TS.

 

Bharath Nunepalli,

Senior DB2 DBA.

Bharath Nunepalli

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to alain pary)

Alain,

These are the timestamps for the events.

2015-06-16 16.30.27.077683 -- TS is dropped

2015-06-16 16.30.27.641640 -- UPDATESTATSTIME in SYSTABLESPACESTATS

 

I see milli seconds different between 2 events. 

 

Bharath Nunepalli,

Senior DB2 DBA.

Kai Stroh

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Bharath Nunepalli)

Bharat,

looks like a glitch to me. If I had to take a guess, I'd say Db2 was in the process of externalizing the RTS to SYSTABLESPACESTATS almost exactly when the tablespace was dropped. The entry still made it into the RTS table and when a tablespace with the same name was created later, it caused Db2 to insert another row with the same object name.

If the tablespace does not require 24x7 availability, I would stop it, delete the rows referring to it from SYSTABLESPACESTATS, then start the tablespace and run REORG on it. This should give you a valid RTS baseline and thing should be smooth from now on.

Best regards

Kai

 

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Kai Stroh

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Kai Stroh)

Also, UPDATESTATSTIME refers to the last time RUNSTATS was run, not the last time RTS were externalized. So it is not surprising that the timestamp is older than CURRENT TIMESTAMP - 30 MINUTES. It depends on how often you run RUNSTATS.

 

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Roy Boxwell

Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Kai Stroh)
Sorry to be pedantic but STATSLASTTIME is when RUNSTATS last ran and UPDATESTATSTIME is indeed the last time the RTS got externalized.

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Kai Stroh [mailto:[login to unmask email]
Sent: Sunday, July 1, 2018 2:56 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS


Also, UPDATESTATSTIME refers to the last time RUNSTATS was run, not the last time RTS were externalized. So it is not surprising that the timestamp is older than CURRENT TIMESTAMP - 30 MINUTES. It depends on how often you run RUNSTATS.


--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5 https://www.ubs-hainer.com/solutions/bcv5 .
Learn how the Test Data Management Field Guide http://tdm.ubs-hainer.com can help you to improve your own process.

-----End Original Message-----

Bharath Nunepalli

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Kai Stroh)

I don’t think MODIFY STATISTICS will be able to delete unwanted entries from SYSTABLESPACESTATS.

And, I do not think it’s good idea to run DELETE query either. Is there any other way to delete unwanted entries from SYSTABLESPACESTATS?

Kai Stroh

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Roy Boxwell)

I stand corrected. I should have consulted the SQL Reference before hitting "Send"...

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Roy Boxwell

Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Bharath Nunepalli)
I just do a compare against the Catalog and delete all TP and IP that are not in the catalog.. Always find one or two every month.

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Tuesday, July 3, 2018 8:49 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS


I don’t think MODIFY STATISTICS will be able to delete unwanted entries from SYSTABLESPACESTATS.

And, I do not think it’s good idea to run DELETE query either. Is there any other way to delete unwanted entries from SYSTABLESPACESTATS?

-----End Original Message-----

Bharath Nunepalli

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Roy Boxwell)

Roy,

Do you run DELETE SQL statement on catalog table?

 

Bharath Nunepalli,

Senior DB2 DBA.

Roy Boxwell

Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Bharath Nunepalli)
Yes of course! It is an updatable catalog table – No problem to insert, update or delete. If the data is invalid then a delete is sometimes the only way!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Bharath Nunepalli [mailto:[login to unmask email]
Sent: Thursday, July 5, 2018 1:45 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS


Roy,

Do you run DELETE SQL statement on catalog table?



Bharath Nunepalli,

Senior DB2 DBA.

-----End Original Message-----

Bharath Nunepalli

RE: Two entries for a Seg TS in SYSIBM.SYSTABLESPACESTATS
(in response to Roy Boxwell)

Roy,

Thanks for the suggestion.

 

Bharath Nunepalli,

Senior DB2 DBA.