Reverse engineering using SQL trace (ST05)
Prerequisites: – Familiar with ST05 and basic debugging
When we talk about transaction ST05, it brings out a lot of concepts related to performance analysis, code optimization, index, explaining queries etc. There are numerous informative blogs/articles/discussions which have discussed this to death, and this is not one of them. ST05 has always been considered a tool for performance improvement. At least I was taught so, till I started using it for reverse engineering.
Very often we come across requirements which have already been accomplished. All we need is to find what was done and mimic/enhance the same. ‘No need to Re-invent the Wheel – just find it and roll with it’ as one of my mentors used to say. Some examples of requirements.
- Find and mimic a 3rd party print subroutine triggered somewhere in a user exit or a repair.
- Propose destination bin like LT01.
- Write a program for translation of se91 messages.
- Finding backend tables for a report.
SCN and colleagues can only be of limited help sometimes and you may have to get down to good old debugging. But where do you start? ‘/h’ and the magical F5, F6, F7,F8? But for how long, one may have to go through 1000’s of lines to get around to the relevant area. IMHO not the best way to go about. Combining SQL trace with debugging helps get to the relevant code quicker. Coupled with a little functional knowledge, a lot quicker. I am sure this is a well-known technique and many of ABAPers with a couple of years under their belt do this on a daily basis. However I did not find any document which explains this technique for our beginner friends, just learning to debug standard programs.
For the benefit of all readers – SQL Trace, (T-code ST05) when activated tracks all database interactions like selects, inserts, updates etc. Let us consider a simple example – A functional consultant tells you that data in a custom pricing table needs to be changed. The table name, user exit used are all required to be found. All you know is that a custom table is updated during order creation and it needs to be found. We can just activate the trace->run the transaction for creating order->and look at all the Z tables listed along with the parameters values that were used in the actual query. This would be the simplest use of ST05 and the functional consultants can use this themselves with little help/training. This technique can also be used to find the SAP backend tables for a particular report.
An example ST05 screenshot (Make sure you use -> ‘Display Detailed Trace list’ option to get the include name.)
Let us look at another example – we need to change the status of the job from released to scheduled programmatically. We know that this can be done in SM37. Let us use debugging combined with ST05 to actually find this. This process should be updating some DB table. Run a trace while doing this in real time. Insert, Update, Modify, Delete statements should be of interest as the process is updating something. Once you locate the tables, get the table description in se11. If you find it relevant then put a break-point where it is getting updated. Run the Released->scheduled again via sm37. This time you would stop at the breakpoint (Unless updates are made in update task – Use Update debugging in this case). We have already reached the point of interest. The next part is looking at the call stack. If your read the names of the FM’s, subroutines or some basic comments of the code then you would be able to make sense of where you are in process of Released->Scheduled. You might even find the FM’s used and how they are populated. This will help you mimic/understand the functionality.
Step 1. Activate trace in ST05.
Step 2. Change job from Released->Scheduled in sm37
Step 3. Deactivate and display trace in ST05
Step 4. Look for statements like Update, Insert and Delete. Description of TBTCS and TBTCO sounds like they are candidates for further debugging.
Step 5. Double click on the Program name to navigate to the actual query and set necessary break points. Run Released->Scheduled again as in step 2.
Observe the call stack/comments as in the screenshot. We can use BP_JOB_MODIFY to change the job status from released to scheduled. We can look at the source code in PERFORM CHANGE_JOB_STATUS_SM37B to look at how this FM is populated. Also please take note other things that may be relevant (Eg. authorization checks).
In certain cases updates are made in a separate update task and cannot be debugged until you activate update debugging. You can also use transaction SAT (Available from 7.02 and higher) for call stack. This would let you know how a particular SQL task was carried out. For more details refer below link. http://scn.sap.com/community/abap/testing-and-troubleshooting/blog/2010/08/23/st12-who-calls-whom-get-the-call-stack-with-bottom-up-analysis
Using ST05 sometimes helps get results much faster when compared with direct debugging. Run an ST05 trace->Scan through the tables->Get a fair idea of what is involved->Set breakpoints->Observe Call stack/comments/sub-routines. Also switch on ‘Table Buffer Trace’ – If you suspect that data is getting read from the buffer.
Hope you find ST05 as useful as I find it to be. Peace.