DB2 - L

Hi Peoples,

              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 :

1. Create the new Structure (for table = TAB_Table_Nm in TableSpace STAB with Indices XTAB0 and XTAB1)
   • Table NEW_Table_Nm
   • TabSpace = STAB0
   • Indices = XTAB00 and XTAB01
2. Prior to the application outage Unload & then pre-load and static data that is effectively read only (ie: historic partitions of data we can place in Read-Only) 
3. Run Runstats (this gives us reasonable stats real Stats to bind against) to save time in the deploy window
4. On the Outage night ... bring the application down 
5. Unload and reload the remaining data (or all of it if we could not preload anything)
6. Rename TAB_Table_Nm to OLD_Table_Nm and then NEW_Table_Nm to TAB_Table_Nm
7. drop old RI and recreate it against the new-current table  (we force the RI rather than CHECK-DATA to save time and run SQL RI checks in it's place)
8. Update any AP-hints to point to the new indices and then Generate and REBIND the invalidated packages. 

... 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 :

• TAB_Table_Nm    
• STAB0
• XTAB00 and XTAB01

... and Kewl ... I'm expecting this ... but also in SYSPACKDEP is :

• STAB
• XTAB0 and XTAB1

... 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 CIANCI
Senior Database Administrator (DB2 for z/OS)
Data Management DBA team
------------------------------

Hi Peoples,

              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 :

1. Create the new Structure (for table = TAB_Table_Nm in TableSpace STAB with Indices XTAB0 and XTAB1)
   • Table NEW_Table_Nm
   • TabSpace = STAB0
   • Indices = XTAB00 and XTAB01
2. Prior to the application outage Unload & then pre-load and static data that is effectively read only (ie: historic partitions of data we can place in Read-Only) 
3. Run Runstats (this gives us reasonable stats real Stats to bind against) to save time in the deploy window
4. On the Outage night ... bring the application down 
5. Unload and reload the remaining data (or all of it if we could not preload anything)
6. Rename TAB_Table_Nm to OLD_Table_Nm and then NEW_Table_Nm to TAB_Table_Nm
7. drop old RI and recreate it against the new-current table  (we force the RI rather than CHECK-DATA to save time and run SQL RI checks in it's place)
8. Update any AP-hints to point to the new indices and then Generate and REBIND the invalidated packages. 

... 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 :

• TAB_Table_Nm    
• STAB0
• XTAB00 and XTAB01

... and Kewl ... I'm expecting this ... but also in SYSPACKDEP is :

• STAB
• XTAB0 and XTAB1

... 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 CIANCI
Senior Database Administrator (DB2 for z/OS)
Data Management DBA team
------------------------------