Transcription
Adaptive Server AnywhereDatabase AdministrationGuidePart number: DC38123-01-0902-01Last modified: October 2004
Copyright 1989–2004 Sybase, Inc. Portions copyright 2001–2004 iAnywhere Solutions, Inc. All rights reserved.No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, orotherwise, without the prior written permission of iAnywhere Solutions, Inc. iAnywhere Solutions, Inc. is a subsidiary of Sybase, Inc.Sybase, SYBASE (logo), AccelaTrade, ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server,Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor, Adaptive Server Enterprise Replication, AdaptiveServer Everywhere, Adaptive Server IQ, Adaptive Warehouse, AnswerBase, Anywhere Studio, Application Manager, AppModeler,APT Workbench, APT-Build, APT-Edit, APT-Execute, APT-Library, APT-Translator, ASEP, AvantGo, AvantGo Application Alerts, AvantGoMobile Delivery, AvantGo Mobile Document Viewer, AvantGo Mobile Inspection, AvantGo Mobile Marketing Channel, AvantGo Mobile Pharma,AvantGo Mobile Sales, AvantGo Pylon, AvantGo Pylon Application Server, AvantGo Pylon Conduit, AvantGo Pylon PIM Server, AvantGoPylon Pro, Backup Server, BayCam, Bit-Wise, BizTracker, Certified PowerBuilder Developer, Certified SYBASE Professional, Certified SYBASEProfessional Logo, ClearConnect, Client Services, Client-Library, CodeBank, Column Design, ComponentPack, Connection Manager, Convoy/DM,Copernicus, CSP, Data Pipeline, Data Workbench, DataArchitect, Database Analyzer, DataExpress, DataServer, DataWindow, DB-Library,dbQueue, Developers Workbench, Direct Connect Anywhere, DirectConnect, Distribution Director, Dynamic Mobility Model, Dynamo, e-ADK,E-Anywhere, e-Biz Integrator, EC Gateway, ECMAP, ECRTP, eFulfillment Accelerator, Electronic Case Management, Embedded SQL, EMS,Enterprise Application Studio, Enterprise Client/Server, Enterprise Connect, Enterprise Data Studio, Enterprise Manager, Enterprise Portal (logo),Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise Work Designer, Enterprise Work Modeler, eProcurement Accelerator,eremote, Everything Works Better When Everything Works Together, EWA, E-Whatever, Financial Fusion, Financial Fusion (and design), FinancialFusion Server, Formula One, Fusion Powered e-Finance, Fusion Powered Financial Destinations, Fusion Powered STP, Gateway Manager,GeoPoint, GlobalFIX, iAnywhere, iAnywhere Solutions, ImpactNow, Industry Warehouse Studio, InfoMaker, Information Anywhere, InformationEverywhere, InformationConnect, InstaHelp, Intelligent Self-Care, InternetBuilder, iremote, iScript, Jaguar CTS, jConnect for JDBC,KnowledgeBase, Logical Memory Manager, Mail Anywhere Studio, MainframeConnect, Maintenance Express, Manage Anywhere Studio, MAP,M-Business Channel, M-Business Network, M-Business Server, MDI Access Server, MDI Database Gateway, media.splash, Message AnywhereServer, MetaWorks, MethodSet, ML Query, MobiCATS, My AvantGo, My AvantGo Media Channel, My AvantGo Mobile Marketing, MySupport,Net-Gateway, Net-Library, New Era of Networks, Next Generation Learning, Next Generation Learning Studio, O DEVICE, OASiS, OASiS logo,ObjectConnect, ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Biz, Open Business Interchange, Open Client,Open Client/Server, Open Client/Server Interfaces, Open ClientConnect, Open Gateway, Open Server, Open ServerConnect, Open Solutions,Optima , Orchestration Studio, Partnerships that Work, PB-Gen, PC APT Execute, PC DB-Net, PC Net Library, PhysicalArchitect, PocketPowerBuilder, PocketBuilder, Power Through Knowledge, power.stop, Power , PowerAMC, PowerBuilder, PowerBuilder Foundation ClassLibrary, PowerDesigner, PowerDimensions, PowerDynamo, Powering the New Economy, PowerJ, PowerScript, PowerSite, PowerSocket,Powersoft, Powersoft Portfolio, Powersoft Professional, PowerStage, PowerStudio, PowerTips, PowerWare Desktop, PowerWare Enterprise,ProcessAnalyst, QAnywhere, Rapport, Relational Beans, RepConnector, Replication Agent, Replication Driver, Replication Server, ReplicationServer Manager, Replication Toolkit, Report Workbench, Report-Execute, Resource Manager, RW-DisplayLib, RW-Library, S.W.I.F.T. MessageFormat Libraries, SAFE, SAFE/PRO, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts,smart.script, SQL Advantage, SQL Anywhere, SQL Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU,SQL Everywhere, SQL Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL Server SNMP SubAgent, SQL Server/CFT,SQL Server/DBM, SQL SMART, SQL Station, SQL Toolset, SQLJ, Stage III Engineering, Startup.Com, STEP, SupportNow, Sybase Central,Sybase Client/Server Interfaces, Sybase Development Framework, Sybase Financial Server, Sybase Gateways, Sybase Learning Connection,Sybase MPP, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase Synergy Program, Sybase User Workbench, SybaseVirtual Server Architecture, SybaseWare, Syber Financial, SyberAssist, SybMD, SyBooks, System 10, System 11, System XI (logo), SystemTools,Tabular Data Stream, The Enterprise Client/Server Company, The Extensible Software Platform, The Future Is Wide Open, The LearningConnection, The Model For Client/Server Solutions, The Online Information Center, The Power of One, TotalFix, TradeForce, Transact-SQL,Translation Toolkit, Turning Imagination Into Reality, UltraLite, UltraLite.NET, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kitfor UniCode, Versacore, Viewer, VisualWriter, VQL, Warehouse Control Center, Warehouse Studio, Warehouse WORKS, WarehouseArchitect,Watcom, Watcom SQL, Watcom SQL Server, Web Deployment Kit, Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup SQL Server,XA-Library, XA-Server, and XP Server are trademarks of Sybase, Inc. or its subsidiaries.All other trademarks are property of their respective owners.ii
ContentsAbout This ManualSQL Anywhere Studio documentation . . . . . .Documentation conventions . . . . . . . . . . . .The Adaptive Server Anywhere sample databaseFinding out more and providing feedback . . . .ix.x. xiii. xv. xviI Starting and Connecting to Your Database1 Running the Database ServerIntroduction . . . . . . . . . . . . . . . . . . . .Starting the server . . . . . . . . . . . . . . . .Some common options . . . . . . . . . . . . .Stopping the database server . . . . . . . . . .Starting and stopping databases . . . . . . . .Running the server outside the current sessionTroubleshooting server startup . . . . . . . . .1.2 Connecting to a DatabaseIntroduction to connections . . . . . . . . . . . . . . . .Connecting from Sybase Central or Interactive SQL . .Simple connection examples . . . . . . . . . . . . . . .Working with ODBC data sources . . . . . . . . . . . .Connecting from desktop applications to a Windowsdatabase . . . . . . . . . . . . . . . . . . . . . . .Connecting to a database using OLE DB . . . . . . . .Connection parameter tips . . . . . . . . . . . . . . . . .Troubleshooting connections . . . . . . . . . . . . . . .Using integrated logins . . . . . . . . . . . . . . . . . . . . . . . . . . .CE. . . . . . . . . . .3 Client/Server CommunicationsSupported network protocols . . . . . . . . . . . . . . . . . .Using the TCP/IP protocol . . . . . . . . . . . . . . . . . . . .Using the SPX protocol . . . . . . . . . . . . . . . . . . . . .Using Named Pipes . . . . . . . . . . . . . . . . . . . . . . .Adjusting communication compression settings to improveperformance . . . . . . . . . . . . . . . . . . . . . . . .Troubleshooting network communications . . . . . . . . . . i
4 Adaptive Server Anywhere as an Open ServerOpen Clients, Open Servers, and TDS . . . . . . . . . . .Setting up Adaptive Server Anywhere as an Open ServerConfiguring Open Servers . . . . . . . . . . . . . . . . . .Characteristics of Open Client and jConnect connections.1011021041061125 The Database Server115The database server . . . . . . . . . . . . . . . . . . . . . . . 1166 Connection Parameters and Network Protocol Options175Connection parameters . . . . . . . . . . . . . . . . . . . . . 176Network protocol options . . . . . . . . . . . . . . . . . . . . 2067 Using Web ServicesAbout web services . . . . . . . . . . . . . . . . . . . .Quick start . . . . . . . . . . . . . . . . . . . . . . . . .Creating web services . . . . . . . . . . . . . . . . . . .Starting a database server that listens for web requestsHow URLs are interpreted . . . . . . . . . . . . . . . . .Creating SOAP and DISH web services . . . . . . . . .Tutorial: Accessing web services from Java JAX-RPC .Creating web service client functions and procedures .Procedures that provide HTML documents . . . . . . .Automatic character set conversion . . . . . . . . . . . .Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . .II Working with Database Files2252262282312352382402432502612662672698 File Locations and Installation SettingsInstallation directory structure . . . . . . . . .How Adaptive Server Anywhere locates filesEnvironment variables . . . . . . . . . . . . .Registry and INI files . . . . . . . . . . . . . .2712722742762839 Working with Database FilesOverview of database files . . . . . .Using additional dbspaces . . . . . .Working with write files (deprecated)Using the utility database . . . . . .287288290294296.10 Automating Tasks Using Schedules and Events301Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302Understanding events . . . . . . . . . . . . . . . . . . . . . . 304iv
Understanding schedules . . .Understanding system events .Understanding event handlersSchedule and event internals .Event handling tasks . . . . . .30530731131331511 International Languages and Character SetsIntroduction to international languages and character setsUnderstanding character sets in software . . . . . . . . .Understanding locales . . . . . . . . . . . . . . . . . . . .Understanding collations . . . . . . . . . . . . . . . . . .Understanding character set translation . . . . . . . . . .Collation internals . . . . . . . . . . . . . . . . . . . . . .International language and character set tasks . . . . . .Code page and character set reference . . . . . . . . . .31932032333133534334735236312 Backup and Data RecoveryIntroduction to backup and recovery . . . . .Understanding backups . . . . . . . . . . . .Designing backup procedures . . . . . . . . .Configuring your database for data protectionBackup and recovery internals . . . . . . . .Backup and recovery tasks . . . . . . . . . .373374378381391395403.III Permissions and Replication13 Managing User IDs and PermissionsDatabase permissions overview . . . . . . . .Setting user and group options . . . . . . . . .Managing individual user IDs and permissionsManaging connected users . . . . . . . . . . .Managing groups . . . . . . . . . . . . . . . . .Database object names and prefixes . . . . . .Using views and procedures for extra security .Changing ownership on nested objects . . . .How user permissions are assessed . . . . . .Managing the resources connections use . . .Users and permissions in the system tables . .425.42742843143244344445045245545745845914 Replicating Data with Replication Server463Introduction to replication . . . . . . . . . . . . . . . . . . . . 464Tutorial: Replicate data using Replication Server . . . . . . . 467Configuring databases for Replication Server . . . . . . . . . 477v
Using the LTM . . . . . . . . . . . . . . . . . . . . . . . . . . 480IVUtilities, Options, and Properties49115 Database Administration UtilitiesAdministration utilities overview . . . . . . . . .The Adaptive Server Anywhere Console utilityThe Backup utility . . . . . . . . . . . . . . . .The Collation utility . . . . . . . . . . . . . . . .The Compression utility (deprecated) . . . . .The Data Source utility . . . . . . . . . . . . . .The Erase utility . . . . . . . . . . . . . . . . .The File Hiding utility . . . . . . . . . . . . . . .The Histogram utility . . . . . . . . . . . . . . .The Information utility . . . . . . . . . . . . . .The Initialization utility . . . . . . . . . . . . . .The Interactive SQL utility . . . . . . . . . . . .The Language utility . . . . . . . . . . . . . . .The License utility . . . . . . . . . . . . . . . .The Log Transfer Manager . . . . . . . . . . .The Log Translation utility . . . . . . . . . . . .The Ping utility . . . . . . . . . . . . . . . . . .The Rebuild utility . . . . . . . . . . . . . . . .The Server Location utility . . . . . . . . . . . .The Service Creation utility . . . . . . . . . . .The Spawn utility . . . . . . . . . . . . . . . . .The Stop utility . . . . . . . . . . . . . . . . . .The Transaction Log utility . . . . . . . . . . . .The Uncompression utility (deprecated) . . . .The Unload utility . . . . . . . . . . . . . . . . .The Upgrade utility . . . . . . . . . . . . . . . .The Validation utility . . . . . . . . . . . . . . .The Write File utility (deprecated) . . . . . . . 5656356656756957557758058558859960460916 Database OptionsIntroduction to database optionsDatabase options . . . . . . . . .Compatibility options . . . . . . .Replication options . . . . . . . .Interactive SQL options . . . . .Alphabetical list of options . . . .613614619624628630633vi.
17 Database Performance and Connection Properties703Database performance statistics . . . . . . . . . . . . . . . . 704Database properties . . . . . . . . . . . . . . . . . . . . . . . 71318 Physical Limitations743Size and number limitations . . . . . . . . . . . . . . . . . . . 744Index747vii
viii
About This ManualSubjectThis book covers material related to running, managing, and configuringdatabases. It describes database connections, the database server, databasefiles, security, backup procedures, and replication with the ReplicationServer, as well as administration utilities and options.AudienceThis manual is for all users of Adaptive Server Anywhere. It is to be used inconjunction with other manuals in the documentation set.ix
SQL Anywhere Studio documentationThis book is part of the SQL Anywhere documentation set. This sectiondescribes the books in the documentation set and how you can use them.The SQL AnywhereStudio documentationThe SQL Anywhere Studio documentation is available in a variety of forms:in an online form that combines all books in one large help file; as separatePDF files for each book; and as printed books that you can purchase. Thedocumentation consists of the following books: Introducing SQL Anywhere Studio This book provides an overview ofthe SQL Anywhere Studio database management and synchronizationtechnologies. It includes tutorials to introduce you to each of the piecesthat make up SQL Anywhere Studio. What’s New in SQL Anywhere Studio This book is for users ofprevious versions of the software. It lists new features in this andprevious releases of the product and describes upgrade procedures. Adaptive Server Anywhere Database Administration Guide Thisbook covers material related to running, managing, and configuringdatabases and database servers. Adaptive Server Anywhere SQL User’s Guide This book describeshow to design and create databases; how to import, export, and modifydata; how to retrieve data; and how to build stored procedures andtriggers. Adaptive Server Anywhere SQL Reference Manual This bookprovides a complete reference for the SQL language used by AdaptiveServer Anywhere. It also describes the Adaptive Server Anywheresystem tables and procedures. Adaptive Server Anywhere Programming Guide This book describeshow to build and deploy database applications using the C, C , and Javaprogramming languages. Users of tools such as Visual Basic andPowerBuilder can use the programming interfaces provided by thosetools. It also describes the Adaptive Server Anywhere ADO.NET dataprovider. Adaptive Server Anywhere SNMP Extension Agent User’s GuideThis book describes how to configure the Adaptive Server AnywhereSNMP Extension Agent for use with SNMP management applications tomanage Adaptive Server Anywhere databases. Adaptive Server Anywhere Error Messages This book provides acomplete listing of Adaptive Server Anywhere error messages togetherwith diagnostic information.x
SQL Anywhere Studio Security Guide This book providesinformation about security features in Adaptive Server Anywheredatabases. Adaptive Server Anywhere 7.0 was awarded a TCSEC(Trusted Computer System Evaluation Criteria) C2 security rating fromthe U.S. Government. This book may be of interest to those who wish torun the current version of Adaptive Server Anywhere in a mannerequivalent to the C2-certified environment. MobiLink Administration Guide This book describes how to use theMobiLink data synchronization system for mobile computing, whichenables sharing of data between a single Oracle, Sybase, Microsoft orIBM database and many Adaptive Server Anywhere or UltraLitedatabases. MobiLink Clients This book describes how to set up and synchronizeAdaptive Server Anywhere and UltraLite remote databases. MobiLink Server-Initiated Synchronization User’s Guide This bookdescribes MobiLink server-initiated synchronization, a feature ofMobiLink that allows you to initiate synchronization from theconsolidated database. MobiLink Tutorials This book provides several tutorials that walk youthrough how to set up and run MobiLink applications. QAnywhere User’s Guide This manual describes MobiLinkQAnywhere, a messaging platform that enables the development anddeployment of messaging applications for mobile and wireless clients, aswell as traditional desktop and laptop clients. iAnywhere Solutions ODBC Drivers This book describes how to setup ODBC drivers to access consolidated databases other than AdaptiveServer Anywhere from the MobiLink synchronization server and fromAdaptive Server Anywhere remote data access. SQL Remote User’s Guide This book describes all aspects of theSQL Remote data replication system for mobile computing, whichenables sharing of data between a single Adaptive Server Anywhere orAdaptive Server Enterprise database and many Adaptive ServerAnywhere databases using an indirect link such as e-mail or file transfer. SQL Anywhere Studio Help This book includes the context-sensitivehelp for Sybase Central, Interactive SQL, and other graphical tools. It isnot included in the printed documentation set. UltraLite Database User’s Guide This book is intended for allUltraLite developers. It introduces the UltraLite database system andprovides information common to all UltraLite programming interfaces.xi
UltraLite Interface Guides A separate book is provided for eachUltraLite programming interface. Some of these interfaces are providedas UltraLite components for rapid application development, and othersare provided as static interfaces for C, C , and Java development.In addition to this documentation set, PowerDesigner and InfoMaker includetheir own onlin
databases. It describes database connections, the database server, database files, security, backup procedures, and replication with the Replication Server, as well as administration utilities and options. Audience This manual is for all users of Adaptive Server Anywhere. It is to be used i
