SQL Anywhere® Server Database Administration - SAP

1232 Pages·2008·7.46 MB·English

SQL Anywhere® Server
Database Administration
June 2008
Version 11.0.0
Copyright and trademarks
Copyright © 2008 iAnywhere Solutions, Inc. Portions copyright © 2008 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
I. Starting and Connecting to Your Database ............................................ 1
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
2. Working with database files ................................................................................. 11
Overview of database files ................................................................................... 12
Creating a database ............................................................................................ 14
Using additional dbspaces ................................................................................... 17
Using the utility database ..................................................................................... 22
Erasing a database .............................................................................................. 26
3. Running the database server ............................................................................... 29
Introduction to running SQL Anywhere database servers ................................... 30
Starting the database server ................................................................................ 33
Some common options ........................................................................................ 37
Stopping the database server .............................................................................. 48
Starting and stopping databases ......................................................................... 49
Running the server outside the current session .................................................. 52
Troubleshooting server startup ............................................................................ 63
Running authenticated SQL Anywhere applications ............................................ 65
Error reporting in SQL Anywhere ......................................................................... 71
4. Connecting to a database ..................................................................................... 73
Introduction to SQL Anywhere database connections ......................................... 74
Connecting from Sybase Central, Interactive SQL, or the SQL Anywhere
Console utility ...................................................................................................... 78
Simple connection examples ............................................................................... 81
Disconnecting from a database ........................................................................... 89
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 iii
SQL Anywhere® Server - Database Administration
Working with ODBC data sources ....................................................................... 90
Connecting from desktop applications to a Windows Mobile database ............... 99
Connecting to a database using OLE DB .......................................................... 100
Connection parameter tips ................................................................................. 102
Troubleshooting connections ............................................................................. 104
Using integrated logins ...................................................................................... 113
Using Kerberos authentication ........................................................................... 121
5. Client/server communications ........................................................................... 133
Supported network protocols ............................................................................. 134
Using the TCP/IP protocol ................................................................................. 135
Adjusting communication compression settings to improve performance ......... 141
Troubleshooting network communications ........................................................ 143
6. The database server ............................................................................................ 147
The SQL Anywhere database server ................................................................. 148
Database server options .................................................................................... 156
Database options ............................................................................................... 235
7. Connection parameters and network protocol options ................................... 247
Connection parameters ..................................................................................... 248
Network protocol options ................................................................................... 286
II. Configuring Your Database ................................................................. 309
8. SQL Anywhere environment variables .............................................................. 311
Introduction to SQL Anywhere environment variables ....................................... 312
DYLD_LIBRARY_PATH environment variable [Mac OS X] .............................. 314
LD_LIBRARY_PATH environment variable [Linux and Solaris] ........................ 315
LIBPATH environment variable [AIX] ................................................................. 316
ODBCHOME environment variable [Unix] ......................................................... 317
ODBCINI and ODBC_INI environment variables [Unix] .................................... 318
PATH environment variable ............................................................................... 319
SACHARSET environment variable .................................................................. 320
SADIAGDIR environment variable ..................................................................... 321
SALANG environment variable .......................................................................... 323
SALOGDIR environment variable ...................................................................... 324
SATMP environment variable ............................................................................ 325
SHLIB_PATH environment variable [HP-UX] .................................................... 327
iv Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0
SQL Anywhere® Server - Database Administration
SQLANY11 environment variable ...................................................................... 328
SQLANYSAMP11 environment variable ............................................................ 329
SQLCONNECT environment variable ............................................................... 330
SQLPATH environment variable ........................................................................ 331
SQLREMOTE environment variable .................................................................. 332
SYBASE environment variable .......................................................................... 333
TMP, TEMPDIR, and TEMP environment variables .......................................... 334
9. File locations and installation settings ............................................................. 335
Installation directory structure ............................................................................ 336
How SQL Anywhere locates files ....................................................................... 338
Registry and INI files .......................................................................................... 341
10. International languages and character sets ................................................... 345
Localized versions of SQL Anywhere ................................................................ 346
Understanding character sets ............................................................................ 353
Understanding locales ....................................................................................... 359
Understanding collations ................................................................................... 362
International language and character set tasks ................................................. 369
Character set and collation reference information ............................................. 374
11. Managing user IDs, authorities, and permissions .......................................... 385
Managing login policies overview ...................................................................... 386
Database permissions and authorities overview ............................................... 392
Managing user permissions and authorities ...................................................... 400
Managing connected users ................................................................................ 412
Managing groups ............................................................................................... 413
Database object names and prefixes ................................................................ 420
Using views and procedures for extra security .................................................. 422
Changing ownership on nested objects ............................................................. 425
How user permissions are assessed ................................................................. 427
Managing the resources connections use ......................................................... 428
Users and permissions in the catalog ................................................................ 429
12. Database options ............................................................................................... 431
Introduction to database options ........................................................................ 432
13. Database properties .......................................................................................... 537
Understanding database properties ................................................................... 538
14. Physical limitations ........................................................................................... 587
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 v
SQL Anywhere® Server - Database Administration
SQL Anywhere size and number limitations ...................................................... 588
III. Administering Your Database ............................................................ 591
15. SQL Anywhere graphical administration tools ............................................... 593
Using Sybase Central ........................................................................................ 594
Using Interactive SQL ........................................................................................ 612
Using text completion ........................................................................................ 658
Using the fast launcher option ........................................................................... 661
Using the SQL Anywhere Console utility ........................................................... 662
Checking for software updates .......................................................................... 665
16. Database administration utilities ..................................................................... 667
Administration utilities overview ......................................................................... 669
Backup utility (dbbackup) ................................................................................... 672
Broadcast Repeater utility (dbns11) .................................................................. 677
Certificate Creation utility (createcert) ............................................................... 679
Certificate Viewer utility (viewcert) ..................................................................... 682
Data Source utility (dbdsn) ................................................................................ 684
Erase utility (dberase) ........................................................................................ 696
File Hiding utility (dbfhide) ................................................................................. 698
Histogram utility (dbhist) .................................................................................... 700
Information utility (dbinfo) .................................................................................. 702
Initialization utility (dbinit) ................................................................................... 703
Interactive SQL utility (dbisql) ............................................................................ 716
Interactive SQL utility (dbisqlc) .......................................................................... 720
Key Pair Generator utility (createkey) ................................................................ 722
Language Selection utility (dblang) .................................................................... 723
Log Transfer Manager utility (dbltm) .................................................................. 726
Log Translation utility (dbtran) ........................................................................... 731
Ping utility (dbping) ............................................................................................ 736
Rebuild utility (rebuild) ....................................................................................... 739
Script Execution utility (dbrunsql) ...................................................................... 740
Server Enumeration utility (dblocate) ................................................................. 742
Server Licensing utility (dblic) ............................................................................ 745
Service utility (dbsvc) for Windows .................................................................... 748
Service utility (dbsvc) for Linux .......................................................................... 754
vi Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0
SQL Anywhere® Server - Database Administration
SQL Anywhere Console utility (dbconsole) ....................................................... 758
Start Server in Background utility (dbspawn) ..................................................... 760
Stop Server utility (dbstop) ................................................................................ 762
Support utility (dbsupport) .................................................................................. 764
Transaction Log utility (dblog) ............................................................................ 773
Unload utility (dbunload) .................................................................................... 776
Upgrade utility (dbupgrad) ................................................................................. 790
Validation utility (dbvalid) ................................................................................... 793
Version Diagnostic utility (dbversion) ................................................................. 796
IV. Monitoring Your Database ................................................................. 797
17. The SQL Anywhere SNMP Extension Agent ................................................... 799
Introduction to the SQL Anywhere SNMP Extension Agent .............................. 800
Understanding SNMP ........................................................................................ 801
Using the SQL Anywhere SNMP Extension Agent ............................................ 805
SQL Anywhere MIB reference ........................................................................... 813
RDBMS MIB reference ...................................................................................... 839
V. Maintaining Your Database ................................................................. 845
18. Backup and data recovery ................................................................................ 847
Introduction to backup and recovery .................................................................. 848
Understanding backups ..................................................................................... 852
Designing backup procedures ........................................................................... 855
Configuring your database for data protection ................................................... 865
Backup and recovery internals .......................................................................... 868
Backup and recovery tasks ................................................................................ 875
Creating a maintenance plan ............................................................................. 895
19. Automating tasks using schedules and events ............................................. 897
Introduction to using schedules and events ....................................................... 898
Understanding events ........................................................................................ 899
Understanding schedules .................................................................................. 900
Understanding system events ........................................................................... 902
Understanding event handlers ........................................................................... 906
Schedule and event internals ............................................................................ 908
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 vii
SQL Anywhere® Server - Database Administration
Event handling tasks .......................................................................................... 910
20. SQL Anywhere high availability ....................................................................... 913
Introduction to database mirroring ..................................................................... 914
Tutorial: Using database mirroring ..................................................................... 921
Tutorial: Using database mirroring with multiple databases sharing an arbiter
server ................................................................................................................. 925
Setting up database mirroring ............................................................................ 930
Using the SQL Anywhere Veritas Cluster Server agents .................................. 940
VI. Security ................................................................................................ 945
21. Keeping your data secure ................................................................................. 947
Introduction to security features ......................................................................... 948
Security tips ....................................................................................................... 950
Controlling database access .............................................................................. 952
Auditing database activity .................................................................................. 958
Running the database server in a secure fashion ............................................. 964
Encrypting a database ....................................................................................... 965
Keeping your Windows Mobile database secure ............................................... 975
22. Transport-layer security ................................................................................... 977
Introduction to transport-layer security .............................................................. 978
Setting up transport-layer security ..................................................................... 981
Creating digital certificates ................................................................................. 983
Encrypting SQL Anywhere client/server communications ................................. 989
Encrypting SQL Anywhere web services ........................................................... 994
Encrypting MobiLink client/server communications ........................................... 995
Certificate utilities ............................................................................................. 1002
VII. Replication ........................................................................................ 1003
23. Using SQL Anywhere as an Open Server ..................................................... 1005
Open Clients, Open Servers, and TDS ............................................................ 1006
Setting up SQL Anywhere as an Open Server ................................................ 1008
Configuring Open Servers ............................................................................... 1010
Characteristics of Open Client and jConnect connections ............................... 1016
24. Replicating data with Replication Server ...................................................... 1019
viii Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0
SQL Anywhere® Server - Database Administration
Introduction to using SQL Anywhere with Replication Server ......................... 1020
Tutorial: Replicate data using Replication Server ............................................ 1023
Configuring databases for Replication Server ................................................. 1032
Using the LTM ................................................................................................. 1035
VIII. SQL Anywhere for Windows Mobile .............................................. 1045
25. SQL Anywhere for Windows Mobile .............................................................. 1047
Installing SQL Anywhere on a Windows Mobile device ................................... 1048
Using the Windows Mobile sample applications .............................................. 1052
Connecting to a database running on a Windows Mobile device .................... 1057
Configuring Windows Mobile databases .......................................................... 1062
Running the database server on Windows Mobile .......................................... 1071
Using the administration utilities on Windows Mobile ...................................... 1072
SQL Anywhere feature support on Windows Mobile ....................................... 1079
IX. Glossary ............................................................................................. 1085
26. Glossary ........................................................................................................... 1087
Index ........................................................................................................ 1117
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 ix
x