DB2 - L

Back to discussions
Expand all | Collapse all

Nasty REORG problem - Heads up!

  • 1.  Nasty REORG problem - Heads up!

    Posted yesterday
    Hi All!

    One of my customers has been hit by this and so I thought a general warning was called for! They applied

    PH28092 UI71385 REORG PI PARALLEL BUILD IMPROVEMENT (Closed September)
    REORG PI parallel BUILD improvement for partitioning indexes when running on a table space with a single PI
    The enhancement takes place when all of the following criteria are met:
    - REORG TABLESPACE SHRLEVEL REFERENCE or CHANGE is run on a multi-part range partitioned table space with only one clustering partitioned index defined on the table involved, with no other indexes defined
    - REORG is sorting the data records into clustering order with the default or specified SORTDATA YES option
    - Data unload/reload partition is used on the REORG with message DSNU1160I issued
    - REBALANCE is not specified


    Which is all in all a good idea. After a while they noticed "problems"... Now there is this HIPER APAR

    PH30979 UI72742 DATA CORRUPTION AFTER REORG COMPLETED WITH RC00 WITH PH28092 INSTALLED, RC00C90206 ERQUAL5007, INCORROUT, ABEND0C4 DSNURBXD (closed end of November)

    Multiple issues after PH28092 / UI71385 is applied.
    Reorg utility might end with RC00 but leaving the tablespace in inconsistent state.
    There are index related errors on PI during REORG or after REORG.
    In particular there are rows with the same primary key while non key columns can have different values containing the tablespace rids.
    Applications can get abend04E RC00C90206 ERQUAL5007 in DSNIDIFS, or can even get incorrect outputs depending on the access path chosen .
    Other symptoms include next REORG possibly ending with ABEND04E RC00E4030F RC00C90003 in DSNURLOG or CHECK INDEX and REBUILD INDEX errors like MSGDSNU713I KEYS MISMATCH / MSGDSNU340I ERROR LOADING INDEX DUPLICATE KEY.

    User can check if the Reorg output contains this message:
    DSNU1160I.... PARTITIONS WILL BE UNLOADED/RELOADED IN PARALLEL...

    If the message is there and PH28092/UI71385 is applied then the user is exposed to the issue which can cause the unavailability of the involved data to the applications, as the user needs to fix the bad data.
    In the reported case the client performed the following actions to prevent additional duplicate keys:
    - stop db2 applications / prevent all data access to corrupted tablespace
    - drop unique index
    - correct the data
    - re-create the unique index
    - start db2 application


    Happy New Year - And I hope no-one else finds this...


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

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




  • 2.  RE: Nasty REORG problem - Heads up!

    Posted yesterday

    Is it fair/safe to say if both fixes are deployed simultaneously, there should be NO occurance of this issue?

     

    Chris Hoelscher

    Lead Sys DBA

    IBM Global Technical Services on assignmemt to Humana Inc.

    T 502.476.2538  or 502.407.7266

     




    Original Message


  • 3.  RE: Nasty REORG problem - Heads up!

    Posted yesterday
    Yep! The second HIPER corrects the first - Basically you must be "unlucky" to hit this but it is so nasty that I thought it warranted a quick post!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

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


    Original Message


  • 4.  RE: Nasty REORG problem - Heads up!

    Posted 19 hours ago

    Roy,

    was puzzled by this statement:

    "In particular there are rows with the same primary key while non key columns can have different values containing the tablespace rids."

    Normally there cannot be more than 1 rows with same primary key, are you implying the table ends up with duplicate rows, or something else?  I suspect the use of word "primary" was not quite right.
    Maybe it should mean duplicate index entries are created in the partitioning index?




    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------

    Original Message


  • 5.  RE: Nasty REORG problem - Heads up!

    Posted 14 hours ago
    That is verbatim from IBM and the data corruption along with duplicate index keys were the "nasty problem" I mentioned!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

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


    Original Message


  • 6.  RE: Nasty REORG problem - Heads up!

    Posted 18 hours ago
    Hi Roy ,
            Thanks for the heads-up!

    Is the 1 index only a hard and fast rule to meet the criteria?


    We have some parted tables that have a 2nd index that is a DPSI  (with very similar columns to the PI). Both PI and DPSI use the Parting column as the 1st column in the index definition.


    To date we haven't run across this issue, and I'm checking with our SysProg to see if both APARs have been applied.


    We did have a problem last year with ReOrg Index on an index that was particularly volatile, which blew out the index size and resulted in a massive increase in Index GetPages but that was resolved I think around June 2020.  I don't have the APAR number at hand though


    Regards,

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



    Original Message


  • 7.  RE: Nasty REORG problem - Heads up!

    Posted 16 hours ago

    Nic,

    How's it going?
    I don't quite follow:

    "We have some parted tables that have a 2nd index that is a DPSI  (with very similar columns to the PI). Both PI and DPSI use the Parting column as the 1st column in the index definition."
    If an Index starts with the table partitioning column(s), then it is a partitioning index or PI. DPSIs are partitioned indexes but do not commence with the partitioning column(s), right? Or at least not all of them. That seems to be inconsistent with your DPSI, or maybe your wording is slightly off.

    I doubt  it is possible to force something to be DPSI, when it really is not?  
    Is it more likely that you have more than one "Partitioning Index" which is very possible since Table Partitioning came in.

    DPSI normally cannot provide a rows set in its key sequence without merging rows from each partition used, unless predicates limit query to a single partition scanned.  DPSIs can be useful (rarely) but is not a viable alternative to a normal NPI. Its a special case only thing.
    Was so funny when Table Partitioning came in that people were talking about converting their NPIs to DPSIs. Converting them to be PIs if lead columns matched partitioning, was far more likely. 


    Picky Micky Hannan  (Ha ha)



    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------

    Original Message


  • 8.  RE: Nasty REORG problem - Heads up!

    Posted 15 hours ago
    Hi Mister H,
            Happy New Year Michael !!!

    Yes you are correct, but it was my understanding that all secondary parted indices are "DPSIs".  However I guess what I'm talking about is simply a PSI (Parted Secondary Index),
    {/waffle mode} my IMS background forces me to add the label "Secondary" to something not a primary key index {/waffle off}
    ... as the Tables Partitioning column is the 1st Column in the index.  If the parting column wasn't in the index then I guess it would be data-partitioned ... and hence a DPSI ... so my apols for any confusion


    Specifically in our case we have a parted TableSpace (Table Parted not the old Index Parted) On Column "Create_TS"
    ... with 2 Partitioned Indices that look a little like this :
    1. INDEX1 (Unique for the Primary Key)         = Create_TS , Key_Col2, Key_Col3, Key_Col4, Key_Col5
    2. INDEX2 (Unique)                                 = Create_TS , Key_Col2, Key_Col4, Key_Col3, Key_Col5

    Before we partitioned the Secondary Index it was approaching the then max 1/2 TB for index size. Whilst the second Index almost seems like a waste at time, some critical transaction do make better use of the 2nd index, and until they are rewritten (never ROFL IMHO) the index will be required.
    Because the indices are so similar, and a unique or tight create_TS range is always spec'd in SQL, we didn't need to worry about scanning multiple Index partitions. The other benefit we got from partitioning the secondary index was it cut down tree levels as well.  Our rather excellent Dev-DBA (Bruce) also changed the Page size (I think we went to 16K pages) in the index so we saved even more tree levels


    Regards,

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



    Original Message


  • 9.  RE: Nasty REORG problem - Heads up!

    Posted 14 hours ago
    Yep! If you have any DPSIs or NPSIs then you are "safe" as REORG cannot even attempt to use the parallel feature (which also sometimes corrupts your data and index as an added bonus!)

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

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


    Original Message


  • 10.  RE: Nasty REORG problem - Heads up!

    Posted 10 hours ago
    Nic,

    Only the "waters were 'parted' ". Ha ha. Roy says you are safe if have any DPSIs, or NPSIs, but you don't I guess.  Both are called Partitioning Indexes in the latest jargon.   Neither are NPSIs or DPSIs. Incidentally in Catalog table SYSINDEXES, INDEXTYPE   P is for Partitioning, D is for Data Partitioned, and 2 is for all other type 2 indexes.   Primary is indicated by UNIQUERULE. So both your indexes should have INDEXTYPE= 'P'. 

    So I don't know if your table is safe from the problem or not.  I have not looked at the fine detail yet. 

    I absolutely agree the the word Secondary just adds a great degree of confusion, so to me, an NPSI is still just an NPI. 
    I would prefer to see Secondary used to mean Non-Primary indexes, but its really just a noise word that has become somewhat meaningless.

    Remember when we were told to call it Db2 UDB for z OS, and the UDB added nothing whatsoever. It was not Universal.
    In modern day we have Declared Global Temp Tables, which I call Declared Temp tables, because there is nothing Global about them whatsoever.

    Pity that miss-names proliferate.

    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------

    Original Message