Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp

Aurora Emanuela Dellanno

Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp

Hey guys,

 

just checking if there are any good creative ideas on how best to check about used and not used packages - would it be a combination of the timestamp in the LASTUSED column of SYSPACKAGE together with the date in the BINDTIME in case that is '0001-01-01'?

 

I have found a nice query in an IBM System mag from Troy *hello Troy!* and that's what I am going on - or has Db2 found a new miraculous way of confusing me in the last few months when I was elsewhere busy?

 

Thanks in advance.

 

Aurora

Michael Hannan

RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Aurora Emanuela Dellanno)

Aurora,

LASTUSED for a Package does indeed tell when a Package was last used, for current and old versions of the Package, and for each collection.  Even Package Copies can show you when the older bind copies were last used.

BINDTIME is not at all useful for this purpose. Packages get rebound even if no usage has occurred for a long time.

Note that even packages not used for a long time, may get used in the future for some obscure function of the application not in common or daily use, like change of settings for example.

Generally it may be safe to free up old versions though, if not used for a very long time.

Michael Hannan


In Reply to Aurora Emanuela Dellanno:

Hey guys,

 

just checking if there are any good creative ideas on how best to check about used and not used packages - would it be a combination of the timestamp in the LASTUSED column of SYSPACKAGE together with the date in the BINDTIME in case that is '0001-01-01'?

 

I have found a nice query in an IBM System mag from Troy *hello Troy!* and that's what I am going on - or has Db2 found a new miraculous way of confusing me in the last few months when I was elsewhere busy?

 

Thanks in advance.

 

Aurora



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Chris Muncan

RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Michael Hannan)

We use the LASTUSED column to determine "old" or "unused" packages and free them accordingly.  We usually do some analysis every year or 2 years to see if we can cleanup old packages especially in non-prod environments.

Aurora Emanuela Dellanno

RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Aurora Emanuela Dellanno)

Thanks, Michael and Chris.

 

I am looking at the BINDDATE column also, for the following reasons:

 

1. every time a BIND command is issued, the value of LASTUSED resets to '0001-01-01'

2. a REBIND command changes values in BINDTIME but not LASTUSED

 

therefore, I am looking to see if there is a more recent (than whatever) BINDTIME where my LASTUSED is at default.

 

My first phase is to look at all packages that have a BINDTIME older than a certain date, and default values in LASTUSED, this means the package has not even been rebound since the date in BINDTIME, and therefore I'll free it.

 

I then have this SQL for further analysis:

 

SELECT DAYS(CURRENT DATE) - DAYS(LASTUSED) AS AGE
, LASTUSED
, SUBSTR(NAME, 1, 8) AS NAME
, SUBSTR(COLLID, 1, 26) AS COLLID
, SUBSTR(VERSION, 1, 26) AS VERSION
, HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSPACKAGE
WHERE DAYS(CURRENT DATE) - DAYS(LASTUSED) > 365
AND LASTUSED > '01.01.0001'
AND NAME LIKE '%1504%'
ORDER BY 1 DESC

 

Is my reasoning specious?

 

Thanks again.

 

Aurora

 

alain pary

RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Aurora Emanuela Dellanno)

Hello, 

To increase your query  , you can perhaps see if a recent version exists and is used in your sytem. 

Many company or deployement tools used version , to authorize multiple package with same collid and name .

regards 

 

Alain

Walter Janißen

AW: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Chris Muncan)
Hi

I think you also have to watch for the TIMESTAMP-column, which tells you, when the package was first bound. So we use LASTUSED and TIMESTAMP to decide, if a package can be dropped. But till now, we only dare to drop packages in our test-environments.

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: Chris Muncan <[login to unmask email]>
Gesendet: Donnerstag, 24. Mai 2018 18:21
An: [login to unmask email]
Betreff: [DB2-L] - RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp


We use the LASTUSED column to determine "old" or "unused" packages and free them accordingly. We usually do some analysis every year or 2 years to see if we can cleanup old packages especially in non-prod environments.

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

  • image001.png (2.6k)

Michael Hannan

RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Aurora Emanuela Dellanno)

Aurora,

I would transform your predicate from:

WHERE DAYS(CURRENT DATE) - DAYS(LASTUSED) > 365

to

WHERE LASTUSED < CURRENT DATE - 365 DAYS

This is a stage 1 predicate and more efficient.

I would probably consider cleenup of a Package Version if there exists another version with higher PCTIMESTAMP and/or CONTOKEN, and the package to be removed was LASTUSED more than 1 year ago or never used.

Not sure I understand why you ever BIND (as distinct from REBIND) a Package that is not used. BIND is for when the program was changed normally, implying it is still useful (maybe). Anyway you don't need to keep the old versions forever as possible fallback from the current version.

Beware of wasting too much time trying to decide if current version of Packages are needed any more or not. Cleanup is not very important in the overall scheme of things. If in doubt, just keep it, and don't waste your effort!

DB2 Catalog is a bit large or your Package extended management uses extra disk space? Don't worry about it too much! Just my opinion!  As Walter said, they don't dare remove Packages from Production! So they have no problems, right?

So the key point is not to waste effort on packages that were not used for a long time. I don't waste time on them while tuning access paths for example. LASTUSED is definitely my friend.

Michael Hannan

In Reply to Aurora Emanuela Dellanno:

Thanks, Michael and Chris.

 

I am looking at the BINDDATE column also, for the following reasons:

 

1. every time a BIND command is issued, the value of LASTUSED resets to '0001-01-01'

2. a REBIND command changes values in BINDTIME but not LASTUSED

 

therefore, I am looking to see if there is a more recent (than whatever) BINDTIME where my LASTUSED is at default.

 

My first phase is to look at all packages that have a BINDTIME older than a certain date, and default values in LASTUSED, this means the package has not even been rebound since the date in BINDTIME, and therefore I'll free it.

 

I then have this SQL for further analysis:

 

SELECT DAYS(CURRENT DATE) - DAYS(LASTUSED) AS AGE
, LASTUSED
, SUBSTR(NAME, 1, 8) AS NAME
, SUBSTR(COLLID, 1, 26) AS COLLID
, SUBSTR(VERSION, 1, 26) AS VERSION
, HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSPACKAGE
WHERE DAYS(CURRENT DATE) - DAYS(LASTUSED) > 365
AND LASTUSED > '01.01.0001'
AND NAME LIKE '%1504%'
ORDER BY 1 DESC

 

Is my reasoning specious?

 

Thanks again.

 

Aurora

 


Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 30, 2018 - 10:40 AM (Europe/Berlin)

Aurora Emanuela Dellanno

RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Aurora Emanuela Dellanno)

once again, thanks Alain, Walter and Michael.

 

We do not have versions.

 

Michael, thanks for the better SQL :-) as for the rest: if we DO have a default value in the BINDTIME, this could be due to a BIND, however it could have been put there during migration to DB2 10 NFM - so in fact we only check the LASTUSED if there is a default, to try and work out if a BIND can have happened or not - if the BIND reset the value over (let's say) a year ago and there are no rebinds then we can think about freeing the package.

 

Walter, sorry can you explain better your use of the TIMESTAMP field?

 

Aurora

Michael Hannan

RE: Db2 z/OS 11 NFM - SYSPACKAGE LASTUSED and BIND timestamp
(in response to Aurora Emanuela Dellanno)

Aurora,

I am having trouble to understand your points as well.

BINDTIME is a very old column, timestamp of last REBIND (or BIND the first time when Package is created or recreated from BIND again). It should not have been defaulted to anything when you went to version 10. If you converted Plan DBRMs to Packages, then it would be set for a new Package created, perhaps that is what you are getting at, even though this new Package might never have been used, and LASTUSED would be set to 01.01.0001. BINDTIME could be reset to any new timestamp by someone deciding to REBIND a whole lot of Packages for whatever reason, and therefore not a useful value other than to identify the access path rows etc., but it should not affect the LASTUSED. So it (BINDTIME) still means very little in relation to real usage of a Package.

TIMESTAMP is when the Package was first created, which could be just a new VERSION id or a new CONTOKEN, or new Package at V10 converted from a Plan DBRM. So more useful than the BINDTIME, as I see it.

For any Packages with LASTUSED 01.01.0001, you can't really tell if they will ever be used. You could check to see if a previous version (prev CONTOKEN) was ever used or used in recent years, this assumes you may have previous  versions still there for some Packages.

My point was it is much safer to free up previous versions, if they have not been used for years, because you have a newer version that might be in use. If newer version has not been used, then might not be safe to free up an older version used in the last year or so.

Trying to decide whether current versions of Packages will never be used, is an very difficult, if not quite impossible task. Therefore I just don't want to do it at all. Wasting my time. If the underlying objects are gone, so Package cannot be used, O.K. that is a useless Package. Some sites may have lost the application programs that a Package was created from. LOL.

I did an experiment once. Collected all Package LASTUSED for 10,000 Packages maybe more. 1 month later collected all LASTUSED again. I did find some old Packages had been used the 2nd time that had never been used before, or had not been used for many years beforehand. This proves the danger of removing them! You just never know what very obscure functions will be used (at last) for the first time for that Package Version. The experiment was done back at V10, since that was when some people were pushing for a big Package clean-up (even IBM suggested it). We later abandoned the clean-up idea as being non productive effort. We just accepted that the DB2 Catalog got bigger for Extended Package Management.

In the modern day with Extended Package Management, the experiment might be easier. Suppose you find Packages in SYSPACKCOPY where LASTUSED is '01.01.0001' (last used as a Package before a REBIND caused it to be a Package Copy) but same Package key in SYSPACKAGE shows a usage in the last few months. That tells us that a package may have never been known to be used for a long time, then at last has been used. Could happen to any Package. Have we got a crystal ball to predict which package?

We have useless Packages in there? Who cares! If they don't get used for a long time, then just ignore them! That is my opinion. REBIND them only if forced to do so. Any Mass REBIND could generally skip them perhaps, unless REBIND is mandatory for some reason.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 31, 2018 - 10:19 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 31, 2018 - 10:23 AM (Europe/Berlin)