SQL Anywhere® Server - Database Administration

1314 Pages·2009·8.44 MB·English

SQL Anywhere® Server
Database Administration
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 ........................................................................................... xi
About the SQL Anywhere documentation .............................................................. xii
Starting and Connecting to Your Database ............................................... 1
Tutorial: Using the sample database .................................................................................... 3
Lesson 1: Make a copy of the sample database ................................................... 4
Lesson 2: Start the SQL Anywhere database server ............................................. 5
Lesson 3: Display the database server messages window ................................... 6
Lesson 4: Stop the database server ...................................................................... 8
Summary ............................................................................................................... 9
Working with database files ............................................................................................... 11
Overview of database files ................................................................................... 12
Pre-defined dbspaces .......................................................................................... 13
The transaction log .............................................................................................. 14
Creating a database ............................................................................................ 21
Using additional dbspaces ................................................................................... 25
Using the utility database ..................................................................................... 30
Erasing a database .............................................................................................. 34
Running the database server ............................................................................................. 37
Introduction to running SQL Anywhere database servers ................................... 38
Starting the database server ................................................................................ 42
Some common options ........................................................................................ 46
Stopping the database server .............................................................................. 58
Starting and stopping databases ......................................................................... 59
Running the server outside the current session .................................................. 62
Troubleshooting server startup ............................................................................ 74
Running authenticated SQL Anywhere applications ............................................ 76
Running SQL Anywhere Web Edition applications .............................................. 82
Error reporting in SQL Anywhere ......................................................................... 83
SQL Anywhere database connections ................................................................................ 85
Connection parameters ....................................................................................... 86
Connecting with SQL Anywhere APIs ................................................................. 89
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 iii
SQL Anywhere® Server - Database Administration
Connecting from desktop applications to a Windows Mobile database ............... 91
Connecting from Sybase Central, Interactive SQL, or the SQL Anywhere
Console utility ...................................................................................................... 92
Creating ODBC data sources .............................................................................. 97
Connecting to a database using OLE DB .......................................................... 104
Using integrated logins ...................................................................................... 106
Kerberos authentication ..................................................................................... 114
Sample SQL Anywhere database connections ................................................. 124
Troubleshooting connections ............................................................................. 132
Disconnecting from a database ......................................................................... 140
Client/server communications .......................................................................................... 141
Supported network protocols ............................................................................. 142
Using the TCP/IP protocol ................................................................................. 143
Adjusting communication compression settings to improve performance ......... 149
Troubleshooting network communications ........................................................ 151
The database server ....................................................................................................... 155
The SQL Anywhere database server ................................................................. 156
Database server options .................................................................................... 165
Database options ............................................................................................... 248
Connection parameters and network protocol options ....................................................... 261
Connection parameters ..................................................................................... 262
Network protocol options ................................................................................... 301
SQL Anywhere for Windows Mobile ................................................................................. 327
Installing SQL Anywhere on a Windows Mobile device ..................................... 328
Using the Windows Mobile sample applications ................................................ 331
Connecting to a database running on a Windows Mobile device ...................... 336
Configuring Windows Mobile databases ............................................................ 339
Running the database server on Windows Mobile ............................................ 348
Using the administration utilities on Windows Mobile ........................................ 349
SQL Anywhere feature support on Windows Mobile ......................................... 356
Configuring Your Database ..................................................................... 363
SQL Anywhere environment variables .............................................................................. 365
Introduction to SQL Anywhere environment variables ....................................... 366
DYLD_LIBRARY_PATH environment variable [Mac OS X] .............................. 368
LD_LIBRARY_PATH environment variable [Linux and Solaris] ........................ 369
iv Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1
SQL Anywhere® Server - Database Administration
LIBPATH environment variable [AIX] ................................................................. 370
ODBCHOME environment variable [Unix] ......................................................... 371
ODBCINI and ODBC_INI environment variables [Unix] .................................... 372
PATH environment variable ............................................................................... 373
SACHARSET environment variable .................................................................. 374
SADIAGDIR environment variable ..................................................................... 375
SALANG environment variable .......................................................................... 377
SALOGDIR environment variable ...................................................................... 378
SATMP environment variable ............................................................................ 379
SHLIB_PATH environment variable [HP-UX] .................................................... 381
SQLANY11 environment variable ...................................................................... 382
SQLANYSAMP11 environment variable ............................................................ 383
SQLCONNECT environment variable ............................................................... 384
SQLPATH environment variable ........................................................................ 385
SQLREMOTE environment variable .................................................................. 386
SYBASE environment variable .......................................................................... 387
TMP, TEMPDIR, and TEMP environment variables .......................................... 388
File locations and installation settings ............................................................................... 389
Installation directory structure ............................................................................ 390
How SQL Anywhere locates files ....................................................................... 392
Registry and INI files .......................................................................................... 396
International languages and character sets ....................................................................... 399
Localized versions of SQL Anywhere ................................................................ 400
Understanding character sets ............................................................................ 407
Understanding locales ....................................................................................... 413
Understanding collations ................................................................................... 416
International language and character set tasks ................................................. 424
Character set and collation reference information ............................................. 429
Managing user IDs, authorities, and permissions .............................................................. 439
Managing login policies overview ...................................................................... 440
Database permissions and authorities overview ............................................... 446
Managing user permissions and authorities overview ....................................... 455
Managing connected users ................................................................................ 467
Managing groups ............................................................................................... 468
Database object names and prefixes ................................................................ 475
Using views and procedures for extra security .................................................. 477
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 v
SQL Anywhere® Server - Database Administration
Changing ownership on nested objects ............................................................. 480
How user permissions are assessed ................................................................. 482
Managing the resources connections use ......................................................... 483
Users and permissions in the catalog ................................................................ 484
Database options ............................................................................................................ 487
Introduction to database options ........................................................................ 488
Connection, database, and database server properties ..................................................... 597
Connection properties ........................................................................................ 598
Database server properties ............................................................................... 624
Database properties .......................................................................................... 639
Physical limitations ......................................................................................................... 653
SQL Anywhere size and number limitations ...................................................... 654
Administering Your Database ................................................................. 657
SQL Anywhere graphical administration tools ................................................................... 659
Using Sybase Central ........................................................................................ 660
Using Interactive SQL ........................................................................................ 676
Using text completion ........................................................................................ 725
Using the fast launcher option ........................................................................... 728
Using the SQL Anywhere Console utility ........................................................... 729
Checking for software updates .......................................................................... 732
Database administration utilities ....................................................................................... 735
Administration utilities overview ......................................................................... 737
Backup utility (dbbackup) ................................................................................... 740
Broadcast Repeater utility (dbns11) .................................................................. 745
Certificate Creation utility (createcert) ............................................................... 747
Certificate Viewer utility (viewcert) ..................................................................... 750
Data Source utility (dbdsn) ................................................................................ 752
dbisqlc utility (deprecated) ................................................................................. 764
Erase utility (dberase) ........................................................................................ 766
File Hiding utility (dbfhide) ................................................................................. 768
Histogram utility (dbhist) .................................................................................... 770
Information utility (dbinfo) .................................................................................. 772
Initialization utility (dbinit) ................................................................................... 774
Interactive SQL utility (dbisql) ............................................................................ 786
vi Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1
SQL Anywhere® Server - Database Administration
Key Pair Generator utility (createkey) ................................................................ 790
Language Selection utility (dblang) .................................................................... 791
Log Transfer Manager utility (dbltm) .................................................................. 794
Log Translation utility (dbtran) ........................................................................... 799
Ping utility (dbping) ............................................................................................ 804
Rebuild utility (rebuild) ....................................................................................... 807
Script Execution utility (dbrunsql) ...................................................................... 808
Server Enumeration utility (dblocate) ................................................................. 810
Server Licensing utility (dblic) ............................................................................ 813
Service utility (dbsvc) for Linux .......................................................................... 816
Service utility (dbsvc) for Windows .................................................................... 820
SQL Anywhere Console utility (dbconsole) ....................................................... 827
Start Server in Background utility (dbspawn) ..................................................... 829
Stop Server utility (dbstop) ................................................................................ 831
Support utility (dbsupport) .................................................................................. 833
Transaction Log utility (dblog) ............................................................................ 842
Unload utility (dbunload) .................................................................................... 845
Upgrade utility (dbupgrad) ................................................................................. 860
Validation utility (dbvalid) ................................................................................... 862
Version Diagnostic utility (dbversion) ................................................................. 865
Maintaining Your Database ..................................................................... 867
Backup and data recovery ............................................................................................... 869
Backup quick start ............................................................................................. 871
Types of backup ................................................................................................ 872
Choosing a backup format ................................................................................. 877
Backup and recovery restrictions ....................................................................... 879
Making a server-side backup ............................................................................. 880
Making a client-side backup .............................................................................. 886
Validating backups ............................................................................................. 888
Recovering your database ................................................................................. 889
Designing a backup and recovery plan .............................................................. 900
Backing up databases involved in synchronization and replication ................... 904
The internal backup process .............................................................................. 909
Validating databases ....................................................................................................... 915
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 vii
SQL Anywhere® Server - Database Administration
Introduction to validation .................................................................................... 916
Using checksums to detect corruption ............................................................... 917
Improving performance when validating databases .......................................... 920
Automating tasks using schedules and events .................................................................. 921
Introduction to using schedules and events ....................................................... 922
Understanding events ........................................................................................ 923
Understanding schedules .................................................................................. 924
Understanding system events ........................................................................... 926
Understanding event handlers ........................................................................... 930
Schedule and event internals ............................................................................ 932
Event handling tasks .......................................................................................... 934
SQL Anywhere high availability ........................................................................................ 937
Introduction to database mirroring ..................................................................... 938
Tutorial: Using database mirroring ..................................................................... 945
Tutorial: Using database mirroring with multiple databases sharing an arbiter
server ................................................................................................................. 949
Setting up database mirroring ............................................................................ 954
Using the SQL Anywhere Veritas Cluster Server agents .................................. 965
Monitoring Your Database ....................................................................... 971
SQL Anywhere Monitor ................................................................................................... 973
Introducing the SQL Anywhere Monitor ............................................................. 974
Monitor quick start ............................................................................................. 977
Tutorial: Using the Monitor ................................................................................. 978
Start the Monitor ................................................................................................ 983
Exit the Monitor .................................................................................................. 984
Connect to the Monitor ...................................................................................... 985
Disconnect from the Monitor .............................................................................. 986
Monitoring resources ......................................................................................... 987
Administering resources .................................................................................... 995
Working with Monitor users ............................................................................. 1002
Alerts ................................................................................................................ 1006
Installed objects ............................................................................................... 1010
Installing the SQL Anywhere Monitor on a separate computer ....................... 1011
Troubleshooting the Monitor ............................................................................ 1012
viii Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1
SQL Anywhere® Server - Database Administration
The SQL Anywhere SNMP Extension Agent ................................................................... 1015
Introduction to the SQL Anywhere SNMP Extension Agent ............................ 1016
Understanding SNMP ...................................................................................... 1017
Using the SQL Anywhere SNMP Extension Agent .......................................... 1021
SQL Anywhere MIB reference ......................................................................... 1029
RDBMS MIB reference .................................................................................... 1055
Security ................................................................................................... 1061
Keeping your data secure .............................................................................................. 1063
Introduction to security features ....................................................................... 1064
Security tips ..................................................................................................... 1066
Controlling database access ............................................................................ 1068
Auditing database activity ................................................................................ 1074
Running the database server in a secure fashion ........................................... 1081
Encrypting and decrypting a database ............................................................ 1082
Keeping your Windows Mobile database secure ............................................. 1093
Transport-layer security ................................................................................................. 1095
Introduction to transport-layer security ............................................................ 1096
Setting up transport-layer security ................................................................... 1099
Creating digital certificates ............................................................................... 1101
Encrypting SQL Anywhere client/server communications ............................... 1107
Encrypting SQL Anywhere web services ......................................................... 1112
Encrypting MobiLink client/server communications ......................................... 1113
Certificate utilities ............................................................................................. 1120
Replication .............................................................................................. 1121
Using SQL Anywhere as an Open Server ....................................................................... 1123
Open Clients, Open Servers, and TDS ............................................................ 1124
Setting up SQL Anywhere as an Open Server ................................................ 1126
Configuring Open Servers ............................................................................... 1128
Characteristics of Open Client and jConnect connections ............................... 1133
Replicating data with Replication Server ......................................................................... 1135
Introduction to using SQL Anywhere with Replication Server ......................... 1136
Tutorial: Replicate data using Replication Server ............................................ 1139
Configuring databases for Replication Server ................................................. 1148
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 ix
SQL Anywhere® Server - Database Administration
Using the LTM ................................................................................................. 1151
Glossary .................................................................................................. 1161
Glossary ...................................................................................................................... 1163
Index ........................................................................................................ 1193
x Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1