Playing detective – How counting records helped to identify the culprit

This is the time of year which lends itself to a bit of story telling – so here is a little detective story!

The crime scene

A while ago we noticed an issue in AIF where entries couldn’t be processed properly and the error was of such a nature that it wasn’t even logged. The affected records were just not processed. After some digging, a colleague was able to determine that this was due to a date-field which was actually empty (instead of initial). When the data was supposed to be put into XML-format for further processing this didn’t work due to the unexpected content and format of the date-field.

So, we knew why the processing stopped cold.

What we didn’t know was what actually caused the date-field to be badly formatted in some cases.

Trying to find the actual culprit

There was no easy means to trace back what the common denominator of the affected entries was (or if there even was one!), we only knew that it was somehow related to sales-orders and that the date-field in question was a custom field residing in the append structure of VBAK. Some entries were known to have the issue while many others didn’t. And many which did have spaces in the date-field were not relevant for the AIF-processing so were basically “flying under the radar”.

Gathering evidence

I then remembered that we had a little program which might – if we were lucky! – help to at least narrow down where the affected entries were coming from. The program doesn’t do much, it just counts how many entries exist in a table for a combination of two fields. If the table contains a field named “ERDAT”, the selection can also be restricted by a date-range (yes, this could be improved to allow other fieldnames!). So, it’s ideally suited to look for the “odd man out”, namely combinations where the counts are “through the roof” or look suspicious for other reasons for a combination of two table-fields. Here is how the selection might look like:

You just get back a simple ALV-display (this is from DEV, so not a meaninful sample):

But, doing this in the affected production system(s) and for different fields in combination with the field of interest helped us to narrow down the particular incoming process and interface relying on some BAPI-processing where – as it turned out upon further digging – the way the BAPI-extension structure was filled was the underlying root-cause of the issue.

Not more than circumstantial evidence (but still helpful!)

And to state the obvious: correlation doesn’t mean causation – so, this trick cannot do more than point in the hopefully right direction by at least identifying the most likely haystack in which to look for the proverbial needle. The neat thing is, that the program can be let lose even on very large tables like CDHDR and CDPOS if – and it’s a big IF! – you are on a HANA-DB. Before we had moved to HANA-DB, the likelihood of getting a TIME_OUT for large tables was very high. With HANA, the program churns out the numbers in (almost) no time.

Some technical details

• Fields for the COUNT-statement must be character-based (CHAR, NUMC, DATS or CUKY)
• Our program just takes two fields into consideration
• Datatype is checked against table DD03L

Code to build the dynamic SELECT-statement based on the selection-screen input:

  APPEND: p_feld1 TO t_felder,
          p_feld2 TO t_felder.

  MOVE t_felder[] TO t_group[].
  APPEND 'count(*)' TO t_felder.

  IF NOT p_erd IS INITIAL.
    MOVE 'erdat in s_erd' TO cond.
  ELSE.
    CLEAR cond.
  ENDIF.

Code for the COUNT-statement and to fill the output-table:

  SELECT (t_felder)
    FROM (p_tab)
    INTO (<fs_feld1>, <fs_feld2>, anzahl)
   WHERE (cond)
   GROUP BY (t_group).

    gs_out-field1 = <fs_feld1>.
    gs_out-field2 = <fs_feld2>.
    write anzahl TO gs_out-count LEFT-JUSTIFIED.        

    ADD anzahl TO gv_cnt_out.

    APPEND gs_out TO gt_out.
    CLEAR  gs_out.

  ENDSELECT.

Do you have any comparable little tool-programs which can help in cases like this?

Cheers

Bärbel