ASE121 Adaptive Server Enterprise--Recent Developments and Future Plans in System Management

1. ASE121 Adaptive Server Enterprise--Recent Developments and Future Plans in System Management Peter Dorfman Senior Staff Software Engineer peter.dorfman@sybase.com August 5, 2003

2. Presentation Contents • Sybase System Management Universe • System Management Objectives • ASE System Management Features in ASE 12.5 and beyond • Feature Details • Your comments and questions

3. The System Management Universe Who develops system management tools? Vendors Partners Sybase Customers

4. Software Resources • Sybase, Inc. • Sybase Partners • BMC Software • Leccotech • PeopleSoft • Software Vendors • Quest Software • White Sands Technology • Embarcadero Technologies • Sybase Community • Sybase Developers Network/CodeXchange • Shareware sources • Customers • In-house developers • Customized applications

5. Sybase’s Goals • Reduce cost of ownership through system management features • Build tools to support ASE product features and operational requirements • Support system management vendors and partners in bringing new products to market • Provide tools for customers who develop management tools

6. Commitment to Improvement • We are investing in products such as the ASE Plug-in, DBXray and Installer • We are working with partners and vendors to help provide and improve their tools • We are working with the Sybase community to make additional tools available • E.g., through the SDN CodeXchange forums • We want YOUR ideas and input!

7. Reducing Cost of Ownership • Improve ease of use • For the ASE and management tools • Reduce complexity • To make powerful ASE features more accessible and easy to understand • Leverage existing skills and resources • So that you can spend your time on important tasks

8. Improving Ease of Use • The ASE is already one of the easiest databases to manage • We are adding new features and tools to make it easier • Job Scheduler • Self-management features • DBXray • We welcome your input on improvements in this area

9. Still Farther to Go… • Some areas we will be working on: • Enterprise features • ASE Plug-in improvements • Support for common tasks such as security management, backup management and troubleshooting • Data movement • Integration with Replication Server • Index tuning • Space management

10. ASE System Management Features in ASE 12.5 and Beyond

11. ASE System Management Tools • Sybase Central ASE Plug-in • Monitor Server • Historical Server • DDLGen • DBXray for Sybase • Migration Tool • Monitoring Tables • SNMP Subagent • Job Scheduler • Sp_sysmon • SQL Expert • SQL Debugger • PowerTransfer

12. New System Management Features: ASE 12.5 • ASE Plugin Java Edition • 100% java • Runs on all ASE platforms • Highest level support for ASE • DDLGen Utility • Command line utility also available in ASE Plugin • SQL Debugger API • Enhanced SQL debugging also used by some vendors • SQL Expert • Identifies poorly performing queries • Provides automated SQL tuning recommendations

13. New System Management Features: ASE 12.5 • ASE Replicator and Replicator Manager • New “light weight” data replicator • Migration Tool • Supports migration to larger page size • Support for cross-platform migration • SQL Advantage (back by popular demand!) • SNMP Subagent • AgentX protocol subagent • Available from Sybase Developer Network

14. New Features: 12.5.0.3 and Beyond… • ASE Monitoring Tables • SQL access to process-, object- and resource-level performance monitoring data • DBXray • Graphical performance monitor (at last!) • State of the art detailed “real-time” diagnostic information • ASE Job Scheduler (ASE 12.5.1) • Support for scheduled backups, DBCC, update stats, reports, etc. • Pre-defined templates for common tasks • Improved Installation: InstallShield • GUI, character mode and scripted installation • For all release types

15. ASE Plugin to Sybase Central • Complete support for managing your ASE • Fully rewritten in Java for ASE 12.5 release • Runs on all ASE Platforms • Supports new ASE features including EJB Server, HA, ASE Replicator, expanded column and page sizes, multiple tempdb • Future features will improve usability and enterprise administration • Section 508 Compliant

16. ASE Plugin: New in 12.5.1 • Table data view • Job Scheduler • Logging of SQL for ASE Plugin commands • Non-ASE proxy data sources • Portable database support • Disk Resize • Improved process and lock monitoring • Section 508 accessibility compliance

17. ASE Plugin: Examples Table Data View SQL Log Server Shutdown Wizard

18. ASE Plugin: Directions • Increased information about sessions • Resource usage, SQL Text, blocking • SQL pre-view and editing of commands • Access to ASE errorlog • Improved and new wizards • Performance improvements

19. DDLGen Utility • Introduced in ASE 12.5 • Command line utility for generating DDL for an existing database or individual objects • Tables, indexes, triggers, etc. • Available on all platforms • Also used by Sybase Central ASE Plugin to generate DDL for objects • Will continue to support new object types in future releases

20. ASE Monitoring Tables • Available in ASE 12.5.0.3 Release and later • Full SQL language support for access to detailed monitoring data • Tables include data caches, locks, SQL statement statistics, SQL Text, system and process wait states, device and network IO…. • Filter, join, analyze performance data: • E.g., Process wait time, data cache object usage • Access performance data from remote servers via CIS • Useful to DBA’s and system management vendors

21. Monitoring Tables: Example Show object with Largest # of pages in Default data cache Show all objects in default data cache

22. Monitoring Tables: Example • Show the currently executing query and user with the greatest amount of CPU time • select s.SPID, suser_name(p.suid), s.CpuSeconds, t.LineNumber, t.SQLText • from monProcessStatement s, monProcessSQLText t, sysprocesses p • where s.CpuSeconds=(select max(CpuSeconds) from monProcessStatement) • and s.SPID = t.SPID • and p.SPID = s.SPID

23. Job Scheduler • Available in ASE 12.5.1 • Create jobs and execute them according to a schedule • Perform backups, DBCC’s, update statistics, reorgs, reports, etc. on a scheduled – hands-off - basis • Job results are logged to allow problem analysis • Job and Schedule definitions can be shared by multiple users • Supports job definition through: • GUI Interface in Sybase Central • SQL Language interface

24. Job Scheduler: Example Review Job Status Manage Job Properties Create Scheduled Jobs

25. Self-Management: ob Scheduler Templates • Make life easier by reducing steps to schedule a task • Automate common management and configuration tasks • Shortcuts for defining common jobs • Jobs execute on threshold or time basis

26. Self-Management: ob Scheduler Templates • Templates planned for ASE 12.5.1 • Database backup • Dump transaction • Update statistics • Delete statistics • Reorg rebuild table or index • Reorg reclaim space • Reconfigure • User connections • Metadata cache • Number of locks

27. Migration Tool • Introduced in ASE 12.5.0.1 release • ASE 12.5 introduced larger page sizes • Use to move existing data from an ASE using 2K page size to one using larger page size • Supports cross-platform move in 12.5.0.3 • Automates schema and data movement processes • Generates and applies database DDL • Copies data to target server using CIS • Easier than recreating database and using BCP

28. Migration Tool • Migrate data between ASE servers • Database schema and data migration • Graphical selection of source and target servers and objects and migration status • Used to increase page size or move databases

29. PowerTransfer • Data migration utility • Extension (Plugin) to PowerDesigner’s Physical Architect Module • Based on PowerDesigner 9.5.1 • Launched by PowerDesigner, and uses Data Model to identify tables to be copied • Assumes that Schema has been pushed from source DBMS to ASE • Supports importing data from MS SQL Server, Oracle, DB2, ASE 12.5 and earlier

30. PowerTransfer: Example Server Login Table Selection

31. SNMP Subagent for ASE • Available in ASE 12.5.0.2 for Apple OS X • Available on all platforms soon • Uses Java AgentX implementation • Downloadable opensource from SDN • SNMP Version 2c support • Support for standard MIBS: • RDBMS MIB RFC 1697 • Network Service MIB RFC 2248 • Implementation of Sybase Proprietary MIB • Server monitoring and trap notification for server state changes. • Trap notification for ASE errors through sp_errornotify • Monitoring of Sybase ASE and Open Servers.

32. SNMP Subagent Architecture ASE • Server Status • RDBMS MIB • Error traps • sp_errornotify ASE Monitoring OpenServer (Backupserver) SNMP Subagent Notification OpenServer OpenServer (Monitor Server} • Server up/down Management Applications

33. Sybase SQL Expert • Released in ASE 12.5 • Separately licensed Windows GUI application • Identifies, evaluates and rewrites SQL statements according to the database structure, indexes and data distribution. • Uses a unique technology based on Artificial Intelligence to rewrite SQL statements. • Automates the SQL tuning process • Recommends SQL improvements • Eliminates the need for manual tuning • Solution for inexperienced and experienced SQL tuners SQL

34. SQL Expert: Results • Scans existing SQL procedures • In database • From disk files • Monitors SQL queries in running server • Identifies “problematic” SQL according to customized standards • Determines all semantically equivalent alternative queries • Performs performance benchmarks of each alternative query • Identifies most efficient query formulation • By initial response time or overall time

35. SQL Expert: Finds and Fixes Problematic SQL Scans your stored procedures or scripts Identifies problematic SQL

36. SQL Expert: Recommendations Recommends more efficient query formulations Measures query performance

37. ASE Performance Monitoring: BMC DB Xray • Graphical performance monitoring tool • Separately licensed and supported by Sybase • Developed in partnership by Sybase and BMC Software, Inc. • Provides real-time information on system resource utilization, performance and tuning • User details (statistics, current SQL, query plan) • Database space usage and object statistics • Resource high-water marks • Data and procedure caches, disk devices, network IO, etc… • Identifies performance problems and provides tuning recommendations • User-defined alerts

38. DB Xray: Main Screen Potential problem alerts And hints Number of connections And high-water mark 24 Hour Graph Server Error Log Data Cache Efficiency Locks Database Space Usage

39. DB Xray: Users Screen User List SQL Text (and Query Plan) Statement Statistics Process Wait State History

40. Time for Your Comments… • What are your most important system management tasks? • What system management tools do you use? • What system management tools and features do you want to see? • We welcome your questions and comments… Thank you! peter.dorfman@sybase.com