Question:
HI, First time caller long time listener (ftcltl). I am wondering if anyone has run across this problem:
I am running a program in R/3 in background mode and scheduling it to run periodically (weekly). The program first deletes then fills a temporary table with over 8 million records to date. It takes slightly over an hour to complete when scheduled early morning Sundays.
This will work fine the first time it is scheduled but usually on the next weekly periodic run the job runs for 3 seconds and reports a completed status. The table is emptied but that is it.
I can schedule it again to run once and it will work, but if I schedule it periodically it will not finish the job after the first run.
I'm thinking maybe the deletion process of the large table is the problem. I tried using a direct Oracle sql truncate statement. This helps speed things up a bit but the same issue is occurring. Any ideas on how to solve or questions?
Thanks.
Answer:
Hi TacoBro,
You must be aware that managing such amount of data requires some techniques in the way of programming your database operations.
What I mean is that (as you may already know) database operations are first stored into the rollback segment before they are actually applied to the database after a COMMIT operation.
So if you do a large amount of database operations at once the rollback segment might overflow.
So what you need to do is to COMMIT your database operations regularly (like every 10.000) in order to empty the rollback segment and avoid the overflow problem:
--> For deleting your table entirely, use function module DD_DB_OPERATION. Try to find an example for the deletion to make sure you use the right parameter values.
--> For deleting only specific records, use the following code:
CONSTANTS treshold TYPE i VALUE 10000.
DATA rest TYPE i.
DATA itab LIKE <your table> OCCURS 0 WITH HEADER LINE.
DO.
SELECT * FROM <your table> WHERE <your condition>.
DELETE <your table>.
rest = sy-dbcnt MOD treshold.
IF rest IS INITIAL.
EXIT.
ENDIF.
ENDSELECT.
IF sy-subrc NE 0.
EXIT.
ELSE.
COMMIT WORK AND WAIT.
ENDIF.
ENDDO.
I'd suggest you do something similar for inserting your new records, just to make sure it doesn't stop right in the middle of the process.
I've already used this technique to handle tables with more than 3 million entries and it works nice.
Hope this will help you...
Answer:
Thanks Chinka! I will try your suggestions and report back.
This custom extract program was written for BW and I inherited it. The long term plan is to restructure it to do only delta loads or incremental since last load and archive the older data as opposed to reloading all historical items weekly as it is doing now.
This doesn't have the priority to do a rewrite now though so I'm trying to limp it along. We'll see how long that lasts, it's up over 9 million records this week. Woo hoo!
Answer:
I took a look at how DD_DB_OPERATION is used and it seems it would serve the same purpose as a direct Oracle sql truncate statement in that it would delete and recreate the table in order to avoid logging and rollback overhead.
This is the statement I am currently using to replace the given delete function in said program:
exec sql.
Truncate table <my table>
endexec.
Also, the insert process is already doing so in chunks of 10,000 for each commit which has been working fine. Maybe the problem exists in waiting for the sql statement to finish.
Answer:
So your problem's still not solved ?
If that's the case I'd suggest you put WRITE statements at strategic points in the source code (like just before the part that fills in the table) to see where it stops suddenly. But maybe you already did...
Answer:
To udate it's May and no sir, the problem is still not solved. I am just about to implement the delta process to overcome the large loads but the issue of background periodic scheduling is still a question.
The process is now up to loading over 12 million records at one time on a weekly basis. This has not failed to complete yet if I schedule the background job to run once each week. That means each Friday I schedule the job to run Sunday.
The problem happens when I set it to WEEKLY in PERIODIC mode.
Answer:
TacoBro - I think it's time to post as much of the relevant pieces of code as you can and give us full details of what system it's running on (DB & level, SAP & level, OS & level) as so far it's obvious you've been working hard to solve it, but you're coming up empty.
Hopefully with a bit more info one of us will spot something that will help / ask a stupid question that points you in the right direction...
Fingers crossed for you
_________________
Kind Regards
Rosie Brent
Please remember to search the forum and check the FAQ before posting questions, thank you.
Tuly Idiot most of the time, part-time Guru
Answer:
My guess is that the filling of the table is based on some date or other. When the job is scheduled on a weekly basis, the date of the first run is always used instead of that for each run.
_________________
Champions League, here we come...
Answer:
Yes, that is a correct guess. The program sets and gets the last run date from a user table and uses it for the next date range of the extract. It populates the GUI dialog where the user can reset the range.
That must be where the problem lies. Is there a setting (e.g. system field) to programatically check if PERIODIC WEEKLY is set so it can get the date from the user table directly at run time and not from the dialog?
I obviously haven't been working that hard at it, I'm a BW guy trying to program in ABAP so I don't get a lot of practice. Like I said, I inherited the program. But we are on SAP 4.7 w/patch ug 620 level 41, OS = SunOS sun4u, DB = ORACLE 9.2.0.5.0.
Thanks for your insight.
Answer:
Store the last run date in TVARVC and create a program variant with selection variable checked for the date selection option and set up the selection variable as the relevant TVARVC.
_________________
Search first:
http://help.sap.com/search
http://service.sap.com/notes
/forums/search.php
& then ask a smart question