Sybase DC38133-01-0902-01 User Manual

SQL Remote™User’s Guide

Part number: DC38133-01-0902-01

Last modified: October 2004

Copyright ©1989–2004 Sybase, Inc. Portions copyright © 2001–2004 iAnywhere Solutions, Inc. All rights reserved.
No part of thispublication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or

otherwise, without the priorwritten permission of iAnywhereSolutions, Inc. iAnywhere Solutions, Inc. is a subsidiary of Sybase, Inc.
Sybase, SYBASE (logo), AccelaTrade, ADAWorkbench,Adaptable Windowing Environment,Adaptive Component Architecture, Adaptive Server,

Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor,Adaptive Server Enterprise Replication, Adaptive
Server 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, AvantGo
Mobile Delivery, AvantGo Mobile Document Viewer, AvantGo Mobile Inspection, AvantGo Mobile Marketing Channel, AvantGo Mobile Pharma,
AvantGo Mobile Sales, AvantGoPylon, AvantGo Pylon Application Server, AvantGo Pylon Conduit, AvantGo Pylon PIM Server,AvantGo
Pylon Pro, Backup Server, BayCam, Bit-Wise, BizTracker, Certified PowerBuilder Developer, Certified SYBASE Professional, Certified SYBASE
Professional Logo, ClearConnect, ClientServices, Client-Library, CodeBank, Column Design, ComponentPack, Connection Manager, Convoy/DM,
Copernicus, CSP, Data Pipeline, Data Workbench,DataArchitect, DatabaseAnalyzer, 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, EnterpriseClient/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 WorksBetter WhenEverything Works Together, EWA, E-Whatever, Financial Fusion, Financial Fusion (and design), Financial
Fusion Server, Formula One, Fusion Powered e-Finance,Fusion PoweredFinancial Destinations,Fusion PoweredSTP, Gateway Manager,
GeoPoint, GlobalFIX, iAnywhere, iAnywhere Solutions, ImpactNow, Industry WarehouseStudio, InfoMaker, Information Anywhere, Information
Everywhere, 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 DatabaseGateway,media.splash, MessageAnywhere
Server, 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, OmniSQLAccess 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, Partnershipsthat Work, PB-Gen,PC APT Execute, PC DB-Net, PC Net Library, PhysicalArchitect, Pocket
PowerBuilder, PocketBuilder, Power Through Knowledge, power.stop, Power++, PowerAMC, PowerBuilder, PowerBuilderFoundation Class
Library, PowerDesigner, PowerDimensions, PowerDynamo,Powering the New Economy, PowerJ, PowerScript, PowerSite, PowerSocket,
Powersoft, Powersoft Portfolio, Powersoft Professional, PowerStage, PowerStudio, PowerTips, PowerWareDesktop, PowerWare Enterprise,
ProcessAnalyst, QAnywhere, Rapport, Relational Beans, RepConnector, Replication Agent, Replication Driver, Replication Server, Replication
Server Manager, Replication Toolkit, Report Workbench, Report-Execute, Resource Manager, RW-DisplayLib, RW-Library, S.W.I.F.T. Message
Format 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, SQLServer 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, SybaseFinancial Server, Sybase Gateways, Sybase Learning Connection,
Sybase MPP, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase Synergy Program,Sybase User Workbench, Sybase
Virtual Server Architecture, SybaseWare, Syber Financial, SyberAssist, SybMD, SyBooks, System 10, System 11, System XI (logo), SystemTools,
Tabular Data Stream, The EnterpriseClient/Server Company, TheExtensible SoftwarePlatform, The Future Is Wide Open, The Learning
Connection, The Model ForClient/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 Kit
for UniCode, Versacore, Viewer, VisualWriter, VQL, Warehouse Control Center, Warehouse Studio, Warehouse WORKS, WarehouseArchitect,
Watcom,Watcom SQL, Watcom SQL Server, Web DeploymentKit, Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup SQL Server,
XA-Library, XA-Server, and XP Server are trademarks of Sybase, Inc. or its subsidiaries.

Certicom, MobileTrust, and SSL Plus are trademarks and Security Builder is aregistered trademarkof CerticomCorp. Copyright © 1997–2001
Certicom Corp. Portions are Copyright © 1997–1998, Consensus Development Corporation, a wholly owned subsidiary of Certicom Corp. All
rights reserved. Contains an implementation of NR signatures, licensed under U.S. patent 5,600,725. Protected by U.S. patents 5,787,028;
4,745,568; 5,761,305. Patents pending.

All other trademarks areproperty of their respective owners.

ii

Contents

About This Manual ix

SQL Anywhere Studio documentation . . . . . . . . . . . . . x

Documentation conventions . . . . . . . . . . . . . . . . . . . xiii

The Adaptive Server Anywhere sample database . . . . . . . xv

Finding out more and providing feedback . . . . . . . . . . . xvi

I Introduction to SQL Remote 1

1 Welcome to SQL Remote 3

About SQL Remote . . . . . . . . . . . . . . . . . . . . . . . 4

About this manual . . . . . . . . . . . . . . . . . . . . . . . . 5

2 SQL Remote Concepts 7

SQL Remote components . . . . . . . . . . . . . . . . . . . . 8

Publications and subscriptions . . . . . . . . . . . . . . . . . 11

SQL Remote features . . . . . . . . . . . . . . . . . . . . . . 13

Some sample installations . . . . . . . . . . . . . . . . . . . . 15

3 Setting Up SQL Remote 19

Setup overview . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Preparing your Adaptive Server Enterprise server . . . . . . 21

Upgrading SQL Remote for Adaptive Server Enterprise . . . 25

Uninstalling SQL Remote . . . . . . . . . . . . . . . . . . . . 26

4 Tutorials for Adaptive Server Anywhere Users 27

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

Tutorial: Adaptive ServerAnywherereplication using Sybase

Central . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Tutorial: Adaptive Server Anywhere replication using Interac-

tive SQL and dbxtract . . . . . . . . . . . . . . . . . . . 40

Start replicating data . . . . . . . . . . . . . . . . . . . . . . . 47

A sample publication . . . . . . . . . . . . . . . . . . . . . . . 51

5 A Tutorial for Adaptive Server Enterprise Users 53

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Tutorial: Adaptive Server Enterprise replication . . . . . . . . 57

Start replicating data . . . . . . . . . . . . . . . . . . . . . . . 66

iii

II Replication Design for SQL Remote 71

6 Principles of SQL Remote Design 73

Design overview . . . . . . . . . . . . . . . . . . . . . . . . . 74

How statements are replicated . . . . . . . . . . . . . . . . . 78

How data types are replicated . . . . . . . . . . . . . . . . . . 83

Who gets what? . . . . . . . . . . . . . . . . . . . . . . . . . 86

Replication errors and conflicts . . . . . . . . . . . . . . . . . 88

7 SQL Remote Design for Adaptive Server Anywhere 91

Design overview . . . . . . . . . . . . . . . . . . . . . . . . . 92

Publishing data . . . . . . . . . . . . . . . . . . . . . . . . . . 93

Publication design for Adaptive Server Anywhere . . . . . . . 102

Partitioning tables that do not contain the subscription ex-

pression . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

Sharing rows among several subscriptions . . . . . . . . . . 112

Managing conflicts . . . . . . . . . . . . . . . . . . . . . . . . 120

Ensuring unique primary keys . . . . . . . . . . . . . . . . . . 129

Creating subscriptions . . . . . . . . . . . . . . . . . . . . . . 139

8 SQL Remote Design for Adaptive Server Enterprise 141

Design overview . . . . . . . . . . . . . . . . . . . . . . . . . 142

Creating publications . . . . . . . . . . . . . . . . . . . . . . . 143

Publication design for Adaptive Server Enterprise . . . . . . . 147

Partitioning tables that do not contain the subscription column 149

Sharing rows among several subscriptions . . . . . . . . . . 157

Managing conflicts . . . . . . . . . . . . . . . . . . . . . . . . 165

Ensuring unique primary keys . . . . . . . . . . . . . . . . . . 175

Creating subscriptions . . . . . . . . . . . . . . . . . . . . . . 181

III SQL Remote Administration 183

9 Deploying and Synchronizing Databases 185

Deployment overview . . . . . . . . . . . . . . . . . . . . . . 186

Test before deployment . . . . . . . . . . . . . . . . . . . . . 187

Synchronizing databases . . . . . . . . . . . . . . . . . . . . 189

Using the extraction utility . . . . . . . . . . . . . . . . . . . . 191

Synchronizing data over a message system . . . . . . . . . . 198

10 SQL Remote Administration 199

Management overview . . . . . . . . . . . . . . . . . . . . . . 200

Managing SQL Remote permissions . . . . . . . . . . . . . . 201

Using message types . . . . . . . . . . . . . . . . . . . . . . 210

iv

Running the Message Agent . . . . . . . . . . . . . . . . . . 223

Tuning Message Agent performance . . . . . . . . . . . . . . 228

Encoding and compressing messages . . . . . . . . . . . . . 235

The message tracking system . . . . . . . . . . . . . . . . . . 237

11 Administering SQL Remote for Adaptive Server Anywhere241

Running the Message Agent . . . . . . . . . . . . . . . . . . 242

Error reporting and handling . . . . . . . . . . . . . . . . . . . 245

Transaction log and backup management . . . . . . . . . . . 249

Using passthrough mode . . . . . . . . . . . . . . . . . . . . 260

12 Administering SQL Remote for Adaptive Server Enterprise263

How the Message Agent for Adaptive Server Enterprise works 264

Running the Message Agent . . . . . . . . . . . . . . . . . . 269

Error reporting and handling . . . . . . . . . . . . . . . . . . . 271

Adaptive Server Enterprise transaction log and backup man-

agement . . . . . . . . . . . . . . . . . . . . . . . . . . . 272

Making schema changes . . . . . . . . . . . . . . . . . . . . 275

Using passthrough mode . . . . . . . . . . . . . . . . . . . . 276

13 Using SQL Remote with Replication Server 277

When you need to use the SQL Remote Open Server . . . . 278

Architecture for Replication Server/SQL Remote installations 279

Setting up SQL Remote Open Server . . . . . . . . . . . . . 282

Configuring Replication Server . . . . . . . . . . . . . . . . . 285

Other issues . . . . . . . . . . . . . . . . . . . . . . . . . . . 287

IV Reference 289

14 Utilities and Options Reference 291

The Message Agent . . . . . . . . . . . . . . . . . . . . . . . 292

The Database Extraction utility . . . . . . . . . . . . . . . . . 302

The SQL Remote Open Server . . . . . . . . . . . . . . . . . 312

SQL Remote options . . . . . . . . . . . . . . . . . . . . . . . 315

SQL Remote event-hook procedures . . . . . . . . . . . . . . 320

15 System Objects for Adaptive Server Anywhere 325

SQL Remote system tables . . . . . . . . . . . . . . . . . . . 326

SQL Remote system views . . . . . . . . . . . . . . . . . . . 333

16 System Objects for Adaptive Server Enterprise 337

SQL Remote system tables . . . . . . . . . . . . . . . . . . . 338

SQL Remote system views . . . . . . . . . . . . . . . . . . . 346

v

Stable Queue tables . . . . . . . . . . . . . . . . . . . . . . . 350

17 Command Reference for Adaptive Server Anywhere 353

ALTER REMOTE MESSAGE TYPE statement . . . . . . . . 355

CREATE PUBLICATION statement . . . . . . . . . . . . . . . 356

CREATE REMOTE MESSAGE TYPE statement . . . . . . . 357

CREATE SUBSCRIPTION statement . . . . . . . . . . . . . . 358

CREATE TRIGGER statement . . . . . . . . . . . . . . . . . 359

DROP PUBLICATION statement . . . . . . . . . . . . . . . . 361

DROP REMOTE MESSAGE TYPE statement . . . . . . . . . 362

DROP SUBSCRIPTION statement . . . . . . . . . . . . . . . 363

GRANT CONSOLIDATE statement . . . . . . . . . . . . . . . 364

GRANT PUBLISH statement . . . . . . . . . . . . . . . . . . 365

GRANT REMOTE statement . . . . . . . . . . . . . . . . . . 366

GRANT REMOTE DBA statement . . . . . . . . . . . . . . . 367

PASSTHROUGH statement . . . . . . . . . . . . . . . . . . . 368

REMOTE RESET statement . . . . . . . . . . . . . . . . . . 369

REVOKE CONSOLIDATE statement . . . . . . . . . . . . . . 370

REVOKE PUBLISH statement . . . . . . . . . . . . . . . . . 371

REVOKE REMOTE statement . . . . . . . . . . . . . . . . . 372

REVOKE REMOTE DBA statement . . . . . . . . . . . . . . 373

SET REMOTE OPTION statement . . . . . . . . . . . . . . . 374

START SUBSCRIPTION statement . . . . . . . . . . . . . . . 375

STOP SUBSCRIPTION statement . . . . . . . . . . . . . . . 376

SYNCHRONIZE SUBSCRIPTION statement . . . . . . . . . 377

UPDATE statement . . . . . . . . . . . . . . . . . . . . . . . . 378

18 Command Reference for Adaptive Server Enterprise 379

sp_add_article procedure . . . . . . . . . . . . . . . . . . . . 381

sp_add_article_col procedure . . . . . . . . . . . . . . . . . . 383

sp_add_remote_table procedure . . . . . . . . . . . . . . . . 384

sp_create_publication procedure . . . . . . . . . . . . . . . . 386

sp_drop_publication procedure . . . . . . . . . . . . . . . . . 387

sp_drop_remote_type procedure . . . . . . . . . . . . . . . . 388

sp_drop_sql_remote procedure . . . . . . . . . . . . . . . . . 389

sp_grant_consolidate procedure . . . . . . . . . . . . . . . . 390

sp_grant_remote procedure . . . . . . . . . . . . . . . . . . . 393

sp_link_option procedure . . . . . . . . . . . . . . . . . . . . 396

sp_modify_article procedure . . . . . . . . . . . . . . . . . . 398

sp_modify_remote_table procedure . . . . . . . . . . . . . . 400

sp_passthrough procedure . . . . . . . . . . . . . . . . . . . 402

sp_passthrough_piece procedure . . . . . . . . . . . . . . . 403

sp_passthrough_stop procedure . . . . . . . . . . . . . . . . 405

sp_passthrough_subscription procedure . . . . . . . . . . . . 406

vi

sp_passthrough_user procedure . . . . . . . . . . . . . . . . 407

sp_populate_sql_anywhere procedure . . . . . . . . . . . . . 408

sp_publisher procedure . . . . . . . . . . . . . . . . . . . . . 409

sp_queue_clean procedure . . . . . . . . . . . . . . . . . . . 410

sp_queue_confirmed_delete_old procedure . . . . . . . . . . 411

sp_queue_confirmed_transaction procedure . . . . . . . . . 412

sp_queue_delete_old procedure . . . . . . . . . . . . . . . . 413

sp_queue_drop procedure . . . . . . . . . . . . . . . . . . . 414

sp_queue_dump_database procedure . . . . . . . . . . . . . 415

sp_queue_dump_transaction procedure . . . . . . . . . . . . 416

sp_queue_get_state procedure . . . . . . . . . . . . . . . . . 417

sp_queue_log_transfer_reset procedure . . . . . . . . . . . . 418

sp_queue_read procedure . . . . . . . . . . . . . . . . . . . 419

sp_queue_reset procedure . . . . . . . . . . . . . . . . . . . 420

sp_queue_set_confirm procedure . . . . . . . . . . . . . . . 421

sp_queue_set_progress procedure . . . . . . . . . . . . . . . 422

sp_queue_transaction procedure . . . . . . . . . . . . . . . . 423

sp_remote procedure . . . . . . . . . . . . . . . . . . . . . . 424

sp_remote_option procedure . . . . . . . . . . . . . . . . . . 425

sp_remote_type procedure . . . . . . . . . . . . . . . . . . . 427

sp_remove_article procedure . . . . . . . . . . . . . . . . . . 428

sp_remove_article_col procedure . . . . . . . . . . . . . . . . 429

sp_remove_remote_table procedure . . . . . . . . . . . . . . 430

sp_revoke_consolidate procedure . . . . . . . . . . . . . . . 431

sp_revoke_remote procedure . . . . . . . . . . . . . . . . . . 432

sp_subscription procedure . . . . . . . . . . . . . . . . . . . 433

sp_subscription_reset procedure . . . . . . . . . . . . . . . . 434

V Appendices 435

A SQL Remote for Adaptive Server Enterprise and Adaptive

Server Anywhere: Differences 437

Types of difference . . . . . . . . . . . . . . . . . . . . . . . . 438

Differences in functionality . . . . . . . . . . . . . . . . . . . . 439

Differences in approach . . . . . . . . . . . . . . . . . . . . . 440

Limitations for Enterprise to Enterprise replication . . . . . . 442

B Supported Platforms and Message Links 445

Supported message systems . . . . . . . . . . . . . . . . . . 446

Supported operating systems . . . . . . . . . . . . . . . . . . 447

Index 449

vii

viii

About This Manual

Subject This book describes all aspects of the SQL Remote data replication system

for mobile computing, which enables sharing of data between a single
Adaptive Server Anywhere or Adaptive Server Enterprise database and
many Adaptive Server Anywhere databases using an indirect link such as
e-mail or file transfer.

Audience This book is for users of Adaptive Server Anywhere and Adaptive Server

Enterprise who wish to add SQL Remote replication to their information
systems.

Before you begin ☞ For a comparison of SQL Remote with other replication technologies,

see “ Introducing Replication Technologies” [Introducing SQL Anywhere

Studio, page 21].

ix

SQL Anywhere Studio 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
Studio documentation

The 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 separate
PDF files for each book; and as printed books that you can purchase. The
documentation consists of the following books:

♦ Introducing SQL Anywhere Studio This book provides an overview of

the SQL Anywhere Studio database management and synchronization
technologies. It includes tutorials to introduce you to each of the pieces
that make up SQL Anywhere Studio.

♦ What’s New in SQL Anywhere Studio This book is for users of

previous versions of the software. It lists new features in this and
previous releases of the product and describes upgrade procedures.

♦ Adaptive Server Anywhere Database Administration Guide This

book covers material related to running, managing, and configuring
databases and database servers.

♦ Adaptive Server Anywhere SQL User’s Guide 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.

♦ Adaptive Server Anywhere SQL Reference Manual This book

provides a complete reference for the SQL language used by Adaptive
Server Anywhere. It also describes the Adaptive Server Anywhere
system tables and procedures.

♦ Adaptive Server Anywhere Programming Guide This book describes

how to build and deploy database applications using the C, C++, and Java
programming languages. Users of tools such as Visual Basic and
PowerBuilder can use the programming interfaces provided by those
tools. It also describes the Adaptive Server Anywhere ADO.NET data
provider.

♦ Adaptive Server Anywhere SNMP Extension Agent User’s Guide

This book describes how to configure the Adaptive Server Anywhere
SNMP Extension Agent for use with SNMP management applications to
manage Adaptive Server Anywhere databases.

♦ Adaptive Server Anywhere Error Messages This book provides a

complete listing of Adaptive Server Anywhere error messages together
with diagnostic information.

x

♦ SQL Anywhere Studio Security Guide This book provides

information about security features in Adaptive Server Anywhere
databases. Adaptive Server Anywhere 7.0 was awarded a TCSEC
(Trusted Computer System Evaluation Criteria) C2 security rating from
the U.S. Government. This book may be of interest to those who wish to
run the current version of Adaptive Server Anywhere in a manner
equivalent to the C2-certified environment.

♦ MobiLink Administration Guide This book describes how to use the

MobiLink data synchronization system for mobile computing, which
enables sharing of data between a single Oracle, Sybase, Microsoft or
IBM database and many Adaptive Server Anywhere or UltraLite
databases.

♦ MobiLink Clients This book describes how to set up and synchronize

Adaptive Server Anywhere and UltraLite remote databases.

♦ MobiLink Server-Initiated Synchronization User’s Guide This book

describes MobiLink server-initiated synchronization, a feature of
MobiLink that allows you to initiate synchronization from the
consolidated database.

♦ MobiLink Tutorials This book provides several tutorials that walk you

through how to set up and run MobiLink applications.

♦ QAnywhere User’s Guide This manual describes MobiLink

QAnywhere, a messaging platform that enables the development and
deployment of messaging applications for mobile and wireless clients, as
well as traditional desktop and laptop clients.

♦ iAnywhere Solutions ODBC Drivers This book describes how to set

up ODBC drivers to access consolidated databases other than Adaptive
Server Anywhere from the MobiLink synchronization server and from
Adaptive Server Anywhere remote data access.

♦ SQL Remote User’s Guide This book describes all aspects of the

SQL Remote data replication system for mobile computing, which
enables sharing of data between a single Adaptive Server Anywhere or
Adaptive Server Enterprise database and many Adaptive Server
Anywhere databases using an indirect link such as e-mail or file transfer.

♦ SQL Anywhere Studio Help This book includes the context-sensitive

help for Sybase Central, Interactive SQL, and other graphical tools. It is
not included in the printed documentation set.

♦ UltraLite Database User’s Guide This book is intended for all

UltraLite developers. It introduces the UltraLite database system and
provides information common to all UltraLite programming interfaces.

xi

♦ UltraLite Interface Guides A separate book is provided for each

UltraLite programming interface. Some of these interfaces are provided
as UltraLite components for rapid application development, and others
are provided as static interfaces for C, C++, and Java development.

In addition to this documentation set, PowerDesigner and InfoMaker include
their own online documentation.

Documentation formats SQL Anywhere Studio provides documentation in the following formats:

♦ Online documentation The online documentation contains the

complete SQL Anywhere Studio documentation, including both the
books and the context-sensitive help for SQL Anywhere tools. The online
documentation is updated with each maintenance release of the product,
and is the most complete and up-to-date source of documentation.

To access the online documentation on Windows operating systems,
choose Start ➤ Programs ➤ SQL Anywhere 9 ➤ Online Books. You can
navigate the online documentation using the HTML Help table of
contents, index, and search facility in the left pane, as well as using the
links and menus in the right pane.

To access the online documentation on UNIX operating systems, see the
HTML documentation under your SQL Anywhere installation.

♦ PDF books The SQL Anywhere books are provided as a set of PDF

files, viewable with Adobe Acrobat Reader.
The PDF books are accessible from the online books, or from the

Windows Start menu.

xii

♦ Printed books The complete set of books is available from Sybase

sales or from eShop, the Sybase online store, at

http://eshop.sybase.com/eshop/documentation

.

Documentation conventions

This section lists the typographic and graphical conventions used in this
documentation.

Syntax conventions The following conventions are used in the SQL syntax descriptions:

♦ Keywords All SQL keywords appear in upper case, like the words

ALTER TABLE in the following example:

ALTER TABLE [ owner.]table-name

♦ Placeholders Items that must be replaced with appropriate identifiers

or expressions are shown like the words
following example:

ALTER TABLE [ owner.]table-name

♦ Repeating items Lists of repeating items are shown with an element of

the list followed by an ellipsis (three dots), like
following example:

ADD column-definition [ column-constraint, .. . ]

One or more list elements are allowed. In this example, if more than one
is specified, they must be separated by commas.

♦ Optional portions Optional portions of a statement are enclosed by

square brackets.

RELEASE SAVEPOINT [ savepoint-name ]

owner

and

column-constraint

table-name

in the

in the

These square brackets indicate that the
square brackets should not be typed.

♦ Options When none or only one of a list of items can be chosen,

vertical bars separate the items and the list is enclosed in square brackets.

[ ASC | DESC ]

For example, you can choose one of ASC, DESC, or neither. The square
brackets should not be typed.

♦ Alternatives When precisely one of the options must be chosen, the

alternatives are enclosed in curly braces and a bar is used to separate the
options.

[ QUOTES { ON | OFF } ]

If the QUOTES option is used, one of ON or OFF must be provided. The
brackets and braces should not be typed.

savepoint-name

is optional. The

xiii

Graphic icons The following icons are used in this documentation.

API

♦ A client application.

♦ A database server, such as Sybase Adaptive Server Anywhere.

♦ A database. In some high-level diagrams, the icon may be used to

represent both the database and the database server that manages it.

♦ Replication or synchronization middleware. These assist in sharing data

among databases. Examples are the MobiLink Synchronization Server
and the SQL Remote Message Agent.

♦ A programming interface.

xiv

The Adaptive Server Anywhere sample database

fin_code

code char(2) <pk>
type char(10)
description char(50)

product

id integer  <pk>
name char(15)
description  char(30)
size char(18)
color char(6)
quantity integer
unit_price numeric (15,2)

asademo.db

contact

id integer <pk>
last_name char(15)
first_name char(15)
title char(2)
street char(30)
city char(20)
state char(2)
zip char(5)
phone char(10)
fax char(10)

customer

id integer  <pk>
fname char(15)
lname char(20)
address char(35)
city char(20)
state char(2)
zip char(10)
phone char(20)
company_name char(35)

fin_data

year char(4) <pk>
quarter char(2) <pk>
code char(2) <pk,fk>
amount numeric(9)

sales_order

id  integer <pk>
cust_id integer <fk>
order_date date
fin_code_id char(2) <fk>
region char(7)
sales_rep integer <fk>

sales_order_items

id integer <pk,fk>
line_id smallint <pk>
prod_id integer <fk>
quantity integer
ship_date date

department

dept_id integer <pk>
dept_name char(40)
dept_head_id integer <fk>

Employee

emp_id integer  <pk>
manager_id integer
emp_fname char(20)
emp_lname char(20)
dept_id integer <fk>
street char(40)
city char(20)
state char(4)
zip_code char(9)
phone char(10)
status char(1)
ss_number char(11)
salary numeric(20,3)
start_date date
termination_date date
birth_date date
bene_health_ins char(1)
bene_life_ins char(1)
bene_day_care char(1)
sex char(1)

id = prod_id

id = cust_id

id = id

emp_id = sales_rep

emp_id = dept_head_id

code = fin_code_id

dept_id = dept_id

code = code

Many of the examples throughout the documentation use the Adaptive
Server Anywhere sample database.

The sample database is held in a file named
your SQL Anywhere directory.

The sample database represents a small company. It contains internal
information about the company (employees, departments, and finances) as
well as product information and sales information (sales orders, customers,
and contacts). All information in the database is fictional.

The following figure shows the tables in the sample database and how they
relate to each other.

asademo.db

, and is located in

xv

Finding out more and providing feedback

Finding out more Additional information and resources, including a code exchange, are

available at the iAnywhere Developer Network at

http://www.ianywhere.com/developer/

If you have questions or need help, you can post messages to the iAnywhere
Solutions newsgroups listed below.

When you write to one of these newsgroups, always provide detailed
information about your problem, including the build number of your version
of SQL Anywhere Studio. You can find this information by typing dbeng9

-v at a command prompt.

.

The newsgroups are located on the
newsgroups include the following:

♦ sybase.public.sqlanywhere.general
♦ sybase.public.sqlanywhere.linux
♦ sybase.public.sqlanywhere.mobilink
♦ sybase.public.sqlanywhere.product_futures_discussion
♦ sybase.public.sqlanywhere.replication
♦ sybase.public.sqlanywhere.ultralite
♦ ianywhere.public.sqlanywhere.qanywhere

Newsgroup disclaimer

iAnywhere Solutions has no obligation to provide solutions, information
or ideas on its newsgroups, nor is iAnywhere Solutions obliged to provide
anything other than a systems operator to monitor the service and ensure
its operation and availability.

iAnywhere Solutions Technical Advisors as well as other staff assist on the
newsgroup service when they have time available. They offer their help
on a volunteer basis and may not be available on a regular basis to provide
solutions and information. Their ability to help is based on their workload.

forums.sybase.com

news server. The

Feedback We would like to receive your opinions, suggestions, and feedback on this

documentation.
You can e-mail comments and suggestions to the SQL Anywhere

documentation team at [e-mail]. Although we do not reply
to e-mails sent to that address, we read all suggestions with interest.

xvi

In addition, you can provide feedback on the documentation and the
software through the newsgroups listed above.

xvii

xviii

PART I

INTRODUCTION TO SQL

REMOTE

This part describes the concepts, architecture, and features of SQL Remote.

The material in this part refers to both SQL Remote for Adaptive Server

Anywhere and SQL Remote for Adaptive Server Enterprise.

CHAPTER 1

Welcome to SQL Remote

About this chapter This chapter introduces SQL Remote and the documentation.
Contents

Topic: page

About SQL Remote 4
About this manual 5

3

About SQL Remote

SQL Remote is a data-replication technology designed for two-way
replication between a consolidated data server and large numbers of remote
databases, typically including many mobile databases.

SQL Remote replication is message based, and requires no direct
server-to-server connection. An occasional dial-up or e-mail link is
sufficient.

Administration and resource requirements at the remote sites are minimal.
The time lag between the consolidated and remote databases is configurable,
and can range from minutes to hours or days.

Sybase SQL Remote technology is provided in two forms:
♦ SQL Remote for Adaptive Server Anywhere Enables replication

between a consolidated Adaptive Server Anywhere database and a large
number of remote databases.

♦ SQL Remote for Adaptive Server Enterprise Enables replication

between a consolidated Adaptive Server Enterprise database and a large
number of remote Adaptive Server Anywhere databases.

This book describes both of these technologies.
In a SQL Remote installation, you must have properly licensed SQL Remote

software at each participating database.

☞ For a detailed introduction to SQL Remote concepts and features, see

“SQL Remote Concepts” on page 7.

☞ For a list of supported operating systems and message links, see

“Supported Platforms and Message Links” on page 445.

4

About this manual

This manual describes how to design, build, and maintain SQL Remote
installations.

The manual includes the following parts.
♦ Introduction to SQL Remote Replication concepts and features of

SQL Remote.

♦ Replication Design for SQL Remote Designing SQL Remote

installations.

♦ SQL Remote Administration Deploying SQL Remote databases and

administering a running SQL Remote setup.

♦ Reference SQL Remote commands, system tables, and other reference

material.

Product installation

This section describes installation of SQL Remote for Adaptive Server
Enterprise. If you obtained SQL Remote as part of another product, consult
the installation instructions for the product you purchased.

Chapter 1. Welcome to SQL Remote

❖ To install the SQL Remote software ( Windows )

1. Insert the CD-ROM into your CD-ROM drive.

2. If the installation program does not start automatically, start the
application on the CD-ROM.

3. Follow the instructions in the installation program.

❖ To install the SQL Remote software ( UNIX )

1. Consult the instructions for your operating system in the

Anywhere Read Me First

If you are using SQL Remote for Adaptive Server Enterprise, you must
install SQL Remote into any database you wish to replicate.

booklet.

Adaptive Server

☞ For information about installing SQL Remote into a database, see

“Setting Up SQL Remote” on page 19.

setup

5

CHAPTER 2

SQL Remote Concepts

About this chapter This chapter introduces the concepts, design goals, and features of

SQL Remote.

Contents

Topic: page

SQL Remote components 8
Publications and subscriptions 11
SQL Remote features 13
Some sample installations 15

7

SQL Remote components

Message

system

transport

Data server

Message

Agent

Message

system client

The following components are required for SQL Remote:
♦ Data server An Adaptive Server Anywhere or Adaptive Server

Enterprise database-management system is required at each site to
maintain the data.

♦ Message Agent A SQL Remote Message Agent is required at the

consolidated site and at each remote site to send and receive SQL Remote
messages.

The Message Agent connects to the data server by a client/server
connection. It may run on the same machine as the data server or on a
different machine.

♦ Database extraction utility The extraction utility is used to prepare

remote databases from a consolidated database, during development and
testing, and also at deployment time.

♦ Message system client software SQL Remote uses existing message

systems to transport replication messages. A file-sharing “message
system” is provided, which does not require client software. Each
computer involved in SQL Remote replication using a message system
other than file sharing must have that message system installed.

♦ Client applications The applications that work with SQL Remote

databases are standard client/server database applications.

8

The data server

Client applications

Chapter 2. SQL Remote Concepts

The data server may be an Adaptive Server Enterprise or an Adaptive Server
Anywhere server. At the remote site the data server is commonly an
Adaptive Server Anywhere personal server, but can also be an Adaptive
Server Enterprise or Adaptive Server Anywhere server.

Client applications work with the data in the database. Client applications
use one of the client/server interfaces supported by the data server:

♦ For Adaptive Server Anywhere, the client application may use ODBC,

Embedded SQL, or Sybase Open Client to work with Adaptive Server
Anywhere.

♦ For Adaptive Server Enterprise, the client application may use one of the

Sybase Client Server interfaces, ODBC, or Embedded SQL.

Client applications do not have to know if they are using a consolidated or
remote database. From the client application perspective, there is no
difference.

The Message Agent

The SQL Remote Message Agent sends and receives replication messages.
It is a client application that sends and receives messages from database to
database. The Message Agent must be installed at both the consolidated and
at the remote sites.

For Adaptive Server Anywhere, the Message Agent is a program called

dbremote.exe

For Adaptive Server Enterprise, the Message Agent is a program called

ssremote.exe

on PC operating systems, and

on PC operating systems, and

dbremote

ssremote

on UNIX.

on UNIX.

9

Remote

database

Consolidated

database

Message

System

Message Agent Message Agent

Message system client

If you are using a shared file message system, no message system client is
needed.

If you are using an e-mail or other message system, you must have a
message system for that client in order to send and receive messages.

10

Publications and subscriptions

A two-table synchronization definition

Article 1: all of

table A

Article 2: some rows and

columns from table B

+

X

X

X

X

X

X

X X X X X

X X X X X

XXXXX

X

X

X

X

X

X

X

X

X

X

The data that is replicated by SQL Remote is arranged in publications. Each
database that shares information in a publication must have a subscription
to the publication.

Chapter 2. SQL Remote Concepts

Data is organized into
publications

The publication is a database object describing data to be replicated.
Remote users of the database who wish to receive a publication do so by
subscribing to a publication.

A publication may include data from several database tables. Each table’s
contribution to a publication is called an article. Each article may consist of
a whole table, or a subset of the rows and columns in a table.

Periodically, the changes made to each publication in a database are
replicated to all subscribers to that publication. These replications are called
publication updates.

Messages are always
sent both ways

Remote databases subscribe to publications on the consolidated database so
that they can receive data from the consolidated database. To do this, a
subscription is created at the consolidated database, identifying the
subscriber by name and by the publication they are to receive.

SQL Remote always involves messages being sent two ways. The
consolidated database sends messages containing publication updates to
remote databases, and remote databases also send messages to the
consolidated database.

For example, if data in a publication at a consolidated database is updated,
those updates are sent to the remote databases. And even if the data is never
updated at the remote database, confirmation messages must still be sent
back to the consolidated database, to keep track of the status of the

11

replication.

Consolidated

database

Remote

database

Publish Subscribe

Publish

Subscribe

Data updates and

receipt confirmations

Data updates and

receipt confirmations

Both databases
subscribe

Messages must be sent both ways, so not only does a remote database
subscribe to a publication created at the consolidated database, but the
consolidated database must subscribe to a corresponding publication created
at the remote database.

When remote database users modify their own copies of the data, their
changes are replicated to the consolidated database. When the messages
containing the changes are applied at the consolidated database the changes
become part of the consolidated database’s publication, and are included in
the next round of updates to all remote sites (except the one it came from).
In this way, replication from remote site to remote site takes place via the
consolidated database.

Synchronizing a remote
database

12

When a subscription is initially set up, the two databases must be brought to
a state where they both have the same set of information, ready to start
replication. This process of setting up a remote database to be consistent
with the consolidated database is called synchronization. Synchronization
can be carried out manually, but the database extraction utility automates the
process. You can run the Extraction utility as a command-line utility or, if
you are using an Adaptive Server Anywhere consolidated database, from
Sybase Central.

The appropriate publication and subscription are created automatically at
remote databases when you use the SQL Remote database extraction utility
to create a remote database.