Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > White Paper-Technical > Sybase Central for Adaptive Server Enterprise

Sybase Central for Adaptive Server Enterprise

This white paper introduces Sybase Central and the Adaptive Server Enterprise Plug-in. The plug-in provides management & monitoring capabilities for ASE 11.5.x & SQL Server 11.0.x. Features: object properties, user acct. management, remote server access.
 
RSS Feed
 
 
 

Sybase Central for Adaptive Server Enterprise

November 1997

Contents

Executive Summary
Sybase Central Architecture
Managing Adaptive Server Enterprise
Monitoring Adaptive Server Enterprise
Historical Server

Executive Summary

Sybase Central is a graphical management tool for Sybase products. It implements the Sybase enterprise management strategy, which calls for a single management console, seamlessly integrated, across all server and middleware products. Sybase Central runs on Microsoft Windows 95 and Microsoft NT. It connects to and manages Sybase products running on any Sybase-supported platform.

Sybase Central for Adaptive Server Enterprise (known as the Adaptive Server Enterprise Plug-in) is bundled with Adaptive Server Enterprise (ASE) release 11.5 and can be installed from any of the ASE delivery CDs. (For the HP-UX platform, the desktop applications are on a separate CD.)

Using Sybase Central and the ASE Plug-in, systems and database administrators can manage and monitor all Adaptive Server Enterprise release 11.5 and all SQL Server release 11.0.x installations on the network, regardless of the platforms on which they are running, from a single console.

Managing these servers with Sybase Central does not preclude the use of the isql administrative interface for ASE and SQL Server; nor does it preclude the use of scripts. The isql command line interface and the Sybase Central graphical interface perform the same functions and can be used interchangeably. The Sybase Central interface is more intuitive and easier to learn.

Some of the unique benefits of using Sybase Central to manage ASE and SQL Server release 11.0.x are described in this paper:

  • Visual representation of objects. The tree view in the Sybase Central main window expands to show each database, login, device, remote server, named cache, engine group, execution class, role, and current process in each ASE.  Each database expands to show objects such as tables, stored procedures, views, rules, and users.
  • Easy point and click interface. Buttons, selection lists, tab dialog boxes, and wizards provide a convenient and quick way to view and update system and database objects.
  • Management of multiple servers from one console. From the Sybase Central main window, administrators can manage all Adaptive Server and SQL Server 11.0.x installations on the network.
  • OmniConnect management. Administrators can control access to data on remote Sybase and non-Sybase databases by managing remote servers, remote server logins, and proxy tables from Sybase Central.
  • Code editor. Authorized administrators can display, edit, and print code for stored procedures, triggers, and views. The code editor performs syntax highlighting, language-sensitive indenting, and drag-and-drop editing.
  • Generation of data definition language (DDL). A single menu command generates DDL scripts (Transact-SQL commands) that capture the definition of the selected Adaptive Server objects.
  • Object property sheets and dependency lists. Each object has a property sheet that lets authorized administrators view or modify configured characteristics. Where appropriate, the property sheets include tabs showing object dependencies. Administrators can see at a glance, for example, which tables use a specific user-defined datatype.
  • Navigation between related objects. From a displayed object's property sheet, you can navigate directly to the property sheet for a related object.
  • User account management. Logins and roles are manageable objects in Sybase Central. A tab on the login property sheet shows a login's currently assigned permissions on tables, stored procedures, and views.
  • Online help. The product's online help includes ASE conceptual information and live links to related subject matter in Sybooks-on-the-Web.
  • Monitoring for performance and tuning. Sybase Central includes 14 monitors that collect and display various server-level and object-level performance metrics. The data is displayed in graphical or tabular form, using user-controlled sample intervals.

Historical Server, which saves ASE performance data in files for later playback, summarization, or analysis, is also briefly described in this paper. Historical Server extends the monitoring capabilities of Sybase Central, providing input to activities such as resource trends analysis, capacity planning, and after-the-fact problem solving concerning lock and resource contention. The user interfaces to Historical Server are through isql or a programming interface, rather than through Sybase Central. SQL Advantage, a utility that provides access to isql, is available from Sybase Central.

Sybase Central is the third generation of Sybase systems management tools. Long-time Sybase customers might remember SA Companion, SQL Server Manager (SSM), and SQL Monitor. Unlike the earlier products, Sybase Central is meant to manage all Sybase products in a consistent manner. While an attempt was made to incorporate the previous functionality into Sybase Central, it is not simply a rewrite of the earlier products. Some differences will be noticed.

Some customers might also be familiar with the Enterprise SQL Server Manager (ESSM) product. This product was sold to Platinum. Sybase is working with Platinum and other enterprise systems management vendors to ensure that our products will be easy to manage when a customer uses any of these frameworks.

Sybase Central Architecture

Sybase Central provides a basic systems management framework. Individual Sybase products have their own customized management interfaces that "plug in" to the Sybase Central framework. This strategy ensures a common interface across all products while enabling functionality specific to each product.

The Sybase Central framework consists of toolbars, menus, tab-style property sheets, and a two-pane interface similar to that of the Windows 95/NT Explorer. The left pane implements a tree view, which has, as its top level, a list of plug-ins. In the tree view, a plug-in expands to show all installed instances of the product being managed. The list continues to expand to show all manageable objects within an instance of a product. Each plug-in defines an object hierarchy appropriate to the product it represents. Each plug-in has its own wizards, dialog boxes, and property sheets that apply specifically to its product.

A single executable file starts Sybase Central and loads all currently registered plug-ins. Administrators can register and unregister plug-ins at any time. They can also install new plug-ins without disturbing the existing installations.

Plug-ins are currently available for the following Sybase products:

  • Adaptive Server Enterprise (and SQL Server release 11.0.x)
  • Adaptive Server Anywhere
  • Sybase IQ
  • DbQ
  • Net Impact Dynamo

With each Sybase release, more products will develop plug-ins, until the goal to manage all Sybase products from Sybase Central is realized.

Managing Adaptive Server Enterprise

For new or infrequent administrators, Sybase Central reduces the learning curve for effective and timely administration. For even the most experienced administrators, Sybase Central offers an organized presentation of some hard-to-obtain information, such as dependencies of objects on other objects and lists of user permissions by object or by user. For every level of administrator, Sybase Central can increase productivity with its big-picture view and features such as a code editor and script generation.

The ASE Plug-in conforms to Internationalization (I18N) specifications and provides full English, French, German, and Japanese support.

The following figure shows the Sybase Central main window with some expanded objects in an ASE installation.

Easy-to-Use Interface

The ASE Plug-in for Sybase Central is an easy-to-use alternative, not a replacement, to the isql command-line administrative interface. Administrators can use both interfaces interchangeably. They can continue to use and develop scripts.

By using the Sybase Central point-and-click interface, with its expanding tree view, tab dialog boxes, and scroll list boxes, administrators no longer need to remember stored procedure names and syntax to obtain or change system and database information. Wizards eliminate the need to remember the correct command sequences for creating new objects.

The interface presents object names, rather than requiring users to research or remember names for objects such as tables, columns, rules, devices, and triggers. Property sheets display information about an object without requiring the administrator to understand ASE system table structure.

To complete the goal to manage from a single console, the ASE plug-in also provides ASE performance monitoring capabilities and access to the following Sybase utility programs:

  • SQL Advantage. Connects to ASE for the purpose of composing and executing Transact-SQL statements and stored procedures.
  • Directory Services Editor (dsedit). Edits the Sybase directory services file on the Sybase Central machine.
  • Configure Sybase Server (syconfig). Configures ASE, Monitor Server, and Backup Server.

Object Property Sheets

Sybase Central offers a big picture view of all ASE installations combined with the ability to easily obtain properties of individual objects. For example, with a few mouse clicks as illustrated in the following figure, administrators can review configuration details concerning a database device. Where appropriate, changes can be made on the property sheets.

When administrators add new objects using Sybase Central, wizards prompt for all required and optional information (properties) about the object. After creation, an object's properties are viewable on its property sheet. The tabs on a property sheet organize the properties into easily identifiable groupings. Property sheets display object information regardless of how the object was created: by a Sybase Central wizard, with a script file, or with Transact-SQL statements submitted from SQL Advantage or some other isql tool.

Dependency Lists

When database administrators consider making changes to objects, they need to understand the effects those changes might have on other objects. Using Sybase Central, it is easy to research dependencies between objects. The Dependencies menu command displays tab dialogs that show two types of dependencies concerning a selected object:

  • The "Referenced by" tab shows the objects that depend on the selected object. For example, a table might be referenced by stored procedures, triggers, and views.
  • The "References" tab shows the objects on which the selected object depends. For example, tables might have references to defaults, rules, and user types.

The following figure shows examples of both types of dependencies for the titles table.

Account Management

By clicking in the Sybase Central tree view, administrators can see at a glance the logins and user-defined roles in each ASE, and the users and groups defined in each database. Wizards guide administrators through the process of creating new instances of these objects. Property sheets display the parameters used to create the objects and additional interesting information. For example, the property sheet for a login shows:

  • Databases owned by the login
  • Roles assigned to the login
  • Users mapped or aliased to the login

User-defined roles, a new feature in ASE release 11.5, are fully supported by the ASE Plug-in.

Administrators can assign and change permissions by clicking in a matrix displayed on a tab in a property sheet. The matrix format for assigning permissions applies to tables, stored procedures, views and commands (such as create table). It applies to permissions associated with user-defined roles, users, and groups. The following figure shows the matrix for assigning permissions to a user in a database.

The Code Editor

From within Sybase Central, administrators can easily view, edit, and print the statements in stored procedures, views, and triggers. The fully integrated editor opens automatically when an administrator creates a new procedure, view, or trigger. For existing objects, a simple menu command opens the editor and displays the code behind the selected object. Encrypted code is not displayed.

Some of the code editor's features are:

  • Automatic syntax highlighting
  • Language-sensitive indenting
  • Unlimited undo and redo
  • Drag-and-drop editing
  • Find-and-replace editing
  • Opening and saving to external files
  • Printing
  • Executing code that creates new objects

The code editor cannot access data in a database. For example, in a stored procedure that creates and populates a new table, the code editor would create the new table, but would ignore the code that populates the table. To populate the table, the administrator could execute the stored procedure from SQL Advantage.

Data Definition Language (DDL) Generation

Sybase Central includes a powerful DDL generator that writes the Transact-SQL code to create a selected object. The selected object might be a table, view, trigger, login, device, or even an entire database, including all objects within it. The generated code displays in a window where it can be edited, printed, or saved to an external file. Multiple DDL generation requests can be appended into a single file.

OmniConnect Management

The Component Integration Services (CIS) feature in ASE release 11.5 allows users to access data on remote servers from within a local ASE. A remote server can be another Sybase Server such as Adaptive Server Enterprise, Adaptive Server Anywhere, or Sybase IQ. Using the appropriate DirectConnect product, a remote server might also be a non-Sybase server such as Oracle, DB2, or Informix. With CIS, a user logged into one ASE has seamless and transparent access to data stored in both the local ASE and remote servers.

The ASE Plug-in for Sybase Central performs the following management tasks related to remote data access using CIS:

  • Remote server management. Remote servers must be defined on ASE. Using the ASE Plug-in, administrators can add and delete remote servers, and set or change various remote server properties.
  • Remote server login management. Before accessing remote data, a user's local ASE login must be mapped to valid logins on the remote servers. This step can be performed from the ASE Plug-in.
  • Remote server table management through proxy tables. Administrators can add, change, and delete tables on remote servers by making those changes on proxy tables on the local ASE.
  • Proxy table verification. Remote table definitions can be changed by their respective administrators at any time, making the local proxy table definitions out-of-date. Proxy table verification compares the two resources and reports changes required in the proxy table definitions. 

In addition to managing remote servers for CIS, the ASE Plug-in can manage OmniConnect. The interface is the same as the ASE interface. OmniConnect is similar to ASE, except that it does not store data; it has only proxy tables.

Monitoring Adaptive Server Enterprise

The monitors in Sybase Central gather ASE performance information and display it in graphs and tables. System and database administrators can use the information in the monitors to:

  • Identify potential resource bottlenecks.
  • Research current problems.
  • Identify objects involved in lock contention problems.
  • Identify objects that might benefit from specific cache or device bindings.
  • Tune ASE, applications, and stored procedures for better performance.
  • Analyze database design and index structures and how they affect performance.

Some of the resources and major performance indicators that the monitors track are listed below.

Resources

Other Performance Indicators

CPU Usage

Transaction rates

Page I/O at server and object levels

Hit rates for pages found in cache

Device I/O

Yield to OS rates per engine

Locks granted, waited for, and denied

Deadlock counts

Network usage rates and packet sizes

Number of connections

 

Number of executions per stored procedure

 

Number of locks waiting on another lock


In the monitor graphs and tables, performance metrics are displayed as counts of occurrences (such as number of page writes) or rates. A rate reflects the number of occurrences per second. Snapshot information, such as lock status of a table, number of blocked locks, and process status, shows status at the time the last sample was taken.

The Monitors

The ASE Plug-in for Sybase Central includes 14 monitors which are briefly described below. ASE release 11.5.1 (a maintenance release) will include an additional monitor, the Process Current SQL Statement Monitor, which displays the SQL statement and query plan currently executing in a selected process.

Monitor Name

Description

Application Activity Monitor

Shows high-level resource information for currently running applications.

Cache Monitor

Shows information about the procedure cache and data cache.

Data Cache Monitor

Shows overall activity and efficiency levels of the 10 most active data caches (including the named data caches and the default cache).

Device I/O Monitor

Shows buffer (not page) I/O activity on the database devices defined for ASE.

Engine Activity Monitor

Shows details about the current CPU load caused by task processing. The monitor isolates the time ASE spends processing tasks versus the total CPU time used by ASE.

Memory Utilization Monitor

Displays a pie chart showing memory allocation. The information is based on ASE configuration parameter values, and is therefore static.

Network Activity Monitor

Shows packet volume and packet sizes used for communication between ASE and its clients. The monitor also shows values of some configuration parameters that affect network traffic.

Object Lock Status Monitor

Shows details about currently held or blocked locks on tables.

Object Page I/O Monitor

Shows physical and logical page I/O statistics associated with tables and their indexes. It shows activity on all tables, including system tables, temporary work tables, and database tables.

Performance Summary Monitor

Summarizes the principal performance indicators, including CPU usage, transaction rates, page I/O rates, device I/O rates, and lock rates.

Performance Trends Monitor

Plots values for user-selected statistics as they occur, for up to 60 sample intervals. Each selected statistic appears in a separate graph.

Process Activity Monitor

Shows high level resource information for currently running processes. If a process consists of worker threads, the monitor adds all data for all worker threads and presents only the totals for the parent thread (the process).

Stored Procedure Activity Monitor

Shows metrics on execution, page I/O, and locks for all stored procedures and triggers that have executed during the sample or session.

Transaction Activity Monitor

Shows summary information about transaction activity handled by ASE.

The next two figures show examples of the Performance Summary Monitor and the Object Lock Status Monitor.

The Performance Summary Monitor shows the overall performance of ASE.

The Object Lock Status Monitor contains more detailed statistics and some snapshot data.

Refreshes

Refreshes are controlled by the user. Any desired sample interval, from 1 second to 24 hours, can be specified. In most monitors, users can select between viewing cumulative statistics for the entire time a monitor is open or statistics only for the last sample interval.

Filtering

Users can set a filter on a monitor to limit the displayed data. Object filters can restrict data to one or more specific devices, named caches, engines, tables, stored procedures, or applications. Threshold filters can apply to numeric data to restrict the display to the rows containing values that are equal to or above the threshold value. Top N filters on numeric data limit the display to N rows with the highest values in the filtered column.

Monitoring Requirements

The Sybase Central monitors require a Monitor Server to be running on the machine along with the ASE or SQL Server. To monitor SQL Server release 11.0.x using Sybase Central, installations must install the correct executable version of Monitor Server release 11.5 to run on the machine with SQL Server release 11.0.x. Monitor Server obtains information from ASE unobtrusively by accessing a shared memory area. It does not impair the performance of ASE.

The Sybase Central monitors are Java applications. They require the Java Development Kit 1.1.3 or Java Runtime Environment 1.1.3 from Sun Microsystems. Sybase does not deliver these components, but either can be downloaded for free from the Web.

Historical Server

Historical Server collects performance information about ASE 11.5 and SQL Server 11.0.x and saves the information in files for deferred analysis. Administrators can play back the data exactly as it was recorded, or they can use the Historical Server playback features to summarize the data at various levels of granularity. The original detail or the summarized versions can be used in desktop analysis tools or as input to ASE tables.

Summarized historical performance data is useful for observing trends in resource usage. Capacity planning, trends analysis, reporting, and benchmarking are activities that can benefit from summarized performance data.

Detailed historical performance data is useful for tracing the causes of intermittent or recurring problems, for keeping watch over the general performance of ASE, and for creating benchmarks against which to compare future performance. Historical Server can collect all the data collected in the Sybase Central monitors plus additional data not available in Sybase Central. When Historical Server is collecting performance data, an administrator does not need to be physically sitting at a console to capture performance information.

The Historical Server interfaces let users specify the data to collect and the time period desired. Over 150 data items are available. Statistics can be returned as absolute counts of activity or as rates. All rates are number per second. The interfaces include filters to limit the returned data.

Historical Server has two interfaces. One is a command-line interface that is used from isql. Scripts can be used to ensure consistent data collection over time. The second is a programming interface included in Monitor Client Library.



 

DOCUMENT ATTRIBUTES
Last Revised: Aug 21, 1998
Product: SQL Server, Adaptive Server Enterprise
Technical Topics: Performance & Tuning
  
Business or Technical: Technical
Content Id: 20174
Infotype: White Paper-Technical
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics