SQL Anywhere® 11 Introduction February 2009 Version 11.0.1 Copyright and trademarks Copyright © 2009 iAnywhere Solutions, Inc. Portions copyright © 2009 Sybase, Inc. All rights reserved. This documentation is provided AS IS, without warranty or liability of any kind (unless provided by a separate written agreement between you and iAnywhere). 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 iAnywhere is the author or source of the documentation. iAnywhere®, Sybase®, and the marks listed at http://www.sybase.com/detail?id=1011207 are trademarks of Sybase, Inc. or its subsidiaries. ® indicates registration in the United States of America. All other company and product names mentioned may be trademarks of the respective companies with which they are associated. Contents About this book ............................................................................................ v About the SQL Anywhere documentation ............................................................... vi SQL Anywhere 11 Overview ........................................................................ 1 Introducing SQL Anywhere .................................................................................................. 3 SQL Anywhere 11 overview .................................................................................. 4 SQL Anywhere 11 components ............................................................................. 6 Hallmarks of SQL Anywhere 11 ........................................................................... 13 Supported platforms ............................................................................................ 14 Accessibility Enablement option .......................................................................... 15 Overview of data management technologies ....................................................................... 17 The parts of a database system .......................................................................... 18 Relational database concepts .............................................................................. 20 Inside SQL Anywhere .......................................................................................... 25 Choosing between SQL Anywhere and UltraLite databases ............................... 29 Database scenarios ............................................................................................. 30 Multi-tier computing architecture .......................................................................... 32 Running multiple databases on a single database server ................................... 33 ETL features ........................................................................................................ 34 Programming interfaces ....................................................................................... 35 Overview of data exchange technologies ............................................................................ 39 Comparing synchronization technologies ............................................................ 40 Mobile enterprise messaging: QAnywhere .......................................................... 49 Sample Databases ...................................................................................... 53 SQL Anywhere sample database ....................................................................................... 55 About the sample database ................................................................................. 56 Recreate the sample database ............................................................................ 58 The CustDB sample database application .......................................................................... 61 About the CustDB sample database .................................................................... 62 Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 iii SQL Anywhere® 11 - Introduction Getting Started with SQL Anywhere 11 .................................................... 65 Getting started with SQL Anywhere 11 ............................................................................... 67 Getting started ..................................................................................................... 68 Getting started with SQL Anywhere Server ......................................................... 70 Other applications ................................................................................................ 72 Glossary ...................................................................................................... 73 Glossary .......................................................................................................................... 75 Index .......................................................................................................... 105 iv Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 About this book Subject This book introduces SQL Anywhere 11, 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. Audience This book is designed to highlight the main features of SQL Anywhere 11 to help application developers and database administrators assess its scope and functionality. Before you begin This book assumes some familiarity with relational databases. Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 v About this book About the SQL Anywhere documentation The complete SQL Anywhere documentation is available in four formats that contain identical information. ● HTML Help The online Help contains the complete SQL Anywhere documentation, including the books and the context-sensitive help for SQL Anywhere tools. If you are using a Microsoft Windows operating system, the online Help is provided in HTML Help (CHM) format. To access the documentation, choose Start » Programs » SQL Anywhere 11 » Documentation » Online Books. The administration tools use the same online documentation for their Help features. ● Eclipse On Unix platforms, the complete online Help is provided in Eclipse format. To access the documentation, run sadoc from the bin32 or bin64 directory of your SQL Anywhere 11 installation. ● DocCommentXchange DocCommentXchange is a community for accessing and discussing SQL Anywhere documentation. Use DocCommentXchange to: ○ View documentation ○ Check for clarifications users have made to sections of documentation ○ Provide suggestions and corrections to improve documentation for all users in future releases Visit http://dcx.sybase.com. ● PDF The complete set of SQL Anywhere books is provided as a set of Portable Document Format (PDF) files. You must have a PDF reader to view information. To download Adobe Reader, visit http:// get.adobe.com/reader/. To access the PDF documentation on Microsoft Windows operating systems, choose Start » Programs » SQL Anywhere 11 » Documentation » Online Books - PDF Format. To access the PDF documentation on Unix operating systems, use a web browser to open install-dir/ documentation/en/pdf/index.html. About the books in the documentation set The SQL Anywhere documentation consists of the following books: ● SQL Anywhere 11 - Introduction This book introduces SQL Anywhere 11, 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. ● SQL Anywhere 11 - Changes and Upgrading This book describes new features in SQL Anywhere 11 and in previous versions of the software. ● SQL Anywhere Server - Database Administration This book describes how to run, manage, and configure SQL Anywhere databases. It describes database connections, the database server, database vi Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 About the SQL Anywhere documentation files, backup procedures, security, high availability, replication with the Replication Server, and administration utilities and options. ● SQL Anywhere Server - Programming This book describes how to build and deploy database applications using the C, C++, Java, PHP, Perl, Python, and .NET programming languages such as Visual Basic and Visual C#. A variety of programming interfaces such as ADO.NET and ODBC are described. ● SQL Anywhere Server - SQL Reference This book provides reference information for system procedures, and the catalog (system tables and views). It also provides an explanation of the SQL Anywhere implementation of the SQL language (search conditions, syntax, data types, and functions). ● SQL Anywhere Server - SQL Usage This book describes how to design and create databases; how to import, export, and modify data; how to retrieve data; and how to build stored procedures and triggers. ● MobiLink - Getting Started This book introduces MobiLink, a session-based relational-database synchronization system. MobiLink technology allows two-way replication and is well suited to mobile computing environments. ● MobiLink - Client Administration This book describes how to set up, configure, and synchronize MobiLink clients. MobiLink clients can be SQL Anywhere or UltraLite databases. This book also describes the Dbmlsync API, which allows you to integrate synchronization seamlessly into your C++ or .NET client applications. ● MobiLink - Server Administration This book describes how to set up and administer MobiLink applications. ● MobiLink - Server-Initiated Synchronization This book describes MobiLink server-initiated synchronization, a feature that allows the MobiLink server to initiate synchronization or perform actions on remote devices. ● QAnywhere This book describes QAnywhere, which is a messaging platform for mobile, wireless, desktop, and laptop clients. ● SQL Remote This book describes the SQL Remote data replication system for mobile computing, which enables sharing of data between a SQL Anywhere consolidated database and many SQL Anywhere remote databases using an indirect link such as email or file transfer. ● UltraLite - Database Management and Reference This book introduces the UltraLite database system for small devices. ● UltraLite - C and C++ Programming This book describes UltraLite C and C++ programming interfaces. With UltraLite, you can develop and deploy database applications to handheld, mobile, or embedded devices. ● UltraLite - M-Business Anywhere Programming This book describes UltraLite for M-Business Anywhere. With UltraLite for M-Business Anywhere you can develop and deploy web-based database applications to handheld, mobile, or embedded devices, running Palm OS, Windows Mobile, or Windows. ● UltraLite - .NET Programming This book describes UltraLite.NET. With UltraLite.NET you can develop and deploy database applications to computers, or handheld, mobile, or embedded devices. ● UltraLiteJ This book describes UltraLiteJ. With UltraLiteJ, you can develop and deploy database applications in environments that support Java. UltraLiteJ supports BlackBerry smartphones and Java SE environments. UltraLiteJ is based on the iAnywhere UltraLite database product. Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 vii About this book ● Error Messages This book provides a complete listing of SQL Anywhere error messages together with diagnostic information. Documentation conventions This section lists the conventions used in this documentation. Operating systems SQL Anywhere runs on a variety of platforms. In most cases, the software behaves the same on all platforms, but there are variations or limitations. These are commonly based on the underlying operating system (Windows, Unix), and seldom on the particular variant (AIX, Windows Mobile) or version. To simplify references to operating systems, the documentation groups the supported operating systems as follows: ● Windows The Microsoft Windows family includes Windows Vista and Windows XP, used primarily on server, desktop, and laptop computers, and Windows Mobile used on mobile devices. Unless otherwise specified, when the documentation refers to Windows, it refers to all Windows-based platforms, including Windows Mobile. ● Unix Unless otherwise specified, when the documentation refers to Unix, it refers to all Unix-based platforms, including Linux and Mac OS X. Directory and file names In most cases, 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 are the conventions used to simplify the documentation of directory and file names: ● Uppercase and lowercase directory names On Windows and Unix, directory and file names may contain uppercase and lowercase letters. When directories and files are created, the file system preserves letter case. 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. The SQL Anywhere installation contains directories such as Bin32 and Documentation. On Unix, references to directories and files are case sensitive. Mixed case directory and file names are not common. Most use all lowercase letters. The SQL Anywhere installation contains directories such as bin32 and documentation. The documentation uses the Windows forms of directory names. In most cases, you can convert a mixed case directory name to lowercase for the equivalent directory name on Unix. ● Slashes separating directory and file names The documentation uses backslashes as the directory separator. For example, the PDF form of the documentation is found in install-dir\Documentation\en \PDF (Windows form). viii Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 About the SQL Anywhere documentation On Unix, replace the backslash with the forward slash. The PDF documentation is found in install-dir/ documentation/en/pdf. ● 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. For example, on Windows, the network database server is dbsrv11.exe. On Unix, it is dbsrv11. ● install-dir During the installation process, you choose where to install SQL Anywhere. The environment variable SQLANY11 is created and refers to this location. The documentation refers to this location as install-dir. For example, the documentation may refer to the file install-dir\readme.txt. On Windows, this is equivalent to %SQLANY11%\readme.txt. On Unix, this is equivalent to $SQLANY11/readme.txt or $ {SQLANY11}/readme.txt. For more information about the default location of install-dir, see “SQLANY11 environment variable” [SQL Anywhere Server - Database Administration]. ● samples-dir During the installation process, you choose where to install the samples included with SQL Anywhere. The environment variable SQLANYSAMP11 is created and refers to this location. The documentation refers to this location as samples-dir. To open a Windows Explorer window in samples-dir, from the Start menu, choose Programs » SQL Anywhere 11 » Sample Applications And Projects. For more information about the default location of samples-dir, see “SQLANYSAMP11 environment variable” [SQL Anywhere Server - Database Administration]. Command prompts and command shell syntax Most operating systems provide one or more methods of entering commands and parameters using a command shell or command prompt. Windows command prompts include Command Prompt (DOS prompt) and 4NT. Unix command shells include Korn shell and bash. Each shell has features that extend its capabilities beyond simple commands. These features are driven by special characters. The special characters and features vary from one shell to another. Incorrect use of these special characters often results in syntax errors or unexpected behavior. The documentation provides command line examples in a generic form. If these examples contain characters that the shell considers special, the command may require 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. These are some examples of command line syntax that may vary between platforms: ● Parentheses and curly braces Some command line options require a parameter that accepts detailed value specifications in a list. The list is usually 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} Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 ix About this book If both forms result in syntax problems, the entire parameter should be enclosed in quotes as required by the shell: -x "tcpip(host=127.0.0.1)" ● Quotes If you must specify quotes in a parameter value, the quotes may conflict with the traditional use of quotes to enclose the parameter. For example, to specify an encryption key whose value contains double-quotes, you might have to 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. ● Environment variables The documentation refers to setting environment variables. In Windows shells, environment variables are specified using the syntax %ENVVAR%. In Unix shells, environment variables are specified using the syntax $ENVVAR or ${ENVVAR}. Graphic icons The following icons are used in this documentation. ● A client application. ● A database server, such as Sybase SQL Anywhere. ● A database. In some high-level diagrams, the icon may be used to represent both the database and the database server that manages it. ● Replication or synchronization middleware. These assist in sharing data among databases. Examples are the MobiLink server and the SQL Remote Message Agent. x Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1