New features

Following is a list of additions to SQL Anywhere databases and database servers introduced in version 10.0.1.

Encryption enhancements

The following changes have been made to enhance support for encryption.

  • Extension to the CREATE DATABASE statement ENCRYPTION clause   The syntax for the ENCRYPTION clause of the CREATE DATABASE statement has been extended to allow you to specify SIMPLE as an encryption type. Additionally, you can specify the encryption key and the algorithm in any order. See CREATE DATABASE statement.

  • Enhancements to dbinit and dbunload -ea option    The -ea option for dbinit and dbunload now accepts both none and simple as encryption types. Specifying none results in no encryption. Specifying simple results in simple encryption. Also, the default encryption type has changed, depending on whether the -ek, -et, or -ep options are specified with -ea. See Initialization utility (dbinit), and Unload utility (dbunload).

    The -e option is deprecated. See Behavior changes and deprecated features.

  • Strong encryption on Mac OS X   You can now encrypt client/server communications using RSA encryption on Mac OS X. See Encrypting SQL Anywhere client/server communications.

Support for client statement caching

Client statement caching is now supported and enabled by default, so that when the same SQL text is prepared and dropped repeatedly, the client caches the statement, leaving it prepared on the server after it has been dropped by the application. This saves the database server the extra work of dropping and re-preparing the statement. Both a version 10.0.1 client library and a version 10.0.1 database server are required to use client statement caching.

The following changes have been made to provide support for client statement caching.

  • max_client_statements_cached option   This option specifies the maximum number of statements which can remain cached (prepared) on the database server even though they have been dropped by the application. See max_client_statements_cached option [database].

  • New connection and server properties   The ClientStmtCacheHits, ClientStmtCacheMisses, and max_client_statements_cached properties have been added. See Connection-level properties and Server-level properties.

  • New request statistics   The Statement Cache Hits and Statement Cache Misses statistics have been added. See Request statistics.

SQL Flagger enhancements

The SQL Flagger feature has been enhanced to allow better detection of compatibility, and to add support for newer standards. For example, you can now test compatibility with a specific SQL standard, or compatibility with UltraLite SQL.

To support these enhancements, the following changes have been made:

  • New SQLFLAGGER function   You can use the new SQLFLAGGER function to test that a SQL statement conforms to a specified SQL standard, without actually running the statement. See SQLFLAGGER function [Miscellaneous].

  • New sa_ansi_standard_packages system procedure   Using the new sa_ansi_standard_packages system procedure, you can specify a SQL standard and a SQL statement, and obtain the list of non-core SQL extensions that would be used during the execution of the statement. See sa_ansi_standard_packages system procedure.

    You must upgrade your database to use this feature. See Upgrading version 10.0.0 and later databases.

  • New values for the sql_flagger_error_level and sql_flagger_warning_level database options   Several new values are available for the sql_flagger_error_level and sql_flagger_warning_level database options to support the SQL/1999 and SQL/2003 standards. See sql_flagger_error_level option [compatibility] and sql_flagger_warning_level option [compatibility].

  • New values for the SQL preprocessor (sqlpp) -e and -w options   Several new values are available for the -e and -w options of the SQL preprocessor (sqlpp) to support the SQL/1999 and SQL/2003 standards. See SQL preprocessor.

SQL statements

The following enhancements have been made to SQL statements and functions.

  • START DATABASE statement enhancement   The START DATABASE statement now supports a DIRECTORY clause that lets you specify the directory where the database's dbspace files are located. See START DATABASE statement.

  • INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements include an OPTION clause   The INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements support an OPTION clause that controls how materialized views are used by the statement, specifies how the query is optimized, and can override the settings of the following database options:

    • isolation_level
    • max_query_tasks
    • optimization_goal
    • optimization_level
    • optimization_workload

    See:

  • HTML_DECODE function   The HTML_DECODE function now decodes more Unicode codepoints given as numeric entities, such as the trademark symbol (™). If a codepoint cannot be represented in the database character set, it is left in its codepoint form. Previously, codepoints less than 0x7F were converted to characters (for some character sets, codepoints less than 0xFF were converted to characters), while all other codepoints remained in their codepoint form. See HTML_DECODE function [Miscellaneous].

Support for collation tailoring

SQL Anywhere now supports collation tailoring when creating a database. The following changes have been made to support collation tailoring:

  • Enhancements to CREATE DATABASE statement   When creating a database using the CREATE DATABASE statement, or the Initialization utility (dbinit), you can now specify tailoring options for additional control over the sorting and comparing of characters.

    For the CREATE DATABASE statement, collation tailoring is supported using the COLLATION and NCHAR COLLATION clauses. See CREATE DATABASE statement.

    For the Initialization utility, collation tailoring is supported using the -z and -zn options. See Initialization utility (dbinit).

    Note

    Databases created with collation tailoring options cannot be started using a pre-10.0.1 database server.

    If you want to use collation tailoring in an existing database, you must create a new version 10.0.1 database that supports collation tailoring, unload the existing database, and then reload the database into the new version 10.0.1 database. See Rebuilding version 10.0.0 and later databases.

  • New HasCollationTailoring database property   A new database property, HasCollationTailoring, indicates whether tailoring support was enabled when creating the database. See Database-level properties.

  • New extended property values   The following new DB_EXTENDED_PROPERTY values are available when querying the Collation, NcharCollation, and CatalogCollation database properties: CaseSensitivity, AccentSensitivity, PunctuationSensitivity, Properties, and Specification. See DB_EXTENDED_PROPERTY function [System].

  • Enhancements to the SORTKEY and COMPARE functions   In addition to accepting a collation name as a parameter, the SORTKEY and COMPARE functions now accept the same parenthesized set of collation tailoring options as the CREATE DATABASE statement. See SORTKEY function [String] and COMPARE function [String].

Enhancements to web services

The following enhancements have been made to improve the configurability of HTTP and SOAP headers:

  • Improved configurability   The new SET clause of the CREATE PROCEDURE and CREATE FUNCTION statements lets you modify the following options for the HTTP and SOAP protocols: the HTTP version used by the client, whether to use chunking, and, in the case of SOAP requests, the name of the SOAP operation to call, if it is different from the name of the procedure or function. See CREATE PROCEDURE statement.

  • HTTP header specification   The syntax for the HEADER clause of the CREATE PROCEDURE and CREATE FUNCTION statements has been extended to allow you to suppress a given HTTP request header, or to provide an empty value for it. This functionality extends to HTTP request headers that are generated automatically, which were not modifiable in previous releases. See CREATE PROCEDURE statement and Modifying HTTP headers.

  • Support for data types for the SOAP:RPC client   Data typing can be enabled using the DATATYPE clause of the CREATE SERVICE statement. Data type information is included in the XML encoding of parameter input and result set output or responses for all SOAP service formats. This simplifies parameter passing from SOAP toolkits by not requiring client code to explicitly convert parameters to Strings. See Working with data types.

  • HTTPS supported on Mac OS X   In previous releases, only the HTTP protocol was supported for Mac OS X. You can now use HTTPS when running the SQL Anywhere database server as a web server on Mac OS X. See -xs server option and SQL Anywhere web services.

Enhancements to database mirroring

The following enhancements have been added to the database mirroring feature:

SQL Anywhere plug-in
  • Triggers folder column names changed   In the Triggers folder, the Table Name and Table Owner columns have been replaced with Object Name, Object Owner, and Object Type columns. The Object Type column does not appear by default, but can be displayed by choosing View » Choose Columns.

  • Triggers tab added to the View Properties window   The properties window for non-materialized views now has a Triggers tab that lists the view's INSTEAD OF triggers.

  • INSTEAD OF trigger support added to Create Trigger wizard   Several enhancements have been made to the Create Trigger wizard to support INSTEAD OF triggers, including the option of choosing whether you are creating a trigger for a table or a non-materialized view. See Creating triggers.

  • Collation tailoring support added to Create Database wizard   The Create Database wizard now includes a Collation Tailoring page if the selected database server is a version 10.0.1 or later server, or if you have chosen to create the database on the local computer by starting a new database server.

Miscellaneous enhancements
  • Plan caching for simple DML statements   Plan caching has been extended to include INSERT, UPDATE, and DELETE statements that qualify for query bypass (simple statements). See Plan caching.

  • Materialized views with left and right outer joins now eligible for use during cost-based optimization   Previously, left and right outer joins were allowed in the definition of a materialized view. However, this disqualified the materialized view for use in cost-based optimization. Now, materialized views that have left or right outer joins can be used during cost-based optimization. See Improving performance with materialized views.

  • Support for INSTEAD OF triggers   A BEFORE or AFTER trigger fires before or after the triggering operation, respectively. An INSTEAD OF trigger replaces the triggering operation. INSTEAD OF triggers can give you more control over the trigger's behavior during insert, update, or delete operations. See CREATE TRIGGER statement.

  • DBTools enhancement   You can now determine whether a database was created using SQL Anywhere 10.0.0, or a prior version, without starting the database, by using the DBCreatedVersion function. See DBCreatedVersion function.

  • OLAP enhancements   Two new window aggregate functions, FIRST_VALUE and LAST_VALUE, are now supported. These functions return the first or last value, respectively, of a window, eliminating the need to return these values using self-joins. You can then use these values as baselines in further calculations performed on the window. See FIRST_VALUE function [Aggregate] and LAST_VALUE function [Aggregate].

  • Enhanced Unix support for IPv6   On Unix you can specify either an interface identifier or interface name as part of the IPv6 address. On Linux (kernel 2.6.13 and later), an interface identifier is required when you specify an IP address on the client or server (for example, when using the HOST=, MYIP=, or BROADCAST= TCP protocol options). See IPv6 support in SQL Anywhere.

  • Support for TDS DATE and TIME data types   The TDS DATE and TDS TIME data types were recently introduced into TDS clients. Applications that use Open Client 15 or newer versions or EBFs of jConnect can now fetch date and time columns as TDS DATE or TDS TIME values instead of TDS DATETIME.

    SQL Anywhere has been enhanced so that TDS-based applications can fetch date and time data as TDS DATE and TDS TIME values. Applications that use older versions of Open Client or jConnect will continue to fetch date and time data as TDS DATETIME. Note that non-TDS-based applications (applications that use embedded SQL, ODBC, or the iAnywhere JDBC driver) have always been able to fetch date and time data as date and time values.

  • New dbinit option to list available character set encodings   Use the Initialization utility (dbinit) -le option to list the available character set encodings for a database. See Initialization utility (dbinit).

  • New -ds server option   The -ds server option allows you to specify the directory where the dbspace files for a database are located. See -ds database option.

  • SADbType.Xml data type   The SADbType.Xml enumeration constant has been added to the SQL Anywhere .NET provider.

  • Units are supported for dynamic traps for the SQL Anywhere SNMP Extension Agent   When setting dynamic traps, you can now use k, m, g, or t to specify units of kilobytes, megabytes, gigabytes, or terabytes when specifying a numeric value for the trap. See Creating dynamic traps.

  • Creating ODBC data sources for the iAnywhere Solutions Oracle driver    You can now use the Data Source utility (dbdsn) to create ODBC data sources for the iAnywhere Solutions Oracle driver by specifying the -or option. See Data Source utility (dbdsn).

  • Enhancement to window for submitting error reports   The dbsupport window that prompts you to submit an error report to iAnywhere now includes a View Error Report button, so you can view the information contained in the error report before submitting it.