SQL Anywhere® 10 - Changes and Upgrading
What's New in Version 10.0.0
SQL Anywhere
Deprecated and discontinued features
-f, -fi, -fd, -fn options for the Validation utility (dbvalid) deprecated The syntax for the dbvalid utility has been simplified. Previously, when no option was specified, the dbvalid utility performed express validation when validating tables. Now, the dbvalid utility performs full validation by default, as though the -f, -fi- and -fd options were specified. As a result, use of these options is deprecated and you must specify the -fx option to perform an express validation on tables.
Also, support for the -fn option, which performed validation using the algorithm from version 9.0.0 and earlier releases, is no longer supported. For more information on the Validation utility, see Validation utility (dbvalid).VALIDATE TABLE statement options deprecated The syntax for the VALIDATE TABLE statement has been simplified. Previously, when no option was specified, the VALIDATE TABLE statement performed a normal validation. Now, the VALIDATE TABLE statement performs full validation by default, as though the WITH FULL CHECK options was specified. As a result, the WITH FULL CHECK, WITH INDEX, and WITH DATA options are deprecated. See VALIDATE statement.
Transact-SQL outer joins deprecated Transact-SQL outer joins have been deprecated in this release, and will not be supported in future versions of SQL Anywhere. The new tsql_outer_joins database option enables or disables the ability to use the TSQL outer joins operators *= and =* in DML statements and views for the current connection. This option is set to Off by default. See tsql_outer_joins option [compatibility].
WITH HASH SIZE clause no longer supported With the elimination of older B-tree index technology, the WITH HASH SIZE clause of the CREATE INDEX statement is no longer supported.
Unsupported properties The NumProcessorsAvail and NumProcessorsMax server properties are no longer supported. You can use the NumLogicalProcessors, NumLogicalProcessorsUsed, NumPhysicalProcessors, and NumPhysicalProcessorsUsed server properties instead. See Server-level properties.
STRIP ON clause of LOAD TABLE is deprecated While the stripping of leading and trailing blanks has been enhanced in SQL Anywhere 10.0.0 to allow you to fine tune the stripping behavior, STRIP ON is deprecated. To continue stripping trailing blanks only (default behavior in previous releases when STRIP ON was specified), use STRIP RTRIM instead. See LOAD TABLE statement.
UTF8 collation is deprecated The UTF8 collation is deprecated. Use the UTF8BIN collation instead. See Supported and alternate collations.
jConnect 4.5 no longer supported Applications that previously connected using jConnect 4.5 will still work, but the using jConnect 5.5 or 6.0.5 is recommended. See Using the jConnect JDBC driver.
SQLLOCALE environment variable no longer supported The SQLLOCALE environment variable is no longer supported. It has been replaced by the SALANG and SACHARSET environment variables. See SALANG environment variable and SACHARSET environment variable.
Named Pipes no longer supported The Named Pipes protocol is no longer supported. Applications that previously used Named Pipes must be changed to use shared memory instead. See Selecting communications protocols.
Data Source utility (dbdsn) -o option deprecated The -o option for the Data Source utility has been deprecated. If you want to write output messages to a file, you can specify the LogFile connection parameter in the connection string. See LogFile connection parameter [LOG].
Creation of custom collations not supported Creation of custom collations is no longer supported. The Create Custom Collation wizard, the Collation utility (dbcollat), the DBCollate function, and the a_db_collation structure, are no longer supported. See Choosing collations.
If you are rebuilding a database with a custom collation, the collation is preserved if you rebuild in a single step. If you choose to unload the database and then load the schema and data into a database that you create, then you must use one of the supplied collations. See Rebuilding your database for version 10.0.0.Server Licensing utility -p option not supported In previous releases, the Server Licensing utility supported the -p option, which was used to specify the operating system the database server was licensed for. This option is no longer supported.
Database server -d option not supported The -d database server option, used on NetWare to force the use of POSIX I/O rather than DFS (Direct File System) I/O is no longer supported.
Database server -y option not supported The -y database server option, used on Windows 95/98/Me to run the database server as a Windows service is no longer supported because these operating systems are no longer supported. To run the database server as a service on any of the supported platforms, use the dbsvc utility. See Service utility (dbsvc) for Windows.
-sc option not supported SQL Anywhere 7.0 was awarded a TCSEC (Trusted Computer System Evaluation Criteria) C2 security rating from the U.S. Government. The -sc server option allowed you to run the current version of SQL Anywhere in a manner equivalent to the C2-certified environment. Support for the -sc option, as well as the C2 server property, has been removed in version 10.0.0.
max_work_table_hash_size database option not supported The max_work_table_hash_size option is no longer supported. The query optimizer allocates hash sizes for the internal temporary tables based on the data distribution within the table.
max_hash_size database option not supported The max_hash_size option is no longer supported.
Compressed databases and write files not supported As a result, the following features are no longer available:
File extensions The following file extensions are no longer supported:
the .wrt extension used to identify write files
the .cdb extension used to identify compressed database files
Database server behavior on NetWare The database server no longer looks for database files with the .wrt extension when a database file is specified without an extension. See The SQL Anywhere database server.
Deploying databases on read-only media You can no longer supply a write file to record changes to a database supplied on read-only media, such as a CD-ROM. However, you can still deploy databases on read-only media if they are run in read-only mode. See Deploying databases on read-only media and -r server option.
Database utilities The following utilities and wizards are no longer supported:
SQL statements The following SQL statements are no longer supported:
DBTools structures The following structures or members of structures are no longer supported:
a_backup_db structure This structure holds the information needed to perform backup tasks using the DBTools library.
The backup_writefile member now appears as _unused.a_compress_db structure This structure has been removed.
a_compress_stats structure This structure holds the information needed to perform database compression tasks using the DBTools library.
a_db_info structure This structure holds the information needed to return dbinfo information using the DBTools library.
The wrtbufsize member now appears as _unused1, the wrtnamebuffer member now appears as _unused2, and the compressed member now appears as _unused3.an_expand_db structure This structure holds information needed for database expansion using the DBTools library.
a_stats_line structure This structure holds information needed for database compression and expansion using the DBTools library.
a_writefile structure This structure holds information needed for database write file management using the DBTools library.
DBTools functions The following functions are no longer supported:
Database properties The following database properties are no longer supported:
Compression
FileSize writefile
FreePages writefile
DB_BACKUP_WRITEFILE This embedded SQL function is no longer supported.
Support for unused ASE-compatibility views and procedures removed Support for the following unused Adaptive Server Enterprise views in the SQL Anywhere database has been removed:
| View name | View name |
|---|---|
| SYSALTERNATES | SYSLOGINROLES |
| SYSAUDITOPTIONS | SYSLOGS |
| SYSAUDITS | SYSMESSAGES |
| SYSCHARSETS | SYSPROCEDURES |
| SYSCONFIGURES | SYSPROCESSES |
| SYSCONSTRAINTS | SYSPROTECTS |
| SYSCURCONFIGS | SYSREFERENCES |
| SYSDATABASES | SYSREMOTELOGINS |
| SYSDEPENDS | SYSROLES |
| SYSDEVICES | SYSSEGMENTS |
| SYSENGINES | SYSSERVERS |
| SYSKEYS | SYSSRVROLES |
| SYSLANGUAGES | SYSTHRESHOLDS |
| SYSLOCKS | SYSUSAGES |
| Procedure name | Procedure name |
|---|---|
| sp_addalias | sp_helpindex |
| sp_addauditrecord | sp_helpjoins |
| sp_addlanguage | sp_helpkey |
| sp_addremotelogin | sp_helplanguage |
| sp_addsegment | sp_helplog |
| sp_addserver | sp_helpremotelogin |
| sp_addthreshold | sp_helpprotect |
| sp_adddumpdevice | sp_helpsegment |
| sp_auditdatabase | sp_helpserver |
| sp_auditlogin | sp_helpsort |
| sp_auditobject | sp_helpthreshold |
| sp_auditoption | sp_helpuser |
| sp_auditsproc | sp_indsuspect |
| sp_bindefault | sp_lock |
| sp_bindmsg | sp_locklogin |
| sp_bindrule | sp_logdevice |
| sp_changedbowner | sp_modifylogin |
| sp_checknames | sp_modifythreshold |
| sp_checkreswords | sp_monitor |
| sp_clearstats | sp_placeobject |
| sp_commonkey | sp_primarykey |
| sp_configure | sp_procxmode |
| sp_cursorinfo | sp_recompile |
| sp_dboption | sp_remap |
| sp_dbremap | sp_remoteoption |
| sp_depends | sp_rename |
| sp_diskdefault | sp_renamedb |
| sp_displaylogin | sp_reportstats |
| sp_dropalias | sp_role |
| sp_dropdevice | sp_serveroption |
| sp_dropkey | sp_setlangalias |
| sp_droplanguage | sp_spaceused |
| sp_dropremotelogin | sp_syntax |
| sp_dropsegment | sp_unbindefault |
| sp_dropserver | sp_unbindmsg |
| sp_dropthreshold | sp_unbindrule |
| sp_estspace | sp_volchanged |
| sp_extendsegment | sp_who |
| sp_foreignkey | sp_column_privileges |
| sp_help | sp_databases |
| sp_helpconstraint | sp_datatype_info |
| sp_helpdb | sp_server_info |
| sp_helpdevice | sp_table_privileges |
| sp_helpgroup |
index_type and index_owner columns removed from SYSINDEX system view The index_type and index_owner columns have been removed from the SYSINDEX view. These columns previously contained the default values USER and SA, respectively. Index information is now stored in the ISYSIDX and ISYSIDXCOL system views. See SYSIDX system view and SYSIDXCOL system view.
DLL protocol option removed on server The DLL protocol option now applies only to clients running on Windows 32-bit platforms. The DLL protocol option has been removed from the database server as it uses only Winsock 2.2. Similarly, the DLL protocol has been removed from Windows CE clients as they use only Winsock 1.1.
Winsock 2.2 is required for database servers on all Windows platforms. See DLL protocol option.ASANY and ASANYSH environment variables renamed The ASANY and ASANYSH environment variables have been renamed SQLANY10 and SQLANYSH10, respectively. See SQLANY10 environment variable and SQLANYSAMP10 environment variable.
PreserveSource property deprecated The PreserveSource database property has been deprecated in this release and always returns the value On when its setting is queried.
Unsupported database properties The following database properties have been removed in this release:
Unsupported system procedures The sa_conn_properties_by_name and sa_conn_properties_by_conn system procedures are no longer supported. You can use the new sa_conn_options system procedure to obtain this information. See sa_conn_options system procedure.
Algorithms removed from query optimization plans The Lock, Nested Block Join, Sorted Block, and JNBO algorithms have been removed from query optimization plans, and lock nodes no longer appear in the plan. You can view locking information in the scan nodes in the plan.
util_db.ini file deprecated Using the util_db.ini file to specify the password for the DBA user when connecting to the utility database has been deprecated. You can use the -su server option instead. See Using util_db.ini with network database servers (deprecated) and -su server option.
Deprecated Windows CE platforms Support for Windows CE MIPS processors have been removed. For a list of supported platforms, see the SQL Anywhere for PC table in ![[external link]](gif/external.gif)
SQL Anywhere Supported Platforms and Engineering Support Status.