Using MySQL SET to squeeze out material reservations from SAP with RFC_READ_TABLE.

The RESB table in SAP contains order dependent requirements of materials. The key is the reservation number (RSNUM). Over time this table will be very large and do a full table scan for active reservations is not a very good idea. The reservation number is incremented for each new reservation, so if you like to fetch reservations done after reservation 10, you only need to fetch reservations >10.  When a reservation is ‘allocated’ it will never go active again, so if you are looking for active reservation you can safely start from the earliest active reservation.  

I used these facts to set up a performant  extraction routine. We have a MySQL table containing RESB active reservations.

0021006667 is the lowest active reservation so I do not need to inspect lower RSNUM, this is a good start since I can bypass +21 million rows. But still I have more than 2 million (23053466 – 21006667) RSNUM to look at. I do not want to inspect these +2 million rows in one go, I want to chop this up in smaller intervals and process them in parallel. For this I created My SQL statements that extracts min and max RSNUM from the MySQL RESB table and chop this interval into smaller chunks.  And subsequently run all these intervals in parallel against SAP with RFC_READ TABLE, scanning for active reservations and reload the MySQL table with the results.

The first job above ‘getHiLow’ retrieves min and max RSNUM and calculates how many intervals is needed to cover the SAP RESB table from low RSNUM.  The second job ‘createDriver’ creates a result table with low and high RSNUM for each interval. Note! the MySQL limit option  in the last select which stops the cross join from go into an infinite loop. ( These SQL statements are somewhat complex and could be more elegantly expressed in a stored procedure.  But when I created these jobs I did not really understood the problem or  how to solve it. So I started to fiddle around with MySQL SET until I had something that worked well together with RFC_READ_TABLE, and this is the result. )    

The next task is to feed these intervals into RFC_READ_TABLE .                     

The job ‘loadRESB’ takes the result table (driver0) from the createDriver job, run each interval in parallel, extracts requested demands and inserts the result rows into the MySQL RESB table. If you look at the selection (the OPTION array) you see what I mean by active  reservations. ( The only appropriate index I have for this query  is RSNUM. If I had a suitable index this hole exercise would be unnecessary. )

The job takes about 50 minutes to run,(which is too much). If we look at the log we see execution 58869 ended with a bad (0,zero, boolean false) result. This was due to a timeout condition for one of the intervals. (Tasks in SAP have an execution time-limit.) To make this job go faster we can shrink the intervals, run more in parallel and/or analyze the problem and solve it.

Anyway 50 minutes is too long, something should be done to make this go faster. If you have a more performant way to extract these material requirements  please drop me a mail or add a comment.

UPDATE - A closer inspection of the log revealed the typical duration is below nine minutes, which is good enough. These extreme execution times probably shows database locking problems.

The jobs above are executed by my PHP job scheduler  .