SQL Anywhere® Server SQL Usage

874 Pages·2006·4.99 MB·English

SQL Anywhere® Server
SQL Usage
Published: October 2006
Copyright and trademarks
Copyright © 2006 iAnywhere Solutions, Inc. Portions copyright © 2006 Sybase, Inc. All rights reserved.
iAnywhere Solutions, Inc. is a subsidiary of Sybase, Inc.
iAnywhere grants you permission to use this document for your own informational, educational, and other non-commercial purposes; provided
that (1) you include this and all other copyright and proprietary notices in the document in all copies; (2) you do not attempt to "pass-off" the
document as your own; and (3) you do not modify the document. You may not publish or distribute the document or any portion thereof without
the express prior written consent of iAnywhere.
This document is not a commitment on the part of iAnywhere to do or refrain from any activity, and iAnywhere may change the content of
this document at its sole discretion without notice. Except as otherwise provided in a written agreement between you and iAnywhere, this
document is provided “as is”, and iAnywhere assumes no liability for its use or any inaccuracies it may contain.
iAnywhere®, Sybase®, and the marks listed at http://www.ianywhere.com/trademarks are trademarks of Sybase, Inc. or its subsidiaries. ®
indicates registration in the United States of America.
Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.
All other company and product names mentioned may be trademarks of the respective companies with which they are associated.
Contents
About This Manual ...................................................................................... ix
SQL Anywhere documentation .................................................................................. x
Documentation conventions ................................................................................... xiii
Finding out more and providing feedback ............................................................ xvii
I. Designing and Creating Databases ......................................................... 1
Designing Your Database ........................................................................................... 3
Introduction ............................................................................................................ 4
Database design concepts .................................................................................... 5
The design process ............................................................................................. 11
Designing the database table properties ............................................................. 24
Working with Database Objects ............................................................................... 29
Introduction .......................................................................................................... 30
Working with databases ....................................................................................... 31
Working with tables .............................................................................................. 42
Working with views .............................................................................................. 56
Working with indexes ........................................................................................... 80
Working with temporary tables ............................................................................ 87
Ensuring Data Integrity ............................................................................................. 89
Data integrity overview ........................................................................................ 90
Using column defaults ......................................................................................... 93
Using table and column constraints ..................................................................... 99
Using domains ................................................................................................... 103
Enforcing entity and referential integrity ............................................................ 106
Integrity rules in the system tables .................................................................... 110
Using Transactions and Isolation Levels .............................................................. 111
Introduction to transactions ................................................................................ 112
Isolation levels and consistency ........................................................................ 116
Transaction blocking and deadlock ................................................................... 130
Choosing isolation levels ................................................................................... 133
Isolation level tutorials ....................................................................................... 137
How locking works ............................................................................................. 155
Copyright © 2006, iAnywhere Solutions, Inc. iii
SQL Anywhere® Server - SQL Usage
Particular concurrency issues ............................................................................ 168
Summary ........................................................................................................... 170
Tutorial: Creating a SQL Anywhere Database ...................................................... 173
Lesson 1: Create a database file ....................................................................... 174
Lesson 2: Connect to the database ................................................................... 175
Lesson 3: Create a table .................................................................................... 177
Lesson 4: Set column properties ....................................................................... 179
Lesson 5: Create relationships between tables using foreign keys ................... 181
Summary ........................................................................................................... 182
II. Monitoring and Improving Database Performance ........................... 183
Monitoring and Improving Performance ............................................................... 185
Overview ............................................................................................................ 186
Application profiling ............................................................................................ 188
Advanced application profiling using diagnostic tracing .................................... 200
Other diagnostic tools and techniques ............................................................... 218
Monitoring database performance ..................................................................... 225
Performance improvement tips .......................................................................... 238
III. Querying and Modifying Data ............................................................. 261
Queries: Selecting Data from a Table .................................................................... 263
Query overview .................................................................................................. 264
The SELECT list: specifying columns ................................................................ 267
The FROM clause: specifying tables ................................................................. 275
The WHERE clause: specifying rows ................................................................ 276
The ORDER BY clause: ordering results ........................................................... 288
Summarizing data .............................................................................................. 291
Summarizing, Grouping, and Sorting Query Results .......................................... 295
Summarizing query results using aggregate functions ...................................... 296
The GROUP BY clause: organizing query results into groups .......................... 301
Understanding GROUP BY ............................................................................... 303
The HAVING clause: selecting groups of data .................................................. 307
The ORDER BY clause: sorting query results ................................................... 309
iv Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Usage
Performing set operations on query results with UNION, INTERSECT, and
EXCEPT ............................................................................................................ 312
Standards and compatibility ............................................................................... 318
Joins: Retrieving Data from Several Tables ......................................................... 321
Introduction ........................................................................................................ 322
How joins work ................................................................................................... 323
Joins overview ................................................................................................... 324
Explicit join conditions (the ON clause) ............................................................. 329
Cross joins ......................................................................................................... 333
Inner and outer joins .......................................................................................... 334
Specialized joins ................................................................................................ 341
Natural joins ....................................................................................................... 348
Key joins ............................................................................................................ 352
Common Table Expressions .................................................................................. 365
About common table expressions ...................................................................... 366
Typical applications of common table expressions ............................................ 369
Recursive common table expressions ............................................................... 372
Parts explosion problems .................................................................................. 374
Data type declarations in recursive common table expressions ........................ 377
Least distance problem ...................................................................................... 378
Using multiple recursive common table expressions ......................................... 381
OLAP Support .......................................................................................................... 383
Overview of OLAP functionality ......................................................................... 384
GROUP BY clause extensions .......................................................................... 386
Window functions ............................................................................................... 395
Using Subqueries .................................................................................................... 427
Introduction to subqueries ................................................................................. 428
Using subqueries in the WHERE clause ........................................................... 434
Subqueries in the HAVING clause ..................................................................... 435
Subquery comparison test ................................................................................. 437
Quantified comparison tests with ANY and ALL ................................................ 438
Testing set membership with IN conditions ....................................................... 441
Existence test .................................................................................................... 443
Outer references ................................................................................................ 445
Subqueries and joins ......................................................................................... 446
Copyright © 2006, iAnywhere Solutions, Inc. v
SQL Anywhere® Server - SQL Usage
Nested subqueries ............................................................................................. 448
How subqueries work ........................................................................................ 450
Adding, Changing, and Deleting Data ................................................................... 457
Data modification statements ............................................................................ 458
Adding data using INSERT ................................................................................ 464
Changing data using UPDATE .......................................................................... 468
Changing data using INSERT ............................................................................ 470
Deleting data using DELETE ............................................................................. 471
Query Optimization and Execution ........................................................................ 473
Phases of query processing .............................................................................. 474
Semantic query transformations ........................................................................ 476
How the optimizer works .................................................................................... 487
Query execution algorithms ............................................................................... 507
Reading execution plans ................................................................................... 529
Physical data organization and access .............................................................. 548
IV. SQL Dialects and Compatibility ......................................................... 559
Other SQL Dialects .................................................................................................. 561
SQL Anywhere SQL features ............................................................................ 562
Transact-SQL Compatibility ............................................................................... 564
Adaptive Server architectures ............................................................................ 567
Configuring databases for Transact-SQL compatibility ..................................... 572
Writing compatible SQL statements .................................................................. 579
Transact-SQL procedure language overview .................................................... 583
Automatic translation of stored procedures ....................................................... 585
Returning result sets from Transact-SQL procedures ....................................... 586
Variables in Transact-SQL procedures .............................................................. 587
Error handling in Transact-SQL procedures ...................................................... 588
V. XML in the Database ............................................................................ 591
Using XML in the Database .................................................................................... 593
What is XML? .................................................................................................... 594
Storing XML documents in relational databases ............................................... 595
Exporting relational data as XML ....................................................................... 596
vi Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Usage
Importing XML documents as relational data .................................................... 597
Obtaining query results as XML ......................................................................... 604
Using SQL/XML to obtain query results as XML ............................................... 622
VI. Remote Data and Bulk Operations .................................................... 631
Importing and Exporting Data ................................................................................ 633
Transferring data into and out of databases ...................................................... 634
Importing data .................................................................................................... 636
Exporting data from databases .......................................................................... 645
Rebuilding databases ........................................................................................ 655
Extracting databases ......................................................................................... 663
Migrating databases to SQL Anywhere ............................................................. 664
Using SQL command files ................................................................................. 668
Adaptive Server Enterprise compatibility ........................................................... 671
Accessing Remote Data .......................................................................................... 673
Introduction ........................................................................................................ 674
Basic concepts to access remote data .............................................................. 675
Working with remote servers ............................................................................. 677
Using directory access servers .......................................................................... 682
Working with external logins .............................................................................. 685
Working with proxy tables .................................................................................. 687
Joining remote tables ......................................................................................... 691
Joining tables from multiple local databases ..................................................... 693
Sending native statements to remote servers ................................................... 694
Using remote procedure calls (RPCs) ............................................................... 695
Transaction management and remote data ....................................................... 697
Internal operations ............................................................................................. 698
Troubleshooting remote data access ................................................................. 701
Server Classes for Remote Data Access .............................................................. 705
Overview ............................................................................................................ 706
JDBC-based server classes .............................................................................. 707
ODBC-based server classes ............................................................................. 710
VII. Stored Procedures and Triggers ...................................................... 721
Copyright © 2006, iAnywhere Solutions, Inc. vii
SQL Anywhere® Server - SQL Usage
Using Procedures, Triggers, and Batches ............................................................ 723
Procedure and trigger overview ......................................................................... 724
Benefits of procedures and triggers ................................................................... 725
Introduction to procedures ................................................................................. 726
Introduction to user-defined functions ................................................................ 732
Introduction to triggers ....................................................................................... 735
Introduction to batches ...................................................................................... 741
Control statements ............................................................................................. 744
The structure of procedures and triggers ........................................................... 747
Returning results from procedures .................................................................... 750
Using cursors in procedures and triggers .......................................................... 755
Errors and warnings in procedures and triggers ................................................ 759
Using the EXECUTE IMMEDIATE statement in procedures ............................. 767
Transactions and savepoints in procedures and triggers .................................. 768
Tips for writing procedures ................................................................................ 769
Statements allowed in procedures, triggers, events, and batches .................... 771
Calling external libraries from procedures ......................................................... 772
Debugging Procedures, Functions, Triggers, and Events .................................. 779
Introduction to debugging in the database ......................................................... 780
Tutorial: Getting started with the debugger ........................................................ 781
Working with breakpoints ................................................................................... 786
Working with variables ....................................................................................... 788
Working with connections .................................................................................. 789
Index .................................................................................................................................. 791
viii Copyright © 2006, iAnywhere Solutions, Inc.
About This Manual
Subject
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.
Audience
This manual is for all users of SQL Anywhere.
Before you begin
This manual assumes that you have an elementary familiarity with database management systems and SQL
Anywhere in particular. If you do not have such a familiarity, you should consider reading SQL Anywhere
10 - Introduction before reading this manual.
Copyright © 2006, iAnywhere Solutions, Inc. ix
About This Manual
SQL Anywhere documentation
This book is part of the SQL Anywhere documentation set. This section describes the books in the
documentation set and how you can use them.
The SQL Anywhere documentation
The complete SQL Anywhere documentation is available in two forms: an online form that combines all
books, and as separate PDF files for each book. Both forms of the documentation contain identical
information and consist of the following books:
♦ SQL Anywhere 10 - Introduction This book introduces SQL Anywhere 10—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 10 - Changes and Upgrading This book describes new features in SQL Anywhere
10 and in previous versions of the software.
♦ SQL Anywhere Server - Database Administration This book covers material related to running,
managing, and configuring SQL Anywhere databases. It describes database connections, the database
server, database files, security, backup procedures, security, and replication with Replication Server, as
well as administration utilities and options.
♦ 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.
♦ SQL Anywhere Server - SQL Reference This book provides a complete reference for the SQL
language used by SQL Anywhere. It also describes the SQL Anywhere system views and procedures.
♦ SQL Anywhere Server - Programming This book describes how to build and deploy database
applications using the C, C++, and Java programming languages, as well as Visual Studio .NET. Users
of tools such as Visual Basic and PowerBuilder can use the programming interfaces provided by those
tools.
♦ SQL Anywhere 10 - Error Messages This book provides a complete listing of SQL Anywhere error
messages together with diagnostic information.
♦ MobiLink - Getting Started This manual introduces MobiLink, a session-based relational-database
synchronization system. MobiLink technology allows two-way replication and is well suited to mobile
computing environments.
♦ MobiLink - Server Administration This manual describes how to set up and administer MobiLink
applications.
♦ MobiLink - Client Administration This manual describes how to set up, configure, and synchronize
MobiLink clients. MobiLink clients can be SQL Anywhere or UltraLite databases.
♦ MobiLink - Server-Initiated Synchronization This manual describes MobiLink server-initiated
synchronization, a feature of MobiLink that allows you to initiate synchronization or other remote actions
from the consolidated database.
x Copyright © 2006, iAnywhere Solutions, Inc.