Database error 1205 at DEL access to table /SAPAPO/AM

[former_member215376]

Hi Folks,

Good day and happy new year!

Would like to get your insights and knowledge regarding issue in SNP heuristics (background job). We always got this error log 'Parallel process cancelled with error: SQL error in the database when accessing a table.'

Upon checking with the error (via ST22 and SM21), I've traced that this issue was because of the deadlocks due to 'Database error 1205 at DEL access to table /SAPAPO/AM'.

Checkng our alert database table entries, we have millions of entries there (even in the past years) and we decided to delete them (i.e. all 2013 backwards) via report /SAPAPO/AMON_REORG leaving us with 100k+ entries only in the table /SAPAPO/AM_ALERT. Not sure though if there are other alert table/s (apart from /SAPAPO/AM_ALERT) that we should check and delete the entries, can you please confirm?

When we tested and run again the SNP heuristics (background job), the issue still exists. I tried to zero out the entries in the table and re-run again Heuristics but still nothing has changed.

Basically, I'd like to get your insights if ever you can share some SAP notes or you have encountered the same in the past. Thanks for your continuous help. Appreciate your feedback the soonest!

Regards,

AA

[former_member187488 · Accepted solution]

Hello, I think this is because you have assigned the alert macros to 'Default' group for the used planning book/data view, which is not desired. Please remove the alert macros from 'Default' group. Generally alert macros are executed manually in interactive planning or run in background job.

[former_member215376 · Accepted solution]

Hi Ada Lv,

I found 3 alert macros maintained as 'Default' group in the Planning Book/Data View concerned. I already removed them and now running SNP in the background in Development system.

Will let you know the results. Thanks!

Regards,

AA

[former_member215376 · Accepted solution]

Hi Ada Lv,

Just to give you an initial update on this. Initial SNP Background job ran only at around 437 secs. Now, upon applying your recommendation, it still running (now at 1065 secs). Will this cause a performance issue upon doing the same in Production/Live system? Why a sudden change on the job duration.

I've checked again the /SAPAPO/AM_ALERT and found out new entries there. I think this is one of the reasons? Please confirm. Thank you.

Regards,

AA

[former_member187488 · Accepted solution]

Hi, removing the macros from default group will improve the performance -- actually running alert macros during heuristic is useless ... if you have no alert macro in the start group and default group, the heurstic run will not touch /sapapo/am_alert at all. Please check whether you have other jobs running in parallel.

You can start a ST12 trace with "trace workprocesses" to trace your herustic run when it is running. The trace will tell you what is the bottleneck process during background job run.

[former_member215376 · Accepted solution]

HI Ada Lv,

Thank you! Now, it's working fine (although some errors like Product /location : Maximum number of orders per period reached (000000100)). No error has been seen for 'Parallel process cancelled with error: SQL error in the database when accessing a table.'

Many thanks!

Regards,

AA

[former_member187488 · Accepted solution]

Hi AA, glad to know that, thanks for the feedback

I think you know the reason of the maximum number of orders issue

[former_member215376 · Accepted solution]

Yes Ada Lv, it's a known issue to us.

Anyway, would you mind sharing some best practices documents about alerts? I'm seeing lots in the internet but not sure if all of them are okay.

Thanks,

AA

[former_member187488 · Accepted solution]

Hi, since I'm working on specific problems much more than overall consulting of the whole system, it's not easy to provide you best practice here. But below notes are always the ones we should check first:

852508 - Performance: second index to access data on table /SAPAPO/ALERT_HP (for planning Heuristic Alerts)

830673 - AMON: Some consulting issues when handling alert monitor

781319 - Creating a user-defined alert type

521639 - Generation of DB Alerts in Background

510189 - SDP: Definition of user-defined texts for the display

500051 - Alert monitor overview (AMO) alert types

495166 - Tips and Tricks for Handling Alert Monitor

[former_member215376 · Accepted solution]

Thanks Ada Lv,

Last question for this issue, it seems that previous setup (i.e. alert macros were not really in Default group in the past) was overwritten based on the advm workbench (last changed field). However, I want to know exactly what changes that this user did. Are there any change log documents for macro like the prod_cd_read and loc_cd_read available in APO? Thanks!

Regards,

AA

[former_member182607 · Accepted solution]

Hi AA,

You can keep track of Macro Changes by enabling Version Managment in the macro book. This can be done in the macro workbench by going to Edit->Book Information tab.

Subash

[former_member215376 · Accepted solution]

Thanks Subash,

That was very helpful. I already saw and traced the changes based on this version.

Regards,

AA