REORGSCANACCESS nad LASTDATACHANGE having NULL in SYSTABLESPACESTATS

Bharath Nunepalli

REORGSCANACCESS nad LASTDATACHANGE having NULL in SYSTABLESPACESTATS

Hi all,
We are trying to figure out the inactive tables with the help of data in REORGSCANACCESS and LASTDATACHANGE columns in SYSTABLESPACESTATS.

I noticed that REORGSCANACCESS and LASTDATACHANGE are having nulls for some Tablespaces.
E.g.,
1. REORG on TS1 was done in 2013. But, REORGSCANACCESS and LASTDATACHANGE have nulls
2. REORG on TS2 was done in 2019, REORGSCANACCESS has value 0 and LASTDATACHANGE has null


Can someone please explain how this is possible?

 

Thanks,
Bharath Nunepalli

steen rasmussen

REORGSCANACCESS nad LASTDATACHANGE having NULL in SYSTABLESPACESTATS
(in response to Bharath Nunepalli)
Those hew RTS columns were added recently so a reorg needs to happen before Db2 will start counting.

Steen

Sent from my iPhone

> On Jul 11, 2019, at 10:37 AM, Bharath Nunepalli <[login to unmask email]> wrote:
>
> Hi all,
> We are trying to figure out the inactive tables with the help of data in REORGSCANACCESS and LASTDATACHANGE columns in SYSTABLESPACESTATS.
>
> I noticed that REORGSCANACCESS and LASTDATACHANGE are having nulls for some Tablespaces.
> E.g.,
> 1. REORG on TS1 was done in 2013. But, REORGSCANACCESS and LASTDATACHANGE have nulls
> 2. REORG on TS2 was done in 2019, REORGSCANACCESS has value 0 and LASTDATACHANGE has null
>
>
> Can someone please explain how this is possible?
>
>
>
> Thanks,
> Bharath Nunepalli
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Bharath Nunepalli

RE: REORGSCANACCESS nad LASTDATACHANGE having NULL in SYSTABLESPACESTATS
(in response to steen rasmussen)

Steen,
Thanks for the response.

 

A value in REORGSCANACCESS and NULL in LASTDATACHANGE means that the table is used only for SELECT. is that correct?

 

Bharath Nunepalli,

Senior DB2 DBA.

steen rasmussen

REORGSCANACCESS nad LASTDATACHANGE having NULL in SYSTABLESPACESTATS
(in response to steen rasmussen)
Not necessarily when you read the details: 
REORGSCANACCESS:The number of times data is accessed for SELECT,FETCH, searched UPDATE, or searched DELETE since thelast CREATE, LOAD REPLACE or REORG, or since theobject was created.A null value indicates that the numberof times data is accessed is unknown.
LASTDATACHANGE:The last time that this row was updated because data wasmodified in the table space or partition. The timestampreflects the time at which the real-time statistics table wasupdated, and not the time at which the data in the tablespace or partition was modified. Physical data changessuch as reorganization of data are not reflected in thiscolumn.
Steen On Thursday, July 11, 2019, 10:47:38 AM EDT, steen rasmussen <[login to unmask email]> wrote:

Those hew RTS columns were added recently so a reorg needs to happen before Db2 will start counting.
Steen

Sent from my iPhone
On Jul 11, 2019, at 10:37 AM, Bharath Nunepalli <[login to unmask email]> wrote:



Hi all,
We are trying to figure out the inactive tables with the help of data in REORGSCANACCESS and LASTDATACHANGE columns in SYSTABLESPACESTATS.

I noticed that REORGSCANACCESS and LASTDATACHANGE are having nulls for some Tablespaces.
E.g.,
1. REORG on TS1 was done in 2013. But, REORGSCANACCESS and LASTDATACHANGE have nulls
2. REORG on TS2 was done in 2019, REORGSCANACCESS has value 0 and LASTDATACHANGE has null


Can someone please explain how this is possible?

 

Thanks,
Bharath Nunepalli

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

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
http://www.ESAIGroup.com/idug



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Michael Hannan

RE: REORGSCANACCESS nad LASTDATACHANGE having NULL in SYSTABLESPACESTATS
(in response to Bharath Nunepalli)

In Reply to Bharath Nunepalli:

Hi all,
We are trying to figure out the inactive tables with the help of data in REORGSCANACCESS and LASTDATACHANGE columns in SYSTABLESPACESTATS.

I noticed that REORGSCANACCESS and LASTDATACHANGE are having nulls for some Tablespaces.
E.g.,
1. REORG on TS1 was done in 2013. But, REORGSCANACCESS and LASTDATACHANGE have nulls
2. REORG on TS2 was done in 2019, REORGSCANACCESS has value 0 and LASTDATACHANGE has null

What does one do with Inactive Tablespaces? Set to CLOSE YES? HSM archive them? 

Hopefully Steen covered it, so it appears that it is not known if TS1 is inactive, since no reorg recent enough. TS2 does seem to be inactive for read and update since the last Reorg. LASTDATACHANGE suggests no changes, and REORGSCANACCESS suggests no reads (but does not rule out Inserts).  

DB2 V12 added GETPAGES column so that we could see the number of Getpages per Scan/access, although Getpages might also occur for Inserts of course. Not sure if R.I. check Getpages are included. High Getpages per Scan access suggests long scans and could even be TS Scans.

Would be wise to check if the indexes are in use or not too, LASTUSED in SYSINDEXSPACESTATS, since usage could be "Index Only".

Not sure I ever wanted to know "inactive" tablespaces. They may not be causing a problem. Ha ha. You never know when application suddenly uses some obscure ad hoc function that suddenly will use them.

Tangent: Can we drop Packages that have not been used for a long time? Nope, not unless we are feeling very lucky. Packages that have suddenly been used after no use for a year or two, do happen. Same could occur for a Tablespace.

Might be interesting to see if the "inactive" table appears in SYSPACKDEP. Even then, it is possible for Dynamic to use it.

My message is probably to be more concerned with things that are heavily used and costly. One could drop indexes not used for a very long time if they are really a problem, and will not result in a potential performance blowup in the future. 

There once was an idea to cleanup unused Packages and non Package DBRMs, before going to DB2 V10 and to Extended Management, but a somewhat misguided one. I also love to complain about overly jealous cleanup of Explain History. We need to see what might have caused a performance changes weeks ago.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Avram Friedman

RE: REORGSCANACCESS nad LASTDATACHANGE having NULL in SYSTABLESPACESTATS
(in response to Michael Hannan)

In the code page section (under articles and content) there is a utility for initializing RTS statistics sp collection will start.

Code Page is where IDUG members contribute or borrow sample code.  Feel free to do both.

Avram Friedman
DB2-L hall of fame contributor
DB2-L 'past' administrator

[login to unmask email]