EM410 Upgrading Your SQL Anywhere Studio Applications

1. EM410Upgrading Your SQL Anywhere Studio Applications • Robert Waywell • Senior Product Support Engineer • iAnywhere Solutions • Rwaywell@sybase.com

2. 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. Objectives

3. Introduction Upgrade Tools Client-Server Applications SQL Remote Applications MobiLink Applications UltraLite Applications Required Files Topics

4. A 5.5.x, 6.x, or 7.x database file can be loaded on an 8.0 database engine without being upgraded. 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 Even if the database file is not upgraded, upgrading the software can introduce new behaviors. Introduction

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

6. Performance Improvements Platform 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 Status http://my.sybase.com/detail?id=1002288 Reasons to Upgrade Software

7. 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 Utility Changes

8. Functionality requires it. e.g. SQL Remote required changes to system information between 6.x and 7.0 Integrity Check e.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 Reasons to Upgrade the Database File

9. Always make a full backup before starting an upgrade. 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. Upgrade Practices

10. 4) Test Many 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 Upgrade Practices (cont)

11. 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. Upgrade Practices (cont)

12. Introduction Upgrade Tools DBUpgrade DBUnload Client-Server Applications SQL Remote Applications MobiLink Applications UltraLite Applications Required Files Topics

13. 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. DBUpgrade

14. 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 DBUpgrade (cont)

15. What it does Unloads data and schema, generating a SQL script 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 DBUnload

16. 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_text char(30) ); create view test_view as select row_id, row_text from test; DBUnload - Limitations

17. 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: Undocumented –hu switch on DBUnload In the reload.sql file, writes out a SET CONNECTION statement before the CREATE VIEW statement Edit reload.sql GRANT GROUP to all users that own database objects 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” DBUnload – Limitations (Views)

18. Views may reference other views. If views have been created dropped, recreated, then they may be “out of order” in the system table CREATE VIEW v1 … CREATE VIEW v2 … DROP VIEW v1 CREATE VIEW v3 … CREATE VIEW v1 … DBUnload –j <count> DBUnload – View Dependencies

19. DBInfo Log File Encryption Mirror Log File Blank Padding Compression Case Sensitivity Page Size Collation Sequence Characteristics not covered by DBInfo jConnect Support Java Support JDK Version Sys.syscolumns & sys.sysindexes views DBUnload – Database Initialization

20. 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 Classes SELECT * FROM sys.sysjar - file will be empty if Java classes are not installed JDK Version 5.5.x – No Java in the database 6.x - JDK 1.1.6 7.x - JDK 1.1.8 8.x - SELECT db_property(‘JDKVersion’) DBUnload – Database Initialization (cont)

21. Default collation sequences changed Default 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 DBUnload – Database Initialization (cont)

22. Need to maintain log offset information SQL 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.0 Can not use DBUnload –a? switches Must do a manual unload/reload May 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. DBUnload – SQL Remote Systems

23. Introduction Upgrade Tools Client-Server Applications Architecture Behavior Changes SQL Remote Applications MobiLink Applications UltraLite Applications Required Files Topics

24. 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.x Version 8.x Architecture

25. Standalone Engine Version 5.5.x ODBC Translator Wtr50?.dll Shared Memory Named Pipes DDE HLI Database Engine Dbeng50?.exe Language DLL Wl50??.dll Client Application ODBC Driver Wod50?.dll ESQL Library Dbl50?.dll Language DLL Wl50??.dll Language DLL Wl50??.dll Client Application Client-Server TCPIP IPX NetBios NetBeui Database Server Dbsrv50?.exe Dbclien?.exe Language DLL Wl50??.dll Language DLL Wl50??.dll

26. ODBC driver works through the ESQL library In this context, ODBC was not a “native” interface to the database engine 2. DBClient is a separate process This 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 strings Maintaining these strings in a separate component facilitates internationalization Version 5.5.x – Key Points

27. Version 6.x • Personal Server (Standalone Engine) ODBC Translator Dbodtr6.dll Dbsrv6.dll Client Application ODBC Driver DBODBC6.dll Database Engine Dbeng6.exe Shared Memory Named Pipes Language DLL Dbl??6.dll Language DLL Dbl??6.dll Client Application ESQL Library Dblib6.dll Language DLL Dbl??6.dll Client-Server TCPIP IPX NetBios NetBeui Database Server Dbsrv6.exe Dbsrv6.dll Dbport6.dll Language DLL Dbl??6.dll

28. 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 On current operating systems, intra-process communication is more efficient that inter-process communication making this model more efficient than the 5.5.x model Version 6.x – What Changed?

29. 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 Version 6.x – What Changed? (cont.)

30. 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 context According 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 Version 6.x – 16-bit Applications

31. Version 7.x • Personal Server (Standalone Engine) ODBC Translator Dbodtr7.dll Dbsrv7.dll Client Application ODBC Driver DBODBC7.dll Database Engine Dbeng7.exe Shared Memory Named Pipes Language DLL Dbl??7.dll Language DLL Dbl??7.dll Client Application ESQL Library Dblib7.dll Language DLL Dbl??7.dll Client-Server TCPIP SPX(IPX) NetBios NetBeui Database Server Dbsrv7.exe Dbsrv7.dll Language DLL Dbl??7.dll

32. The dbport6.dll which previously handled network communications has been eliminated and this functionality has been rolled up 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 Version 7.x – What Changed?

33. Version 8.x • Personal Server (Standalone Engine) ODBC Translator Dbodtr8.dll Dbsrv8.dll Client Application ODBC Driver DBODBC8dll Database Engine Dbeng8.exe Shared Memory Named Pipes Language DLL Dbl??8.dll Language DLL Dbl??8dll Client Application ESQL Library Dblib8.dll Language DLL Dbl??8.dll Client-Server TCPIP SPX(IPX) NetBios NetBeui Database Server Dbsrv8.exe Dbsrv8.dll Language DLL Dbl??8.dll

34. 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 Version 8.x – What Changed?

35. Introduction Upgrade Tools Client-Server Applications Architecture Behavior Changes SQL Remote Applications MobiLink Applications UltraLite Applications Required Files Topics

36. Behavior Changes in 6.0 Behavior Changes in 7.0 Behavior Changes in 7.0 Patches Behavior Changes in 8.0 Behavior Changes

37. 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 Behavior Changes in 6.0

38. 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 Behavior Changes in 6.0 (cont)

39. 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” Behavior Changes in 6.0 (cont)

40. 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 Behavior Changes in 6.0 (cont)

41. TSQL Outer Join Changes “A table cannot participate in both a Transact-SQL outer join clause and a regular join clause” WHERE R.x *= S.x AND S.y = T.y 2) “You cannot use a subquery that contains the null-supplying table of an outer join” WHERE R.x *= S.y AND EXISTS ( SELECT * FROM T WHERE T.x = S.x ) Behavior Changes in 6.0 (cont)

42. % 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 Behavior Changes in 6.0 (cont)

43. Global Temporary Tables There may only be 1 user connected to the database, that has referenced the global temporary table at the time you alter it. Behavior Changes in 6.0 (cont)

44. 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 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. Behavior Changes in 6.0 - Summary

45. Behavior Changes in 6.0 Behavior Changes in 7.0 Behavior Changes in 7.0 Patches Behavior Changes in 8.0 Behavior Changes

46. Deprecated and Unsupported Features Behavior Changes Behavior Changes From ASA 6 to ASA 7

47. 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 Vail Deprecated network communication parameters Broadcast and CommAutoStop no longer have any effect No DBClient compatibility executable Deprecated and Unsupported Features in 7.0

48. ISQL Changes Default version of ISQL became a Java version INPUT 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 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. Behavior Changes in 7.0

49. 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 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 Behavior Changes in 7.0 (cont.)

50. 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. Behavior Changes in 7.0 (cont.)