Main features

Following is a list of the main features introduced in SQL Anywhere version 12.0.0.

  • New spatial data support   The following features have been added in support of the new spatial data capabilities in SQL Anywhere 12.0.0. You must upgrade your database to use this feature.

    Note

    Spatial data support for 32-bit Windows and 32-bit Linux requires a CPU that supports SSE2 instructions. This support is available with Intel Pentium 4 or later (released in 2001) and AMD Opteron or later (released in 2003).

    • SQL statements   The following SQL statement enhancements have been made in support of the spatial feature:

    • Interactive SQL changes   A new viewer tool, the Spatial Viewer, has been added to Interactive SQL to allow you to view spatial geometries. You can query spatial data in the top portion of the viewer, and then see your results represented as an image in the lower portion of the viewer. See View spatial data as images.

      Also, when viewing a result row in Interactive SQL, you can now preview a geometry as a Scalable Vector Graphic (SVG) using the new Spatial Preview tab. See View spatial data as images.

    • New data types, methods, and constructors   New types, methods, and constructors have been added to allow you access, model, and analyze spatial data. See Accessing and manipulating spatial data.

      As well, many spatial compatibility functions have been provided to mimic regular SQL functions when accessing and manipulating spatial data. These functions have been provided for compatibility with other products, and make use of the spatial methods and constructors provided in SQL Anywhere. See Spatial compatibility functions.

    • New functions and system procedures   The following functions and system procedures have been added in support of spatial data in the database:

      • TREAT function   Allows you to change the declared type of a geometry expression to a subtype. See TREAT function [Data type conversion].

      • sa_describe_shapefile system procedure   Describes the names and types of columns contained in a ESRI shapefile. This system feature is for use with the spatial feature. See sa_describe_shapefile system procedure.

      • sa_install_feature system procedure   Installs additional features that were not present in the database when SQL Anywhere was installed. See sa_install_feature system procedure.

      • st_geometry_dump system procedure   Expands a geometry object into a result set with each row representing one of the geometry objects contained in the input. See st_geometry_dump system procedure.

    • Wizards   In Sybase Central, the following wizards have been added in support of the spatial data feature:

      • Create Spatial Reference System wizard   The Create Spatial Reference System wizard allows you to create new spatial reference systems. See Create a spatial reference system.

      • Create Unit Of Measure wizard   The Create Unit Of Measure wizard allows you to create new units of measure for use with spatial data. See Create a unit of measure.

    • Catalog changes   The following changes have been made to the catalog as part of the new spatial data support:

      • SYSSPATIALREFERENCESYSTEM system view   Each row of the SYSSPATIALREFERENCESYSTEM system view describes a spatial reference system defined in the database. See SYSSPATIALREFERENCESYSTEM system view.

      • SYSUNITOFMEASURE system view   Each row of the SYSUNITOFMEASURE system view describes a unit of measure defined in the database. See SYSUNITOFMEASURE system view.

      • ST_GEOMETRY_COLUMNS consolidated view   Each row of the ST_GEOMETRY_COLUMNS system view describes a spatial column defined in the database. See ST_GEOMETRY_COLUMNS consolidated view.

      • ST_SPATIAL_REFERENCE_SYSTEMS consolidated view   Each row of the ST_SPATIAL_REFERENCE_SYSTEMS system view describes a spatial reference system defined in the database. See ST_SPATIAL_REFERENCE_SYSTEMS consolidated view.

      • ST_UNITS_OF_MEASURE consolidated view   Each row of the ST_UNITS_OF_MEASURE system view describes a unit of measure defined in the database. See ST_UNITS_OF_MEASURE consolidated view.

    • Database options and properties   The following database options and properties have been added in support of the spatial data features.

    • SYS_SPATIAL_ADMIN_ROLE group   Membership in this group allows users to create, alter, or drop spatial reference systems and units of measure. See Spatial permissions.

    For more information about SQL Anywhere spatial support, see Getting started with spatial data.

  • Read-only scale-out   You can now use SQL Anywhere in a read-only scale-out system. In this configuration, one database server (the root node) runs a read-write copy of the database, while other database servers run read-only copies of the database (copy nodes) that can be used to offload reporting and other operations that require read access to the database. Read-only scale-out can be used on its own, or with database mirroring. You must upgrade or rebuild existing databases to use this feature.

    A sample has been added in samples-dir\SQLAnywhere\DBMirror that uses a database mirroring system in conjunction with a scale-out system.

    See:

  • Database mirroring enhancements   You can now set up a database mirroring system using SQL statements instead of specifying mirroring settings on the database server command line. You must upgrade or rebuild existing databases to use this feature.

    See:

  • Host connection parameter   The new Host connection parameter takes a host name (or IP address) and optional port number that tells the client where to find the database server. This connection parameter is now the recommended way to connect to database servers running on a different computer than the client. See Host connection parameter.

  • Enhancements to automatic statistics management   SQL Anywhere 12 includes a statistics governor that improves the automatic maintenance of statistics on database columns. The health and usefulness of each statistic in the database is automatically evaluated, and required maintenance is performed so that the statistics are self-monitored and self-healing. Statistics maintenance is performed in the background and does not create a significant load on database server performance. See How the statistics governor maintains statistics.

    The sa_server_option system procedure now supports the following options to help you manage statistics: DropBadStatistics, DropUnusedStatistics, and StatisticsCleaner. See sa_server_option system procedure.

  • Sequences   SQL Anywhere now supports the generation of sequences. Sequences can be used by applications to generate unique key values. Using sequence values can help applications prevent concurrency and performance issues.

    You can also create, edit, and manage sequences using the SQL Anywhere plug-in in Sybase Central. For example, use the Create Sequence Generator Wizard to create a new sequence in the database.

    See also:

    You must upgrade or rebuild existing databases to use sequences.

  • Multiprogramming level enhancements   The network database server (dbsrv12) now automatically controls its multiprogramming level by default. This behavior allows the database server to improve its throughput and adapt to workload changes without DBA intervention.

    When the database server starts, it creates a pool of workers that are used to service requests. The number of workers is the current multiprogramming level of the server. The pool has minimum and maximum limits, and the current multiprogramming level is always within those limits. The DBA can change the minimum and maximum values at start up by using database server options or while the database server is running by using the sa_server_option system procedure.

    The following options have been added to allow you to control the database server's multiprogramming level:

    Database server option sa_server_option value Description
    -gn dbsrv12 server option CurrentMultiProgrammingLevel Sets the initial multiprogramming level of the database server.
    -gna dbsrv12 server option AutoMultiProgrammingLevel Turns on and off dynamic tuning of the database server's multiprogramming level.
    -gnh dbsrv12 server option MaxMultiprogrammingLevel Sets the maximum number of tasks that the database server can execute concurrently.
    -gnl dbsrv12 server option MinMultiProgrammingLevel Sets the minimum number of tasks that the database server can execute concurrently.
    -gns dbsrv12 server option AutoMultiProgrammingLevelStatistics

    Controls whether statistics about the automatic changes to the multiprogramming level appear in the database server message log.

    For more information about the multiprogramming level in SQL Anywhere, see Configuring the database server's multiprogramming level.

  • Immediate materialized views now support outer joins   Materialized views containing OUTER JOINs in their definitions can now be declared immediate. See Restrictions on materialized views.

  • Selecting from DML statements   You can now specify a DML statement in the FROM clause of the SELECT statement. This feature allows you to write SQL queries over a derived table populated by the rows modified by an UPDATE, INSERT, DELETE, or MERGE statement and return values from these updated rows to the application.

    The most common use of this feature is to verify or validate the values of rows that have been modified by the application. Previously, the only way to accomplish this would be through the use of a trigger and multiple SQL statements. See FROM clause and Executing a SELECT over a DML statement.

  • Full text search feature now supports external prefilter and term breaker libraries   A new API has been added to allow you to connect to external external prefilter and term breaker libraries when creating and updating full text indexes. This means you can can take document formats like XML, PDF, and Word and remove unwanted terms and content before indexing their content. Some sample prefilter and term breaker libraries are included to help you design your own, or you can use 3rd party libraries. See External term breaker and prefilter libraries.

    If Microsoft Office is installed on the system running the database server then IFilters for Office documents such as Word and Excel are available. If the server has Acrobat Reader installed, then a PDF IFilter is likely available.

    The PREFILTER EXTERNAL NAME clause and TERM BREAKER EXTERNAL NAME clause have been added to the ALTER TEXT CONFIGURATION statement to allow you to specify the name and location of your external libraries. See ALTER TEXT CONFIGURATION statement.

    Additionally, a new API is provided for working with external prefilter and term breaker libraries.

    The ISYSTEXTCONFIG system table has been modified to hold information about the entry points and the external libraries used for tokenizing and/or prefiltering. Specifically, the existing prefilter column data type has changed to be a LONG VARCHAR to hold the entry points and library name for an external prefilter library. A new LONG VARCHAR column, external_term_breaker, has been added to hold the entry points and library name for an external term breaker library. See SYSTEXTCONFIG system view.

    You must upgrade your database to use external prefilter and term breaker libraries.

  • Checksum enhancements   The database server now determines whether to create write checksums for databases pages (checksums that are created only when the pages are written to disk) based on the database version. By default, version 10 and 11 databases do not have global checksums enabled, and version 12 databases have global checksums enabled. When you start an older database on a version 12 database server, the default behavior of the database server is to enable write checksums. For version 12 databases, the database server's default behavior is to not enable write checksums because by default version 12 databases have global checksums enabled. See Checksums enabled by default for new databases.

    You can use the CHECKSUM clause of the START DATABASE statement or the -wc option when starting a database or database server to change the database server behavior for write checksums. See -wc dbeng12/dbsrv12 database option, -wc dbeng12/dbsrv12 server option, and START DATABASE statement.

    You can disable checksums for a database using the CHECKSUM clause of the ALTER DATABASE statement. See ALTER DATABASE statement.