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”.
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?
Good example of how to approach a problem from the data side.
As far as the tools go, tx. TAANA does exactly that, in background and on large tables. You can even create custom dynamic fields such as the year/month parts of dates.
Never heard of transaction TAANA but will try it out tomorrow when I'm in the office again (unless missing auths prevent me from doing that!).
I tried TAANA today and I'm sure that it'll come in handy sooner rather than later! The Tcode is already used regularly by our basis team in connection with archiving but we can use it as well.
It would however not have helped in our particular case as the blank entries cannot be distinguished from the ones with initial values:
Interesting, Table Analysis does obviously distinguish 30 'special' records, but conversion exits make it look the same.
You could also download the results, I often pull them into Excel for further slicing and dicing, wonder if the difference will show up there?
Thanks for the feedback.
yes, it must be conversion exits or something similar. This is also happening in SE16 where the entries with spaces are also only distinguishable from those with initial values (all zeroes) when the user-settings are switched to the old SE16 Standard list.
There are two native SQL editors, one in transaction code ST04, and one in program RSDU_EXEC_SQL.
There is the ABAP SQL editor in Eclipse.
There is also a database scanner for searching specific values (hex or char), but it scans all columns, in transaction I18N (or directly the program RSI18N_SEARCH). It can also replace values of matching entries.
I am using
And there are also SQL editor tools like this one.