SMP Cockpit Application Deletion Failure

[ed_miller]

Experts,

I have a scenario where I have two Windows servers running the SMP 3.0 SP08 PL03 environment that are clustered together (with an Oracle DB behind the scenes).  I have a single Agentry application defined in the Cockpit.  I had two versions of the Agentry application published (e.g. 5-2-15 and 5-2-16).  When logged into the Cockpit from either SMP server, looking the Applications and specifically the Application Specific Settings, I see the two versions published (i.e. I see the same information from either server's Cockpit).  I selected the older version (5-2-15) and deleted it.  I waited for it to finish and verified that in both Cockpits, there is now only the 5-2-16 version showing.  At this point I even stopped/started the SMP services on both servers so I know I'm starting fresh.

Here is the issue. I want to now delete the entire application configuration from the Cockpit.  From the Application list, I select the application row, press "Delete", and confirm I want to delete the application.  This process will appear to be running, and running, and running, forever; but the application never gets deleted.  In fact the Cockpit will eventually come up to it's timeout, in which case I press the button to continue to stay connected.

A little more information.  This is in a production environment.  I have the exact same SMP setup in DEV and QA other than they are not clustered.  I was able to delete the older 5-2-15 version, then delete the entire Agentry application definition.

So my questions are these:

*  Is there any known bug/issue in the SMP Cockpit that keeps me from deleting an application in a clustered environment?  I could not find anything.  Clearly I can delete it from a non-clustered environment (see my notes above about DEV and QA).

*  Is there a manual work-around that I can do to delete the application from the DB so I can import a new application?

Thank you in advance for any assistance.

  Ed

[mark_pe · Accepted solution]

Ed and team,

Hi.

1. Can you try a newer SMP 3.0 Server Runtime server to see if you can duplicate?
2. Can you try to see if you turn off one of the node to delete individually to see if ?

Let us know your findings.

Regards,

Mark Pe
SAP Platinum Support Engineer

[ed_miller · Accepted solution]

Mark,

I am not able to try a newer SMP runtime at this time as it's a production environment.

However, I did try to show down one of the servers and attempt to delete the application configuration from the other server that remained up and running.  The same results to where it basically ran forever but did not delete anything.

  Ed

[mark_pe · Accepted solution]

Ed,

Try to review this table in terms of timestamp, data and others.

SMP_CLUSTER_PROP_VALUE

This table holds a few of the property value. I want to see if it is just too big in number of rows. Normally as a support technique we use some queries and identify to delete items in it that may be contributing to the # of records slowing down the system.

For this query, just let me know how many number of records you have here.  If it is in the millions then that might be the culprit.

Example delete script (But this has to be modified to only delete X amount of rows pertaining to what is really old):

~~~~~example~~~~~~

1. The SQL on ASE database is:
   DELETE FROM SMP_CLUSTER_PROP_VALUE where CREATED_MS not in (select CREATED_MS from  (select PROP_ID, max(CREATED_MS) as CREATED_MS, max(VALUE_ID) as VALUE_ID from SMP_CLUSTER_PROP_VALUE  group by PROP_ID) a )
2. For the other DBMS, you need to find the similar SQL to execute.
3. The user will need to determine up to what date needs to be deleted and to make sure no one is transmitting while the deletion is happening.

~~~~~~end~~~~~~~~

I just want to see if you have millions of records or a ton of records that could be contributing why it is not getting deleted.

Best Regards,

Mark Pe
SAP Platinum Support Engineer

[ed_miller · Accepted solution]

Mark,

There are 3,902,023 records in the SMP_CLUSTER_PROP_VALUE table.

  Ed

[ed_miller · Accepted solution]

Mark,

In addition, the oldest date (based on LAST_MODIFIED) record has a date of 2014-07-29.  The newest record has a date of "today", 2016-03-08.

What are these records holding, and can I just use the "LAST_MODIFIED" field to delete records that are more than "x" days old (e.g. where "x" is 30)?

  Ed

[mark_pe · Accepted solution]

Ed,

Normally for slow times in SMP 3.0 and issues on slow transmit, we modify the query above (or based on your backend DB) to remove old data tied to X days old.  It helps in the performance of the system (from transmit is slow to the system is getting disconnected during transmit - Error 11). I am speculating that you have millions of records which you confirmed above. This can be the cause of the issue.

I believed in newer SMP 3.0 SP09 or later, we fixed the code or server to do this automatically. Due to you are using older SMP 3.0 SP08, you may be still seeing it.  But this technique can still be used for optimizing the system.

Anything you do in SMP 3.0 will get log here including ID of transactions (So if you know that you are not using data back in 2014 then that may be a good place to start cleaning).  Try to avoid dates tied to a published of Agentry apps (So if you published today) exclude those dates on your cleaning up.

Best Regards,

Mark Pe
SAP Platinum Support Engineer

[ed_miller · Accepted solution]

I have tried the above SQL statement (with the addition of LAST_MODIFIED < '2015-08-01').  That statement succeeded, however when I now go to the "CLUSTER" tab in the Cockpit, it hangs there forever.  And when I go to the "Users" tab and click on Agentry user type, it errors.  And the service will now not stop, or start up (ends up repeating the message "86 of 126 tracked bundled have initialized.").

Is there other cleanup that needs to occur?

  Ed

[mark_pe · Accepted solution]

Ed,

Hi. I am wondering if the dates chosen - older than August 2015 had some important setup that needs to be accommodated.  As I said try to avoid dates that has publish (or really any setup). If you reset the cluster by removing and setting it up again, would it work? At least this time you have not a lot of rows.  That technique above is what we give to our consultants to go onsite and they tweak it based on what needs to be removed. As part of that, the suggestion is to avoid dates where they remember they did some editing (like publishing or setting up or configuring the cockpit - i.e. not to remove rows where X is between dates that they remember they did some edits on the cockpit).  For the most part at least 2~3 production companies setup worked for them and it improve their performance.  Note: This step is normally not needed if you have at least SMP 3.0 SP09 or higher (I can't remember the PL for SP09 that has the fix so if you take the latest PL you should be okay).

Regards,

Mark Pe
SAP Platinum Support Engineer

[ed_miller · Accepted solution]

Mark,

How would I remove one of the servers from the cluster if I can't start up the SMP service?  Is there a table (or set of table) information in the DB that I could update?

And/Or is there some sort of data integrity query that can be run against the DB?

Lastly, if worse comes to worse, is there a set of queries that can "clean up" the entire DB to start fresh?

Thanks,

  Ed

[mark_pe · Accepted solution]

Ed,

I am not aware of a clean up script from scratch yet outside a remove SMP 3.0 runtime program and a reinstall. Did you back up the DB before implementing the delete?  This way you can just revert back and figure out (or remember) what dates should be removed (when was the cluster created) and so forth.

Regards,

Mark

[ed_miller · Accepted solution]

Not even restoration worked.  Ended up re-installing everything (both SMP and a new DB) from scratch.