EM 409 SQL Anywhere 8 0 New Feature

EM 409 SQL Anywhere 8. 0 New Feature Overview Dave Neudoerffer VP, Engineering i. Anywhere Solutions [email protected] com Mike Paola Group Product Manager i. Anywhere Solutions [email protected] com

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

Sybase i. Anywhere Solutions Extending Corporate Information Delivery of enterprise information anywhere Synchronize information seamlessly from the enterprise to. . . workgroups desktops laptops handheld computing devices pagers intelligent appliances … and back again

SQL Anywhere 8. 0 Recall our general principles. . . • • • Good performance right out of the box Ease of use Interoperability/open Embeddability Cross platform support Being responsive to our customers

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

SQL Anywhere Improved documentation • • New context-sensitive help in all admin tools Re-organized book set Enhanced online books Improved samples

SQL Anywhere 8. 0 Adaptive Server Anywhere • Major enhancements to query execution engine and optimizer • Storage improvements • Comprehensive data encryption • Improved administration and troubleshooting tools Benefits: • Performance, performance! • Improved security and usability • Stronger base for future enhancements

ASA 8. 0 Performance Features New query execution model • Additional join methods: sort-merge, hybrid-hash, block-nested loop join (in addition to nested loop) • More efficient disk-based sorting algorithm • New grouping methods: hash, sort, index • User-defined function caching • More efficient subquery caching Provides a solid base for future enhancements such as: • INTERSECT, EXCEPT set operators • RECURSIVE UNION (bill-of-materials) • OLAP operators

ASA 8. 0 Performance Features Enhanced optimizer • • Chooses amongst suite of physical operators Cost-based index selection Significantly improved predicate analysis Histograms used for selectivity estimation • Data distribution evaluated during query processing; histograms are built and maintained automatically • Data retained across unload/reload • Improved estimates for range predicates, DISTINCT/Group-by cardinality • DBHIST utility for viewing histogram data • New cost model validated for 8. 0 query processing • Visual access plan displayer

Visual Access Plan Displayer Demo

ASA 8. 0 Performance Features Dynamic cache sizing on most platforms • Windows NT, 2000, XP, 95, 98, ME • All Unix platforms • Not CE, Netware Improved SMP support - better scalability • New cache page replacement algorithm reduced contention • New memory manager based on “Hoard” algorithm reduced contention and fragmentation

ASA 8. 0 New Features Improved Cursor Support • Cleaner semantics (more closely match ANSI and ODBC) • True implementation of keyset-driven cursors • Take advantage of new query optimization capabilities (eg. pre-fetch) Support for FULL OUTER JOIN

ASA 8. 0 New Features Store enhancements: • New Patricia trie indexes • Huge improvements for large key handling • Appropriate index automatically chosen • Improved file organization • Large block I/O, table page bit-maps for improved sequential scan performance • Checkpoint log moved to end of file reduces database file fragmentation and improves performance • Table or index reorganization on-the-fly • REORGANIZE TABLE and INDEX commands

ASA 8. 0 Performance Features Other Performance Improvements • Improved client/server buffer size negotiation • Fast database validation • Backup with log renaming now allowed with outstanding transactions • Communication compression Applicable Tech. Wave Talks • AM 33: ASA Internals – Performance and Tuning • EM 407: Sound Application Development with ASA

ASA 7. 0 vs 8. 0 Complex Query Performance

ASA – Security Enhancements Strong encryption of database files • AES candidate algorithm – Rijndael • CREATE DECRYPTED file statement Strong encryption of client/server communication • Use Certicom TLS secure TCP/IP stream • Server certificate authentication EM 406: Mobile Data Security

ASA Improvements Java in the database • • Java 2 support (JDK 1. 3) JDBC 2. 0 Diagnostic procedure Security manager EM 431: SQLJ 1 and SQLJ 2 – An Overview of the New Standards

ASA Improvements Miscellaneous • • Timezone adjustment CASE statements Viewing current license information SP_REMOTE_TABLES procedure Obtain remote table foreign key information xp_sendmail accepts messages of any length New embedded SQL function to obtain database properties

ASA Improvements Administration and Troubleshooting • Enhanced DBPING • Obtaining fragmentation statistics • System procedures • Used for tables and indexes • Improved debugger log • Increased limit on number of stored procedures

ASA Improvements Administration Tools • • Improved performance and load time Procedure profiling Visual access plan displayer Query Editor Editable data in ISQL / Sybase Central XML export using OUTPUT statement Separate server monitoring tool

Profiler Demo

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

Ultra. Lite Enhancements Runtime matches Vail execution engine • Faster, tighter code Security • User authentication • Database encryption • Strong encryption • Database obfuscation • Secure synchronization additions • Java Ultra. Lite • Windows CE x 86

Ultra. Lite Enhancements More Flexible Synchronization • • Priority synchronization Download-only synchronization option Client-specific data to control synchronization GLOBAL AUTOINCREMENT Database Management • Re-use of existing databases • Database defragmentation

Ultra. Lite Enhancements Development Features • • Code. Warrior 7 support Improved handling of Palm segments Analyzer hooks in reference database @@identity supported Applicable Tech. Wave Talks • AM 35: Building Ultra. Lite Applications • EM 405: Engineering Overview of Ultra. Lite

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

Mobi. Link Enhancements Enhanced Reporting • • • Statistical scripts Easier troubleshooting of synchronization problems Log messages identify the worker thread Verbose logging Detailed error information

Mobi. Link Enhancements Performance • • • Cursorless upload Optional download acknowledgement Buffered download stream Bulk loading of connection and table scripts Mobi. Link server shutdown enhancements Merged uploads and stored publication and connection information Connection timeout Setting maximum number of processors Option for maximum number of concurrent uploaders

Mobi. Link Enhancements Flexibility • Java synchronization scripts • Synchronization of portions of data • Enhanced client command line functionality Ease of Use • Automatic synchronization script generation • Mobi. Link user authentication • Example synchronization script generation

Mobi. Link Enhancements Adaptability • • Support for more RDBMS’s Support for insert and delete conflict Last download timestamp Liveness detection in TCP/IP streams

Mobi. Link Applicable Tech. Wave Talks • • • EM 401: Overview of Mobi. Link Synchronization EM 402: Advanced Mobi. Link Tips and Techniques EM 403: Mobi. Link Design Best Practices EM 404: New Mobi. Link Technology EM 419: Mobi. Link Advanced Scalability and Reliability

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

i. Anywhere Mobile Manager Features Automated software distribution • • Centralized task scheduling and task management Automated application installs and upgrades File collection and retrieval Remote command execution with built-in scripting tool and package editor Centralized task scheduling and management • E. g. schedule and automate virus protection updates Application self-healing and repair • State management • Imaging • Delta changes

i. Anywhere Mobile Manager Features Built-in remote control • Troubleshoot and establish real-time remote control session with users Asset management • • • Build complete reports of software & hardware inventory Software license metering Hardware and system level (WMI/DMI compliant) Software inventory Software license tracking Customizable inventory, custom fields

i. Anywhere Mobile Manager Features Heterogeneous devices • Windows 95/98/NT/2000 and CE, Palm • Cradle & direct connect PDA support • Single server, central control across all clients Variable connectivity • • Auto connection; bandwidth throttling; start & stop transfers Firewall & proxy friendly – HTTP / HTTPS / digital certificates protect remote user Relay servers cache downloads for distributed environment Small to large deployments • Installs in 10 min -- up and running in ½ day! • Manage 2500 users on a single command server • Clustering, replication options for unlimited scalability

i. Anywhere Mobile Manager Value Proposition Reduce • Cost in installation / configuration • Support calls • Down time Increase • Speed of deployment • Speed of update • Uptime Typical savings $1200 per device per year • Software distribution, help desk/troubleshooting, inventory, maintenance • Highest ROI with more than 200 devices, quick deployment of solution, high software distribution requirements

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

SQL Anywhere 8. 0 Behavior Changes Adaptive Server Anywhere • • User-supplied selectivity estimates Row ordering Access plan changes Cursor type changes OPEN CURSOR on insert not supported User-defined functions Number(*) function changes Trigger name scope changes

SQL Anywhere 8. 0 Behavior Changes ASA deprecated / unsupported features • • • WITH HASH SIZE usage Deprecated collations -e switch replaced with -ec NONE parameter deprecated Netbios port no longer supported EM 410: Upgrading Your SQL Anywhere Applications

SQL Anywhere 8. 0 Behavior Changes Ultra. Lite • Palm 2. x no longer supported • Analyzer uses external Java VM

SQL Anywhere 8. 0 Behavior Changes Mobi. Link • • • User authentication Mobi. Link ASA client setup Mobi. Link server shutdown Default setting for liveness detection in TCP/IP Hiding of dbmluser information Default log extension now. mls

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

Upgrade Issues Compatibility with existing versions Upgrading ASA • Database only • Database file format Mobi. Link Upgrade directories Rebuilding Ultra. Lite applications

Agenda • Introduction • SQL Anywhere 8. 0 New Features and Enhancements • • • Adaptive Server Anywhere Administration Tools Ultra. Lite Mobi. Link i. Anywhere Mobile Manager • Behavior Changes • Upgrade Issues • Summary

Summary SQL Anywhere Studio 8. 0 offers: • Performance, performance! • Security • Improved usability Customer Feedback • You played a big role in new feature determination – THANK YOU! • Forums for more feedback: • Support and Services • Tech. Wave / Customer Visits • Newsgroups (sybase. public. sqlanywhere. product_futures_discussion)

i. Anywhere Solutions Highlights • Ask the Experts - about Mobile & Wireless Solutions -Mezzanine Level Room 15 B Mon. /Tues. 11: 30 am - 3: 30 pm; Wed. 11: 30 - 1: 30; Thurs. 9 am - 12 noon -Exhibit Hall - Demo Center (truck) exhibit hall hours • SIG (Special Interest Group) - Tuesday 5: 30 pm Mobile & Wireless SDCC, Upper level, Room 11 • Keynote - Enabling m-Business Solutions Wednesday 1: 30 pm - 3: 00 pm • i. Anywhere Solutions Developer Community -Excellent resource for commonly asked questions, newsgroups, bug fixes, newsletters, event listings - visit www. ianywhere. com/developer

EM 409 SQL Anywhere 8. 0 New Feature Overview Dave Neudoerffer VP, Engineering i. Anywhere Solutions [email protected] com Mike Paola Group Product Manager i. Anywhere Solutions [email protected] com