ASE 137: What’s new in ASE 12.5.1!

1. ASE 137: What’s new in ASE 12.5.1! Kannan Ananthanarayanan 925.236.4535 kannan@sybase.com

2. ASE 12.5.1 Release Theme REDUCE TOTAL COST OF OWNERSHIP ENABLE NEW APPLICATIONS • Operational Scalability & Performance • Ease-of-Use/Self-Management • High Availability • SQL compatibility & Internationalization • Information Integration • Security

3. ASE 12.5.1 – Operational Scalability & Performance • Dynamic Data Caches • Statement Caches • Dynamic Network Listeners • Parallel Checkpoints

4. ASE 12.5.1 – High Availability • Fast (Parallel) Recovery • Mount/Unmount of Databases • Veritas Cluster Support

5. ASE 12.5.1 – Ease of Use / Self Management • Job Scheduler • Automatic Database Extension • Management Tools improvements • ASE Plug-in Usability improvements • Unified Installation using Installshield

6. ASE 12.5.1 – SQL compatibility & Internationalization • Date and Time Data Type • Derived Tables • SQL standards compatibility • Unicode enhancements

7. ASE 12.5.1 – Security • LDAP User Authentication

8. ASE 12.5.1 – Information Integration • Native XML support • Web Services Support

9. ASE 12.5.1 Feature Details Dynamic Data Caches

10. Dynamic Data Caches • Cache configuration changes take effect at run-time • Benefits • Creating new caches and binding “hot” objects without a server reboot • Adding more memory to caches (including the default data cache) having low hit% without a server reboot • Deleting unused caches online, giving the ability to re-use the same memory

11. Dynamic Data Caches: Usages • No additional configuration parameters – No changes to sp_cacheconfig sp_cacheconfig 'transaction_cache', '10M’ go The change is completed. The option is dynamic and the SQL Server need not be rebooted for the change to take effect. sp_bindcache 'transaction_cache', orders_db, trans_tab go sp_cacheconfig 'transaction_cache', ‘20M’ go 10240 Kilobytes added successfully to named cache (transaction_cache). Change takes effect immediately. sp_cacheconfig ‘unused_cache', ‘0’ go The change is completed. The option is dynamic and the SQL Server need not be rebooted for the change to take effect.

12. Dynamic Data Cache: Limitations • Limitations • Shrinking a cache is not supported • Changing cache partitions is static • Changing replacement policy is static • To delete a cache, there should be no objects bound to it NOTE: For all caches other than default data cache, in order to change the static parameters, the cache can be deleted and re-created with the required parameter (shrinking, cache partitions, replacement policy)

13. Dynamic Data Caches: cache wizard • A new option to sp_sysmon “cache wizard” helps in • Identifying hot objects in a cache • Evaluating effectiveness of Large buffer pools • Sizing data caches • Evaluating effectiveness of APF

14. ASE 12.5.1 – Operational Scalability & Performance STATEMENT CACHE

15. ASE 12.5.1 - Statement Cache Description An ASE performance improvement for applications that • Use standard SQL (not stored procedures) • Execute the same statements more than once • Within one application • Across instances of the same application • Across different applications

16. ASE 12.5.1 - Statement Cache Qualifying Statements • Adaptive Server caches the following statements: • SELECT statements with at least one table • UPDATE and DELETE statements • INSERT statements with a sub SELECT instead of a VALUES clause • Adaptive Server does not cache the following: • Statements that reference temporary tables • SELECT INTO statements • Cursor SELECT statements • All other statements outside the above list of cachable statements

17. SQL statement to querytree Optimize and compile queryplan of ltwt procedure Execute queryplan Cache Text. Normalize qrytree. Create ltwt proc Transform statement to EXEC ltwt proc ASE 12.5.1 - Statement Cache Caching a New Statement Parse Normalize Compile Execute SQL in Cache? No

18. SQL statement to querytree SQL in Cache? Yes Execute queryplan Transform statement to EXEC ltwt proc ASE 12.5.1 - Statement Cache Executing a Cached Statement Parse Normalize Compile Execute

19. ASE 12.5.1 - Statement Cache Using the cache • The statement cache is off by default • The SA turns on caching with the server-wide configuration option “enable statement cache” • When the statement cache is enabled in Adaptive Server, individual sessions control its use by: • set statement_cache {on|off} • New monitor counters track cache hits and misses • Reported under “Procedure Cache Management” section of sp_sysmon output • New DBCC commands allow SA to • View contents of cache – dbcc prsqlcache • Purge cache – dbcc purgesqlcache

20. ASE 12.5.1 - Statement Cache Memory & Performance Impact • The statement cache uses the Procedure Cache memory pool • Each statement consumes as much memory as needed for the SQL text plus a small overhead for other identifying information • Adaptive Server saves the queryplan for a cached statement in the procedure cache, (same as for stored procedures) • For two concurrent uses of the same statement, there will exist one entry in the statement cache although there may be more than one queryplan in the procedure cache • When the queryplan for a cached statement ages out of the procedure cache, then if there remain no other copies of the queryplan in cache, Adaptive Server will drop the SQL text and descriptor from the statement cache and free its memory

21. ASE 12.5.1 - Statement Cache Details • The text of two statements must match exactly to share the same queryplan. In a future release Adaptive Server will • Ignore white space differences • Automatically parameterize constant values, e.g., when used as search arguments, for more extensive queryplan sharing • The Statement Cache will consume object descriptors and procedure cache memory • May need to configure more of these resources • Performance Improvements will vary depending on • The length of compilation time for a cached statement • The ratio of compilation to execution time • The ratio of cache hits to misses. Cache misses bear (small) overhead of creating the lightweight procedure

22. ASE 12.5.1 – Operational Scalability & Performance DYNAMIC NETWORK LISTENERS

23. ASE 12.5.1 – Dynamic Network Listener Description & Benefits DESCRIPTION • Allows flexible management of network listeners • Ability to START, STOP, SUSPEND and RESUME network listeners • Ability to get the status of the listeners BENEFITS • Help improve the performance by (de)activating the network listeners • Improve the availability (from application standpoint) DETAILS • Ability to start and stop network listeners at run time • Supports up to 32 network listeners • Every listener consumes one user connection • Each listener can service multiple engine

24. ASE 12.5.1 – Dynamic Network Listener Configuration & Examples • Sp_listener command can be used to manage the listeners • Sp_listener “command”, “server_name”, engine | remaining • Sp_listener “command”, “[protocol:]machine:port”, engine | remaining • STOP • Stop accepting connections; Closes the listener port • Does not remove the entries from the interfaces file • SUSPEND • Stop accepting new connections; Does NOT close the listener port • REMAINING OPTION • Applies the change only where applicable • Example • Sp_listener “start”, “ASE1251”, “3-6”, “remaining” • Even if one of the engines is unavailable, command will SUCCEED

25. ASE 12.5.1 – Operational Scalability & Performance MULTIPLE CHECKPOINT TASKS

26. ASE 12.5.1 – Multiple (Parallel) Checkpoint Tasks DESCRIPTION • A pool of checkpoint tasks work on the list of active databases BENEFITS • Improves the service interval of checkpoint for different databases • Helps speed up recovery and improve availability CONFIGURATION • Use the following to adjust the number of checkpoint tasks you need • Sp_configure “number of checkpoint tasks”, # • Default value is 1 • Maximum is 8

27. ASE 12.5.1 – High Availability • Fast (Parallel) Recovery • Mount/Unmount of Databases • Veritas Cluster Support

28. ASE 12.5.1 Feature Details FAST RECOVERY

29. ASE 12.5.1 – Fast Recovery PRIMARY BENEFITS • Improves the availability after a shutdown, crash or HA Failover DESCRIPTION • Enhances the performance of a single database recovery • Recovers multiple databases in parallel • Currently applies only for user databases • Take into account the database recovery order, if any specified CONFIGURATION • Sp_configure “max concurrently recovered db” • Determines the maximum number of databases to be recovered in parallel • Default value is 0 (self tuning based on I/O subsystem effectiveness) • Minimum value is 1 (ie., no parallelism – pre 12.5.1 behavior)

30. ASE 12.5.1 Feature Details MOUNT/UNMOUNT

31. ASE 12.5.1 – Mount/Unmount DESCRIPTION • Means to move and copy databases between ASE installations • Used to move databases by operation at device level • A file (know as manifest) is used to describe the mapping for Mount/Unmount BENEFITS • Improves the availability and helps in disaster recovery • Fastest way to create copy of a database for distribution • Fastest way to transport/distribute databases between ASE servers

32. ASE 12.5.1 – Mount/Unmount EXAMPLE/SYNTAX UNMOUNT database <database name list> to <manifest file> UNMOUNT database pubs2 to “/work/pubs2.mfs” MOUNT database all from <manifest file> [with listonly | using <name> = <value>] MOUNT database all from “/work/pub2.mfs DETAILS • SUID between source and target ASE must match • Mapping of path names is possible at mount time • System databases cannot be made portable (except sybsystemprocs) • Mount/Unmount commands are not allowed in a transaction • Mount database command is not allowed in a server configured for HA

33. ASE 12.5.1 – Mount/Unmount COPYING A DATABASE • Quiesce the database with manifest option • Copy the database devices and manifest file • Mount the database • Load transaction • Online the database MOVING A DATABASE • Unmount the database • Mount the database • Online the database

34. ASE 12.5.1 Feature Details VCS 3.5 support

35. ASE 12.5.1 – Veritas Cluster Agent BENEFITS • Improves the availability • Reduces the cost of ownership (HA setup) • Eliminates the need to write custom scripts • Makes it easy to certify with new VCS releases

36. ASE 12.5.1 – Veritas Cluster Agent DESCRIPTION • Provides a standalone agent to support Active/Active cluster support • Standalone agent will run with Veritas VCS 3.5 • Eliminate the need for Veritas Database Edition for HA • Manages resources of type HA ASE associated with ASE on Veritas Cluster Server (VCS) • It takes a resource online, brings a resource offline and monitors a resource to determine its state • Components • Resource definition file • Agent binary executable file including monitor function • Agent scripts • Agent installation tool

37. ASE 12.5.1 – Veritas Cluster Agent CONFIGURATION • Two homogenous, networked system with similar configurations in terms of system resources like CPU, memory, network connection • The two systems should be installed with Solaris 8 and VCS 3.5 • The two systems should be installed with Veritas Volume Manager 3.1 or later version to manage disks and create resources like DiskGroup and Volume • The two systems must have access to shared multi-host disks which store the databases for Adaptive Server • Use third-party vendor mirroring for media failure protection • Create a service group on each system. The service group for Adaptive Server should include such resources as DiskGroup, Volume, Mount, IP, NIC and HA ASE (new resource type for Adaptive Server). A figure describing a sample configuration is in the product manual

38. ASE 12.5.1 – Veritas Cluster Agent LIMITATIONS • Only support active-active HA configuration • Only available for ASE on Solaris. (The support for ASE on Linux is planned for future release) • Customized monitoring SQL script is not supported • Only provide HA support for Adaptive Server, no HA support for auxiliary servers such as backup server

39. ASE 12.5.1 – Ease of Use / Self Management • Job Scheduler • Automatic Database Extension • Management Tools improvements • ASE Plug-in Usability improvements • Unified Installation using Installshield

40. ASE 12.5.1 Feature Details Job Scheduler

41. ASE 12.5.1 – Job Scheduler Description • Principle Components • Internal ASE task • External process called the Job Scheduler (JS) Agent • sybmgmtdb database and stored procedures • Graphical user interface Benefits • Ability to schedule the activities that otherwise require manual interaction • Relieve DBA to focus on more important tasks

42. ASE 12.5.1 – Job Scheduler - Components • Internal Task • Determines when scheduled jobs should run • Starts the JS Agent and feeds JS Agent the necessary information • user name, password, and the id of the scheduled job • Creates historical record of jobs that are run • Job Scheduler Agent • Retrieves the job information from the sybmgmtdb database • Issues the job commands • Logs any result to sybmgmtdb database • Sybmgmtdb • Stores all the job, schedule and scheduled job information • Most access to Sybmgmtdb is through Stored Procedure • JS Task is the only one which access this database directly

43. ASE 12.5.1 – Job Scheduler - Components • Graphical User Interface • Assists user in creating and scheduling jobs • Assists in viewing job status and job history • Allows turning on and off of the ASE internal task • Templates • Tool to define parameterized tasks • Implemented as batch T-SQL commands for which parameter values can be provided. • Used for generating jobs, which can be scheduled to run at desired times.

44. Client GUI Command Line Stored Procedures Tables JS Agent Job Scheduler Internal Task Target ASE ASE Server Process ASE 12.5.1 – Job Scheduler - Components

45. Command Line Client GUI Stored Procedures ASE task wake-up JS_HISTORY JS_SCHEDULES JS_SCHEDULED JOBS JS_OUTPUT JS_CALLOUTS JS_JOBS ASE 12.5.1 – Job Scheduler - Catalogs

46. ASE 12.5.1 Feature Details Automatic Database Extension

47. ASE 12.5.1 – Automatic Database Extension DESCRIPTION • Out-of-the-box threshold action procedure to manage database spaces • Automatically add database devices on-demand • Automatically extend the database upon hitting predefined thresholds BENEFITS • Flexible database space management for DBAs • Improve the availability (from application standpoint) by automatically extending the space, instead of blocking on transactions

48. ASE 12.5.1 Feature Details Usability Enhancements • Sybase Central/ASE Plug-in • Installer Enhancements

49. ASE 12.5.1 – Sybase Central/ASE PLUG-IN • LDAP Directory Support • Quiesce Database Support

50. ASE 12.5.1 – Sybase Central/ASE PLUG-IN • Support for Mount/Unmount functionality