Sybase Sql Remote Dc38133 01 0902 Users Manual

2015-02-02

• SQL Remote User's Guide
• Contents
  • About This Manual
    • SQL Anywhere Studio documentation
    • Documentation conventions
    • The Adaptive Server Anywhere sample database
    • Finding out more and providing feedback
• Introduction to SQL Remote
  • Welcome to SQL Remote
    • About SQL Remote
    • About this manual
      • Product installation
  • SQL Remote Concepts
    • SQL Remote components
      • The data server
      • Client applications
      • The Message Agent
      • Message system client
    • Publications and subscriptions
    • SQL Remote features
    • Some sample installations
      • Server-to-laptop replication for mobile workforces
      • Server-to-server replication among offices
  • Setting Up SQL Remote
    • Setup overview
    • Preparing your Adaptive Server Enterprise server
      • Ensuring TEMPDB is large enough
      • Installing the SQL Remote system objects
        • The SQL Remote system objects
      • Command-line installation of the stable queue
    • Upgrading SQL Remote for Adaptive Server Enterprise
    • Uninstalling SQL Remote
  • Tutorials for Adaptive Server Anywhere Users
    • Introduction
      • Goals
      • The database
      • Replication goals
      • Sybase Central or command-line utilities
    • Tutorial: Adaptive Server Anywhere replication using Sybase Central
      • Preparing for the Sybase Central replication tutorial
      • Setting up a consolidated database
        • Add a SQL Remote message type
        • Add the publisher and remote user to the database
        • Add publications and subscriptions
      • Set up the remote database in Sybase Central
    • Tutorial: Adaptive Server Anywhere replication using Interactive SQL and dbxtract
      • Preparing for the replication tutorial
      • Set up the consolidated database
        • Create a SQL Remote message type
        • Grant PUBLISH and REMOTE at the consolidated database
        • Create publications and subscriptions
      • Set up the remote database
        • Extract the remote database information
        • Load the remote database information
    • 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
    • A sample publication
  • A Tutorial for Adaptive Server Enterprise Users
    • Introduction
      • Goals
      • The database
      • Replication goals
    • Tutorial: Adaptive Server Enterprise replication
      • First steps
      • Setting up the consolidated database
        • Create the message links and addresses
        • Create the necessary users and permissions
        • Create the publication and subscription
        • Extract the remote database
    • 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
• Replication Design for SQL Remote
  • Principles of SQL Remote Design
    • Design overview
      • Ensuring compatible databases
      • Using compatible sort orders and character sets
    • How statements are replicated
      • Replication of inserts and deletes
      • Replication of updates
      • Replication of procedures
      • Replication of triggers
      • Replication of data definition statements
    • How data types are replicated
      • Replication of blobs
      • Replication of dates and times
    • Who gets what?
    • Replication errors and conflicts
      • Replication errors
      • Replication conflicts
      • Tracking SQL errors
  • SQL Remote Design for Adaptive Server Anywhere
    • Design overview
    • 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
      • 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
    • Partitioning tables that do not contain the subscription expression
      • The Contact example
      • Partitioning the Customer table in the Contact example
      • Partitioning the Contact table in the Contact example
      • Territory realignment in the Contact example
    • Sharing rows among several subscriptions
      • The Policy example
        • The publication
      • Territory realignment with a many-to-many relationship
      • Using the Subscribe_by_remote option with many-to-many relationships
    • Managing conflicts
      • How SQL Remote handles conflicts
      • Implementing conflict resolution
      • Using conflict resolution triggers
      • Conflict resolution examples
        • Resolving date conflicts
        • Resolving inventory conflicts
        • Reporting conflicts
      • Designing to avoid referential integrity errors
      • Designing triggers to avoid errors
    • Ensuring unique primary keys
      • Using global autoincrement default column values
        • Declaring default global autoincrement
        • Setting the Global_database_id value
        • Setting unique database identification numbers when extracting databases
        • How default values are chosen
      • Using primary key pools
      • The primary key pool table
      • Replicating the primary key pool
      • Filling and replenishing the key pool
      • Adding new customers
      • Primary key pool summary
    • Creating subscriptions
  • SQL Remote Design for Adaptive Server Enterprise
    • Design overview
    • 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
        • Creating an article using a WHERE clause
        • Creating an article using a subscription column
      • Notes on articles
    • Publication design for Adaptive Server Enterprise
      • Design issues overview
      • Conditions for valid articles
    • Partitioning tables that do not contain the subscription column
      • The Contact example
      • Territory realignment in the Contact example
      • Partitioning the Customer table in the Contact example
      • Adding a subscription-list column to the Contact table
      • Maintaining the subscription-list column
      • Tuning extraction performance
    • Sharing rows among several subscriptions
      • The Policy example
        • Solving the problem
        • The publication
      • Maintaining the subscription-list column
      • Tuning extraction performance for shared rows
      • Using the Subscribe_by_remote option with many-to-many relationships
    • Managing conflicts
      • How SQL Remote handles conflicts
      • Implementing conflict resolution
      • A first conflict resolution example
      • A second conflict resolution example
      • Designing to avoid referential integrity errors
    • Ensuring unique primary keys
      • The primary key pool
      • Replicating the primary key pool
      • Filling and replenishing the key pool
      • Adding new customers
      • Testing the key pool
      • Primary key pool summary
    • Creating subscriptions
• SQL Remote Administration
  • Deploying and Synchronizing Databases
    • Deployment overview
    • Test before deployment
      • Changes to avoid on a running system
    • Synchronizing databases
      • Mixed operating systems and database extraction
      • Notes on synchronization and extraction
    • Using the extraction utility
      • Creating a database from the reload files
      • Before extracting a database
      • Using the extraction utility from Sybase Central
      • Designing an efficient extraction procedure
      • Extracting groups
      • Limits to using the extraction utility
      • Using the extraction utility for Adaptive Server Enterprise
        • Adaptive Server Enterprise features unsupported in Adaptive Server Anywhere
        • Customizing the system tables
    • Synchronizing data over a message system
  • SQL Remote Administration
    • Management overview
    • Managing SQL Remote permissions
      • Granting and revoking PUBLISH permissions
      • Granting and revoking REMOTE and CONSOLIDATE permissions
        • Granting REMOTE permissions
        • Selecting a send frequency
        • Granting CONSOLIDATE permissions
        • Revoking REMOTE and CONSOLIDATE permissions
      • Assigning permissions in multi-tier installations
    • Using message types
      • Working with message types
        • Using Sybase Central to work with message types
        • Using commands to work with message types
      • Setting message type control parameters
      • The file message system
      • The ftp message system
        • Troubleshooting ftp problems
      • The SMTP message system
        • Sharing SMTP/POP addresses
      • The MAPI message system
      • The VIM message system
    • Running the Message Agent
      • Message Agent batch and continuous modes
      • Connections used by the Message Agent
      • Replication system recovery procedures
      • Ensuring consistent Message Agent settings
      • The Message Agent and replication security
      • Troubleshooting errors at remote sites
    • Tuning Message Agent performance
      • Tuning throughput by controlling Message Agent threading
      • Tuning throughput by caching messages
      • Tuning incoming message polling
        • Polling interval
        • Requesting resends
        • Example
      • Tuning the message sending process
        • Polling interval
        • Resending messages
    • Encoding and compressing messages
      • The encoding scheme
      • Creating custom encoding schemes
    • The message tracking system
      • Status information in the remoteuser table
      • Tracking messages by transaction log offsets
      • Handling of lost or corrupt messages
  • Administering SQL Remote for Adaptive Server Anywhere
    • Running the Message Agent
      • Starting the Message Agent
      • Running the Message Agent as a service
      • The Message Agent and replication security
    • Error reporting and handling
      • Default error handling
      • Ignoring errors
      • Implementing error handling procedures
        • Example: e-mailing notification of errors
    • Transaction log and backup management
      • Setting the transaction log directory
      • Backup utility options
        • Using the live directory as the transaction log directory
        • Using the backup directory as the transaction log directory
      • Managing old transaction logs
      • Recovery from database media failure for consolidated databases
        • Recovery with a single transaction log
        • Recovery with multiple transaction logs
      • Backup procedures at remote databases
      • Upgrading consolidated databases
      • Unloading and reloading a database participating in replication
    • Using passthrough mode
      • Uses and limitations of passthrough mode
      • Operations not replicated in passthrough mode
  • 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
    • Running the Message Agent
      • The Message Agent and replication security
      • Running multiple Message Agents
    • 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
      • Stable queue recovery issues
      • Transaction log management
    • Making schema changes
    • Using passthrough mode
      • Schema modifications
  • Using SQL Remote with Replication Server
    • When you need to use the SQL Remote Open Server
    • Architecture for Replication Server/SQL Remote installations
      • How the pieces fit together
    • Setting up SQL Remote Open Server
    • Configuring Replication Server
      • Set the dsi_xact_group_size parameter
      • Set the dsi_num_threads parameter
      • Create replication definitions for SQL Remote data
      • Suspend and restart the connection
    • Other issues
• Reference
  • Utilities and Options Reference
    • The Message Agent
    • The Database Extraction utility
      • Extracting a remote database in Sybase Central
      • The extraction utility
      • Extraction utility options
    • The SQL Remote Open Server
    • SQL Remote options
    • 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
      • sp_hook_dbremote_send_begin and sp_hook_ssrmt_send_begin
      • sp_hook_dbremote_send_end and sp_hook_ssrmt_send_end
      • sp_hook_dbremote_message_sent and sp_hook_ssrmt_message_sent
      • sp_hook_dbremote_message_missing and sp_hook_ssrmt_message_missing
      • sp_hook_dbremote_message_apply_begin and sp_hook_ssrmt_message_apply_begin
      • sp_hook_dbremote_message_apply_end and sp_hook_ssrmt_message _apply_end
  • System Objects for Adaptive Server Anywhere
    • SQL Remote system tables
      • SYSARTICLE table
      • SYSARTICLECOL table
      • SYSPUBLICATION table
      • SYSREMOTEOPTION table
      • SYSREMOTEOPTIONTYPE table
      • SYSREMOTETYPE table
      • SYSREMOTEUSER table
      • SYSSUBSCRIPTION table
    • SQL Remote system views
      • SYSARTICLES view
      • SYSARTICLECOLS view
      • SYSPUBLICATIONS view
      • SYSREMOTEOPTIONS view
      • SYSREMOTEUSERS view
      • SYSSUBSCRIPTIONS view
  • System Objects for Adaptive Server Enterprise
    • SQL Remote system tables
      • ##remote table
      • sr_article table
      • sr_articlecol table
      • sr_marker table
      • sr_object table
      • sr_option table
      • sr_passthrough table
      • sr_publication table
      • sr_publisher table
      • sr_remoteoption table
      • sr_remoteoptiontype table
      • sr_remotetable table
      • sr_remotetype table
      • sr_remoteuser table
      • sr_subscription table
    • 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
      • sr_subscriptions view
    • Stable Queue tables
      • sr_queue_state table
      • sr_transaction table
      • sr_confirmed_transaction table
      • sr_queue_coordinate table
  • Command Reference for Adaptive Server Anywhere
    • ALTER REMOTE MESSAGE TYPE statement
    • CREATE PUBLICATION statement
    • CREATE REMOTE MESSAGE TYPE statement
    • CREATE SUBSCRIPTION statement
    • CREATE TRIGGER statement
    • 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
  • Command Reference for Adaptive Server Enterprise
    • sp_add_article procedure
    • sp_add_article_col procedure
    • sp_add_remote_table procedure
    • sp_create_publication procedure
    • sp_drop_publication procedure
    • sp_drop_remote_type procedure
    • sp_drop_sql_remote procedure
    • sp_grant_consolidate procedure
    • sp_grant_remote procedure
    • sp_link_option procedure
    • sp_modify_article procedure
    • sp_modify_remote_table procedure
    • sp_passthrough procedure
    • sp_passthrough_piece procedure
    • sp_passthrough_stop procedure
    • sp_passthrough_subscription procedure
    • sp_passthrough_user procedure
    • sp_populate_sql_anywhere procedure
    • sp_publisher procedure
    • sp_queue_clean procedure
    • sp_queue_confirmed_delete_old procedure
    • sp_queue_confirmed_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_confirm procedure
    • sp_queue_set_progress procedure
    • sp_queue_transaction procedure
    • sp_remote procedure
    • sp_remote_option procedure
    • sp_remote_type procedure
    • 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
    • sp_subscription_reset procedure
• Appendices
  • SQL Remote for Adaptive Server Enterprise and Adaptive Server Anywhere: Differences
    • Types of difference
    • Differences in functionality
    • Differences in approach
      • Adaptive Server Enterprise procedures and Adaptive Server Anywhere statements
    • Limitations for Enterprise to Enterprise replication
  • Supported Platforms and Message Links
    • Supported message systems
    • Supported operating systems
• Index