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.
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:
SHAPEFILE clause A new SHAPEFILE format option is available for the OPENSTRING subclause of the FROM clause. See FROM clause.
As well, a new SHAPEFILE format option is available for the FORMAT clause of the LOAD TABLE statement. See LOAD TABLE statement.
CREATE SPATIAL REFERENCE SYSTEM statement Creates or replaces a spatial reference system. See CREATE SPATIAL REFERENCE SYSTEM statement.
ALTER SPATIAL REFERENCE SYSTEM statement Changes the settings of an existing spatial reference system. See the Remarks section for considerations before altering a spatial reference system. See ALTER SPATIAL REFERENCE SYSTEM statement.
DROP SPATIAL REFERENCE SYSTEM statement Drops a spatial reference system. See DROP SPATIAL REFERENCE SYSTEM statement.
CREATE SPATIAL UNIT OF MEASURE statement Creates or replaces a spatial unit of measurement. See CREATE SPATIAL UNIT OF MEASURE statement.
DROP SPATIAL UNIT OF MEASURE statement Drops a spatial unit of measurement. See DROP SPATIAL UNIT OF MEASURE statement.
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 Viewing spatial data as images (Interactive SQL).
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 Viewing spatial data as images (Interactive SQL).
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 Creating a spatial reference system (Sybase Central).
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 Creating a unit of measure (Sybase Central).
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.
st_geometry_asbinary_format option Controls how spatial values are converted from a geometry to binary. See st_geometry_asbinary_format option.
st_geometry_astext_format option Controls how spatial values are converted from a geometry to text. See st_geometry_astext_format option.
st_geometry_asxml_format option Controls how spatial values are converted from a geometry to XML. See st_geometry_asxml_format option.
st_geometry_describe_type option Controls how spatial values are described. See st_geometry_describe_type option.
st_geometry_on_invalid option Controls the behavior when a geometry fails basic validation. See st_geometry_on_invalid option.
st_geometry_asbinary_format connection property Returns a value that indicates how spatial values are converted from a geometry to binary. See st_geometry_asbinary_format connection property.
st_geometry_astext_format connection property Returns a value that indicates how spatial values are converted from a geometry to text. See st_geometry_astext_format connection property.
st_geometry_asxml_format connection property Returns a value that indicates how spatial values are converted from a geometry to xml. See st_geometry_asxml_format connection property.
st_geometry_describe_type connection property Returns a value that indicates how spatial values are described to the client. See st_geometry_describe_type connection property.
st_geometry_on_invalid connection property Returns a value that indicates the behavior when a geometry fails basic validation. See st_geometry_on_invalid connection property.
SYS_SPATIAL_ADMIN_ROLE group Membership in this group allows users to create, alter, or drop spatial reference systems and units of measure. See Grant spatial permissions.
For more information about SQL Anywhere spatial support, see Using 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 %SQLANYSAMP12%\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 for 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 Database server configuration of the multiprogramming level.
Immediate materialized views now support outer joins Materialized views containing OUTER JOINs in their definitions can now be declared immediate. See Materialized views restrictions.
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 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 prefilter and term breaker libraries when creating and updating full text indexes. This means you can take document formats like XML, PDF, and Word and remove unwanted tags and metadata before indexing their content. Sample term breaker libraries can be used to do language- or application-specific term breaking. The sample prefilter and term breaker libraries are included to help you design your own, or you can use third-party libraries. See Advanced: 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.
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.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |