Transcript No Slide Title - SAP SQL Anywhere Forum

EM409
SQL Anywhere 8.0
New Feature Overview
Dave Neudoerffer
VP, Engineering
iAnywhere Solutions
[email protected]
Mike Paola
Group Product Manager
iAnywhere Solutions
[email protected]
Agenda
• Introduction
• SQL Anywhere 8.0 New Features and Enhancements
•
•
•
•
•
Adaptive Server Anywhere
Administration Tools
UltraLite
MobiLink
iAnywhere Mobile Manager
• Behavior Changes
• Upgrade Issues
• Summary
Sybase iAnywhere 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
UltraLite
MobiLink
iAnywhere 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, 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 TechWave Talks
• AM33: ASA Internals – Performance and Tuning
• EM407: Sound Application Development with ASA
ASA 7.0 vs 8.0
Complex Query Performance
SQL Anywhere Performance
80
70
60
50
ASA 7.0.2
Time (in minutes) 40
ASA 8.0
30
20
10
0
5
9
Query
1
13
17
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
EM406: Mobile Data Security
ASA Improvements
Java in the database
•
•
•
•
Java 2 support (JDK 1.3)
JDBC 2.0
Diagnostic procedure
Security manager
EM431: SQLJ1 and SQLJ2 – 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
UltraLite
MobiLink
iAnywhere Mobile Manager
• Behavior Changes
• Upgrade Issues
• Summary
UltraLite Enhancements
Runtime matches Vail execution engine
• Faster, tighter code
Security
• User authentication
• Database encryption
• Strong encryption
• Database obfuscation
• Secure synchronization additions
• Java UltraLite
• Windows CE x86
UltraLite 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
UltraLite Enhancements
Development Features
•
•
•
•
CodeWarrior 7 support
Improved handling of Palm segments
Analyzer hooks in reference database
@@identity supported
Applicable TechWave Talks
• AM35: Building UltraLite Applications
• EM405: Engineering Overview of UltraLite
Agenda
• Introduction
• SQL Anywhere 8.0 New Features and Enhancements
•
•
•
•
•
Adaptive Server Anywhere
Administration Tools
UltraLite
MobiLink
iAnywhere Mobile Manager
• Behavior Changes
• Upgrade Issues
• Summary
MobiLink Enhancements
Enhanced Reporting
•
•
•
•
•
Statistical scripts
Easier troubleshooting of synchronization problems
Log messages identify the worker thread
Verbose logging
Detailed error information
MobiLink Enhancements
Performance
•
•
•
•
•
•
•
•
•
Cursorless upload
Optional download acknowledgement
Buffered download stream
Bulk loading of connection and table scripts
MobiLink 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
MobiLink Enhancements
Flexibility
• Java synchronization scripts
• Synchronization of portions of data
• Enhanced client command line functionality
Ease of Use
• Automatic synchronization script generation
• MobiLink user authentication
• Example synchronization script generation
MobiLink Enhancements
Adaptability
•
•
•
•
Support for more RDBMS’s
Support for insert and delete conflict
Last download timestamp
Liveness detection in TCP/IP streams
MobiLink
Applicable TechWave Talks
•
•
•
•
•
EM401: Overview of MobiLink Synchronization
EM402: Advanced MobiLink Tips and Techniques
EM403: MobiLink Design Best Practices
EM404: New MobiLink Technology
EM419: MobiLink Advanced Scalability and Reliability
Agenda
• Introduction
• SQL Anywhere 8.0 New Features and Enhancements
•
•
•
•
•
Adaptive Server Anywhere
Administration Tools
UltraLite
MobiLink
iAnywhere Mobile Manager
• Behavior Changes
• Upgrade Issues
• Summary
iAnywhere 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
iAnywhere 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
iAnywhere 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
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
iAnywhere 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
UltraLite
MobiLink
iAnywhere 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
EM410: Upgrading Your SQL Anywhere
Applications
SQL Anywhere 8.0
Behavior Changes
UltraLite
• Palm 2.x no longer supported
• Analyzer uses external Java VM
SQL Anywhere 8.0
Behavior Changes
MobiLink
•
•
•
•
•
•
User authentication
MobiLink ASA client setup
MobiLink 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
UltraLite
MobiLink
iAnywhere Mobile Manager
• Behavior Changes
• Upgrade Issues
• Summary
Upgrade Issues
Compatibility with existing versions
Upgrading ASA
• Database only
• Database file format
MobiLink Upgrade directories
Rebuilding UltraLite applications
Agenda
• Introduction
• SQL Anywhere 8.0 New Features and Enhancements
•
•
•
•
•
Adaptive Server Anywhere
Administration Tools
UltraLite
MobiLink
iAnywhere Mobile Manager
• Behavior Changes
• Upgrade Issues
• Summary
Summary
SQL Anywhere Studio 8.0 offers:
• Performance, 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
• TechWave / Customer Visits
• Newsgroups (sybase.public.sqlanywhere.product_futures_discussion)
iAnywhere Solutions Highlights
• Ask the Experts - about Mobile & Wireless Solutions
-Mezzanine Level Room 15B
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:30pm Mobile & Wireless SDCC, Upper level, Room 11
• Keynote - Enabling m-Business Solutions
Wednesday 1:30 pm - 3:00 pm
• iAnywhere Solutions Developer Community
-Excellent resource for commonly asked questions, newsgroups, bug
fixes, newsletters, event listings - visit www.ianywhere.com/developer
EM409
SQL Anywhere 8.0
New Feature Overview
Dave Neudoerffer
VP, Engineering
iAnywhere Solutions
[email protected]
Mike Paola
Group Product Manager
iAnywhere Solutions
[email protected]