2012-07-21

Aftermath of vacation

During   my vacation , our Business intelligence system was hit by two severe crashes, the first one   currency rate load failure  I have blogged about, but we also had failed to load project costs from SAP. Two days before my vacation I enhanced the   job iterator  functionality in my job scheduling system.  While trying to debug the currency rate problem on the German Autobahn on my way to France, I got a call from the office telling me there is also a problem with project costs. From the info I got I guessed it must have something to do with job iterators, the costs where to high 17 times to be exact. And the job that failed had a job iterator with 17 rows. I could not for my life figure out what the problem was and I had done extensive testing of my changes, but  if you finds bugs look at the last changes and 17 was also pointing  at my job iterator, so still on the road I backed out my changes, but the cost problem remained.
Back at the office we found out  the problem had started before I made the changes, it was another job that was the culprit and not all projects were affected. Since all updates are done with SQL we suspected unwanted duplicate rows in some involved table. During my absence two colleagues had analyzed the problem and they had isolated the problem to SAP table EKPO (purchasing document items). So we checked our ‘copy’ of the EKPO table and found no indexes. EBELN (Purchasing Document) and EBELP (PD item) should be primary index in our EKPO table. By the query select count(*),EBELN, EBELP from EKPO group by EBELN, EBELP having count(*) > 1 , we found SEVENTEEN  invalid rows with blank EBELN, EBELP (these rows were inserted into SAP a few days before my vacation). We removed the offending lines, inserted the primary index and reloaded the costs, now right again to the last cent.
This crash could have been avoided if we have had proper input checks and correct indexes on the database table but we do make mistakes, ‘those who do not do mistakes tend not to do anything at all’. And as always, shit happens at the worst possible time. Murphy rules.
   
One interesting observation, the execution time for the failing SQL increased from 2 minutes to over 8 hours with these 17 bad EKPO rows! I end this post with the failing SQL.
 
INSERT INTO WBS_TABLEB_temp1  (
                            SELECT
                            'WBS', '1', A.PSPID, A.POST1, B.POSID, C.PERIO,
                            CASE
WHEN LEFT(CONCAT(substring(B.POSID,9),' ','/',B.POST1),2) = ' /' THEN 'Internal Order & Unassigned'
WHEN LEFT(CONCAT(substring(B.POSID,9),' ','/',B.POST1),5) IN ('03-01','03-02') THEN CONCAT(substring(B.POSID,9),' ','/',B.POST1,' ','Lab')
                                   ELSE CONCAT(substring(B.POSID,9),' ','/',B.POST1)
                            END AS SufixName,
                            CAST(I.COST_ELEMENT AS SIGNED),
                            COALESCE(I.DESCR_LONG, C.KSTAR, 'NO NUMBER EXISTS') AS AccountDescription,
                            C.WOGBTR,  C.MATNR,
                            COALESCE(E.MAKTX, C.MATNR, 'NO NUMBER EXISTS') AS MATDESCR,
                            C.EBELN,
                            COALESCE(F.TXZ01, C.EBELN, 'NO NUMBER EXISTS') AS PurchOrderText,
                            COALESCE(H.SNAME, C.PERNR, 'NO NUMBER EXISTS') AS Person,
                            C.PERNR, C.MBGBTR, C.MEINH, A.PRCTR, C.FKBER,
                            CONCAT(RIGHT(CAST(C.GJAHR AS CHAR),2),LPAD(CAST(C.Perio AS CHAR), 2, '0')) AS Period,
                            C.BUKRS, B.PSPNR, C.GJAHR, B.FKSTL, C.REFBN, C.BUDAT, A.VERNA, A.ASTNA, A.PROFL,
                            COALESCE(L.TypeDescr, L.TypeNr, 'NO NUMBER EXISTS') AS ProfileDescr,
                            B.USR00, C.PAROB, C.PAROB1, C.USPOB, I.CON_AREA,
                            I.SETNAME AS SetName, I.SUBSETNAME as SubSetName,
                            I.SUBSETDESCR as AccountGroup,I.DESCR_LONG AS AccountConcat,
                            C.PAROB, C.CPUDT, C.BLDAT, C.BLTXT, C.BELNR, C.BLART, C.SGTXT,C.GKONT, C.USNAM,
                            C.WSDAT,C.PFKBER, C.PSCOPE,C.WERKS,C.BUDAT, C.AWTYP,C.AWREF_REV,
                            C.CPUTM,M.CRI,M.DOC_ID, H.KOSTL,
                            CASE   WHEN C.REFBN IS NULL OR M.CRI IS NULL OR M.REF_DOC_NR IS NULL
                                   THEN 'No' ELSE 'Yes'
                            END AS PDF_Document
                            FROM ACTAPRJ.PROJ A
                            RIGHT JOIN ACTAPRJ.PRPS B
                            ON A.PSPNR = B.PSPHI
                            LEFT JOIN ACTAPRJ.COVP C
                            ON B.OBJNR = C.OBJNR
                            LEFT JOIN ACTAPRJ.CSKU D
                            ON C.KSTAR = D.KSTAR
                            AND D.SPRAS ='E'
                            LEFT JOIN ACTAPRJ.MAKT E
                                ON C.MATNR = E.MATNR
                            LEFT JOIN ACTAPRJ.EKPO F
                            ON C.EBELN = F.EBELN
                            AND C.EBELP = F.EBELP
                            LEFT JOIN ACTAPRJ.PA0001 H
                            ON C.PERNR = H.PERNR
                            AND H.ENDDA > NOW()
                            LEFT JOIN ACTAPRJ.DB3FTM_SET_STRUCTURE_PS I
                            ON C.KSTAR = I.COST_ELEMENT
                            LEFT JOIN ACTAPRJ.PROJECT_TYPE L
                            ON RIGHT(A.PROFL,2) = L.ID
                            LEFT JOIN ACTADW.DB3FTM_EXTERNAL M
                            ON C.REFBN = M.REF_DOC_NR
                            AND M.REF_DOC_NR <> ''
                            WHERE C.KSTAR NOT IN ('9999210000','9999210003','9999210004','0009004902')
                            )
                            UNION ALL
                            ( SELECT
                            'IO', '1', A.PSPID, A.POST1,
                            COALESCE(RIGHT(E.AUFNR,10), E.PSPEL, 'NO NUMBER EXISTS') AS OBJECT,
                            C.PERIO,
                            CASE
WHEN LEFT(CONCAT(substring(B.POSID,9),' ','/',B.POST1),2) = ' /' THEN 'Internal Order & Unassigned'
                                           ELSE CONCAT(substring(B.POSID,9),' ','/',B.POST1)
                            END AS SufixName,
                            CAST(I.COST_ELEMENT AS SIGNED),
                            COALESCE(I.DESCR_LONG, C.KSTAR, 'NO NUMBER EXISTS') AS AccountDescription,
                            C.WOGBTR, C.MATNR,
                            COALESCE(K.MAKTX, C.MATNR, 'NO NUMBER EXISTS') AS MATDESCR,
                            C.EBELN,
                            COALESCE(F.TXZ01, C.EBELN, 'NO NUMBER EXISTS') AS PurchOrderText,
                            COALESCE(H.SNAME, C.PERNR, 'NO NUMBER EXISTS') AS Person,
                            C.PERNR, C.MBGBTR,C.MEINH, A.PRCTR, C.FKBER,
                            CONCAT(RIGHT(CAST(C.GJAHR AS CHAR),2),LPAD(CAST(C.Perio AS CHAR), 2, '0')) AS Period,
                            C.BUKRS,B.PSPNR,C.GJAHR,B.FKSTL,C.REFBN,C.BUDAT, A.VERNA, A.ASTNA, A.PROFL,
                            COALESCE(L.TypeDescr, L.TypeNr, 'NO NUMBER EXISTS') AS ProfileDescr,
                            B.USR00,C.PAROB,C.PAROB1,C.USPOB, I.CON_AREA,
                            I.SETNAME AS SetName,I.SUBSETNAME as SubSetName,
                            I.SUBSETDESCR as AccountGroup,I.DESCR_LONG AS AccountConcat,
                            C.PAROB, C.CPUDT,C.BLDAT,C.BLTXT,C.BELNR, C.BLART, C.SGTXT, C.GKONT,
                            C.USNAM, C.WSDAT,C.PFKBER, C.PSCOPE,C.WERKS,C.BUDAT, C.AWTYP,
                            C.AWREF_REV, C.CPUTM, M.CRI,M.DOC_ID,H.KOSTL,
                            CASE
                                     WHEN C.REFBN IS NULL OR M.CRI IS NULL OR M.REF_DOC_NR IS NULL
                                     THEN 'No' ELSE 'Yes'
                            END AS PDF_Document
                            FROM ACTAPRJ.PROJ A
                            RIGHT JOIN ACTAPRJ.PRPS B
                            ON A.PSPNR = B.PSPHI
                            RIGHT JOIN ACTAPRJ.AUFK E
                            ON B.PSPNR = E.PSPEL
                            RIGHT JOIN ACTAPRJ.COVP C
                            ON E.OBJNR = C.OBJNR
                            LEFT JOIN ACTAPRJ.CSKU D
                            ON C.KSTAR = D.KSTAR
                            AND D.SPRAS ='E'
                            LEFT JOIN ACTAPRJ.MAKT K
                            ON C.MATNR = K.MATNR
                            LEFT JOIN ACTAPRJ.EKPO F
                            ON C.EBELN = F.EBELN
                            AND C.EBELP = F.EBELP
                            LEFT JOIN ACTAPRJ.PA0001 H
                            ON C.PERNR = H.PERNR
                            AND H.ENDDA > NOW()
                            LEFT JOIN ACTAPRJ.DB3FTM_SET_STRUCTURE_PS I
                            ON C.KSTAR = I.COST_ELEMENT
                            LEFT JOIN ACTAPRJ.PROJECT_TYPE L
                            ON RIGHT(A.PROFL,2) = L.ID
                            LEFT JOIN ACTADW.DB3FTM_EXTERNAL M
                            ON C.REFBN = M.REF_DOC_NR
                            AND M.REF_DOC_NR <> ''
                            WHERE C.KSTAR NOT IN ('9999210000','9999210003','9999210004','0009004902') AND
.PSPID != ' ‘ );

No comments:

Post a Comment