1.14.4 CREATE EXISTING TABLE 33
1.15 Temporary Tables 35
1.15.1 Global Temporary Tables 36
1.15.2 Local Temporary Tables 37
1.15.2.1 CREATE TABLE #table_name 37
1.15.2.2 DECLARE LOCAL TEMPORARY TABLE 38
1.15.2.3 SELECT INTO #table_name 40
1.16 Normalized Design 40
1.16.1 First Normal Form 41
1.16.2 Second Normal Form 42
1.16.3 Third Normal Form 43
1.16.4 Boyce-Codd Normal Form 44
1.16.5 Fourth Normal Form 45
1.16.6 Fifth Normal Form 46
1.17 Chapter Summary 47
Chapter 2 Inserting 49
2.1 Introduction 49
2.2 INSERT 49
2.2.1 INSERT All Values 50
2.2.2 INSERT Named Values 52
2.2.3 INSERT Select All Columns 53
2.2.4 INSERT Select Column List 54
2.2.5 INSERT Select With Auto Name 55
2.3 LOAD TABLE 56
2.4 ISQL INPUT 64
2.5 Chapter Summary 68
Chapter 3 Selecting 69
3.1 Introduction 69
3.2 Logical Execution of a SELECT 70
3.3 FROM Clause 80
3.4 JOIN 82
3.4.1 CROSS JOIN 82
3.4.2 INNER JOIN 83
3.4.3 LEFT OUTER JOIN 84
3.4.4 RIGHT OUTER JOIN 85
3.4.5 FULL OUTER JOIN 86
3.5 Derived Tables 87
3.6 Multi-Table Joins 90
3.7 SELECT FROM Procedure Call 96
3.8 LATERAL Procedure Call 99
3.9 SELECT List 101
3.10 Expressions and Operators 102
3.10.1 IF and CASE Expressions 105
3.11 Top 15 Scalar Built-in Functions 107
3.12 Boolean Expressions and the WHERE Clause 113
3.12.1 Comparison Predicates 116
Contents
iv
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3.12.2 EXISTS Predicates 117
3.12.3 IN Predicates 118
3.12.4 BETWEEN Predicates 119
3.12.5 NULL Test Predicates 120
3.12.6 LIKE Predicates 120
3.12.7 Trigger Predicates 122
3.13 GROUP BY Clause 123
3.14 Aggregate Function Calls 125
3.15 GROUP BY ROLLUP Clause 131
3.16 HAVING Clause 134
3.17 ORDER BY Clause 135
3.18 SELECT DISTINCT 137
3.19 FIRST and TOP 137
3.20 NUMBER(*) 139
3.21 INTO Clause 140
3.22 UNION, EXCEPT, and INTERSECT 141
3.23 CREATE VIEW 145
3.24 WITH Clause 148
3.24.1 Recursive UNION 149
3.25 UNLOAD TABLE and UNLOAD SELECT 155
3.26 ISQL OUTPUT 160
3.27 Chapter Summary 165
Chapter 4 Updating 167
4.1 Introduction 167
4.2 Single-Row UPDATE 167
4.3 Multi-Row UPDATE 169
4.4 Logical Execution of a Set UPDATE 169
4.4.1 Set UPDATE 174
4.5 UPDATE WHERE CURRENT OF Cursor 176
4.6 Chapter Summary 179
Chapter 5 Deleting 181
5.1 Introduction 181
5.2 Single-Row DELETE 181
5.3 Multi-Row DELETE 182
5.4 Logical Execution of a Set DELETE 183
5.4.1 Set DELETE 185
5.5 DELETE WHERE CURRENT OF Cursor 188
5.6 TRUNCATE TABLE 190
5.7 Chapter Summary 194
Chapter 6 Fetching 195
6.1 Introduction 195
6.2 Cursor FETCH Loop 195
6.2.1 DECLARE CURSOR FOR Select 199
6.2.2 DECLARE CURSOR USING Select 203
6.2.3 DECLARE CURSOR FOR CALL 204
Contents
v
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6.2.4 OPEN and CLOSE Cursor 206
6.2.5 FETCH Cursor 206
6.3 Cursor FOR Loop 207
6.4 Chapter Summary 210
Chapter 7 Synchronizing 211
7.1 Introduction 211
7.2 How MobiLink Works 212
7.3 The MobiLink ASA Client 216
7.4 MobiLink Client Setup 217
7.4.1 CREATE PUBLICATION 217
7.4.2 CREATE SYNCHRONIZATION USER 219
7.4.3 CREATE SYNCHRONIZATION SUBSCRIPTION 220
7.4.4 The DBMLSYNC Command Line 221
7.4.5 SYSSYNC and Publication Defaults 226
7.5 The MobiLink Server 228
7.6 MobiLink Server Setup 229
7.6.1 Connection-Level Scripts 229
7.6.2 Session-Level Scripts 230
7.6.3 Table-Level Scripts 232
7.6.4 Row-Level Scripts 233
7.6.4.1 Handling Uploaded Inserts 233
7.6.4.2 Handling Uploaded Updates 234
7.6.4.3 Handling Uploaded Deletes 235
7.6.4.4 Handling Upload Conflicts 236
7.6.4.5 Handling Upload Errors 240
7.6.4.6 Downloading Inserts and Updates 243
7.6.4.7 Downloading Deletes 246
7.6.4.8 Handling Download Errors 249
7.7 The MobiLink System Tables 254
7.8 MobiLink Monitor 258
7.9 Chapter Summary 260
Chapter 8 Packaging 261
8.1 Introduction 261
8.2 BEGIN Block 261
8.3 Exception Handler 264
8.4 Assignment Statement 267
8.5 IF Statement 268
8.6 CASE Statement 269
8.7 WHILE Loop 270
8.8 EXECUTE IMMEDIATE 271
8.9 CREATE PROCEDURE, CALL, and RETURN 274
8.10 CREATE FUNCTION 280
8.11 CREATE TRIGGER 284
8.12 CREATE EVENT 293
8.13 TRIGGER EVENT 301
8.14 CREATE VARIABLE 304
Contents
vi
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8.15 Chapter Summary 305
Chapter 9 Protecting 307
9.1 Introduction 307
9.2 Options 308
9.3 Transactions 313
9.4 Savepoints and Subtransactions 320
9.5 Error Handling 324
9.5.1 SIGNAL and RESIGNAL 324
9.5.2 RAISERROR and CREATE MESSAGE 327
9.5.3 ROLLBACK TRIGGER 333
9.6 Locks 336
9.7 Blocks and Isolation Levels 339
9.7.1 Isolation Level 0 340
9.7.2 Isolation Level 1 343
9.7.3 Isolation Level 2 346
9.7.4 Isolation Level 3 349
9.8 Deadlock 355
9.9 Mutexes 359
9.10 GRANT CONNECT 361
9.10.1 Table Privileges 364
9.10.2 View Privileges 367
9.10.3 Execution Privileges 369
9.10.4 Special Privileges 370
9.10.5 GRANT GROUP 372
9.11 Logging and Recovery 375
9.12 Backup 379
9.12.1 Full Offline Image Backup 379
9.12.2 Full Online Image Backup 381
9.12.3 Differential Online Log Backup 384
9.12.4 Incremental Online Log Backup 385
9.12.5 Live Log Backup 386
9.13 Restore 387
9.14 Validation 393
9.15 Chapter Summary 396
Chapter 10 Tuning 399
10.1 Introduction 399
10.2 Request-Level Logging 400
10.3 Index Consultant 408
10.4 Execution Profiler 413
10.5 Graphical Plan 416
10.6 Fragmentation and Reorganization 421
10.6.1 File Fragmentation 421
10.6.2 Table Fragmentation 423
10.6.3 Table Reorganization 428
10.6.4 Index Fragmentation 429
10.6.5 Index Reorganization 432
Contents
vii
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
10.6.6 Database Reorganization with Unload/Reload 433
10.7 CREATE INDEX 437
10.8 Database Performance Counters 443
10.9 Tips and Techniques 446
10.10 Chapter Summary 453
Index 455
Contents
viii
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Preface
There’s a good reason that SQL Anywhere has over 70% of the mobile database
market. In fact, there are several good reasons: It’s easy to use, easy to adminis
-
ter, and it doesn’t take up much space. Those are all good things if you’re trying
to deploy hundreds or thousands of databases. And they’re good things to have
if you’re trying to embed relational databases inside tens of thousands of routers
or switches or other hardware devices.
But SQL Anywhere isn’t just for mobile and embedded databases; it’s got
the features and the flexibility to work as a network server with hundreds of
active users pounding away on a single database. And it’s got a solid foundation
and a long history, from its roots with Watcom in the 1980s through to
Powersoft and Sybase in the 1990s and now with iAnywhere Solutions in the
new millennium.
Through all those years, the SQL Anywhere development team has never
strayed from one central virtue; as Richard Williams of VMI Communication
and Learning Systems said it, “Watcom does things the way they should be
done.”
Here’s an overview of the history behind SQL Anywhere Studio Version 9:
n
1965 The WATFOR FORTRAN compiler was initially developed for the
IBM 7040 at the University of Waterloo.
n
1981 The company Watcom was founded.
n
1988 The PACEBase SQL Database System was released as part of the
Watcom Information Workbench. It ran on PCs using DOS, and included
ISQL, transaction processing, declarative referential integrity, CREATE
TABLE and ALTER TABLE statements, inner joins, subqueries in
SELECT lists, multiple user IDs, and a separate rollback file for recovery.
This was “Version 1”; no Version 2 was ever released.
n
1992 Watcom SQL 3 became commercially available. It was a multi-user
server with row-level locking, a separate transaction log, and multi-table
updates. Later versions added Windows 3 support, an ODBC driver, outer
joins, DEFAULT values, CHECK constraints, TIME, DATE, and LONG
data types, and online backup.
n
1993 Powersoft included Watcom SQL 3.2 in the box with PowerBuilder 3.
n
1994 Powersoft bought Watcom.
n
1994 Watcom SQL 4 was released. It included stored procedures and trig
-
gers, cascading updates and deletes, temporary tables, multiple databases in
one server, a self-tuning query optimizer, and TCP/IP support.
n
1995 Sybase bought Powersoft.
n
1995 SQL Anywhere 5 was released. It included SQL Remote, SQL Cen
-
tral (now Sybase Central), Transact SQL syntax, and support for the Sybase
Replication Server.
n
1996 SQL Anywhere 5.5 was released as a bundled product that included
InfoMaker, NetImpact Dynamo (later PowerDynamo), and a plug-in archi
-
tecture for Sybase Central.
ix
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
n
1998 SQL Anywhere 6 was released, with the new names “Adaptive Server
Anywhere” applied to the engine itself and “SQL Anywhere Studio”
applied to the product bundle, which now included SQL Modeler (later
called PowerDesigner). SQL Anywhere 6 was the version that introduced
Java in the database; later releases added proxy tables, the UltraLite deploy
-
ment option, MobiLink synchronization, and an interactive debugger.
n
2000 SQL Anywhere 7 was released. It supported ASA databases as
MobiLink remote databases, and included UltraLite improvements, an
OLEDB driver, and dynamic cache resizing. Version 7 also introduced the
Java versions of ISQL and Sybase Central.
n
2000 iAnywhere Solutions, Inc. was founded as a subsidiary of Sybase.
n
2001 SQL Anywhere 8 was released. This version included hundreds of
enhancements but the big news was all about the query engine, which was
completely rewritten to improve performance and support future
enhancements.
n
2003 SQL Anywhere 9 was released. It included hundreds more enhance
-
ments including more performance improvements, the Index Consultant,
support for OLAP, XML, and 64-bit processors, and many new SQL fea
-
tures including recursive union and stored procedure calls in the FROM
clause.
In the years to come, as the prices of computers and hard drives continue to fall
and the performance and capabilities of SQL Anywhere continue to improve,
SQL Anywhere will be used for larger and more sophisticated applications. This
book is dedicated to that prospect, and the belief that you, the database designer
and application developer, will want to explore the capabilities of SQL Any-
where and exploit its features to the fullest.
This book is divided into 10 chapters, each with a single verb as its title,
arranged in a rough approximation of the tasks performed during the life cycle
of a typical database:
1. Creating
2. Inserting
3. Selecting
4. Updating
5. Deleting
6. Fetching
7. Synchronizing
8. Packaging
9. Protecting
10. Tuning
Chapter 1, “Creating,” starts with Codd’s 12 rules that define what a relational
database management system like SQL Anywhere really is. It then proceeds to
describe the five different kinds of tables SQL Anywhere uses: global perma
-
nent, global temporary, local temporary, remote, and proxy. Chapter 1 also
covers data types, column properties like DEFAULT, and column and table con
-
straints like CHECK and FOREIGN KEY, and ends with a discussion of
normalized design.
Chapter 2, “Inserting,” talks about putting data into the database, and it
covers five different flavors of INSERT as well as the LOAD TABLE statement
and ISQL’s client-side alternative, the INPUT statement.
Preface
x
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 3, “Selecting,” talks about the inverse of Chapter 2, getting data
out of the database with SELECT, UNLOAD, and the ISQL OUTPUT state
-
ment. This is the longest chapter in the book, which should come as no surprise;
the SQL SELECT statement may be the most powerful, most complex single
syntactic construction available in any programming language.
Chapter 3 starts with an explanation of the SELECT statement, the logical
execution of a SELECT, which shows how all the various clauses fit together
and contribute to the final result set without concern for how SQL Anywhere
actually executes a SELECT. Most of the rest of Chapter 3 follows the steps laid
out and explained in the beginning of the chapter. If you’ve ever had trouble
with the GROUP BY clause, or had a SELECT that returned 10,000 rows
instead of the three you wanted, or wondered why you can’t call NUMBER(*)
in a WHERE clause, this is the chapter for you.
Chapter 4, “Updating,” covers the UPDATE statement and how it can be
used to update a single row in one table, or multiple rows, or even multiple rows
in multiple tables. This chapter also includes a section on the logical execution
of a set UPDATE and a discussion of cursor UPDATE statements using the
WHERE CURRENT OF clause.
Chapter 5, “Deleting,” is the evil twin of Chapter 4; it talks about deleting
single and multiple rows, and includes a section on the logical execution of a set
DELETE as well as information about cursor DELETE statements using
WHERE CURRENT OF. Chapter 5 also includes a section on TRUNCATE
TABLE and how it compares with DELETE: sometimes faster, sometimes not,
and why.
Chapter 6, “Fetching,” talks about the DECLARE, OPEN, FETCH, and
CLOSE statements as they apply to cursors using SELECT queries and proce-
dure calls. It also describes the cursor FOR loop, which vastly simplifies the
cursor loops in SQL by eliminating the need to DECLARE all those local vari-
ables or code those endless FETCH lists.
Chapter 7, “Synchronizing,” is the MobiLink chapter; it discusses how the
MobiLink server works together with the MobiLink client for ASA to synchro
-
nize data back and forth between a consolidated database and dozens or
thousands of remote databases. MobiLink scripts are classified by level (con
-
nection, session, table, and row) and by direction (upload versus download), and
error handling on the server and client sides is described.
Chapter 8, “Packaging,” talks about moving application logic into the data
-
base using BEGIN blocks and SQL modules based on BEGIN blocks:
procedures, functions, triggers, and events. The SET assignment statement is
described, as well as the flow-of-control structures IF, CASE, and WHILE, the
EXECUTE IMMEDIATE statement, and the connection-level CREATE
VARIABLE statement. This chapter also introduces the basics of exception han
-
dling, a topic that is expanded on in Chapter 9.
Chapter 9, “Protecting,” is the second-longest chapter, and it explores the
widest variety of topics including options, transactions, exceptions, errors,
subtransactions, locks, blocks, isolation levels, deadlocks, mutexes, user IDs,
privileges, user groups, logging, recovery, backup, restore, and (gasp!) database
validation. Many of these topics are only loosely connected to one another, but
they all fall under the general heading “Crashing, Bashing, Thrashing, and the
Protection Therefrom.”
Preface
xi
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 10, “Tuning,” might be the one with the biggest audience: Every
-
one’s interested in speed, speed, speed. This is also the chapter that should be
read last, because the first rule of optimization is “Don’t do it.” If you’re an
expert, Rule 2 also applies: “Don’t do it yet.”
Chapter 10 starts by explaining the four big performance tuning tools that
SQL Anywhere provides: request-level logging, the Index Consultant, the Exe
-
cution Profiler, and the Graphical Plan. It then moves on to more arcane topics
such as file, table, and index fragmentation, and table, index, and database reor
-
ganization. Sections on the syntax of CREATE INDEX and database
performance counters are included, and the book ends with a performance tun
-
ing “Tips and Techniques” section that lists 36 do’s and don’ts that didn’t find a
home as a tip somewhere earlier in the book.
This book includes working code examples of just about every single point
made in the text. Except for some references to SQL Anywhere’s own demon
-
stration database, ASADEMO.DB, and a cute organization chart in Chapter 3
on the recursive UNION feature, there are no extended case studies or attempts
to mimic the detail and complexity of real-world applications. Instead, most of
the examples use simple tables with artificial names like “t1” and “t2”. The
examples focus on the syntax and semantics of SQL Anywhere: They’re short,
they stick to the current topic, and there’s lots of them.
The companion CD includes the full BNF from the book in HTML format
for easy browsing. It also includes all of the examples from the book, plus a full
copy of SQL Anywhere Studio 9 Developer Edition for Windows.
Preface
xii
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Acknowledgments
I am grateful to Chris Kleisath, Dave Neudoerffer, Michael Paola, Glenn
Paulley, and Tom Slee at iAnywhere Solutions for the years of encouragement,
and to Steven McDowell for bringing me together with Wordware Publishing to
get the project finally started. I also want to thank Wes Beckwith and Beth
Kohler at Wordware, for their enthusiasm and their patience.
I am also grateful to Ruth Haworth for coordinating the official iAnywhere
Solutions “tech check” process, and to the following people who performed that
duty: Peter Bumbulis, Russ Chamberlain, Reg Domaratzki, Bruce Hay, and
Glenn Paulley; especially Glenn, who provided the starting point for the BNF
and also put up with daily phone calls over weeks and months to ask about this
detail and that. And yes, some folks are getting mentioned here more than once.
Several people reviewed material and provided valuable feedback: Hartmut
Branz, Kerry Ferguson, Jonathan P. Groves, Ruth Haworth, Margaret
Kammermayer, Tom Slee, and Carol Stone.
Tom Slee pushed me past a stumbling block, the organization of the book
as a whole, by pointing out that when you’re trying to explain a relational data-
base system “you need to introduce everything before everything else.” In other
words, there’s no way to get the order perfect, and it’s okay to put SELECT
INTO #table_name in Chapter 1 because it creates a table and that’s what Chap-
ter 1 is all about, even though the syntax for SELECT doesn’t appear until
Chapter 3.
In a way, Tom Slee and his colleagues at iAnywhere Solutions made this
book more difficult to write by making the SQL Anywhere Help so good. Many
of the differences between this book and the Help are simply intended to be just
that: different, not necessarily better or worse, but different. One example is the
Backus-Naur Form (BNF) notation used for the syntax in this book (which, by
the way, is explained in the introduction to Chapter 1); the Help uses a different
notation for the syntax. For this and other differences, the hope is that they will
be useful and that if you find something is confusing in one place, you’ll be able
to turn to the other, this book or the Help, and find the answer.
Many folks provided ideas and answered questions, among them Richard
Biffl, Ivan Bowman, Mark Culp, Mahesh Dixit, Nick Elson, Dan Farrar, Greg
Fenton, David Fishburn, Anil Goel, Jason Hinsperger, Graham Hurst, Chris Irie,
Ian McHardy, Martin Neumann, Graeme Perrow, John Smirnios, Dave
Wellstood, and Matthew Young-Lai (yes, these lists are alphabetic).
I also want to thank Ken Schrader, NASCAR Nextel Cup competitor, from
whom I copied the phrase “best loser” as a synonym for second place; you can
find it used in a stored procedure name in the section titled “SELECT FROM
Procedure Call” in Chapter 3.
And finally, seriously, I want to thank my wife, Bonnie, for her support and
encouragement over all the years, including but not limited to the year it took to
write this book.
xiii
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét