em410 upgrading your sql anywhere studio applications
DESCRIPTION
EM410 Upgrading Your SQL Anywhere Studio Applications. Robert Waywell Senior Product Support Engineer iAnywhere Solutions [email protected]. Focus on behavior and architectural changes rather than new features. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/1.jpg)
EM410Upgrading Your SQL Anywhere Studio Applications
Robert WaywellSenior Product Support EngineeriAnywhere [email protected]
![Page 2: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/2.jpg)
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 Vail.
Determine when and how to upgrade the database file.
![Page 3: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/3.jpg)
Topics
IntroductionUpgrade ToolsClient-Server ApplicationsSQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 4: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/4.jpg)
Introduction
1) A 5.5.x, 6.x, or 7.x database file can be loaded on an 8.0 database engine without being upgraded.
2) The benefits of many new features can be obtained without upgrading the database file.e.g. Multi-processor support Improved Network Communications Enhanced optimizer Thinner client layer Dynamic Caching
3) Even if the database file is not upgraded, upgrading the software can introduce new behaviors.
![Page 5: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/5.jpg)
Introduction
Upgrading can involve multiple layers and components:- Front-end Application
- Operating System
- Hardware
- SQL Anywhere Studio Software
- Database File
![Page 6: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/6.jpg)
Reasons to Upgrade Software
Performance ImprovementsPlatform Support
- New OS versions are only supported by current versions of released software.
Improved Stability- Reduced chance of corruption
- Bug fixes are made in current versions, only back ported to active versions
- More robust DBValid
SQL Anywhere Studio Supported Platforms and Support Statushttp://my.sybase.com/detail?id=1002288
![Page 7: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/7.jpg)
Utility Changes
DBISQL and Sybase Central now Java based.- Use JDBC rather than ESQL
- Use TDS instead of Cmdseq communications protocol
ASA User's Guide PART 6. The Adaptive Server Family CHAPTER 33. Adaptive Server Anywhere as an Open Server
Characteristics of Open Client and jConnect connections
![Page 8: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/8.jpg)
Reasons to Upgrade the Database File
Functionality requires it.e.g. SQL Remote required changes to system information between 6.x
and 7.0
Integrity Checke.g. Catch invalid date values (pre-5.5.03 bug #446424)
Performance Improvements- Separate Primary Key/Foreign Key structures- New index structures for long indexes
- HASH SIZE, Patricia Trie- Enhanced Statistics- Modify Page Size
- If database has grown, a larger page size may be more appropriate
![Page 9: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/9.jpg)
Upgrade Practices
1) Always make a full backup before starting an upgrade.
2) Benchmark performance.Check the PLAN() function for key queries.
Measure performance of your standard tests.
3) If you will be using DBUpgrade ensure that you run DBValid.Should be part of your regular backup strategy.
![Page 10: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/10.jpg)
Upgrade Practices (cont)
4) TestMany intentional behavior changes.
Fix bugs
Improve compliance to standards
Unexpected side effects.
May have been well intentioned.
May be a combination of changes.
Application may have been coded to a bug.
Reliance on non-static features.
e.g. Error message text
Assertion Numbers
![Page 11: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/11.jpg)
Upgrade Practices (cont)
5) ODBC Datasources Need to be recreated.Even if not using ODBC, may need to modify startup and connection
parameters.
6) 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.
![Page 12: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/12.jpg)
Topics
IntroductionUpgrade Tools
DBUpgrade
DBUnload
Client-Server ApplicationsSQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 13: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/13.jpg)
DBUpgrade
What it doesUpdates the system tables, provides new database options, adds or
modifies system procedures.This process is not recoverable.You must:
1) Perform a full backup before starting the upgrade.2) Perform a full backup after completing the upgrade.
When to use itWhen upgrading the database in-place.When changes to the physical file format are not important.Possibly when upgrading for platform support.
![Page 14: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/14.jpg)
DBUpgrade (cont)
LimitationsDoes 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
![Page 15: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/15.jpg)
DBUnload
What it doesUnloads data and schema, generating a SQL script
and .dat files.
When to use itWhen 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 databaseCompleted by reading the SQL script via ISQL
![Page 16: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/16.jpg)
DBUnload - Limitations
Some pieces of schema were previously stored verbatim. This can lead to problems in reloading views that require qualified table names.grant connect to rob identified by sql;grant dba to rob;connect rob;
create table test( row_id int default autoincrement primary key,
row_textchar(30));
create view test_view as select row_id, row_text from test;
![Page 17: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/17.jpg)
DBUnload – Limitations (Views)
In Pre-5.0.02 databases, the table referenced in the view would not be qualified with the owner id (“rob”) before being stored in the database.
To get around this:1) Undocumented –hu switch on DBUnload
• In the reload.sql file, writes out a SET CONNECTION statement before the CREATE VIEW statement
2) Edit reload.sql1) GRANT GROUP to all users that own database objects2) GRANT MEMBERSHIP IN GROUP <..> TO DBA• This will work for as long as no object names are repeated with
different owner id’s.• e.g. “rob”.”t1” and “DBA”.”t1”
![Page 18: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/18.jpg)
DBUnload – View Dependencies
Views may reference other views.If views have been created dropped,
recreated, then they may be “out of order” in the system tableCREATE VIEW v1 …CREATE VIEW v2 …DROP VIEW v1CREATE VIEW v3 …CREATE VIEW v1 …
DBUnload –j <count>
![Page 19: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/19.jpg)
DBUnload – Database Initialization
DBInfoLog File Encryption
Mirror Log File Blank Padding
Compression Case Sensitivity
Page Size Collation Sequence
Characteristics not covered by DBInfojConnect Support Java Support
JDK Version
Sys.syscolumns & sys.sysindexes views
![Page 20: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/20.jpg)
DBUnload – Database Initialization (cont)
Test for:jConnect Support
SELECT * FROM sys.systable WHERE table_name LIKE 'jdbc%‘Note: 7.0 and above use Java version of DBISQL and Sybase Central
Java ClassesSELECT * FROM sys.sysjar- file will be empty if Java classes are not installed
JDK Version5.5.x – No Java in the database6.x - JDK 1.1.67.x - JDK 1.1.88.x - SELECT db_property(‘JDKVersion’)
![Page 21: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/21.jpg)
DBUnload – Database Initialization (cont)
Default collation sequences changedDefault collation depends on OS that the database is
initialized on.
SELECT PROPERTY( 'DefaultCollation' )
“Internal” collation used in 5.5.x has been deprecated and replaced by 850Latin1
DBUnload –ar … will use 1252Latin1 when creating the new database
![Page 22: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/22.jpg)
DBUnload – SQL Remote Systems
1. Need to maintain log offset informationSQL Remote User's Guide
11. Administering SQL Remote for Adaptive Server Anywhere Transaction log and backup management
Unloading and reloading a consolidated database
• Instructions apply to both consolidated and remote databases
2. New system users added in Version 6.0Can not use DBUnload –a? switchesMust do a manual unload/reloadMay get an error “at least one user in this database has a user id which does not match its orig”If you get this error, you should compare the sys.sysuser table in the new
database to the original database.
![Page 23: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/23.jpg)
Topics
IntroductionUpgrade ToolsClient-Server Applications
Architecture
Behavior Changes
SQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 24: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/24.jpg)
Architecture
At this point, let’s 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.xVersion 8.x
![Page 25: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/25.jpg)
Version 5.5.x Standalone Engine
ClientApplication
ODBCDriver
Wod50?.dll
ESQL LibraryDbl50?.dll
Database Engine
Dbeng50?.exe
ClientApplication
Dbclien?.exe
DatabaseServer
Dbsrv50?.exe
Shared MemoryNamed Pipes
DDEHLI
TCPIPIPX
NetBiosNetBeui
ODBC TranslatorWtr50?.dll
Language DLLWl50??.dll
Language DLLWl50??.dll
Language DLLWl50??.dll
Language DLLWl50??.dll
Language DLLWl50??.dll
Client-Server
![Page 26: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/26.jpg)
Version 5.5.x – Key Points
1. ODBC driver works through the ESQL libraryIn this context, ODBC was not a “native” interface to the database
engine
2. DBClient is a separate processThis model was a good choice historically when inter-process
communication was relatively inexpensive
3. The language dll is used by several different components and contains language specific resource stringsMaintaining these strings in a separate component facilitates
internationalization
![Page 27: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/27.jpg)
Version 6.x Personal Server (Standalone Engine)
ClientApplication
ODBCDriver
DBODBC6.dll
ESQL LibraryDblib6.dll
Database Engine
Dbeng6.exe
ClientApplication
Dbport6.dll
DatabaseServer
Dbsrv6.exe
Shared MemoryNamed Pipes
TCPIPIPX
NetBiosNetBeui
ODBC TranslatorDbodtr6.dll
Language DLLDbl??6.dll
Language DLLDbl??6.dll
Language DLLDbl??6.dll
Language DLLDbl??6.dll
Client-Server
Dbsrv6.dll
Dbsrv6.dll
![Page 28: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/28.jpg)
Version 6.x – What Changed?
ODBC is now a native interface, that communicates directly with the database engine independently of the ESQL libraryOne layer of translation has been omitted
Network communications are now handled through a dll rather than through a separate processOn current operating systems, intra-process communication is
more efficient that inter-process communication making this model more efficient than the 5.5.x model
![Page 29: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/29.jpg)
Version 6.x – What Changed? (cont.)
The internal communications protocol between the client components and the database server also changed at this time with the side effect that the 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
For local machine connections, DDE and HLI are no longer supported interfaces
![Page 30: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/30.jpg)
Version 6.x – 16-bit Applications
When a 16-bit application thunks up to a 32-bit ODBC driver, the 32-bit ODBC driver is now part of a 16-bit contextAccording to Microsoft Knowledge Base article# Q140697:
“This means that no Win16 application running on Windows 95 can use a Win32 driver that issues any threading commands.”
16-bit applications that previously ran on Windows 95/98 by thunking up to the 32-bit ODBC driver, you will now be limited to using the 16-bit ODBC driver for network connections- 16-bit applications on Windows NT and 2000 can use thunking and connect
across the network
Since no 16-bit client support is available in ASA 7 or Vail, 16-bit applications would be limited to using a 6.x or lower 16-bit client to connect to the Vail server
![Page 31: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/31.jpg)
Version 7.xPersonal Server (Standalone Engine)
ClientApplication
ODBCDriver
DBODBC7.dll
ESQL LibraryDblib7.dll
Database Engine
Dbeng7.exe
ClientApplication
DatabaseServer
Dbsrv7.exe
Shared MemoryNamed Pipes
TCPIPSPX(IPX)NetBiosNetBeui
ODBC TranslatorDbodtr7.dll
Language DLLDbl??7.dll
Language DLLDbl??7.dll
Language DLLDbl??7.dll
Language DLLDbl??7.dll
Client-Server
Dbsrv7.dll
Dbsrv7.dll
![Page 32: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/32.jpg)
Version 7.x – What Changed?
The dbport6.dll which previously handled network communications has been eliminated and this functionality has been rolled up into the dbodbc7.dll and dblib7.dllThe 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
![Page 33: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/33.jpg)
Version 8.xPersonal Server (Standalone Engine)
ClientApplication
ODBCDriver
DBODBC8dll
ESQL LibraryDblib8.dll
Database Engine
Dbeng8.exe
ClientApplication
DatabaseServer
Dbsrv8.exe
Shared MemoryNamed Pipes
TCPIPSPX(IPX)NetBiosNetBeui
ODBC TranslatorDbodtr8.dll
Language DLLDbl??8dll
Language DLLDbl??8.dll
Language DLLDbl??8.dll
Language DLLDbl??8.dll
Client-Server
Dbsrv8.dll
Dbsrv8.dll
![Page 34: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/34.jpg)
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 deploymentAdditional files would be required to support External Function
Calls, Java in the database, NT Performance monitor
![Page 35: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/35.jpg)
Topics
IntroductionUpgrade ToolsClient-Server Applications
Architecture
Behavior Changes
SQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 36: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/36.jpg)
Behavior Changes
Behavior Changes in 6.0Behavior Changes in 7.0Behavior Changes in 7.0 PatchesBehavior Changes in 8.0
![Page 37: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/37.jpg)
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
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
![Page 38: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/38.jpg)
Behavior Changes in 6.0 (cont)
Max_cursor_count- Introduced in 6.0- Limits the number of cursor in use by a given
connection- Default is 50, to disable it set it to 0
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
![Page 39: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/39.jpg)
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”
![Page 40: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/40.jpg)
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
![Page 41: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/41.jpg)
Behavior Changes in 6.0 (cont)
TSQL Outer Join Changes1) “A table cannot participate in both a Transact-SQL outer join
clause and a regular join clause”WHERE R.x *= S.xAND S.y = T.y
2) “You cannot use a subquery that contains the null-supplying table of an outer join”WHERE R.x *= S.yAND EXISTS ( SELECT * FROM T WHERE T.x = S.x )
![Page 42: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/42.jpg)
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 > 50 assumed to be 19xx- Anything < 50 assumed to be 20xx
![Page 43: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/43.jpg)
Behavior Changes in 6.0 (cont)
Global Temporary TablesThere may only be 1 user connected to the database, that
has referenced the global temporary table at the time you alter it.
![Page 44: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/44.jpg)
Behavior Changes in 6.0 - Summary
To make a 6.0 database/engine look like a 5.5.x database/engine:
Engine Switches-gn <num> to specify internal thread count-gd all to set database starting permissions
Database OptionsSET 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.
![Page 45: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/45.jpg)
Behavior Changes
Behavior Changes in 6.0Behavior Changes in 7.0Behavior Changes in 7.0 PatchesBehavior Changes in 8.0
![Page 46: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/46.jpg)
Behavior Changes From ASA 6 to ASA 7
Deprecated and Unsupported FeaturesBehavior Changes
![Page 47: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/47.jpg)
Deprecated and Unsupported Features in 7.0
Win 3.x and WinCE 2.0 no longer supportedIPX protocol deprecated
SPX is the preferred alternative
NUMBER function deprecatedA modified, safer version of this function is provided in Vail
Deprecated network communication parametersBroadcast and CommAutoStop no longer have any effect
No DBClient compatibility executable
![Page 48: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/48.jpg)
Behavior Changes in 7.0
ISQL Changes Default version of ISQL became a Java versionINPUT statement
• Dropped support for DIF and WATFILE file formats• Added support for EXCEL file format
OUTPUT statement• Dropped support for DIF, TEXT, and WATFILE file formats• Added support for EXCEL and HTML file formats
Server name space changesStarting 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 2638The 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.
![Page 49: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/49.jpg)
Behavior Changes in 7.0 (cont.)
No separate network ports libraryThe functionality in the client-side library dbport6.dll has now been included
in the dblibX.dll or dbodbcX.dllThis change reduces the number of files that must be deployed for client
installationsSince this file had to be the same build as the client libraries, there was
limited benefit to maintaining it as a separate library
Path settingsThe names of the command line utilities do not include a version numberThis 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
![Page 50: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/50.jpg)
Behavior Changes in 7.0 (cont.)
Connection Behavior Change- Same machine connections typically use shared memory- Previously, if Commlinks parameter was specified, and no server was
found on the specified protocol, then a shared memory connection would be attempted as a fall-back
Dbisqlc –c “uid=dba;pwd=sql;eng=myengine;links=tcpip”- Now, we will only look for a server on TCPIP and won’t try shared
memory as a default- To get the historic behavior, need to explicitly specify shared memory in
the links parameter
Dbisqlc –c “uid=dba;pwd=sql;eng=myengine;links=tcpip,shmem”
Note: By default the Personal Server starts both Shared Memory and TCPIP listeners.
![Page 51: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/51.jpg)
Behavior Changes in 7.0 (cont.)
ODBC SQLDescribeCol BehaviorThe @@identity field is now described as SQL_BIGINT, previously it was
SQL_INTEGER
ANSI_UPDATE_CONSTRAINTSDefault 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
![Page 52: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/52.jpg)
Behavior Changes in 7.0 (cont.)
Identifier Length LimitAll identifiers are now limited to 128 bytes, previously, some
identifiers were allowed to be longer than 128 bytes.Identifiers include user id’s, 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)
jConnect connections There were changes to the way the REMOTEPWD connection field
is populated for jConnect 4.2
User Defined errorsCan now define multiple user defined errors within a stored
procedure or batch
![Page 53: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/53.jpg)
Behavior Changes in 7.0 (cont.)
LOAD TABLE and UNLOAD TABLE securityA 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 Vail server then you will need specify:
• dbsrv7 –gl all …
![Page 54: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/54.jpg)
Behavior Changes in 7.0 (cont.)
@@identity value in triggersPrior to 7.0, only the last identity value set was available
This value is now nested
Example:
1) Insert into t1 -> this table has an identity column and fires a trigger
2) Trigger inserts into t2 which also has an identity column
3) Within the trigger, the value of @@identity will reflect the value set by the insert in to t2
4) After exiting the trigger, the value of @@identity will reflect the value set by the insert in to t1
![Page 55: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/55.jpg)
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 OptionsSET 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’
![Page 56: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/56.jpg)
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.
![Page 57: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/57.jpg)
Behavior Changes
Behavior Changes in 6.0Behavior Changes in 7.0Behavior Changes in 7.0 PatchesBehavior Changes in 8.0
![Page 58: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/58.jpg)
Behavior Changes Within ASA 7 Patches
Aliases must be defined before first reference (7.0.2)ODBC autocommit
• (7.0.0) this was modified to utilize the databases CHAINED mode which executes a COMMIT after each operation
• The side effect of this change was that COMMIT’s were executed between statements within a stored procedure
• This was a significant change from the previous behavior in which the COMMIT was issued by the ODBC driver which meant that a stored procedure call would execute as a single batch
• (7.0.1) this change has since been reverted to provide the historic behavior of versions 6.x and below
![Page 59: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/59.jpg)
Behavior Changes
Behavior Changes in 6.0Behavior Changes in 7.0Behavior Changes in 7.0 PatchesBehavior Changes in 8.0
![Page 60: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/60.jpg)
Behavior Changes in 8.0
User Supplied Selectivity EstimatesDisabled for this beta release
Row OrderingLess predictable than previously
Must use an ORDER BY clause to ensure order
Access plan changesLower 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 Vail, then we want to hear about them
![Page 61: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/61.jpg)
Behavior Changes in 8.0 (cont)
Cursor ChangesCurrent 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 supportedNUMBER(*) 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
![Page 62: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/62.jpg)
Behavior Changes in 8.0 (cont)
DBBackupTransactions can now span log files.
Benefit is reduced blocking during the backup process.
Can no longer assume that all transactions are complete by the end of a log file if truncating the log file as part of an online backup.
![Page 63: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/63.jpg)
Deprecated and Unsupported Features in 8.0
Deprecated CollationsA number of collations have been replaced or droppedExisting databases using these collations will continue to work with
the 8.0 engineThe character set conversion functionality can be used to migrate
these databases to a current collation via an unload and reload sequence
-e switch no longer supportedThis switch enabled the encryption of the network packets sent
between the client and serverThis functionality has been replaced by certificate based encryption
via the –ec switch
NONE parameter has been deprecated for the ISQL_PLAN option
![Page 64: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/64.jpg)
Behavior Changes in 8.0 - Summary
No current 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. In fact 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.
![Page 65: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/65.jpg)
Topics
IntroductionUpgrade ToolsClient-Server ApplicationsSQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 66: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/66.jpg)
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’
![Page 67: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/67.jpg)
Topics
IntroductionUpgrade ToolsClient-Server ApplicationsSQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 68: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/68.jpg)
MobiLink Applications (8.0)
MobiLink user authenticationMust specify –zu+ if not using authentication
Client SetupNow use Publications and Synchronization Subscriptions
ShutdownDBMLStop will only shut down a server when executed
from the same machine.
TCP/IP LivenessKeep_alive is now on (1) by default.MobiLink & TCP/IP Keep-Alivehttp://my.sybase.com/detail?id=1013181
![Page 69: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/69.jpg)
MobiLink Applications (7.0)
System tables and scripts previously prefixed with ul_ * are now prefixed with ml_*
Stored procedure names also changed:Sp_table_script = ml_add_table_script
Sp_connection_script = ml_add_connection_script
DBSSrv6 was renamed to DBMLSrv7Synchronization scripts now require a script
version
![Page 70: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/70.jpg)
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?id=1011880
![Page 71: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/71.jpg)
Topics
IntroductionUpgrade ToolsClient-Server ApplicationsSQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 72: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/72.jpg)
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.
![Page 73: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/73.jpg)
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.
![Page 74: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/74.jpg)
UltraLite Behavior Changes(6.x - 7.0)
New synchronization function call and data structure
6.xULSynchronize( &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 applications source code
![Page 75: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/75.jpg)
UltraLite Behavior Changes(6.x - 7.0)
7.xul_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
![Page 76: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/76.jpg)
UltraLite Behavior Changes (7.0 –7.0.1)
Dropped support for DOSNow require CodeWarrior 6 or above
![Page 77: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/77.jpg)
Topics
IntroductionUpgrade ToolsClient-Server ApplicationsSQL Remote ApplicationsMobiLink ApplicationsUltraLite ApplicationsRequired Files
![Page 78: EM410 Upgrading Your SQL Anywhere Studio Applications](https://reader035.vdocuments.us/reader035/viewer/2022081800/56814d84550346895dbae31e/html5/thumbnails/78.jpg)
Required Files
Version 7.0 ASA User's Guide
PART 5. Database Administration and Advanced Use
CHAPTER 28. Deploying Databases and Applications
Version 8.0ASA Programming Guide
12. Deploying Databases and Applications