Sybase DC38133-01-0902-01 - page 475

Index

PUBLISH 35,42, 201

REMOTE 35, 42, 204

revokingREMOTE 207

SQLRemote administration 200

platforms

SQLRemote supported operating systems 445

policyexample

SQLRemote for ASA publications 112

polling

messages 230

pop3_hostcontrol parameter

SMTPmessage type 219

pop3_passwordcontrol parameter

SMTPmessage type 219

pop3_useridcontrol parameter

SMTPmessage type 219

portcontrol parameter

FTPmessage type 216

primarykey pools

generatingunique values using default global

autoincrementin SQL Remote for ASA 129

replenishingin SQL Remote for ASA 135

replenishingin SQL Remote for ASE 177

replicating 134

replicatingwith SQL Remote for ASE 176

usingin SQL Remote for ASE 175

primarykeys

generatingunique values in SQL Remote for

ASA 129

generatingunique values using pools for SQL

Remotefor ASA 133

replication 127,129, 173

replicationerrors 88

SQLRemote for ASA 127

SQLRemote for ASE 173

SQLRemote publications 78

SQLRemote publications for ASA 102

SQLRemote publications for ASE 147

uniquenessin SQL Remote for ASE 175

proceduregroups

extractionutility 197

procedures

correspondingstatements 440

passthroughmode 262

replicating 80

SQLRemote 80

SQLRemote Open Server 287

proﬁles

MicrosoftExchange 221

properties

articlesin SQL Remote for ASA 93

messagetype properties 211

publicationsin SQL Remote for ASA 93

publicationcreation wizard

column-wisepartitioning in SQL Remote for

ASA 94

creatingarticles using a subscription expression

inSQL Remote for ASA 97

creatingSQL Remote for ASA publications 93

usinga WHERE clause in SQL Remote for ASA

96

publicationdesign

formany SQL Remote for ASA subscribers 97

formany SQL Remote subscribers for ASE 145

SQLRemote for ASA 91

SQLRemote for ASE 141

usingsubscription expressions in SQL Remote

forASA 97

usingsubscription expressions in SQL Remote

forASE 145

publicationSQL Remote table

ASA 328

ASE 341

publications

alteringin SQL Remote for ASA 99

avoidingreferential integrity errors in SQL

Remotefor ASE 173

column-wisepartitioning in SQL Remote for

ASA 94

column-wisepartitioning in SQL Remote for

ASE 143

creatingin SQL Remote for ASA 93

creatingin SQL Remote for ASE 143

designingin SQL Remote 74,88

designingin SQL Remote for ASA 102,127

designingin SQL Remote for ASE 147

droppingfrom SQL Remote for ASA 100

foreignkeys in SQL Remote for ASA 102, 105

foreignkeys in SQL Remote for ASE 147,149

lockingin SQL Remote for ASA 103

lockingin SQL Remote for ASE 147

managingSQL Remote for ASA subscriptions

139

many-to-manyrelationships in SQL Remote for

457

Contents

Main ii Contents I Introduction to SQL Remote 1 II Replication Design for SQL Remote 71 III SQL Remote Administration 183 IV Reference 289 Page V Appendices 435 Page About This Manual SQL Anywhere Studio documentation Page http://eshop.sybase.com/eshop/documentation Documentation conventions table-name savepoint-name column-constraint owner Page The Adaptive Server Anywhere sample database xv asademo.db asademo.db Finding out more and providing feedback http://www.ianywhere.com/developer/ forums.sybase.com Page Page Page Page Page About SQL Remote About this manual Product installation setup AdaptiveServer AnywhereRead Me First Page CHAPTER 2 SQL Remote Concepts SQL Remote components Data server Message Agent Message system client The data server Client applications dbremote.exe The Message Agent Message system client Publications and subscriptions A two-table synchronization definition + Publish Subscribe Publish Subscribe SQL Remote features Page Some sample installations Server-to-laptop replication for mobile workforces Server-to-server replication among ofces Page Page CHAPTER 3 Setting Up SQL Remote Setup overview Preparing your Adaptive Server Enterprise server Ensuring TEMPDB is large enough Installing the SQL Remote system objects Page Command-line installation of the stable queue logle password login_id server-name Page Upgrading SQL Remote for Adaptive Server Enterprise ssupdate.sql squpdate.sql ssupdate.sql Uninstalling SQL Remote CHAPTER 4 Tutorials for Adaptive Server Anywhere Users Introduction Goals The database Page Replication goals Sybase Central or command-line utilities Tutorial: Adaptive Server Anywhere replication using Sybase Central Preparing for the Sybase Central replication tutorial c:\tutorial\HQ.db Setting up a consolidated database Page Page Page Set up the remote database in Sybase Central c:\tutorial\eld.db Tutorial: Adaptive Server Anywhere replication using Interactive SQL and dbxtract Preparing for the replication tutorial Set up the consolidated database Page Page Set up the remote database Page Page Start replicating data Enter data at the consolidated database Send data from the consolidated database Receive data at the remote database Replicate from the remote database to the consolidated database Page A sample publication salespub.sql path Page Page Introduction Goals The database Replication goals 55 Page Tutorial: Adaptive Server Enterprise replication First steps server-name Page Setting up the consolidated database Page Page Page Page c:\tutorial\eld rtsql Page Start replicating data Enter data at the consolidated database Send data from the consolidated database Receive data at the remote database Replicate from the remote database to the consolidated database Page Page Page Page CHAPTER 6 Principles of SQL Remote Design Ensuring compatible databases test.db Using compatible sort orders and character sets locales.dat Page Chapter 6. Principles of SQL Remote Design 77 How statements are replicated SQL Remote replicates SQL statements that modify data Replication of inserts and deletes Replication of updates > Replication of procedures SQL Remote replicates procedures by replicating the actions of a procedure. Replication of triggers Page Page How data types are replicated Replication of blobs Replication of dates and times language-name Who gets what? subscriptions Page Replication errors and conicts Replication errors Replication conicts TrackingSQL errors procedure-name Page CHAPTER 7 SQL Remote Design for Adaptive Server Anywhere Page Publishing data Publishing whole tables Publishing only some columns in a table Publishing only some rows in a table Publishing only some rows using a WHERE clause Publishing only some rows using a subscription expression Page Altering existing publications Dropping publications Notes on publications Publication design for Adaptive Server Anywhere Design issues overview Conditions for valid articles Design tips for performance Page Partitioning tables that do not contain the subscription expression The Contact example Page Partitioning the Customer table in the Contact example Partitioning the Contact table in the Contact example Territory realignment in the Contact example Page table-name publication-name subscription-expression Page Page Sharing rows among several subscriptions The Policy example Chapter 7. SQL Remote Design for AdaptiveSer verAnywhere Page Territory realignment with a many-to-manyrelationship Page Page Using the Subscribe_by_remote option with many-to-many relationships Page Managing conicts How SQL Remote handles conicts Implementing conict resolution Using conict resolution triggers table-list Conict resolution examples Page Page Designing to avoid referential integrity errors Designing triggers to avoid errors Page Ensuring unique primary keys Using global autoincrement default column values Page Page Page Using primary key pools The primary key pool table Replicating the primary key pool Filling and replenishing the key pool Page Adding new customers Primary key pool summary Creating subscriptions Page CHAPTER 8 SQL Remote Design for Adaptive Server Enterprise Page Creating publications Creating whole-table articles Creating articles containing some of the columns in a table Creating articles containing some of the rows in a table column Notes on articles Publication design for Adaptive Server Enterprise Design issues overview Conditions for valid articles Page Partitioning tables that do not contain the subscription column The Contact example Page Territory realignment in the Contact example Partitioning the Customer table in the Contact example Adding a subscription-list column to the Contact table Page Contact Customer Maintaining the subscription-list column Tuningextraction performance Page Sharing rows among several subscriptions The Policy example Page rep_key Maintaining the subscription-list column Page Page Tuningextraction performance for shared rows Page Using the Subscribe_by_remote option with many-to-many relationships Managing conicts How SQL Remote handles conicts Implementing conict resolution old_row_table remote_row_table old_row_table remote_row_table A rst conict resolution example after A second conict resolution example Page Page Designing to avoid referential integrity errors Page Ensuring unique primary keys The primary key pool Replicating the primary key pool Filling and replenishing the key pool Adding new customers Testing the keypool Primary key pool summary Creating subscriptions Page Page Page CHAPTER 9 Deploying and Synchronizing Databases Deployment overview Test before deployment Changes to avoid on a running system Page Synchronizing databases Donot execute SYNCHRONIZE SUBSCRIPTION or sp_subscription synchronize at a remote database. Mixed operating systems and database extraction Notes on synchronization and extraction Using the extraction utility Creating a database from the reload les Before extracting a database Using the extraction utility from Sybase Central Designing an efcient extraction procedure Page Extracting groups Limits to using the extraction utility Using the extraction utility for Adaptive Server Enterprise Transact-SQLCompatibility AdaptiveServer Anywhere Users Guide Synchronizing data over a message system dbunload CHAPTER 10 SQL Remote Administration Management overview Managing SQL Remote permissions Granting and revoking PUBLISH permissions Page Page Granting and revoking REMOTEand CONSOLIDATE permissions Page Page Page Assigning permissions in multi-tier installations Page Using message types Working with message types Page Page address-string Setting message type control parameters smtp user:\dbr43 sqlremote The le message system The ftp message system Page The SMTP message system Page The MAPI message system The VIM message system Page dbremote.exe Message Agent batch and continuous modes Connections used by the Message Agent Replication system recovery procedures Ensuring consistent Message Agent settings Troubleshootingerrors at remote sites nnn Tuning Message Agent performance Tuningthroughput by controlling Message Agent threading Tuningthroughput by caching messages Tuningincoming message polling Page userX.n Tuningthe message sending process Page Page Encoding and compressing messages The encoding scheme dbencod.dll ssencod.dll h Creating custom encoding schemes The message tracking system Status information in the remoteuser table Trackingmessages by transaction log offsets Page Handling of lost or corrupt messages Page Page Starting the Message Agent Running the Message Agent as a service Page Page Error reporting and handling Default error handling Ignoring errors Implementing error handling procedures Chapter 11. Administering SQL Remote for AdaptiveSer ver Anywhere Thestored procedure calls another stored procedure to manage the sending ofEmail: 247 Anaudit table Anaudit table could be dened as follows: Thecolumns have the following meaning: Page Transaction log and backup management e:\archive Setting the transaction log directory Backup utility options consol.db c:\archive c:\live c:\archive\consol.log xx c:\live consol.db c:\archive xx ZZ AA yymmdd01.log Managing old transaction logs Recovery from database media failure for consolidated databases X Page Page c:\dbdir\cons.log c:\recover\cons.log d:\mirdir\cons.mlg c:\dbdir\cons.log Backup procedures at remote databases Upgrading consolidated databases Unloading and reloading a database participating in replication dbtran dblog Using passthrough mode before after Uses and limitations of passthrough mode Operations not replicated in passthrough mode CHAPTER 12 Administering SQL Remote for Adaptive Server Enterprise How the Message Agent for Adaptive Server Enterprise works Scanning the transaction log The stable queue Message Agent operation phases Message Agent Page Page squeue.sql @data Running multiple Message Agents Page Error reporting and handling Default error handling Implementing error handling procedures Adaptive Server Enterprise transaction log and backup management Protecting against media failure on the transaction log System Administration Guide Stable queue recovery issues Transactionlog management Page Making schema changes Using passthrough mode Schema modications Page When you need to use the SQL Remote Open Server Architecture for Replication Server/SQL Remote installations How the pieces t together Page Message Agent Message system ssqueue ssqueue.exe Setting up SQL Remote Open Server Page Page Conguring Replication Server Set the dsi_xact_group_size parameter Set the dsi_num_threads parameter Create replication denitions for SQL Remote data Suspend and restart the connection Other issues Page Page Page CHAPTER 14 Utilities and Options Reference The Message Agent trackingsystem to ensure message delivery. 292 Chapter 14. Utilities and Options Reference le size n time sybase\locales\locales.dat charset_name sort_order language_name The maximum message length must be the same at all sites in an installa- tion. yymmddxx.dbr yymmddxx.ss yymmdd Page Page size The Database Extraction utility Extracting a remote database in Sybase Central The extraction utility Page Page Extraction utility options asademo.db asacopy.db newdemo.db asademo c:\unload sybase\locales\locales.dat charset_name sort_order language_name Page Page Page The SQL Remote Open Server sql.ini interfaces yymmddxx.dbr yymmddxx.ss yymmdd Chapter 14. Utilities and Options Reference SQL Remote options integer timestamp-string time-string date-string Page Page Page Page SQL Remote event-hook procedures sp_hook_dbremote_begin and sp_hook_ssrmt_begin sp_hook_dbremote_end and sp_hook_ssrmt_end sp_hook_dbremote_shutdown and sp_hook_ssrmt_shutdown sp_hook_dbremote_receive_begin and sp_hook_ssrmt_receive_begin sp_hook_dbremote_receive_end and sp_hook_ssrmt_receive_end Page Page Page Page SQL Remote system tables 326 Thesetables are described in more detail in the following sections. SYSARTICLE table Function Eachrow describes an article in a SQL Remote publication. SYSARTICLECOL table itis in, and the publication it is part of. 327 SYSPUBLICATIONtable SYSREMOTEOPTION table 328 SYSREMOTEOPTIONTYPE table SYSREMOTETYPE table publisheraddress. 329 SYSREMOTEUSER table togetherwith the status of SQL Remote messages sent to and from that user. Page SYSSUBSCRIPTION table REMOTEpermissions) to one publication. 331 Page SQL Remote system views SYSARTICLES view SYSARTICLECOLS view SYSPUBLICATIONSview SYSREMOTEOPTIONS view theSYSREMOTEOPTION and SYSREMOTEOPTIONTYPE system tables,in more readable form. 334 SYSREMOTEUSERS view SYSSUBSCRIPTIONS view 335 Page Page SQL Remote system tables These tables are for use only by SQL Remote. Do not alter these tables or their contents directly. #remote table sr_article table sr_articlecol table itis in, and the publication it is part of. 339 sr_marker table databasesin the same session. sr_object table sr_option table sr_passthrough table sr_publication table sr_publisher table sr_remoteoption table sr_remoteoptiontype table sr_remotetable table 342 sr_remotetype table publisheraddress. sr_remoteuser table 343 togetherwith the status of SQL Remote messages sent to and from that user. Page sr_subscription table REMOTEpermissions) to one publication. 345 SQL Remote system views sr_articles view sr_articlecols view sr_publications view sr_remoteoptions view sr_remotetables view sr_remotetypes view sr_remoteusers view 348 sr_subscriptions view 349 Stable Queue tables sr_queue_state table sr_transaction table 351 sr_conrmed_transaction table sr_queue_coordinate table 352 sendingthread to access the stable queue and related tables. CHAPTER 17 Command Reference for Adaptive Server Anywhere 353 Page ALTER REMOTE MESSAGE TYPE statement CREATE PUBLICATION statement CREATE REMOTE MESSAGE TYPE statement CREATE SUBSCRIPTION statement CREATE TRIGGER statement column-list Page DROP PUBLICATION statement DROP REMOTE MESSAGE TYPE statement DROP SUBSCRIPTION statement GRANT CONSOLIDATE statement GRANT PUBLISH statement GRANT REMOTE statement GRANT REMOTE DBA statement PASSTHROUGH statement REMOTE RESET statement REVOKE CONSOLIDATE statement REVOKE PUBLISH statement REVOKE REMOTE statement REVOKE REMOTE DBA statement SET REMOTE OPTION statement START SUBSCRIPTION statement STOP SUBSCRIPTION statement SYNCHRONIZE SUBSCRIPTION statement UPDATE statement CHAPTER 18 Command Reference for Adaptive Server Enterprise 379 executingSQL Remote commands. Page sp_add_article procedure Page sp_add_article_col procedure sp_add_remote_table procedure Page sp_create_publication procedure sp_drop_publication procedure sp_drop_remote_type procedure sp_drop_sql_remote procedure sp_grant_consolidate procedure 390 frequency address type_name send_time Page Chapter 18. Command Referencefor Adaptive Server Enterprise sp_grant_remote procedure 393 frequency address type_name send_time Page sp_link_option procedure ftp.mycompany.com sp_modify_article procedure Page sp_modify_remote_table procedure Page sp_passthrough procedure sp_passthrough_piece procedure Page sp_passthrough_stop procedure sp_passthrough_subscription procedure subscribe_by publication_name sp_passthrough_user procedure sp_populate_sql_anywhere procedure sp_publisher procedure user_name sp_queue_clean procedure sp_queue_conrmed_delete_old procedure sp_queue_conrmed_transaction procedure sp_queue_delete_old procedure sp_queue_drop procedure sp_queue_dump_database procedure sp_queue_dump_transaction procedure sp_queue_get_state procedure sp_queue_log_transfer_reset procedure sp_queue_read procedure sp_queue_reset procedure sp_queue_set_conrm procedure sp_queue_set_progress procedure sp_queue_transaction procedure sp_remote procedure remote_user sp_remote_option procedure Page sp_remote_type procedure publisher: sp_remove_article procedure sp_remove_article_col procedure sp_remove_remote_table procedure sp_revoke_consolidate procedure sp_revoke_remote procedure sp_subscription procedure operation Page Page Page Page Types of difference Differences in functionality Differences in approach Adaptive Server Enterprise procedures and Adaptive Server Anywhere statements Appendix A. SQL Remote forAdaptive Ser verEnter prise and Adaptive Server Anywhere: Differences 441 Limitations for Enterprise to Enterprise replication Page Page Page Supported message systems Supported operating systems \My Documents\Synchronized Files SQL AnywhereStudio Read Me First for UNIX ce-machine-name Page Index Symbols A B C D E F G H I L M N O P Page Q R Page S Page Page Page Page Page T U V W