when tablespace was last accessed?

william giannelli

when tablespace was last accessed?

what column would I look at to determine when a tablespace was last accessed?

thanks

Bill

Peter Conlin

RE: when tablespace was last accessed?
(in response to william giannelli)

If you know the dataset name(s) of the tablespace, you can look at the reference date in ISPF 3.4 or 3.2.

The same information is available via ISMF or DCOLLECT (IDCAMS).

It's fairly trivial to generate listings for all tablespaces in a database using Db2's standard vcatname.DSNDBC|DSNDBD.dbname convention as the HLQ with ISPF 3.4 or ISMF or DCOLLECT.

 

pete conlin

Kirk Hampton

when tablespace was last accessed?
(in response to william giannelli)
There is no such data maintained by DB2.
If you think about it, would you really want DB2 to update something in the
catalog EVERY time a tablespace is accessed ?

Peter described one way of obtaining a date, from the z/OS catalog info on
the dataset, but it's not going to tell you if the access was by a human, a
process, or some regularly-scheduled utility such as COPY or RUNSTATS.

What do you hope to achieve by getting this date ? Retire an old unused
table ? I have dealt with this by first ALTERing the table to AUDIT ALL.
Then we had a daily-scheduled DB2/PM (now Omegamon) AUDIT report, and I
would watch this every day for, say, 90 days. After that I would STOP the
tablespace, and leave it for some period of time, maybe another 90 days,
and finally DROP it. All this after contacting the appropriate heads of
Application Support for their concurrence.

Thanks,

Kirk Hampton



On Tue, Sep 10, 2019 at 2:41 PM william giannelli <[login to unmask email]>
wrote:

> what column would I look at to determine when a tablespace was last
> accessed?
>
> thanks
>
> Bill
>
> -----End Original Message-----
>

Tim Hare

RE: when tablespace was last accessed?
(in response to william giannelli)

If you turn auditing on for the table and are capturing the SMF records for that auditing, there are IFCIDs 143 (for change) and 144 (for first read) sent to SMF.  You can then summarize these with your tool of choice.

Walter Jani&#223;en

AW: when tablespace was last accessed?
(in response to william giannelli)
Hi William

In SYSTABLESPACESTATS there are two columns, which are updated each time a tablespace is accessed: REORGSCANACCESS and GETPAGES. GETPAGES is also increased, if you ran e.g. a COPY-Utility, but REORGSCANACCESS is only increased, if the tablespace is touched by SQL. In V12 you can use the Db2’s history-support and then you can see, when a tablespace was last accessed.

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: william giannelli <[login to unmask email]>
Gesendet: Dienstag, 10. September 2019 21:41
An: [login to unmask email]
Betreff: [EXTERN] [DB2-L] - when tablespace was last accessed?


what column would I look at to determine when a tablespace was last accessed?

thanks

Bill

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

  • image001.png (2.6k)

Russell Peters

RE: when tablespace was last accessed?
(in response to Kirk Hampton)

We identify potentially unused tables via SYSTABLESPACESTATS then start an audit policy on each identified table. I receive a daily report of any activity. After a period of time including a month-end process I put the tablespace in UT status. This allows utilities to function but no sql access.

william giannelli

RE: when tablespace was last accessed?
(in response to Russell Peters)

Thank you all for your replies. We are being pushed by our MVS storage people to delete old unused datasets.

thanks

Bill

Tim Hare

RE: when tablespace was last accessed?
(in response to william giannelli)

Has anyone ever created an RFE to IBM to maintain a 'last access' date in SYSTABLES or elsewhere? It would seem to me to be less overhead than auditing and recording the audit records.

Javier Estrada Benavides

RE: when tablespace was last accessed?
(in response to william giannelli)

mmm... weird, normally it would be the storage team to run DCOLLECT to get the report and then ask you about those unused tablespaces. There are a few products that can easily get that kind of report and export it in CSV for anyone to see.

Have a great day :)

 

Javier Estrada Benavides, Czech Republic / Mexico

IBM Champion for Analytics

IBM Certified System Administrator - Db2 12 for z/OS

IBM Db2 12 DBA for z/OS - 2018 (the ugly brown badge from IBM Open Badge Program)

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS

Greg Palgrave

RE: when tablespace was last accessed?
(in response to Tim Hare)

FWIW,

Db2 LUW has a LASTUSED column in SYSIBM.SYSTABLES and the SYSCAT.TABLES view, so I'd imagine an RFE for the z/OS  environment should be an easy win.

Cheers

Greg


In Reply to Tim Hare:

Has anyone ever created an RFE to IBM to maintain a 'last access' date in SYSTABLES or elsewhere? It would seem to me to be less overhead than auditing and recording the audit records.