M405 Upgrading Your SQL Anywhere Applications

1
M405 Upgrading Your SQL Anywhere Applications

• Robert Waywell
• Senior Product Support Consultant
• iAnywhere Solutions
• rwaywell_at_ianywhere.com

2
Objectives

• Focus on behavior and architectural changes
  rather than new features.
• Develop an understanding of necessary changes to
  migrate an existing SQL Anywhere Studio
  application from SQL Anywhere 5.5.x, Adaptive
  Server Anywhere 6.x or 7.x software to Version
  8.x
• Determine when and how to upgrade the database
  file.

3
Topics

• Introduction
• Upgrade Tools
• Client-Server Applications
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

4
Introduction

• Compatibility with existing software
• 8.0 database servers can run older databases (eg.
  5.5.x, 6.x, 7.x) that have not been upgraded
• For 6.x or later, can mix client and server
  software from different versions
• Benefits of many new features can be obtained
  without upgrading the database file
• eg. multi-processor support, improved network
  communications,dynamic caching
• Even if the database file is not upgraded,
  software upgrades can introduce new behaviors

5
Introduction

• Upgrading can involve multiple layers and
  components
• Front-end Application
• Operating System
• Hardware
• SQL Anywhere Studio Software
• Database File

6
Reasons to Upgrade Software

• Performance Improvements
• Platform Support
• New OS versions are only supported by current
  versions of released software
• Improved Stability
• Bug fixes are made in current versions, only back
  ported to active versions
• Enhancement of utility programs eg. dbvalid
• Move from inactive or archived versions
• Support matrix located at
• www.sybase.com/detail?id1002288

7
Reasons to Upgrade the Database File

• Performance Improvements
• Separate Primary Key/Foreign Key structures
• New index structures for long indexes
• Enhanced Statistics
• Modify Page Size
• If database has grown, a larger page size may be
  more appropriate
• Functionality requires it
• eg. SQL Remote required changes to system
  information between 6.x and 7.0
• Take advantage of new features that rely on new
  database options or changes to system tables
• eg. Scheduling and event handling

8
Upgrade Practices

• Check behavior changes
• Always make a full backup before starting an
  upgrade
• Benchmark performance
• Check the PLAN() function for key queries
• Measure performance of your standard tests
• If you will be using dbupgrade ensure that you
  run dbvalid.
• Should be part of your regular backup strategy

9
Upgrade Practices (cont)

• Test application
• Many intentional behavior changes
• Fix bugs
• Improve compliance to standards
• Return Warnings that were previously missed
• Could be unexpected side effects
• Application may have been coded to a bug
• Reliance on non-static features
• e.g. Error message text, assertion error
• Test upgrade procedure
• Development environment first before rolling out
  in production

10
Upgrade Practices (cont)

• Pay attention to system path when using
  command-line tools
• e.g. Which version of dbinit is being used?
• Between 5.5.x and 6.0, moved from a proprietary
  installation program to using InstallShield
• InstallShield has a silent install feature that
  lets you record a response file to replay.
• Also provide template InstallShield scripts to
  install common components.

11
Topics

• Introduction
• Upgrade Tools
• DBUpgrade
• DBUnload
• Client-Server Applications
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

12
DBUpgrade

• What it does
• Updates the system tables, provides new database
  options, adds or modifies system procedures
• This process is not recoverable
• You must
• Perform a full backup before starting the upgrade
• Perform a full backup after completing the
  upgrade
• When to use it
• When upgrading the database in-place
• When changes to the physical file format are not
  important
• Possibly when upgrading for platform support

13
DBUpgrade (cont)

• Limitations
• Does not modify the physical file format
• No benefit from
• Separate Primary Key Foreign Key structure.
• Enhanced Index Structures
• Improved statistics recording
• Does not validate the data
• Only modifies the necessary system objects

14
DBUnload

• What it does
• Unloads data and schema, generating a SQL script
  (reload.sql) and .dat files.
• When to use it
• When you want to take advantage of changes to the
  physical data store
• Process of unloading and reloading the database
  both validates and defragments the data
• Creating and loading the new database
• Completed by reading the SQL script via ISQL

15
DBUnload - Limitations

• Reloading views that require qualified table
  names
• View dependencies
• Database initialization
• Certain characteristics not covered by DBINFO
  (eg. jConnect support)
• Default collation sequences changed
• SQL Remote
• Need to maintain log offset information
• New system users added in V6.0

16
Topics

• Introduction
• Upgrade Tools
• Client-Server Applications
• Architecture
• Upgrading
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

17
Architecture

• At this point, lets look at how the file
  components have changed between versions and how
  this impacts both the files required for
  deployment and the way these components interact.
• Version 5.5.x
• Version 6.x
• Version 7.x
• Version 8.x

18
Version 5.5.x

• Standalone Engine

19
Version 5.5.x Key Points

• ODBC driver works through the ESQL library
• In this context, ODBC was not a native
  interface to the database engine
• DBClient is a separate process
• This model was a good choice historically when
  inter-process communication was relatively
  inexpensive
• The language dll is used by several different
  components and contains language specific
  resource strings
• Maintaining these strings in a separate component
  facilitates internationalization

20
Version 6.x

• Personal Server (Standalone Engine)

21
Version 6.x What Changed?

• ODBC is now a native interface, that communicates
  directly with the database engine independently
  of the ESQL library
• One layer of translation has been omitted
• Network communications are now handled through a
  dll rather than through a separate process
• No separate dbclient executable
• More efficient than 5.5.x model

22
Version 6.x What Changed? (cont.)

• The internal client-server communications
  protocol changed
• 5.5.x client cannot communicate directly with the
  6.x server
• A client-side compatibility library is available
  that allows 5.5.x client applications to
  communicate with a 6.x server
• This library replaces the original dbl50?.dll
• DDE and HLI are no longer supported for local
  machine connections

23
Version 7.x

• Personal Server (Standalone Engine)

24
Version 7.x What Changed?

• The dbport6.dll has been eliminated
• Functionality rolled into the dbodbc7.dll and
  dblib7.dll
• The exception is that IPX (as opposed to SPX)
  support is still maintained in a separate dll
  since it has been deprecated
• This step simplifies the EBF process for client
  applications by eliminating one file from the
  deployment list

25
Version 8.x

• Personal Server (Standalone Engine)

26
Version 8.x What Changed?

• No significant changes in the file components
  required to deploy an 8.x client-server
  application relative to a 7.x client-server
  application
• The previous slides have highlighted the
  differences between major versions and the core
  files required for deployment
• Additional files would be required to support
  External Function Calls, Java in the database, NT
  Performance monitor

27
Topics

• Introduction
• Upgrade Tools
• Client-Server Applications
• Architecture
• Upgrading
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

28
Upgrading V5 Embedded SQL Apps

• Install current version of software at each
  client machine
• Use compatibility library only if you have V5
  clients that need to communicate with a V8 server
• Create a new connection description
• Capture dbclient command-line information
• ODBC data source use START parameter
• Batch file move parameters into Commlinks
  connection parameter
• Hard-wired connection string alter source of
  application and recompile
• Upgrade database server
• Use new connection description at each client

29
Upgrading V5 Embedded SQL Apps
30
Upgrading V5 ODBC Apps

• Install current version of software
• Create a Version 8 ODBC source
• Changes made depend on connection parameters used
  in data source
• See
• Start Parameters and the compatibility library
• Capturing dbclient command-line information
• Use new data source
• Additional client-server steps same as ESQL

31
Upgrading V5 ODBC Applications
8
32
Centralized Upgrading of Applications

• iAnywhere Mobile Manager
• Remote command execution
• Built-in scripting
• Distribution of files
• Schedule upgrades
• M417 Managing Your Mobile Devices and
  Applications
• AM34 iAnywhere Manage Anywhere Administration
• Upgrading SQL Anywhere Studio
• Upgrading server-side database
• Upgrading client applications

33
Upgrading SQL Anywhere Studio

• Distribute custom installation image to users

• User input
• Setup program starts once install image is
  downloaded to user
• Silent install
• Setup program runs in background

34
Upgrading Server-Side Database

• Scripts to automatically validate and backup
  database

• Automatically unload data and schema and reload
  into new ASA 8.0 database

Execute ltASAdirgt\win32\dbunload.exe -c
"dbfsademo.dbstartdbeng8uiddbapwdsql"
Unload Execute ltASAdirgt\win32\dbinit.exe
asademo.db Execute ltASAdirgt\win32\dbisql.exe -c
"asademo.dbstartdbeng8uiddbapwdsql" -q
reload.sql
35
Upgrading Client Applications

• Schedule upgrade at any time
• Distribute new install files to users
• Copy from package to lttemp pathgt\VQinst
• Execute lttemp pathgt\VQinst\setup.exe
• Delete File lttemp pathgt\VQinst
• Automatically remove old application and replace
  by new one
• Program files and registry entries

36
Upgrading Client Applications

• Automatically update ODBC settings

37
Upgrading Client Applications

• Automatically update Windows shortcuts

38
Topics

• Introduction
• Upgrade Tools
• Client-Server Applications
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

39
SQL Remote Applications

• DBXtract uses internal reload(7.0.0)
• .dat file path is now relative to the server
• Need to specify xx to get the historic behavior
• Message link parameters stored in the database
  (6.0.3)
• If not found in the database, will still be read
  from the registry, .ini file, or environment
  variable
• Message format changed (6.0.0)
• Compression of the messages was introduced in
  6.0.0
• To continue to use messages with the 5.5.x
  format, you need to set the database option
  Compression -1
• SET OPTION public.Compression -1

40
Topics

• Introduction
• Upgrade Tools
• Client-Server Applications
• Architecture
• Upgrading
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

41
MobiLink Applications Upgrade

• Upgrade scripts are provided under
• asany8\MobiLink\upgrade\6.0.x
• asany8\MobiLink\upgrade\7.0.x
• Readme.txt file in that directory provides
  instructions on using the scripts
• Existing MobiLink applications do not need to be
  upgraded to communicate with a newer version of
  the Mobilink server
• Recommended ODBC Drivers for MobiLink
• http//my.sybase.com/detail?id1011880

42
Topics

• Introduction
• Upgrade Tools
• Client-Server Applications
• Architecture
• Upgrading
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

43
UltraLite Applications

• All UltraLite applications need to be recompiled
  as part of the upgrade process.
• UltraLite applications do NOT need to be
  recompiled to upgrade the MobiLink server.
• If you upgrade the Hotsync conduit, then you must
  recompile the UltraLite application
• This applies to EBFs as well

44
UltraLite Applications (8.0)

• Running the UltraLite generator automatically
  upgrades the analyzer components in the reference
  database.
• Version 8.0 UltraLite applications require a
  MobiLink server of version 8.0 or above.

45
Topics

• Introduction
• Upgrade Tools
• Client-Server Applications
• Architecture
• Upgrading
• SQL Remote Applications
• MobiLink Applications
• UltraLite Applications
• Question Period
• Behavior Changes

46
Summary

• New versions offer significant enhancements
• Upgrading requires
• Checking behavior changes
• Backups
• Testing
• Required files listing
• 7.0 ASA Users Guide Chapter 28
• 8.0 ASA Programming Guide Chapter 12
• Use tools like iAnywhere Mobile Manager to
  facilitate upgrades

47
SQL Anywhere Studio 8.0 Resources

• iAnywhere web site
• www.ianywhere.com
• iAnywhere Developer Community
• www.ianywhere.com/developer

48
Upgrading SQL Anywhere Studio Applications

• Additional References Behavior Changes

49
Behavior Changes

• Outline major behavior changes
• Version 6.x
• Version 7.x
• Version 8.0
• For comprehensive list, check out the Online
  Documentation Home Page
• http//www.sybase.com/detail_list?id4296

50
Behavior Changes

• Behavior Changes in 6.0
• Behavior Changes in 7.0
• Behavior Changes in 8.0

51
Behavior Changes in 6.0

• Thread_count option ignored
• This option specified the number of internal
  tasks to be used by the engine to process queries
• Only relevant at the engine/server level
• Specified by gn switch on the server
• Licensing
• As of 6.0, the server limits the total number of
  client connections for both Per Seat and
  Concurrent User licenses
• DBLic utility lets you re-license the database
  server

52
Behavior Changes in 6.0 (cont)

• Max_statement_count
• Introduced in 6.0
• Limits the number of prepared statements per
  connection
• Default is 50, to disable it set it to 0
• Max_cursor_count
• Introduced in 6.0
• Limits the number of cursors in use by a given
  connection
• Default is 50, to disable it set it to 0

53
Behavior Changes in 6.0 (cont)

• TCPIP Connections
• In 5.5.x actually used UDP, not TCP
• 6.0 uses UDP for broadcasts, but uses TCP once
  the connection is established
• May require changes to the configuration of
  firewalls, routers and gateways
• Default Port Number Changed
• was port 1498 in 5.5.x, 2638 in 6.0 and above
• Database Starting Permissions
• controlled by the gd switch
• Default is different between the Personal Server
  and Network Server
• To get the same behavior as in 5.5.x need to
  specify gd all

54
Behavior Changes in 6.0 (cont)

• DBTOOL statement dropped
• provided access to the database utilities
• DBBackup
• DBValid
• In 5.5.x this functionality was available through
  ISQL
• As of 6.0, functionality was incorporated in the
  database engine
• Requires changes to any scripts that previously
  used the DBTOOL statement

55
Behavior Changes in 6.0 (cont)

• Comments
• unload with the same version that you will be
  running the reload
• Stored procedures may need to change
• Percent_as_comment database option
• Default is ON to give historic behavior
• Nearest_century
• Default changed from 0 to 50
• Anything gt 50 assumed to be 19xx
• Anything lt 50 assumed to be 20xx

56
Behavior Changes in 6.0 - Summary

• To make a 6.0 database/engine look like a 5.5.x
  database/engine
• Engine Switches
• -gn ltnumgt to specify internal thread count
• -gd all to set database starting permissions
• Database Options
• SET OPTION public.Max_statement_count 0
• SET OPTION public.Max_cursor_count 0
• SET OPTION public.Percent_as_comment On
• SET OPTION public.Nearest_century 0
• Some differences are not configurable.

57
Behavior Changes

• Behavior Changes in 6.0
• Behavior Changes in 7.0
• Behavior Changes in 8.0

58
Behavior Changes From ASA 6 to ASA 7

• Deprecated and Unsupported Features
• Behavior Changes

59
Deprecated and Unsupported Features in 7.0

• Win 3.x and WinCE 2.0 no longer supported
• IPX protocol deprecated
• SPX is the preferred alternative
• NUMBER function deprecated
• A modified, safer version of this function is
  provided in V8.0
• Deprecated network communication parameters
• Broadcast and CommAutoStop no longer have any
  effect
• No DBClient compatibility executable

60
Behavior Changes in 7.0

• Admin tools now Java-based (Sybase Central and
  DBISQL)
• Allows for use on Windows and Unix platforms
• Some ISQL input/output formats dropped, Excel
  added
• Server name space changes
• Starting with 7.0, a client connection is able to
  find a server by name even if it is running on a
  port other that the default port of 2638
• The side effect of this change is that you can no
  longer start 2 servers using the same name, but
  on different ports within the same visible
  network.

61
Behavior Changes in 7.0 (cont.)

• No separate network ports library
• The functionality in the client-side library
  dbport6.dll has now been included in the
  dblibX.dll or dbodbcX.dll
• This change reduces the number of files that must
  be deployed for client installations
• Since this file had to be the same build as the
  client libraries, there was limited benefit to
  maintaining it as a separate library

62
Behavior Changes in 7.0 (cont.)

• Path settings
• The names of the command line utilities do not
  include a version number
• This means that if you have multiple versions of
  ASA on a single machine, then you must qualify
  the path appropriately to ensure that you start
  the expected version of the utility
• In addition, the command line name of the stored
  procedure debugger has changed to dbprdbg

63
Behavior Changes in 7.0 (cont.)

• Connection Behavior Change
• Same machine connections typically use shared
  memory
• When no server was found on specified protocol, a
  shared memory connection was tried as fall-back
• dbisqlc c uiddbapwdsqlengmyenginelinkstc
  pip
• Now, we will only look for a server on TCPIP and
  wont try shared memory as a default
• To get the historic behavior, need to explicitly
  specify shared memory in the links parameter
• dbisqlc c uiddbapwdsqlengmyenginelinkstcp
  ip,shmem
• Note By default the Personal Server starts both
  Shared Memory and TCPIP listeners.

64
Behavior Changes in 7.0 (cont.)

• ANSI_UPDATE_CONSTRAINTS
• Default value is OFF for database files created
  prior to 7.0
• Default value is CURSORS for database files
  created with 7.0 or above
• This option determines whether a given cursor
  will allow updates or not
• If you have upgraded a database file to version
  7.x or above by unloading and reloading the
  database, then you may need to set the value of
  this option to OFF in order to get the same
  behavior you had previously expected

65
Behavior Changes in 7.0 (cont.)

• Identifier Length Limit
• All identifiers are now limited to 128 bytes,
  previously, some identifiers were allowed to be
  longer than 128 bytes.
• Identifiers include user ids, table and column
  names, and the names of other objects in the
  database.
• Most identifiers are stored in system tables and
  are defined as CHAR(128) User Defined errors
• Can now define multiple user defined errors
  within a stored procedure or batch

66
Behavior Changes in 7.0 (cont.)

• LOAD TABLE and UNLOAD TABLE security
• A new server switch (-gl) was introduced to
  control who could execute these statements
• The default for a personal server on a non-Unix
  platform is all
• The default for a network server on all platforms
  and the personal server on Unix platforms is DBA
• To obtain the historic behavior with a 7.x or 8.0
  server then you will need specify
• dbsrv7 gl all

67
Behavior Changes in 7.0 - Summary

• To make a 7.0 database/engine look like a 6.0
  database/engine
• Engine Switches
• -x ipx to start IPX protocol
• -gl all to specify the permissions for using
  LOAD TABLE and UNLOAD TABLE
• Database Options
• SET OPTION public.Ansi_update_constraints OFF
• Particularly if using DBUnload to migrate the
  database file since the default for a 7.0
  initialized database is to have this option set
  to CURSORS

68
Behavior Changes in 7.0 Summary (cont)

• To make a 7.0 database/engine look like a 5.5.x
  database/engine
• Use the settings specified to make a 6.x
  database/engine look like a 5.5.x database/engine
  plus the settings to make a 7.x database/engine
  look like a 6.x database/engine.

69
Behavior Changes

• Behavior Changes in 6.0
• Behavior Changes in 7.0
• Behavior Changes in 8.0

70
Behavior Changes in 8.0

• Java in the database separately licensable
• SQL Anywhere Studio 8.0 Separately-Licensable
  Components
• http//www.sybase.com/detail?id1015780
• Aggregate functions and outer references
• Conformance to SQL/99 standard
• User Supplied Selectivity Estimates
• Option to control whether used or ignored
• ENABLED, DISABLED, OVERRIDE-MAGIC
• Row Ordering
• Less deterministic
• Must use an ORDER BY clause to ensure order

71
Behavior Changes in 8.0 (cont)

• Access plan changes
• Lower probability of an index being used, however
  net performance of any given access plan should
  improve
• If you have queries that are running more slowly
  in ASA 8.0, then we want to hear about them
• Cursor Changes
• Current behavior now adheres more closely to the
  defined standards
• May result in changes in cursor sensitivity which
  in turn could impact existing applications
• OPEN CURSOR on insert not supported (ESQL only)

72
Behavior Changes in 8.0 (cont)

• NUMBER() function changes
• Use of the NUMBER function in a WHERE or HAVING
  clause will now generate an error
• The NUMBER function may now generate negative
  numbers
• User-defined functions now cached
• Trigger name uniqueness across a database
• DBBackup
• Transactions can now span log files.
• Benefit is reduced blocking during the backup
  process.

73
Deprecated and Unsupported Features in 8.0

• Netware 4.10 unsupported
• Netbios and IPX unsupported
• Deprecated Collations superseded by new ones
• WITH_HASH_SIZE clause deprecated
• New index structure (compressed B-tree)
  automatically used if necessary
• Hash size options also unsupported
• -e switch (for encryption) no longer supported
• This functionality has been replaced by
  certificate based encryption via the ec switch
  (old behavior can be accomplished with ec
  simple)
• NONE parameter deprecated for the ISQL_PLAN
  option

74
Behavior Changes in 8.0 - Summary

• No changes in 8.0 require any switches or options
  to mimic a 7.0 database/engine.
• 8.0 requires the same switches and options to
  mimic a 6.0 or 5.5.x database that you would use
  with 7.0
• Remember
• Not all changes are configurable.
• most changes in behavior can not be turned off.
• Deprecated features are supported in (at least)
  the version that they were deprecated in. Support
  for a deprecated feature is not dropped for at
  least 1 full version.

75
UltraLite Behavior Changes(6.x - 7.0)

• New synchronization function call and data
  structure
• 6.x
• ULSynchronize( sqlca, m_EmpID, ULSerialStream(),
  m_SynchParms )
• Limited to 4 parameters
• Changing or adding to the parameter list required
  a change to the function prototype
• Any changes to the function prototype would
  require changes to the UltraLite application
  source code

76
UltraLite Behavior Changes(6.x - 7.0)

• 7.x
• ul_synch_info info
• ULInitSynchInfo( info )
• info.user_name m_EmpIDStr
• info.version SCRIPT_VERSION
• info.stream m_Stream
• info.stream_parms m_SynchParms
• info.observer ObserverFunc
• ULSynchronize( sqlca, info )
• Additional members can be added to the
  ul_synch_info structure without affecting
  existing applications

77
UltraLite Behavior Changes (7.0 7.0.1)

• Dropped support for DOS
• Now require CodeWarrior 6 or above

78
UltraLite Behavior Changes (7.x 8.0)

• Required Code Change for Palm Applications
• ULEnablePalmRecordDB( sqlca )
• ULEnableFileDB( sqlca )
• Palm 2.x no longer supported    
• ULPalmDBStream and ULConduitStream deprecated    
  
• UltraLite generator uses external Java VM    
• UltraLite JDBC package name changed    
• ianywhere.ultralite.jdbc
• All changes must be committed before download
  synchronization