Sybase DC38133-01-0902-01 Ensuring unique primary keys

Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise

Ensuring unique primary keys

Usersat physically distinct sites can each INSERT new rows to a table, so

thereis an obvious problem ensuring that primary key values are kept

unique.

Iftwo users INSERT a row using the same primary key values, the second

INSERTto reach a given database in the replication system will fail. As

SQLRemote is a replication system for occasionally-connected users, there

canbe no locking mechanism across all databases in the installation. Itis

necessaryto design your SQL Remote installation so that primary key errors

donot occur.

Forprimary key errors to be designed out of SQL Remote installations; the

primarykeys of tables that may be modiﬁed at more than one site must be

guaranteedunique. Thereare several ways of achieving this goal. This

chapterdescribes a general, economical and reliable method that uses a pool

ofprimary key values for each site in the installation.

Overviewof primar y key

pools Theprimary key pool is a table that holds a set of primary key values for

eachdatabase in the SQL Remote installation. Eachremote user receives

theirown set of primary key values. When a remote user inserts a new row

intoa table, they use a stored procedure to select a valid primary key from

thepool. Thepool is maintained by periodically running a procedure at the

consolidateddatabase that replenishes the supply.

Themethod is described using a simple exampledatabase consisting of sales

representativesand their customers. Thetables are much simpler than you

woulduse in a real database; this allows us to focus just on those issues

importantfor replication.

The primary key pool

Thepool of primary keys is held in a separate table. Thefollowing CREATE

TABLEstatement creates a primary key pool table:

CREATE TABLE KeyPool (

table_name VARCHAR(40) NOT NULL,

value INTEGER NOT NULL,

location VARCHAR(6) NOT NULL,

PRIMARY KEY (table_name, value),

)

go

Thecolumns of this table have the following meanings:

175

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