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