1 Sybase ASE Plug-in Overview and Prerequisites

This chapter describes the Sybase Adaptive Server Enterprise (ASE) plug-in and provides the prerequisites necessary for installation on Oracle Enterprise Manager Cloud Control.

The following topics are presented in this chapter:

• Introduction to the Sybase ASE Plug-in

• What's New in This Release

• Platforms Supported by the Sybase ASE Plug-in

• Software Versions Supported by the Sybase ASE Plug-in

• Prerequisites

1.1 Introduction to the Sybase ASE Plug-in

The System Monitoring Plug-in for Sybase Adaptive Server Enterprise extends Oracle Enterprise Manager Cloud Control 12c to add support for managing Sybase Adaptive Server instances. By deploying the plug-in within your Cloud Control environment, you gain the following management features:

• Monitor Sybase Adaptive Server instances.

• Gather configuration data and track configuration changes for Sybase Adaptive Server instances.

• Raise alerts and violations based on thresholds set on monitored metrics and configuration data.

• Provide rich out-of-box reports based on the gathered data.

• Support monitoring by a remote Agent. Local Agent is an agent running on the same host as the Sybase Adaptive Server. Remote Agent is an agent running on a host that is different from the host where Sybase Adaptive Server is running.

• Collect and display top SQL Statements and their related statistics, including CPU Time and Execution Count, of your Sybase ASE Database.

• Collect and display information regarding the top Lock Wait Events in Sybase ASE Database, including top Wait Count by Table and top Wait Times.

• Utilization of Cloud Control jobs to start and stop the Sybase ASE database. These jobs are not only accessible from the Cloud Control Jobs Library but also from the Sybase ASE plug-in's UI home page.

• Expose vital Sybase ASE metrics in the form of easy-to-use charts and tables.

1.2 What's New in This Release

This release of the Sybase ASE Database plug-in for Oracle Enterprise Manager Cloud Control 12c includes many new features for better visibility into your Sybase ASE environment. Highlighted below are some of the new features included in this release:

• Support Updates

• Updated User Interface

• Sybase ASE Out-of-the-Box Monitoring Templates

• Sybase ASE Database Plug-in Metric Thresholds

• Business Intelligence Publisher Reports

1.2.1 Support Updates

The Sybase ASE Database plug-in for Oracle Enterprise Manager Cloud Control 12c has been updated to support the jconn4.jar JDBC driver and maintain support for jconn3.jar.

1.2.2 Updated User Interface

The user interface has been updated to add a Performance page and add a new link to the Home page.

The Home page (Figure 1-1) has been enhanced with the addition of a link in the Summary section to the Performance page. This has enabled more efficient navigation throughout the plug-in.

Figure 1-1 Sybase ASE Plug-in Target Home Page

The all-new Performance page (Figure 1-2) has been added to the plug-in to give greater insight into the monitored Sybase ASE key performance indicators (KPI's) over a selectable time range. This includes Connection KPI's, Memory KPI's and Space and CPU KPI's.

Figure 1-2 Sybase ASE Plug-in Performance Page

1.2.3 Sybase ASE Out-of-the-Box Monitoring Templates

This release adds three out-of-the-box monitoring templates (Figure 1-3) to streamline configuration of your Sybase ASE Database. This includes Efficiency, Traffic, and Utilization monitoring templates that you can use depending on your needs.

Figure 1-3 Sybase ASE Plug-in Monitoring Templates

1.2.4 Sybase ASE Database Plug-in Metric Thresholds

This release enables additional metric thresholds (Figure 1-4) to increase user control of your Sybase ASE Database. This includes metric thresholds for Device Statistics, Top 10 Big Cached Objects, Waiting Events Statistics, Cache Pools Statistics, and many more.

Figure 1-4 Sybase ASE Plug-in Metric Thresholds

1.2.5 Business Intelligence Publisher Reports

In this release, Information Publisher Reports included in the previous release of the Sybase ASE Plug-in have been migrated to BI Publisher Reports. BI Publisher Reports eliminate complexity and simplify report development and maintenance.

See Reports and Metrics for the Sybase ASE Plug-in for more information on using these reports.

1.3 Platforms Supported by the Sybase ASE Plug-in

The plug-in supports monitoring of Sybase Adaptive Server Enterprise (ASE) on all the platforms where Sybase ASE can be installed.

1.4 Software Versions Supported by the Sybase ASE Plug-in

This plug-in supports the following versions of products:

• Enterprise Manager Cloud Control 12c:

  • The plug-in is certified for the latest release of Oracle Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5.0) (Oracle Management Server (OMS) and Oracle Management Agent).

  • For the previous version of Enterprise Manager (12.1.0.4.0), you should:

    • Upgrade the OMS to version 12.1.0.4.3 by installing patch 20392036.

    • Upgrade the Management Agent to version 12.1.0.4.9 by installing patch 20950034.

    These patches are available for download from My Oracle Support:

    https://support.oracle.com
    

• Version 15.7 and 16.0 or higher of Express (XE), Enterprise (EE), and Developer (DE) editions of Sybase Adaptive Server Enterprise.

Note:

The system monitoring plug-in for Sybase Adaptive Server is not certified for monitoring Sybase Adaptive Server Clusters. For more information, refer to the known issues described in the Oracle Enterprise Manager System Monitoring Plug-in Troubleshooting Guide.

1.5 Prerequisites

The following prerequisites must be met before you can deploy the plug-in:

1. Sybase Adaptive Server Enterprise version 15.7 to 16.0 or higher is installed.

2. The following components of Oracle Enterprise Manager Cloud Control are installed and running:

   • Oracle Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4.0) or higher. To upgrade to this version, install patch 20392036, available for download from My Oracle Support (https://support.oracle.com).

   • Oracle Management Agent 12.1.0.4.0 or higher (any platform supported by this release of agent).

     You can install the agent on the same computer as Sybase Adaptive Server (referred to as local agent monitoring), or you can install the agent on a different host computer from Sybase Adaptive Server (referred to as remote agent monitoring).

   • The Inventory and Usage Patch (20692416) must be installed. This patch is available for download from My Oracle Support (https://support.oracle.com).

3. The Monitoring and Diagnostic (MDA) tables must be installed before they can be queried. Follow the steps below to install MDA tables:

   1. Ensure that the configuration parameter enable cis is set to 1. If not, restart ASE. Run the following command:

      sp_configure 'enable cis', 1 
      go
      

   2. Install the MDA tables. Do not run this script with 'sqsh' as it will cause errors. 'sqsh' sees a '$' as the start of a sqsh variable, and this distorts the native RPC names, since these start with a '$' as well. Instead, either use 'isql' as below, or run 'sqsh' with the '-Lexpand=0' option to disable sqsh variable expansion feature. This script is included in the installmaster.

      isql -U sa -P yourpassword -S YOURSERVER \
           -i $SYBASE/$SYBASE_ASE/scripts/installmontables
      

   3. Assign mon_role to logins allowed MDA access. This also applies to the 'sa' login.

      use master
      go
      grant role mon_role to sa
      go
      

   4. Test basic MDA configuration as follows. You may need to disconnect and reconnect to activate mon_role when you grant this role to the login you are currently using.

      select * from master..monState
      go
      

   5. Enable all configuration parameters. These are all dynamic (except the per object statistics active parameter). For all 'pipe' tables, the number of messages is set to 100 here, but you can choose a larger size.

      sp_configure "enable monitoring", 1
      go
      sp_configure "sql text pipe active", 1
      go
      sp_configure "sql text pipe max messages", 500
      go
      sp_configure "plan text pipe active", 1
      go
      sp_configure "plan text pipe max messages", 100
      go
      sp_configure "statement pipe active", 1
      go
      sp_configure "statement pipe max messages", 500
      go
      sp_configure "errorlog pipe active", 1
      go
      sp_configure "errorlog pipe max messages", 100
      go
      sp_configure "deadlock pipe active", 1
      go
      sp_configure "deadlock pipe max messages", 100
      go
      sp_configure "wait event timing", 1
      go
      sp_configure "process wait events", 1
      go
      sp_configure "object lockwait timing", 1
      go
      sp_configure "SQL batch capture", 1
      go
      sp_configure "statement statistics active", 1
      go
      sp_configure "per object statistics active", 1
      go
      sp_configure "enable stmt cache monitoring", 1
      go
      sp_configure "max SQL text monitored", 4096
      go
      set mon_stateful_history off
      go
      

   6. Set the following static parameter to a higher value (the setting is in bytes per user connection) if you are expecting a lot of (or long) SQL batches:

      sp_configure "max SQL text monitored", 2048
      go
      

   Note:

   Enabling the following configuration parameters will result in >5% performance overhead:

   sp_configure "sql text pipe active", 1
   sp_configure "sql text pipe max messages", 500
   sp_configure "max SQL text monitored", 2048
   sp_configure "plan text pipe active", 1
   sp_configure "plan text pipe max messages", 100
   sp_configure "statement pipe active", 1
   sp_configure "statement pipe max messages", 500
   sp_configure "statement statistics active", 1
   sp_configure "per object statistics active", 1
   sp_configure "enable stmt cache monitoring", 1
   sp_configure "max SQL text monitored", 4096 
   set mon_stateful_history off
   

4. As part of JDBC URL, either IP Address or host name can be provided. Ensure that the host name can be resolved consistently on the network. Standard TCP tools such as "nslookup" and "traceroute" can be used to verify the host name. Validate using the following commands on Management Agent where plug-in is deployed:

   • nslookup <hostname>

     This returns the IP address and fully qualified host name.

   • nslookup <IP>

     This returns the IP address and fully qualified host name.

5. The plug-in may sometimes depend on the cache size of the TempDB.

6. If the monitoring user does not have permission on all databases in Sybase ASE target, provide permission to query SYSSEGMENTS table to monitoring user or else create guest user account in each database.

7. To enable the use of Jobs for Sybase ASE the following settings are required:

   1. The plug-in for Sybase ASE must be deployed as a local configuration. Local configuration is when the Oracle Management Agent with the plug-in installed resides on the host of the Sybase ASE Server.

   2. Set the Sybase Adaptive Server Enterprise Preferred Credentials for both "Admin Sybase ASE Credentials" and "Agent Host Credentials."

   3. Set the following Sybase environmental variables:

      • STARTSERVER - Must equal the full path to the Sybase startserver executable. For example:

        /apps/sybase/ASE-15_0/install/startserver
        

      • RUNFILE - Must equal to the full patch of the Sybase runfile executable. For example:

        /apps/sybase/ASE-15_0/install/RUN_SYBASE_SERVER
        

8. Verify that the JDBC drivers are available in your Sybase/SAP ASE Installation. The JDBC drivers are located in the database software installation location:

   • For Sybase 15.7 ASE:

     echo $SYBASE
     =/apps/sybas
     $SYBASE/jConnect-7_0/classes/jconn4.jar
     /apps/sybase/jConnect-7_0/classes/jconn4.jar
     

   • Sybase 16.0 ASE

     /apps/sap/jConnect-16_0/classes/jconn4.jar
     

   If the drivers are not there, then follow the instructions in Setting up the JDBC Driver.