SQL Anywhere® 16 Introduction Version 16.0 February 2013 Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 Version 16.0 February 2013 © 2013 SAP AG or an SAP affiliate company. All rights reserved. You may use, print, reproduce, and distribute this documentation (in whole or in part) subject to the following conditions: 1) you must retain this and all other proprietary notices, on all copies of the documentation or portions thereof, 2) you may not modify the documentation, 3) you may not do anything to indicate that you or anyone other than SAP is the author or source of the documentation. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. National product specifications may vary. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Please see http://www.sap.com/corporate-en/legal/copyright/index.epx#trademark. Contents About this book ..................................................................................... v About the SQL Anywhere documentation ......................................................... v SQL Anywhere 16 overview .................................................................. 1 SQL Anywhere in frontline environments ......................................................... 1 Editions and licensing ......................................................................................... 2 Separately licensed components ....................................................................... 3 Hallmarks of SQL Anywhere 16 .......................................................................... 5 Supported platforms ........................................................................................... 6 Accessibility ......................................................................................................... 7 Data management technologies ......................................................... 15 The parts of a database system ....................................................................... 15 Relational database concepts .......................................................................... 16 Inside SQL Anywhere ........................................................................................ 20 SQL Anywhere and UltraLite target platforms ................................................ 23 Database scenarios ........................................................................................... 24 Multi-tier computing architecture ..................................................................... 25 Multiple databases running on a single database server .............................. 26 ETL features ....................................................................................................... 27 Programming interfaces ................................................................................... 28 Overview of data exchange technologies ......................................... 31 Synchronization technology comparison ....................................................... 31 Propagation methods ........................................................................................ 36 Synchronization technology considerations .................................................. 37 Design and management tools ........................................................... 41 Sample databases ............................................................................... 45 Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 iii SQL Anywhere® 16 - Introduction SQL Anywhere sample database ..................................................................... 45 The CustDB sample database application ...................................................... 48 List of SQL Anywhere tutorials .......................................................... 51 Resources for getting started with SQL Anywhere 16 ..................... 55 Resources for getting started with SQL Anywhere Server ............................ 56 Frequently asked questions - SQL Anywhere .................................. 59 Index ..................................................................................................... 65 iv Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 About this book This book introduces SQL Anywhere 16, a comprehensive package that provides data management and data exchange, enabling the rapid development of database-powered applications for server, desktop, mobile, and remote office environments. About the SQL Anywhere documentation The complete SQL Anywhere documentation is available in four formats: Documentation conventions This section lists the conventions used in this documentation. Operating systems To simplify references to operating systems, the documentation groups the supported operating systems as follows: ● Windows ● Unix Directory and file names Usually references to directory and file names are similar on all supported platforms, with simple transformations between the various forms. In these cases, Windows conventions are used. Where the details are more complex, the documentation shows all relevant forms. These conventions are used to simplify the documentation of directory and file names: ● Uppercase and lowercase directory names On Windows and Unix, directory and file names can contain uppercase and lowercase letters. On Windows, references to directories and files are not case sensitive. Mixed case directory and file names are common, but it is common to refer to them using all lowercase letters. On Unix, references to directories and files are case sensitive. Mixed case directory and file names are not common. Most use all lowercase letters. ● Slashes separating directory and file names The documentation uses backslashes (\) as the directory separator. On Unix, replace the backslash with the forward slash (/). ● Executable files The documentation shows executable file names using Windows conventions, with a suffix such as .exe or .bat. On Unix, executable file names have no suffix. ● 32-bit and 64-bit versions The documentation does not always distinguish between 32-bit and 64-bit versions of the software. In some cases, the documentation provides an example file path that Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 v About this book includes bin32 or bin64. The two are interchangeable, and depend on the version of the software that was installed. For example, the file paths C:\Program Files\SQL Anywhere 16\bin32 and C:\Program Files\SQL Anywhere 16\bin64 are equivalent. Command prompts and command shell syntax The documentation provides command line examples in a generic form. If these examples contain characters that the shell considers special, the command requires modification for the specific shell. The modifications are beyond the scope of this documentation, but generally, use quotes around the parameters containing those characters or use an escape character before the special characters. ● Parentheses and curly braces Some command line options require a parameter that accepts detailed value specifications in a list. The list is enclosed with parentheses or curly braces. The documentation uses parentheses. For example: -x tcpip(host=127.0.0.1) Where parentheses cause syntax problems, substitute curly braces: -x tcpip{host=127.0.0.1} If both forms result in syntax problems, enclose the entire parameter in quotes as required by the shell: -x "tcpip(host=127.0.0.1)" ● Semicolons On Unix, enclose semicolons in quotes. ● Quotes If you must specify quotes in a parameter value, the quotes can conflict with the traditional use of quotes to enclose the parameter. For example, to specify an encryption key whose value contains double quotes, you can enclose the key in quotes and then escape the embedded quote: -ek "my \"secret\" key" In many shells, the value of the key would be my "secret" key. Online resources and additional help Resource Description SAP Sybase SQL The SAP Sybase SQL Anywhere Community has links to blogs maintained Anywhere Communi- by executives, employees, and developers who exchange views and ideas ty about using SQL Anywhere and related technologies. See http://scn.sap.com/ community/sybase-sql-anywhere/content. vi Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 About the SQL Anywhere documentation Resource Description SQL Anywhere Fo- The SQL Anywhere Forum is a website where you can ask and answer ques- rum tions about the SQL Anywhere software, and comment and vote on the ques- tions of others and their answers. When you post a message on the forum, always provide details about your problem, including the build number of your version of SQL Anywhere. You can find this information by running the following command: dbsrv16 -v. See http://sqlanywhere-forum.sybase.com. There is also a community discussion page at http://scn.sap.com/community/ sybase-sql-anywhere/content?filterID=content~objecttype~object- type[thread]#. DocCommentX- DocCommentXchange is a website where you can read the SAP Sybase SQL change Anywhere documentation, leave questions or feedback on the documentation, and read the documentation questions and replies of others. See http:// dcx.sybase.com. SQL Anywhere Sup- Platforms supported by SAP Sybase SQL Anywhere. See http:// ported Platforms and www.sybase.com/detail?id=1002288. Engineering Support Status SQL Anywhere Tech The SQL Anywhere Tech Corner gives developers easy access to product Corner technical documentation. You can browse technical white papers, FAQs, tech notes, downloads, techcasts, and more to find answers to your questions as well as solutions to many common issues. See http://www.sybase.com/devel- oper/library/sql-anywhere-techcorner. SQL Anywhere .NET Get started and get answers to specific questions regarding SQL Anywhere Developer Center and .NET development. See www.sybase.com/developer/library/sql-any- where-techcorner/microsoft-net. PHP Developer Cen- An introduction to using the PHP (PHP Hypertext Preprocessor) scripting ter language to query your database. See www.sybase.com/developer/library/sql- anywhere-techcorner/php. SQL Anywhere Win- Get started and get answers to specific questions regarding SQL Anywhere dows Mobile Devel- and Windows Mobile development. See www.sybase.com/developer/library/ oper Center sql-anywhere-techcorner/windows-mobile. Top 10 recommended whitepapers ● SQL Anywhere I/O Requirements for Windows and Linux http://scn.sap.com/docs/ DOC-34090 ● Capacity Planning with SQL Anywhere http://scn.sap.com/docs/DOC-35069 ● Diagnosing Application Performance Issues with SQL Anywhere http://scn.sap.com/docs/ DOC-35601 Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 vii About this book ● Analytic Functions in SQL Anywhere http://scn.sap.com/docs/DOC-35603 ● Optimizing Adaptive Server Anywhere Performance Over a WAN http://scn.sap.com/docs/ DOC-35605 ● Semantics and Compatibility of Transact-SQL Outer Joins http://scn.sap.com/docs/ DOC-35606 Earlier versions of the documentation A compilation of new features and behavior changes from versions 6.0 to 10.0.1 and later can be found at http://dcx.sybase.com/html/dbwnen10/dbwnen10.html. Full documentation for SQL Anywhere versions 10.0.1 up to the current release can be found at http:// dcx.sybase.com. From version 8.0 to 9.0.2, SQL Anywhere was called SQL Anywhere Studio. Full documentation for these versions can be found at http://manuals.sybase.com/onlinebooks/group-sas/ @Generic__CollectionView;pt=group-awarc. Prior to version 8, SQL Anywhere was called Adaptive Server Anywhere. Full documentation for version 6 can be found at: http://manuals.sybase.com/onlinebooks/group-awarc/ @Generic__CollectionView;pt=group-sas. viii Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 SQL Anywhere 16 overview SQL Anywhere is a comprehensive package that provides technologies for data management and enterprise data exchange, enabling the rapid development of database-powered applications for server, desktop, mobile, and remote office environments. SQL Anywhere offers: ● Data management technologies SQL Anywhere provides enterprise-caliber databases that are designed to handle the challenges of operating in many different frontline environments—from a high performance database server deployed with an independent software vendor application, to a mobile database that can be deployed to tens of thousands of handheld devices within the enterprise. ● Data exchange technologies SQL Anywhere offers several data exchange technologies to handle the complexities of exchanging data across unreliable wired and wireless networks to back-end databases, application servers, and messaging systems. In addition, SQL Anywhere mobile messaging and synchronization technologies guarantee secure message delivery for distributed and mobile computing. ● Design and management tools SQL Anywhere includes a suite of tools to improve the design and development of database-driven applications, and to simplify the management of databases and data exchange environments. See also ● “Relational database concepts” on page 16 ● “Overview of data exchange technologies” on page 31 ● “Design and management tools” on page 41 SQL Anywhere in frontline environments SQL Anywhere technologies are used in many different ways by over 10000 customers. Four common uses of SQL Anywhere are: ● Client-server applications Whether it is 5, 50, 500 users or more, SQL Anywhere is a powerful database solution for server applications, providing high performance out of the box, with low maintenance and cost. SQL Anywhere easily scales to support hundreds of active users, hundreds of gigabytes of data, and hundreds of millions of rows. Yet many ease-of-use and administration features ensure that costs stay down as performance scales up. This deployment model works best when the majority of users are connected to the network. ● Desktop applications SQL Anywhere delivers enterprise-caliber features, without the bulky characteristics of an enterprise database. Its robust reliability and performance, along with highly efficient usage of memory and system resources, ensure that the database can be hidden from laptop and desktop users. Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0 1 SQL Anywhere 16 overview Organizations embed SQL Anywhere databases in their applications because SQL Anywhere databases are built for use in widely deployed, minimally administered environments, and require minimal memory and disk space. ● Remote office applications SQL Anywhere data exchange architectures address the challenges of managing and sending data within and between offices and workers that are dispersed geographically. Companies choose SQL Anywhere database and data exchange technologies to provide remote workers with the data they need to run their operations effectively, while providing the central office with the critical information that gives the pulse of the business. ● Mobile and wireless applications Recognized as the industry's leading mobile database, SQL Anywhere gives mobile workers the ability to access their data and corporate applications. Regardless of connection or application type, SQL Anywhere data exchange technologies ensure that mobile workers stay productive by having the information they need, when they need it. Workers can access information and queue up transactions offline, reducing communications costs while increasing application and battery performance. Companies depend on SQL Anywhere for reliable management of data and mobile applications running on laptops, handheld devices, and smartphones. See also ● “Client/server applications” on page 25 ● “Desktop applications and embedded databases” on page 24 ● “Consolidated and remote databases” on page 34. ● “UltraLite overview” [UltraLite - Database Management and Reference] ● “SQL Anywhere for Windows Mobile” [SQL Anywhere Server - Database Administration] Editions and licensing SQL Anywhere offers various editions that include certain separately licensed components and that can restrict the number of CPUs used by the database server. For more information about editions, see http:// www.sybase.com/detail?id=1068247. Licensing and CPUs Per-seat licensing A per-seat license restricts the number of client connections to the database server. With per-seat licensing, the network database server can use all CPUs available on your computer, but not more than the maximum allowed by the SQL Anywhere edition you are running. If a CPU contains multiple cores or threads, all cores and threads of the CPU may be used. The personal database server is limited to four cores on one CPU. If a core contains multiple threads, all threads of that core may be used. CPU-based licensing With CPU-based licensing, the network database server uses up to the number of CPUs specified in your license, but not more than the maximum allowed by the SQL Anywhere edition you are running. If a CPU contains multiple cores or threads, all cores and threads of the CPU may be used. 2 Copyright © 2013, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0