Welcome to TechWave 2006 SQL Anywhere Technical Summit Tracks 1 and 2

Welcome to TechWave 2006
SQL Anywhere Technical Summit
Tracks 1 and 2
Monday, August 7, 2006
Tuesday August 8, 2006
iAnywhere at TechWave 2006
 Tech Support at TechWave 2006
• Meet with technical experts from Sybase iAnywhere and TeamSybase
• Bring your technical questions and get answers on the spot!
• Located off the Exhibit Hall on the fourth floor, Palace Ballroom Foyer
 Ask the iAnywhere Experts
• Drop in during support hours to have your questions answered by experts!
• Appointments are available to speak one-on-one with Senior Engineers
• Located across from the Tech Support area
 TechWave-To-Go AvantGo Channel on your handheld device
• Download the TechWave AvantGo channel for up-to-date details on
sessions, events, maps and more
• www.ianywhere.com/techwavetogo
• Visit the AvantGo Kiosk on the 3rd floor
iAnywhere at TechWave 2006
 Don’t miss the Feedback & Futures Session
• Tuesday, August 8th 4:30 pm – 6:00 pm
• Join us to wrap up of all summit sessions and provide feedback
• Save your questions for dedicated time with all session presenters
• Giveaways, prizes and more – your survey is your ballot!
 Tuesday Night Event 8:00 – 10:00pm
• Exclusive party for all 2-day Summit attendees
• Pick up your ticket at the AvantGo Kiosk or the Feedback session
• Must have your ticket + TechWave badge to enter the party
iAnywhere at TechWave 2006
 Reference Program
• Share your vision and innovation with your peers
• Come by the Information Desk at the Sybase booth to complete a
survey form -- all submissions will receive a gift!
 iAnywhere Developer Community
A one-stop source for technical information!
• Access to newsgroups, new betas and code samples
• Technical whitepapers, tips and online product documentation
• Excellent resources for commonly asked questions
• All available express bug fixes and patches
• Network with thousands of industry experts
SQL Anywhere 10
Dave Neudoerffer
VP Engineering, Sybase iAnywhere
[email protected]
Monday, August 7, 2006
1:00 pm – 2:30 pm
 SQL Anywhere 10 Enhancements
Innovative Data Movement
 Release Notes
The Data Explosion in
Frontline Environments
• The landscape for where data is managed is changing…
Enterprises are becoming more distributed, with global workforces
Small & medium-sized enterprises are more sophisticated users of IT
Large enterprises are increasingly mobilizing their workforces
Software implementation and management is increasingly outsourced
Packaged applications, hosting, 3rd party development
• Front lines evolving into a mission-critical computing environment
Server applications installed in an ISV’s customer sites
Workgroup applications installed in retail outlets, remote offices
ISV applications with databases hidden under the covers
Mobile applications used by field workers and sales forces
At the Frontlines – Scenarios
Small & Medium Enterprise
• Accounting
• Administration
Solution packages
• Phone systems
• Point of sale
Remote office
• Store management
• Inventory control
• Traveling sales
• Field service worker
• Inspector
Remote office
Mobile environments
SQL Anywhere is Unique
 Built from inception to enable front line applications
• Customers with server, desktop, mobile, remote office
 Over 15 years of engineering of data management
technologies for frontline environments
• Running on laptops and desktops while other enterprise DBs
were running in large data centers
• First synchronization solution for mobile environments
 Focus on making applications just as reliable, secure and
available as those running in an enterprise HQ
SQL Anywhere 10 Components
SQL Anywhere
Self-managing relational database with high reliability, high performance out
of the box, small footprint, and a full range of SQL features across a variety
of platforms.
Database system for small devices, including Palm OS, Pocket PC, and
Symbian, providing full transaction-processing support, a choice of
development models, and synchronization with enterprise data stores.
Synchronization technology for sharing information among relational
databases while maintaining the integrity of transactions across the entire
Application-to-application messaging solution that delivers secure and
assured message delivery for distributed and mobile users.
SQL Anywhere 10 Components
SQL Remote
Synchronization technology for synchronizing ASA remotes with ASA consolidated
databases. Message based – direct connection not required.
Sybase Central
Management tool for ASA, UltraLite, MobiLink and SQL Remote. Uses a tree structure for
easy manipulation and management of objects. Also includes a built-in stored procedure
Physical data modeller allows complete design of database schemas. It also provides
facilities to reverse engineer the schema of an existing database and to generate SQL to
create any schema you design.
A powerful and easy-to-use reporting tool that lets you query databases and create
sophisticated and effective custom reports of data.
Powerful control/component for .NET environments providing flexible broad range of data
SQL Anywhere 10
 Over 200 enhancements to both the Database Server and Database
Synchronization components in 4 significant areas
• Performance – Equipping customers to deal with data explosion
– Pushing SQL Anywhere to 100’s of gig’s and 1000’s of users
• Protection – Consequences of data loss are increasing
– Push data protection, by theft or system failure, to higher levels
• Productivity – Increasing developer productivity, improving ROI
– New tools and enhancements for development, analysis and debugging
• Innovative Data Movement - address increasing volume of data being
exchanged with larger numbers of systems
– New architecture design, integration and mobile Web Services tools
 SQL Anywhere 10 Enhancements
Innovative Data Movement
 Release Notes
Enhanced Data Management
Performance - customers better equipped to deal with an
explosion of data in frontline environments
Materialized views
Snapshot isolation
Intra-query parallelism
Parallel backups
New Index design
New Storage format
Column Compression
Checkpoint write behind
Java VM
New Catalog
Random access to BLOBs
Materialized Views
 Result of view stored as ordinary
base table in database
 Read-only
 Can build indices on it
 Can control frequency of refresh
 Used in optimization
 Enhanced query performance
 Improved concurrency
Usage Scenarios
When using large databases
Expensive queries on devices with limited processing power
When frequent queries result in repetitive aggregation (summing of data)
When access to up-to-the-moment data is not a requirement
Snapshot Isolation
Mechanism to eliminate “blocking”
of other readers when locks held
Keeps copy of original data while
it’s being changed for users who
want to read it in the interim
Also known as row versioning
Enabled by global database option
Can list currently active snapshots
Reduced locking
 Readers don’t block readers
 Readers don’t block writers
 Writers don’t block readers
 Better performance
Ensure consistency in reporting
Usage Scenarios
Cases where query “locks” are being held for long periods of time and
preventing completion of query results for other users (blocking)
 Long running batch jobs / reports while user queries being issued
 Ex. Accounting Balance Sheet report
 Statistical queries that “touch” a lot of rows
 Stored procedures or logic holding transactions open for longer periods
Intra-query Parallelism
Use of multiple processors or cores for
a single query
Parallel sequential scans, index
scans, joins
NOTE: Do not confuse with inter-query
parallelism (supported since SQL
Anywhere V6) which allows multiple
queries to execute in parallel
Can speed up CPU-bound queries
Single client apps can benefit since
server can exploit more than one CPU
for each request
Usage Scenarios
By default, engine automatically uses for benefit
Option available to control its usage
Parallel Backups
 Decreases overall time required for
 Only bound by performance of
slowest I/O component
Supported through server-side
image backups, dbbackup utility,
db_backup function
Independent I/O operations may
be overlapped with each other (vs.
performed sequentially)
Usage Scenarios
Especially useful for larger databases
Internal tests showed up to 3-fold improvement in performance on 10GB
Indexing Enhancements
 New indexes (to support snapshot
 Improved control over how indexes
are created
 Improvements to storing and
tracking index information
 Reduced contention on inserts
 Improved concurrency leads to
increased performance in multi-user
 Can index BLOB data -> faster
searching / retrieval
Usage Scenarios
If a particular column is searched frequently
Large tables with many rows
Index Consultant tool assists you in appropriate selection of indexes for
your database!
New Storage Format
 Redesigned persistent storage
 Allows new indexes, snapshot
 Better placement of data on the disk
 More efficient use of page storage
 Faster sequential scans, row
Usage Scenarios
All ;-)
Enhanced Data Management
Performance - customers better equipped to deal with an
explosion of data in frontline environments
Materialized views
Snapshot isolation
Intra-query parallelism
Parallel backups
New Index design
New Storage format
Column Compression
Checkpoint write behind
Java VM
New Catalog
Random access to BLOBs
 SQL Anywhere 10 Enhancements
Innovative Data Movement
 Release Notes
Enhanced Data Management
Protection - pushing data protection, whether from theft or system failure,
to even higher levels as consequences of data loss in frontline
environments grow in importance
 High Availability (Hot Failover)
• Database Mirroring
• Cluster agent
 FIPS 140-2 validated security
 Table level encryption
Resource governors
RSA encryption included
Kerberos authentication
New authorities added
 Server lockdown to disable
database server features or
groups of features
• Eg. local_call
High Availability – Database Mirroring
 Servers communicating with each
other to increase availability of DB
• Primary – current active server
• Mirror – current standby server
• Arbiter – determines primary
 Maintain separate copies of db / logs
 Clients only see (connect) to 1 server
 Protection of data against system
 Very easy to setup (built-in)
 No special hw or sw requirements
 Does not require particular OS
 Little impact on server performance
 Failover automatic, fast
Usage Scenarios
For highest levels of data protection against system failure (eg if you require
24 x 7 availability)
Use Mirroring if you want something built into the server with no
requirement for third-party software
High Availability – Database Mirroring
Arbiter determines who is the
current active server (also
known as primary server)
Arbiter Server
High Availability – Cluster Support
 Custom agent for Veritas Cluster
Service (VCS)
 Set of scripts integrated into VCS install
 One node goes down, automatic failover
to another node with no intervention
required (eg. shared disk)
 Cluster support existed before Jasper
but now more “SQL Anywhere-aware”
 Protection of data against system failure
 Can leverage existing Veritas Cluster
Server (VCS) installations
 More control over how/when failover for
database server occurs
 Can failover a server, or a database on
a server
Usage Scenarios
For highest levels of data protection against system failure (eg if you require
24 x 7 availability)
Use VCS Cluster Agent if you are already a VCS shop
FIPS 140-2 Validated Security
 Strong encryption components have
been FIPS 140-2 validated
 Extended to multiple platforms
beyond Windows including Linux
and Solaris
 FIPS 140-2 validation provided
without having to go through
lengthy and costly FIPS approval
process yourself
Usage Scenarios
Government agencies, financial institutions, healthcare providers requiring
FIPS 140-2 validation of any strong encryption components
Table Level Encryption
 Ability to encrypt individual tables
vs. the complete database
 Enabled through simple database
 Allows database users finer
granularity on security control
 Reduces overhead of encrypting /
decrypting non-sensitive data in
Usage Scenarios
Companies (eg. government agencies, financial institutions, healthcare
providers) requiring more granularity on strong encryption components
Enhanced Data Management
Protection - pushing data protection, whether from theft or system failure,
to even higher levels as consequences of data loss in frontline
environments grow in importance
 High Availability (Hot Failover)
• Database Mirroring
• Cluster agent
 FIPS 140-2 validated security
 Table level encryption
Resource governors
RSA encryption included
Kerberos authentication
New authorities added
 Server lockdown to disable
database server features or
groups of features
• Eg. local_call
 SQL Anywhere 10 Enhancements
Innovative Data Movement
 Release Notes
Enhanced Data Management
Productivity - enhanced developer productivity tools to decrease
implementation time of mission critical frontline applications
 Performance Analysis and
Application profiling
 Visual Studio Integration
 .NET 2.0 Support
 SA Deployment Wizard
 UltraLite Enhancements
 Global shared temporary tables
 Temporary procedures
 Task based administration
 Admin tool system wide search
 Product wide exception reporting
and statistics gathering
 Internationalization
• Extended multi-byte and
Unicode character sorting
• NCHAR data type
 Interfaces and Tools
• JDBC 3.0 support in iAnywhere
JDBC driver
• JConnect 6
• IPV6 support
 File/Directory data via external
Performance Analysis and
Application Profiling
 Robust tool for app and db
performance troubleshooting
 Problem determination for:
• Concurrency issues
• Pinpointing of queries with suboptimal performance
• Server machine capabilities
 Increased developer productivity
 Flexible
• “Off-line” analysis
• Remote machine analysis
 Decrease risk of performance
related issues before deployment
Usage Scenarios
Performance trouble-shooting tool aimed at 3 different classes of users:
• Beginner DBA (wizard-based)
• Experienced DBA (wizard and tracing database)
• Power-User DBA (attach tracing feature)
Performance Analysis and
Application Profiling
Enhanced Visual Studio 2005 Integration
 SQL Anywhere Explorer
• Create connections to SA dbs
• Browse contents of tables / columns
• Toolbar to launch SA tools
• Drag-and-drop operations to populate
forms with controls
 Supports VS 2005 and VS 2003
 Installed within VS by default
 VS developers continue to work in
development environment they are
comfortable with
 Leverage existing knowledge of Server
 SQL Anywhere developers have easy
access to tools within VS environment
Usage Scenarios
Large Visual Studio developer base with requirement to extend database
applications to the “front-lines”
 Looking for more powerful alternative to SS Mobile / Express
 May be part of heterogeneous replication environments
.NET 2.0 Support
 ADO.NET preferred data access
component for .NET apps
 .NET 2.0 released with VS2005
 Jasper includes native SA .NET 2.0
 SA supports .NET 2.0 and 1.1
 DataWindow.NET included
 Superior performance (native access, no
bridge technology required)
 Supports .NET 2.0 key features
 Broad Microsoft Windows support
(including Windows Mobile)
 DW.NET provides powerful extensions
to VS data grid support
Usage Scenarios
Existing .NET development shops
DataWindow.NET for programmers looking to easily incorporate complex
business rules and deliver sophisticated data presentation in their datadriven applications
Deployment Wizard
 Easy-to-use SA deployment tool – from
list, just “pick and choose” SA
components to deploy
 Generates a Microsoft Windows installer
file (.MSI)
 Provides alternative to other deployment
methods (silent, custom-built installs)
 Developer productivity – minimizes time
to embed SQL Anywhere into
Usage Scenarios
OEMs that need to embed SQL Anywhere
Environments that require hundreds / thousands of deployments
UltraLite Enhancements
Single file format across all platforms
Access from SA admin tools: DBISQL, Sybase Central
FIPs-approved security
Bigger database support (now 16 million rows per table)
Smaller footprint (as small as 300k) when not using SQL
Predicates on sync publications
• Symbian OS 7,8
– Series 60, 80, UIQ 2.0 and 2.1
– C++, AppForge development
• Windows Mobile 5
UltraLite Enhancements
 Extended Dynamic SQL and db management
• Distinct on aggregates
• constraint names, asc/desc in column lists for fkeys/unique
• Updatable cursors
• More properties accessible through functions
• Integrated schema (system tables)
 Complete DDL support
 AppForge Crossfire support
Enhanced Data Management
Productivity - enhanced developer productivity tools to decrease
implementation time of mission critical frontline applications
 Performance Analysis and
Application profiling
 Visual Studio Integration
 .NET 2.0 Support
 SA Deployment Wizard
 UltraLite Enhancements
 Global shared temporary tables
 Temporary procedures
 Task based administration
 Admin tool system wide search
 Product wide exception reporting and
statistics gathering
 Internationalization
• Extended multi-byte and Unicode
character sorting
• NCHAR data type
 Interfaces and Tools
• JDBC 3.0 support in iAnywhere
JDBC driver
• JConnect 6
• IPV6 support
 File/Directory data via external tables
 SQL Anywhere 10 Enhancements
Innovative Data Movement
 Release Notes
Innovative Data Movement
 Synchronization Modelling Tool
• “ Point-and-click” setup, testing and deployment of synchronization
 Flexibility and Performance
• Direct row handling for sync to virtually any data source
• Script based uploads for SA (in addition to log scanning)
• Global connection scripts
• Named parameters in SQL scripts
• Download only publications
• FIPS approved security option – extended platform support
 Messaging
• Message management and monitoring
• Performance improvements and additional API support
• Mobile Web services
Manageability – MobiLink Administration
 New plug-in for Sybase Central
 Intuitive wizard-based approach to
create model of entire sync solution
 Simulation and testing of sync scenarios
 Script generation
 Easy deployment and generation of
remote databases
 Developer productivity - ability to quickly
setup and maintain a MobiLink sync
environment against all supported
consolidated and remote databases
Usage Scenarios
Fast, easy development of initial synchronization system
New users to synchronization
Also useful for prototyping, proof-of-concepts, pilot programs that require
quick implementation
New MobiLink Administration
Direct Row Handling on Sync
 Synchronizing to data sources other
than supported consolidated dbs
 Implemented using new MobiLink server
APIs for Java and .NET
 Generates download data, model
passes data to MobiLink for delivery to
remote device
 Sync directly without requirement for
staging database
 Flexibility – increases # of potential data
sources (relational and non) that can
participate in ML sync
 Leverage existing knowledge
• .NET, Java, SQL
Usage Scenarios
Requirement to sync directly with enterprise systems including:
 ERP Systems (SAP, Siebel, PeopleSoft, …)
 Web Servers (IIS, Apache, …), Application Servers (WebLogic, WebSphere …)
 Legacy Systems (Mainframes, …), Data Warehouses (Sybase IQ, ….)
 Non-Standard Relational Database (MySQL, Excel, Access, …)
 Messaging Servers (MQ Series, Netscape Messaging Server, …) etc…
Performance Enhancements
 New threading model
 Initial handshake - decoupling of
authentication and data upload
 Data compression
 Schema caching
 Enhanced communication architecture
 Persistent connections
 Avoids large uploads if authentication
 Reduces amount of time and cost (when
charges apply) of moving data
 Eliminates redundant data transmission
as schema rarely changes
 Better throughput (performance), error
detection, compression, maintainability
Usage Scenarios
Any MobiLink implementations – small or large – can take advantage of
performance gains
Message Management and Monitoring
 Sybase Central plug-in
• Browse client/server msg store - examine
message properties, status, and history
• Forward, delete, or export messages
• View and modify message store
• View and create agent command files
• View and create transmission rules
 Better development-time debugging
 Better runtime exception
Usage Scenarios
Developers implementing and maintaining messaging solutions using
Performance and API Support
 Optimized message overhead
 Additional API support:
• Currently C++
• New SQL stored procedure
interface to messaging
• New Java API
 Reduction in amount of data sent (lower
cost, better performing applications)
 More efficient handling of interrupted
 Exposes messaging paradigm to
developers more comfortable with SQL /
Usage Scenarios
All developers implementing and maintaining messaging solutions can take
advantage of performance improvements
SQL shops who want to implement messaging
Mobile Web Services
 Built on top of QAnywhere
 Web services can be invoked from
mobile applications using traditional
tools and techniques
 Requests and responses handled
by QAnywhere
 Allows SOA applications to be
extended to mobile environments
 Asynchronous web services with
reliability and efficiency over nonpersistent connections
Usage Scenarios
Integration with backend systems that make use of web services
Using web services in an occasionally connected environment
Innovative Data Movement
 Synchronization Modelling Tool
• “ Point-and-click” setup, testing and deployment of synchronization
 Flexibility and Performance
• Direct row handling for sync to virtually any data source
• Script based uploads for SA (in addition to log scanning)
• Global connection scripts
• Named parameters in SQL scripts
• Download only publications
• FIPS approved security option – extended platform support
 Messaging
• Message management and monitoring
• Performance improvements and additional API support
• Mobile Web services
 SQL Anywhere 10 Enhancements
Innovative Data Movement
 Release Notes
SQL Anywhere 10 Platform Changes
 SQL Anywhere
• Solaris Sparc, HP-UX, AIX
– New 64-bit servers only
– 32-bit and 64-bit client support
• Mac support - Intel only (no current plans for PPC)
• Dropping support
– Windows 95/98/ME/NT 4.0
– Compaq TRU64
 UltraLite
• Adding Symbian Series 60, Series 80, UIQ 2.0 and 2.1
• Dropping MIPS support
MobiLink Changes
 Consolidated Databases Supported
SQL Anywhere 10
Sybase ASE 12.5.2, 12.5.3, 15
Microsoft SQL Server 2000, 2005
Oracle 9i, 10g
SQL Anywhere 10 Database Format Support
 Prior to V10, new SQL Anywhere releases supported
database formats of previous releases
 In V10, only V10-format databases are supported
• Decision made after consultation with many customers
• Enables us to offer new features such as Snapshot Isolation
• Enables many performance enhancements
 Important notes
Faster parallelized unload/reload utility will ease migration
Application level compatibility will not be affected
Older SQL Anywhere clients will continue to work
Synchronization compatibility will not be affected
 With version 10 of SQL Anywhere, we have raised the
bar once again in providing developers with an
underlying data management, synchronization, security,
and integration architecture that addresses the
complexities unique to frontline environments.
Upgrading to
SQL Anywhere 10
Joshua Savill
Product Support Analyst, Sybase iAnywhere
[email protected]
Monday, August 7, 2006
2:45 pm - 4:15 pm
Objectives for Presentation
 Overview of how to upgrade current databases and
applications to SQL Anywhere 10
 Demonstration and explanation of SQL Anywhere tools used
during the upgrade process
 Discuss behaviour changes and considerations during the
upgrade process
 Introduce new features to consider while upgrading
Agenda for Presentation
Overview of the Upgrade Process
Upgrading to SQL Anywhere 10
Upgrading the MobiLink Server
Upgrading MobiLink Clients
Upgrading UltraLite
Upgrading QAnywhere
Agenda for Presentation
 Overview of the Upgrade Process
• Components involved in upgrading
• Advantages of upgrading
• Upgrading best practices
Upgrading to SQL Anywhere 10
Upgrading the MobiLink Server
Upgrading MobiLink Clients
Upgrading UltraLite
Upgrading QAnywhere
Components to Upgrade
 Upgrading can involve multiple layers and components
• Components may depend or rely on other component being
• Front-end application
• Operating system
• Hardware
• SQL Anywhere software
• Database File ( Consolidated/Remote )
 MobiLink
 UltraLite
 QAnywhere
Information on What’s New in SQL Anywhere 10
 Could be upgrading from
version 5.X, 6.X, 7.X, 8.X, or
9.X to SQL Anywhere 10
 Each new release has new
features and behavior
 Full details provided in the
SQL Anywhere 10.0.0
 Additional resources
provided in the Technical
Support Lounge
Advantages of Upgrading
 Platform support
• Operating system support is most current with active supported
versions of SQL Anywhere
• Upgrading to move from an inactive or archived version onto a
supported version
( http://www.ianywhere.com/products/supported_platforms.html )
 Performance improvements
• Query processing, multi-processor, memory management,
improved communications
 New and enhanced features
• Hardware upgrades that can make use of new features
– High availability or clustering
Snapshot isolation
Materialized views
NCHAR data type support
Application profiling and diagnostic tracing
Upgrading Best Practices
 Review new behavior changes
 SQL statements in older versions may not be supported in SQL
Anywhere 10
• More restrictive due to ANSI compliance
• Coding to a bug
• Test upgrading to SQL Anywhere 10 before rolling out
 Always make a full backup and validate database before and
after the upgrade process
 Benchmark performance
• Check the PLAN() function for key queries
• Measure performance of application with the database
– Start with default database settings, then make changes to improve
performance, if necessary
Upgrading Best Practices Con’t
 Revisit performance with upgraded database server
• Review how some queries are written to determine efficiency
• Index Consultant for suggestions on schema improvements
 Test application
• Fix bugs
• Improvements in compliance to standards
• Could be unexpected side effects
– Application may have been coded to a bug
– Reliance on non-static features
E.g. Error message text
Upgrading Best Practices Con’t
 Test upgrade procedure in a development environment that is
identical to the production environment
 Verify command line and installation path if multiple versions of
SQL Anywhere are installed ( utilities exist in multiple versions )
 Review installation plan
 Upgrading SQL Remote, MobiLink Client, MobiLink Server and
• These components may have a dependency on SQL Anywhere (
formerly Adaptive Server Anywhere )
• UltraLite may have dependency on MobiLink
• Replicate or synchronize all databases before upgrading
 Upgrading QAnywhere Client
• MobiLink and SQL Anywhere upgrade is required
Agenda for Presentation
 Overview of the Upgrade Process
 Upgrading to SQL Anywhere 10
• New database file format
• New feature notes
• How to upgrade to SQL Anywhere 10
 Upgrading the MobiLink Server
 Upgrading MobiLink Clients
 Upgrading UltraLite
 Upgrading QAnywhere
Name Changes
 Adaptive Server Anywhere  SQL Anywhere
• Changes to some binary file names
– dbmlsrv9.exe  mlsrv10.exe
• Environment variable name changes
• Driver name changes
– .NET, OLE-DB, Perl DBD Provider/Driver renamed
• Default installation directory changed to SQL Anywhere 10
• Sample database name, data and schema changed ( demo.db )
• Unix/Linux configuration script asa_config.sh renamed to
New Database File Format
 Requires that database files be rebuilt using the Unload utility (
dbunload.exe )
 Old database file formats cannot be started on a SQL
Anywhere 10 engine
 New file format allows for new features that cannot be
implemented with the older format
• Snapshot isolation
• Improved Unicode data support
• BLOB performance enhancements
 More flexibility in adding new features
• Catalog, fixed-length columns, concurrency, international support
 Performance
• Support added for parallelism to improve performance
– Using multiple processors for processing a single query
New Database File Format
 Reduced code complexity
• More efficient code in the database server
– Increased stability as code line is more efficient
• Easier to maintain code line and ability to add new feature not
capable when supporting old database file formats
 The Upgrade utility ( dbupgrad.exe ) will not be shipped with
SQL Anywhere 10
 The Unload utility will be able to read older file formats
• The upgrade process will include a special engine for the Unload
utility to read older databases ( dbunlspt.exe )
Catalog and Reserve Word Changes
 Catalog changes
• Catalog names now start with an I ( ISYSFILE, ISYSOPTION )
• Benefits
– More Efficient/Faster to access
– Flexibility for updating catalog without affecting applications
– Compatibility views for previous catalog included
• Applications relying on following may need to change
 Catalog table names have changed, but views for backwards
compatibility Additional reserved words for SQL Anywhere 10
• Caution needs to be taken if reserved words are used in scripts
International Language Support
 Support for international languages has been enhanced
• International Components for Unicode ( ICU ) libraries introduced
• Unicode Collation Algorithm ( UCA ) added for sorting the entire
Unicode character set
– More CPU intensive, but can sort most languages in an appropriate
 New NCHAR support added
• Used to store Unicode character data up to 8191 characters in
• NVARCHAR ( 8191 characters maximum ) and LONG
NVARCHAR ( 2 GB maximum )
• Default collation is UCA
Database Collation Additions
 New database collation added
• Norwegian 1252NOR, default on Norwegian Windows systems
• UTF8BIN added for improved sorting of binary data
– UTF8 deprecated
 The Collation utility has been deprecated
• DBCollate function and dbcollat.exe removed
• Creation of custom collations is no longer supported
• Custom collations are preserved in the database when upgrading
 Affects on existing applications
• SORTKEY functionality is still supported
• COMPARE functionality is still supported
• Deployment of ICU library may require addition files in the
deployment list
ANSI Compliance
 SQL Anywhere 10 complies completely with the SQL-92-based
United States Federal Information Processing Standard
Publication ( FIPS PUB ) 127
 With minor exceptions, SQL Anywhere 10 complies with
ISO/ANSI SQL-2003 core specifications
 Substring() function now conforms to ISO/ANSI SQL-2003
• Negative or zero start offset is now treated as if the string is
padded on the left with non-characters
• Gives an error if negative length is provided
• ansi_substring option provided for compatibility
 Integer overflow now produces a SQLSTATE = 22003 overflow to conform to ISO/ANSI SQL-2003
• ansi_integer_overflow option provided for compatibility
ANSI Compliance
 string_rtruncation option default is changed to ON
• Data truncation by database engine now causes error by default
 Further ANSI compliance questions are welcomed in the
Technical Support Lounge
Java Virtual Machine Enhancements
 Initialized databases are always Java enabled
 iAnywhere Virtual Machine deprecated
• Previously SQL Anywhere shipped an iAnywhere Virtual Machine
• SQL Anywhere 10 uses an external VM
– External VM will run in its own process space
– Java logic not affected
– Can make use of any external Java VM
• Benefits
– Expected better performance
– Immediate support of new versions of Java
– No longer a separate key to install
• New options
– java_location ( no default )
– Java_main_userid ( default dba )
Connection and Security
 iAnywhere JDBC driver supports JDBC 3.0
 jConnect 5.5 and 6.0 are shipped with SQL Anywhere 10
• jConnect 4.5 is no longer supported
 Support for IPv6 on Windows XP, Windows 2003, and Linux
 -sc switch for C2 security is no longer supported
• Common Criteria is more comprehensive/up-to-date
• Named Pipes no longer supported
 RSA now included with SQL Anywhere 10
 Enhancements to FIPS support
Target Platform Support
 Platforms Targeted in SQL Anywhere 10
• Windows 2000, XP, 2003
– Remove support for Windows NT 4.0, 95, 98, ME
• Linux Kernel minimum 2.6.5
• Solaris 8, 9 and 10
– Solaris 7 not supported
• NetWare 5.1 (sp6), 6.0 (sp3) and 6.5
• Mac OS X PPC replaced in favour of Mac Intel
• Windows CE 3.0, 4.1, 4.2, Windows Mobile 5.0
• Support for 486 chipset removed
• 64-bit Database Servers on AIX, HPUX and Solaris in place of the
32-bit servers
How to Upgrade to SQL Anywhere 10
 Backup and validate the existing database
 Shutdown all other SQL Anywhere database servers
 The Unload utility or Unload Database wizard will rebuild the
database file format into SQL Anywhere 10
Upgrade Restrictions
 Recommend to disconnect from any earlier versions of the
database server and shut all other database servers
 Do not include the ENG, START, or LINKS connection
parameters in the dbunload connection string or enter the
Server Name and Start Line fields in Sybase Central
connection dialog box
 The Unload utility must be run on the same machine where the
database file is located as a shared memory connection is
 Environment variables SATMP and ASTMP must be set to the
same value, or not set at all
 Netware database need to be rebuilt on a Windows or Unix
Special Considerations
 Passwords in newly created SQL Anywhere 10 database are
case sensitive regardless of case-sensitivity of the database
 Rebuilding an existing database, case sensitivity of the
database is as follows
• If password was originally entered in case-insensitive database,
password remains case-insensitive
• If password was originally entered in case-sensitive database,
uppercase and mixed case passwords remain case-sensitive,
lowercase passwords become case-insensitive
• Newly created passwords are case-sensitive
 Default pages size is 4096 bytes
 Collations from old database are unloaded and reused unless
otherwise specified
Rebuilding into SQL Anywhere 10
 The Unload Database wizard in Sybase Central
 The Unload utility
 Ability to create a new database, replace the existing
database with the new file format, or unload the
database to flat files
 Required to rebuild the database into SQL Anywhere
The Unload Database Wizard in Sybase Central
 Starting the Unload Database wizard
• Start Sybase Central and click ‘Prepare a Version 9 or Earlier
Database for SQL Anywhere 10’ or select Tools  SQL Anywhere
10  Unload Database
• Demo on how to use the Unload Database wizard in Sybase
The Unload Utility ( dbunload.exe )
 Not all dbunload options apply when upgrading the database to
SQL Anywhere 10
 -c
• Specifies connection parameters for the source database
• For upgrading the database do not use the ENG, START, or
LINKS connection parameters
• The user ID supplied needs to be DBA authority
 -an <filename>
• Creates a new SQL Anywhere 10 database from the existing
database ( original database file is preserved )
• Initialization options are not changed during the rebuild
The Unload Utility Con’t
• When rebuild is complete, new dbspaces will have the extension
• This option does not preserve the offsets for replication or
 -ar <directory>
• Creates a new SQL Anywhere database to replace the existing
database ( original database file is not preserved )
• The directory name provided must contain the old transaction logs
for replication or synchronization
• When rebuild is complete, new dbspaces will have the extension
• This option preserves the offsets for replication or synchronization
• When rebuilding an encrypted database, the encryption key for the
original and new database must be the same
The Unload Utility Con’t
 -o <filename>
• Writes output messages to the supplied file name
 -r <[directory\]filename>
• Modify the name and directory of generated reload script file
 -v
• Displays verbose output during the database unload
– Name of the tables being unloaded
– Number of rows being unloaded
Upgrading to SQL Anywhere 10 Examples
Example # 1
dbunload -c “UID=dba;PWD=sql;DBF=asademo.db” -ar c:\temp
In current directory before running dbunload:
In current directory after running dbunload:
Upgrading to SQL Anywhere 10 Examples Con’t
Example # 2
dbunload -c “UID=dba;PWD=sql;DBF=asademo.db” -an
In current directory before running dbunload:
In newdb directory after running dbunload:
Upgrading to SQL Anywhere 10 Examples Con’t
In current directory after running dbunload:
Upgrading to SQL Anywhere 10 Examples Con’t
Example # 3
dbunload -c “UID=dba;PWD=sql;DBF=asademo.db” c:\backup
In the current directory before running dbunload:
In the current directory after running dbunload:
Upgrading to SQL Anywhere 10 Examples Con’t
dbinit newdb.db
dbisql -c “UID=dba;PWD=sql;DBF=newdb.db” reload.sql
In the current directory after running dbinit and dbisql:
The reload.sql and \backup\*.dat files can be removed
Agenda for Presentation
 Overview of the Upgrade Process
 Upgrading to SQL Anywhere 10
 Upgrading the MobiLink Server
• Consideration during upgrade
• Upgrading the consolidated database
• New feature notes
 Upgrading MobiLink Clients
 Upgrading UltraLite
 Upgrading QAnywhere
MobiLink Upgrade Process
 Dependent on whether the database file will be new or
 Often customers deploy a new application with a new
synchronization environment
 Typically upgrades are done in stages/phases
• Script versioning to accommodate an upgrade in stages/phases
 Important to have all remote databases synchronized before
starting the upgrade process
 Validate and backup the database before staring upgrade
 Test the upgrade process in an environment that is equivalent
to the production environment
Upgrading the Consolidated Database
 Setup script required to run on consolidated database before
being upgraded ( including SQL Anywhere 10 database )
• Appropriate upgrade script can be found in the
%SQLANY10%\MobiLink\upgrade\ directory
• Newly created consolidated database require the appropriate setup
script found in the %SQLANY10%\MobiLink\setup\ directory
– By default, MobiLink system tables are no longer created in the
database during initialization
 Ability to use a user other than the DBA to start the MobiLink
• Grant permission to the user on the necessary MobiLink system
MobiLink System Table Changes
 New MobiLink server system tables and schema
• ml_database, ml_column, and ml_qa_clients system tables added
• Altered schema to the ml_subscription, ml_user, ml_script,
ml_listening and ml_sis_sync_state tables
 New ways to clean up the MobiLink system tables on the
consolidated database
• ml_delete_sync_state_before system procedure added
– Purges information about obsolete remote databases
• ml_delete_sync_state system procedure added
– Deletes unused or unwanted synchronization state information
• ml_reset_sync_state system procedure added
– Resets synchronization state information
MobiLink Server Changes
 MobiLink server name has changed from dbmlsrv9 to mlsrv10
• Many MobiLink utilities have dropped the db and added ml if there
was no ml already in the name
• MobiLink DLLs have also changed naming conventions
 Compatibility with existing software
• SQL Anywhere 10 MobiLink server has the ability to synchronize
with version 8, 9 and 10 clients
• mlsrv10 -xo option needed to synchronize version 8 and 9 clients
 Options for setting cache size are deprecated ( -bc, -d, -dd, -u )
• Replaced with the mlsrv10 -cm option
Script Changes
 upload_cursor, new_row_cursor, old_row_cursor scripts are
deprecated and replaced with statement based uploads
 Statement based uploads are easier to understand for most
developers and offer better ODBC driver support
• Better performance with statement based scripts
 New MobiLink Create Synchronization Model wizard generates
Script generation with timestamps
Shadow table generation
Deployment features
GUI for column mappings
Automatic script generation with the -za and -ze options has been
Script Changes
 Unrecognized scripts will now cause synchronization to fail
• Unrecognized table-level or connection-level scripts
• Presence of cursor-based upload scripts
 Errors in upload or download scripts will now cause
synchronization to fail
 When a begin script is called, the corresponding end script is
called regardless of the success of synchronization
 Upload scripts are not called for a table when there is no data
to synchronize
 Global script version added
• Can define a script that is automatically used in all
synchronizations unless another script is specified
• Avoids duplicate connection level scripts
Script Changes
 Named script parameters can now be used
• Can use predefined named parameters or create user defined
named parameters
For Example:
Select * from worktable where
last_modified > { ml s.last_download }
and owner = { ml s.username };
Instead of the following:
Select * from worktable where
last_modified > ? and owner = ?
Script Changes
 Using SQL Anywhere 10 and Microsoft SQL Server 2005
consolidated databases, the isolation level should not be
changed in the begin_connection script
• Default isolation level for downloads is now snapshot
• Changes to the isolation level for downloads should be in the
begin_download script or using the new mlsrv10 -dsd option to
disable snapshot isolation
• Changing the isolation level in the begin_connection script is still
good practice for consolidated databases that do not use snapshot
Protocol Enhancements
 Changes to protocol names and options for network security
• -x https_fips changed to -x https(fips=y;...)
• -x rsa_tls changed to -x tls(tls_type=rsa;...)
– TCP/IP with TLS using RSA encryption
• -x rsa_tls_fips changed to -x tls(tls_type=rsa;fips=y;...)
– TCP/IP with TLS using RSA encryption and FIPS
• -x ecc_tls changed to -x tls(tls_type=ecc;...)
– TCP/IP with TLS using ECC encryption
• -x tcpip(security=...) changed to -x tcpip
• -x http(security=...) changed to -x http
Error Handling
 handle_error and handle_odbc_error events are more
• Called only when an ODBC error occurs while MobiLink is
processing an insert, update, or delete script during the upload
transaction, or is fetching download rows
• If an ODBC error occurs during other times of the synchronization,
the MobiLink server will call the report_error or report_odbc_error
script and abort the synchronization
Target Consolidated Platform Support
 Targeted Consolidated Database Vendors
• SQL Anywhere 10
• Sybase ASE 12.5.2, 12.5.3, 15.0
– Windows, Linux
• Microsoft SQL Server 2000, 2005
• Oracle 9i and 10g
– Linux, Windows, Solaris
• IBM DB2 UDB 8.1
– AIX, Linux, Windows
Suggested ODBC Drivers
 Sybase ASE and IBM DB2 ODBC Drivers are not shipped with
SQL Anywhere 10
• DB2 and ASE Version 12.5.3 and above include ODBC drivers,
which are the recommended drivers
 Oracle ODBC Drivers
• Data Direct driver ( Wire Protocol ) will be available for download
for the SQL Anywhere 10 GA release
• New iAnywhere Solutions 10 - Oracle ODBC Driver is being
developed and will be available after the SQL Anywhere 10 GA
Recommended Drivers:
( http://www.ianywhere.com/developer/technotes/odbc_mobilink.html )
 Review upload scripts and remove any upload cursor scripts
• Replace with statement based upload scripts
 Review database schema to determine if changes are needed
in the synchronization definitions
 Review the script versions and determine if there needs to be
• New script versions are recommended if synchronizing older client
( version 8 or 9 ) with version 10 clients
 If moving from version 7, review address parameter ordering
 Evaluate binary name changes and ensure the proper version
of the utilities are being used
 Test all scripts prior to upgrading to the new version
Execution Plan
 Stop the MobiLink server running on the consolidated database
 Backup and validate the consolidated database
 Identify the consolidated database type ( Oracle, IBM DB2,
SQL Anywhere, etc )
 Execute the appropriate upgrade script in the upgrade folder
• E.g. %SQLANY10%\MobiLink\upgrade\9.0.0\upgrade_ora.sql if
running an Oracle consolidated with version 9.0.0
• Note DB2 has special requirements
Upgrade the consolidated database
Make any script changes based on the preparation items
Assemble command line based on the preparation
Start the MobiLink server with the appropriate command line
Agenda for Presentation
Overview of the Upgrade Process
Upgrading to SQL Anywhere 10
Upgrading the MobiLink Server
Upgrading MobiLink Clients
• Consideration for upgrade
• New feature notes
• Upgrading remote databases
 Upgrading UltraLite
 Upgrading QAnywhere
MobiLink Clients Upgrade Process
 Upgrading the software
• Recommended to upgrade the MobiLink client and SQL Anywhere
database at the same time
– Remote database must be upgraded before running the new dbmlsync
utility for SQL Anywhere 10
 Upgrading the remote database
• Synchronize all outstanding data
• Rebuilding the database into SQL Anywhere 10
• Schema changes or significant database changes will require a
manual rebuild of the remote database
User Name and Remote IDs
 MobiLink user names and remote IDs
• Unique remote IDs are now created for each remote database
involved in synchronization
– MobiLink user name no longer needs to be unique
– MobiLink user name can now be considered a true user name for
Deprecated Behaviour
 Download error hooks deprecated
• sp_hook_dbmlsync_fatal_sql_error and
sp_hook_dbmlsync_sql_error have been removed
 Version 7 syntax and utilities have been deprecated
MobiLink client database extraction utility ( mlxtract )
 Review database schema to determine how to upgrade the
• No schema changes, the remote database can be upgraded
using the Unload utility
• Schema changes or significant database changes will require the
database be manually upgraded
 Review the script versions used by the remote database
• May need multiple script versions if planning to synchronize older
 Evaluate binary name changes and ensure the proper version
of the utilities are being used
 Test all scripts prior to upgrading to the new version
Execution Plan 1
 Creating a new remote database if schema change or
significant database changes are required
• Start with an existing SQL Anywhere database, or create a new
one and add the necessary tables
• Create one or more publications in the remote database
• Create the MobiLink users in the remote database
• Register users with the consolidated database
– mluser authentication utility
• Subscribe MobiLink users to one or more of the publications
Execution Plan 2
 Upgrading using the Unload utility to rebuild the database into
SQL Anywhere 10
• All data should to be synchronized before upgrading the database
• Backup and validate the database before upgrading
dbunload -c “UID=dba;PWD=sql;DBF=asademo.db” -ar c:\temp
 MobiLink remote databases require the -ar option to
automatically rebuild the database
• Transaction log offsets need to be preserved for synchronization
• Extremely important to backup and validate the database before
attempting an upgrade
– Automatic rebuild will replace the existing database file
Execution Plan 3
Upgrading using the Unload utility to rebuild the database into
SQL Anywhere 10 but manually preserving the transaction
log offset
Perform a successful synchronization, validate and backup the
remote database
Run the dbtran.exe utility to display the starting offset and ending
offset of the database transaction log
Rename the transaction log and ensure that it is not modified
during the unload process
Make note of the ending offset as it is required
Move the renamed log file to a secure location, such as an offline
Unload the database
Initialize a new database
Execution Plan 3 Con’t
Reload the data into the new database
Shut down the new database
Erase the transaction log of the new database
Run dblog.exe on the new database to reset the transaction log
– Use -z to specify the ending offset that was recorded
– Use -x to set the relative offset to zero
E.g. dblog -x 0 -z 137829 database-name.db
• Start dbmlsync, specifying the location of the original log file that
was moved to the secure location
• When the old transaction logs are no longer needed, set the
database option delete_old_logs
Upgrading SQL Remote
 SQL Anywhere 10 replication will work pre-SQL Anywhere 10
SQL Remote clients provided new data types are not
introduced in the system ( NCHAR )
 Recommend to migrate to MobiLink if using SQL Remote for
( http://www.ianywhere.com/whitepapers/migrate_to_ml.html )
 SQL Remote for ASE is EOL’d
( http://www.ianywhere.com/developer/eol/sql_remote_ase_160505.html )
 Any further questions regarding the SQL Remote upgrade can
be answered in the Technical Support Lounge
Agenda for Presentation
Overview of the Upgrade Process
Upgrading to SQL Anywhere 10
Upgrading the MobiLink Server
Upgrading MobiLink Clients
Upgrading UltraLite
• Considerations for upgrade
• New feature notes
• API changes
 Upgrading QAnywhere
UltraLite Upgrade Process
 Most UltraLite database upgrades occur with application
• Synchronize previous application
• Deploy new application and database
 Schema files are no longer used
Can deploy database file with application
Deploy database file through mlfiletransfer.exe
Call CreateDatabase function and execute DDL
Plan for non-synchronizing tables
Database creation function/method needs to be added
– Remove all schema upgrade code and schema file reference code
Advantages of UltraLite Upgrade
 Increased database limits
• Maximum number of rows in a table increased to 16 million
 Integrated schema
• UltraLite is now a standalone RDBMS and no longer requires a
separate schema file to define logical structure of the database
• UltraLite database can now be created directly
 Increased database performance and data integrity
 Indexes may utilize hashing
• Hash size can be specified on a per-index basis
• Improved performance for index lookups
 Checksum validation of the database added
 Extended BLOB support
• Ability to update, cast data types, and get the length of BLOBs
Advantages of UltraLite Upgrade Con’t
 Palm support for network_leave_open
• Palm devices can choose whether network connectivity stays open
after synchronization
 Configurable and increased default cache size for HotSync
conduit synchronization
• Default cache size ( on desktop ) for UltraLite conduit increased to
4 MB
• Improves synchronization time by reducing unnecessary file I/O
 Improved MobiLink client network layer
• Synchronization compression is available for all protocols
• Persistent connections
• Resumable synchronization
Advantages of UltraLite Upgrade Con’t
• Introduction of IPv6 support
• Improved error detection and debugging
 Set table order for synchronization
• Ability to specify table ordering to avoid referential integrity issues
during table upload
Upgrading UltraLite Database
 Upgrading database on desktop
• ulunloadold – exports XML from version 9 .udb or .usm
• ulload – creates a new database and loads in the XML
• Sybase Central Upgrade wizard can be used
 Creating new databases
• No longer a separate Unicode runtime
• Sybase Central wizard can be use to set the options
 Can now use graphical tools
• Sybase Central now supports UltraLite
• Interactive SQL now supports UltraLite
UltraLite Applications: C/C++ Static APIs
 IN
• Focus on standards-based interfaces going forward
 OUT - Static APIs
• ESQL applications can be easily migrated
– Simpler build process as SQL Anywhere is no longer required
– Review SQL used as UltraLite must now support it
– sqlpp -u generates UltraLite code
• Static C++ API based applications can migrate to the C++
– Sybase Central includes Migrate C++ Utility
UltraLite Applications: APIs
 Keeping pace with mobile application environment
 IN
• Symbian OS ( C++ and Crossfire )
• C/C++, m-Business Anywhere, or Crossfire are recommended for
cross-platform development
• Windows Mobile 5.0
• Palm 3.x
• Static Java, Native UltraLite for Java, and ActiveX interfaces
• .NET iAnywhere.UltraLite namespace in favor for
iAnywhere.Data.UltraLite namespace
 Synchronization code
• Secure synchronization streams are now all separate ( no security
parameters )
Name Changes
 Name changes for command-line utilities
• dbuleng9.exe has been renamed to uleng10.exe
• dbulstop.exe has been renamed to ulstop.exe
• dbcond9.exe has been renamed to ulcond10.exe
 Any further questions regarding the UltraLite upgrade can be
answered in the Technical Support Lounge
Agenda for Presentation
Overview of the Upgrade Process
Upgrading to SQL Anywhere 10
Upgrading the MobiLink Server
Upgrading MobiLink Clients
Upgrading UltraLite
Upgrading QAnywhere
• What is QAnywhere?
• Considerations for upgrade
What is QAnywhere?
 Application-to-application messaging system
• Store-and-forward nature of messaging
– Messages can be constructed even when destination application is not
reachable over the network
• Network-independent communication
 Provides messaging between mobile devices or between
mobile devices and the enterprise
 Permits seamless communication with other messaging
systems that have a JMS interface
• Integration with J2EE application
Upgrading QAnywhere
 The QAnywhere Message Server can communicate with older
QAnywhere message agents using the -xo option
 The QAnywhere Message Server upgrade process is identical
to that of the MobiLink Server
 To upgrade the QAnywhere Message Agent
• Required to upgrade the message store and this process is
identical to that to the MobiLink Client coupled with the -su switch
on the qaagent
• qaagent -sur is offered to simplified means to the above
 Transaction log is no longer used or maintained
 New option for setting up failover
 Persistent connections are introduced
Upgrading QAnywhere Con’t
 qaagent -port deprecated
• The -port option specified a port number on which QAnywhere
Agent listened for communications from the listener
 qaagent -la_port replaced
• The -la_port option has been replaced by the -lp option
 qaagent -push_notifications is renamed
• Now called -push
 getPropertyNames replaced
• The getPropertyNames function has been replaced with the
beginEnumPropertyNames, nextPropertyName, and
endEnumPropertyNames functions
 Any further questions regarding the QAnywhere upgrade can
be answered in the Technical Support Lounge
 Questions?
Application Development
with SQL Anywhere for .NET
Bill Hillis
Senior Manager - Engineering, Sybase iAnywhere
[email protected]
Monday, August 7, 2006
4:30 pm – 6:00 pm
 Introduction to .NET
 SQL Anywhere and ADO.NET
 Visual Studio Integration
 What’s new in SQL Anywhere 10
.NET: Definition
 .NET technology enables the creation and use of XML-based
applications, processes, and Web sites as services that share and
combine information and functionality with each other by design,
on any platform or smart device, to provide tailored solutions for
organizations and individual people.
 .NET is a comprehensive family of products, built on industry and
Internet standards, that provide for each aspect of developing
(tools), managing (servers), using (building block services and
smart clients) and experiencing (rich user experiences) Web
 http://www.microsoft.com/net/basics/faq.asp
.NET Framework
 Infrastructure for the overall .NET platform
 Common Language Runtime (CLR)
• Managed, protected application execution environment
• C#, Visual Basic.NET, C++, J#, …
 Common Class Libraries
• Windows Forms, ADO.NET, ASP.NET,…
 .NET Compact Framework
• Subset of .NET Framework for smart devices
• Part of Visual Studio.NET 2003
• Included on device with CE.NET (CE 4.1), add-on for older devices
.NET: Managed Code
 Code is written in desired language (C++, C#, VB.NET,
Pascal, etc.)
 Compiled into Microsoft Intermediate Language (MSIL)
 At runtime Common Language Runtime (CLR)
compiles the MSIL code and executes it
.NET Terms
 Namespace
• “A logical naming scheme for grouping related types”
• Analogous to Java packages
• iAnywhere.Data.SQLAnywhere.SAConnection 
iAnywhere.Data.SQLAnywhere is the namespace
 Assembly
• “A collection of one or more files that are versioned and deployed
as a unit”
• DLLs in .NET-land
• Unlike DLLs, .NET assemblies also include version
control/security information
 GAC (Global Assembly Cache)
• “A machine-wide code cache that stores assemblies specifically
installed to be shared by many applications on the computer”
What is ADO.NET?
 Microsoft’s latest data access API
 Implements System.Data namespace
 “Data providers” manage access to data stores
 Providers from Microsoft:
• System.Data.OleDb
• System.Data.Odbc
• System.Data.SQLClient
• System.Data.OracleClient
 “Managed provider”  “Data provider”
ADO.NET Interfaces
 Each data provider implements the following classes:
Connection – connects to data source
Command – executes commands
DataReader – forward-only, read-only access to data
DataAdapter – fills DataSet and handles updates
• Parameter – parameter to a Command object
• Transaction – provides commit/rollback functionality
• Error, Exception – collect error/warning messages
 Introduction to .NET
 SQL Anywhere and ADO.NET
 Visual Studio Integration
 What’s new in SQL Anywhere 10
SQL Anywhere Interfaces
 Open Client
 Perl
SQL Anywhere Data Provider
 Implements iAnywhere.Data.SQLAnywhere namespace
 Supports Windows (.NET framework) and Windows CE
(.NET compact framework)
 Also support 64-bit Windows
SQL Anywhere Data Provider in v8, v9
 The namespace was changed for SQL Anywhere 10
 Previously iAnywhere.Data.AsaClient namespace
 Available since (March 2003)
ADO.NET Data Providers For SQL Anywhere
 SQL Anywhere
SA ODBC Driver
Example using DataReader (C#)
SAConnection conn =
new SAConnection( “dsn=SQL Anywhere 10 Demo” );
SACommand cmd = new SACommand(
“select GivenName from Employees”, conn );
SADataReader reader = cmd.ExecuteReader();
while( reader.Read() ) {
str = reader.GetString( 0 );
Console.WriteLine( str );
Example using DataAdapter (VB.NET)
 Dim conn As New
 conn.ConnectionString = “dsn=SQL Anywhere 10 Demo"
 conn.Open()
 Dim ds As New DataSet()
 Dim da As New SADataAdapter
("select * from Employees", conn)
 da.Fill(ds, "Employees")
 DGEmployees.DataSource = ds
 DGEmployees.DataMember = "Employees"
Using the SQL Anywhere Data Provider
 Use Visual Studio.NET (VS.NET 2003 or 2005 preferred)
 Reference the provider in your project (required)
• Project menu, Add Reference
• In the .NET tab, find iAnywhere.Data.SQLAnywhere.dll
• If the provider is not listed, find it in %SQLANY10%\Assembly
 Reference provider in your code (optional)
• Allows you to use SQL Anywhere provider classes without
namespace prefix
• C#: using iAnywhere.Data.SQLAnywhere
• VB.NET: Imports iAnywhere.Data.SQLAnywhere
SQL Anywhere ADO.NET Data Provider
 Classes
• SAConnection
• SAError
• SAException
 Classes (continued)
 Enumerations
– SADbType
 Delegates
– SAInfoMessageEventHandler
– SARowUpdatedEventHandler
– SARowUpdatingEventHandler
ADO.NET Application Tasks
 Connecting
 Error handling
 Executing SQL
 Retrieving data
 Transactions
 Disconnected result sets
 SAConnection
• Represents a connection to a SQL Anywhere database
• Uses normal SA connection strings
• Optional use of event handlers (InfoMessage, StateChange)
Connection Example
 using iAnywhere.Data.SQLAnywhere;
 private SAConnection myConn;
 myConn = new iAnywhere.Data.SQLAnywhere.SAConnection();
myConn.ConnectionString =
"Data Source=SQL Anywhere 10 Demo;UID=DBA;PWD=sql”;
Connection Pooling
 ADO.NET spec dictates that connection pooling be
enabled by default
• Even on Windows CE!
• Disable in connection string: POOLING=false
 SQL Anywhere server may not auto-stop with
pooling enabled
• Connection object must be destroyed first
 myConn.ConnectionString =
 “POOLING=FALSE;Data Source=SQL Anywhere 10
Error Handling
 SAException
• Thrown by a failed statement (try/catch)
• Message parameter contains error message
• Errors property is a collection of SAError objects
 SAError
• More detailed SQL Anywhere-specific error information
• Message, NativeError, Source, SqlState
Errors and Exceptions Example
 try {
myConn = new SAConnection(
"Data Source=SQL Anywhere 10 Demo” );
 } catch( SAException ex ) {
ex.Errors[0].Source + " : " +
ex.Errors[0].Message + " (" +
ex.Errors[0].NativeError.ToString() + ")",
"Failed to connect" );
Executing SQL
 SACommand
• Represents a SQL statement or stored procedure that is
executed against a SQL Anywhere database
• Parameter property is a collection of SAParameter objects
 Multiple methods to execute your SQL
• ExecuteNonQuery (returns a row count)
• ExecuteReader (returns result set – DataReader)
• ExecuteScalar (returns a single value – column 1, row 1)
 Stored procedures
• Use the name of the procedure as the statement (no “call” or
• Set the CommandType property to StoredProcedure
SACommand Example
 SAConnection myConn;
 SACommand myCmd;
 int num_depts;
 myConn = new SAConnection(
 myConn.Open();
 myCmd = new SACommand(
"select count(*) from Department", myConn );
 num_depts = (int) myCmd.ExecuteScalar();
Retrieving Data
 SADataReader
• Read-only, forward-only result set from a query or stored
procedure (rows are fetched as needed)
• GetXXX methods to get column value as specific data types
• Read method moves to next row
 SADataAdapter
• Used to fill a DataSet; fetches all rows and closes cursor
• More on this later…
DataReader Example
SACommand cmd = new SACommand(
"select DepartmentId, DepartmentName from Departments",myConn);
SADataReader reader;
reader = cmd.ExecuteReader();
 while( reader.Read() ) {
int id = reader.GetInt32(0);
string name = reader.GetString(1);
MessageBox.Show( “Id: " + id + "\nName: " + name );
 }
 reader.Close();
DataReader Example – BLOBs
 SACommand cmd = new SACommand(
"select bitmap from images where id = 1", myConn);
 SADataReader reader = cmd.ExecuteReader();
 if( reader.Read() ) {
// get the length of the BLOB by passing a NULL buffer
long len = reader.GetBytes(0, 0, null, 0, 0);
byte bitmap[] = new byte[len];
// get the BLOB
reader.GetBytes(0, 0, bitmap, 0, (int)len);
 }
 reader.Close();
 SATransaction
• Represents a SQL transaction
• Returned by SAConnection.BeginTransaction()
• Commit, Rollback methods
• IsolationLevel property
 Autocommit is on by default in ADO.NET
• Disable by explicitly using an SATransaction
myTran = myConn.BeginTransaction();
myCmd = new SACommmand(
“call sp_update_some_data()”, myConn, myTran );
Disconnected Result Sets
 ADO.NET DataSet (System.Data.DataSet)
• Disconnected data access
• In-memory cache of data retrieved from database
• A collection of DataTables which consist of:
– DataRow (data)
– DataColumn (schema)
– DataRelation (relate DataTables via DataColumns)
• Can read/write data/schema as XML documents
• Works with data providers to load and modify data using the
provider’s DataAdapter
 SADataAdapter
• Represents a set of commands and a database connection used to
fill a DataSet and to update a database
• Fill method fetches all rows and closes cursor
• Update method applies changes to DataSet to database (beware
of ConcurrencyException!)
• SelectCommand, InsertCommand, UpdateCommand,
DeleteCommand properties
 SACommandBuilder
• Attached to an SADataAdapter
• Given a SELECT statement, generates corresponding
DataAdapter Example
 SADataAdapter da;
 SACommandBuilder cb;
 DataSet ds;
 da = new SADataAdapter("select * from product", conn );
 cb = new SACommandBuilder(da);
 ds = new DataSet();
 da.Fill(ds, "product");
 ...
 da.Update(ds, "product“ );
Application Deployment
 SQL Anywhere ADO.NET Provider has two files
• iAnywhere.Data.SQLAnywhere.dll (managed code)
• dbdata10.dll (native code)
 Both files must be deployed
• Version of files (i.e. build number) must match
• iAnywhere.Data.SQLAnywhere.dll will throw error if versions
don’t match
.NET Common Language Runtime
Updating the SQL Anywhere Provider
 At compile time, .NET compilers use strong name of
referenced assemblies
• Strong name includes both name AND version
• Microsoft’s attempt to eliminate “DLL hell”
 At run time, .NET looks for assemblies based on strong
 An application compiled with
iAnywhere.Data.SQLAnywhere version will
only run with version UNLESS you have a
publisher policy file in place
Publisher Policy Files
 Policy files redirect one version of an assembly to another
 Installed into GAC
 SQL Anywhere EBFs install policy files, for example:
• Application built against
• EBF applied to machine; upgrade to
– EBF installs policy file
– Requests for – redirected to
• %SQLANY10%\Assembly\V2\iAnywhere.Data.SQLAnywhere.dll.config
 Security is built-in to policy files
• Policy files cannot be compiled without private key assembly was signed
• Only iAnywhere can create policy files for iAnywhere assemblies
Example Policy File
<assemblyBinding xmlns="urn:schemas-microsoftcom:asm.v1">
Application Deployment: Win32
 Files can go anywhere, but typically in
 iAnywhere.Data.SQLAnywhere.dll
 policy.iAnywhere.Data.SQLAnywhere.dll
• Register with gacutil.exe (shipped with .NET)
 dbdata10.dll
 dblgen10.dll
• No registration required
Application Deployment: Windows CE
 One iAnywhere.Data.SQLAnywhere.dll for all CE platforms
But, separate versions for .NET 1.x and 2.0
Deploy to the Windows or application directory
Visual Studio.NET will deploy automatically
Make sure to use the CE version of the DLLs!
 Separate dbdata10.dll for each CE platform
• In %SQLANY10%\ce\xxx
• Can go in Windows directory or your application’s directory on the device
 Policy files are not supported by .NET Compact Framework
• .NET will automatically use newest version of
iAnywhere.Data.SQLAnywhere.dll that it finds
Introduction to .NET
SQL Anywhere and ADO.NET
Visual Studio Integration
What’s new in SQL Anywhere 10
Visual Studio Integration
 Added in 9.0.2 (November 2004)
 Described in white paper
• www.ianywhere.com/downloads
 Adds 3 icons to the toolbar
 Enables developers to be more
 SQL Anywhere Explorer added in
November 2005
Visual Studio Integration
 Demo
Introduction to .NET
SQL Anywhere and ADO.NET
Visual Studio Integration
What’s new in SQL Anywhere 10
What’s New in SQL Anywhere 10
 Namespace is renamed
 iAnywhere.Data.SQLAnywhere
 More integration with Visual Studio .NET
• Server Explorer integration
 ADO.NET 2.0
Server Explorer Integration
 Demo
 Released with Visual Studio 2005 in November
 New features
Provider factories
Data source enumeration
Connection string builder
Metadata schemas
Asynchronous commands
Snapshot isolation level
SQL Anywhere 10
Data Management and
Query Processing
Glenn Paulley
Senior Manager - Engineering, Sybase iAnywhere
[email protected]
Tuesday, August 8, 2006
8:00 am – 12:00 pm
Performance and Application
Profiling in SQL Anywhere 10
Dan Farrar
Sr. Engineer, Sybase iAnywhere
[email protected]
Tuesday August 8, 2006
1:00 pm – 2:30 pm
Plan of attack
 Overview
 Demonstration of automatic application profiling
 Details about application profiling
• Architecture
• User interface
 Demonstration of manual application profiling
• How to set it up
• What to look for
 Other methods of troubleshooting performance
 Questions?
Our problem
 Users are complaining about slowness on our
application – what should we do?
• We will use a “sabotaged” version of SalesSim
• Simulates the sales, shipping, and finance departments of a
• How do we use the new profiling features of SQL Anywhere
10 to find the boat anchors and restore/improve
Application profiling
 Combines in one tool most of the functionality provided
Request logging
Procedure profiling
Graphical plan capturing
Index consultant
Statistics monitoring
 Many usage scenarios:
• Debugging application logic
• Troubleshooting specific performance problem
• Leave running in background permanently
Application profiling wizard
 SA Plugin for Sybase Central includes the Application
Profiling Wizard
• Handle all details of setting up a profile and analyzing it
• Detect common problems automatically
Server and connection options
Application structure
• Make suggestions for improving your application
• Simplest way to use application profiling capabilities
Manual application profiling
 Using the application profiling tool manually allows for
more flexibility in controlling what data is analyzed
 High level steps:
Create and start a tracing database
Configure a tracing session
Run your application
Close (“detach”) the tracing session and save the trace data
Analyze the tracing session using the application profiling
mode in the SQL Anywhere plugin
Diagnostic tracing
 The Jasper engine includes new functionality to record
many types of database events:
SQL statements
Query execution plans
Blocked connections
Performance counters
 All types of data can be traced from sources both
internal and external to the server
Diagnostic architecture
 Traced data can go to any database
• To local database for ease of use
• To a non-local database for performance and to avoid bloat
• For best results, use a dedicated database
 Traced data stored in temporary tables
• New feature in Jasper: shared temporary tables
• No I/O overhead
• At end of logging session, data automatically saved to
permanent storage (base tables)
Diagnostic architecture
Specifying what to trace
 Trace only for a specified list of objects:
• Users
• Connections
• Procedures, triggers, functions, events
 Trace only under certain circumstances
• When a statement is “expensive”
• When a query differs from its estimated cost
• Every n milliseconds
 Limit volume of trace that is stored
• By disk space
• By length of time
Specifying what to trace
 You can mix and match these configurations and
change them on the fly
 For example:
• Trace all plans used by user ‘ALICE’
• Trace all statements used by procedures ‘PR1’ and ‘PR2’
• Trace all query plans in the database for queries that take
more than 20 seconds
 You can use the default tracing levels (low, medium,
high) as a template
• The tracing wizard in the SA Plugin will give you this choice
• Manually, using the sa_set_tracing_level() procedure
Controlling tracing manually
 sa_set_tracing_level()
| {SIZE nnn{MB|GB}}]
The sa_diagnostic_tracing_levels table
 Scope – what objects are we interested in?
• The whole database?
• A specific procedure, user, connection, or table?
 Type – what type of data are we interested in?
• SQL statements?
• Query plans?
• Information about blocks, deadlocks, or statistics
 Condition – under what conditions should we capture
this data?
• Only for expensive or misestimated queries?
Specifying what to trace
Controlling tracing with the SA Plugin
 The Tracing wizard is accessible by right-clicking on the
database object
• First, choose basic tracing level – it acts as a template
• Then, add or remove specific tracing entries
• Next, if you need to create a tracing database, create it and
start it on a database server
• Finally, specify where the trace is to be sent, and how much
data to store
Tracing databases
Saving a tracing session
 When finished tracing, the tracing session can be
stopped (detached):
• In the SA plugin, right-click the database object
• Manually, use the DETACH TRACING statement
 Detaching without saving will leave the data in the
temporary tables in the tracing database
• It can later be saved using the sa_save_trace_data()
 Detaching with save will permanently store the data
Analysis of traced data
 Can be viewed / queried in real time during trace
• Using DBISQL or custom scripts, issue queries against the
sa_tmp_diagnostic_* tables
 Once saved, a tracing session is analyzed using
Application Profiling mode in SA Plugin
• Provides multiple views of traced data
– Allows “drill-down” to see more detail about a specific entry
• Graphical correlation of performance statistics with
statements that were active at the time
• Automatic detection of common performance problems
Replay of server state
 Tracing captures optimizer state as queries are
• Captures cache contents, table sizes, option settings, etc.
• Allows server to recreate the optimizer state for queries in
the trace
– Not foolproof (because of changing statistics)
• Can be used to see the graphical plan used by the server
when only SQL text was traced
• Lets Index Consultant make higher-quality recommendations
• Works even if tracing sent to another database
– Allows Index Consultant to run offline on another server
Status panel
 If the trace was created as part of the Application
Profiling Wizard:
• Shows a summary of what was captured
• Performance recommendations are automatically generated
and available on the Recommendations panel
 If you created a tracing session manually:
• Shows all tracing sessions stored in a database
• Allows you to generate recommendations
Summary panel
 Gives a high-level view of SQL statements captured by the tracing
 “Similar” statements are grouped together
• For each statement a signature is computed
• For queries, insert, update, and delete statements, statements are
similar when they involve the same tables and columns
• Other statements are grouped by type (for example, all CREATE
TABLE statements are similar)
 From this view, you can determine which statements are most
expensive, either because:
• They are expensive individually, or
• They are cheap individually but executed many times
Details panel
 Shows low-level details about all SQL statements
captured in the trace
• Start time is the time the statement began execution
• Duration is the amount of time spent by the server
processing the request – all statements have a minimum
reported duration of 1ms
• For cursors, time the cursor was closed
• For compound statements, shows line number and
procedure name (if available)
• Text plan is always captured at optimization time
Statement details
 Right click on a statement to obtain more details about it
• User that executed it
• SQL error code, if any
• SQL text
– If the statement was captured as it was executed, the text will
be the original text
– If the statement was captured later (because it met some
condition), it will be reconstructed from the parse tree
– Reconstructed statements may not be identical to the original
Query details
 Right clicking on a query from the Details view will show
both statement and query details
 Query details include
Numbers of each type of fetch (forward, reverse, absolute)
Time to fetch first row
Text plan captured at execution time
Graphical plan representation
– May be the graphical plan at execution time
– May be a best guess at the execution plan, based on the
conditions in the server – compare a guessed graphical plan to
the text plan before relying on it
Blocking panel
 Shows connections that were blocked
• What statement was the connection executing when it was
• What connection blocked it
• How long did the block last
• Right click to see more details about either connection
involved in a block
Deadlock panel
 Shows deadlock events that were traced
• Displays a graphical representation of which connections
waited on each other
• Shows which connection was rolled back
• If available (that is, if tracing was attached to the local
database), shows the primary key of each row that was
blocked on
Statistics panel
 Shows a graphical representation of performance
counters captured
• Multiple statistics can be viewed, but only for one connection
at a time
• You are often interested in changes in a statistic (a “knee” in
a graph) – “Show Statements” button will filter the list of
statements in the Details panel to just those that fit on the
visible portion of the graph
Index consultant
 Index consultant can be invoked
• on the entire database
• on individual queries from the Details panel
 It is run automatically when application
recommendations are generated
• But it generates more details when run manually
Other tools for troubleshooting performance
 New properties for performance monitoring
 Almost all of the old methods of troubleshooting
performance are available in SQL Anywhere 10
• There are specific circumstances in which the legacy
methods may be the best approach
 Connection-level property – CPU time accumulated by
this connection
 Reasonably accurate most of the time – but still an
 Each CPU contributes to the counter – thus if two
connections are maxing out two CPUs for one second,
each will have an ApproximateCPUTime value of 1.0
 Use to determine what connection may need to be
dropped if the server is dragging (but be careful!)
 Best viewed from DBConsole
Request logging
 Stores SQL text of all requests
 Enable in two ways:
• -zr command line switch (with -zo to redirect output to a
• sa_server_option( ‘RequestLogging’, ‘all’ )
 Additional switches let you store data in a cyclical series
of files to limit the maximum captured data
 Probably deprecated in future releases
Procedure profiling
 View the times and execution counts of stored
 This feature is now part of application profiling mode in
the SA Plugin
 Can be used manually from DBISQL
• sa_server_option(‘ProcedureProfiling’,‘on’)
• Analyse with sa_procedure_profile_summary() and
sa_procedure_profile() procedures
 Useful for rapid tuning of procedures – it is easy to
change the procedure definitions on the fly
Improving Performance
with SQL Anywhere 10
Materialized Views
Anil Goel
Sr. Engineer, Sybase iAnywhere
[email protected]
Tuesday, August 8, 2006
3:00 pm – 4:30 pm