DB2 - L

 View Only
  • 1.  Rebinds on shadow deployed renamed objects

    Posted 3 days ago
    Edited by Nick CIANCI 3 days ago

    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.


    Nick CIANCI
    Senior Database Administrator (DB2 for z/OS)
    Data Management DBA team

  • 2.  RE: Rebinds on shadow deployed renamed objects

    Posted 3 days ago

    Hi Nick,

    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


  • 3.  RE: Rebinds on shadow deployed renamed objects

    Posted 3 days ago
    I think plan management is the cause. These days every package has three copies (At least!) The original, the last and the current running. So in your case you invalidate one or two of the copies. Easiest way out is free all the copies before the change. In all of the years that plan management has existed I have used it three times. The chance that you need these old packages, especially with a ddl change, is zero!
     I mentioned „at least" because with the new (re-)bind phase-in you can get 14 copies now...

    Roy Boxwell 

    -Product Development-

    IBM Champion 2023

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Web  http://www.seg.de

    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich