Parallel Background Job

[Former Member]

Dear all,

I have a problem in parallel background job.

  The background job is to update one database table with different variant for each background job.

  Firstly the program delete database content for that variant , and then insert the new content based on that variant.

At the end of program I used syntax commit work and wait.

But unfortunately all the parallel program hung up  .

  If I check the SM66 all program hung up in the syntax insert table and never end.

And it only happen in the production environment , in QA and DEV environment its run smoothly.

Regards,

Ikrar

[FredericGirod]

Hi Ikrar,

did you try to remove the "AND WAIT" ?

regards

Fred

[RaymondGiuseppi]

• Are the variant defining exclusive subsets of the database, and no overlapping between two jobs, are there message in system log or on database server (deadlocks) did you use a lock/enqueue tool to prevent such behavior ?
• The AND WAIT serves no purpose as long as you did not call any update task FM (read the documentation of COMMIT WORK via F1 or at help.sap.com)

Regards,

Raymond

[FredericGirod]

Hi Raymond,

I didn't understand your comment on the AND WAIT ?

for you AND WAIT have no impact if you are not un a update task FM ?

regards

fred

[RaymondGiuseppi]

The AND WAIT option of the COMMIT WORK statement, system will wait until all Update V1 task are ended, here none were called.

However, if the addition AND WAIT is specified, program processing after COMMIT WORK will not continue until the update work process has executed the high-priority update function modules (synchronous updating).

Execution a COMMIT WORK after only OPEN-SQL statements will only trigger a database commit like FM DB_COMMIT or a native SQL COMMIT statement whatever options are used.

Try to create a sample that update a million records via OPEN-SQL, test with a COMMIT WORK AND WAIT and a simple COMMIT WORK.

Depending on OS database, a native sql statement can look like EXECUTE IMMEDIATE COMMIT but not in OPEN-SQL, or use a Native SQL syntax.

Also there can be delay due to communication between application server and database server, and with buffering of data...

Sample for Oracle could be COMMIT WORK WRITE IMMEDIATE WAIT ?

Regards,

Raymond

[FredericGirod]

Yes, I think I'm agree with you.

But, if you run 3 programs that update the same table (not the same entries), if you use the COMMIT WORK, SAP will wait the end of the 3 programs ?  no ?

regards

Fred

[RaymondGiuseppi]

There could be lock between the job, on undo space and shared areas ?. 

That can raise in Oracle for example some ORA-0060 during insertions of different records) but an OS like DB400 never raise me this kind of error, so depend on OS database ?

In every case, the lock/wait may arise in DB commit task even if no WAIT in statement, a solution in Oracle could be a

  EXEC SQL.
    COMMIT WORK WRITE BATCH NOWAIT
  ENDEXEC.

Regards,

Raymond

[Former Member]

I have check in the SM12 and I don't find  any locking.
Do you think because I add the Wait statement and the background job became synchronous

task ?

and the hung up situation because the synchronous task.

Regards,

Ikrar

[RaymondGiuseppi]

You wont find anything in SM12 as you did not manage lock at SAP level, your problem is caused by lock/wait or even deadlock at database level. Use Database tool to find actual error.

You could

• Use SAP lock to prevent insertion of overlapping keys range
• Use an update task FM (CALL Z_XXX IN UPDATE TASK) to executer the OPEN-SQL statements i  update task, so main job wont wait, and system will manage some queue in update

Regards,

Raymond