What’s new in SAP SQL Anywhere 11?

Quick overview

SQL Anywhere 11 brought the product into the "modern-DBMS" era on three fronts:

1. Richer SQL & search capabilities (MERGE, full-text, regular expressions);
2. A smarter, leaner engine that can satisfy many queries straight from indexes and stream data in/out of client files;
3. Infrastructure upgrades for governance, scale-out, backup and mobile sync.

In practice, most gains arrive with a simple catalog upgrade; only features like immediate-refresh materialized views or text search need a rebuild to expose new system objects.

1. SQL language & data types

• MERGE now lets you combine tables, views or procedure result-sets in one statement, eliminating multi-step UPSERT scripts.
• Full-text search with CONTAINS brings Google-style term, phrase, proximity and fuzzy queries driven by a dedicated text index.
• Two new search predicates, REGEXP and SIMILAR TO, add Perl-compatible regular-expression filtering for ad-hoc analytics.
• The new OPENSTRING clause lets you query CSV, XML or BLOB data on the fly as if it were a table.

2. Optimizer & kernel performance

• A multiple-index scan algorithm can combine up to four indexes to satisfy complex predicates, often replacing temp-table work-arounds.
• Index-only retrieval means result sets that touch only indexed columns avoid heap reads entirely, shaving latency on reporting workloads.
• Simple statements may now bypass the cost-based optimizer when cheaper heuristic plans are obvious, reducing parse overhead on OLTP bursts.

3. Data loading, extraction & recovery

• LOAD TABLE and UNLOAD can pull from / push to client-side files without staging copies on the server, secured via the CmdSeq protocol.
• Three new logging modes—WITH CONTENT LOGGING, ROW LOGGING and FILE NAME LOGGING—let mirrored systems or point-in-time recovery replay bulk loads without the original data files.
• LOAD TABLE … USING COLUMN|VALUE and UNLOAD … INTO VARIABLE streamline ETL from staging tables or in-memory BLOBs.

4. Materialized views

• Views can now be declared IMMEDIATE to self-refresh on base-table change, ideal for tactical aggregates;
• Manual views refresh faster thanks to a multi-view list option and a WITH SHARE MODE that lets readers run during refresh.

5. Security & governance

• Login policies give DBAs a fine-grained place to enforce password lifetime, lock-out rules or connection caps per user group, with a root policy for defaults.
• Server switch -xd blocks the instance from becoming the "default" on its host, closing a common mis-routing loophole in multi-server estates.

6. Administration, backup & ops

• Parallel server-side archive backups (new WITH CHECKPOINT LOG COPY & MAX WRITE clauses) exploit multi-threaded I/O to shrink maintenance windows.
• DBA scripting is simpler: sa_server_option can now watch and react to option changes, and Mac-based tools shed the old environment-hack requirement.

7. Mobility & synchronization stack

• MobiLink 11 introduces relay-server aware farms, SQL-passthrough downloads and new admin switches (-cs, -lsc, -ss) for multi-node deployments.
• Fresh diagnostics flags (-vm, -vq, -ppv) expose per-phase timings, row values and periodic counters directly in the sync log for easier tuning.
• QAnywhere can now use UltraLite as its message store and ships a standalone agent plus .NET, Java and C# listener APIs for lightweight queueing on devices.
• UltraLite 11 gains LOAD TABLE, VALIDATE and isolation-level options, closes many SQL gaps (multi-table joins, row-traversal APIs) and debuts new C++/Java table APIs.

Suggested next steps

• Upgrade test databases with dbupgrad to enable login policies, text indexes and new catalog views.
• Benchmark multiple-index scans vs. manual hints on query workloads with mixed predicates.
• Move nightly ETL to client-file LOAD/UNLOAD paths to cut SAN hops.
• Plan first parallel archive backup run and adjust retention scripts.
• Pilot MobiLink relay-server farm mode for roaming apps; switch logs to -vm to capture baseline sync phase timings.