Manage, Synchronize and Exchange Data
  1. Home 
  2. SQL Anywhere 
  3. SQL Anywhere 12 

What’s new in SAP SQL Anywhere 12?

Quick overview

SQL Anywhere 12 arrives with three headline shifts:

  1. A new spatial-data stack with round-earth support, SRID catalogues and shapefile loaders;
  2. A smarter, faster core engine — multiple-index scans, index-only retrieval, a modern CPU cost model and lighter-weight locking;
  3. Refreshed security, cloud and tooling that pave the way for OnDemand Edition and large-scale deployments.

Patch level 12.0.1 adds cloud-specific login modes and SHA-2/OSGi-ready client libraries. Most gains are turnkey after an upgrade + catalog rebuild; spatial functions and deadlock events require a dbinit/dbunload-reload to surface the new system objects.

1. Engine performance & optimizer

  • Multiple-index scans (up to four) and automatic index-only retrieval let the optimiser satisfy multi-predicate queries without ever touching the table heap, controlled by a new hint in the WITH clause.
  • A CPU-aware cost model replaces the decade-old heuristic, often rewiring access plans on modern hardware.
  • Plan caching now embraces simple DML that previously bypassed the cache, further reducing parse/optimise overhead.
  • Heavy DDL/DML workloads benefit from re-written locking rules — non-key updates no longer block concurrent foreign-key inserts, and share-mode table locks skip row locks at isolation 2/3.
  • Validation, index deletes and remote-data proxy tables all pick up sotto-voce speed-ups, while packet defaults jump to 7 300 bytes and the new floor is 500 bytes for snappier latency.

2. Spatial, data types & SQL

  • Native spatial support arrives: geometry types, CREATE SPATIAL REFERENCE SYSTEM/UNIT OF MEASURE, round-earth predicates, and a loader proc st_geometry_load_shapefile that turns ESRI files into tables in a single call.
  • INPUT/LOAD TABLE and OPENSTRING gain a FORMAT SHAPEFILE keyword, plus SRID to pin coordinates on ingest.
  • New high-precision TIMESTAMP WITH TIME ZONE (alias DATETIMEOFFSET) and enlarged CHAR/NCHAR/VARCHAR lengths (up to 32 767 chars) make the engine timezone- and LOB-ready.
  • TOP / LIMIT clauses now accept 64-bit arithmetic, and DELETE finally supports table aliases.

3. Security, cloud & connectivity

  • The -kp server switch lets you state a full Kerberos principal (service/host@REALM) instead of relying on default realms — superseding the older -kr option.
  • createcert grows friendly shortcuts, and OPENSTRING … ENCRYPTED means the server can read files produced by UNLOAD … ENCRYPTED without staging clear-text copies.
  • SQL Anywhere OnDemand features land in 12.0.1: CloudAdmin login_mode, cloud-aware connect dialogues and a smarter NodeType parameter that routes traffic to PRIMARY, MIRROR or READONLY copies automatically.
  • Server messages are now categorised/severitised and exposed through sa_server_messages, making it easier to tail just the warnings that matter.

4. Administration & tools

  • Parallel archive backups slash full-backup windows via WITH CHECKPOINT LOG COPY and MAX WRITE n|AUTO clauses.
  • A brand-new START SERVER / STOP SERVER pair replaces the older ENGINE verbs and prepares the ground for cross-platform scripting.
  • The Performance Statistics utility (dbstats) brings Unix the real-time counters Windows admins already enjoy.
  • Compressed-index rebuilds shrink existing databases transparently, and you can now run reporting queries on the mirror while the primary handles OLTP.
  • New dead-lock system event wired to sa_report_deadlocks enables automated post-mortems of blocking storms.

5. Remote data & file handling

  • Remote servers can bypass the OS driver manager via CLASS 'saodbc' USING 'DRIVER=SQL Anywhere Native;…', trimming an extra layer from cross-server joins (doc update in "Remote data access" chapter).
  • LOAD TABLE learns USING COLUMN/USING VALUE to import BLOBs straight from another table or a variable, and it can stream files directly from the client workstation through CmdSeq — no more staging on the host.

6. Mobility & synchronisation

  • The MobiLink 12 engine (mlsrv12) debuts with dynamic cache sizing (-cinit/-cmin/-cmax) and an integrated outbound-enabler protocol (-x oe) for Relay Server.
  • Synchronisation now handles TIMESTAMP WITH TIME ZONE columns and >32 KB character LOBs, plus spatial geometries across Oracle, SQL Server, DB2 and MySQL back-ends.
  • On the device side, UltraLiteJ for Android gains ZLIB compression, end-to-end encryption, restartable HTTP and full cursor navigation — mirroring the BlackBerry API set.

7. Programming interfaces

  • Ruby bindings join the roster, while ASP.NET membership/roles/profile providers let web apps store state in SQL Anywhere instead of SQL Server.
  • The JDBC 4 driver adds PreparedStatement.setClob() and OSGi-bundle metadata for embedded containers.
  • .NET developers get Entity Framework 4.2 Code-First support and a revamped SetupVSPackage that auto-handles GAC and machine.config registration.

8. Behaviour & compatibility notes

  • Default and minimum cache size heuristics scale with max cache; tiny settings are auto-bumped to ⅛ × max.
  • The old START/STOP ENGINE, -kr, JDBC 3 driver and REPLICATE ON/OFF syntax are deprecated; the engine warns at startup and dbunload prunes obsolete catalog bits.
  • CONVERT parsing rules tighten: time strings must now match the format style exactly (e.g. 11:45 AM no longer fits style 14) — watch legacy code paths.

Suggested next steps

  • Rebuild or upgrade test databases to activate spatial catalogues, dead-lock events and compressed index layouts.
  • Benchmark multiple-index scans versus handcrafted hints on your star-schema workloads.
  • Enable mirror read-only routing for BI/reporting tools.
  • Refresh client stacks — switch to JDBC 4/OSGi, Entity Framework 4.2, and Ruby gems.
  • For cloud deployments, trial the OnDemand login flow (CloudAdmin / NodeType) and capture any firewall tweaks early.

Manage, Synchronize and Exchange Data

Discover SQL Anywhere

Try