Monitoring non-SAP ASE databases with the DBA Cockpit - garbled ATM installation

[Former Member]

I have followed the two documents on integrating ASE systmes into DBA Cockpit:

*** 1593987 - SYB Monitoring non-SAP ASE databases with the DBA Cockpit

*** DBA Cockpit - Automatic Table Maintenance for Sybase ASE

For the ASE systems which have been integrated into DBA cockpit as part of installing SAP product, the installation and configuration of ATM was pretty straightforward.  But:

+ Although the default updstat/reorg profiles are installed and the default maintenance windows are set up and are in the scheduled state, there are zero objects reported to be queued for maintenance

+ When started manually, the queue remains zero, although the ATM history displays that some objects were indeed maintained - based on the default maintenance window triggers defined as part of the installation.

For the ASE systems which have not been integrated into DBA cockpit as part of installing SAP product but are used by standard SAP application (SAP BO - which was meant to run on ASA but does support running on ASE as well), ATM configurations are garbled.

+ Default updstat/reorg profiles are are installed partially (some systems they are, some they aren't - installed only a single database in ASE, leaving others to be added manually, maintenance window is in "not scheduled" state and cannot be started (DBA cockpit gets error message stating that some jobs are missing from the job scheduler).

+ ASE integration into DBA cockpit seems to be partial.

A couple of questions:

a. Is there a procedure to self-test the DBA Cockpit configuration?  A health-check for the spatools installation on an ASE box?  How should we identify missing DBA Cockpit components in target ASE?

b. For the systems that are integrated into DBA Cockpit by default and after turning the ATM feature on, how to identify which objects are set up for the maintenance and when?  Except for the standard windows to see ATM future queue, is there a way to test the maintenance window to see which object it will affect? 

c. For the systems that aren't - are there corrective SAP notes available to fix the garbled DBA Cockpit integration? It looks like pulling the non-default ASE into DBA Cockpit leaves certain installation aspect in an unstable state (some of the components definitely mis-perform - System Load can be displayed for the current time only - no history seems to be collected, dashboard keeps complaining on dirty read from monProcessActivity MDA, IO controller window keeps crashing with ABAP OBJECTS_OBJREF_NOT_ASSIGNED error, SQL Statements complains on yet another dirty read errors from monCachedStatements MDA, &c).

d. Is there an extensive documentation on integrating an ASE into DBA Cockpit - other than the 1593987 SAP note?

e. Is there an extensive documentation on configuring ATM other than 2011 document specified in the beginning of this post?

f. What kind of support is available for fixing DBA Cockpit / ATM issues?

Appreciate your help.

Thank you,

Andrew

[Former Member]

Ok. 

So in order to have ATM functional for ASE's which are not installed by SAP installation routines you have:

1.  Add sapsa login

sp_addlogin sapsa, *******, saptools

go

2. Follow the 1593987 - SYB: Monitoring non-SAP ASE databases with the DBA Cockpit note (and the note that resets the default locking scheme no row level specified in that document).

3.  Fix the missing db option on tempdb:

sp_dboption tempdb, "ddl", true

go

4. Add sapsa missing privileges in the note above

--a must

grant role js_admin_role to sapsa

go

exec sp_modifylogin sapsa, 'add default role', js_admin_role

go

-- default installation has it, ATM seems to work without

grant role sybase_ts_role to sapsa

go

exec sp_modifylogin sapsa, 'add default role', sybase_ts_role

go

5.  Add the external login to job scheduler configuration

sp_addexternlogin SYB_JSTASK, sapsa, sapsa, {SAPSAPWD}

go

6.  Configure ASE to work with xml

exec sp_configure "enable xml", 1

go

7.  Navigate to Confniguration->Automatic Table Maintenance in DBA Cockpit and setup ATM.

8.  Add manually the maintenance tasks in case you have more than one database in you system which ATM should take care of (I've seen a note that this will be covered in future automatically - 2043922 - SYB Support of cross database ATM profiles.

9.  Start the regular 24x7 maintenance window manually and navigate to Diagnositcs->ATM->ATM Logs to see if the jobs really fired (If the status of the Maintenance Window turns to Running it is already a good sign.

That seems to cover the installation requirements for ATM.

Could anyone setup the documentation to cover all this please?

Thank you,

Andrew

[Former Member]

The ATM scheduling part for non-SAP ASEs is resolved.  The note for integrating ASE into DBA Cockpit does not specify the need for sapsa login to have the js_admin_role.  Whoever is responsible for the note - can you please update it?

1593987 - SYB: Monitoring non-SAP ASE databases with the DBA Cockpit

Thank you.

[former_member182259]

Can you check something.....sapsa should have the sap_adm role.   If you look at the sap_adm role, it should have the following roles:

sa_role

js_admin_role    <--- Missing from yours??

sap_mon

sap_mon should have mon_role....   In addition, sapsa should have sybase_ts_role.

Reason for question is whether the role was missing or inheritance wasn't being checked.   Some of the code used to do has_role(<role_name>)=1 - which fails if the role is inherited (which would return 2).....e.g. optdiag used to have this issue with sa_role.    So the question is whether there was an issue with your setup in which js_admin_role wasn't granted correctly to sap_adm - or if the routine that is failing was checking improperly.

[Former Member]

Jeff,

Thank you for reply.

There is no sap_adm role configured on this ASE since the installation was done manually rather than following the sap installation guide for BO systems.  I did figure out that the js_admin role was missing from sapsa login.  Adding this fixed the inability of DBA Cockpit to setup necessary jobs on JS.

There was another thing missing, though.  The jobs were indeed added to JS as soon as js_admin role was added to sapsa.  They were never scheduled or run, though, since there was no external login configured on ASE side. 

I think this code should be added to the 1593987 note:

grant role js_admin_role to sapsa

go

exec sp_modifylogin sapsa, 'add default role', js_admin_role

go

sp_addexternlogin SYB_JSTASK, sapsa, sapsa, {SAPSAPWD}

go

This fixed up the DBA Cockpit installation.  Which is now up and running (although it still continues to hit the dirty read issue on master..monProcessActivity and master..monCachedStatement.  In addition, ATM is not functioning either - all t he jobs hit errors in CL_SYB_DATABASE (line 11).  No ATM jobs scheduled to run are able to run.

Is there any guideline how to fix the ATM problems?

Thank you,

Andrew

[Former Member]

It turns out there is another thing to be configure on ASE level - 'enable xml'....

[Former Member]

I missed your point on sap_adm role.  sap_adm role has not been configured on this ASE since it was not built as part of installation procedure of SAP software package.