Foglight for Sybase 6.0.0

Workaround: You may install the new SAP ASE cartridge on the same FMS instance as the old cartridge. The new SAP ASE cartridge must use a different FglAM than the legacy cartridge, as well as a new SAP ASE user account.

History data

To maintain cartridge history, do not remove the legacy cartridge. For the same reasons as mentioned above, the history data in the legacy cartridge is not accessible from the new cartridge. Also, as mentioned in the workaround above, both legacy and new SAP ASE cartridges can be installed side by side. Hence legacy history will be available via the legacy cartridge.

Monitoring the SAP ASE instance

Same SAP ASE instance can be monitored by both Legacy SAP ASE agent and new SAP ASE agent (5.9.7.20), if unique FglAM and unique SAP ASE user account are used for each account.

ASP enhancements

•

Changes in agent startup parameter (ASP) values no longer require the agent to be deactivated and reactivated for the change to take effect.

•

Changes in agent startup parameter (ASP) values such that frequency and max rows are defined on a per-collection basis. Edit the Collection Details list to define the frequency and max rows of each collection.

Sybase open client

SAP ASE cartridge 6.0.0does not require Sybase Open Client to be installed.

Installation

After installing the cartridge, update the SAP ASE account for Foglight by manually downloading and running the scripts DB_Sybase_FirstRun_Scripts located under Foglight > Dashboards > Administration > Agents > Cartridges > Components for Download.

Run script rapsAdmin.sql as sa user. This script creates only two helper stored procedures:

•

sp_fgl_addgrant and sp_fgl_adduser

•

isql -Usa -Ppassword -SserverName -i rapsAdmin.sql -o rapsAdmin.out

Run these scripts to complete the upgrade and to display the correct information on the new dashboards. For script execution instructions, see “Configuring Sybase Login Privileges” in the Foglight for Sybase User and Reference Guide. Failed to upgrade the user account will result in broken agent instances.

Once you have installed the cartridge, deploy the agent package. For complete information, see “Deploying agent packages to monitored hosts” in the Foglight Administration and Configuration Help. After upgrading the cartridge and starting the agent, the Agent log will display the database version, cartridge version, EBF level, and so on.

•

When you start using a new SAP ASE user or when the SAP ASE cartridge is upgraded with a new version, the stored procedures will be automatically reinstalled.

•

For RS cartridge you need to configure the path to the sql.ini file in the ASP with all the servers participating in the RS Agent. You can take the sql.ini file which exists in the Replication Server as an example.

•

When creating a new agent, it is recommended to clone all the default lists to new lists which will be used only for this agent – in the same way you did with the legacy cartridge.

Post Installation Instructions

After installing the Cartridge for SAP ASE, perform the configuration steps below:

To create a new SAP ASE user, run the following scripts as sa user (use your username and password).

exec sp_addlogin 'username' , 'password' , sybsystemprocs

exec sp_fgl_adduser 'username' , 'foglightGroup'

Grant mon_role to 'username'

NOTE: The new user should have only one role: mon_role.

Viewing the Cartridge for SAP ASE:

On the navigation panel, under Dashboards, click Administration > Cartridges > Cartridge Inventory. For details specific to the installation of the cartridge agents, see the following sections.

Sybase Adaptive Server Prerequisites: Sybase_MDA Agent

Install Sybase_MDA

The first pre-requisite is that the Sybase_MDA feature be installed on the respective ASE. This feature is not automatically installed on all versions of Sybase ASE. A check the DBA can run is the query: “select * from master..monState”.

If the monitoring tables are not installed in your Adaptive Server (12.5.4 or later), have the Sybase DBA for the respective ASE follow the steps to install the tables listed in the Addendum at the end of this section.

For versions 15.x, there is no need for loopback and installmontables.

Configure Sybase_MDA

The Sybase_MDA feature has numerous parameters to both turn on and turn off specific metrics and determine the amount of memory the ASE will set aside for these metrics.

Performance monitoring how has the ability to enable the user to set the per_object_statistics flag to off.

Configure ports

Callback collections require communication between the Foglight Management Server and the Sybase server. Ensure that the port used by the Sybase host server is open between the Sybase host and the Foglight Management Server.

Sybase 'mon_role' assigned

The Sybase login defined in the Foglight agent property for the respective agent must have the “mon_role” assigned. This is also applicable to the ‘sa’ login.

Command to execute via ISQL or other client tool:

grant role mon_roleto_<logon_name>

Sybase Adaptive Server Pre-requisites: Sybase_RS Agent

The Sybase_RS agent requires separate common logins to be created for the Replication Server, the RSSD data server, the Primary data server, and the Replicate data server.

•

Replication Server - Create a user with no special permission for each Replication Server being monitored.

•

RSSD data server - Create a login for RSSD data server. Create a user with the same login name for the RSSD database. This user should be a member of the rs_systabgroup.

•

Primary data server - Create a login with the replication_role in each Primary data server. Create a user with the same login name for the Primary database.

•

Replicate data server - Create a login in each Replicate data server. Create a user with the same login name for the Replicate database. This user should have selected permission for the rs_lastcommit table.

NOTE: Refer to the Sybase_RS “Setting Connection Details” topic in the Managing Sybase Database Systems online help for more information.

Addendum

Preparation of the ASE version 12.5.4 or later for Foglight Cartridge for SAP ASE

Install MDA

If the monitoring tables are not installed in your Adaptive Server (12.5.4 or subsequent release), have the Sybase DBA for the respective ASE follow these steps to install the tables.

First, a server named “loopback” must be included in sysservers before installing the scripts for the MDA tables. Proxy tables for the monitoring tables are not created by default when you configure Adaptive Server. The tables are created in the second step using the installmontables script that is included in the Adaptive Server install (located in the $SYBASE/ASE-12_5/scripts directory).

Create a loopback server entry in ISQL:

Logon to Ada. and execute:

sp_addserver 'loopback', null, <ASE server name> go

Execute the installmontables script located in the $SYBASE/ASE-12_5/scripts directory (%SYBASE%/ASE-12_5/scripts for Windows) using ISQL:

isql –Usa –P<sa password> -S<server name> -n –i%SYBASE%/%SYBASE_ASE%/scripts/installmontables

Adaptive Server Enterprise configuration parameters

Sybase_MDA agent requires the following configuration for it to run correctly and gather the specified data for the Foglight Management Server.

The values shown for the following parameters are the minimum required values that the Foglight Cartridge for SAP ASE 5.9.3.10 needs for proper data collection and reporting. Any of these parameter values could be increased if necessary. (You can copy, paste, and execute the following script into an ISQL or other client session.)

sp_configure "statement cache size" <size_of_cache>

sp_configure "errorlog pipe max messages",1000

sp_configure "max SQL text monitored",256

sp_configure "sql text pipe max messages",3000

sp_configure "statement pipe max messages",3000

-- Flags

sp_configure "enable stmt cache monitoring" , 1

sp_configure "enable literal autoparam" [0|1]

sp_configure "enable monitoring", 1

sp_configure "sql text pipe active", 1

sp_configure "errorlog pipe active", 1

sp_configure "per object statistics active",1

sp_configure "statement statistics active",1

sp_configure "statement pipe active",1

sp_configure "wait event timing",1

sp_configure "SQL batch capture",1

sp_configure "process wait events",1

The following tables list proprietary Sybase flags and the Sybase_MDA agent tables dependent upon them. If any of the settings do not have values that fall in the recommended value range, data collection will issue complaints and fail to successfully collect; indicating that some MDA table collection was canceled as a result of the flags being set incorrectly. To prevent these alerts from occurring, use the appropriate Data Management properties to disable the collection that is failing to collect. Below is a listing of Sybase_MDA agent tables and the configuration settings they depend on:


Foglight Table	Sybase MonTable (MDA) Configuration Settings
TopHash	enable stmt cache monitoring = 1 statement cache size = size_of_cache enable literal autoparam = 0 or 1 NOTE: Setting enable literal autoparam to 0 causes Sybase to treat each SQL statement separately; setting it to 1 produces bind variables for each search argument in an SQL statement.
TopSQL	enable monitoring = 1 statement statistics active = 1 per object statistics active = 1 statement pipe active = 1 SQL batch capture = 1 max SQL text monitored > 0 statement pipe max messages > 0 sql text pipe max messages > 0
UsersBlocked	enable monitoring = 1 wait event timing = 1 SQL batch capture = 1 max SQL text monitored > 0
UsersLogCache	enable monitoring = 1 wait event timing = 1 per object statistics active = 1
TopApplications	enable monitoring = 1 wait event timing = 1 per object statistics active = 1
Index	enable monitoring = 1 wait event timing = 1 per object statistics active = 1
TopUsers	enable monitoring = 1 wait event timing = 1 per object statistics active = 1 SQL batch capture = 1 statement pipe active = 1 max SQL text monitored > 0 sql text pipe max messages > 0