This has been bugging me for a while and normally we just live with it, but as it is still bugging me I thought I'd ask the brains trust in Db2-land as to are other people witnessing similar antics, any how Db2 should and why it behaves this way? OK so the story is when we do structural changes on an existing table, we'll often build || structures and swap them in, as it can save us some deployment time, and means that there is a straightforward back out option should it be required. So in summary this is what we do :
... AND ... so far so good it all works well and we wait a couple of days before doing the tidy ups (Dropping the old Tables). The trouble is that sometimes when we drop the Old Tables / Indices / TableSpaces it invalidates packages and at other times it doesn't . It would be really nice if things were consistent (and preferably not requiring rebinds after the drops) but my experience is that Db2 is not being consistent, I can only assume somethings in Maintenance levels change what is happening under the covers.
I have one such drop that will happen in the next week or 2 and I'm trying to 2nd guess whether Packages will be invalidated or not?Like in other case is I look at SYSPACKDEP what I see is :
... and Kewl ... I'm expecting this ... but also in SYSPACKDEP is :
... ie the old index and TableSpace (nb: not the old table) ... and I'm at a loss as to explain why the "hell" (pardon my french) they are on SYSPACKDEP, and potentially invalidating my packages when I drop the old TableSpaces. Surely when I do the rebinds DB2 is clever enough to know that dependencies CAN CHANGE and resets old dependencies before defining new ones?Before anyone asks about versioning we don't use it ... so just the one package version to worry about. If anyone can shed light on my dilemma I'd be grateful for knowledge.
------------------------------Regards,Nick CIANCISenior Database Administrator (DB2 for z/OS)Data Management DBA team------------------------------
I would guess it has something to do with the fact that the DBD still knows that in the past the table was in the old tablespace.
Try MODIFY RECOVERYing the old tablespace to remove all ICs older than the point in time you renamed the table.
Maybe you also need a rebind to remove the dependency from the package.
I would also do a 2nd check to the ORIGINAL/PREVIOUS copy of the package
Mauro Moschelli Intesa Sanpaolo
Segui Intesa Sanpaolo su:
There are 10 types of people: those who understand binary and those who don't
SOFTWARE ENGINEERING GmbH and SEGUS Inc.-Product Development-
IBM Champion 2023
Vagedesstrasse 1940479 Dusseldorf/GermanyTel. +49 (0)211 96149-675Fax +49 (0)211 96149-32Email: R.Boxwell@seg.deWeb http://www.seg.de
Link zur Datenschutzerklärung
Software Engineering GmbHAmtsgericht Düsseldorf, HRB 37894Geschäftsführung: Gerhard Schubert, Ulf Heinrich
4569 Technology DriveSte 1 Wilmington, NC 28405Phone: (910) 660-8649Fax: (910) 523-5504