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 this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior written permission of iAnywhere Solutions, Inc. iAnywhere Solutions, Inc. is a subsidiary of Sybase, Inc.
Contents About This Manual SQL Anywhere Studio documentation . . . . . . Documentation conventions . . . . . . . . . . . . The Adaptive Server Anywhere sample database Finding out more and providing feedback . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix . x . xiii . xv . xvi I Introduction to SQL Remote 1 1 Welcome to SQL Remote About SQL Remote . . . . . . . . . . . . . . . . . . . . . . . About this manual . . . . . . . . . . . . . . . . . . . . . . . .
II Replication Design for SQL Remote 6 Principles of SQL Remote Design Design overview . . . . . . . . . . . How statements are replicated . . . How data types are replicated . . . . Who gets what? . . . . . . . . . . . Replication errors and conflicts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 . . . . . . . . . . . . . . . . . . . . . . . . . 7 SQL Remote Design for Adaptive Server Anywhere Design overview . . . . . . . . . . . . . . . . . . . . . . Publishing data . .
Running the Message Agent . . . . . Tuning Message Agent performance . Encoding and compressing messages The message tracking system . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 228 235 237 11 Administering SQL Remote for Adaptive Server Anywhere241 Running the Message Agent . . . . . . . . . . . . . . . . . . 242 Error reporting and handling . . . . . . . . . . . . . . . . . . .
Stable Queue tables . . . . . . . . . . . . . . . . . . . . . . . 350 17 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 . . . . . . . .
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 . . . . . . . .
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.
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.
♦ 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.
♦ 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 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.
Graphic icons The following icons are used in this documentation. ♦ 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.
The Adaptive Server Anywhere sample database Many of the examples throughout the documentation use the Adaptive Server Anywhere sample database. The sample database is held in a file named asademo.db, and is located in 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).
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.
In addition, you can provide feedback on the documentation and the software through the newsgroups listed above.
xviii
PART I I NTRODUCTION TO SQL R EMOTE 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 Contents This chapter introduces SQL Remote and the documentation.
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.
Chapter 1. Welcome to SQL Remote 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.
CHAPTER 2 SQL Remote Concepts About this chapter Contents This chapter introduces the concepts, design goals, and features of SQL Remote.
SQL Remote components 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.
Chapter 2. SQL Remote Concepts The data server 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 Client applications work with the data in the database.
Message System Message Agent Consolidated database Message Agent Remote database 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.
Chapter 2. SQL Remote Concepts Publications and subscriptions 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. 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.
replication. 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.
Chapter 2. SQL Remote Concepts SQL Remote features The following features are key to SQL Remote’s design. SQL Remote is designed to support replication with many subscribers to a publication. Support for many subscribers This feature is of particular importance for mobile workforce applications, which may require replication to the laptop computers of hundreds or thousands of sales representatives from a single office database.
extra hardware to run SQL Remote. SQL Remote is provided on a number of operating systems and message links. Multi-platform support ☞ For a list of supported environments, see “Supported Platforms and Message Links” on page 445.
Chapter 2. SQL Remote Concepts Some sample installations While SQL Remote can provide replication services in many different environments, its features are designed with the following characteristics in mind: ♦ SQL Remote should be a solution even when no administration load can be assigned to the remote databases, as in mobile workforce applications. ♦ Data communication among the sites may be occasional and indirect: it need not be permanent and direct.
The office server may be running a server to manage the company database. The Message Agent at the company database runs as a client application for that server. At the laptop computers each sales representative has an Adaptive Server Anywhere personal server to manage their own data. While away from the office, a sales representative can make a single phone call from their laptop to carry out the following functions: ♦ Collect new e-mail. ♦ Send any e-mail messages they have written.
Chapter 2. SQL Remote Concepts Central office database Central office network server More... Office database Office database Sales office server Sales office server More... Desktop computer Desktop computer SQL Remote is easy to configure to allow each office to receive their own set of data. Tables that are of office interest only (staff records, perhaps, if the office is a franchise) may be kept private in the same database as the replicated data.
CHAPTER 3 Setting Up SQL Remote About this chapter This chapter describes how to add SQL Remote capabilities to your Adaptive Server Enterprise server. Adaptive Server Enterprise users only This chapter is required only for users of SQL Remote for Adaptive Server Enterprise. SQL Remote capability is automatically installed into Adaptive Server Anywhere databases. This chapter assumes you have already installed the SQL Remote software onto your machine.
Setup overview We call the collection of databases exchanging information using SQL Remote an installation. From a physical point of view, a SQL Remote installation may consist of hundreds or even thousands of databases sharing information; but as SQL Remote keeps the information in each physical database loosely consistent at a transactional level with that in other physical databases, you can also think of the whole installation as a single dispersed database.
Chapter 3. Setting Up SQL Remote Preparing your Adaptive Server Enterprise server Before you start This section assumes the following: ♦ You have installed an Adaptive Server Enterprise server that is to contain the SQL Remote database. ♦ You have installed the SQL Remote software on your computer. To install the SQL Remote software, run the setup program from the CD-ROM. ♦ You have created a database in the Adaptive Server Enterprise server that will take part in your SQL Remote installation.
where database_name is the name of the database to take part in SQL Remote replication. These two lines set the current database to database_name, so that the SQL Remote tables are created in the database_name database. The SQL Remote tables are owned by the database owner. 3. Run the script against your Adaptive Server Enterprise server.
Chapter 3. Setting Up SQL Remote Command-line installation of the stable queue The stable queue is a pair of database tables that hold transactions until they are no longer needed by the replication system. Every Adaptive Server Enterprise database participating in a SQL Remote installation needs a stable queue. ☞ For detailed information about the stable queue, see “The stable queue” on page 265.
☞ The login_id must correspond to the name used by the Message Agent. For more information, see “The Message Agent and replication security” on page 269. 4. Inspect the log file to confirm that the tables and procedures were created without error.
Chapter 3. Setting Up SQL Remote Upgrading SQL Remote for Adaptive Server Enterprise This section describes the procedure for upgrading SQL Remote for Adaptive Server Enterprise. As a SQL Remote installation may consist of a large number of databases, it is generally not practical to upgrade software on all machines at the same time. SQL Remote is designed so that upgrades can be carried out incrementally.
Uninstalling SQL Remote This section describes how to uninstall the SQL Remote objects from a database, and uninstall the stable queue from a database. ❖ To uninstall the SQL Remote objects from a database 1. Connect to the database containing the SQL Remote objects, as a user with dbo permissions. 2. Run the sp_drop_sql_remote stored procedure to remove all SQL Remote objects apart from the procedure itself. The sp_drop_sql_remote procedure is installed along with the other SQL Remote objects.
CHAPTER 4 Tutorials for Adaptive Server Anywhere Users About this chapter Contents This chapter guides you through setting up a simple replication system using Adaptive Server Anywhere.
Introduction These tutorials describe how to set up a simple SQL Remote replication system using Adaptive Server Anywhere. Goals In the tutorials, you act as the system administrator of a consolidated Adaptive Server Anywhere database, and set up a simple replication system. The replication system consists of a simple sales database, with two tables. The consolidated database holds all of the database, while the remote database has all of one table, but only some of the rows in the other table.
Chapter 4.
Table Description SalesRep One row for each sales representative that works for the company. The SalesRep table has the following columns: ♦ rep_key An identifier for each sales representative. This is the primary key. ♦ name The name of each sales representative. The SQL statement creating this table is as follows: CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) ) Customer One row for each customer that does business with the company.
Chapter 4. Tutorials for Adaptive Server Anywhere Users Sybase Central or command-line utilities Use Sybase Central or the command line Where next? The tutorial material is presented twice. One tutorial describes how to set up the installation using the Sybase Central management utility. The second tutorial describes how to set up the installation using command-line utilities: this requires typing commands individually.
Tutorial: Adaptive Server Anywhere replication using Sybase Central The following sections are a tutorial describing how to set up a simple SQL Remote replication system in Adaptive Server Anywhere using Sybase Central. You do not need to enter SQL statements if you are using Sybase Central to administer SQL Remote.
Chapter 4. Tutorials for Adaptive Server Anywhere Users mkdir c:\tutorial\HQ mkdir c:\tutorial\field 3. Create the HQ database: ♦ Start Sybase Central. ♦ In the left pane, select the Adaptive Server Anywhere plug-in. ♦ In the right pane, click the Utilities tab. ♦ Double-click Create Database in the right pane. The Create Database wizard appears. ♦ Create a database with filename c:\tutorial\HQ.db. Use the default settings for this database.
Again, you do not need to use the property sheets. 7. Save the table. 8. In the Tables folder in the left pane, select the Customer table, then click the Foreign Keys tab in the right pane. 9. From the File menu, choose New ➤ Foreign Key. Using the wizard, add a foreign key to the Rep_key column of the SalesRep table. You can use the default settings for this foreign key. You are now ready for the rest of the tutorial.
Chapter 4. Tutorials for Adaptive Server Anywhere Users ❖ To add an address to a message type 1. From Sybase Central, connect to the HQ database. 2. Open the SQL Remote Users folder for the HQ database. 3. In the right pane, click the Message Types tab. 4. In the right pane, right-click the FILE message type and choose Properties from the popup menu. 5. Enter a publisher address to provide a return address for remote users.
For any database in a SQL Remote replication setup, there are three permissions that may be granted to identify databases on the hierarchy: ♦ PUBLISH permission Identifies the current database in all outgoing messages ♦ REMOTE permission Identifies each database receiving messages from the current database that is below it on the hierarchy ♦ CONSOLIDATE permission Identifies a database receiving messages from the current database that is directly above it on the hierarchy.
Chapter 4. Tutorials for Adaptive Server Anywhere Users ❖ To add a remote user 1. Select the SQL Remote Users folder. 2. From the File menu, choose New ➤ SQL Remote User. The Create a New Remote User wizard appears. 3. Create a remote user with user ID field_user with the following options: ♦ Enter the password field_pwd. ♦ Ensure that Remote DBA authority is selected, so that the user can run the Message Agent. ♦ Select the message type FILE, and enter the address field.
5. Select Customer from the list of Available Tables. Click Add. 6. On the SUBSCRIBE BY Restrictions tab, select the Customer table and enter the expression rep_key. 7. Click Finish to create the publication. Add a subscription Each user ID that is to receive changes to a publication must have a subscription to that publication. Subscriptions can only be created for a valid remote user. You need to add a subscription to the SalesRepData publication for the remote database user field_user.
Chapter 4. Tutorials for Adaptive Server Anywhere Users You need to extract a database from the consolidated database for remote user field_user. ❖ To extract a database 1. Connect to the HQ database. 2. Right-click the database and choose Extract Database from the popup menu. 3. Choose to extract the HQ database with the following options: ♦ Choose to extract at isolation level 3. ♦ Choose to Start Subscriptions Automatically, for user field_user. ♦ Choose Extract and Reload into a New Database.
Tutorial: Adaptive Server Anywhere replication using Interactive SQL and dbxtract The following sections are a tutorial describing how to set up a simple SQL Remote replication system for users who prefer to use command-line tools or who want to know what Sybase Central is doing behind the scenes. This tutorial describes the SQL statements for managing SQL Remote, which can be run from Interactive SQL.
Chapter 4. Tutorials for Adaptive Server Anywhere Users mkdir c:\tutorial\hq mkdir c:\tutorial\field The next step is to add a pair of tables to the consolidated database. ❖ To add the tables to the consolidated database 1. Connect to hq.db from Interactive SQL with a user ID of DBA and a password of SQL. 2. Execute the following CREATE TABLE statement to create the SalesRep table: CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY ( rep_key ) ); 3.
❖ To create the message type 1. In Interactive SQL, create the file message type using the following statement: CREATE REMOTE MESSAGE TYPE file ADDRESS ’hq’ The address (hq) for a file link is a directory in which files containing the message are placed. It is taken relative to the SQLRemote environment variable or registry entry. As you have not set this value, the address is taken relative to the directory from which the Message Agent is run.
Chapter 4. Tutorials for Adaptive Server Anywhere Users You can check the publishing user ID of a database at any time using the CURRENT PUBLISHER special constant: SELECT CURRENT PUBLISHER GRANT REMOTE for each database to which you send messages Each remote database is identified using the GRANT REMOTE statement. Whether the remote database is a personal server or a network server with many users, it needs a single user ID to represent it to the consolidated database.
❖ To create the publication 1. Connect to the database from Interactive SQL, and execute the following statement: CREATE PUBLICATION SalesRepData ( TABLE SalesRep, TABLE Customer SUBSCRIBE BY rep_key ) Set up a subscription Each user ID that is to receive changes to the publication must have a subscription. The subscription can only be created for a user who has REMOTE permissions. The GRANT REMOTE statement contains the address to use when sending the messages. ❖ To create the subscription 1.
Chapter 4. Tutorials for Adaptive Server Anywhere Users The dbxtract utility enables you to carry out all the steps needed to create a remote database complete with subscriptions and required user IDs. Extract the remote database information Leave the hq database running, and change to the tutorial directory.
♦ Creates the table in the database. If the table had contained any data before extraction, the command file would fill the replicated table with a copy of the data. ♦ Creates a publication to identify the data being replicated. ♦ Creates the subscription for the consolidated database, and starts the subscription.
Chapter 4. Tutorials for Adaptive Server Anywhere Users Start replicating data You now have a replication system in place. In this section, data is replicated from the consolidated database to the remote database, and from the remote to the consolidated database. Enter data at the consolidated database First, enter some data into the consolidated database. ❖ To enter data at the consolidated database 1.
❖ To send the data to the remote database 1. From a command prompt, change to your tutorial directory. For example, > c: > cd c:\tutorial 2. Enter the following statement at the command line to run the Message Agent against the consolidated database: dbremote -c "dbn=hq;uid=dba;pwd=sql" This command line assumes that the hq database is currently running on the default server. If the database is not running, you must supply a dbf parameter with the database file name instead of the dbn parameter.
Chapter 4. Tutorials for Adaptive Server Anywhere Users The Message Agent window displays status information while running. This information can be output to a log file for record keeping in a real setup. You will see that the Message Agent first receives a message from hq, and then sends a message.
COMMIT; 4. With the field.db database running, run the dbremote utility from a command line to send the message to the consolidated database. dbremote -c "dbn=field;uid=dba;pwd=sql" 5. With the hq.db database running, run the dbremote utility from a command line to receive the message at the consolidated database: dbremote -c "dbn=hq;uid=dba;pwd=sql" 6. Connect to the consolidated database.
Chapter 4. Tutorials for Adaptive Server Anywhere Users A sample publication The command file salespub.sql contains a set of statements that creates a publication on the sample database. This publication illustrates several of the points of the tutorials, in more detail. ❖ To add the publication to the sample database 1. Connect to the sample database from Interactive SQL. 2. In the SQL Statements pane, execute the following statement: READ path\scripts\salespub.
CHAPTER 5 A Tutorial for Adaptive Server Enterprise Users About this chapter Contents This chapter presents a tutorial in which you set up a simple SQL Remote replication system between an Adaptive Server Enterprise database and an Adaptive Server Anywhere database, from scratch.
Introduction This chapter presents a tutorial to lead you through setting up a SQL Remote installation. The installation replicates data between an Adaptive Server Enterprise database (the consolidated database) and an Adaptive Server Anywhere database (the remote database). Goals In the tutorial you act as the system administrator of a consolidated Adaptive Server Enterprise database, and set up a simple replication system. The replication system consists of a simple sales database, with two tables.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users table to the SalesRep table. The relationship between the Customer table and the SalesRep table is many-to-one. The tables in the database The tables are described in more detail as follows: Table Description SalesRep One row for each sales representative that works for the company. The SalesRep table has the following columns: ♦ rep_key An identifier for each sales representative. This is the primary key.
♦ The complete SalesRep table. ♦ Those customers assigned to them. The tutorial describes how to meet this goal using SQL Remote.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users Tutorial: Adaptive Server Enterprise replication The following sections are a tutorial describing how to set up a simple SQL Remote replication system. This tutorial describes the stored procedures used to configure and manage SQL Remote. It also describes how to run the ssxtract utility to extract remote databases from a consolidated database and the Message Agents to send information between the databases in the replication system.
use master go 3. Create a database named hq. In this example, we use a 5 MB database with a 5 Mb log, on two different devices: create database hq on database_device = 5 log on log_device = 5 go ☞ For more information on how to create databases and assign space to them, see your Adaptive Server Enterprise documentation. Install SQL Remote You need to install SQL Remote into the hq database. ❖ To install SQL Remote into the hq database 1.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users You should create a directory for each of the two users of the replication system under your parent directory for this tutorial: mkdir c:\tutorial\hq mkdir c:\tutorial\field The next step is to add a pair of tables to the consolidated database. ❖ To add tables to the consolidated database 1. Connect to the hq database from isql, as a system administrator. 2. Use the hq database: use hq go 3.
2. Grant PUBLISH permissions to a user ID to identify the source of outgoing messages. 3. Grant REMOTE permissions to all user IDs that are to receive messages. 4. Create a publication describing the data to be replicated. 5. Create subscriptions describing who is to receive the publication. You should have system administrator authority to carry out these tasks. Create the message links and addresses In this tutorial, messages are exchanged using the shared file link.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users ❖ To create the publisher 1. Add a login called hq_user, with hq as the default database and with system administrator access: exec sp_addlogin hq_user, hq_pwd, hq go exec sp_role ’grant’, sa_role, hq_user go 2. Add the login name as a user to the HQ database: use hq go exec sp_adduser hq_user go 3.
exec sp_grant_remote field_user, file, field go As with the publisher address, the address of the remote user (field) is a directory relative to the SQLRemote environment variable or registry entry. As you have not set this value, the address is taken relative to the directory from which the Message Agent is run. You should run the Message Agent from your tutorial directory for the addresses to be interpreted properly.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users Add a subscription Each user ID that is to receive changes to a publication must have a subscription to that publication. Subscriptions can only be created for a valid remote user. You need to add a subscription to the SalesRepData publication for the remote database user field_user. ❖ To create a subscription 1.
♦ uid=sa The login ID to use to log on to the database. ♦ pwd=sysadmin The password to use to log on to the database. ♦ C:\tutorial\field The directory in which to place files holding the data. ♦ field_user The user ID for which to extract the database. ☞ For more information on extraction utility options, see “The extraction utility” on page 303. Running this command produces the following files: ♦ Reload script The reload script is named reload.sql, and is placed in the current directory.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users 2. Connect to the server using the Interactive SQL utility: dbisql -c "eng=field;dbn=field;uid=DBA;pwd=SQL" The user ID and password must be entered in upper case, as the Adaptive Server Anywhere database was created as case-sensitive. 3. Load the data using the READ command: READ C:\TUTORIAL\RELOAD.SQL ❖ To load the data into the database as a batch process 1. Start an Adaptive Server Anywhere server running on the field database: dbeng9 field.
Start replicating data You now have a replication system in place. In this section, data is replicated from the consolidated database to the remote database, and from the remote to the consolidated database. Enter data at the consolidated database In this section we enter data into the SalesRep and Customer tables at the consolidated (Adaptive Server Enterprise) database, and replicate this data to the Adaptive Server Anywhere database. ❖ To enter data at the Adaptive Server Enterprise database 1.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users ❖ To replicate the data from Adaptive Server Enterprise 1. Enter the following statement (on a single line) at the command line to run the Message Agent against the consolidated database: ssremote -c "eng=server-name;dbn=hq;uid=sa;pwd=sysadmin" 2. Click Shutdown on the Message Agent window to stop the Message Agent when the messages have been sent.
You will see that the SalesRep table contains both rows entered at the consolidated database. This is because the SalesRepData publication included all the data from the SalesRep table. 3. Inspect the Customer table by typing the following statement: SELECT * FROM Customer You will see that the Customer table contains only one row (Ocean Sports) entered at the consolidated database. This is because the SalesRepData publication included only those customers assigned to the subscribed Sales Rep.
Chapter 5. A Tutorial for Adaptive Server Enterprise Users cust_key name rep_key cust1 Ocean Sports rep1 cust2 Sports Plus rep2 cust3 North Land Trading rep1 In this simple example, there is no protection against duplicate entries of primary key values. SQL Remote does provide for such protection. For information, see the chapters on SQL Remote Design.
70
PART II R EPLICATION D ESIGN FOR SQL R EMOTE This part describes replication design issues for SQL Remote.
CHAPTER 6 Principles of SQL Remote Design About this chapter This chapter describes general issues and principles for designing a SQL Remote installation. ☞ For system-specific details, see the chapters “SQL Remote Design for Adaptive Server Enterprise” on page 141 and “SQL Remote Design for Adaptive Server Anywhere” on page 91.
Design overview This chapter describes general publication design issues that you must address when designing a SQL Remote installation. It also describes how SQL Remote replicates data. Design at the consolidated database Like all SQL Remote administrative tasks, design is carried out by a database administrator or system administrator at the consolidated database. The Adaptive Server Enterprise System Administrator or database administrator should perform all SQL Remote configuration tasks.
Chapter 6. Principles of SQL Remote Design 4. Make the database case sensitive You can do this with the dbinit -c option. The following command creates a case-sensitive database named test.db in the current directory, using the current dbo user, ignoring trailing blanks, and removing historical system views: dbinit -b -c -k test.db Using compatible sort orders and character sets The SQL Remote Message Agent does not perform any character set conversions.
76 Adaptive Server Anywhere collation name Open Client / Open Server name Open Client / Open Server casesensitive sort order Open Client / Open Server caseinsensitive sort order 819ELL iso_1 bin_iso_1 bin_iso_1 819ESP iso_1 espdict_iso_1 espnocs_iso_1 819ISL iso_1 bin_iso_1 bin_iso_1 819LATIN1 iso_1 dictionary_iso_1 nocase_iso_1 819LATIN2 iso_1 bin_iso_1 bin_iso_1 819NOR iso_1 bin_iso_1 bin_iso_1 819RUS iso_1 bin_iso_1 bin_iso_1 819SVE iso_1 bin_iso_1 bin_iso_1 819TRK i
Chapter 6.
How statements are replicated SQL Remote replication is based on the transaction log, enabling it to replicate only changes to data, rather than all data, in each update. When we say that SQL Remote replicates data, we really mean that SQL Remote replicates SQL statements that modify data .
Chapter 6. Principles of SQL Remote Design UPDATE statement may differ from the entered UPDATE statement. UPDATE statements replicated as INSERTS or DELETES If an UPDATE statement has the effect of removing a row from a given remote user’s subscription, it is sent to that user as a DELETE statement. If an UPDATE statement has the effect of adding a row to a given remote user’s subscription, it is sent to that user as an INSERT statement.
In this case, the UPDATE does not update anything. ♦ The row to be updated differs in one or more of its columns If one of the values expected to be present has been changed by some other user, an update conflict occurs. At remote databases, the update takes place regardless of the values in the row. At the consolidated database, SQL Remote allows conflict resolution operations to take place.
Chapter 6. Principles of SQL Remote Design The Adaptive Server Anywhere FIRE_TRIGGERS database option prevents triggers from being fired. If you set this option for the user ID used by the Message Agent, be careful to not use this user ID for other purposes.
body of the triggers or you can set the Adaptive Server Anywhere Fire_triggers option to OFF for the Message Agent user ID. Replication of data definition statements Data definition statements (CREATE, ALTER, DROP, and others that modify database objects) are not replicated by SQL Remote unless they are entered while in passthrough mode. ☞ For information about passthrough mode for Adaptive Server Anywhere, see “Using passthrough mode” on page 260.
Chapter 6. Principles of SQL Remote Design How data types are replicated Long binary or character data, and datetime data, need special consideration. Replication of blobs Blobs are LONG VARCHAR, LONG BINARY, TEXT, and IMAGE data types: values that are longer than 256 characters. Adaptive Server Anywhere replication SQL Remote includes a special method for replicating blobs between Adaptive Server Anywhere databases.
The better solution is to have a document_in_progress table. When the user is done revising a document, the application moves it from the document_in_progress table to the replicated table. The results in a single update (200 kb of messages). Controlling replication of blobs The Adaptive Server Anywhere BLOB_THRESHOLD option allows further control over the replication of long values. Any value longer than the BLOB_THRESHOLD option is replicated as a blob.
Chapter 6. Principles of SQL Remote Design ❖ To find the dateformat settings on an Adaptive Server Enterprise database 1. Login to the Adaptive Server Enterprise database from isql using the login ID used by ssremote. In this example, we use ssr for this login ID. 2. Issue the following command: select * from master..syslogins where name = ’ssr’ go Adaptive Server Enterprise returns the default language for the ssr user. 3. If ssr uses the default language (us_english) then the default dateformat is YMD.
Who gets what? Each time a row in a table is inserted, deleted, or updated, a message has to be sent to those subscribed to the row. In addition, an update may cause the subscription expression to change, so that the statement is sent to some subscribers as a delete, some as an update, and some as an insert. ☞ For details of what statements get sent to which subscribers, see “How statements are replicated” on page 78. For details on subscriptions, see the following two chapters.
Chapter 6. Principles of SQL Remote Design contains either a single value or a comma-separated list of values. Not the subscriber list Adaptive Server Enterprise does not enter into the log a list of subscribers. The column value is entered. All handling of subscribers is left to the Message Agent.
Replication errors and conflicts SQL Remote is designed to allow databases to be updated at many different sites. Careful design is required to avoid replication errors, especially if the database has a complicated structure. This section describes the kinds of errors and conflict that can occur in a replication setup; subsequent sections describe how you can design your publications to avoid errors and manage conflicts.
Chapter 6. Principles of SQL Remote Design ♦ Conflicts A user updates a row. A second user updates the same row at another site. The second user’s operation succeeds, and SQL Remote allows a trigger to be fired (Adaptive Server Anywhere) or a procedure to be called (Adaptive Server Enterprise) to resolve these conflicts in a way that makes sense for the data being changed. Conflicts will occur in many installations.
CHAPTER 7 SQL Remote Design for Adaptive Server Anywhere About this chapter This chapter describes how to design a SQL Remote installation when the consolidated database is an Adaptive Server Anywhere database. Similar material for Adaptive Server Enterprise Many of the principles of publication design are the same for Adaptive Server Anywhere and Adaptive Server Enterprise, but there are differences in commands and capabilities.
Design overview Designing a SQL Remote installation includes the following tasks: ♦ Designing publications The publications determine what information is shared among which databases. ♦ Designing subscriptions The subscriptions determine what information each user receives. ♦ Implementing the design Creating publications and subscriptions for all users in the system.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Publishing data This section describes how to create simple publications consisting of whole tables, or of column-wise subsets of tables; these tables are also called articles. You can perform these tasks using Sybase Central or with the CREATE PUBLICATION statement in Interactive SQL. All publications in Sybase Central appear in the Publications folder.
♦ The following statement creates a publication that publishes the whole customer table: CREATE PUBLICATION pub_customer ( TABLE customer ) ♦ The following statement creates a publication including all columns and rows in each of a set of tables from the Adaptive Server Anywhere sample database: CREATE PUBLICATION sales ( TABLE customer, TABLE sales_order, TABLE sales_order_items, TABLE product ) ☞ For more information, see the “CREATE PUBLICATION statement” [ASA SQL Reference, page 385].
Chapter 7. SQL Remote Design for Adaptive Server Anywhere ❖ To publish only some columns in a table (SQL) 1. Connect to the database as a user with DBA authority. 2. Execute a CREATE PUBLICATION statement that specifies the publication name and the table name. List the published columns in parenthesis following the table name.
Publishing only some rows using a WHERE clause You can specify a WHERE clause to include in the publication only the rows that satisfy the WHERE conditions. ❖ To create a publication using a WHERE clause (Sybase Central) 1. Connect to the database as a user with DBA authority. 2. In the left pane, select the Publications folder. 3. From the File menu, choose New ➤ Publication. The Publication Creation wizard opens. 4. Type a name for the new publication. Click Next. 5.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere ♦ The following is a single-article publication sending relevant order information to Samuel Singer, a sales rep: CREATE PUBLICATION pub_orders_samuel_singer ( TABLE sales_order WHERE sales_rep = 856 ) ☞ For more information, see the “CREATE PUBLICATION statement” [ASA SQL Reference, page 385]. SUBSCRIBE BY The create publication statement also allows a SUBSCRIBE BY clause. This clause can also be used to selectively publish rows in SQL Remote.
❖ To create an article using a subscription expression (Sybase Central) 1. Connect to the database as a user with DBA authority. 2. In the left pane, select the Publications folder. 3. From the File menu, choose New ➤ Publication. The Publication Creation wizard appears. 4. Type a name for the publication and click Next. 5. On the Tables tab, configure the desired values for that table. 6. On the SUBSCRIBE BY Restrictions tab, use the controls to create the subscription expression. 7.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Users can subscribe to more than one publication, and can have more than one subscription to a single publication.
♦ Choose a table and click Next. ♦ Choose the columns for the article. Click Next. ♦ Enter a WHERE clause (if desired). Click Next. ♦ Create a SUBSCRIBE BY restriction (if desired). 6. Click Finish to create the article. ❖ To remove articles (Sybase Central) 1. Connect to the database as a user who owns the publication or as a user with DBA authority. 2. Open the Publications folder. 3. Select the publication you want to remove an article from. 4.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere ❖ To delete a publication (Sybase Central) 1. Connect to the database as a user with DBA authority. 2. Open the Publications folder. 3. Right-click the desired publication and choose Delete from the popup menu. ❖ To delete a publication (SQL) 1. Connect to the database as a user with DBA authority. 2. Execute a DROP PUBLICATION statement. Example The following statement drops the publication named pub_orders.
Publication design for Adaptive Server Anywhere Once you understand how to create simple publications, you must think about proper publication design. Sound design is an important part of building a successful SQL Remote installation. This section helps set out the principles of sound design as they apply to SQL Remote for Adaptive Server Anywhere.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere These key features of relational databases must be incorporated into the design of your publications and subscriptions. This section describes principles and techniques for sound design. Conditions for valid articles All columns in the primary key must be included in the article.
♦ Keep the number of publications small In particular, try not to reference the same table in many different publications. The work the database server needs to do is proportional to the number of publications. Keeping the number low and making effective use of subscriptions lightens the load on the database server. When operations occur on a table, the database server and the Message Agent must do some work for each publication that contains the table.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Partitioning tables that do not contain the subscription expression In many cases, the rows of a table need to be partitioned even when the subscription expression does not exist in the table. The Contact example The Contact database illustrates why and how to partition tables that do not contain the subscription expression. Example Here is a simple database that illustrates the problem.
Table Description Customer All customers that do business with the company. The Customer table includes the following columns: ♦ cust_key An identifier for each customer. This is the primary key. ♦ name The name of each customer. ♦ rep_key An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere ♦ Those contacts belonging to the relevant customers, from the Contact table. Partitioning the Customer table in the Contact example The Customer table can be partitioned using the rep_key value as a subscription expression.
Reps. When a customer is reassigned to a new sales rep, the Customer table is updated. The UPDATE is replicated as an INSERT or a or a DELETE to the old and new sales representatives, respectively, so that the customer row is properly transferred to the new sales representative. ☞ For information on the way in which Adaptive Server Anywhere and SQL Remote work together to handle this situation, see “Who gets what?” on page 86. When a customer is reassigned, the Contact table is unaffected.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere A special UPDATE statement for publications The UPDATE statement in this trigger is of the following special form: UPDATE table-name PUBLICATION publication-name { SUBSCRIBE BY subscription-expression | OLD SUBSCRIBE BY old-subscription-expression NEW SUBSCRIBE BY new-subscription-expression } WHERE search-condition ♦ Here is what the UPDATE statement clauses mean: ♦ The table-name indicates the table that must be modified at the remote databases.
♦ The subscription expression in this example returns a single value. Subqueries returning multiple values can also be used. The value of the subscription expression must the value after the UPDATE. In this case, the only subscriber to the row is the new sales representative. In “Sharing rows among several subscriptions” on page 112, we see cases where there are existing as well as new subscribers. Information in the transaction log Here we describe the information placed in the transaction log.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere SalesRepData - Publication Name rep1 - BEFORE list rep2 - AFTER list UPDATE Contact SET contact_key = ’contact1’, name = ’David’, cust_key = ’cust1’ WHERE contact_key = ’contact1’ SalesRepData - Publication Name rep1 - BEFORE list rep2 - AFTER list UPDATE Customer SET rep_key = ’rep2’ WHERE cust_key = ’cust1’ The Message Agent scans the log for these tags.
Sharing rows among several subscriptions There are cases where a row may need to be included in several subscriptions. For example, we may have a many-to-many relationship. In this section, we use a case study to illustrate how to handle this situation. The Policy example The Policy database illustrates why and how to partition tables when there is a many-to-many relationship in the database. Example database Here is a simple database that illustrates the problem.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Table Description Customer All customers that do business with the company.
♦ Those rows from the Customer table listing customers that deal with the sales rep subscribed to the data. New problems The many-to-many relationship between customers and sales representatives introduces new challenges in maintaining a proper sharing of information: ♦ We have a table (in this case the Customer table) that has no reference to the sales representative value that is used in the subscriptions to partition the data. Again, this problem is addressed by using a subquery in the publication.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere ... TABLE Policy SUBSCRIBE BY rep_key, ... The subscription expression ensures that each sales rep receives only those rows of the table for which the value of the rep_key column matches the value provided in the subscription. The Policy table partitioning is disjoint: there are no rows that are shared with more than one subscriber. A subscription expression with a subquery is used to define the partition.
in their database. However, no change has been made to the Customer table, and so no changes to the Customer table are replicated to the subscriber. In the absence of triggers, this would leave the subscriber with incorrect data in their Customer table. The same kind of problem arises when a new row is added to the Policy table. Using Triggers to solve the problem The solution is to write triggers that are fired by changes to the Policy table, which include a special syntax of the UPDATE statement.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere that this subquery can be multi-valued. Multiple-valued subqueries The subquery in the BEFORE INSERT trigger is a UNION expression, and can be multi-valued: ... SELECT rep_key FROM Policy WHERE cust_key = NewRow.cust_key UNION ALL SELECT NewRow.rep_key ... ♦ The second part of the UNION is the rep_key value for the new sales representative dealing with the customer, taken from the INSERT statement.
♦ UPDATES on the Policy table have not been described here. They should either be prevented, or a BEFORE UPDATE trigger is required that combines features of the BEFORE INSERT and BEFORE DELETE triggers shown in the example. Using the Subscribe_by_remote option with many-to-many relationships When the Subscribe_by_remote option is ON, operations from remote databases on rows with a subscribe by value of NULL or an empty string will assume the remote user is subscribed to the row.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Subscribe_by_remote were set to OFF, the result would be that the new Customer is sent back to Marc Dill as a DELETE operation. As long as Subscribe_by_remote is set to ON, the Message Agent assumes the row belongs to the Sales Rep that inserted it, the INSERT is not replicated back to Marc Dill, and the replication system is intact.
Managing conflicts An UPDATE conflict occurs when the following sequence of events takes place: 1. User 1 updates a row at remote site 1. 2. User 2 updates the same row at remote site 2. 3. The update from User 1 is replicated to the consolidated database. 4. The update from User 2 is replicated to the consolidated database. When the SQL Remote Message Agent replicates UPDATE statements, it does so as a separate UPDATE for each row. Also, the message contains the old row values for comparison.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere custom conflict resolution, using a trigger to resolve conflicts in a way that makes sense for the data being changed. Conflict resolution does not apply to primary key updates UPDATE conflicts do not apply to primary key updates. You should not update primary keys in a SQL Remote installation. Primary key conflicts must be excluded from the installation by proper design.
Implementing conflict resolution This section describes what you need to do to implement custom conflict resolution in SQL Remote for Adaptive Server Anywhere. The concepts are the same in SQL Remote for Adaptive Server Enterprise, but the implementation is different. SQL Remote allows you to define conflict resolution triggers to handle UPDATE conflicts. Conflict resolution triggers are fired only at a consolidated database, when messages are applied by a remote user.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere UPDATE table-list SET column-name = expression, . . . [ VERIFY (column-name, . . . ) VALUES ( expression, . . . ) ] [ WHERE search-condition ] The VERIFY clause can be used only if table-list consists of a single table. It compares the values of specified columns to a set of expected values, which are the values that were present in the publisher database when the UPDATE statement was applied there.
group or just for the user contained in the Message Agent connection string. Using the CURRENT REMOTE USER special constant The CURRENT REMOTE USER special constant holds the user ID of the remote user sending the message. This can be used in RESOLVE UPDATE triggers that place reports of conflicts into a table, to identify the user producing a conflict. Conflict resolution examples This section describes some ways of using RESOLVE UPDATE triggers to handle conflicts.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Resolving inventory conflicts Consider a warehouse system for a manufacturer of sporting goods. There is a table of product information, with a quantity column holding the number of each product left in stock. An update to this column will typically deplete the quantity in stock or, if a new shipment is brought in, add to it.
inventory column to produce the final result, so that a final value of 63 is placed into the database. Conflict resolution trigger: correct result 28 > 23 Implementing the solution 68 > 63 28 > 68 A suitable RESOLVE UPDATE trigger for this situation would add the increments from the two updates. For example, CREATE TRIGGER resolve_quantity RESOLVE UPDATE OF quantity ON "DBA".product REFERENCING OLD AS old_name NEW AS new_name REMOTE AS remote_name FOR EACH ROW BEGIN SET new_name.quantity = new_name.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere SQL Remote resolves conflicts; you may just want to report the conflicts by storing them in a table. In this way, you can look at the conflict table to see what, if any, conflicts have occurred, and if necessary take action to resolve the conflicts. Designing to avoid referential integrity errors The tables in a relational database are related through foreign key references.
exist at other databases in the setup. When an action that fires a trigger at the consolidated database is replicated at the replicate site, the trigger is automatically fired. By default, the database extraction utility extracts the trigger definitions, so that they are in place at the remote database also. If a publication includes only a subset of a database, a trigger at the consolidated database may refer to tables or rows that are present at the consolidated database, but not at the remote databases.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Ensuring unique primary keys Primary key values must be unique. When all users are connected to the same database, there is no problem keeping unique values. If a user tries to re-use a value, the INSERT statement fails. The situation is different in a replication system because users are connected to many databases. A potential problem arises when two users, connected to different databases, insert a row using the same primary key value.
You assign each copy of the database a unique global database identification number. Adaptive Server Anywhere supplies default values in a database only from the partition uniquely identified by that database’s number. For example, if you assigned the database in the above example the identity number 10, the default values in that database would be chosen in the range 10001–11000.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere ❖ To set the global database identification number 1. You set the identification number of a database by setting the value of the public option Global_database_id. The identification number must be a non-negative integer. For example, the following statement sets the database identification number to 20. SET OPTION PUBLIC.
set option "PUBLIC"."Global_database_id" = ’1’; create table extract_id ( next_id integer not null) ; insert into extract_id values( 1 ); create procedure sp_hook_dbxtract_begin as declare @next_id integer update extract_id set next_id = next_id + 1000 select @next_id = (next_id ) from extract_id commit update #hook_dict set value = @next_id where name = ’extracted_db_global_id’ Then each extracted or re-extracted database will get a different Global_database_id.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere If the public option Global_database_id is set to the default value of 2147483647, a null value is inserted into the column. Should null values not be permitted, the attempt to insert the row causes an error. This situation arises, for example, if the column is contained in the table’s primary key. Because the public option Global_database_id cannot be set to negative values, the values chosen are always positive.
The primary key pool table The pool of primary keys is held in a separate table. The following CREATE TABLE statement creates a primary key pool table: CREATE TABLE KeyPool ( table_name VARCHAR(40) NOT NULL, value INTEGER NOT NULL, location CHAR(12) NOT NULL, PRIMARY KEY (table_name, value), ); The columns of this table have the following meanings: Column Description table_name Holds the names of tables for which primary key pools must be maintained.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere 2. Create subscriptions for each remote database to the KeyPoolData publication. CREATE SUBSCRIPTION TO KeyPoolData( ’user1’ ) FOR user1; CREATE SUBSCRIPTION TO KeyPoolData( ’user2’ ) FOR user2; ... The subscription argument is the location identifier. In some circumstances it makes sense to add the KeyPool table to an existing publication and use the same argument to subscribe to each publication.
CREATE PROCEDURE ReplenishPool() BEGIN FOR EachTable AS TableCursor CURSOR FOR SELECT table_name AS CurrTable, max(value) as MaxValue FROM KeyPool GROUP BY table_name DO FOR EachRep AS RepCursor CURSOR FOR SELECT location AS CurrRep, count(*) as NumValues FROM KeyPool WHERE table_name = CurrTable GROUP BY location DO // make sure there are 100 values.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Cannot use a trigger to replenish the key pool You cannot use a trigger to replenish the key pool, as trigger actions are not replicated. Adding new customers When a sales representative wants to add a new customer to the Customer table, the primary key value to be inserted is obtained using a stored procedure. This example shows a stored procedure to supply the primary key value, and also illustrates a stored procedure to carry out the INSERT.
CREATE PROCEDURE NewCustomer( IN customer_name CHAR( 40 ) ) BEGIN DECLARE new_cust_key INTEGER ; CALL NewKey( ’Customer’, new_cust_key ); INSERT INTO Customer ( cust_key, name, location ) VALUES ( ’Customer ’ || CONVERT (CHAR(3), new_cust_key), customer_name, CURRENT PUBLISHER ); ); END You may want to enhance this procedure by testing the new_cust_key value obtained from NewKey to check that it is not NULL, and preventing the insert if it is NULL.
Chapter 7. SQL Remote Design for Adaptive Server Anywhere Creating subscriptions To subscribe to a publication, each subscriber must be granted REMOTE permissions and a subscription must also be created for that user. The details of the subscription are different depending on whether or not the publication uses a subscription expression. Working with subscriptions in Sybase Central ❖ To create and manage subscriptions in Sybase Central 1. In the left pane, open the Publications folder. 2.
CREATE SUBSCRIPTION TO pub_orders_samuel_singer FOR SamS Subscriptions with a subscription expression To subscribe a user to a publication, if that publication does have a subscription expression, you need the following information: ♦ User ID The user who is being subscribed to the publication. This user must have been granted remote permissions. ♦ Publication name The name of the publication to which the user is being subscribed.
CHAPTER 8 SQL Remote Design for Adaptive Server Enterprise About this chapter This chapter describes how to design a SQL Remote installation when the consolidated database is at an Adaptive Server Enterprise server. Similar material for Adaptive Server Anywhere Many of the principles of publication design are the same for Adaptive Server Anywhere and Adaptive Server Enterprise, but there are differences in commands and capabilities.
Design overview Designing a SQL Remote installation includes the following tasks: ♦ Designing publications The publications determine what information is shared among which databases. ♦ Designing subscriptions The subscriptions determine what information each user receives. ♦ Implementing the design Creating publications and subscriptions for all users in the system.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Creating publications In this section This section describes how to create simple publications consisting of whole tables, or of column-wise subsets of tables. ☞ Simple publications are also discussed in the chapter “A Tutorial for Adaptive Server Enterprise Users” on page 53. Creating whole-table articles The simplest type of article is one that includes all the rows and columns of a database table.
2. Add the table to the publication. You do this by executing the sp_add_article procedure: sp_add_article publication-name, table-name go The sp_add_article procedure adds a table to a publication. By default, all columns of the table are added to the publication. If you wish to add only some of the columns, you must use the sp_add_article_col procedure to specify which columns you wish to include. 3. Add individual columns to the publication.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise ♦ Subscription column SQL Remote for Adaptive Server Anywhere supports expressions other than column names. For Adaptive Server Enterprise, the subscription expression must be a column name. When to use WHERE and SUBSCRIBE BY You should use a subscription expression when different subscribers to a publication are to receive different rows from a table. The subscription expression is the most powerful method of partitioning tables.
❖ To create an article using a subscription column 1. If you have not already done so, mark the table for replication. You do this by executing the sp_add_remote_table procedure: sp_add_remote_table table_name 2. Add the table to the publication.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Publication design for Adaptive Server Enterprise Once you understand how to create simple publications, you must think about proper design of publications. This section describes the issues involved in designing publications, and how to take steps towards sound design. Design issues overview Each subscription must be a complete relational database A remote database shares with the consolidated database the information in their subscriptions.
Supporting INSERTS at remote databases For INSERT statements at a remote database to replicate correctly to the consolidated database, you can exclude from an article only columns that can be left out of a valid INSERT statement. These are: ♦ Columns that allow NULL. ♦ Columns that have defaults. If you exclude any column that does not satisfy one of these requirements, INSERT statements carried out at a remote database will fail when replicated to the consolidated database.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Partitioning tables that do not contain the subscription column In many cases, the rows of a table need to be partitioned even when the subscription column does not exist in the table. This section describes how to handle this case, using an example. The Contact example The Contact database illustrates why and how to partition tables that do not contain the subscription column. Example Here is a simple database that illustrates the problem.
Table Description SalesRep All sales representatives that work for the company. The SalesRep table has the following columns: ♦ rep_key An identifier for each sales representative. This is the primary key. ♦ name The name of each sales representative. The SQL statement creating this table is as follows: CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) ) go Customer All customers that do business with the company.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Table Description Contact All individual contacts that do business with the company. Each contact belongs to a single customer. The Contact table includes the following columns: ♦ contact_key An identifier for each contact. This is the primary key. ♦ name The name of each contact. ♦ cust_key An identifier for the customer to which the contact belongs. This is a foreign key to the Customer table.
No log entries for the Contact table when territories realigned When a customer is reassigned, the Contact table is unaffected. There are no changes to the Contact table, and consequently no entries in the transaction log pertaining to the Contact table. In the absence of this information, SQL Remote cannot reassign the rows of the Contact table along with the Customer.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Log entries are values, not subscribers Although in this case the values entered correspond to subscribers, it is not a list of subscribers that is entered in the log. The server handles only information about publications, and the Message Agent handles all information about subscribers. The values entered in the log are for comparison to the subscription value in each subscription.
Customer Contact contact cust_key _key subscription _list cust_key rep_key con1 cust101 rep1 cust101 rep1 con2 cust101 rep1 cust102 rep1 con3 cust102 rep1 cust103 rep2 con4 cust103 rep2 cust104 rep3 con5 cust104 rep3 ☞ For an Adaptive Server Anywhere consolidated database, the solution is different. For more information, see “Partitioning tables that do not contain the subscription expression” on page 105.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise inserted; these rows being identified by the subquery SELECT contact_key FROM inserted An UPDATE trigger for the Contact table The trigger for an UPDATE on the Contact table checks to see if the cust_key column is changed, and if it has updates the subscription_list column. CREATE TRIGGER update_contact_sub_list ON Contact FOR UPDATE AS IF UPDATE ( cust_key ) BEGIN UPDATE Contact SET subscription_list = Customer.
have triggers defined to maintain the subscription-list column. ❖ To create a subscription view 1. Design a query that uses a subquery to select the proper rows for a subscription from a table. For example, continuing the example from the preceding sections, the following query selects the rows of the Contact table for a user subscribed by rep_key value rep5: SELECT * FROM Contact WHERE ’rep5’ = (SELECT rep_key FROM Customer WHERE cust_key = Contact.cust_key ) 2. Create a view that contains this subquery.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Sharing rows among several subscriptions There are cases where a row may need to be included in several subscriptions. For example, if instead of the many-to-one relationship between customers and sales representatives that we had above, we may have a many-to-many relationship. The Policy example The Policy database illustrates why and how to partition tables when there is a many-to-many relationship in the database.
Customer Policy SalesRep cust_key policy_key rep_key name cust_key name subscription_list rep_key Adaptive Server Enterprise VARCHAR columns are limited to 255 characters, and this limits the number of values that can be stored in the comma-delimited list.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise // Create an empty publication exec sp_create_publication ’SalesRepData’ //Add the Sales Rep table to the publication exec sp_add_article ’SalesRepData’, ’SalesRep’ //Add the Policy table to the publication exec sp_add_article ’SalesRepData’, ’Policy’, NULL, ’rep_key’ // Add the Customer table to the publication.
CREATE PROCEDURE SubscribeCustomer @cust_key CHAR(12) AS BEGIN -- Rep returns the rep list for customer @cust_key DECLARE Rep CURSOR FOR SELECT DISTINCT RTRIM( rep_key ) FROM Policy WHERE cust_key = @cust_key DECLARE @rep_key CHAR(12) DECLARE @subscription_list VARCHAR(255) -- build comma-separated list of rep_key -- values for this Customer OPEN Rep FETCH Rep INTO @rep_key IF @@sqlstatus = 0 BEGIN SELECT @subscription_list = @rep_key WHILE 1=1 BEGIN FETCH Rep INTO @rep_key IF @@sqlstatus != 0 BREAK SELECT
Chapter 8.
Triggers at the consolidated database only The values in the subscription-list column are maintained by triggers. These triggers fire at the consolidated database when the triggering inserts or updates are applied by the Message Agent. The triggers must be excluded from the remote databases, as they maintain a column that does not exist. You can use the sp_user_extraction_hook procedure to exclude only certain triggers from a remote database on extraction.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise If you are extracting databases for many users, and performance is a problem for you, you can use a subscription view to improve performance. The view must contain a subquery, which is used for extraction and synchronization only, and is ignored during log scanning. The tables involved still need to have triggers defined to maintain the subscription-list column. ❖ To create a subscription view 1.
Using the Subscribe_by_remote option with many-to-many relationships When the SUBSCRIBE_BY_REMOTE option is ON, operations that arrive from remote databases on rows with a subscribe by value of NULL or ‘’ will assume the remote user is subscribed to the row. By default, the SUBSCRIBE_BY_REMOTE option is set to ON. In most cases, this setting is the desired setting. The SUBSCRIBE_BY_REMOTE option solves a problem that otherwise would arise with publications including the Policy example.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Managing conflicts An UPDATE conflict occurs when the following sequence of events takes place: 1. User 1 updates a row at remote site 1. 2. User 2 updates the same row at remote site 2. 3. The update from User 1 is replicated to the consolidated database. 4. The update from User 2 is replicated to the consolidated database. When the SQL Remote Message Agent replicates UPDATE statements, it does so as a separate UPDATE for each row.
Conflicts do not apply to primary keys UPDATE conflicts do not apply to primary key updates. If the column being updated is a primary key, then when the update from User 2 arrives at the consolidated database, no row will be updated. This section describes how you can build conflict resolution into your SQL Remote installation at the consolidated database.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Naming the objects When a table is marked for replication, using the sp_add_remote_table or sp_modify_remote_table stored procedure, optional parameters specify the names of the conflict resolution objects. The sp_add_remote_table and sp_modify_remote_table procedures take one compulsory argument, which is the name of the table being marked for replication.
VERIFY_ALL_COLUMNS is set to OFF, if an UPDATE statement updates more than 128 columns, conflict resolution will not work. A first conflict resolution example In this example, conflicts in the Customer table in the two-table example used in the tutorials are reported into a table for later review.
Chapter 8.
❖ To test the example 1. Create the tables and the procedure in the consolidated database, and add them as conflict resolution objects to the Customer table. 2. Insert and commit a change at the consolidated database. For example: UPDATE Customer SET name = ’Sea Sports’ WHERE cust_key=’cust1’ go COMMIT go 3. Insert and commit a different change to the same line at the remote database. For example: UPDATE Customer SET name = ’C Sports’ WHERE cust_key=’cust1’ go COMMIT go 4.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise ♦ Report the name of the remote user whose update failed, along with the lost and won names. The conflict resolution objects In this case, the ConflictLog table has an additional column to record the user ID of the remote user.
-- Get the remote user value from #remote SELECT @remote_user = current_remote_user FROM #remote -- Report the problem INSERT INTO ConflictLog ( lost_name, won_name, remote_user ) VALUES ( @lost_name, @won_name, @remote_user ) -- Disallow the update from the Message Agent -- by resetting the row in the Customer table UPDATE Customer SET name = @won_name WHERE cust_key = @cust_key END Notes There are several points of note here: ♦ The user ID of the remote user is stored by the Message Agent in the current
Chapter 8. SQL Remote Design for Adaptive Server Enterprise 4. Replicate the change from the remote to the consolidated database, by running the Message Agent at the remote database to send the message, and then at the consolidated database to receive and apply the message. 5. At the consolidated database, view the Customer table and the ConflictLog table.
If the SalesRep table had a foreign key to another table (say, Employee) that was not included in the publication, inserts or updates to SalesRep would fail to replicate unless the remote database had the foreign key reference removed. If you use the extraction utility to create the remote databases, the foreign key reference is automatically excluded from the remote database, and this problem is avoided.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Ensuring unique primary keys Users at physically distinct sites can each INSERT new rows to a table, so there is an obvious problem ensuring that primary key values are kept unique. If two users INSERT a row using the same primary key values, the second INSERT to reach a given database in the replication system will fail.
Column Description table_name Holds the names of tables for which primary key pools must be maintained. In our simple example, if new sales representatives were to be added only at the consolidated database, only the Customer table needs a primary key pool and this column is redundant. It is included to show a general solution. value Holds a list of primary key values. Each value is unique for each table listed in table_name.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise The subscription argument is the location identifier. In some circumstances it makes sense to add the KeyPool table to an existing publication and use the same argument to subscribe to each publication. Here we keep the location and rep_key values distinct to provide a more general solution. Filling and replenishing the key pool Every time a user adds a new customer, their pool of available primary keys is depleted by one.
This procedure fills the pool for each user up to ten values. You may wish to use a larger value in a production environment. The value you need depends on how often users are inserting rows into the tables in the database. The ReplenishPool procedure must be run periodically at the consolidated database to refill the pool of primary key values in the KeyPool table.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise CREATE PROCEDURE NewKey @TableName VARCHAR(40), @Location VARCHAR(6), @Value INTEGER OUTPUT AS BEGIN DECLARE @NumValues INTEGER SELECT @NumValues = count(*), @Value = min(value) FROM KeyPool WHERE table_name = @TableName AND location = @Location IF @NumValues > 1 DELETE FROM KeyPool WHERE table_name = @TableName AND value = @Value ELSE -- Never take the last value, -- because RestorePool will not work.
Primary key pool summary The primary key pool technique requires the following components: ♦ Key pool table A table to hold valid primary key values for each database in the installation. ♦ Replenishment procedure A stored procedure keeps the key pool table filled. ♦ Sharing of key pools Each database in the installation must subscribe to its own set of valid values from the key pool table.
Chapter 8. SQL Remote Design for Adaptive Server Enterprise Creating subscriptions To subscribe to a publication, each subscriber must be granted REMOTE permissions and a subscription must also be created for that user. The details of the subscription are different depending on whether or not the publication uses a subscription column.
Starting a subscription 182 In order to receive and apply updates properly, each subscriber needs to have an initial copy of the data. The synchronization process is discussed in “Synchronizing databases” on page 189.
PART III SQL R EMOTE A DMINISTRATION This part describes deployment and administration issues for SQL Remote.
CHAPTER 9 Deploying and Synchronizing Databases About this chapter Contents This chapter describes the steps you need to take to deploy and synchronize a SQL Remote replication installation.
Deployment overview When you have completed the design phase of a SQL Remote system, the next step is to create and deploy the remote databases and applications. Deployment tasks In some cases, deployment is a major undertaking. For example, if you have a large number of remote users in a sales force automation system, deployment involves the following steps: 1. Building an Adaptive Server Anywhere database for each remote user, with their own initial copy of the data. 2.
Chapter 9. Deploying and Synchronizing Databases Test before deployment Thorough testing of your SQL Remote system should be carried out before deployment, especially if you have a large number of remote sites. When you are in the design and setup phase, you can alter many facets of the SQL Remote setup. Altering publications, message types, writing triggers to resolve update conflicts are all easy to do. Once you have deployed a SQL Remote application, the situation is different.
permissible, while other changes are restrictive, and must be avoided. The following changes must be avoided, except under the conditions stated: ♦ Change the publisher for the consolidated database. ♦ Make restrictive changes to tables, such as dropping a column or altering a column to not allow NULL values. Changes that include the column or including NULL entries may already be being sent in messages around the SQL Remote setup, and will fail. ♦ Alter a publication.
Chapter 9. Deploying and Synchronizing Databases Synchronizing databases What is synchronization? SQL Remote replication is carried out using the information in the transaction log, but there are two circumstances where SQL Remote deletes all existing rows from those tables of a remote database that form part of a publication, and copies the publication’s entire contents from the consolidated database to the remote site. This process is called synchronization.
Example For example, you may be running an Adaptive Server Enterprise server on a UNIX system that holds the consolidated database, but wish to deploy remote databases on laptop computers running some flavor of Windows. In this circumstance, you have several options for the platforms on which you extract the database, including the following, assuming you have the requisite software: ♦ Run the extraction utility on UNIX to create the reload script and data files.
Chapter 9. Deploying and Synchronizing Databases Using the extraction utility The extraction utility is an aid to creating remote Adaptive Server Anywhere databases. It cannot be used to create remote Adaptive Server Enterprise databases. Running the extraction utility The extraction utility can be accessed in the following ways: ♦ From Sybase Central, if your consolidated database is Adaptive Server Anywhere. ♦ As a command-line utility.
When used from Sybase Central, the extraction utility carries out the database unloading task, in the same way that dbxtract does, and then takes the additional step of creating the new database. The extraction utility does not use a message system. The reload file (ssxtract /dbxtract ) or database (from Sybase Central) is created in a directory accessible from the current machine.
Chapter 9. Deploying and Synchronizing Databases ♦ Extracts (unloads) the relevant structures and/or data from the consolidated database to files ♦ Loads those files into the newly created remote database ❖ To extract a database for a remote user (Sybase Central) 1. In the left pane, select the Adaptive Server Anywhere plug-in. 2. In the right pane, click the Utilities tab. 3. In the right pane, double-click Extract Database. 4. Follow the instructions in the wizard.
☞ For performance tips for Adaptive Server Enterprise users using a subscription-list column, see “Tuning extraction performance” on page 155 and “Tuning extraction performance for shared rows” on page 162. There are several potential causes of inefficiency in a large-scale extraction process: ♦ The extraction utility extracts one database at a time, including the schema and data for each user. Commonly, many users share a common schema, and only the data differs.
Chapter 9. Deploying and Synchronizing Databases and uses large numbers of locks. Also, the subscriptions must be started at the same time that the copy is made. Any operations that take place between the copy and the starting of the subscriptions would be lost, and could lead to errors at remote databases. Extracting groups If the remote user is a group user ID, the extraction utility extracts all the user IDs of members of that group.
♦ Using the extraction utility in multi-tiered setups To understand the role of the extraction utility in multi-tiered arrangements, consider a three-tiered SQL Remote setup. This setup is illustrated in the following diagram. HQ Region 1 Laptop 1 Region 2 Laptop 2 Laptop 3 From the consolidated database at the top level, you can use the extraction utility to create the second-level databases.
Chapter 9. Deploying and Synchronizing Databases ☞ For a full description of Adaptive Server Enterprise/Adaptive Server Anywhere compatibility, see the part Transact-SQL Compatibility, in the Adaptive Server Anywhere User’s Guide. Features not supported in ssxtract include the following: ♦ Grouped procedures Adaptive Server Anywhere does not support procedure groups, and they are not extracted by ssxtract.
Synchronizing data over a message system Creating subscriptions A subscription is created at a consolidated Adaptive Server Enterprise database using the sp_subscription procedure with a first argument of create. Creating a subscription defines the data to be received. It does not synchronize a subscription (provide an initial copy of the data) or start (exchange messages) a subscription.
CHAPTER 10 SQL Remote Administration About this chapter This chapter describes general issues and principles for administering a running SQL Remote installation. ☞ For system-specific details, see the chapters “Administering SQL Remote for Adaptive Server Enterprise” on page 263 and “Administering SQL Remote for Adaptive Server Anywhere” on page 241.
Management overview This chapter describes administration issues for SQL Remote installations. Administration of a deployed and running SQL Remote setup is carried out at a consolidated database. ♦ Permissions As a SQL Remote installation includes many different physical databases, a consistent scheme for users having permissions on remote and consolidated databases is necessary. A section of this chapter describes the considerations you need to make when assigning users permissions.
Chapter 10. SQL Remote Administration Managing SQL Remote permissions Users of a database involved in SQL Remote replication are identified by one of the following sets of permissions: ♦ PUBLISH A single user ID in a database is identified as the publisher for that database. All outgoing SQL Remote messages, including both publication updates and receipt confirmations, are identified by the publisher user ID.
❖ To create a new user as the publisher (Sybase Central) 1. In the left pane, select the Users & Groups folder. 2. From the File menu, choose New ➤ User. The User Creation wizard appears. 3. Follow the instructions in the wizard. Ensure that the user has a password and is granted Remote DBA authority; this enables the user ID to run the Message Agent. 4. Click Finish to create the user. 5. In the Users & Groups folder, right-click the user you just created and choose Change to Publisher from the popup menu.
Chapter 10. SQL Remote Administration The userid is a user with CONNECT permissions on the current database.
loss of information. You should not change the consolidated database publisher user ID unless you are prepared to close down the SQL Remote setup and resynchronize all remote users. Granting and revoking REMOTE and CONSOLIDATE permissions REMOTE and CONSOLIDATE permissions are very similar. Each database receiving messages from the current database must have an associated user ID on the current database that is granted one of REMOTE or CONSOLIDATE permissions.
Chapter 10. SQL Remote Administration Sybase Central example You can add a remote user to a database using Sybase Central. Remote users and groups appear in two locations in Sybase Central: in the Users & Groups folder, and in the SQL Remote Users folder. This section applies only to Adaptive Server Anywhere databases. By default, remote users are created with remote DBA authority. Since the message agent for access to the remote database requires this authority, you shouldn’t revoke it.
♦ Send message daily, at 10 p.m. GRANT REMOTE TO S_Beaulieu TYPE smtp ADDRESS ’s_beaulieu@acme.com’ SEND AT ’22:00’ Adaptive Server Enterprise example The following statement grants remote permissions to user S_Beaulieu with the following options: ♦ Use the file-sharing system to exchange messages. ♦ Place messages in the directory beaulieu under the address root directory.
Chapter 10. SQL Remote Administration ♦ Default setting (no SEND clause) If any user has no SEND AT or SEND EVERY clause, the Message Agent sends messages every time it is run, and then stops: it runs in batch mode. Setting the send frequency in Sybase Central In Sybase Central, you can specify the send frequency in the following ways: ♦ When you make an existing user or group remote. For more information, see “Granting REMOTE permissions” on page 204.
You can revoke REMOTE permissions on Adaptive Server Anywhere databases from Sybase Central. Revoking permissions from Sybase Central ❖ To revoke REMOTE permissions (Sybase Central) 1. Open either the Users & Groups folder or the SQL Remote Users folder. 2. Right-click the remote user or group and choose Revoke Remote from the popup menu. Revoking permissions in Adaptive Server Anywhere REMOTE and CONSOLIDATE permissions can be revoked from a user using the REVOKE statement.
Chapter 10. SQL Remote Administration Publish Remote no permissions no permissions The following picture shows SQL Remote permissions, as granted at an internal site of a three-tier installation. Consolidate Publish Remote Remote The following picture shows SQL Remote permissions, as granted at an internal site of a three-tier installation.
Using message types SQL Remote supports several different systems for exchanging messages. The message systems supported by SQL Remote are: ♦ file Storage of message files in directories on a shared file system for reading by other databases. ♦ ftp Storage of message files in directories accessible by a file transfer protocol (ftp) link. ♦ mapi Microsoft’s messaging API (MAPI) link, used in Microsoft Mail and other electronic mail systems.
Chapter 10. SQL Remote Administration publisher address at a consolidated database is used by the database extraction utility as a return address when creating remote databases. It is also used by the Message Agent to identify where to look for incoming messages for the file system. The address supplied with a message type definition is closely tied to the publisher ID of the database. Valid addresses are considered in following sections.
3. In the right pane, right-click the message type you wish to alter and choose Properties from the popup menu. 4. On the property sheet, configure the various options. If you wish to drop a message type from the installation, you can do so. ❖ To drop a message type (Sybase Central) 1. In the left pane, open the SQL Remote Users folder for a database. 2. In the right pane, click the Message Types tab. 3. In the right pane, right-click the message type you wish to alter and choose Delete from the popup menu.
Chapter 10. SQL Remote Administration If you wish to change the publisher’s address, you can do so by altering the message type. ❖ To alter a message type (SQL) 1. Make sure you have decided on a new address for the publisher under the message type. 2. Execute an ALTER REMOTE MESSAGE TYPE statement.
♦ “ALTER REMOTE MESSAGE TYPE statement” on page 355 ♦ “DROP REMOTE MESSAGE TYPE statement” on page 362 Setting message type control parameters Each message link has several parameters that govern aspects of its behavior. The parameters differ from message system to message system, but all are managed in the same way. When you first use the Message Agent for a particular message link, it displays a dialog box showing a set of parameters that control the behavior of the link.
Chapter 10. SQL Remote Administration The parameters for each message link go in a key under the SQL Remote key, with the name of the message link (4, smtp, and so on). ♦ NetWare You should create a file named dbremote.ini in the sys:\system directory to hold the FILE system directory setting. This file is not a Windows-format INI file: it must consist of a single line, holding only the directory name. For example, if the directory is user:\dbr43, then the dbremote.
You can also use the file system to put the messages in directories on the consolidated and remote machines. A simple file transfer mechanism can then be used to exchange the files periodically to effect replication. FILE message control parameters The FILE message system uses the following control parameters: ♦ Directory This is set to the directory under which the messages are stored. The setting is an alternative to the SQLREMOTE environment variable.
Chapter 10. SQL Remote Administration ♦ port Usually not required. This is the IP port number used for the Ftp connection. ♦ debug This is set to either YES or NO, with the default being NO. When set to YES, debugging output is displayed. ♦ active_mode This is set to either YES or NO, with the default being NO (passive mode). Troubleshooting ftp problems Most problems with the FTP message link are network setup issues. This section contains a list of tests you can try to troubleshoot problems.
masquerading gateway incoming connections may fail depending on you gateway software. If the FTP server is connecting and having problems getting directory listings or manipulating files; make sure your permissions are set up correctly and the directories that you need exist. Check permissions and directory structures Log into the FTP server using an FTP program. Change directories to the location stored in the root_directory parameter.
Chapter 10. SQL Remote Administration message link is working. SMTP message control parameters Before the Message Agent connects to the message system to send or receive messages, the user must either have a set of control parameters already set on their machine, or must fill in a window with the needed information. This information is needed only on the first connection. It is saved and used as the default entries on subsequent connects.
♦ pop3_password This is used to retrieve mail. It corresponds to the password field in the SMTP/POP3 login dialog. If all of these five fields are set, the login dialog is not displayed. ♦ Debug When set to YES, displays all SMTP and POP3 commands and responses. This is useful for troubleshooting SMTP/POP support problems. Default is NO. Sharing SMTP/POP addresses The database should have its own e-mail account for SQL Remote messages, separate from personal e-mail messages intended for reading.
Chapter 10. SQL Remote Administration SQL Remote attempts to send application-defined messages, which MAPI identifies and hides when the mailbox is opened. In this way, users can use the same e-mail address and same connection to receive their personal e-mail and their database updates, yet the SQL Remote messages do not interfere with the mail intended for reading.
☞ For a list of operating systems for which VIM is supported, see “Supported operating systems” on page 447. VIM message control parameters The VIM message system uses the following control parameters: ♦ Path This corresponds to the Path field in the cc:Mail login dialog. It is not applicable to and is ignored under Lotus Notes. ♦ Userid This corresponds to the User ID field in the cc:Mail login dialog. ♦ Password This corresponds to the Password field in the cc:Mail login dialog.
Chapter 10. SQL Remote Administration Running the Message Agent The SQL Remote Message Agent is a key component in SQL Remote replication. The Message Agent handles both the sending and receiving of messages. It carries out the following functions: ♦ It processes incoming messages, and applies them in the proper order to the database. ♦ It scans the transaction log or stable queue at each publisher database, and translates the log entries into messages for subscribers.
The options available depend on the send frequency options selected for the remote users. Sending frequency options are described in “Selecting a send frequency” on page 206. ❖ To run the Message Agent in continuous mode 1. Ensure that every user has a sending frequency specified. The sending frequency is specified by a SEND AT or SEND EVERY option in the GRANT REMOTE statement (Adaptive Server Anywhere) or sp_grant_remote procedure (Adaptive Server Enterprise). 2.
Chapter 10. SQL Remote Administration enable recovery of data from system or media failure. In a replication installation, even if such recovery is achieved, the recovered database can be out of synch with remote databases. This can require a complete resynchronization of remote databases, which can be a formidable task if the installation involves large numbers of databases.
settings that need to be the same. ♦ Maximum message length The maximum message length for SQL Remote messages has a default value of 50K. This is configurable, using the Message Agent -l option. However, the maximum message length must be the same for each Message Agent in the installation, and may be restricted by operating system memory allocation limits. Received messages that are longer than the limit are deleted as corrupt messages. ☞ For details of this setting, see “The Message Agent” on page 292.
Chapter 10. SQL Remote Administration Execute the following command against the remote database: SET REMOTE link-name OPTION PUBLIC.OUTPUT_LOG_SEND_LIMIT = ’nnn’ The value of this option is the number of bytes at the tail of the output log (that is, the most recent entries) which are sent to the consolidated site. You can use nnn K to indicate kilobytes. The default setting is ’5K’.
Tuning Message Agent performance Who needs to read this section? If performance is not a problem at your site, you do not need to read this section. There are several options you can use to tune the performance of the Message Agent. This section describes those options. Sending messages and receiving messages are two separate processes. The major performance issues for these two processes are different.
Chapter 10. SQL Remote Administration What messages are applied in parallel When worker threads are being used, messages from different remote users are applied in parallel. Messages from a single remote user are applied serially. For example, ten messages from a single remote user will be applied by a single worker thread in the correct order. Deadlock is handled by re-applying the rolled back transaction at a later time. Reading messages from the message system is single-threaded.
messages are flushed in a least-recently-used fashion. This option is provided primarily for customers considering a single consolidated database for thousands of remote databases. Tuning incoming message polling When running a Message Agent in continuous mode, typically at a consolidated database site, you can control how often it polls for incoming messages, and how “patient” it is in waiting for messages that arrive out of order before requesting that the message be resent.
Chapter 10. SQL Remote Administration Setting a very small interval may have some detrimental impact on overall system throughput, for the following reasons: ♦ Each poll of the mail server (if you are using e-mail) places a load on your message system. Too-frequent polling may affect your message system and produce no benefits.
In the following sequence of operations, messages are marked as userX.n so that user1.5 is the sixth message from user1. The Message Agent expects messages to start at number 1 for both users. At time 0 seconds: 1. The Message Agent reads user1.1, user2.4 2. The Message Agent applies user1.1 3. The Message Agent patience is now user1: N/A, user2: 3, as an out of sequence message has arrived from user 2. At time 30 seconds: 1. The Message Agent reads: no new messages 2. The Message Agent applies: none 3.
Chapter 10. SQL Remote Administration Issues to consider The issues to consider when tuning the message-sending process are similar to those when tuning the incoming-message polling frequency: ♦ Regular messages Your choices dictate how often updates are sent to remote databases. ♦ Resend requests When a remote user requests that a message be resent, the Message Agent needs to take special action that can interrupt regular message sending.
to retrieve this message and send it causes the Message Agent to interrupt the regular sending process. If you are tuning your SQL Remote installation for optimum performance, you must balance the urgency of sending requests for resent messages with the priority of processing regular messages. The -ru option controls the urgency of the resend requests. The value for the parameter is a time in minutes (or in other units if you add s or h to the end of the number), with a default of zero.
Chapter 10. SQL Remote Administration Encoding and compressing messages As messages pass through e-mail and other message systems, there is a danger of them becoming corrupted. For example, some message systems use certain characters or character combinations as control characters. Message size affects the efficiency with which messages pass through a system. Compressed messages can be processed more efficiently by a message system than uncompressed messages.
♦ Some message systems, including SMTP, VIM, and MAPI, require text-based message formats. For these systems, an encoding DLL (dbencod.dll for Adaptive Server Anywhere and ssencod.dll for Adaptive Server Enterprise) translates messages into a text format before sending. The message format is unencoded at the receiving end using the same DLL. ♦ You can instruct SQL Remote to use a custom encoding scheme. The tools for building a custom encoding scheme are described in the following section.
Chapter 10. SQL Remote Administration The message tracking system SQL Remote has a message tracking system to ensure that all replicated operations are applied in the correct order, no operations are missed, and no operation is applied twice. Message system failures may lead to replication messages not reaching their destination, or reaching it in a corrupt state. Also, messages may arrive at their destination in a different order from that in which they were sent.
serial number within the transaction to order the messages correctly. The default maximum message size is 50,000 bytes, but you can use the Message Agent -l option to change this setting. Sending messages The log_sent column holds the local transaction log offset for the latest message sent to the subscriber. When the Message Agent sends a message, it sets the log_sent value to the offset of the last COMMIT in the message.
Chapter 10. SQL Remote Administration Handling of lost or corrupt messages When messages are received at a subscriber database, the Message Agent applies them in the correct order (determined from the log offsets) and sends confirmation to the publisher. If a message is missing, the Message Agent increments the local value of rereceive_count, and requests that it be resent. Other messages present or en route are not applied.
CHAPTER 11 Administering SQL Remote for Adaptive Server Anywhere About this chapter Contents This chapter details set-up, and management issues for SQL Remote administrators using Adaptive Server Anywhere as a consolidated database.
Running the Message Agent This section describes how to run the Message Agent for Adaptive Server Anywhere. ☞ For information on features of the Message Agent that are common to Adaptive Server Anywhere and Adaptive Server Enterprise, see “SQL Remote Administration” on page 199. Starting the Message Agent The Message Agent has a set of options that control its behavior. The only option that is required for the Message Agent to run is the connection parameters option (-c).
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere The Message Agent and replication security In the tutorials in the previous chapter, the Message Agent was run using a user ID with DBA permissions. The operations in the messages are carried out from the user ID specified in the Message Agent connection string; by using the user ID DBA, you can be sure that the user has permissions to make all the changes.
In Adaptive Server Anywhere, you can add the REMOTE DBA authority to a remote user by checking the appropriate option on the Authorities tab of the remote user’s property sheet.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere Error reporting and handling This section describes how errors are reported and handled by the Message Agent. Default error handling The default action taken by the Message Agent when an error occurs is to record the fact in its log output. The Message Agent sends log output to a window or a log file recording its operation. By default, log output is sent to the window only; the -o option sends output to a log file as well.
Implementing error handling procedures SQL Remote allows you to carry out some other process in addition to logging a message if an error occurs. The Replication_error database option allows you to specify a stored procedure to be called by the Message Agent when an error occurs. By default no procedure is called. The procedure must have a single argument of type CHAR, VARCHAR, or LONG VARCHAR. The procedure is called twice: once with the error message and once with the SQL statement that causes the error.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere // Log the error INSERT INTO cons.replication_audit ( remoteuser, errormsg) VALUES ( current_remote_user, error_text); COMMIT WORK; //Now notify the DBA an error has occurred // using email.
Column Description pub Current publisher of the database (lets you know at what database it was inserted) remoteuser Remote user applying the message (lets you know what database it came from) errormsg Error message passed to the Replication_error procedure Here is a sample insert into the table from the above error: INSERT INTO cons.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere Transaction log and backup management The importance of good backup practices Replication depends on access to operations in the transaction log, and access to old transaction logs is sometimes required. This section describes how to set up backup procedures at the consolidated and remote databases to ensure proper access to old transaction logs. It is crucial to have good backup practices at SQL Remote consolidated database sites.
Log names are not important The Message Agent opens all the files in the transaction log directory to determine which files are logs, so the actual names of the log files are not important. This section describes how you can set up a backup procedure to ensure that such a directory is kept in proper shape. Backup utility options The Adaptive Server Anywhere backup utility has several options, accessible through Sybase Central wizard selections or through dbbackup options, that control its behavior.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere 1. Backs up the transaction log file, creating a backup file c:\archive\consol.log. 2. Renames the existing transaction log file to 971201 xx.log, where xx are sequential characters ranging from AA to ZZ. 3. Starts a new transaction log, as consol.log. After several backups, the live directory contains a set of sequential transaction logs. 971201AA.log 971201AB.log consol.db 971201AC.log consol.log consol.log consol.
971201AA.log 971201AB.log consol.db consol.log A backup command line The following command line backs up the database using the rename and restart option, and also uses an option to rename the transaction log backup file: dbbackup -r -k -c "uid=DBA;pwd=SQL" c:\archive The connection string options would be different for each database.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere Old log names different before 8.0.1 Prior to release 8.0.1 of Adaptive Server Anywhere, the old log files were named yymmdd01.log, yymmdd02.log, and so on. The name change was introduced to allow more old logs to be stored. As the Message Agent scans all the files in the specified directory, regardless of their names, the name change should not affect existing applications.
Recovery with a single transaction log In this case, we assume that there is a single transaction log file, which has existed since the database was created. Also, we assume previous backups of the database file have been made and are available, for example on tape. ❖ To recover the database 1. Make a copy of the database and log file. 2. Restore the database (.db ) file, not the log file, from tape into a temporary directory. 3.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere Example This example illustrates recovery using a mirrored transaction log. Suppose you have a consolidated database file named consol.db in a directory c:\dbdir, and a transaction log file c:\logdir\consol.log which is mirrored to d:\mirdir\consol.mlg. ❖ To recover from media failure on the C drive 1. Backup the mirrored transaction log d:\mirdir\consol.mlg. 2. Replace the failed hardware and re-install all affected software. 3.
❖ To recover the database 1. Make a copy of the database and log file. 2. Restore the database (.db ) file, not the log file, from tape into a temporary directory. 3. In the temporary directory, start the database, applying the old logs using the -a option, applying the named transaction logs in the correct order. 4. Start the database using the current transaction log and the -a option, to apply the transactions and bring the database file up to date. 5. Start the database in your normal way.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere 4. Apply the renamed transaction logs in order, as follows dbeng9 -a C:\DBDIR\date00.LOG C:\RECOVER\CONS.DB dbeng9 -a C:\DBDIR\date01.LOG C:\RECOVER\CONS.DB 5. Copy the current transaction log, c:\dbdir\cons.log to the recovery directory, giving c:\recover\cons.log. 6. Start the database using the following command: dbeng9 C:\RECOVER\CONS.DB 7. Shutdown the database server. 8.
Upgrading consolidated databases This section describes issues in upgrading a consolidated database in a SQL Remote environment. The same considerations apply to Adaptive Server Anywhere databases that are primary sites in a Sybase Replication Server installation. Installing new software does not always make new features available. In many cases, new features require the Upgrade utility to be run on databases.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere ❖ To unload and reload a consolidated database (manual) 1. Shut down the existing database. 2. Perform a full off-line backup by copying the database and transaction log files to a secure location. 3. Run the dbtran utility to display the starting offset and ending offset of the database’s current transaction log file. Note the ending offset for later use. 4.
Using passthrough mode The publisher of the consolidated database can directly intervene at remote sites using a passthrough mode, which enables standard SQL statements to be passed through to a remote site. By default, passthrough mode statements are executed at the local (consolidated) database as well, but an optional keyword prevents the statements from being executed locally. Caution Always test your passthrough operations on a test database with a remote database subscribed.
Chapter 11. Administering SQL Remote for Adaptive Server Anywhere messages, in the order in which the statements are recorded in the log. Passthrough is commonly used to send data definition language statements. In this case, replicated DML statements use the before schema before the passthrough and the after schema following the passthrough. Notes on using passthrough mode ♦ You should always test your passthrough operations on a test database with a remote database subscribed.
♦ Resynchronize users. ♦ Drop users from the setup. ♦ Change the address, message type, or frequency for a remote user. ♦ Add a column to a table. Many other schema changes are likely to cause serious problems if executed on a running SQL Remote setup. Passthrough works on only one level of a hierarchy In a multi-tier SQL Remote installation, it becomes important that passthrough statements work on the level of databases immediately beneath the current level.
CHAPTER 12 Administering SQL Remote for Adaptive Server Enterprise About this chapter Contents This chapter details setup and management issues for SQL Remote administrators using Adaptive Server Enterprise as the server for the consolidated database.
How the Message Agent for Adaptive Server Enterprise works This section describes how the Message Agent for Adaptive Server Enterprise works. There are some significant differences between how the Message Agent for Adaptive Server Enterprise and the Message Agent for Adaptive Server Anywhere operate, which accommodate the different roles of the two servers.
Chapter 12. Administering SQL Remote for Adaptive Server Enterprise Message Agent must be run to reclaim log space The Message Agent must be run frequently enough to prevent the transaction log from running out of space. The dump transaction command does not reclaim space from pages after the truncation point. Replication Server and SQL Remote Using SQL Remote on an Adaptive Server Enterprise database participating in a Replication Server setup may involve other considerations.
Do not modify the stable queue directly The stable queue is maintained by and for the Message Agent. You should not modify the stable queue directly. The stable queue consists of a set of tables that contain information on all transactions scanned from the transaction log, ☞ For a description of each of the columns of these tables, see “Stable Queue tables” on page 350.
Chapter 12. Administering SQL Remote for Adaptive Server Enterprise Stable queue Transaction log Message Agent ♦ Sending messages During this phase, the Message Agent builds outgoing messages from the stable queue. Message system Stable queue Message Agent The transactions remain in the stable queue until confirmation has been received from all remote databases.
Message Agent. The Message Agent does not scan the transaction log of the database in which the stable queue resides if it is different from the database with SQL Remote system tables. ☞ For information on running multiple copies of the Message Agent to carry out these tasks, see “Running multiple Message Agents” on page 269.
Chapter 12. Administering SQL Remote for Adaptive Server Enterprise Running the Message Agent This section describes how to run the Message Agent for Adaptive Server Enterprise. For information on features of the Message Agent that are common to Adaptive Server Anywhere and Adaptive Server Enterprise, see “Running the Message Agent” on page 223. The Message Agent and replication security In the tutorials earlier in this book, the Message Agent was run using a user ID with system administrator permissions.
You may wish to run separate copies of the Message Agent to carry out these different phases. You can specify which phases a given Message Agent is to execute on the Message Agent command line. Specifying which phases to execute The command-line options are as follows: ♦ Receive The -r command-line option instructs the Message Agent to receive messages while it is running. To cause the Message Agent to shut down after receiving available messages, use the -b option in addition to -r.
Chapter 12. Administering SQL Remote for Adaptive Server Enterprise Error reporting and handling This section describes how errors are reported and handled by the Message Agent. Default error handling The default action taken by the Message Agent when an error occurs is to record the fact in its log output. The Message Agent sends log output to a window or a log file recording its operation. By default, log output is sent to the window only; the -o command-line option sends output to a log file as well.
Adaptive Server Enterprise transaction log and backup management You must protect against losing transactions that have been replicated to remote databases. If transactions are lost that have already been replicated to remote databases, the remote databases will be inconsistent with the consolidated database. In this situation, you may have to re-extract all remote databases.
Chapter 12. Administering SQL Remote for Adaptive Server Enterprise System Administration Guide. Replicating only backed-up transactions The Message Agent also provides a command-line option (-u) that only sends transactions that have been backup up. In Adaptive Server Enterprise, this means transactions complete before the latest dump database command or dump transaction command.
interface is being used, it sets a truncation point in the transaction log. The truncation point prevents Adaptive Server Enterprise from re-using pages in the transaction log before they have been scanned by SSREMOTE. For this reason, DUMP TRANSACTION will not necessarily release transaction log pages that are before the oldest open transaction. DUMP TRANSACTION will not release transaction log pages beyond the “truncation point”. Initializing the truncation point The SQL Remote setup script (ssremote.
Chapter 12. Administering SQL Remote for Adaptive Server Enterprise Making schema changes Schema changes to tables being replicated by SQL Remote must be made on a quiet system. A quiet system means the following: ♦ No transactions being replicated There can be no transactions being replicated that modify the tables that are to be altered. All transactions that modify tables being altered must be scanned from the transaction log into the stable queue before the schema is altered.
Using passthrough mode The publisher of the consolidated database can directly intervene at remote sites using a passthrough mode, which enables standard SQL statements to be passed through to a remote site. Determining recipients of Passthrough destinations are determined by sp_passthrough_user and passthrough statements sp_passthrough_subscription. Executing either of these procedures determines a set of recipients for any subsequent passthrough statements.
CHAPTER 13 Using SQL Remote with Replication Server About this chapter Contents This chapter describes the additional components needed to use SQL Remote on an Adaptive Server Enterprise database that also participates in a Replication Server installation.
When you need to use the SQL Remote Open Server The Message Agent for Adaptive Server Enterprise scans the Adaptive Server Enterprise transaction log to populate the stable queue, as described in the section “The stable queue” on page 265). SQL Remote messages are built from the transactions in the stable queue. The Message Agent uses the same interface to scan the transaction log as the Replication Agent for Adaptive Server Enterprise.
Chapter 13. Using SQL Remote with Replication Server Architecture for Replication Server/SQL Remote installations The arrangement for using a database as a Replication Server primary site and as a SQL Remote database is illustrated in the following diagram. The diagram illustrates a case where the stable queue is held in a different database from the data being replicated. The stable queue may alternatively be held in the same database as the data being replicated.
table. These transactions are used by the Message Agent to build SQL Remote messages. Message system Stable queue Message Agent Incoming messages 280 The Message Agent always applies incoming SQL Remote messages directly to Adaptive Server Enterprise. It does not send operations to Replication Server. Incoming messages are applied directly to the consolidated database regardless of how the stable queue is populated. Conflict resolution is also performed in the same way.
Chapter 13. Using SQL Remote with Replication Server Message system Message Agent Replication Server and SQL Remote SQL Remote allows two-way replication between the consolidated database and remote databases. Replication Server is performing one-way replication from the consolidated database to the SQL Remote Open Server. From Replication Server’s perspective, transactions that originate in remote SQL Remote databases appear as transactions originating in the consolidated SQL Remote database.
Setting up SQL Remote Open Server This section describes how to set up a SQL Remote installation using the SQL Remote Open Server. The procedure depends on whether the SQL Remote stable queue is being kept in a separate Adaptive Server Enterprise database from the tables being replicated or in the same Adaptive Server Enterprise database. ☞ For more information about stable queue location, see “The stable queue” on page 265.
Chapter 13. Using SQL Remote with Replication Server ❖ To prepare your SQL Remote setup, with no existing installation 1. Set up SQL Remote as described in “Setting Up SQL Remote” on page 19. 2. Set up your SQL Remote publications and subscriptions at this point. For information on this procedure, see “SQL Remote Design for Adaptive Server Enterprise” on page 141. 3. Extract the remote databases. For information on this procedure, see “Using the extraction utility” on page 191.
☞ For information on creating replication definitions with no materialization, see the Replication Server Administration Guide. The section in Chapter 10, Managing Subscriptions entitled Bulk Materialization describes how to set up Replication Server for the case where data exists at a remote database. 6. Define, activate, and validate replication definitions and subscriptions for the tables in your database that need to be replicated by SQL Remote. These must be created without materialization.
Chapter 13. Using SQL Remote with Replication Server Configuring Replication Server This section describes how to configure Replication Server for use with the SQL Remote Open Server The Replication Server connection to the SQL Remote Open Server must have several configuration parameters set. Set the dsi_xact_group_size parameter By default, Replication Server groups multiple transactions into larger transactions. The dsi_xact_group_size parameter controls the maximum size of a grouped transaction.
prevent every column from being listed in the SET clause of the update. Text and image columns Replication Server does not accept TEXT or IMAGE columns in the primary key of a replication definition. You should include all the columns except for the TEXT and IMAGE columns in the PRIMARY KEY list of your replication definition, and specify all the TEXT and IMAGE columns in the ALWAYS_REPLICATE clause. You should use REPLICATE ALL COLUMNS, instead of REPLICATE MINIMAL COLUMNS in your replication definition.
Chapter 13. Using SQL Remote with Replication Server Other issues This section lists other issues regarding using SQL Remote with Replication Server. Running the Message Agent The Message Agent should be run with command-line options to receive and send (-r and -s). This will prevent the Message Agent from attempting to scan the transaction log.
288
PART IV R EFERENCE This part presents reference material for SQL Remote.
CHAPTER 14 Utilities and Options Reference About this chapter This chapter provides reference material for the SQL Remote utilities and SQL Remote database options. It also describes client event-hook stored procedures, which can be used to customize the replication process.
The Message Agent Purpose To send and apply SQL Remote messages, and to maintain the message tracking system to ensure message delivery. Syntax { dbremote | ssremote } [ options ] [ directory ] Options 292 Option Description @data Read options from the specified environment variable or configuration file –a Do not apply received transactions –b Run in batch mode –c ”keyword=value ; ...” Supply database connection parameters –cq ”keyword=value ; ...
Chapter 14. Utilities and Options Reference Description Option Description –ot file Truncate file and log output messages –p Do not purge messages –q Run with minimized window –r Receive messages –rd minutes Polling frequency for incoming messages -ro filename Log remote output to file –rp number Number of receive polls before message is assumed lost -rt filename Truncate, and log remote output to file. –ru time Waiting period to re-scan log on receipt of a resend.
Deprecated feature In the next major release of SQL Anywhere Studio, SQL Remote for Adaptive Server Enterprise will not be present. MobiLink provides a more flexible and scalable solution for data synchronization between Adaptive Server Enterprise and Adaptive Server Anywhere databases. You can also run the Message Agent from your own application by calling into the DBTools library. For more information, see the file dbrmt.h in the h subdirectory of your SQL Remote installation directory.
Chapter 14. Utilities and Options Reference ssremote @c:\config.txt or dbremote @c:\config.txt Process the received messages (those in the inbox) without applying them to the database. Used together with -v (for verbose output) and -p (so the messages are not purged), this option can help detect problems with incoming messages. Used without -p, this option purges the inbox without applying the messages, which may be useful if a subscription is being restarted. –a Run in batch mode.
Specify connection parameters for the stable queue. This option applies to Adaptive Server Enterprise only. If not supplied, the values default to the -c values. –cq “parameter=value; . . . ” Display messages in the Message Agent window or at the command prompt and also in the log file if specified. –dl This option allows you to specify the encryption key for strongly encrypted databases directly at the command prompt.
Chapter 14. Utilities and Options Reference than n operations together with transactions that follow. The default is twenty operations. Increasing the value of n can speed up processing of incoming messages, by doing less commits. However, it can also cause deadlock and blocking by increasing the size of transactions. –I Scan transactions from the transaction log into the stable queue. This option is available for Adaptive Server Enterprise only.
This option makes it possible for dbremote to delete old mirror log files when either of the following two circumstances occur: -ml ♦ the offline mirror log is located in a different directory from the mirror transaction log ♦ dbremote is run on a different machine from the remote database server In a typical setup, the active mirror log and renamed mirror transaction logs are located in the same directory, and dbremote is run on the same machine as the remote database, so this option is not required and o
Chapter 14. Utilities and Options Reference dbremote -rd 30s polls every thirty seconds. ☞ For more information on polling, see “Tuning incoming message polling” on page 230. This option is for use at consolidated sites. When remote databases are configured to send output log information to the consolidated database, this option writes the information to a file. The option is provided to help administrators troubleshoot errors at remote sites.
only. Process only transactions that have been backed up. This option prevents the Message Agent from processing transactions since the latest backup. Using this option, outgoing transactions and confirmation of incoming transactions are not sent until they have been backed up. –u In Adaptive Server Anywhere, this means only transactions from renamed logs are processed.
Chapter 14. Utilities and Options Reference outgoing messages. In some circumstances, replicating data to a consolidated database can take the place of backing up remote databases, or renaming the transaction log when the database server is shut down. This option is available for Adaptive Server Anywhere only. If the optional size qualifier is supplied, the transaction log is renamed only if it is larger than the specified size. The allowed size can be specified as n (in bytes), n K, or n M.
The Database Extraction utility You can access the remote database extraction utility in the following ways: ♦ From Sybase Central, for interactive use. ♦ From the system command prompt, using the ssxtract or dbxtract utilities. This is useful for incorporating into batch or command files. ssxtract is the extraction utility for Adaptive Server Enterprise, dbxtract is the extraction utility for Adaptive Server Anywhere. By default, the extraction utility runs at isolation level zero.
Chapter 14. Utilities and Options Reference Note Only tables for users selected in the Filter Objects by Owner dialog appear in the Extract Database wizard. If you wish to view tables belonging to a particular database user, right-click the database you are unloading, choose Filter Objects by Owner from the popup menu, and then select the desired user in the resulting dialog. ❖ To extract a remote database from a running database (Sybase Central) 1. Connect to the database. 2.
304 Option Description –c ”keyword=value ; ...
Chapter 14. Utilities and Options Reference Description Option Description –xv Exclude views –xx External unload, external load –y Overwrite command file without confirmation directory The directory to which the files are written. This is not needed if you use -an or -ac subscriber The subscriber for whom the database is to be extracted. ssxtract is the extraction utility for Adaptive Server Enterprise.
Server Enterprise database. ♦ Passwords The password for the extracted user IDs are the same as the user ID itself. ♦ Permissions The extracted user ID is granted REMOTE DBA authority. ♦ Named constraints These are extracted as Adaptive Server Anywhere CHECK constraints. ♦ System tables The sp_populate_sql_anywhere SQL Remote procedure builds a set of Adaptive Server Anywhere system tables in TEMPDB from the Adaptive Server Enterprise system tables.
Chapter 14. Utilities and Options Reference You can combine the operation of unloading a database and reloading the results into an existing database using this option. Reload the data to an existing database (–ac) For example, the following command (which should be entered all on one line) loads a copy of the data for the field_user subscriber into an existing database file named newdemo.db : dbxtract -c "uid=dba;pwd=sql;dbf=asademo.db" -ac "uid=dba;pwd=sql;dbf=newdemo.
Parameter Description UID Login ID PWD Password DBN (optional) Database name. If this parameter is not supplied, the connection defaults to the default database for the login ID. ENG Adaptive Server Enterprise name. ssxtract cannot extract passwords. It sets passwords to be the same as the user ID. Unload the data only (–d) If this option is selected, the schema definition is not unloaded, and publications and subscriptions are not created at the remote database.
Chapter 14. Utilities and Options Reference Separately licensable option required Strong database encryption using AES_FIPS requires that you obtain the separately-licensable SQL Anywhere Studio security option and is subject to export regulations. ☞ To order this component, see “Separately-licensable components” [Introducing SQL Anywhere Studio, page 5]. This option allows you to create a strongly encrypted database by specifying an encryption key directly in the command.
than the Interactive SQL OUTPUT and INPUT statements to unload and load data, respectively. This combination of operations is the default behavior. External operations takes the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the server. Using this option forces the reload script to use the internal UNLOAD statement to unload data, and the Interactive SQL INPUT statement to load the data into the new database.
Chapter 14. Utilities and Options Reference By default the data in each table is ordered by primary key. Unloads are quicker with the -u option, but loading the data into the remote database is slower. Output the data unordered (–u) The name of the table being unloaded and the number of rows unloaded are displayed. The SELECT statement used is also displayed.
The SQL Remote Open Server Purpose To take replication data from Replication Server and apply it to the SQL Remote stable queue. This utility is needed only for databases participating in both Replication Server (and using a Replication Agent) and SQL Remote replication. Deprecated feature In the next major release of SQL Anywhere Studio, SQL Remote for Adaptive Server Enterprise will not be present.
Chapter 14. Utilities and Options Reference The name of the executable is as follows: ♦ ssqueue.exe Windows operating systems. ♦ ssqueue UNIX operating systems. Option details Replication Server must connect to the SQL Remote Open Server, which therefore must have an open server name.
SQL Remote renames the output file to yymmddxx.dbr (for dbremote) and yymmddxx.ss r (for ssremote) where xx are sequential characters ranging from AA to ZZ, and yymmdd represents the current year, month, and date. If the Message Agent us running in continuous mode for a long time, this option allows you to manually delete old log files and free up disk space. Truncate the log file and then append output messages to it. Default is to send output to the screen.
Chapter 14. Utilities and Options Reference SQL Remote options Function Replication options are database options included to provide control over replication behavior. Adaptive Server Anywhere Syntax SET [ TEMPORARY ] OPTION [ userid. | PUBLIC. ]option_name = [ option_value ] Adaptive Server Enterprise syntax: Parameters exec sp_remote_option option-name, option-value Description Argument Description option_name The name of the option being changed.
ID specified in the Message Agent command. They can also be set for general public use. The options are as follows: Any value longer than the Blob_threshold option is replicated as a blob. That is, it is broken into pieces and replicated in chunks, before being reconstituted by using a SQL variable and concatenating the pieces at the recipient site.
Chapter 14. Utilities and Options Reference not needed in Adaptive Server Anywhere setups, messages will be slightly smaller with the option off. Quote_all_identifiers option Controls whether SQL statements being replicated by SQL Remote should use quoted identifiers. The default is OFF. When this option is off, the dbremote quotes identifiers that require quotes by Adaptive Server Anywhere (as it has always done) and ssremote does not quote any identifiers.
are also upper case. For the digit formats, the case of the option setting controls padding. If the symbols are the same case (such as DD), the number is padded with zeroes. If the symbols are mixed case (such as Mm), the number is not zero padded. SR_Time_Format option The Message Agent uses this option when replicating columns that store a time.
Chapter 14. Utilities and Options Reference relationships” on page 164. If the data type of a column is longer than the threshold, old values for the column are not verified when an UPDATE is replicated. The default setting is 1000. Verify_threshold option This option keeps the size of SQL Remote messages down, but has the disadvantage that conflicting updates of long values are not detected. Verify_all_columns option The default setting is OFF.
SQL Remote event-hook procedures The following stored procedure names and arguments provide the interface for customizing synchronization at SQL Remote databases. Notes Unless otherwise stated, the following apply to event-hook procedures: ♦ The stored procedures must either have DBA authority ( Adaptive Server Anywhere ) or dbo authority (Adaptive Server Enterprise). ♦ The procedure must not commit or rollback operations, or perform any action that performs an implicit commit.
Chapter 14. Utilities and Options Reference Agent exits. Rows in #hook_dict table Description Name Values Description send true or false Indicates if the process is performing the send phase of replication. receive true or false Indicates if the process is performing the receive phase of replication exit code integer A non-zero exit code indicates an error. If a procedure of this name exists, it is called as the last event before the Message Agent shuts down.
Rows in #hook_dict None sp_hook_dbremote_send_begin and sp_hook_ssrmt_send_begin Function Use this stored procedure to perform actions before the start of the send phase of replication. Rows in #hook_dict None sp_hook_dbremote_send_end and sp_hook_ssrmt_send_end Function Use this stored procedure to perform actions after the end of the send phase of replication.
Chapter 14. Utilities and Options Reference Name Values remote user The name of the remote user who sent the messages about to be applied. sp_hook_dbremote_message_apply_end and sp_hook_ssrmt_message _apply_end Function Use this stored procedure to perform actions just after the Message Agent has applied a set of messages from a user. Rows in #hook_dict Name Values remote user The name of the remote user who sent the messages that have been applied.
CHAPTER 15 System Objects for Adaptive Server Anywhere About this chapter Contents SQL Remote-specific system information is held in the Adaptive Server Anywhere catalog. A more comprehensible version of this information is held in a set of system views.
SQL Remote system tables This section describes the system tables used by SQL Remote to define and manage SQL Remote information. In the following diagram, arrows indicate foreign key relations between tables: the arrow leads from the foreign table to the primary table.
Chapter 15. System Objects for Adaptive Server Anywhere Columns Column Data type Description publication_id UNSIGNED INT The publication of which this article is a part. table_id UNSIGNED INT Each article consists of columns and rows from a single table. This column contains the table ID for this table. where_expr LONG VARCHAR For articles that contain a subset of rows defined by a WHERE clause, this column contains the search condition.
SYSPUBLICATION table Function Each row describes a SQL Remote publication. Columns Column Data type Description publication_id UNSIGNED INT A unique identifier for the publication creator UNSIGNED INT The user ID that owns the publication publication_name VARCHAR(128) The name of the publication remarks LONG VARCHAR Comments SYSREMOTEOPTION table Function Each row describes the values of a SQL Remote message link parameter.
Chapter 15. System Objects for Adaptive Server Anywhere Column Data type Description option_id UNSIGNED INT An identification number for the message link parameter. type_id SMALLINT An identification number for the message type that uses this parameter "option" VARCHAR(128) The name of the message link parameter. SYSREMOTETYPE table Function Each row describes one of the SQL Remote message types, including the publisher address.
330 Column Data type Description user_id UNSIGNED INT The user ID of the user with REMOTE permissions. consolidate CHAR(1) The column contains either an N to indicate a user granted REMOTE permissions, or a Y to indicate a user granted CONSOLIDATE permissions. type_id SMALLINT The ID of the message system used to send messages to this user. address LONG VARCHAR The address to which SQL Remote messages are to be sent. The address must be appropriate for the address_type.
Chapter 15. System Objects for Adaptive Server Anywhere Column Data type Description resend_count INT Counter to ensure messages are applied only once at the subscriber database. time_received DATETIME The time the most recent message was received from this subscriber. log_received NUMERIC(20, 0) The log offset in the subscriber’s database for the operation most recently received at the current database.
332 Column Data type Description publication_id UNSIGNED INT The identifier for the publication to which the user ID is subscribed. user_id UNSIGNED INT The user ID that is subscribed to the publication. subscribe_by VARCHAR(128) For publications with a SUBSCRIBE BY expression, this column holds the matching value for this subscription. created NUMERIC(20, 0) The offset in the transaction log at which the subscription was created.
Chapter 15. System Objects for Adaptive Server Anywhere SQL Remote system views This section describes the database views used by SQL Remote to present and summarize SQL Remote information. SYSARTICLES view Function Each row lists describes an article. Columns Column Description publication_name The publication of which this article is a part. table_name Each article consists of columns and rows from a single table. This column contains the name of this table.
Column Description publication_name The name of the publication creator The owner of the publication remarks Comments SYSREMOTEOPTIONS view Function Lists the SQL Remote message link parameters and their values, as stored in the SYSREMOTEOPTION and SYSREMOTEOPTIONTYPE system tables, in more readable form. Columns Column Description type_name The message link type. "option" The option name. setting The option value.
Chapter 15. System Objects for Adaptive Server Anywhere Column Description log_send Messages are sent only to subscribers for whom log_send is greater than log_sent. time_sent The time the most recent message was sent to this subscriber. log_sent The transaction log offset for the most recently sent operation. confirm_sent The transaction log offset for the most recently confirmed operation from this subscriber. send_count How many SQL Remote messages have been sent.
336 Column Description publication_name The name of the publication to which the user ID is subscribed. user_name The user ID that is subscribed to the publication. subscribe_by For publications with a SUBSCRIBE BY expression, this column holds the matching value for this subscription. created The offset in the transaction log at which the subscription was created. started The offset in the transaction log at which the subscription was started.
CHAPTER 16 System Objects for Adaptive Server Enterprise About this chapter Contents SQL Remote-specific system information is held in a set of tables called the SQL Remote system tables. A more comprehensible version of this information is held in a set of views, called the SQL Remote system views.
SQL Remote system tables This section describes the database tables used by SQL Remote to define and manage SQL Remote information. Caution These tables are for use only by SQL Remote. Do not alter these tables or their contents directly. #remote table Function This temporary table is created by the Message Agent to hold the name of the current remote user and of the current publisher. This table exists only in Adaptive Server Enterprise.
Chapter 16. System Objects for Adaptive Server Enterprise Column Data type Description publication_id INT The publication of which this article is a part. table_id INT Each article consists of columns and rows from a single table. This column contains the table ID for this table. where_expr VARCHAR(128) For articles that contain a subset of rows defined by a WHERE clause, this column contains the search condition.
Description Column Data type Description marker DATETIME A time value indicating when the latest messages were applied. When a consolidated database uses two Message Agents, one to populate the stable queue (-I) and one to receive and send messages (-r -s), the single row of the sr_marker table is used to ensure that messages received and applied to the database are sent before the Message Agent closes down. sr_object table Function Holds a list of SQL Remote objects.
Chapter 16. System Objects for Adaptive Server Enterprise subscribers to a publication. Columns Column Data type Description operation VARCHAR(20) A passthrough operation, or piece of a passthrough operation, entered using sp_passthrough or sp_passthrough_piece. value VARCHAR(255) A subscription column value indicating which users are to receive the operation. id INT A user who is to receive the operation. sr_publication table Function Each row describes a SQL Remote publication.
Column Data type Description option_id INTEGER An identification number for the message link parameter. user_id INTEGER The user ID for which the parameter is set. "setting00" VARCHAR(255) The value of the message link parameter. sr_remoteoptiontype table Function Each row describes one of the SQL Remote message link parameters. Columns Column Data type Description option_id INTEGER An identification number for the message link parameter.
Chapter 16. System Objects for Adaptive Server Enterprise publisher address. Columns Column Data type Description type_id INT An identification number for the message type. type_name VARCHAR(128) The message type. There is a separate row for each of the following: ♦ FILE ♦ MAPI ♦ VIM ♦ SMTP publisher_address VARCHAR(128) The publisher’s address for the message type type_name.
344 Column Data type Description frequency CHAR(1) How frequently SQL Remote messages are to be sent. send_time DATETIME The next time messages are to be sent to this user. log_send NUMERIC(20, 0) Messages are sent only to subscribers for whom log_send is greater than log_sent. time_sent DATETIME The time the most recent message was sent to this subscriber. log_sent NUMERIC(20, 0) The log offset for the most recently sent operation.
Chapter 16. System Objects for Adaptive Server Enterprise Column Data type Description filler2 CHAR(255) Reserved filler3 CHAR(255) Reserved filler4 CHAR(255) Reserved sr_subscription table Function Each row describes a subscription from one user ID (which must have REMOTE permissions) to one publication. Columns Column Data type Description publication_id INT The identifier for the publication to which the user ID is subscribed.
SQL Remote system views This section describes the database views used by SQL Remote to present and summarize SQL Remote information. sr_articles view Function Each row lists describes an article. Columns Column Description publication_name The publication of which this article is a part. table_name Each article consists of columns and rows from a single table. This column contains the name of this table.
Chapter 16. System Objects for Adaptive Server Enterprise Columns Column Description publication_name The name of the publication sr_remoteoptions view Function Lists the SQL Remote message link parameters and their values, as stored in the remoteoption and remoteoptiontype system tables, in more readable form. Columns Column Description type_name The message link type. "option" The option name. setting The option value.
Column Description type_id An identification number for the message type. type_name The message type. There is a separate row for each of the following: ♦ FILE ♦ MAPI ♦ VIM ♦ SMTP publisher_address The publisher’s address for the message type type_name. sr_remoteusers view Function Lists information about remote users and their status. Columns 348 Column Description user_name The user ID of the user with REMOTE permissions.
Chapter 16. System Objects for Adaptive Server Enterprise Column Description log_sent The log offset for the most recently sent operation. confirm_sent The log offset for the most recently confirmed operation from this subscriber. send_count How many SQL Remote messages have been sent. resend_count Counter to ensure messages are applied only once at the subscriber database. time_received The time the most recent message was received from this subscriber.
Stable Queue tables This section describes the database tables used by SQL Remote to define and manage the stable queue information. The stable queue may be kept in the same database as the SQL Remote database, or in a separate database. The stable queue is used only by SQL Remote for Adaptive Server Enterprise. sr_queue_state table Function A single row table that stores persistent global information about the state of the stable queue.
Chapter 16. System Objects for Adaptive Server Enterprise Column Data type Description marker DATETIME The most recent incoming message that has been scanned into the stable queue. When a message is applied to the Adaptive Server Enterprise server, it sets the time_received column in sr_remoteuser. When the transaction log is scanned and the transactions from that message are scanned into the stable queue, it sets the time_received column of sr_queue_state.
Column Description offset The transaction log offset of the commit operation for the transaction. This value uniquely identifies each transaction user_id The remote user where the transaction originated. This column holds NULL if the transaction did not originate from a remote user. The user_id column is used to ensure that actions are not replicated back to the remote site that entered them. data The transaction itself, in an internal representation.
CHAPTER 17 Command Reference for Adaptive Server Anywhere About this chapter Contents This chapter describes the SQL statements used for executing SQL Remote commands, and the system tables, used for storing information about the SQL Remote installation and its state.
Topic: 354 page START SUBSCRIPTION statement 375 STOP SUBSCRIPTION statement 376 SYNCHRONIZE SUBSCRIPTION statement 377 UPDATE statement 378
Chapter 17. Command Reference for Adaptive Server Anywhere ALTER REMOTE MESSAGE TYPE statement Function Use this statement to change the publisher’s message system, or the publisher’s address for a given message system, for a message type that has been created. Syntax ALTER REMOTE MESSAGE TYPE message-system ADDRESS address message-system: FILE | FTP | MAPI | SMTP | VIM address: string Parameters Parameter Description messagesystem One of the message systems supported by SQL Remote.
CREATE PUBLICATION statement Function Use this statement to create a publication. In SQL Remote, publications identify replicated data in both consolidated and remote databases. Syntax CREATE PUBLICATION [ owner.]publication-name ( TABLE article-description, . . . ) owner , publication-name : identifier article-description: table-name [ ( column-name, . . .
Chapter 17. Command Reference for Adaptive Server Anywhere CREATE REMOTE MESSAGE TYPE statement Function Use this statement to identify a message-link and return address for outgoing messages from a database. Syntax CREATE REMOTE MESSAGE TYPE message-system ADDRESS address message-system: FILE | FTP | MAPI | SMTP | VIM address: string Parameters Parameter Description messagesystem One of the supported message systems. address The address for the specified message system.
CREATE SUBSCRIPTION statement Function Use this statement to create a subscription for a user to a publication. Syntax CREATE SUBSCRIPTION TO publication-name [ ( subscription-value ) ] FOR subscriber-id publication-name: identifier subscription-value, subscriber-id : string subscriber-id : string Parameters Parameter Description publication-name The name of the publication to which the user is being subscribed. This may include the owner of the publication.
Chapter 17. Command Reference for Adaptive Server Anywhere CREATE TRIGGER statement Function Use this statement to create a new trigger in the database. One form of trigger is designed specifically for use by SQL Remote. Syntax CREATE TRIGGER trigger-name trigger-time trigger-event, . . .
Usage Anywhere. Permissions Must have RESOURCE authority and have ALTER permissions on the table, or must have DBA authority. CREATE TRIGGER puts a table lock on the table and thus requires exclusive use of the table. Side effects Automatic commit.
Chapter 17. Command Reference for Adaptive Server Anywhere DROP PUBLICATION statement Function Use this statement to drop a publication. In SQL Remote, publications identify replicated data in both consolidated and remote databases. Syntax DROP PUBLICATION [ owner.
DROP REMOTE MESSAGE TYPE statement Function Use this statement to delete a message type definition from a database. Syntax DROP REMOTE MESSAGE TYPE message-system message-system: FILE | FTP | MAPI | SMTP | VIM Parameters Parameter Description message-system One of the message systems supported by SQL Remote. Permissions Must have DBA authority. To be able to drop the type, there must be no user granted REMOTE or CONSOLIDATE permissions with this type. Side effects Automatic commit.
Chapter 17. Command Reference for Adaptive Server Anywhere DROP SUBSCRIPTION statement Function Use this statement to drop a subscription for a user from a publication. Syntax DROP SUBSCRIPTION TO publication-name [ ( subscription-value ) ] FOR subscriber-id, . . . subscription-value: string subscriber-id: string Parameters Parameter Description publication-name The name of the publication to which the user is being subscribed. This may include the owner of the publication.
GRANT CONSOLIDATE statement Function Use this statement to identify the database immediately above the current database in a SQL Remote hierarchy, who will receive messages from the current database. Syntax GRANT CONSOLIDATE TO userid, . . . TYPE message-system, . . . ADDRESS address-string, . . .
Chapter 17. Command Reference for Adaptive Server Anywhere GRANT PUBLISH statement Function Use this statement to identify the publisher of the current database. Syntax GRANT PUBLISH TO userid Permissions Must have DBA authority. Side effects Automatic commit.
GRANT REMOTE statement Function Use this statement to identify a database immediately below the current database in a SQL Remote hierarchy, who will receive messages from the current database. These are called remote users. Syntax GRANT REMOTE TO userid, . . . TYPE message-system, . . . ADDRESS address-string, . . .
Chapter 17. Command Reference for Adaptive Server Anywhere GRANT REMOTE DBA statement Function Use this statement to provide DBA privileges to a user ID, but only when connected from the Message Agent. Syntax GRANT REMOTE DBA TO userid, . . . IDENTIFIED BY password Permissions Must have DBA authority. Side effects Automatic commit.
PASSTHROUGH statement Function Use this statement to start or stop passthrough mode for SQL Remote administration. Forms 1 and 2 start passthrough mode, while form 3 stops passthrough mode. Syntax 1 PASSTHROUGH [ ONLY ] FOR userid, . . . Syntax 2 PASSTHROUGH [ ONLY ] FOR SUBSCRIPTION TO [ ( owner ) ].publication-name [ ( constant ) ] Syntax 3 PASSTHROUGH STOP Permissions Must have DBA authority. Side effects None.
Chapter 17. Command Reference for Adaptive Server Anywhere REMOTE RESET statement Function Use this statement in custom database-extraction procedures to start all subscriptions for a remote user in a single transaction. Syntax REMOTE RESET userid Permissions Must have DBA authority. Side effects No automatic commit is done by this statement.
REVOKE CONSOLIDATE statement Function Use this statement to stop a consolidated database from receiving SQL Remote messages from this database. Syntax REVOKE CONSOLIDATE FROM userid, . . . Permissions Must have DBA authority. Side effects Automatic commit. Drops all subscriptions for the user.
Chapter 17. Command Reference for Adaptive Server Anywhere REVOKE PUBLISH statement Function Use this statement to terminate the identification of the named user ID as the CURRENT publisher. Syntax REVOKE PUBLISH FROM userid Permissions Must have DBA authority. Side effects Automatic commit.
REVOKE REMOTE statement Function Use this statement to stop a user from being able to receive SQL Remote messages from this database. Syntax REVOKE REMOTE FROM userid, . . . Permissions Must have DBA authority. Side effects Automatic commit. Drops all subscriptions for the user.
Chapter 17. Command Reference for Adaptive Server Anywhere REVOKE REMOTE DBA statement Function Use this statement to provide DBA privileges to a user ID, but only when connected from the Message Agent. Syntax 1 REVOKE REMOTE DBA FROM userid, . . . Permissions Must have DBA authority. Side effects Automatic commit.
SET REMOTE OPTION statement Function Use this statement to set a message control parameter for a SQL Remote message link. Syntax SET REMOTE link-name OPTION [ userid.| PUBLIC.
Chapter 17. Command Reference for Adaptive Server Anywhere START SUBSCRIPTION statement Function Use this statement to start a subscription for a user to a publication. Syntax START SUBSCRIPTION TO publication-name [ ( subscription-value ) ] FOR subscriber-id , . . . Parameters Parameter Description publication-name The name of the publication to which the user is being subscribed. This may include the owner of the publication.
STOP SUBSCRIPTION statement Function Use this statement to stop a subscription for a user to a publication. Syntax STOP SUBSCRIPTION TO publication-name [ ( subscription-value) ] FOR subscriber-id, . . . Parameters Parameter Description publication-name The name of the publication to which the user is being subscribed. This may include the owner of the publication. subscription-value A string that is compared to the subscription expression of the publication.
Chapter 17. Command Reference for Adaptive Server Anywhere SYNCHRONIZE SUBSCRIPTION statement Function Use this statement to synchronize a subscription for a user to a publication. Syntax SYNCHRONIZE SUBSCRIPTION TO publication-name [ ( subscription-value) ] FOR remote-user , . . . Parameters Parameter Description publication-name The name of the publication to which the user is being subscribed. This may include the owner of the publication.
UPDATE statement Function Use this statement to modify data in the database. Syntax 1 UPDATE table-list SET column-name = expression, . . . [ VERIFY ( column-name, . . . ) VALUES ( expression, . . . ) ] [ WHERE search-condition ] [ ORDER BY expression [ ASC | DESC ], . . .
CHAPTER 18 Command Reference for Adaptive Server Enterprise About this chapter Contents This chapter describes the SQL Remote stored procedures, used for executing SQL Remote commands.
Topic: 380 page 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 s
Chapter 18. Command Reference for Adaptive Server Enterprise sp_add_article procedure Purpose To add an article to a publication. Syntax sp_add_article publication_name, table_name, where_expr , subscribe_by_expr , subscribe_by_view Argument Description publication_name The name of the publication to which the article is to be added. table_name The table containing the article. where_expr This optional argument must be a column name or NULL.
you wish to include only some of the columns of the table in a publication you must first run sp_add_article and then call sp_add_article_col. As with other data definition changes, in a production environment this procedure should only be run on a quiet SQL Remote installation. ☞ For more information on the requirements for a quiet system, see “Making schema changes” on page 275.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_add_article_col procedure Purpose To add a column to an article in a publication. Syntax sp_add_article_col publication_name, table_name, column_name See also Argument Description publication_name The name of the publication to which the article is to be added. table_name The table containing the article.
sp_add_remote_table procedure Purpose To mark a table for SQL Remote replication. Syntax sp_add_remote_table table_name, [ resolve_procedure, ] [ old_row_name, ] [ remote_row_name ] Argument Description table_name The table to be marked for SQL Remote replication. resolve_procedure The name of a stored procedure that carries out actions when a conflict occurs. old_row_name The name of a table holding the values in the table when a conflict occurs.
Chapter 18. Command Reference for Adaptive Server Enterprise The two tables must have the same columns and data types as table table_name. Examples ♦ The following statement marks the Customer table for replication, using default conflict resolution: exec sp_add_remote_table Customer ♦ The following statement marks the Customer table for replication, using a stored procedure named Customer_Conflict to resolve conflicts.
sp_create_publication procedure Purpose To create a publication. Syntax sp_create_publication publication_name See also Argument Description publication_name The name of the publication “sp_drop_publication procedure” on page 387 “CREATE PUBLICATION statement” [ASA SQL Reference, page 385] Description Example Running sp_create_publication creates a publication, but one with no content.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_drop_publication procedure Purpose To drop a publication from the database. Syntax sp_drop_publication publication_name See also Argument Description publication_name The name of the publication to be dropped “sp_create_publication procedure” on page 386 “DROP PUBLICATION statement” [ASA SQL Reference, page 459] Description Example Running sp_drop_publication drops a publication from the database.
sp_drop_remote_type procedure Purpose To drop a message type from the database. Syntax sp_drop_remote_type type_name Argument Description type_name The message type to drop. This must be a string containing one of the following: ♦ file ♦ ftp ♦ smtp ♦ mapi ♦ vim See also “sp_remote_type procedure” on page 427 “DROP REMOTE MESSAGE TYPE statement” on page 362 Description Drops the named message type from the database.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_drop_sql_remote procedure Purpose To drop the SQL Remote system objects from a database. Syntax sp_drop_sql_remote See also “sp_queue_drop procedure” on page 414 Description Drops the SQL Remote system objects from the database, so that it can no longer function in a SQL Remote installation. The sole SQL Remote object not removed is the sp_drop_sql_remote procedure itself (a procedure cannot drop itself from a database).
sp_grant_consolidate procedure Purpose To identify a database immediately above the current database in a SQL Remote hierarchy, who will receive messages from the current database. This procedure applies only to Adaptive Server Enterprise databases acting as remote databases. Syntax sp_grant_consolidate user_name, type_name, address [, frequency ] [, send_time ] Argument Description user_name The user ID who will be able to receive SQL Remote messages. type_name The message type to be used.
Chapter 18. Command Reference for Adaptive Server Enterprise Argument Description send_time A string containing a time specification with the following meaning: ♦ If frequency is SEND EVERY, specifies a length of time between messages. ♦ If frequency is SEND AT, specifies a time of day at which messages will be sent. If no frequency is specified, the Message Agent sends messages, and then stops.
sp_grant_consolidate @user_name=hq_user, @address=hq_dir, @type_name=file go 392
Chapter 18. Command Reference for Adaptive Server Enterprise sp_grant_remote procedure Purpose To identify a database immediately below the current database in a SQL Remote hierarchy, who will receive messages from the current database. These are called remote users. Syntax sp_grant_remote user_name, type_name, address [, frequency ] [, send_time ] Argument Description user_name The user ID who will be able to receive SQL Remote messages. type_name The message type to be used.
Argument Description send_time An optional string containing a time specification with the following meaning: ♦ If frequency is SEND EVERY, specifies a length of time between messages. ♦ If frequency is SEND AT, specifies a time of day at which messages will be sent. If no frequency is specified, the Message Agent sends messages, and then stops.
Chapter 18.
sp_link_option procedure Purpose To set a message control parameter for a SQL Remote message link.
Chapter 18. Command Reference for Adaptive Server Enterprise sensitivity of passwords, directory names, and other strings depend on the cases sensitivity of the file system (for directory names), or the database (for user IDs and passwords). userid If no userid is specified, then the current publisher is assumed. The option values are message-link dependent. For more information, see the following locations: Option values ♦ “The file message system” on page 215. ♦ “The ftp message system” on page 216.
sp_modify_article procedure Purpose To change the description of an article in a procedure. Syntax sp_modify_article publication_name, table_name, [ where_expr , ] [ subscribe_by_expr ] [ subscribe_by_view ] Argument Description publication_name The name of the publication for which the article is to be modified. table_name The table containing the article. where_expr This optional argument must be a column name or NULL.
Chapter 18. Command Reference for Adaptive Server Enterprise ☞ For more information on the requirements for a quiet system, see “Making schema changes” on page 275.
sp_modify_remote_table procedure Purpose To change the resolution objects for a table marked for SQL Remote replication. Syntax sp_modify_remote_table table_name, [ resolve_name, ] [ old_row_name, ] [ remote_row_name ] See also Argument Description table_name A table marked for SQL Remote replication. resolve_procedure The name of the new stored procedure for carrying out actions when a conflict occurs.
Chapter 18.
sp_passthrough procedure Purpose To send a SQL statement in passthrough mode. Syntax sp_passthrough statement See also Argument Description statement A string containing a statement to be executed in passthrough mode. “sp_passthrough_piece procedure” on page 403 “sp_passthrough_stop procedure” on page 405 “sp_passthrough_subscription procedure” on page 406 “sp_passthrough_user procedure” on page 407 “PASSTHROUGH statement” on page 368 Description To send passthrough operations.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_passthrough_piece procedure Purpose To build a long SQL statement for passthrough. Syntax sp_passthrough_piece string See also Argument Description string A piece of a statement to be executed in passthrough mode.
begin transaction go exec sp_passthrough_piece ’CREATE TABLE DBA.
Chapter 18.
sp_passthrough_subscription procedure Purpose Adds subscribers to a given publication to the recipient list for passthrough statements. Syntax sp_passthrough_subscription publication_name, subscribe_by See also Argument Description publication_name The name of the publication subscribe_by The subscription value for recipients to receive passthrough statements.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_passthrough_user procedure Purpose Adds a named user to the list of recipients for passthrough statements. Syntax sp_passthrough_user user_name See also Argument Description user_name The user to be added to the list of recipients.
sp_populate_sql_anywhere procedure Purpose To create a copy of the Adaptive Server Anywhere system tables in the TEMPDB. This procedure is used by the extraction utility ssxtract. Syntax sp_populate_sql_anywhere Description To create a set of Adaptive Server Anywhere system tables for a remote Adaptive Server Anywhere database, in TEMPDB.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_publisher procedure Purpose To set the publisher of the current database, or to remove the publisher. Syntax sp_publisher [ user_name ] See also Argument Description user_name The user ID to be identifies as the publisher for the database. “Managing SQL Remote permissions” on page 201.
sp_queue_clean procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_clean Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It removes from the stable queue any transactions that completed after the start of the oldest incomplete transaction the last time the log was scanned.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_queue_confirmed_delete_old procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_confirmed_delete_old Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It removes from the stable queue any transactions whose offsets are shown in sr_confirmed_transaction.
sp_queue_confirmed_transaction procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_confirmed_transaction offset Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It adds the supplied offset to sr_confirmed_transaction. SQL Remote removes from the stable queue any transactions whose offsets match this offset.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_queue_delete_old procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_delete_old Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It deletes from the stable queue any transactions that have been confirmed by all remote databases.
sp_queue_drop procedure Purpose To drop the stable queue objects from a database. Syntax sp_queue_drop See also “sp_drop_sql_remote procedure” on page 389 Description Drops the stable queue system objects from the database, so that the database no longer supports a SQL Remote stable queue. The sole stable queue object not removed is the sp_queue_drop procedure itself (a procedure cannot drop itself from a database).
Chapter 18. Command Reference for Adaptive Server Enterprise sp_queue_dump_database procedure Purpose To facilitate recovery from media failure when the stable queue is in a separate database from the SQL Remote objects.
sp_queue_dump_transaction procedure Purpose To facilitate recovery from media failure, when the stable queue is in a separate database from the SQL Remote objects. Syntax sp_queue_dump_transaction See also “sp_queue_dump_database procedure” on page 415 “Stable queue recovery issues” on page 273 Description Keeping the stable queue in a separate database complicates backup and recovery, as consistent versions of the two databases have to be recovered.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_queue_get_state procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_get_state Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It returns a description of the current state of the stable queue.
sp_queue_log_transfer_reset procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_log_transfer_reset Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It resets the page and row IDs to zero in the sr_queue_state table.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_queue_read procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_read start_offset, stop_offset Description This procedure reads transactions from the stable queue. It is exclusively for use by the Message Agent.
sp_queue_reset procedure Purpose To reset the server to a point where the stable queue is empty. Syntax sp_queue_reset Description This procedure is used by the SQL Remote Message Agent, and should not be called directly in a production environment. It deletes all rows from the stable queue sr_transaction table, and resets the sr_queue_state table, ready for a new SQL Remote setup. In a development phase, this procedure can be useful to reset the server.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_queue_set_confirm procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_set_confirm confirm_offset Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It sets the minimum confirmation offset from all remote users in the sr_queue_state table.
sp_queue_set_progress procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_set_progress page_id, row_id, commit_offset, backup_offset, marker Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It sets the transaction log scanning progress value in the sr_queue_state table.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_queue_transaction procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly. Syntax sp_queue_transaction offset, user_id Description This procedure is used by the SQL Remote Message Agent, and should not be called directly. It adds a new transaction to the stable queue.
sp_remote procedure Purpose This procedure is used by the SQL Remote Message Agent, and should not be called directly, with a single exception described below. It manages rows in the sr_remoteuser table. Syntax sp_remote operation, user_name [ , offset ] [ , confirm ] Description Argument Description operation The name of an action. The only value that should be used by a user is reset; all others are for use by the Message Agent.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_remote_option procedure Purpose To set a SQL Remote option. Syntax sp_remote_option option_name, option_value Argument Description option_name The name of one of the SQL Remote options option_value The value to which the option is set. See also “SQL Remote options” on page 315. Description The SQL Remote options provide control over replication behavior.
sp_remote_option Verify_all_columns, OFF go 426
Chapter 18. Command Reference for Adaptive Server Enterprise sp_remote_type procedure Purpose To create or modify a SQL Remote message type. Syntax sp_remote_type type_name publisher_address Argument Description type_name The message type to create or alter. This must be one of the following: ♦ file ♦ ftp ♦ smtp ♦ mapi ♦ vim publisher_address See also The address of the publisher under the specified message type.
sp_remove_article procedure Purpose To remove an article from a publication Syntax sp_remove_article publication_name, table_name See also Argument Description publication_name The name of the publication from which the article is to be deleted. table_name The table containing the article. “sp_add_article procedure” on page 381 “ALTER PUBLICATION statement” [ASA SQL Reference, page 280] Description Example Running sp_add_article removes an article from a publication.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_remove_article_col procedure Purpose To remove a column from an article in a publication. Syntax sp_remove_article_col publication_name, article_name, column_name See also Argument Description publication_name The name of the publication to which the article belongs. article_name The article from which the column is to be removed. column_name The column to be removed from the article.
sp_remove_remote_table procedure Purpose To mark a table as unavailable for SQL Remote replication. Syntax sp_remove_remote_table table_name See also Argument Description table_name The table to be marked as not available for SQL Remote replication. “sp_add_remote_table procedure” on page 384 “sp_modify_remote_table procedure” on page 400 Description Example Marks a table as unavailable for replication.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_revoke_consolidate procedure Purpose To stop a user from being able to receive SQL Remote messages from this database. Syntax sp_revoke_consolidate user_name Argument Description user_name The user ID who will no longer be able to act as a consolidated database.
sp_revoke_remote procedure Purpose To stop a user from being able to receive SQL Remote messages from this database. Syntax sp_revoke_remote user_name Argument Description user_name The user ID who will no longer be able to receive SQL Remote messages. See also “sp_grant_remote procedure” on page 393 Description The sp_revoke_remote procedure removes a user ID from the list of users receiving messages from the current database.
Chapter 18. Command Reference for Adaptive Server Enterprise sp_subscription procedure Purpose To manage subscriptions. Syntax sp_subscription operation, publication_name, user_name, [ subscribe_by ] Argument Description operation The operation to be performed. This must be one of the following: ♦ create To create a subscription to a given publication for a user. ♦ drop To drop a subscription to a given publication for a user. ♦ start tion. To start a subscription to the named publica- ♦ stop tion.
sp_subscription_reset procedure Purpose To reset all SQL Remote information for all remote users. Syntax sp_subscription_reset Description This procedure resets all the entries in the sr_remote_user and sr_subscription tables to zero or NULL.
PART V A PPENDICES The appendices provide additional information that is not necessarily required for everyday use of the application.
APPENDIX A SQL Remote for Adaptive Server Enterprise and Adaptive Server Anywhere: Differences About this Appendix This appendix summarizes the differences between SQL Remote for Adaptive Server Enterprise and for Adaptive Server Anywhere. This appendix describes the main differences between these versions of the technology.
Types of difference The differences between the versions of the software are of the following kinds: ♦ Functionality Tasks that can be carried out by one of the two versions, but not by the other. ♦ Approach Although a similar result can be obtained, a different approach is required in each version. This includes tasks that are carried out in ways that are superficially different, but which have the same result.
Appendix A. SQL Remote for Adaptive Server Enterprise and Adaptive Server Anywhere: Differences Differences in functionality The major differences in functionality between SQL Remote for Adaptive Server Enterprise (SRE) and SQL Remote for Adaptive Server Anywhere (SRA) are as follows: ♦ Schema changes For SRE, schema changes must be made on a quiet system.
Differences in approach There are some features of SQL Remote that must be approached in a different manner in SRE and SRA. ♦ Partitioning tables that do not contain the subscription expression In SRA, publications can contain subqueries, and these allow tables that do not contain a partition expression to nevertheless be distributed properly among subscribers. In SRE, an additional column must be added to such tables, containing a list of subscribers, and triggers must be written to maintain the column.
Appendix A.
Limitations for Enterprise to Enterprise replication If you wish to use SQL Remote for replication between Adaptive Server Enterprise databases, rather than with Adaptive Server Anywhere remote databases, you should be aware of the following limitations: ♦ Database extraction The extraction utility creates RELOAD.SQL scripts and data files for building Adaptive Server Anywhere remote databases. Setting up remote ASE databases requires an extraction process created by the customer.
Appendix A. SQL Remote for Adaptive Server Enterprise and Adaptive Server Anywhere: Differences ♦ Synchronize subscription This is not implemented for Adaptive Server Enterprise remote databases.
APPENDIX B Supported Platforms and Message Links About this Appendix Contents This appendix summarizes the platforms and message links that SQL Remote supports.
Supported message systems SQL Remote exchanges data among databases using an underlying message system. SQL Remote supports the following message systems: ♦ File sharing A simple system requiring no extra software. ♦ FTP Internet file transfer protocol. ♦ SMTP/POP Internet e-mail protocol. ♦ MAPI Microsoft Messaging Application Programming Interface, used in Microsoft products and in cc:Mail release 8 and later. ♦ VIM Vendor Independent Messaging, used in Lotus Notes and in some versions of Lotus cc:Mail.
Appendix B. Supported Platforms and Message Links Supported operating systems SQL Remote for Adaptive Server Enterprise SQL Remote for Adaptive Server Enterprise is available for the following operating systems and message links: ♦ Windows NT/2000/XP All message protocols. ♦ Sun Microsystems Solaris/Sparc File sharing, FTP, and SMTP/POP only.
448
Index Symbols #hook_dict table dbremote 320 unique primary keys in SQL Remote for ASA 131 #remote table about 338 A ActiveSync Windows CE replication 447 Adaptive Server Anywhere creating ASE-compatible databases 74 replicating triggers 81 SQL Remote tutorial 27 Adaptive Server Enterprise replicating triggers 80 SQL Remote setup 21 SQL Remote tutorial 53 uninstalling SQL Remote for ASE 26 adding articles in SQL Remote for ASA 99 addresses file sharing 216 FTP 216 setting for publisher 210 SMTP 218 SMTP/POP
Index C cache for messages 229 ccMail SQL Remote 210 character sets compatibility in SQL Remote replication 74 conversions in SQL Remote 75 collations SQL Remote 75 columns publishing selected columns in SQL Remote for ASA 94 command line environment variables 294 Message Agent 294 COMMIT statement event-hook procedures 320 replicating 78 compatibility among databases in SQL Remote replication 74 ASE and ASA in SQL Remote replication 74, 196, 197 COMPRESSION option replication option 316 configuration file
Index articles in SQL Remote for ASA 93, 99 articles with column-wise partitioning in SQL Remote for ASE 143 articles with row-wise partitioning in SQL Remote for ASE 144 message types 211, 212 publications in SQL Remote for ASA 37, 93 publications in SQL Remote for ASE 143 publications with column-wise partitioning in SQL Remote for ASA 94 publications with column-wise partitioning in SQL Remote for ASE 143 publications with row-wise partitioning in SQL Remote for ASA 95 publications with row-wise partiti
Index about 235 deploying SQL Remote databases 185, 187 design locking in SQL Remote for ASE 147 many-to-many relationships in SQL Remote for ASA 112 many-to-many relationships in SQL Remote for ASE 157 many-to-many relationships SQL Remote example for ASA 115 many-to-many relationships SQL Remote for ASE example 157 performance for SQL Remote for ASA 103 publications in SQL Remote for ASA 102 replication conflicts and publications 88 replication errors and publications 88 SQL Remote for ASE 141 SQL Remote
Index sp_hook_dbremote_shutdown stored procedure 321 sp_hook_ssrmt_begin stored procedure 320 sp_hook_ssrmt_end stored procedure 320 sp_hook_ssrmt_message_apply_begin stored procedure 322 sp_hook_ssrmt_message_apply_end stored procedure 323 sp_hook_ssrmt_message_missing stored procedure 322 sp_hook_ssrmt_message_sent stored procedure 322 sp_hook_ssrmt_receive_begin stored procedure 321 sp_hook_ssrmt_receive_end stored procedure 321 sp_hook_ssrmt_send_begin stored procedure 322 sp_hook_ssrmt_send_end stored
Index consolidated database SQL Remote for ASA setup SQL Remote setup granting CONSOLIDATE permissions PUBLISH permissions REMOTE permissions groups extracting 42 35 204 204 201 204 195, 305 H host control parameter FTP message type 216 I icons used in manuals IF statement passthrough mode replication IMAGE data type replicating INSERT statement replicating Internet e-mail SQL Remote IPM_Receive control parameter MAPI message type IPM_Send control parameter MAPI message type xiv M 262 262 83 78 218 2
Index command 292 connections 224 continuous mode 223 daemon 300 delivering messages 237, 239 introduction 9 -l option 225 -m option 229 message tracking 237, 239 output in SQL Remote for ASA 245 output in SQL Remote for ASE 271 performance 228, 232 polling 230 -rd option 230 replicating data in SQL Remote for ASA 47 replicating data in SQL Remote for ASE 66 reporting errors 245 resend requests 230 -ro option 226 -rp option 230, 231 -rt option 226 running 242 running as a service 242 schema changes 275 sec
Index named defaults extraction utility NCHAR data type extraction utility NetWare SQL Remote supported SQL Remote message types newsgroups technical support Notes SQL Remote Novell NetWare SQL Remote NVARCHAR data type extraction utility using 197 197 216 210 xvi 210, 221 447 197 O object SQL Remote table ASE 340 offsets transaction log 237 option SQL Remote table ASE 340 options BLOB_THRESHOLD 315 COMPRESSION 315 DELETE_OLD_LOGS 315 EXTERNAL_REMOTE_OPTIONS 315 extraction utility 319 QUALIFY_OWNERS 315
Index PUBLISH 35, 42, 201 REMOTE 35, 42, 204 revoking REMOTE 207 SQL Remote administration 200 platforms SQL Remote supported operating systems 445 policy example SQL Remote for ASA publications 112 polling messages 230 pop3_host control parameter SMTP message type 219 pop3_password control parameter SMTP message type 219 pop3_userid control parameter SMTP message type 219 port control parameter FTP message type 216 primary key pools generating unique values using default global autoincrement in SQL Remote
Index ASA 112, 114, 115 many-to-many relationships in SQL Remote for ASE 157 many-to-many relationships in SQL Remote for ASE example 157 notes for SQL Remote for ASA 101 performance in SQL Remote 86 performance in SQL Remote for ASA 103 primary keys in SQL Remote for ASA 102, 127, 129, 134 primary keys in SQL Remote for ASE 147, 175 properties in SQL Remote for ASA 93 referential integrity in SQL Remote for ASA 127 referential integrity in SQL Remote for ASE 173 row-wise partitioning in SQL Remote for ASA
Index remotetype SQL Remote table ASA 329 ASE 342 remotetypes SQL Remote view ASE 347 remoteuser SQL Remote table ASA 329 ASE 343 message tracking 237 using 237 remoteusers SQL Remote view ASA 334 ASE 348 replication administering 13 ASE limitations 442 backup procedures 249, 253, 257, 272 blobs 83 case studies 15 conflicts 88 control statements 262 cursor operations 262 cursor statements 262 data definition statements 82 data recovery 224 data types 83 dbremote 292 design for ASE 147 Message Agent 292 mix
Index tracking SQL errors replications options COMPRESSION reporting conflicts in SQL Remote for ASA errors reporting errors Message Agent resend requests about messages resetting subscriptions RESOLVE UPDATE triggers about REVOKE CONSOLIDATE statement SQL Remote setup REVOKE PUBLISH statement REVOKE REMOTE statement revoking CONSOLIDATE permissions PUBLISH permissions REMOTE permissions revoking remote permissions -ro option Message Agent roles extraction utility ROLLBACK statement event-hook procedures r
Index smtp_password control parameter SMTP message type 219 smtp_userid control parameter SMTP message type 219 sp_add_article procedure syntax 381 sp_add_article_col procedure syntax 383 sp_add_remote_table procedure syntax 384 sp_create_publication procedure syntax 386 sp_drop_publication procedure syntax 387 sp_drop_remote_type procedure syntax 388 sp_drop_sql_remote procedure syntax 389 uninstalling SQL Remote for ASE 26 sp_grant_consolidate procedure syntax 390 sp_grant_remote procedure syntax 393 sp_
Index about syntax sp_populate_sql_anywhere procedure about syntax sp_publisher procedure syntax sp_queue_clean procedure syntax sp_queue_confirmed_delete_old procedure syntax sp_queue_confirmed_transaction procedure syntax sp_queue_delete_old procedure syntax sp_queue_drop procedure syntax uninstalling SQL Remote stable queue sp_queue_dump_database procedure syntax sp_queue_dump_transaction procedure syntax sp_queue_get_state procedure syntax sp_queue_log_transfer_reset procedure syntax sp_queue_read proc
Index Message Agent introduction 9 Message Agent performance 228 message delivery 237, 239 message tracking 237, 239 message types for Windows CE 212 mobile workforces 13, 15 multi-tier installations 262 options 315 procedures before extracting databases 192 publications 11 replicating data types 83 replicating dates 84 replicating DDL statements 82 replicating deletes 78 replicating inserts 78 replicating procedures 80 replicating times 84 replicating triggers 80 replicating updates 78 replication system
Index sp_subscription_reset SQL Remote setup ssremote.sql stableq.
Index sr_transaction table ASE 351 ssqueue about 277, 312 architecture 279 command line 312 setting up 282 when needed 278 ssremote about 223, 269, 292 command 292 introduction 9 Message Agent 66 security 269 ssremote.sql SQL Remote for ASE setup 21 ssupdate.sql upgrading SQL Remote for ASE 25 ssxtract utility about 189, 303 options 306 syntax 303 using 191, 196 stable queue cleaning 296 Replication Server 279 setup 23 SQL Remote Open Server 279 ssqueue 279 system tables 350 stableq.
Index databases using SQL Remote 185 event hooks 320 mixed operating systems and SQL Remote 189 using dbxtract 191 using SQL Remote 189 using ssxtract 191 SYNCHRONIZE SUBSCRIPTION statement about 198 synchronizing SQL Remote databases using the message system 198 SYSREMOTEUSER table 237 using 237 system objects the dbo user 302 system objects for Adaptive Server Anywhere SQL Remote 325 system objects for Adaptive Server Enterprise 337 system tables SQL Remote for ASA 326 stable queue 350 T tables column-w
Index SQL Remote for ASA 27 U -u option Message Agent 225 uninstalling SQL Remote stable queue 26 SQL Remote system objects from a database 389 SQL Remote for ASE 26 stable queue objects from a database 414 unique column values generating in SQL Remote for ASA 129 unique values generating using default global autoincrement in SQL Remote for ASA 129 generating using pools for SQL Remote for ASA 133 UNIX supported SQL Remote message types 210 unlink_delay control parameter FILE message type 216 unloading S
